A fix is available
APAR status
Closed as program error.
Error description
SQLPA R310 receive msganl1016e Fatal SQL error encountered during ANL Registry process w/ sqlcode 12 ios operation 9. Error occurs using SQLPA R310 with DB2 V7. ANL0004T DBRMKEY PATTERN > +OFF+ < ANL4003I The CAF Open return code was 12 for ANLPGM3N on: DSNA ANL1016E Fatal SQL error encountered during ANL Registry process. DB2 Sqlcode = 12, IOS operation = 9 ( 86382 ) ADDITIONAL SYMPTOMS: 1) When on the EEE main menu (panelid EEE) if user presses the PF1 key for help they receive instead the following message: ISPP100 Panel "DSN6SP1 " error. Panel not found. New help panels have been added for Easy Explain. ( 05007 ) 2) High level qualifier (QUALIFY parm) is NOT inserted where table name is unqualified following SELECT INTO :Host variables. For example, if the first outer join follows Select Into it does not get qualified, but the HLQ inserted for other unqualified tables, resulting in SQLCODE -204. ( 71114 ) 3) Host Variable substitution with parameter marker overlays right parenthesis if no intervening space was found after :H, when SQL source is file input (not from DBRM). This includes SQL passed from DB2 Admin, etc. ( 34921 92020 ) 4) High level qualifier (QUALIFY parm) is inserted where first table name is already fully qualified, but ignored on subsequent table names, resulting in erroneous SQLCODE -512 or -204. ( 92025 ) 5) When using EXPLAIN OLD, some plan steps containing sorts (method = 3) or multiple index combinations (MU, MI) do not have corresponding Statistics records to match up with, and Explain Old is failing with error Message ANL3000E ( 58795 ) 6) When using EXPLAIN OLD, some plan steps containing multiple index access do not have proper estimate of the number or rows returned, resulting in division by zero error 320, and error message ANL3030E ( 58480 ). 7) HLQ inserts fail for synonyms resulting in: DSNT408I SQLCODE = -204 DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE ( 74080 62929 ) 8) Using SQLPA interactively under TSO / ISPF and ignoring report definitions users may receive Message ANL3003W repeatedly if the user did not define the ANLREP file for the output. USER TSO session loops on ANL3003W. ( 87723 ) 9) If a SELECT to be explained contains more than one LEFT OUTER JOIN and the tables in the Select are not qualified, then SQLPA only adds the default qualifier to the FIRST left outer join and may not qualify the other the joined tables. ( PMR07857 ) In addition to the defects described above there are several new features added to SQLPA R310 only via this APAR. 1) New stored procedures are introduced which can return the Explain plan data as well as catalog statistics. Separate stored Procs are ANLPRE3C for CAF and ANLPRE3R for RRSAF environments; sample source PLI code to use and decipher this new information is included as programs ANLSTE3C and ANLSTE3R for the two run time environments. 2) New WHAT IF? Functionality is now provided with the TSO ISPF interface allowing users to display and change optimizer- sensitive catalog statistics for their tables and indexes, to test the effects of those changes on the access path selected. Also provides the ability to save and restore old statistics and the ability to create new indexes ( load defer ) to see if the optimizer prefers a better indexed path; indexes may also be dropped by these new panel options. 3) Online catalog statistics migration is also available under the TSO/ISPF interface. Users may build jobs to collect any set of statistics and also apply them in sets to create various run time scenarios resembling production for example, and then restore these to an earlier saved version of the stats. 4) All SELECT statements, especially for explain tables and the catalog, are now rewritten employing the WITH UR clause, or FOR READ ONLY, to avoid any lock contention or delay in processing. This should aid in speeding up performance for all SQL PA components.
Local fix
Bind and relink to program ANLPGM31 for DB2 V7 instead of ANLPGM3M.
Problem summary
**************************************************************** * USERS AFFECTED: Users of DB2 SQL Performance Analyzer. * **************************************************************** * PROBLEM DESCRIPTION: SQLPA R310 users may receive various * * error indications, such as msg ANL1016E * * Fatal SQL error encountered during ANL * * Registry process with sqlcode 12, ios * * operation 9 for TSO/Batch, and/or * * observe message ANL4003I The CAF Open * * return code was 12 for ANLPGM3N on: * * subsystem when trying to execute * * ANLPGM3N under V7. Error occurs only * * using SQLPA R310 with DB2 V7. ANLPGM3N * * is the default program installed with * * SMP/E, so Users must Relink and bind * * ANLPGM31 for V7 TSO and batch installs, * * per instructions. ( 86382 ) * * Additional Symptoms: * * 1. When on the EEE main menu (panelid * * EEE) if user presses the PF1 key for * * help they receive instead the * * following message: ISPP100 Panel * * "DSN6SP1 " error. Panel not found. * * New help panels have been added for * * Easy Explain. ( 05007 ) * * 2. High level qualifier (QUALIFY parm) * * is NOT inserted where table name is * * unqualified following SELECT INTO :Host * * variables. For example, if the first * * outer join follows Select Into it does * * not get qualified, but the HLQ inserted * * for other unqualified tables, resulting * * in SQLCODE -204. ( 71114 ) * * 3. Host Variable substitution with * * parameter marker overlays right * * parenthesis if no intervening space was * * found after :H, when SQL source is * * file input (not from DBRM). This * * includes SQL passed from DB2 Admin, * * etc. ( 34921 92020 ) * * 4. High level qualifier (QUALIFY parm) * * is inserted where first table name is * * already fully qualified, but ignored on * * subsequent table names, resulting in * * erroneous SQLCODE -512 * * or -204. ( 92025 ) * * 5. When using EXPLAIN OLD, some plan * * steps containing sorts (method = 3) or * * multiple index combinations (MU, MI) do * * not have corresponding Statistics * * records to match up with, and Explain * * Old is failing with error Message * * ANL3000E. ( 58795 ) * * 6. When using EXPLAIN OLD, some plan * * steps containing multiple index access * * do not have proper estimate of the * * number or rows returned, resulting in * * division by zero error 320, and * * error message ANL3030E. ( 58480 ) * * 7. HLQ inserts fail for synonyms * * resulting in: * * DSNT408I SQLCODE = -204 * * DSNT418I SQLSTATE = 42704 SQLSTATE * * RETURN CODE ( 74080 62929 ) * * 8. Using SQLPA interactively under * * TSO/ISPF and ignoring report * * definitions users may receive Message * * ANL3003W repeatedly if the user did not * * define the ANLREP file for the output. * * USER TSO session loops on * * ANL3003W. ( 87723 ) * * 9. If a SELECT to be explained contains * * more than one LEFT OUTER JOIN and the * * tables in the Select are not qualified, * * then SQLPA only adds the default * * qualifier to the FIRST left outer join * * and may not qualify the other * * the joined tables. ( PMR07857 ) * * * * In addition to the defects described * * above there are several new features * * added to SQLPA R310 only via this APAR. * * 1. New stored procedures are introduced * * which can return the Explain plan data * * as well as catalog statistics. Separate * * stored Procs are ANLPRE3C for CAF and * * ANLPRE3R for RRSAF environments; sample * * source PLI code to use and decipher * * this new information is included as * * programs ANLSTE3C and ANLSTE3R for the * * two run time environments. * * 2. New WHAT IF? Functionality is now * * provided with the TSO ISPF interface * * allowing users to display and change * * optimizer-sensitive catalog statistics * * for their tables and indexes, to test * * the effects of those changes on the * * access path selected. Also provides the * * ability to save and restore old * * statistics and the ability to create * * new indexes ( load defer ) to see if * * the optimizer prefers a better indexed * * path; indexes may also be dropped by * * these new panel options. * * 3. Online catalog statistics migration * * is also available under the TSO/ISPF * * interface. Users may build jobs to * * collect any set of statistics and also * * apply them in sets to create various * * run time scenarios resembling * * production for example, and then * * restore these to an earlier saved * * version of the stats. * * 4. All SELECT statements, especially * * for explain tables and the catalog, are * * now rewritten employing the WITH UR * * clause, or FOR READ ONLY, to avoid any * * lock contention or delay in processing. * * This should aid in speeding up * * performance for all SQL PA components. * **************************************************************** * RECOMMENDATION: APPLY the PTF. * **************************************************************** Code has been changed to correct this condition.
Problem conclusion
APPLY the PTF.
Temporary fix
Comments
APAR Information
APAR number
PK21594
Reported component name
DB2 SQL PERF AN
Reported component ID
5697F5701
Reported release
310
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2006-03-15
Closed date
2006-05-31
Last modified date
2006-07-06
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK15001
Modules/Macros
ANLALL ANLALLOC ANLANOTE ANLAPPLY ANLBIND ANLCAT31 ANLCNOTE ANLCOLL ANLCOL2 ANLEEE ANLEEEB ANLEEEE ANLEEE3 ANLEH01 ANLEH01A ANLEH01B ANLEH011 ANLEH012 ANLEH013 ANLEH014 ANLEH015 ANLEH016 ANLEH017 ANLEH018 ANLEH019 ANLH01 ANLH02E ANLH02F ANLH02G ANLH09 ANLH09A ANLH091 ANLH092 ANLH093 ANLH094 ANLH095 ANLH096 ANLH097 ANLH098 ANLH099 ANLIDX ANLIDX1 ANLIDX2 ANLINIT ANLJCL ANLLOOP ANLMAIN ANLMAKE ANLMAKE8 ANLNEW1 ANLNEW2 ANLNUX ANLOPUT ANLPARM ANLPGM3N ANLPGM31 ANLPLIC ANLPLIR ANLPNOTE ANLPRC3C ANLPRC3R ANLPRE3C ANLPRE3R ANLQMF31 ANLREST ANLRPTS ANLSHOW ANLSQL ANLSTAT ANLSTE3C ANLSTE3R ANLTAB ANLTAB1 ANLTAB2 ANLWHAT ANLWIF31 ANL21 ANL22 ANL23 ANL32 ANL33 ANL34 H1D0310J
Fix information
Fixed component name
DB2 SQL PERF AN
Fixed component ID
5697F5701
Applicable component levels
R310 PSY UK15001
UP06/06/12 P F606
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.
[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Platform":[{"code":"PF054","label":"z\/OS"}],"Version":"310"}]
Document Information
Modified date:
04 January 2021