Errors can occur when migrating your non-partitioned tables
to partitioned tables.
Error messages are displayed in the trace file if any configuration
errors are detected in filter variables such as KSY_PRODUCT_FILTER,
KSY_TABLE_FILTER, or KSY_SUMMARIZATION_SELECTION. Such messages indicate
which table or product do not exist in the application support or
if the summarization selection is incorrect.
If the filters are configured correctly but errors are detected
at the database level, such as tables already partitioned or tables
do not exist in the database, the migration tool generates only the
SQL statements for the tables that can be migrated and error messages
are logged in the trace file for the tables that cannot be migrated.
When these errors occur, the following message appears on the standard
output:
Warning: One or more tables may not have migration steps generated.
See the log file for additional information.
When the migration tool has generated all the scripts successfully
with a warning, the following message appears on the standard output:
Tool requests completed, RC= 2
When the migration tool has generated all the scripts successfully
without warnings, the following message appears on the standard output:
Tool requests completed, RC= 0
Status tables
You can review the status
tables to help you troubleshoot your errors.
The tdw_migrate_setup.sql
script creates the following status tables:
- WAREHOUSE_MIGRATION_STATUS table for DB2 for Linux, UNIX, and
Windows
- This table provides the following information:
- The name of the table being migrated. This is the attribute group
name and short name.
- The current status of the migration.
- 0: Migrated successfully
- 1: Migration started; need to rename source table
- 2: Source renamed; need to create target table
- 3: Target partitioned table created; need to load data
- 4: Data loaded; need to rename source table
- The SQL code of the last statement executed.
- Number of rows read during the load operation.
- Number of rows skipped before the load operation started. This
information is returned for a single-partition database only.
- Number of rows loaded into the target table. This information
is returned for a single-partition database only.
- Number of rows that could not be loaded into the target table.
- Number of duplicate rows that were not loaded into the target
table. This information is returned for a single-partition database
only.
- Total number of rows processed. This is the number of rows successfully
loaded into the target table, plus the number of skipped and rejected
rows. This information is returned for a single-partition database
only.
- Number of rows distributed by all database distributing agents.
This information is returned for a multi-partition database only.
- Number of entries returned in the second result set for a multi-partitioned
database. This is the number of agent information entries produced
by the load operation. This information is returned for multi-partitioned
databases only.
- The SQL state of the last statement executed.
- The SQL required to retrieve the load utility messages.
- The SQL required to remove the load utility messages.
- Timestamp when the status row was created.
- Timestamp when the status row was last updated.
- Timestamp when step 1 started.
- Timestamp when step 1 successfully completed.
- Timestamp when step 2 started.
- Timestamp when step 2 successfully completed.
- Timestamp when step 3 started.
- Timestamp when step 3 successfully completed.
- Timestamp when step 4 started.
- Timestamp when step 4 successfully completed.
- WAREHOUSE_MIGRATION_STATUS for DB2 on z/OS
- This table provides the following information:
- The schema name of the table migrated.
- The name of the table being migrated. This is the attribute group
name.
- The short name of the table migrated.
- The job ID when a JCL job is used.
- The current status of the migration.
- 0: Migrated successfully
- 1: Migration started; need to rename source table
- 2: Source renamed; need to create target table
- 3: Target partitioned table created; need to create and submit
JCL job
- 4: JCL job to migrate data create and submitted; need to query
job status
- 5: JCL migrate job status available; if the job execute successfully,
need to fetch the job output
- 6: Job output fetched line by line; parse each line to find out
if migration executed successfully
- 7: JCL migrate job purged
- 8: Source table renamed
- Error message if anything failed.
- The last SQL statement that had an error.
- The SQL code of the last error.
- The SQL state of the last error.
- Timestamp when the status row was created.
- Timestamp when the status row was last updated.
- Timestamp when step 1, 2, 3, 4, 5, 6, or 7 started.
- Timestamp when step 1, 2, 3, 4, 5, 6, or 7 successfully completed.
- Timestamp when step 2 successfully completed.
- WAREHOUSE_JCLJOB_MIGRATION_STATUS for DB2 on z/OS
- This table is only created for DB2 on z/OS. This table provides
the following information:
- The JCL job ID.
- The JCL job administrator stored procedure that was used.
- The stored procedure return code.
- The time the stored procedure was executed.
- The JCL job status.
- The job error message.
- The job completion code.
- The job completion type.
- 0: No completion information is available
- 1: Job ended normally
- 2: Job ended by completion code
- 3: Job had a JCL error
- 4: Job was canceled
- 5: Job terminated abnormally
- 6: Converter terminated abnormally while processing the job
- 7: Job failed security checks
- 8: Job failed in end-of-memory
- The system abend code if an abnormal termination occurs.
- The user abend code if an abnormal termination occurs.
The tdw_migrate_step1.sql script provides the return
codes based on your database type. Refer to the IBM Tivoli Monitoring Administrator's
Guide for a complete
list of return codes.
Return codes 1, 2, and 4 for the tdw_migrate_step1.sql
script on DB2 for Linux, UNIX, and Windows
When migrating
a table on DB2 for Linux, UNIX, or Windows, you might receive return
codes 1, 2, or 4.
- 1: Indicates that the source table could not be renamed to MIGRATING_*.
- 2: Indicates that the partitioned table could not be created.
- 4: Indicates that the table MIGRATING_* could not be renamed
to DONE_*.
To determine the cause of each of these errors, note the
SQL code and SQL state values that are provided in the output when
the table is migrated. To get more information about an SQL code,
refer to the DB2 error code documentation or issue the db2
? <SQL CODE> command.
The SQL
code and SQL state values are also stored in the WAREHOUSE_MIGRATION_STATUS
table. To retrieve the values:
- Start the DB2 command line processor. Do not issue SQL from the
operating system's command line to avoid having to escape quotes in
SQL text.
- Connect to the Tivoli® Data Warehouse database
as the Tivoli Data Warehouse user:
connect to <TDW database> user <TDW user ID> using <password>
- Issue the SQL:
SELECT sqlcode, sqlstate FROM WAREHOUSE_MIGRATION_STATUS WHERE tablename =
'<name of table being migrated>'
For
example:
SELECT sqlcode, sqlstate FROM WAREHOUSE_MIGRATION_STATUS
WHERE tablename = 'NT_Process_64'
- Once the cause of the error is resolved, the migration script
tdw_migrate_step1.sql can be rerun.
Return code 3 for the tdw_migrate_step1.sql script
on DB2 for Linux, UNIX, and Windows
When migrating a table
on DB2 for Linux, UNIX, or Windows, a return code of 3 indicates the
load step has failed. This means there was a failure calling the LOAD
stored procedure in the tdw_migrate_step1.sql script for a given table.
The load step could fail for many different reasons including:
- The Tivoli Data Warehouse user
doesn't have sufficient privileges to run the load utility.
- Insufficient space in the migrated table's tablespace for the
data being loaded.
To determine why the load failed:
- Start the DB2 command line processor. Do not issue SQL from the
operating system's command line to avoid having to escape quotes in
SQL text.
- Connect to the Tivoli Data Warehouse database
as the Tivoli Data Warehouse user:
connect to <TDW database> user <TDW user ID> using <password>
- Issue the SQL:
SELECT msgretrieval FROM WAREHOUSE_MIGRATION_STATUS WHERE tablename
= '<name of table being migrated>'
For
example:
SELECT msgretrieval FROM WAREHOUSE_MIGRATION_STATUS
WHERE tablename = 'NT_Process_64'
Returns:
MSGRETRIEVAL
-------------------------------------------------------------------
SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('32727_ITMUSER'))
AS MSG
- Issue the SQL query that was returned in step 3. This displays
the messages from the DB2 LOAD utility.
In this example, there were
no errors in the load step of the migration:
SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('32727_ITMUSER'))
AS MSG
SQLCODE MSG
------- ----------------------------------------------------------
SQL3501W The table space(s) in which the table resides will not be
placed in backup pending state since forward recovery is disabled
for the database.
SQL1193I The utility is beginning to load data from the SQL statement
"SELECT * FROM ITMUSER."MIGRATING_NTPROCESS"".
SQL3500W The utility is beginning the "LOAD" phase at time
"01/14/2013 11:26:47.793424".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "96785" rows were
read from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "96785".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time
"01/14/2013 11:26:59.426438".
9 record(s) selected.
- Fix the problems mentioned in the load utility's message files
and re-execute the tdw_migrate_step1.sql script.