Skip to content

Operational Considerations

Last update May 15, 2018


Review the following operational considerations before you begin using the product:

MIGRATOR Parmlib Options

Review the MIGRATOR parmlib member in hlq.CDBAPARM and set default configuration values for your environments. Note the following options:

  • FULLTREE BUILD(NO|YES). Specifying FULLTREE(NO) for an alteration or migration strategy can significantly reduce the amount of processor storage and CPU used during strategy analysis. When FULLTREE(NO) is in effect, the object tree that is built is limited to only the databases that are involved in the strategy. This is called the limited tree. If the DB2 catalog contains many databases, and the strategy is limited to only a few of them, this can significantly reduce the amount of processor storage used for the object tree. A secondary effect is a reduction in the amount of CPU used, due to fewer objects being processed.

    Note: Specifying FULLTREE(NO) does not guarantee the use of a limited tree. This option is ignored for compare type strategies and for strategies in which a STOGROUP is being dropped. It is also ignored for view processing.
  • SORTUNLD(NO|YES). Set this option to NO to avoid sort processing for large tables. This option suppresses generation of the ORDER BY clause when unloading a table. Use of this option can prevent sort work outages, and reduce CPU consumption and elapsed time when sorted data is not needed.
  • VIEWSCAN(NO|YES). Set this option to YES for an alteration or migration, or compare strategy to scan all view text of included views for possible text after the semicolon of the view, which is caused by creating a view in QMF. In QMF all text after the view text is also stored in sysviews text. The default is VIEWSCAN(NO). The additional text is stripped from the view.
  • SETTERM(x)
    Specifies the SQL termination character to use as the default value for your site or to include the SET TERMINATOR statement in DDL generation for triggers, XML indexes, external SQL functions, and native stored procedures. You cannot specify a comma, double quote, single quote, left or right parentheses, or an underscore.
    When a valid value other than blank or semi-colon (;) is specified, all CREATE statements for triggers, XML indexes, SQL stored procedures, and SQL scalar functions (including all ALTER FUNCTION and PROCEDURE statements that include SQL) are wrapped with a pair of --#SET TERMINATOR statements. The first statement sets the overriding terminator. The second statement restores the termination character to the DB2 default. When the default value is specified or defaulted, the statements are not generated.

Enable Parallel Load and Unload Processing

CA RC/Migrator™ for DB2 for z/OS generates unloads and loads into multiple streams and balances the streams so that the jobs can complete in a shorter amount of time. Parallel processing is useful for large tables with many partitions because it can process the data more quickly.

Parallel unload and load processing is supported for the following utilities:

  • CA Fast Unload® for DB2 for z/OS (CA Fast Unload) and CA Fast Load for DB2 for z/OS (CA Fast Load)
  • IBM UNLOAD and IBM LOAD

You can enable parallel load and unload processing using Utility Profile Services or by specifying symbolic values.

To use symbolic values, see Symbolic Parameters.

Important!  To exploit parallel unload and load processing in existing IBM and CA Technologies unload and load utility models, review the CA RC/Migrator migration considerations .

Follow these steps:

  1. Verify that the utility definition is activated:
    1. Select the Profile option from the product main menu.
    2. Select the Model Services option.
    3. Select a utility model ID for update.
      The General Model Utilities panel appears.
    4. Type X next to the utility definition you want to activate. The following definitions are provided for parallel unload and load processing:
      • For CA Fast Unload and CA Fast Load: FLOAD, FUNLD, and FUNLD_B.
      • For IBM UNLOAD and IBM LOAD: IBMULD, IBMULD_B, and LOAD.
      For more information about these utility definitions, see Utilities Referenced by the DEFAULT Model ID.
  2. Enable parallel load and unload processing using Utility Profile Services:
    1. Select the Profile option from the product main menu.
    2. Select the Utility Profile Management option.
    3. Create a profile, or update or template an existing profile using line commands.
      The Schema Profile panel appears.
    4. Complete the following fields:
      • Specify U (update) in the General and Parallel Options field.
      • Specify P (parallel) in the Before or Normal Unld and Load processing columns for the desired object types.
      Press Enter.
      The Utility Options Detail panel appears.
    5. Specify the following options:
      • General profile options that control the BIND utility and model ID selection for strategy analysis.
      • Parallel processing options for CA Fast Unload and CA Fast Load, which determine the number of tasks to execute in parallel. These options also control the output format of the unload files.

        Note: These options do not apply for IBM UNLOAD and LOAD processing. For these utilities, TEMPLATE statements are generated irrespective of the USETMPLT symbol value when parallel processing is enabled using Utility Profile Services.
      Press the End key (F3). If prompted, confirm the request to create the profile.

    Parallel load and unload processing is enabled.

