PK13207: SQL PA WARNING MESSAGE ANL5018W SHOULD CAUSE RC=12 BUT RESULTS IN RC=00. MSGANL5018W. PTF2205

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In SQL Performance Analyzer for DB2 the series of messages
  ANL5nnnW are warning messages which should all result in a
  RC=12 condition code to allow the user to detect the warning
  condition, but msgANL5018W does not give RC=12. It gives
  RC=0.
  Also messages: ANL5037W, ANL5035W, ANL5036W, ANL5015W, ANL5023W
  and ANL5039W.
  MSGANL5037W, MSGANL5035W, MSGANL5036W, MSGANL5015W,
  MSGANL5023W and MSGANL5039W.
  ADDITIONAL SYMPTOMS:
  1) When SQL PA ANLCAT22 is executed against more than 200
     tables a SQLCODE -804 can occur under some conditions.
     Per Appendix B: SQL PA Limitations, Item number 6 of the
     SQL PA User's Guide, ANLCAT22 is limited to a maximum of
     200 tables per run. However, a run attempted in excess of
     200 tables should not abend with -804. It should end
     cleanly with a msg of explanation. The limit of 200 tables
     will remain, but this apar will address the -804.
  2. No alternate index column key information when operating
     under DB2 V7. Hex '00' needed translation to blanks for
     column names.
  3. PLI Error codes 612 and 600 on character/numeric conversion
     errors.
     ANL1050E  ANL2002E
  4. Performance degredation after migrating to SQL PA R220.
  5. Correct the display of PRIOR access paths and costs when
     using the Compare function (EEEPATH table holds  the PRIOR
     plan and its costs)
  6. Fix situation where logic caused premature termination - no
     valid explains -- occurs when a DBRM member has only one
     statement and it cannot be explained ANL2012W.
   PTF2205
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of SQL Performance Analyzer.           *
  ****************************************************************
  * PROBLEM DESCRIPTION: - In SQL Performance Analyzer for DB2   *
  *                       the series of messages ANL5nnnW are    *
  *                       warning messages which should all      *
  *                       result in a RC=12 condition code to    *
  *                       allow the user to detect the warning   *
  *                       condition, but msgANL5018W does not    *
  *                       give RC=12. It gives RC=0.             *
  *                       Also messages: ANL5037W, ANL5035W,     *
  *                       ANL5036W, ANL5015W, ANL5023W and       *
  *                       ANL5039W.                              *
  *                       MSGANL5037W, MSGANL5035W, MSGANL5036W, *
  *                       MSGANL5015W, MSGANL5023W and           *
  *                       MSGANL5039W.                           *
  *                      - When SQL PA ANLCAT22 is executed      *
  *                       against more than 200 tables a SQLCODE *
  *                       -804 can occur under some conditions.  *
  *                       Per Appendix B: SQL PA Limitations,    *
  *                       Item number 6 of the SQL PA User's     *
  *                       Guide, ANLCAT22 is limited to a        *
  *                       maximum of 200 tables per run.         *
  *                       However, a run attempted in excess of  *
  *                       200 tables should not abend with -804. *
  *                       It should end cleanly with a msg of    *
  *                       explanation. The limit of 200 tables   *
  *                       will remain, but this apar will        *
  *                       address the -804.                      *
  *                      - No alternate index column key         *
  *                       information when operating under DB2   *
  *                       V7. Hex '00' needed translation to     *
  *                       blanks for column names.               *
  *                      - PLI Error codes 612 and 600 on        *
  *                       character/numeric conversion errors.   *
  *                       ANL1050E  ANL2002E                     *
  *                      - Performance degradation for ANLCAT22  *
  *                       statistics migration program           *
  *                       after migrating to SQL PA R220.        *
  *                      - Correct the display of PRIOR access   *
  *                       paths and costs when using the         *
  *                       Compare function (EEEPATH table holds  *
  *                       the PRIOR plan and its costs)          *
  *                      - Fix situation where logic caused      *
  *                       premature termination - no valid       *
  *                       explains -- occurs when a DBRM member  *
  *                       has only one statement and it cannot   *
  *                       be explained ANL2012W.                 *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  - Corrected Message Return Code to 12 when certain Warning
   messages are issued (ANL5015, 5018, 5023, 5035, 5036, 5037,
   5039). (PMR88247)
  - Replaced the ANLBPAM module with new code that provides for
   greater processing limits (up to 2730 members can now be
   processed, up from 200).  (PMR76365)
  - Retired the ANLSPRM program and associated ANLBAT7 JCL
   stream, in lieu of manual instructions to activate the Zparm
   which controls extra explain information in V7. Manual
   setting: on V6 or V7 systems only, edit the DSN###.SDSNMACS
   member DSN6SPRC and set variable &SPRMXPL to 1 (from default
   value of 0). This enables additional explain tables used for
   the Predicates Report. The report may also now be available
   under TSO, which was formerly restricted.
  - Corrected premature termination when a DBRM member had only
   one SQL statement and it could not be processed, allowing
   continuation to next member in sequence.  (PMR88347)
  - Corrected missing alternate index key information for V6 &
   V7 users after PTF2204 applied (alternate SQL). (PMR43468)
  - Corrected the  Compare  process of Old vs. New plans   SQL
   PA was not displaying the correct old plan info or costs.
  (no PMR - IBM Europe)
  - Removed situations causing the PLI 600 and 612 error
   conditions. (PMR19116, PMR80119)
  - Added the SUBVERS parm to ANLCAT22 statistic migration
   program, to recognize V6 and V7 FROM DB2 systems and use
   alternate SQL to improve access paths and
   performance. (PMR82663, PMR68556)
  - Correct Error Code 320 Division by Zero when multiple index
   access and DELETE is processed (PMR75124)
  - Correct display of index used by INSERTs in reports.
  
  Repeated in ACT: Please perform a REBIND on all programs
  (ANLPGM22, ANLPGME8, ANLQMF22, ANLCAT22, ANLCAT22,
  ANLPROCC and/or ANLPROCR) plus relink ANLGOV1
  before use.
  

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:

  UK10891

Modules/Macros

• ANLBPAM  ANLCAT22 ANLPGME7 ANLPGME8 ANLPROCC
  ANLPROCR ANLQMF22
  

Fix information

• Fixed component name

  DB2 SQL PER AN

• Fixed component ID

  5697F5701

Applicable component levels

• R220 PSY UK10891

     UP06/01/20 P F601

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.