Migrate non-partitioned tables to partitioned tables for DB2 on z/OS

IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Administrator's Guide > Manage historical data > Tivoli Data Warehouse range partition migrations
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2

Migrate non-partitioned tables to partitioned tables for DB2 on z/OS

Use the following steps to migrate non-partitioned tables if you are using DB2 on z/OS.
Review the Prerequisites and best practices. You must ensure you have enough disk space.
You must have DB2 on z/OS V9 or later to use this procedure.
The schema publication tool can only be executed on a distributed platform, such as UNIX or Windows. The scripts generated must be executed from a distributed platform with a DB2 client connected to the remote DB2 z/OS database.
The generated migration scripts must be executed from a DB2 client.
The Tivoli Data Warehouse user must have one or more of the following privileges in order to execute the tdw_migrate_setup.sql script:

Ownership and EXECUTE privilege for the packages, DSNADMJS and DSNADMJF, used in the setup script

SYSADM authority

PACKADM authority for the package collection

Daemon authority
If the BPX.DAEMON is active, the stored procedures loaded into an address space must be defined to the RACF program control. Otherwise, the following error is returned: EDC5139I Operation not permitted. For detailed information about this issue, see APAR II13698 in the IBM Support Portal.
The privileges required for migration can be revoked after all the desired migrations are complete.
You must define the stored procedures to DB2 using the DSNTIJSG sample installation job, then ensure that all stored procedures are defined to RACF program control. Additionally, you must define the necessary application environment in WLM to run these stored procedures, and also specify a WLMENV value. For more information on defining the stored procedures to DB2, see the DB2 for z/OS Installation and Migration Guide and DB2 for z/OS Administration Guide for DB2 9 or later, in the DB2 for z/OS Information Center.

The migration of non-partitioned tables to partitioned tables in a Tivoli Data Warehouse DB2 on z/OS database uses a stored procedure generated by the schema publication tool. The stored procedure itself uses a JCL job which uses the DB2 LOAD utility. A JCL job is created and submitted for each table that is being migrated.

Procedure
Configure the Summarization and Pruning agent to partition tables. For detailed steps, see "Specifying range partitioned tables for the Summarization and Pruning Agent" in the IBM Tivoli Monitoring Installation and Setup Guide.

Stop all Warehouse Proxy agent and Summarization and Pruning agent instances.

Backup the Tivoli Data Warehouse database.
Catalog the z/OS database using the following commands:
db2 catalog tcpip node <node_Name> remote <DB_server_hostname> 
    server <port_number> ostype OS390
db2 catalog dcs database <db_name> as <db_name>
db2 catalog db <database_name_on_server> as <alias_on_client_database_name> 
    at node <node_Name> authentication dcs
Edit the schema publication tool response file.
Open the response file:

install_dir\TMAITM6\tdwschema.rsp for 32-bit Windows systems

install_dir\TMAITM6_x64\tdwschema.rsp for 64-bit Windows systems

install_dir/arch/bin/tdwschema.rsp
Configure the following variables:
KSY_PRODUCT_SELECT = migrate
KSY_PRODUCT_FILTER = list of products to migrate
An optional filter to indicate that only certain specific products are included. (If you do not specify a filter, all products in the specified category are included by default.) Specify the three-letter product codes of the products you want to include, separated by commas. You can find these codes using the tacmd histListProduct command (for more information, see the IBM Tivoli Monitoring Command Reference).
KSY_TABLE_FILTER = list of tables to migrate
An optional filter to indicate only specific tables. This filter can be used in addition to the KSY_PRODUCT_FILTER variable. Use the following command to get the list of tables that are available for a given product. Replace each space in the attribute group name with an underscore character. For a list of table names, use the following command:
tacmd histListAttributeGroups -t <productcode>
KSY_SUMMARIZATION_SELECTION = list of aggregation periods to migrate
An optional filter that can be used in addition to the KSY_PRODUCT_FILTER and KSY_TABLE_FILTER variables. This variable has an additional option when the migrate mode is used. The R option allows you to migrate the detailed tables. Other options are as follows:

R: Migrate detailed tables only

H: Hourly

D: Daily

W: Weekly

M: Monthly

Q: Quarterly