Temporal Table Support

Temporal table support is provided in migrate, alter, and compare strategies for DB2 subsystems running DB2 10 or above. System-period (SYSTEM_TIME), business-period (BUSINESS_TIME), and bi-temporal tables are supported. This support includes strategy creation, analysis, and execution.

If you are implementing temporal table support, note the following items:

  • Support is provided for temporal tables that exist in explicitly created tablespaces. For temporal tables that exist in implicitly created tablespaces, SQL errors can occur.
  • History tables are not displayed in the list of objects when creating an alter or compare strategy.
    To create the history table, use either of the following options:
    • Template the base table, delete the PERIOD clause and AS column clauses: AS ROW BEGIN, AS ROW END, and AS TRANSACTION STARTID, and then ALTER TABLE ADD VERSION.
    • Flip the table type to REGULAR, and then ALTER TABLE ADD VERSION.
    To add or drop versioning, use a compare strategy or ISQL.
  • When altering a temporal table, you can:
    • Add or change a default attribute of a column defined AS ROW BEGIN, AS ROW END, or AS TRANSACTION START ID.
    • Add a PERIOD definition.
  • When creating, altering, or templating a table in a compare or migrate strategy, you can add the BUSINESS_TIME WITHOUT OVERLAPS clause to the definition of a unique constraint. Use the B option on the Unique Constraints Management panel.

    Note: The table must be a temporal table with a BUSINESS_TIME period defined. The constraint cannot explicitly specify a column of the BUSINESS_TIME period.
  • When creating, altering, or templating an index in a compare or migrate strategy, you can specifically include or exclude the BUSINESS_TIME WITHOUT OVERLAPS clause in the index key. To enable this functionality, use the BTWOO field on the Index Create, Alter, and Template panels.
    The index and table must meet the following conditions:
    • The index must be defined as unique, nonpartitioned, and type 2.
    • The index key cannot explicitly specify a column of the BUSINESS_TIME period.
    • The table must be defined with a BUSINESS_TIME period.
  • Support is not provided for the following options:
    • Compare rules for temporal table attributes (like period, versioning schema, and versioning table). Instead, CA RC/Compare™ for DB2 for z/OS behaves as if there were rules and they are all on (set to Y).

      Note:  Temporary support is added to use Creator and Name rules of table for Versioning schema and Versioning table if the paired source and target tables are SYSTEM PERIOD temporal tables.
    • Dependent object strategies (dependency strategies).
    • History tables are not displayed in the list of objects when creating an alter or compare strategy.
    • Alter or compare on history tables. Base temporal tables must be selected for strategies.
  • For the standalone syntax checker, when executing with OBJVER=Y, syntax checking the following statements is not currently supported:

    ALTER TABLE ... ADD VERSIONING
    ALTER TABLE ... ADD PERIOD
    

Example: How to Create System Time and Business Time Temporal Tables

This example provides high-level information for creating, templating, and altering temporal tables. For a system time temporal table, the BEGIN, END, and transaction ID columns must be TIMESTAMP(12). The default indicators are B, E, and I, respectively. For a business time temporal table, you can pick all timestamp formats or date, size of 6.

Follow these steps:

  • Create an alteration strategy:
    • Create a table, change the Table Type to T (for temporal), and add columns.
    • Create a history table by template the previously created table. Change the Table Type to R (regular) and change the table name.
    Save, analyze, and generate DDL.
  • To tie together the tables by adding versioning, use ISQL (ALTER TABLE ADD VERSION).
  • To alter these tables, change the business table column type from date to timestamp, and analyze the changes. The drop and recreate are generated created for the table. You can also add new columns.

Restart of Identity Column Numbering after a Table Reload

When a table with an identity column is dropped and re-created in an alter, compare, or in-place migration strategy, the original table definition remains intact (including the original starting value for the column). However, this process can lead to duplicate rows when additional rows need to be inserted.

