Routines

Routines are stored procedures and functions that you can create, alter, template, drop, migrate, and compare.
2
2
Routines are Db2 executable SQL objects that can be called to perform operations. Routines are stored procedures and user functions. You can create, alter, template, drop, migrate, and compare routines.
You can reference routines by a trigger, a view, or application logic (for example, external program and SQL procedure statements). Migration strategies support these objects, and the drop object feature within a migration analysis generates DROP statements for user functions.
Routine Processing
The name, schema, object type, and version uniquely determine a stored procedure or a user function. You can reference routines by a trigger, a view, or application logic (for example, external program and SQL procedure statements).
User functions (UFs) and stored procedures (PRs) are treated as if they are composed of two components: definition and implementation. For example, declaration or Db2 registration piece, and the UF or PR text or body.
  • For non-native SQL stored procedures, only migration of the definition component is supported.
    If the implementation text for a non-native SQL stored procedure is not available in the catalog, the definition component of the non-native SQL stored procedure is generated as comments. These comments are used to view the attributes of the PR and to prevent interference with the usual methods that are used to create an SQL procedure, including the following methods:
    • JCL
    • SQL procedure processor DSNTPSMP
    • IBM Stored Procedure Builder
  • For native SQL stored procedures, both components are in the definition.
  • Migration of the implementation component is supported for SQL scalar functions (compiled SQL, inlined SQL, and external), sourced functions (both built-in and user functions), and table functions (SQL and external).
VSAM and PDS access are not supported when accessing SYSROUTINES_SRC and SYSROUTINES_OPTS. The EXTERNAL NAME clause is removed to enable DSNTPSMP to generate an external name.
DDL Requirements
When a routine is defined in Db2, it can accept zero or more table parameters. However, when the routine is migrated, a table using a table parameter might not exist. To generate the proper routine definition, RC/Migrator must generate and commit CREATE TABLE DDL for all transient table parameters. The transient table columns must match in data type, length, precision, scale, and encoding scheme to the description of the routine table parameter columns used when the routine was created.
The routine CREATE DDL syntax prohibits the use of a declared temporary table. In addition, the maximum record size of the table and the CCSID of string data columns in the table must be considered.
Size Considerations
The maximum record size of a table depends on the page size of the tablespace. To handle any possible table size, CREATE TABLE DDL in a tablespace that uses the largest page size is needed. RC/Migrator is not aware of whether such a tablespace is present on the target subsystem. Therefore, to generate a CREATE TABLE, CREATE TABLESPACE having the largest possible page size is necessary to contain the TABLE.
An implicit creation of a tablespace using a CREATE TABLE is unreliable. An implicitly created tablespace resides in the default database (DSNDB04) and will “inherit” the page size (buffer pool) specified for DSNDB04. This inherited value might be inadequate to accommodate the maximum record size needed by the table.
Encoding Scheme Considerations
The CCSID clause of CREATE TABLE specifies the encoding scheme for string data (columns) stored in a table. The value must agree with the encoding scheme for the tablespace. This restriction is handled by explicitly including a CCSID clause in the same CREATE TABLESPACE statement that is generated to address the maximum record size concern. A tablespace is needed for each CCSID required by the routine table parameters that are selected for migration.
Other DDL Considerations for Routines
The generation of CREATE TABLE and CREATE TABLESPACE DDL introduces these concerns:
  • Generated TABLE and TABLESPACE keys must be unique.
  • The largest possible page size, as determined by the buffer pool name that is specified in a CREATE TABLESPACE, must identify an activated buffer pool on the target subsystem.
Generating multiple unique TABLE and TABLESPACE keys is handled by requiring that the CREATOR-component (for tables) and DBNAME-component (for tablespaces) are specified (through the MIGRATOR parmlib member in hlq.CDBAPARM), under which any table or tablespace name can be freely created and destroyed. The generated table names follow the sequence T1, T2, and so on, as needed by the routine. The generated tablespace DBNAME-components are CATSA, CATSE, and CATSU for ASCII, EBCIDIC, and Unicode encoding schemes, respectively.
Using a user-specified database requires a transient CREATE DATABASE statement is generated. The generated statement uses a specified buffer pool (through the MIGRATOR parmlib member) that satisfies the largest possible page size requirement and that is activated on the target subsystem. The CURRENT RULES in Db2 are assumed so that creates of tables that contain LOB columns do not result in the implicit creation of LOB tablespaces, auxiliary tables, or indexes on auxiliary tables.
To allow for a large number of table parameters to define a specific routine, the generated transient tables are dropped after each routine has been created. After all routines that are selected for migration have been created, the generated transient database (and tablespaces) is dropped.
You can specify the hlq.CDBAPARM(MIGRATOR) parmlib member keywords (RTNDBNAME, RTNBP, and RTNTBCR) for each target subsystem ID.
For more information about the options that are available for a migration strategy, see Migration Strategies.