PM63340: ERRONEOUS SQL IN CREATE, DROP, ALTER DATASETS 'WITH DEFAULT 'N':'

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Object Comparison Tool for z/OS created erroneous DDL
  in the work data set GOCALTR, GOCCREA, GOCDROP which are
  are described in Table 2. 'Workdata set descriptions' in
  Chapter 4. 'Creating DB2 Object Comparison Tool batch jobs'.
  
  An example of the erroneous DDL is shown below after the valid
  COMMIT statement:
  
  COMMIT ;
        WITH DEFAULT 'N' ;
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Object Comparison Tool      *
  *                 for z/OS who compare tables, and as a        *
  *                 result, ALTER TABLE statements are           *
  *                 generated in a Work Statement List (WSL)     *
  *                 or in the work data sets GOCCREA and/or      *
  *                 GOCDROP.                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: The DB2 Object Comparison Tool for      *
  *                      z/OS may create erroneous ALTER         *
  *                      TABLE statements in the APPLY job or    *
  *                      Work Statement List (WSL), or in the    *
  *                      work data sets GOCCREA and GOCDROP,     *
  *                      as described in Table 2.'Work data      *
  *                      set descriptions', Chapter 4.           *
  *                      'Creating DB2 Object Comparison         *
  *                      Tool batch jobs' of the DB2 Object      *
  *                      Comparison Tool User's Guide.           *
  *                                                              *
  *                      An example of the erroneous ALTER       *
  *                      TABLE statements that are generated     *
  *                      in the GOCDROP work data set is:        *
  *                                                              *
  *                      COMMIT ;                                *
  *                        WITH DEFAULT 'N'                      *
  *                                                              *
  *                      ALTER TABLE >tablename<                 *
  *                        DROP PRIMARY KEY  ;                   *
  *                                                              *
  *                      The WITH DEFAULT statement which        *
  *                      should be generated in the GOCCREA      *
  *                      work data set is incorrectly            *
  *                      generated in the GOCDROP work data      *
  *                      set, after the valid COMMIT             *
  *                      statement.                              *
  *                                                              *
  *                      When running the Work Statement List    *
  *                      (WSL) or the APPLY job, the user will   *
  *                      encounter a SQLCODE= -104 /             *
  *                      SQLCODEN104:                            *
  *                                                              *
  *                      ADB5044I *** INPUT STATEMENT:           *
  *                               WITH DEFAULT 'N' ;             *
  *                      sqlerror on WITH  command,              *
  *                      PREPARE    function                     *
  *                      DSNT408I SQLCODE = -104,                *
  *                      ERROR:  ILLEGAL SYMBOL "'N'".           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The DB2 Object Comparison Tool for z/OS may create erroneous
  ALTER TABLE statements in the APPLY job, WSL, or in the work
  data sets GOCCREA and GOCDROP as described in Table 2.'Work
  data set descriptions', Chapter 4. 'Creating DB2 Object
  Comparison Tool batch jobs' of the DB2 Object Comparison
  Tool User's Guide.
  
  An example of the erroneous ALTER TABLE statements that are
  generated in the GOCDROP work data set is:
  
  COMMIT ;
    WITH DEFAULT 'N'
  
  ALTER TABLE >tablename<
    DROP PRIMARY KEY  ;
  
  The WITH DEFAULT statement which should be generated in
  the GOCCREA work data set is incorrectly generated in the
  GOCDROP work data set after the valid COMMIT statement.
  
  When running the WSL or the APPLY job, the user will
  encounter a SQLCODE=-104 / SQLCODEN104 error:
  
  ADB5044I *** INPUT STATEMENT:
           WITH DEFAULT 'N' ;
  sqlerror on WITH       command, PREPARE    function
  DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "'N'".
  
  
  Users may see similar erroneous ALTER TABLE statements
  in the APPLY job under the following circumstances:
  
  1. When comparing tables and the changes of the table are
  applied via ALTER TABLE statements.
  2. The owner and creator of the table have different
  values, hence a SET CURRENT SCHEMA statement is generated
  before the ALTER TABLE statements.
  3. When generated ALTER TABLE statements include:  ALTER
  TABLE >tablename< RENAME COLUMN statement or ALTER TABLE
  >tablename< DROP statement.
  

Problem conclusion

• Problem has been resolved.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK79671

Modules/Macros

•    ADB2CMT
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK79671

     UP12/06/21 P F206

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.