PK30961: SQLPA REPORTS SUCCESSFUL ATTEMPT TO CREATE A CLUSTERING INDEX, BUT DB2 REPORTS IT REALLY FAILED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLPA user attempts to create a clustering index for a table via
  the SQLPA ISPF panels, starting on panelid ANLNEW1. He specifies
  CLUSTER which would cause the new index to be a CLUSTERING
  INDEX. SQPLA reports the index was successfully created, but
  DB2 actually failed the create with SQLCODE -623:
  DSNT408I SQLCODE = -623, ERROR: A CLUSTERING INDEX ALREADY
  EXISTS ON
  TABLE table_name
  DSNT418I SQLSTATE = 55012 SQLSTATE RETURN CODE
  .
  SQLPA should accurately report the failure.
  ADDITIONAL SYMPTOMS:
  1) SQLPA in the ISPF 'WHAT IF' dialogs uses PF3 as a GO
     key. This apar will set PF3 to EXIT in keeping with
     general ISPF dialog standards.
  2) The SQLPA User's Guide defines message ANL1033W, but when
     the message is issued it is as ANL1033I. This apar will
     change the message to match the User's Guide as a WARNING
     message, not informational.
  3) Change the return code from 0 to 4 when SQL PA has reached or
     exceeded the threshold for the number of DBRM members (2730)
     it can process in one run.
  4) Problem fixed in ANLWIF31 program to include a return
     code when the program completes to allow the panels to
     display a message when a problem is encountered.
  5) MSGANL8004E ERROR ENCOUNTERED:     12 SQLCODE:      0
     ERROR ENCOUNTERED DURING "OUTSTAT" OUTPUT PROCESSING
     POSSIBLE CAUSES INCLUDE NO DDNAME "OUTSTAT", BAD DCB
     FOR FILE OR ERRORS ENCOUNTERED DURING WRITE TO FILE.
     This message is issued if a non-existent DB2 SSID is
     specified for PARM1 SUBSYST or if the OUTSTAT DD card
     points to a PDS with no members defined. In each case
     the error itself is valid, but the message is issued over
     and over in a loop. 33069
  6) Corrects host variable substitution with parameter maker if
     no intervening space was found between the semicolon and H,
     when the SQL source is from SQLJ DBRM's.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 SQL Performance Analyzer.       *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLPA user attempts to create a         *
  *                      clustering index for a table via the    *
  *                      SQLPA ISPF panels, starting on panelid  *
  *                      ANLNEW1. He specifies CLUSTER which     *
  *                      would cause the new index to be a       *
  *                      CLUSTERING INDEX. SQPLA reports the     *
  *                      index was successfully created, but DB2 *
  *                      actually failed the create with SQLCODE *
  *                      -623: DSNT408I SQLCODE = -623, ERROR:   *
  *                      A CLUSTERING INDEX ALREADY EXISTS ON    *
  *                      TABLE table_name DSNT418I SQLSTATE =    *
  *                      55012 SQLSTATE RETURN CODE. SQLPA       *
  *                      should accurately report the failure.   *
  *                      ADDITIONAL SYMPTOMS:                    *
  *                      1. SQLPA in the ISPF 'WHAT IF' dialogs  *
  *                      uses PF3 as a GO key. This apar will    *
  *                      set PF3 to EXIT in keeping with         *
  *                      general ISPF dialog standards.          *
  *                      2. The SQLPA User's Guide defines       *
  *                      message ANL1033W, but when the message  *
  *                      is issued it is as ANL1033I. This apar  *
  *                      will change the message to match the    *
  *                      User's Guide as a WARNING message, not  *
  *                      informational.                          *
  *                      3. Change the return code from 0 to 4   *
  *                      when SQL PA has reached or exceeded     *
  *                      the threshold for the number of DBRM    *
  *                      members (2730) it can process in one    *
  *                      run.                                    *
  *                      4. Problem fixed in ANLWIF31 program    *
  *                      to include a return code when the       *
  *                      program completes to allow the panels   *
  *                      to display a message when a problem is  *
  *                      encountered.                            *
  *                      5. MSGANL8004E ERROR ENCOUNTERED:       *
  *                      12 SQLCODE:  0  \ ERROR ENCOUNTERED     *
  *                      DURING "OUTSTAT" OUTPUT PROCESSING      *
  *                      POSSIBLE CAUSES INCLUDE NO DDNAME       *
  *                      "OUTSTAT", BAD DCB FOR FILE OR ERRORS   *
  *                      ENCOUNTERED DURING WRITE TO FILE.       *
  *                      This message is issued if a             *
  *                      non-existent DB2 SSID is specified for  *
  *                      PARM1 SUBSYST or if the OUTSTAT DD      *
  *                      card points to a PDS with no members    *
  *                      defined. In each case the error itself  *
  *                      is valid, but the message is issued     *
  *                      over and over in a loop. 33069          *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  Code has been changed to correct this condition.
  

Problem conclusion

• APPLY the PTF.
  

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:

  UK19527

Modules/Macros

• ANLCAT31 ANLIDX1  ANLIDX2  ANLPGM3N ANLPGM31
  ANLPRC3C ANLPRC3R ANLPRE3C ANLPRE3R ANLQMF31 ANLREST  ANLTAB1
  ANLTAB2  ANLWIF31
  

Fix information

• Fixed component name

  DB2 SQL PERF AN

• Fixed component ID

  5697F5701

Applicable component levels

• R310 PSY UK19527

     UP06/11/10 P F611

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.