PK11683: LONG RESPONSE TIMES AFTER CONVERSION TO R220.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• After migrating to R220 SQLPA has a slower response time.
  Additional Symptoms:
  1. CEE0813S Insufficient storage was available to satisfy a
     GET STORAGE (CEECZST).
  2. A correction to the ANLINIT clist to add error recovery if
     the user does not include a SUBVERS parm in SANLDATA.
  3. Add alternative static and dynamic SQL to ANLPGME7 to allow
     indexed access to catalog under V6, V7 and V8C, where larger
     sizes were creating a mismatch and access paths were
     degenerating to TSCANs - uses SUBVERS parm in ANLCNTL to tell
     program when to switch to large sizes again (for V8 enabling
     NFM), but default is SMALLER sizes.
  4. A major reduction in storage requirements for all programs by
     2MB (ANLPGME8, ANLPGM22, ANLQMF22, ANPROCC and ANLPROCR).
  5. Corrects HLQ inserts in front of unqualified table names when
     USEPLAN is set, plus QUALIFY is blanks, and user has
     Synonyms.
  6. 340 size error. The symptoms include:
     ANL3034E A SIZE ERROR WAS DETECTED DURING INTERNAL
     CALCULATIONS CHECK STATISTICS AND CORRECT
     ANL3099E A TERMINAL PROCESSING ERROR HAS OCCURRED
     PLI ERROR CODE =    340
     ANL ERROR CODE =   3034
      & RETURN CODE =     16
      SQL PA  PROGRAM TERMINATES WITH UNRECOVERABLE ERROR
     POSSIBLE CAUSE:
     VARIABLE SIZE ERROR - CHECK CATALOG STATS, RUNSTATS
  

Local fix

• Circumvention for symptom number 2:
  /* PTF2203:  OBTAIN THE "SUBVERS" PARM FROM SANLDATA PARAMETERS
  */
  
   OPENFILE ANLCNTL INPUT
    DO WHILE &LASTCC ¬= 400
      GETFILE ANLCNTL
      SET &MYPARM = &ANLCNTL
       IF &STR(&SUBSTR(1:7,&MYPARM.)) = &STR(SUBVERS) THEN DO
         SET SVERS = &STR(&SUBSTR(9:13,&MYPARM.))
         ISPEXEC VPUT (SVERS) PROFILE
         GOTO CLOSET
       END
    END
   CLOSET: +
  CLOSFILE ANLCNTL             /* END PTF2203 MOD */
  
   ALLOC F(ANLPARM) NEW SP(1,1) BL(20) DELETE REUS +
          REL RECFM(F,B) LRECL(80) BLKSIZE(9040) DSORG(PS)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 SQL Performance Analyzer.       *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. Clist ANLINIT now contains           *
  *                       additional error routine for users     *
  *                       who do NOT specify the SUBVERS parm    *
  *                       (introduced in PTF2203) for            *
  *                       ANL220.SANLDATA members. Now users     *
  *                       may choose to employ, or leave out,    *
  *                       this parameter and it will no longer   *
  *                       affect any program operations under    *
  *                       TSO.                                   *
  *                      2. Reduced run time storage             *
  *                       requirements for all SQL PA programs   *
  *                       (ANLPGME7, ANLPGME8, ANLQMF22,         *
  *                        ANLPROCC, ANLPROCR) by 2 MB each.     *
  *                       (PMR 78137)                            *
  *                      3. Corrected insertion of the high      *
  *                       level qualifier (HLQ) when             *
  *                       'QUALIFY blanks' was coded along with  *
  *                       a USEPLAN parm. This fixes problem     *
  *                       introduced in PTF2201 which includes   *
  *                       not properly setting the length of     *
  *                       the qualifier, resulting in a length   *
  *                       of 0, when Synonyms are also used.     *
  *                       The resulting HLQ insertions had no    *
  *                       qualifier:   ".tablename"  instead of  *
  *                       the expected   "hlq.tablename" and     *
  *                       SQLCODE -104 (illegal character '.')   *
  *                       was issued. (PMR 19116 and PMR 71114)  *
  *                      4. ANLPGME7 is a multi-release version  *
  *                       of the program, which runs under DB2   *
  *                       V6, V7 and V8 compatibility and        *
  *                       enabling modes. Access paths at run    *
  *                       time under V6 and V7 and V8C can       *
  *                       suffer from mismatched host variables  *
  *                       (defined as 128 chars for V8) vs.      *
  *                       catalog columns, resulting in DB2      *
  *                       choosing Tablespace Scans at run time, *
  *                       instead of the intended Indexed Scan   *
  *                       accesses, for the SQL PA internal      *
  *                       queries. For large DBRMs this created  *
  *                       long run times and poorer performance  *
  *                       than in the previous release.          *
  *                       This issue is now resolved             *
  *                      6. TSO welcome logo and jump panels     *
  *                       have been updated with level PTF2204   *
  *                       signature.                             *
  *                      7. Add optional code to ANLIQMA clist   *
  *                       to recognize and select Target Host    *
  *                       member that corresponds to DB2         *
  *                       Subsystem ID, when called by Query     *
  *                       Monitor with multiple DB2 subsystem    *
  *                       connections.                           *
  *                      8. Correct quoted literal detection     *
  *                       for PMR13621 as in SELECT              *
  *                       COL,  "--- Table ---" FROM T1          *
  *                      9. Correct 340 Size error when the      *
  *                       count of host variables                *
  *                       (PMRs 80119 and 26423) is split across *
  *                       records, causing overflow on storing   *
  *                       the host variable types.               *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  Code has been changed to correct these conditions.
  

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:

  UK08110

Modules/Macros

•    ANL@DSN  ANLINIT  ANLIQMA  ANLLOGO  ANLPGME7
  ANLPGME8 ANLPROCC ANLPROCR ANLQMF22
  

Fix information

• Fixed component name

  DB2 SQL PER AN

• Fixed component ID

  5697F5701

Applicable component levels

• R220 PSY UK08110

     UP05/10/25 P F510

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.