To avoid potential conflicts with duplicate identity column rows:

  • Install the Table Process Exit (RMRTPE) stored procedure. RMRTPE resets identity column numbering after a table reload. RMRTPE is called before the table is dropped to save the MAXASSIGNEDVAL of the identity column with +1 added to that value. MAXASSIGNEDVAL is stored in an internal sequence object. Once the table is recreated and loaded, RMRTPE is calledagain to retrieve the value that was saved from the first call. Behind the scenes, an ALTER TABLE, ALTER column RESTART WITH is executed.

    Note: Saving the MAXASSIGNEDVAL of the identity column with +1 added may not be appropriate for your site. For example, this value might not be appropriate when the identity column is defined with:
    • A descending increment
    • An increment other than 1
    • A CYCLE
    • A MINVALUE or MAXVALUE that matches MAXASSIGNEDVAL
    • Other site-specific requirements that would also make this change invalid. 

    We recommend that you review RMRTPE and make changes as necessary so that it addresses the requirements of your environment.

  • Add new entries to Model Services to ensure that after a table is dropped, recreated, and reloaded, any inserted identity columns begin from the highest value that existed before the table was dropped.

Any sequences that are created during this process are automatically dropped by CA RC/Migrator processing after the new starting value is obtained.

The following BPA077E messages might display under certain circumstances:

BPA0077E: 4 = RC from RMRTPE "GETIDCOLS". The MAXASSIGNED value is NULL or not available for identity column in table tb-creator.tb-name. The RESTART WITH value will not be set for that column.

BPA0077E: 4 = RC from RMRTPE "PUTIDCOLS". The temporary SEQUENCE used to hold the RESTARTWITH value for the identity column in table tb-creator.tb-name could not be found. The RESTART WITH value will not be set for that column.

If you receive the first message, it means that:

  • The table was newly recreated, but empty
  • The RESTART WITH attribute of the table was altered by an ALTER TABLE statement, but no rows have been inserted yet through an SQL INSERT
  • The table was newly recreated and populated with a LOAD utility, but no rows have been inserted yet through an SQL INSERT.

If you receive the second message, it means that the temporary sequence that should have been created in the GETIDCOLS step was not created. This can occur because the GETIDCOLS step failed or was subsequently deleted before running the PUTIDCOLS step in the REXX EXEC.

Unique Index INCLUDE Column Support

DB2 10 New Function Mode (NFM) includes a new INCLUDE clause on the CREATE INDEX and ALTER INDEX statements. The INCLUDE clause lets you define nonkey columns in a unique index. These nonkey columns let queries use the unique index for index-only access, so you can eliminate indexes that were created solely to enable index-only access. Eliminating unnecessary indexes can help improve your system performance, simplify index maintenance, and decrease physical storage requirements.

The ALTER INDEX... ADD INCLUDE(column-name) statement is generated. Use utility profile services (Expert profile menu option U) to ensure generation of the REBUILD INDEX utility. If utility profile services is not used, the altered index status will be AREOR or RBDP.

Trusted Context Support

Trusted context and roles support is provided for alter, create, template, and drop options.

An internal error occurs when attempting to alter or template a trigger, sequence, or stored procedure under a trusted context.

Clone Support

If a base table has a clone, the base and clone and all dependent objects are migrated. To migrate a table without its clone, use a dependency strategy. The base table is included automatically when a clone is selected with a dependency strategy. However, you can select the base and exclude the clone.

If a change is made to the base table that requires it to be dropped and recreated, the clone is preserved. To add or drop a clone table, update the DDL with the ALTER TABLE or DROP TABLE statements, respectively

Note: The RC/Merger component also supports copying or moving cloned objects.

Native SQL Stored Procedure Support Limitations

If you use wildcards in the SQL PATH global change attribute for native SQL stored procedures, the attribute is restricted to 256 characters. Otherwise, the maximum length of SQL PATH is 2048 characters.

Reordered Row Format and RC/Merger

Tablespaces created in DB2 9 will be segmented tablespaces in Reordered Row Format (RRF) for NFM and Basic Row Format (BRF) for CM or prior releases of DB2.

Note the following supported formats:

  • Simple tablespaces are allowed only if they existed prior to converting to DB2 9.
  • Segmented BRF tablespaces are allowed if they exist prior to converting to DB2 9 NFM.

BRF and RRF physically store the rows in the pages of the pageset in different formats, which makes them incompatible for data set move/copying that is done by RC/Merger.

To prevent unpredictable processing results, RC/Merger should be used only when the source tablespace is simple or the source and target segmented tablespace pagesets for each partition are in the same row format.

RC/Merger supports the following formats:

  • Source and target are simple
  • Source is simple and target is segmented BRF (see first note)
  • Source is simple and target is segmented RRF (see first note)
  • Source is segmented BRF and target is segmented BRF
  • Source is segmented RRF and target is segmented RRF

Notes:

  • Source tablespaces that are not segmented (simple) are supported by RC/Merger when the target subsystem is DB2 9.
  • When the source is segmented BRF and the target is simple or segmented RRF, do not use RC/Merger.
  • When the source is segmented RRF and the target is simple or segmented BRF, do not use RC/Merger. This format is not supported by RC/Merger.