Y: Yearly
Filters can be combined. For example, to migrate the detail, hourly, and daily tables for the Windows OS agent:
KSY_PRODUCT=KNT
KSY_SUMMARIZATION_SELECTION=R,H,D
KSY_SQL_OUTPUT_FILE_PATH = optional file path for SQL output
An optional path to the directory where the generated SQL files are to be written. If you do not include this keyword, the current working directory is used.
For more details and the complete syntax for each variable, refer to the comments in the response file.
If you have not already, export the CANDLEHOME variable. Execute the following commands:
set CANDLE_HOME=install_dir
CANDLEHOME=/install_dir
export CANDLEHOME
Ensure the Tivoli Enterprise Portal Server is started.

Run the schema publication tool script to generate the scripts required for migration.

install_dir\TMAITM6\tdwschema.bat for 32-bit Windows systems

install_dir\TMAITM6_x64\tdwschema.bat for 64-bit Windows systems

install_dir/arch/bin/tdwschema.sh
The following scripts are generated for migration: tdw_migrate_setup.sql, tdw_migrate_step1.sql, and tdw_migrate_step2.sql.
Execute the tdw_migrate_setup.sql script and view the results to ensure it executed successfully.
db2 -td# -f tdw_migrate_setup.sql
Use the tdw_migrate_setup.sql script only once, even when migrating in batches. Executing this script more than once breaks the ability of the migration process to restart if an error occurs. This script contains drop statements that might fail if the objects do not already exist. Do not consider these failures as errors, they can be ignored. The expected failures might return the following messages: DB21034E and SQL0204N.
In the tdw_migrate_step1.sql script update the INSERT INTO WAREHOUSE_MIGRATION_CONFIG statement with the following information:

Specify the user ID and password required to execute the stored migration procedure. You can specify NULL for the user ID and password in the following circumstances:

The operating system is z/OS Version 1 Release 13 or later, and the authorization ID that is associated with the stored procedure address space has daemon authority.

The operating system is z/OS Version 1 Release 13 or later, and the authorization ID that is associated with the stored procedure address space does not have daemon authority but is authorized to the BPX.SRV.userid SURROGAT class profile, where useridis the authorization ID of the stored procedure. In this case, install APAR OA36062. For more information see the DB2 for z/OS Administration Guide.

Specify the JCL prefix library where the system LOAD and UNLOAD utilities are located.
Execute the tdw_migrate_step1.sql script and view the results to ensure it executed successfully.
db2 -tf tdw_migrate_step1.sql
If any errors occur after this script is executed, the errors must be resolved before running the tdw_migrate_step2.sql script. Continue to re-execute this script until all errors are resolved.
The following return codes apply:

-5: Invalid system name specified

-4: Invalid job class specified

-3: Prefix library is null

-2: Table already partitioned

-1: Invalid parameter passed

0: No errors occurred

1: Rename source table failed

2: Create partitioned table failed

3: Create or submit migrate JCL job failed

4: Query migrate JCL job status failed

5: Fetch migrate JCL job output failed

7: Load failed

8: Rename source table failed
Return code 6 is intentionally left blank.
Execute the tdw_migrate_step2.sql script and view the results to ensure it executed successfully.
db2 -tf tdw_migrate_step2.sql
You can execute this script multiple times to resolve any errors. This script has no effect on tables that did not migrate successfully.
If you are migrating in batches, the tdw_migrate_step1.sql and tdw_migrate_step2.sql scripts are executed for each batch.
When the tdw_migrate_step2.sql script is executed, the rows from the WAREHOUSE_MIGRATION_CONFIG, WAREHOUSE_JCLJOB_MIGRATION_STATUS, and WAREHOUSE_JCLJOB_OUTPUT table are deleted.
Backup the database. You must complete this step since the load utility was used in a non-recoverable mode to improve migration performance. Migrated tables cannot be restored from a backup until a new backup is made.
Results
The tables you specified are now partitioned, and the source tables have been deleted.

What to do next
If any errors occurred during the migration, review the WAREHOUSE_MIGRATION_STATUS, WAREHOUSE_JCLJOB_MIGRATION_STATUS, and WAREHOUSE_JCLJOB_OUTPUT tables. For detailed information, see the IBM Tivoli Monitoring Troubleshooting Guide.

Parent topic:
Tivoli Data Warehouse range partition migrations

+
Search Tips | Advanced Search