Review the following operational considerations before you begin using the product:
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.
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:
You can enable parallel load and unload processing using Utility Profile Services or by specifying symbolic values.
To use symbolic values, see Symbolic Parameters.
Follow these steps:
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.
Parallel load and unload processing is enabled.
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:
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.
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).
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:
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.
We recommend that you review RMRTPE and make changes as necessary so that it addresses the requirements of your environment.
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:
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.
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 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.
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
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.
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:
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:
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.
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:
Run the REPAIR utility to rebuild the database on the target subsystem. The database and subsystem are identified in the message text.
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.
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:
All DB2 9 features are not detected. For example, the following unsupported DB2 9 features are not detected:
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:
When you are using materialized query tables (MQT), the following restrictions apply:
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.
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:
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.
Note the following XML support limitations:
Note the following LOB support limitations:
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.
The following restrictions apply when you create and alter tables with columns defined with the security label attribute (AS SECURITY LABEL):
The table must not have editproc, validproc, audit, obid, data capture, ccsid, volatile or notvolatile checked.
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.
To create a table with a security label column, specify L in the D column on the Table Create panel.
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:
You can specify the SET TERMINATOR option for migrate, compare, and alter strategies from the following panels:
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:
When DDL is generated, the following statements will be wrapped in a pair of --#SET TERMINATOR statements:
The first statement sets the overriding terminator. The second statement restores the termination character to the DB2 default (;).