If RC/Merger creates the target objects, the tablespaces will have the format as documented previously, with the exception of simple tablespaces, which RC/Merger will handle.

If a DATAONLY copy will be done, the following query can be used to determine row format in a DB2 9 NFM subsystem. It can be used for the source and target tablespace when they are in a DB2 9 subsystem. A FORMAT value of R means RRF and blank means BRF.

SELECT TS.DBNAME, TS.NAME, TS.SEGSIZE, TP.PARTITION, TP.FORMAT
 FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLEPART TP         
WHERE TS.DBNAME = '<DBNAME>'                                  
  AND TS.NAME   = '<TSNAME>'                                  
  AND TP.TSNAME = TS.NAME                                     
  AND TP.DBNAME = TS.DBNAME;

Note: The previous information is a concern only for source tablespaces that are segmented. Source tablespaces that are not segmented (simple) are supported by RC/Merger when the target subsystem is DB2 9.

Simple Tablespace Move

After a successful RC/Merger analysis execution, if you browse or edit the table in the target subsystem, you will receive SQL -438 when your source tablespace is SIMPLE and your target subsystem is running using DB2 9 or later.

If message RCM0263W occurs during execution, use the following process to rebuild the database:

  • Use the DB2 command to change the database status to UT.
  • Run the REPAIR utility to rebuild the database on the target subsystem. The database and subsystem are identified in the message text.

    Note: Skeleton JCL is provided in CDBASRC(REPAIR).
  • Use the DB2 command to change the database status to RW after the REPAIR utility is completed.

Universal Tablespace Support Limitations

LOB and XML columns on tables are not supported in a partition-by-growth (PBG) UTS. Model services does not support generation of CA utilities for a PBG UTS (or tables in this type of tablespace).

Although you can migrate tables that have PARTITION BY SIZE clauses, you cannot create or edit these tables.

DB2 9 Unsupported Features Detection

During every strategy analysis, the DB2 catalog is read. Checks are provided for DB2 9 New Function Mode (NFM) features that may not be supported. If unsupported features are found, warning messages are directed to a separate output DD, named RCMTOLR.

The following DB2 9 features are detected and reported on:

  • XML tablespaces, tables, and indexes
  • Tables with multiple encoding schemes
  • Tables with FOR EACH ROW ON UPDATE

All DB2 9 features are not detected. For example, the following unsupported DB2 9 features are not detected:

  • Reordered rows
  • Implicit databases

Index on Expression Toleration Limitations

Index on expression (IOE) support is functionally incomplete. The implementation was designed to minimally tolerate IOEs and ensure that no loss of expression in the index definition occurs during strategy analysis. Support does not include manipulating the key-expression values by using advanced product techniques.

Index on expression (IOE) toleration support has the following limitations:

  • You cannot explicitly change an IOE into another kind of index by using the Index Alter or Index Template panels.
  • You cannot explicitly create an IOE by using the Index Create panel. To create an IOE, you must template an existing IOE.
  • There is no MATCHCASE or CAPS support for the expression fields. The fields are always maintained in mixed case.
  • Support is limited to Type 1 input methods (comparing DB2 subsystems) for comparison strategies.
  • Support is not provided for Global Change Services.
  • Support does not include a new Compare Rule Database Services attribute for indexes. The index key-expressions will be addressed by using the COLNAME index object attribute for a rule set.
  • Support for syntax checking support is not provided.

Materialized Query Table Restrictions

When you are using materialized query tables (MQT), the following restrictions apply:

  • The limit key values for table-controlled partitioning (TCP) MQT might not be formatted properly in the generated DDL. You can edit these manually to correct.
  • UNLOAD and LOAD utility control statements for data conversions might be generated incorrectly when an existing MQT is created or dropped and recreated as user-maintained, causing the data conversion to fail. Use MQT REFRESH instead of data unload during analysis to generate a REFRESH TABLE DDL statement instead of UNLOAD and LOAD utility statements.

  • Globally changing a table’s column names can cause the column list to be out of sync with the full select result set when converting a table to an MQT. We recommend that global changes of column names not be used in this situation.

Prevent Duplicate Sequences After a Drop-and-Recreate

If an alteration to a sequence causes the object to be dropped and recreated, the default start value for the sequence (START WITH) is set to its default. The recreated sequence also does not use the current value for MAXASSIGNEDVAL (which specifies the last possible assigned value for the sequence). These improper settings cause duplicate sequence objects.

