A fix is available
APAR status
Closed as new function.
Error description
New Function DSNTIJXA/DSNTXTA gets SQLCODE -670 when attempting to alter DSN_FUNCTION_TABLE column FUNCTION_TEXT - If the table space has a 4-KB page size then the SQLCODE -670 error occurs when DSNTXTA attempts to ALTER ADD the FUNCTION_TEXT column to DSN_FUNCTION_TABLE. DB2MIGV10/K
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All users of DB2 EXPLAIN tables in DB2 UDB * * for z/OS Version 8 and DB2 Version 9.1 for * * z/OS are affected by this change. * **************************************************************** * PROBLEM DESCRIPTION: Use of EBCDIC EXPLAIN tables and of * * EXPLAIN tables in a previous-release * * format is deprecated in DB2 V8 and V9. * * . * * Procedures and programs are provided to * * assist with: * * - Putting DB2 explain tables into * * current release format * * - Migrating EBCDIC explain tables * * to Unicode * **************************************************************** * RECOMMENDATION: * **************************************************************** Typically, each new release of DB2 introduces new columns or modifies existing columns in the EXPLAIN tables. DB2 has traditionally honored EXPLAIN tables in previous release formats, but it is best practice to use the current-release format in order to gather maximum benefit from your EXPLAIN data. . In addition, EXPLAIN tables were traditionally created in the EBCDIC encoding scheme because data in the DB2 catalog was EBCDIC encoded. Beginning with DB2 V8, almost all catalog data is Unicode encoded, and it is therefore increasingly beneficial to store EXPLAIN data in Unicode tables. . This APAR deprecates use of EBCDIC EXPLAIN tables, and use of EXPLAIN tables in previous release formats. Beginning in DB2 V10 conversion mode, use of EXPLAIN tables in DB2 V7 or a prior-version format will fail with SQLCODE -20008, and use of EBCDIC EXPLAIN tables and EXPLAIN tables in DB2 V8 or V9 format will result in SQLCODE +20520. It is recommended that you begin to identify non-compliant tables and eliminate or convert them. To facilitate this task, this APAR provides the following: - Queries for discovering non-compliant tables. In DB2 V8 and V9, these queries are added to job DSNTIJPM. In DB2 V8 only, they are added to job DSNTIJP9. - A new sample job, DSNTIJXA, that drives a DB2 REXX exec called DSNTXTA to alter explain tables into current-version format. You can specify to convert all explain tables in DB2 or you can limit the conversion to a particular schema (creator). - Another new sample job, DSNTIJXB, that drives a DB2 REXX exec called DSNTXTB to generate statements for migrating data from EBCDIC explain tables in a specified schema. DSNTXTB produces two equivalent types statements: - Control statements for processing by the DB2 Utilities cross loader (EXEC SQL) - SQL statements for processing by SPUFI or a similar dynamic SQL tool. These statements should not be processed before they are inspected and validated by the user. - A third new sample job, DSNTIJXC, that shows how to process the statements generated by running DSNTIJXB. In general, use this process (specific details follow): - Identify non-compliant explain tables. Reports are provided in job DSNTIJPM (and in DSNTIJP9 in V8 only) that identify all schemas having one or more such tables. - Bring all explain tables into current release format. You can use job DSNTIJXA to bring all explain tables in a specified schema into current release format. To convert all non-compliant explain tables regardless of the schema, specify an asterisk as the schema. - Migrate all EBCDIC-encoded explain tables to Unicode. This is a two step process: 1. Use job DSNTIJXB to generate DB2 cross loader control statements (and equivalent SQL statements) that can be used to copy all EBCDIC explain tables in a specified schema ID to Unicode equivalents. All explain tables belonging to the specified schema must first be in the current release format. 2. After examing the control statements generated by DSNTIJXB, use job DSNTIJXC to process them. DSNTIJXC assumes that you have purchased the DB2 Utilities Suite. If you have not, you can use the equivalent SQL generated by DSNTIJXB. The process works as follows for each table: a. RENAME TABLE to append '_EBCDIC' to the EBCDIC explain table. Renaming the EBCDIC table makes its original name available to its Unicode replacement. b. CREATE TABLE to create the explain table as a Unicode table c. CREATE AUX TABLE (if the table is a LOB table) d. CREATE INDEX for each index on the EBCDIC table. To avoid name conflicts, the new indexes are prefixed with DSNU. If the name already starts with DSN then a 'U' is inserted at the fourth position. Examples: PLAN_TABLE_IDX1 -> DSNU_PLAN_TABLE_IDX1 DSN_STATEMNT_TABLE_IDX1 -> DSNU_STATEMNT_TABLE_IDX1 e. DECLARE CURSOR on the EBCDIC table to extract the data f. LOAD DATA INTO the Unicode table, using the cursor on the _EBCDIC table 3. After converting an EXPLAIN table from EBCDIC to Unicode, check for joins to this table. When you convert EXPLAIN tables to UNICODE, their applications which join with EXPLAIN tables can have different results due to the CCSID conversion. For more information, see 'Objects with different CCSIDs in the same SQL statement' in the DB2 Internationalization Guide. Repeat these steps until all explain tables are Unicode encoded and in current release format. ---------------------------------------------------------------- Introducing Queries for discovering non-compliant tables ---------------------------------------------------------------- Two queries are added to the V8 premigration checkout job (shipped as DSNTIJPM in V8) and the V9 premigration checkout jobs (shipped as DSNTIJPM in V9 and as DSNTIJP9 in V8). - The first query identifies EBCDIC-encoded EXPLAIN tables that need to be migrated to Unicode. - The second query identifies EXPLAIN tables that are either not in V8 format (if you are preparing to migrate to DB2 V8) or not in V9 format (if you are preparing to migrate to DB2 V9). Both queries are lengthy and release-dependent, and therefore are not illustrated in this APAR description. During premigration, you should note any non-compliant tables but wait to correct them until after you have migrated DB2 to the new release and stabilized in new-function mode. Note that you can use these queries anytime after migration to identify non-compliant EXPLAIN tables. ---------------------------------------------------------------- Introducing job DSNTIJXA and REXX exec DSNTXTA ---------------------------------------------------------------- DSNTXTA alters all explain tables under a specified schema name into current-release format. The job prolog of DSNTIJXA explains how to customize it for use on your system. If you have identified non-compliant tables during preparations to migrate to a new release of DB2, you should note them but wait to correct them until after you have completed migration and stabilized DB2 in new-function mode. Job DSNTIJXA contains a single job step that calls DB2 REXX exec DSNTXTA, which accepts three parameters: The DB2 SSID, an authorization ID, and a schema name. If the schema name is an asterisk, DSNTXTA will locate and convert all explain tables under all schemas. Example call: DSNTXTA + DSN SYSADM DSN8810 DSNTXTA analyzes each explain table in a specified schema. If no tables are found, processing terminates with the following message: DSNT090I DSNTXTA REQUEST CANCELLED BECAUSE THERE ARE NO EXPLAIN TABLES IN SCHEMA schema-name When a candidate table is found, the addition and alteration of columns is summarized in the following message: DSNT091I DSNTXTA HAS ALTERED EXPLAIN TABLE schema-name.- table-name. COLUMN column-name-1 WAS description-of-change-1. COLUMN column-name-2 WAS description-of-change-2. ... COLUMN column-name-n WAS description-of-change-n. When alteration of a table cannot be completed because of an unknown or unmanageable format, the following message is written and processing continues to the next table: DSNT092I DSNTXTA DID NOT COMPLETE PROCESSING OF schema-name.table-name BECAUSE reason. When all tables in the schema have been processed, the following summary message is written: DSNT093I DSNTXTA PROCESSING COMPLETE. - SCHEMAS EXAMINED : total-schemas - TABLES EXAMINED : total-tables - TABLES ALTERED : total-altered - TOTAL WARNINGS : total-warnings - TOTAL ERRORS : total-errors Special considerations: - In the V8 format of DSN_QUERY_TABLE, HASHKEY and HAS_PRED are columns 8 and 9, whereas in the V9 format they are columns 9 and 10. Therefore, DSNTXTA cannot alter DSN_QUERY_TABLE from V8 format to a newer format. For each such case, DSNTXTA indicates with warning message DSNT092I that you need to use the DSNTXTB process (see below) to migrate the indicated DSN_QUERY_TABLE to a 10-column (V9) format. DSNT092I DSNTXTA DID NOT COMPLETE PROCESSING OF schema-name.DSN_QUERY_TABLE BECAUSE IT CANNOT BE ALTERED TO A 10-COLUMN TABLE - In V8 and V9, prior to APAR PK65772, the names of columns 13 - 16 of DSN_PTASK_TABLE contained the hashmark symbol which is a variant EBCDIC character. In V9 NFM, DSNTXTA checks for and renames these columns if necessary. In V8 and V9 CM, RENAME COLUMN is not available so DSNTXTA indicates with warning message DSNT092I that you need to use the DSNTXTB process to migrate the indicated DSN_PTASK_TABLE to a DSN_PTASK_TABLE that uses the column name corrections introduced by PK65772: DSNT092I DSNTXTA DID NOT COMPLETE PROCESSING OF schema-name.DSN_PTASK_TABLE BECAUSE ONE OR MORE COLUMN NAMES CONTAINS A VARIANT EBCDIC CHARACTER ---------------------------------------------------------------- Introducing job DSNTIJXB and REXX exec DSNTXTB ---------------------------------------------------------------- DSNTXTB generates DB2 Utilities cross loader control statements for creating, indexing, and loading Unicode-encoded copies of all EBCDIC EXPLAIN tables in a specified schema. A parallel set of DDL statements is generated for use by customers who do not have a license for the DB2 Utilities Suite. The job prolog of DSNTIJXB explains how to customize it for use on your system. If you have identified non-compliant tables during preparations to migrate to a new release of DB2, you should note them but wait to correct them until after you have completed migration and stabilized DB2 in new-function mode. Job DSNTIJXB contains two job steps. The first deletes data sets created by the job. The second job step calls DB2 REXX DSNTXTB, which accepts these parameters: - The DB2 SSID to connect to - The authorization ID to use - The schema name of the explain tables to be converted - The name of the target database - The name of the target 4KB page table space - The name of the target 8KB page table space - The name of the target 16KB page table space - The name of the target 8KB page LOB TS - The name of the target 32KB page LOB TS Example call: DSNTXTB + DSN + SYSADM + SCHEMA + TARGETDB + TS4K + TS8K + TS16K + LOBTS8K + LOBTS32K DSNTXTB allocates these output DDs: - XLDOUT: Destination for generated DB2 cross loader control statements (RECFM=FB,LRECL=80) - DDLOUT: Destination for generated SQL statements (RECFM=FB,LRECL=80) C Job DSNTIJXB does not process the generated control statements. These statements need to be inspected by the user, and then processed by the DB2 cross-loader function (for example by using sample job DSNTIJCX) to migrate the tables to Unicode. All explain tables must be in current release format, except that in V9, DSN_QUERY_TABLE can be in V8 format. DSNTXTB also generates a control statement to rename each EBCDIC table so that the name can be reused by the Unicode table. The renaming consists of adding the suffix _EBCDIC to the current table name. DSNTXTB also generates control statements for creating the same indexes on Unicode tables as are defined on the EBCDIC tables. To avoid name conflicts, the new indexes are prefixed with DSNU. If the name already starts with DSN then a 'U' is inserted at the fourth position. Examples: PLAN_TABLE_IDX1 -> DSNU_PLAN_TABLE_IDX1 DSN_STATEMNT_TABLE_IDX1 -> DSNU_STATEMNT_TABLE_IDX1 When no candidate tables exist under the specified schema, DSNTXTB terminates with the following warning message: DSNT094I: DSNTXTB REQUEST CANCELLED BECAUSE THERE ARE NO EBCDIC EXPLAIN TABLES IN SCHEMA schema-name When a candidate table is not in current release format, DSNTXTB terminates with the following message: DSNT095I: DSNTXTB REQUEST CANCELLED BECAUSE TABLE schema-name.table-name IS NOT IN THE REQUIRED FORMAT AT COLUMN NUMBER column-number. - EXPECTED: expected-format - FOUND : actual-format. When the target database does not exist, DSNTXTB generates a statement to create it. If it exists but is not a default Unicode database, DSNTXTB writes the following warning if the target database: DSNT096I: WARNING: THE SPECIFIED TARGET DATABASE, database-name, IS NOT A UNICODE DATABASE When a target table space exists but is not of the required type, processing terminates with the following message: DSNT097I: DSNTXTB REQUEST CANCELLED BECAUSE THE SPECIFIED TARGET required-type TABLE SPACE, database-name.- table-space-name, IS AN EXISTING actual-type TABLE SPACE When a target table space of the required type already exists, DSNTXTA writes the following message once only when the first candidate explain table is discovered that will reside in it: DSNT098I: DSNTXTB HAS GENERATED STATEMENTS TO CREATE UNICODE EXPLAIN TABLES FOR THE SPECIFIED SCHEMA IN AN EXISTING TARGET TABLE SPACE, database-name.- table-space-name For each candidate table, the following message is written: DSNT099I: DSNTXTB HAS GENERATED STATEMENTS FOR MIGRATING TABLE schema-name.table-name TO conversion-type and the following are generated as DB2 cross loader control statements and written to the XLDOUT DD: - RENAME TABLE to append '_EBCDIC' to the table name. The EBCDIC table is renamed to make its original name available to its Unicode replacement. - CREATE TABLE to create the table as a Unicode table - CREATE AUX TABLE (if the table is a LOB table) - CREATE INDEX for each index on the EBCDIC table - DECLARE CURSOR on the EBCDIC table to extract the data there - LOAD DATA INTO the Unicode table, using the cursor on the _EBCDIC table Parallel SQL statements are written to the DDLOUT DD, except that each DECLARE CURSOR and LOAD DATA INTO statement pair is replaced by INSERT INTO unicode-explain-table-name SELECT FROM ebcdic-explain-table name ---------------------------------------------------------------- Introducing job DSNTIJXC ---------------------------------------------------------------- Job DSNTIJXC contains a single job step that invokes the DB2 Utilities cross loader. It can be used to process statements generated by running job DSNTIJXB. The job prolog of DSNTIJXC explains how to customize it for use on your system. If you have identified non-compliant tables during preparations to migrate to a new release of DB2, you should note them but wait to correct them until after you have completed migration and stabilized DB2 in new-function mode. The cross loader is a licensed DB2 Utility. If the cross loader is not installed on your DB2, use SPUFI or a similar dynamic SQL tool to process the SQL statements generated by running job DSNTIJXB. After converting an EXPLAIN table from EBCDIC to Unicode, check for joins to this table. When you convert EXPLAIN tables to UNICODE, their applications which join with CSID EXPLAIN tables can have different results due to the CCSID conversion. For more information, see 'Objects with 2 different CCSIDs in the same SQL statement' in the DB2 Internationalization Guide.
Problem conclusion
Temporary fix
Comments
APAR Information
APAR number
PK85068
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-04-21
Closed date
2009-12-28
Last modified date
2011-09-15
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK53249 UK53250
Modules/Macros
DSNTIJPM DSNTIJP9 DSNTIJXA DSNTIJXB DSNTIJXC DSNTXTA DSNTXTB
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
15 September 2011