PK85068: EXPLAIN TABLE MIGRATION

A fix is available

Subscribe

You can track all active APARs for this component.

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

  • R810 PSY UK53249

       UP10/01/20 P F001

  • R910 PSY UK53250

       UP10/01/20 P F001

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

810

Reference #:

PK85068

Modified date:

2011-09-15

Translate my page

Machine Translation

Content navigation