To resolve this issue, do the following:

  1. Install the RMRSQPE stored procedure. RMRSQPE prevents duplicates for altered sequences. It is called before and after any sequence object is dropped and recreated. The procedure restarts numbering for the sequence object from the previous MAXASSIGNEDVAL after the sequence object is recreated.
    The object is reset to its previous MAXASSIGNEDVAL value so that the sequence object resumes.
  2. Define model utilities to call the stored procedure.
  3. Perform the alteration again. When the sequence object is recreated after an alteration, RMRSQPE restarts numbering for the sequence object from the previous MAXASSIGNEDVAL.

User-Defined Distinct Types Support Limitations

Existing user-defined distinct types (UDTs) are recognized, but they are not created, altered, compared, or dropped as objects. Therefore, you cannot create, alter, compare, or migrate UDT definitions.

XML Support Limitations

Note the following XML support limitations:

  • XML support is not provided for RC/Merger.
  • The CA Fast Unload® for DB2 for z/OS model utility can process tables containing XML columns. However, if you are using the IBM LOAD utility to load the table, the XML (and any LOB) columns must be moved to the end of the row. When CA Fast Unload® for DB2 for z/OS is used to unload the data, these columns are moved for you. When other unload utilities are used, the columns are not moved and errors can occur.

LOB Support Limitations

Note the following LOB support limitations:

  • When the Batch Processor is used to unload or load tables with LOB columns, the size of the LOB data cannot exceed 32 KB.
  • The CA Fast Load for DB2 for z/OS model utility can process tables with LOB columns. If XML columns are not present, the LOB columns can exist anywhere in the row. If XML columns are present, the LOB and XML columns must be moved to the end of the row. The model utility does not move these columns for you. Also, when XML columns are present, the CA Fast Load for DB2 for z/OS model utility cannot process them unless SWITCH-TO-IBM YES is specified in hlq.CDBAPARM(UTIL).
  • The CA Fast Unload® for DB2 for z/OS model utility can process tables containing LOB columns. However, if you are using the IBM LOAD utility to load the table, the LOB (and any XML) columns must be moved to the end of the row. When CA Fast Unload® for DB2 for z/OS is used to unload the data, these columns are moved for you. When other unload utilities are used, the columns are not moved and errors can occur.

Security Label Restrictions

The following restrictions apply when you create and alter tables with columns defined with the security label attribute (AS SECURITY LABEL):

  • The column must not be part of a unique constraint.
  • The table must not have editproc, validproc, audit, obid, data capture, ccsid, volatile or notvolatile checked.

    Note: SQL error -20240 is generated if errors occur.
  • Ensure that the user executing the product does not have authority (explicitly or through the .AUTH command) to drop and recreate a table without the appropriate security label when the table data is unloaded and loaded again.

    Note: The .AUTH command does not affect the security label.

To create a table with a security label column, specify L in the D column on the Table Create panel.

SET TERMINATOR Statement Support

The SET TERMINATOR option lets you change the SQL termination character when the default termination character conflicts with the SQL statements that are used in triggers, indexes, and routines.

The #SET SQL TERMINATOR statement is supported when:

  • Creating DDL syntax for triggers, XML indexes, external SQL functions, and native stored procedures.
  • Altering DDL syntax for native stored procedures.

You can specify the SET TERMINATOR option for migrate, compare, and alter strategies from the following panels:

  • Migrate Strategy Analysis Options
  • ICL/Alter/Compare Strategy Analysis Options
  • Strategy Recovery Options

The following values cannot be specified: double quotes ("), single quote ('), left or right parentheses, or underscore (_).

When specifying an alternate terminator character on the update or recovery options panel, the following parameters are automatically inserted into your PARMFILE DD input statements:

  • SETTERM
    Specifies the overriding terminating character for update analysis.
  • RSETTERM
    Specifies the overriding terminating character for recovery analysis.

When DDL is generated, the following statements will be wrapped in a pair of --#SET TERMINATOR statements:

  • CREATE statements that contain SQL or XML
  • ALTER statements when altering a native stored procedure

The first statement sets the overriding terminator. The second statement restores the termination character to the DB2 default (;).

Was this helpful?

Please log in to post comments.

  1. Lucio Basiaco
    2018-03-01 06:20

    Hi, I would like to see some specific details added that outline the operational considerations for RC/Migrator when handling already existing implicitly created LOB infrastructure such as AUX tables and Indexes when doing Alterations, Migrations and Compares. Currently I could not find any guidance about this subject. Thanks, Lucio

    1. Cindy Hamm
      2018-03-01 06:56

      Hi Lucio, I will talk to the development team and get back to you this request. Thanks, Cindy Hamm.