PH12242: ADMIN_INFO_SQL: SQLCODE -199 FROM CREATE INDEX DDL WITH BUSINESS_TIME WITHOUT OVERLAPS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE = -199 from CREATE  INDEX DDL generated by
  ADMIN_INFO_SQL, caused by a missing comma between the last
  column of the index key and  BUSINESS_TIME WITHOUT OVERLAPS.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 for z/OS and Db2 12               *
  *                 for z/OS customers who utilize               *
  *                 IBM Service, which uses the                  *
  *                 Db2-supplied stored procedure                *
  *                 SYSPROC.ADMIN_INFO_SQL or the                *
  *                 batch program DSNADMSB to                    *
  *                 collect Db2 environmental                    *
  *                 service data either directly                 *
  *                 or via any of the following                  *
  *                 tools:                                       *
  *                 o Query Environment Capture                  *
  *                   feature of Data Studio                     *
  *                 o Query Environment Capture                  *
  *                   feature of Optim Query                     *
  *                   Workload Tuner                             *
  *                 o Capture Query Environment                  *
  *                   feature of Data Server                     *
  *                   Manager                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: The CREATE INDEX DDL generated by       *
  *                      SYSPROC.ADMIN_INFO_SQL and DSNADMSB     *
  *                      is missing a comma before the clause    *
  *                      BUSINESS_TIME WITHOUT OVERLAPS,         *
  *                      causing the DDL to fail with SQLCODE    *
  *                      -199.  For example:                     *
  *                                                              *
  *                       CREATE UNIQUE INDEX ixcreator.ixname   *
  *                         ON tbcreator.tbname (                *
  *                          "COL1" ASC                          *
  *                          BUSINESS_TIME WITHOUT OVERLAPS)     *
  *                         ...                                  *
  *                         ...                                  *
  *                         ...                                  *
  ****************************************************************
  * RECOMMENDATION: APPLY PTF                                    *
  ****************************************************************
  The CREATE INDEX DDL generated by SYSPROC.ADMIN_INFO_SQL and
  DSNADMSB is missing a comma before the clause BUSINESS_TIME
  WITHOUT OVERLAPS, causing the DDL to fail with SQLCODE
  -199.
  

Problem conclusion

• SYSPROC.ADMIN_INFO_SQL and DSNADMSB are modified to add a
  comma before the clause BUSINESS_TIME WITHOUT OVERLAPS in
  the generated CREATE INDEX DDL. For example:
  
   CREATE UNIQUE INDEX ixcreator.ixname
     ON tbcreator.tbname (
      "COL1" ASC
     ,BUSINESS_TIME WITHOUT OVERLAPS)
     ...
     ...
     ...
  

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:

  UI64489 UI64493

Modules/Macros

•    DSN@DMSS DSNADMSS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI64493

     UP19/08/14 P F908

• RC10 PSY UI64489

     UP19/08/14 P F908

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.