Category: Uncategorized

Posted on

APAR Update

This is a quiet month with a few HIPERs and one FTB fix. All checked on the 23rd of March 2023.

HIPERs:

PH52147 STORAGE OVERLAY ABEND04E RC00E2002F IN DSNSVSFM OFFSET00702 AND DB2 CRASH MSGDSNV086E RC00D96001
PH52155 INCORROUT MAY OCCUR FOR A QUERY USING OUTER JOIN
PH52275 ABEND0C4 RC38 IN DSN3EOT0 OFFSET0992

Interesting for RTS:

None.

RUNSTATS:

None.

Query performance:

None.

IAG2:

None.

FIT/FTB:

For Db2 13 PH52893 OBSOLETE FTB MSGDSNI070I MESSAGE DISPLAYED FOR V13 23/02/27 PTF PECHANGE


Now come the three tables containing all the APARs.

First are the Real-time Statistics APARs – If the RTS are incorrect then any Utility generation or Access path decision based upon them could also be incorrect.

Next are the RUNSTATS APARs – If RUNSTATS causes data problems this, obviously, can have a major impact upon performance after the following BIND, REBIND or dynamic SQL PREPARE.

Finally, come the SQL PERFORMANCE APARs – In here are all relevant performance APARs as well as HIPERs that do not fit into any other category. A HIPER warrants a check in all cases.

All three tables have the same headings:

APAR – The assigned APAR for the problem.

CLOSED – This is the date in YYYY-MM-DD format when the APAR was closed.

STATUS – This column is NEW, NEW & CLOSED, CLOSED or PE xxxxx. A PE means that this APAR went PE and the xxxxx is the corrective APAR.

Db2 12 – If the PTF is for Db2 12 then it is listed here else N/A or OPEN.

Db2 13 – If the PTF is for Db2 13 then it is listed here else N/A or OPEN.

HIPER – Contains Y if the APAR is a HIPER. Can also contain (Y) if it is OPEN but presumed to be a HIPER, else it is blank.

Description – This is the APAR description taken from the header text. Sometimes I add extra details if the description is misleading or incorrect.

Note: If the PTF column has OPEN it means that the PTF was still open when the last check was done; of course it could have been Closed since this post was updated. Every year all APARs that were closed over two years ago are deleted.

RTS APARs:

APARCLOSEDSTATUSDb2 12Db2 13HIPERDescription
PH311242021-01-28 UI73674N/A REBUILD INDEX SHRLEVEL REFERENCE OF DIRECTORY INDEX DSNDB1XA OR DSNDB01X GETS DSNU590I WITH RC00C900AE BUT COMPLETES SUCCESSFULL
PH314982021-01-06 UI73361N/A NO RTS UPDATE DURING RUNSTATS TABLESPACE SHRLEVEL REFERENCE UPDATE ALL IN V12 FL505 AND ABOVE.
PH348072021-03-29 UI74649N/A EXTRA CPU COST OF MASS DELETE
PH355892021-05-04 UI75212N/A AFTER A PIT RECOVER , RTS REORGLASTTIME IS NULL AND DSNACCOX DOES NOT EXTRACT THE OBJECT FOR REORG
PH395032021-08-23 UI76887N/A ABEND04E RC0090101 IN DSNILKPD:1001 CAN OCCUR ON A QUERY IF STATS SAY TABLE IS EMPTY AND BUFFERPOOL SIZE IS ZERO
PH414802021-11-12 UI78060N/AYABEND04E RC00C90D01 DSNONLLE ERQUAL53AC DURING DISASTER RECOVERYRECOVER OF DB2 CATALOG AND DIRECTORY
PH459162022-06-29 UI81253UI81254 COPYUPDATEDPAGES IS INCORRECT AFTER LOAD WITH INLINE COPY, INCREMENTAL COPY GETS SKIPPED, RECOVER REQUIRES LOG APPLY
PH470902022-08-08 UI81865UI81866 SYSIBM.SYSINDEXSPACESTATS(STATSLASTTIME) DOES NOT GET UPDATED FROM LOAD REPLACE WITH INLINE STATISTICS SPECIFIED.
PH511082023-01-04 UI83921UI83922YACCESS DATABASE() SPACE() MODE(STATS) COMMAND DOES NOT UPDATE LASTDATACHANGE ON THE PARTITION WHICH IS CLOSED BY 010.PM2PCP01

RUNSTATS APARs:

APARCLOSEDSTATUSDb2 12Db2 13HIPERDescription
PH324482021-01-07 UI73367N/A     RUNSTATS UTILITY IS NOT COLLECTING THE SYSCOLDISTSTAT THAT IS BEING SPECIFIED (EG. FREQVAL COUNT 10 MOST)
PH332262021-02-26 UI74173N/A    YDB2 BUFFER MANAGER OPEN PAGESET STORAGE LEAK
PH341232021-03-01 UI74210N/A     RUNSTATS DOES NOT COLLECT DISTRIBUTION STATISTICS FOR SINGLE COLUMN COLGROUP FOR SEGMENTED MULTI-TABLE TABLE SPACES
PH352672021-06-01 UI75649N/A     RUNSTATS DOES NOT UPDATE COLUMN SYSCOLDIST.CARDF CORRECTLY WHEN NUMCOLUMNS > 1
PH375452021-07-14 UI76330N/A     RUNSTATS ABENDS0CF RC0000000F IN DSNUSEOF.
PH401892022-03-30 UI79958N/A    YRUNSTATS AT PARTITION LEVEL GOT ABEND04E RC00E40213 WITH CAUSE 00C9004F
PH435032022-03-31 UI79966N/A     RUNSTATS TABLESPACE REGISTER NO FAILS WITH RC00C90637 OR ABEND04E WITH RC00C90101 AT DSNIOW ERQUAL 2002
PH442462022-04-01 UI79986N/A    YABEND04E RC00E2000F DSNSVSFB+00A2A DURING A RUNSTATS TABLESPACE WITH EXCLUDE NULL KEYS INDEX
PH455332022-05-20 UI80643N/A     INCORRECT VALUE OF CARDF IN SYSIBM.SYSCOLDISTSTATS WHEN RUNSTATS COLLECTS KEYCARD STATISTICS ON DPSI INDEX
PH463512022-06-01 N/AUI80817 INCORRECT VALUE OF CARDF IN SYSIBM.SYSCOLDIST WHEN RUNSTATS COLLECTS KEYCARD STATISTICS ON DPSI INDEX
PH467672022-08-31 UI82201UI82202 DROP INDEX LEAVES ORPHAN ROWS IN SYSIBM.SYSCOLDIST
PH471832022-07-19 UI81531UI81532 RUNSTATS USE PROFILE GOT TIMEOUT ON WAITING SYSCOLDISTSTATS TABLE X LOCK DURING LOCK ESCALATION.
PH484072022-09-02 UI82237UI82238 RUNSTATS AT PARTITION LEVEL GETS ABEND04E RC00E40213 WITH CAUSE 00C9004F
PH491192022-11-04 UI83135UI83136 COLCARDF IN SYSIBM.SYSCOLUMNS IS INCORRECT (RESET TO ZERO) AFTER A REORG AT PARTITION LEVEL USING INLINE STATISTICS
PH494102022-11-07 UI83157UI83158 ABEND04E RC00C90101 AT DSNIOW ERQUAL2002 DURING RUNSTATS SHRLEVEL CHANGE REGISTER NO
PH519012023-02-22 UI90670UI90671 RUNSTATS INVALIDATECACHE FUNCTION CAUSES ACCESSPATH PROBLEMS
PH51905  OPENOPEN PCTROWCOMP SET BY RUNSTATS MIGHT BE INACCURATE
PH52439  OPENN/A TERM UTILITY COMMAND HANGS

SQL PERFORMANCE and general HIPERs:

APARCLOSEDSTATUSDb2 12Db2 13HIPERDescription
PH11864  OPENOPEN SQL SELECT RETURNS INCORROUT AFTER TABLE WITH LOB COLUMNS IS UPDATED
PH217932021-01-05 UI73335N/A     ABEND04E RC00E2000D AT DSNXESTR DSNSVSFB OFFSET00A18
PH26180  OPENOPEN CARTESIAN PRODUCT RESULTING FROM INCORRECT JOIN ORDER
PH26498  OPENOPEN(Y)ABEND04E RC00C90101 DSNISFPI ERQUAL5019 WHEN USING INSERT ALGORITHM 2 IAG2
PH278402021-02-05 UI73843N/A     INCORROUT FROM QUERY THAT REFERENCES A LARGE VARCHAR FOR BIT DATA COLUMN AS A JOIN PREDICATE FOR SORT MERGE JOIN
PH287312021-01-07 UI73383N/A     ABEND04E RC00C90101 AT DSNIDM DSNISRID ERQUAL500A OCCURRED FOR A SECOND FETCH FROM A ROWSET CURSOR
PH288102021-01-28 UI73671N/A     MSGDSNT286I INDICATES APREUSE WAS NOT SUCCESSFUL WHEN THE ACCESSPATH BEING REUSED CONTAINS CORRELATED SPARSE INDEX IN TBL EXPR
PH299182021-01-19 UI73538N/A     ABEND0C7 AT DSNXRDEC OFFSET8960 MAY BE ISSUED FOR A QUERY THAT REF A VIEW OR AN SQL TABLE UDF THAT CONTAINS RECURSIVE CTE
PH303962021-01-06 UI73347N/A     OPTIMAL ACCESS PATH MIGHT NOT BE USED FOR A JOIN QUERY WHICH CONTAINS BOTH LOCAL PREDICATE AND JOIN PREDICATE ON SAME COLUMN.
PH309782021-06-01 UI75643N/A     SUBSYSTEM PARAMETER TO ENABLE INDEX IN-MEMORY OPTIMIZATION (FTB) FOR NON-UNIQUE INDEXES
PH311182021-01-06 UI73357N/A     INEFFICIENT JOIN ORDER AND JOIN TYPE CAN BE SELECTED AFTER QUERYBLOCK MERGE
PH311462021-01-20 UI73548N/A     ABEND0C4 RC0000003A RC04 PIC3A DSNSVSVB +00CB0 FOR THE QUERY WITH HUGE NUMBER OF UDFS
PH314902021-01-06 UI73359N/A     ABEND0C4 DSNXRSOR +146 @ UI62958
PH315662022-01-12 UI78898N/A    YUSER RECEIVED AN UNEXPECTED ABEND04E RC00E70100 IN DSNXGRDS DSNXISB7 P110 ON AN INSERT STATEMENT
PH316212021-01-07 UI73365N/A     POOR PERFORMING QUERY WHEN DB2 SELECTS INDEX + LIST PREFETCH AP FOR LEADING TABLE WHILE INDEX IS CLUSTERING AND CAN AVOID SORT
PH316802021-02-15 UI73982N/A     POOR PERFORMANCE CAN OCCUR FOR A QUERY WITH TABLES HAVING DIFFERENT CCSID’S
PH317752022-01-05 UI78823N/A    YA LINEAR INDEX WITH > 255 PIECES IS NOT RECOVERED BY RESTORE SYSTEM UTILITY
PH317962021-01-08 UI73397N/A    YA QUERY USING DEGREE=’ANY’ MAY HAVE IDENTICAL ROWS RETURNED
PH318722021-02-02 UI73756N/A     AN INEFFICIENT ACCESS PATH IS CHOSEN AFTER A TABLE EXPRESSION ISMERGED
PH320972021-02-02 UI73760N/A     THE QUERY USING OFFSET CLAUSE WITH UNION AND HOST-VARIABLE CAN RETURN EMPTY RESULT SET
PH323552021-02-12 UI73972N/A    YINCORRECT RESULT WITH NO ROWS RETURNED FOR QUERY FROM TABLE FUNCTION USING LITERAL
PH323762021-01-06 UI73362N/A    YADDITIONAL TAG SORT AND SORT POOL VERIFICATION.
PH324682021-01-20 UI73559N/A     INEFFICIENT ACCESS CAN BE CHOSEN FOR A QUERY CONTAINING A FF1R CLAUSE
PH326612021-01-07 UI73394N/A     ABEND04E RC00E70005 AT DSNXEFDA OFFSET035FC FOR A QUERY THAT REQUIRES A VERY LARGE RID SORT.
PH326882021-03-03 UI74227N/A     POOR PERFORMANCE CAN OCCUR FOR A QUERY WITH FF1R.
PH326902021-02-18 UI74060N/A    YINCORROUT WHEN A SAME EXPRESSION ALIAS APPEARS IN THE SELECTION LIST MULTIPLE TIMES AND THIS QUERY RUNS ON PARALLELISM
PH327262021-03-03 UI74226N/A     POOR PERFORMANCE CAN BE SEEN WHEN A QUERY CONTAINS MULTIPLE PREDICATES.
PH329822021-01-20 UI73560N/A     POOR PERFORMING SQL QUERY WHEN THE QUERY HAS MULTIPLE RANGE OR BETWEEN PREDICATES WHICH QUALIFIED MULTI-INDEX ACCESS
PH329852021-02-03 UI73782N/A     ABND0C4 REASON00000004 AT DSNXGRDS DSNXOLIT OFFSET004DC MAY OCCUR FOR QUERY WITH STATEMENT CONCENTRATION ENABLED
PH331282021-02-10PE PH41952UI73918N/A     POOR PERFORMANCE CAN OCCUR FOR A QUERY CONTAINING A FFNR
PH331962021-04-19 UI74989N/A     PERFORMANCE ISSUE WHEN SPARSE INDEX WAS CHOSEN AND THERE EXISTS PREDICATES THAT ARE CAST TO VERY LONG LENGTHS
PH332852021-03-11 UI74393N/A     INCORRECT ACCESS PATH WHEN A VALUE COMPARE OR A BETWEEN PREDICATE WITH ARITHMETIC EXPRESSION ON DECIMAL VARIABLES AND A
PH333392021-03-03 UI74234N/A     LACK OF IFCID 376 REASON 3 (TIMESTAMP) IN DB2 V12 TRACE WHEN APPLCOMPAT = V10R1 AND BIF_COMPAT IS NOT CURRENT
PH334142021-03-29 UI74634N/A    YRECOVER UTILITY DOES NOT CORRECTLY RECOVER THE COPY YES INDEXES CAUSING DATA/INDEX MISMATCH
PH334432021-02-10 UI73921N/A     ABEND04E RC00C900D0 AT DSNXROHB OFFSET23DC4 MAY OCCUR FOR NOT NULL LOB/XML COLUMN INVOLVING IN OUTER JOIN
PH336362021-03-08 UI74325N/A     INCORRECT OUTPUT FOR AN SQL STATEMENT THAT REFERENCES A VIEW WITH MULTIPLE OUTER JOINS
PH340452021-03-15 UI74443N/A    YINCORRECT SORT ORDER IN OUTPUT OF SELECT STATEMENT
PH340462021-03-29 UI74636N/A     INCORROUT FOR A QUERY WITH EXISTS PREDICATE IN CASE EXPRESSION AND OUTER JOINS
PH340562021-04-02 UI74752N/A     ABEND04E RC00E70005 AT DSNXOW2D P333 MAY HAPPEN WHEN UPDATING A VIEW WITH AN ISNULL PREDICATE
PH340962021-02-16 UI74002N/A     ABEND0C4 RC38 DSNXOGA OFFSETAE6C AE6C WHILE RUNNING COMPLEX QUERY
PH341782021-05-03 UI75206N/A     SUB-OPTIMAL ACCESS PATH CAN BE CHOSEN FOR THE QUERY
PH341892021-04-07 UI74812N/A     ABEND04E RC00C90101 AT DSNIDM DSNIWNRF ERQUAL5006 MIGHT HAPPEN WHEN A QUERY WITH LEFT OUTER JOIN AND SORT MERGE JOIN IS USED
PH342412021-04-12 UI74891N/A     ABEND0C4 RC38 DSNXECST OFFSET09F6 09F6 WHILE RUNNING COMPLEX QUERY
PH342472021-03-12 UI74421N/A     INEFFICIENT INDEX CHOSEN IN A RANGE-LIST ACCESS PATH.
PH342922021-06-23 UI76040N/A    YROLLBACK AFTER ALTER TABLE ALTER COLUMN SET WITH DEFAULT DOES NOT RESTORE PRIOR DEFAULT VALUE
PH344252021-03-08 UI74327N/A     INCORRECT OUTPUT OF CASE WHEN COMPARING VARCHAR COLUMNS
PH344652021-04-16 UI74966N/A    YABENDS0C4 IN DSNIASFP OFFSET001A8 DURING INSERT WORKLOAD USING INSERT ALGORITHM 2 – IAG2
PH344682021-04-20 UI75007N/A     ABEND04E RC00C90101 AT DSNKTRAV ERQUAL5021 VIA FTB TRAVERSAL
PH345842021-03-30 UI74673N/A    YABEND04E AT DSNXOB2 M105 ON A REBIND PACKAGE 21/03/23 PTF PECHANGE
PH346422021-05-17 UI75437N/A     INCORROUT WRONG ROWS RETURNED FOR A QUERY WITH MULTIPLE JOINS WITH NOT EXISTS CORRELATED SUBQUERY
PH346892021-04-02 UI74759N/A    YINCORRECT OUTPUT FROM SELECT WHEN NULL HOST VARIABLE WAS USED IN A PREDICATE AGAINST NOT NULL COLUMN
PH348072021-03-29 UI74649N/A     EXTRA CPU COST OF MASS DELETE
PH348592021-05-05 UI75254N/A     DB2 12 FOR Z/OS NEW FUNCTION FOR FTB (FAST TRAVERSE BLOCKS)
PH349032021-03-17 UI74490N/A     ABEND04E RC00E70005 DSNXOFL M120 OCCURS ON UNION ALL
PH349082021-04-13 UI74918N/A     ABEND0C4 RC04 IN DSNXSRME OFFSET061A FOR SELECT USING TABLE EXPRESSION AND HYBRID JOIN
PH350802021-03-23 UI74586N/A     SQLCODE16061 ISSUED FOR A QUERY USING XMLCAST
PH350882021-04-07 UI74809N/A    YHIGH CPU DUE TO LONG PREPARES AND LOW HIT RATIO IN DYNAMIC STATEMENT CACHE WHEN DYNAMIC SQL PLAN STABILITY IS ENABLED
PH354642021-04-20 UI75013N/A     INEFFICIENT INDEX CHOSEN BETWEEN COMPETING INDEXES WHEN ONE CANDIDATE PROVIDES INDEX ONLY
PH355962021-04-07 UI74814N/A    YINSERT SPLITTING PAGE INTO FTB LEAF NODE GOT DSNKFTIN:5002 ABEND BECAUSE OLD PAGE THAT CAUSE THE PAGE SPLIT WAT MISSING IN FTB.
PH358722021-06-17 UI75961N/A    YABEND0C4 RC4 DSNURWBF+0B012 IN PARALLEL LOAD TO THE SAME PARTITION
PH359592021-04-13 UI74908N/A    YINCORROUT FOR QUERY WHERE LEADING TABLE IN ACCESS PATH IS ACCESSED WITH AN EQUAL UNIQUE INDEX AND NEXT TABLE HAS PREFETCH
PH360932021-07-30 UI76541N/A     INEFFICIENT JOIN TYPE CHOSEN WHEN THE SAME INDEX IS USED WITH LESS MATCHING COLUMNS
PH361472021-04-29 UI75178N/A     ABEND04E RC00E20018 AT DSNSTKGG OFFSET00D76 ISSUED FOR A QUERY ON VIEW AND ITERATING ENTRIES OF DSNXOV1 IN FORMATTED DUMP TRACE
PH361792021-06-02 UI75667N/A     DB2 12 FOR Z/OS ENHANCEMENT – APREUSE supports page range screening
PH362122021-10-28 UI77851N/A     JOIN PREDICATE PUSHDOWN IS BEING ELIMINATED UNDER SOME CIRCUMSTANCES.
PH362222021-05-14 UI75411N/A    YINCORRECT OUTPUT (TOO FEW ROWS RETURNED) POSSIBLE ON FULL OUTER JOIN RUNNING W/PARALLELISM, OUTER & INNER TABLES W/INDEX ACCESS
PH362972021-06-23 UI76046N/A     INEFFICIENT ACCESS PATH WHEN A POOR FILTERING INDEX LEG FOR RID LIST ACCESS IS CHOSEN FOR A JOIN 21/05/27 PTF PECHANGE
PH363002021-08-11 UI76686N/A     UNNECESSARY MULTI-INDEX LEGS ARE GENERATED WHEN A QUERY CONTAINSA HIGH UNCERTAINTY PREDICATE
PH363562021-05-24 UI75545N/A    YUNEXPECTED NULL RESULT IN QUERY RESULT.
PH364062021-05-07 UI75288N/A     INSERT KEY INTO FTB PROCESS DETECTING INCONSISTENT STRUCTURE MODIFICATION NUMBER THEN GOT DSNKFTIN:5043 ABEND
PH364302021-05-13 UI75393N/A    YINCORROUT WHEN A TABLE EXPRESSION IS ACCESSED BY ‘O’ ACCESS TYPE WHILE ITS BODY IS A SINGLE TABLE QUERYBLOCK AND INDEX IS USED
PH364342021-05-13 UI75392N/A     DB2 12 FOR Z/OS INTERNAL SERVICEABILITY UPDATE (Improve Create / Free FTB log recs)
PH365072021-08-18 UI76828N/A    YINCORROUT ON QUERY WITH MULTIPLE LEFT JOINS, COALESCE AND SPARSE INDEX
PH365312021-05-13 UI75391N/A    YABEND04E RC00C90101 AT DSNKINSN ERQUAL5009 AND DSNKFTIN ERQUAL5066 FOR FTB INSERT PLOCK FAILURE
PH365402021-05-11 UI75345N/A     AN INEFFICIENT ACCESS PATH USING LIST PREFETCH+SORT IS CHOSEN WHEN DIRECT INDEX ACCESS PROVIDES BETTER PERFORMANCE.
PH367372021-07-19 UI76363N/A     RIDPOOL FAILURES FOR QUERY WITH MULTI-INDEX ANDING AND NO QUALI FYING RIDS FOR SECOND INDEX
PH369082021-08-03 UI76579N/A     BACKING OUT THE BEHAVIOR FOR LOAD FORMAT DELIMITED FOR DECIMAL FIELDS WITHOUT A DECIMAL POINT THAT WAS IN 21/05/04 PTF PECHANGE
PH369782021-06-18 UI75978N/A     FTB MESSAGE MSGDSNT351I ISSUED INCORRECTLY
PH370192021-08-03 UI76581N/A     DB2 12 FOR Z/OS NEW FUNCTION FOR APREUSE
PH371162021-07-06 UI76220N/A     ABEND0C4 RC38 IN DSNSVSFB OFFSET0758 WHILE EXECUTING DYNAMIC SQL AND DYNAMIC PLAN STABILITY IS ACTIVE
PH371192021-08-03 UI77291N/A     INCONSISTENT ACCESS PATH FOR THE QUERY USING BETWEEN PREDICATE VERSUS RANGE PREDICATE
PH371512021-08-24 UI76897N/A     IAG2 ABEND04E RC00C90101 DSNIBHRE ERQUAL5007
PH371712021-06-15 UI75893N/A     INCORROUT MAY OCCUR FOR A QUERY USING INLIST OR BETWEEN WHICH CONTAINS COLUMNS
PH374162021-11-02 UI77911N/A     EXPLAIN PROVIDES INCORRECT ESTIMATE OF SU/TIME FOR QUERY.
PH374722021-06-25 UI76090N/A     WORKFILE ABENDS0C4 PIC4 AT DSNIWNRF OFFSET0DE50
PH378522021-07-22 UI76410N/A     DSNTIAUL INCORRECT OUTPUT CAN HAPPEN FOR SELECT FROM UTF-16 VARGRAPHIC COLUMN
PH380032021-09-21 UI77249N/A     SLOW QUERY PERFORMANCE FOR LEFT OUTER JOIN QUERY
PH382122021-07-07 UI76239N/A    YABEND04E RC00C90101 AT DSNKFTBU ERQUAL5061 AND DSNK1CNE ERQUAL5006 DURING FTB CREATION
PH382612021-10-18 UI77686N/A    YDSNIIDIS:5002 OCCURRED DUE TO A MISSING INDEX ENTRY CAUSED BY A FAILURE OF SERIALIZATION BETWEEN CREATE INIDEX AND INSERT.
PH382742021-08-04 UI76596N/A     ABEND04E RC00E2000C AT DSNXRSPL DSNSVSVB OFFSET00A54 WHEN INVOKING AN ADVANCED TRIGGER WITH HANDLER
PH384152021-10-25 UI77810N/A     POOR INDEX MAY BE CHOSEN FOR QUERY WITH MULTIPLE PREDICATES ON THE SAME COLUMN
PH385512021-08-06 UI76633N/A     ABEND04E RC00E2000F IN DSNSVSFB +00A2A WHILE RUNNING STORED PROCEDURES
PH386402021-08-09 UI76642N/A     DB2 PARALLELISM TASK ABEND04E RC00C90101 IN WORKILE MODULE DSNIWNRF ERQUAL 5010
PH388722021-09-02 UI77010N/A     UPDATE THE LENGTH OF THE ASCE AND PRHW – Fix in error see PH40706
PH391052021-10-18 UI77687N/A     DB2 12 FTB INDEXTRAVERSECOUNT = 4294967295 FOR OBJECTS NOT ENABLED FOR FTB
PH391112021-09-15 UI77157N/A     POOR PERFORMANCE OF AN UDPATE STATEMENT THAT CONTAINS A TABLE EXPRESSION ON THE NULL PADDING SIDE OF AN OUTER JOIN
PH392862021-08-23 UI76886N/A    YABEND0C4 RC003B AT DSNXODN OFFSET00600 AND ABEND04E RC00E2000D DSNLXDAL DSNSVSFB OFFSET00A18 LEAD TO DB2 ABTERM RC00E50702
PH393032021-10-18 UI77691N/A     ABEND04E RC00E70005 AT DSNXOP1 M150 FOR A CREATE FUNCTION
PH394132021-09-02 UI77014N/A     SQLCODE104 ISSUED WHEN REBIND ADVANCED TRIGGER WHICH CONTAINS A CALL STATEMENT WITH TABLE LOCATOR AS ITS PARAMETER
PH395842021-09-21 UI77253N/A     INEFFICIENT ACCESS PATH CHOSEN WHEN A QUERY CONTAINS A FF1R CLAUSE AND AN INDEX ACCESS CAN BE MATCHING
PH396312022-01-28 UI79120N/A    YABND0C4 RC10 IN DSNVXUL0 +00324 AFTER CANCEL THREAD
PH396772021-09-07 UI77044N/A     AN ALL ZERO PAGE ERROR – ABEND DSNKTRAV:5021
PH397512021-09-14 UI77134N/A     THE INEFFICIENT JOIN SEQUENCE MAY BE CHOSEN, WHEN THE JOIN TABLE HAS A CORRELATED PREDICATE
PH398222021-09-27 UI77334N/A     DB2 12 SERVICEABILITY FOR INFLUENCING ACCESS PATH SELECTION AND SUPPORTING INDEXABILITY FOR SQL STMTS USING IMPLICIT CAST
PH398752021-09-02 UI77013N/A     ABEND04E RC00E70005 DSNXOACM P070 WHEN A COLUMN MASK IS DEFINED
PH400032021-09-27 UI77336N/A    YCORRELATED SQL ISSUE, WHEN A TRANSITIVE CLOSURE PREDICATE IS GENERATED IN A SUB-QUERY INSIDE A CASE-WHEN CLAUSE.
PH402162022-01-21 UI79013N/A     BLOCK JOIN PREDICATE PUSHDOWN WHEN THE TARGET SUBQUERY ACCESS PATH IS TABLESPACE SCAN
PH402432022-04-19 UI80210N/A     DCC NEW FUNCTION FOR DB2ZAI V1.5
PH402692021-09-16 UI77189N/A    YABEND04E RC00E72068 AT DSNXSRME OFFSET01024 DUE TO A TIMING WINDOW WHEN USING INDEX FAST TRAVERSE BLOCK (FTB)
PH402732021-11-09 UI78000N/A     IMPROVE PERFORMANCE OF FTB STORAGE POOL ADMF INDEX MANAGER CL20
PH402742021-11-01 UI77882N/A     SQL0119N MIGHT BE ISSUED WHEN THE QUERY REFERENCES A VIEW AND THERE IS GROUP BY AND UNION ALL IN THE VIEW
PH402822021-09-21 UI77258N/A    YABEND04E RC00E2000C DUE TO STORAGE LEAK IN SUBPOOL ADMF AGL 64.
PH404322021-09-21 UI77254N/A     POOR PERFORMING QUERY WHEN AN INEFFICIENT JOIN ORDER IS CHOSEN FOR QUERY WITH FETCH FIRST N ROWS ONLY CLAUSE
PH404612021-11-02 UI77909N/A     ABEND04E RC00E70005 DSNXOSTP M210 CAN OCCUR ON A CREATE TRIGGER WHICH CONTAINS MORE THAN ONE CALL STATEMENT
PH405272021-10-26 UI77824N/A    YINCORRECT OUTPUT RETURNED BY A QUERY WHOSE ACCESS PATH IS BASED ON A DB2ZAI HOST VARIABLE MODEL
PH405392021-10-07 UI77500N/A     FTB DEADLOCK OCCURS WITH SYSTEM ITASK – CORRID=014.IFTOMK01
PH405432021-10-01 UI77407N/A     SLOW QUERY PERFORMANCE COULD OCCUR WHEN LIST PREFETCH IS CHOSEN
PH406672022-03-02PE PH46287UI79547N/A ENHANCEMENT TO DB2 STACK STORAGE HANDLING
PH407062021-11-10 UI78014N/A     ABEND0C4-04 IN DSN3ID80 WITH PTF UI77010 APPLIED 21/09/17 PTF PECHANGE
PH409812021-12-14 UI78548N/A     ABEND0C4 AT DSNXRSOR OFFSET00136 FOR QUERY WITH VIRTUAL TABLE ACCESS ON TABLE EXPRESSION WITH CASE EXPRESSION
PH410552021-12-27 UI78742N/A    YSTORAGE LEAK WHILE RUNNING DRIVER PACKAGES WITH INCORRECT CLIENTAPPLCOMPAT SETTING
PH412052022-02-14 UI79308N/A     ABEND04E RC00E72068 AT DSNXSRME OFFSET0106E CAN OCCUR WHEN A TABLE EXPRESSION OR A VIEW CONTAINS UNIONALL
PH412122022-01-12 UI78897N/A    YINCORRECT OUTPUT MAY OCCUR FOR A QUERY USING XMLTABLE FUNCTION.
PH412162021-11-17 UI78137N/A     ABENDS0C4 RC04 PIC04 RC00000004 IN DSN3ID80
PH413072021-11-17 UI78136N/A     PERFORMANCE ISSUE WHEN THE JPP IS NOT ALLOWED FOR QUERY USING VIEW WITH UNION ALL INCLUDING A SMALL TABLE
PH413312021-12-08 UI78449N/A     ABEND0C4 RC11 DSNXOSL OFFSET074C8 ON A CREATE TRIGGER STATEMENT
PH413352021-12-04 UI78403N/A     ABEND04E 00C90101 AT DSNICMTC ERQUAL 5004
PH413502021-11-29 UI78295N/A    YINCORRECT OUTPUT MAY OCCUR WHEN IMPLICIT CAST IS PERFORMED FROM SMALL INT TO INT
PH414362022-01-24 UI78933N/A    YABEND04E RC00E70005 AT DSNXRFN:M509 MIGHT HAPPEN FOR A QUERY WITH A CORRELATED SUBQUERY CONTAINING FETCH FIRST N ROWS ONLY
PH414462021-12-22 UI78714N/A    YPOSTPONED ABORT ENTERED INTO AN INFINITE LOOP IN DSNB1CLM DUE TODB2 INTERNAL CONTROL BLOCK POINTING TO ITSELF
PH414482021-11-02 UI77914N/A     ABEND0C4 RC10 AT DSNXOCS OFFSET03770 WHEN RUNNING QUERIES WITH VIEWS OR TABLE EXPRESSIONS WITH JOIN RELATIONS
PH415352021-12-09 UI78457N/A    YHIGH ECSA USAGE AFTER DB2 ABNORMAL TERMINATION
PH415672021-11-23 UI78244N/A     DB2ZAI: A LONG RUNNING DB2ZAI DAEMON THREAD CANNOT BE PROPERLY HANDLED EVEN AFTER -STOP ML COMMAND
PH416272022-02-21 UI79152N/A    YDB2 SHOULD PROCESS DIFFERENT TIMESTAMP DIGITS WITH SAME LENGTH, BUT ACTUALLY NOT
PH416702021-12-14 UI78560N/A     ABEND04E RC00E70005 AT DSNXOSSF M909 WHEN A FNMEDIAN OR PERCENTILE_CONT/PERCENTILE_DISC IN A TABLE EXPRESSION IS NOT
PH417512021-12-01 UI78344N/A     DB2 12 FOR Z/OS NEW FUNCTION
PH417932022-05-26 UI80317N/A    YSQLCODE904 RC00C90084 TYPE100 FOR STATIC SQL STATMENT FROM A PACKAGE RUNNING WITH ISOLATION LEVEL RR
PH419432021-11-22 UI78223N/A    YINCORRECT OUTPUT MAY OCCUR FOR SUBSEQUENT QUERIES WITH A SINGLE UNION ALL AND DB2ZAI ENABLED
PH419522022-01-07 UI78851N/A     DB2 MAY CHOOSE DIRECT INDEX ACCESS OVER INDEX PLAN WITH SORT WITH LOWER COST
PH419782021-12-07 UI78424N/A     DISTRIBUTED CONNECTION CONTROL (DCC) PROCESSING OF IFCID0402 FORDB2ZAI DESIGN CHANGE TO PRODUCE DELTA VALUES
PH420462022-01-27 UI79100N/A     ABEND04E RC00C90101 AT DSNOTFLA ERQUAL5001 FOR QUERY WITH A COMBINATION OF XMLSERIALIZE AND ORDER BY
PH420812022-01-27 UI79099N/A    YAN UNEXPECTED SQLCODE406 RESULTS FROM USING THE MULTIPLY_ALT BUILT-IN FUNCTION WHEN USING BIGINT ARGUMENTS.
PH421982021-12-28 UI78765N/A     SQLCODE -805 MIGHT ISSUE FOR A DROPPED TRIGGER IN SMALL TIME WINDOW
PH424692022-01-24 UI79037N/A    YINCORROUT MAY HAPPEN WHEN THERE IS CORRELATED SUBQUERY WITH EXISTS OR NOT EXISTS WITH FFNR CLAUSE AND A HYBRID JOIN IS USED.
PH425172022-03-23 UI79856N/A    YINCORROUT MAY HAPPEN FOR QUERY USE HYBRID JOIN AND IOE INDEX
PH425722021-12-28 UI78763N/A     INEFFICIENT R-SCAN IS SELECTED FOR TABLES OF 3RD VIEW JOINED
PH425542022-01-04PE PH45702N/A    N/A    YDB2 V11 ABEND04E RC00E2000F ON DSNSVSFB OFFSET08C6 DURING DBET PROCESS.
PH425782022-02-14 UI79305N/A    YLATCH CONTENTION AFTER DB2 RESTART AND LPL RECOVERY HAS COMPLETED
PH425822022-02-01 UI79151N/A     ABEND04E RC00E72068 AT DSNXSMUA OFFSET017F0 MAY HAPPEN TO SQL STATEMENT THAT CONTAINS ORDER BY ON THE RESULT OF UNION ALL
PH429752022-01-27 UI79112N/A     SMF FIELD QISTFTBSIZE EXCEEDS ZPARM INDEX_MEMORY_CONTROL
PH429992022-03-09 UI79656N/A     ABEND04E RC00E70005 IN DSNXOSJT M110 MAY OCCUR FOR A QUERY WITH MULTIPLE OUTER JOINS WHEN STAR JOIN IS ENABLE
PH430832022-03-10 UI79673N/A     LOOP OCCURS IN DSNXOEXB DURING THE PREPARE OF A QUERY WHICH CONTAINS OUTER JOINS AND AN IN SUB-QUERY PREDICATE
PH433382022-02-14 UI79313N/A    YINCONSISTENT DATA AND VARIOUS ABENDS IN DSNIRNXT FOR PBR RPN TABLESPACE AFTER RECOVER USING AN INLINE COPY CREATED BY REORG.
PH434602022-07-11 UI81408UI81409YABEND04E RC000C90101 AT DSNGEPLC ERQUAL5064 MIGHT HAPPEN WHEN EXECUTING A PACKAGE IF REBIND IS OCCURRING AT THE SAME TIME
PH434952022-03-10 UI79683N/A     COMMIT LSN ENHANCEMENT IN V12 IS DISABLED.
PH435472022-02-17 UI79400N/A     INEFFICIENT ACCESS PLAN CAN BE PICKED FOR QUERIES WITH FETCH FIRST N ROWS ONLY / OPTIMIZE FOR N ROWS CLAUSE
PH435622022-02-22 UI79465N/A    YINCORROUT OR ABEND04E RC00C20305 MAY OCCUR FOR A PREDICATE COMPARING ROWIDS FROM DIFFERENT TABLES, DIRECT ROW ACCESS IS USE
PH435652022-02-14 UI79317N/A    YINCORROUT WITH FTB AND NON-UNIQUE INDEXES WITH GREATER THAN PREDICATE
PH435692022-03-04 UI79586N/A     ABEND04E RC00E70005 MIGHT HAPPEN AT DSNXOMB M020 FOR A QUERY WHEN HYBRID JOIN IS SELECTED
PH436632022-02-08 UI79219N/A    YDB2 THREAD ABEND ABND=0C1-00000001 from ssidDIST asid
PH437062022-05-27 UI80740N/A     IAG2 ABEND04E 00C90105 IN DSNIASFP ERQUAL 0CA4
PH437282022-02-22 UI79437N/A     WORKFILE ABEND04E RC00C90101 DSNISFW ERQUAL500B
PH437352022-03-10 UI79674N/A     AFTER ISSUING A DISPLAY STATISTICS COMMAND DISPLAY STATS(ITC) LIMIT(*), DB2 INVALIDLY ISSUES AN ABEND04E 00F9000C
PH437972022-02-22PE PH47264UI79458N/A    YABEND04E 00F31100 – Plus Early APAR PH41216
PH437982022-03-16 UI79767N/A     ACCESS PATH IS NOT REUSED ON REBIND PACKAGE WITH APREUSE FOR TABLES WHERE EXPANSION_REASON <> ‘ ‘ IN THE PLAN TABLE
PH438062022-03-24 UI79873N/A    YDB2 SUBSYSTEM ABNORMAL TERMINATION WITH RC00D94001 AFTER ABEND04E RC00E20028 DUE TO AN OVERLAY BY REORG REBALANCE
PH439492022-03-24 UI79862N/A    YDB2Z AI SQL OPTIMIZATION DASHBOARD DISPLAYS THE PACKAGE ERROR MESSAGE
PH440622022-02-24 UI79486N/A     ABEND04E RC00E70005 DSNXOV0:M101 MAY OCCUR FOR SQL THAT REFERENCES AN UDF THAT CONTAINS AN XMLSERIALIZE FUNCTION
PH440732022-03-08 UI79643N/A    YDB2 CRASHES WITH ABEND04E RC00E50079 DSNURMPG +386 AND 04F RC00E50054 AFTER RESTART(CURRENT) OF LOAD UTILITY
PH441092022-03-31 UI79969N/A    YALLEVIATE QUIESCING BEHAVIOR CHANGES DURING THE POPULATION OF NEW V12 SPACE LEVEL ATTRIBUTE FOR ALTER TABLESPACE PROCESSING
PH441192022-06-07 UI80920UI80921 SYSIBM.SYSTABLEPART LIMITKEY COLUMN HAS AN INCORRECT VALUE AFTERREORG ON A SUBSYSTEM WITH DECIMAL=COMMA IN THE DSNHDECP
PH441372022-03-14 UI79717N/A     SQLCODE904 WITH RC00D50001 AND RESOURCE 0000090A MIGHT OCCUR WHEN XMLCAST AND XMLQUERY OCCURS IN THE WHERE CLAUSE
PH441812022-04-01 UI79984N/A     ABEND04E RC00C90101 IN DSNICUBC ERQUAL5004
PH442112022-03-14 UI79710N/A    YQUERY PREDICATE PUSHDOWN INTO CTE SUBQUERY UNION LEG, FOR SUBQUERY WITH FFNR, MAY CAUSE INCORRECT OUTPUT
PH442312022-03-14 UI79718N/A     ABEND0C4 RC38 AT DSNXOYP1 OFFSET09BF6 issued for CREATE OR REPLACE PROCEDURE
PH442602022-05-17 UI80553N/A     ABEND0C4 RC04 AT DSNXOSEP OFFSET063C8 MAY OCCUR WHEN QUERY CONTAINS CTE OR TABLE EXPRESSIONS
PH442912022-07-20 UI81583UI81584YABENDS0C4 RC00000038 RC38 AT DSNIACCH+19B92 OR ABEND04E RC00C90101 AT DSNISRTI ERQUAL534C
PH443562022-03-22 UI79841N/A    YWORKFILE ABEND04E RC00C90101 IN DSNIWKFD ERQUAL5005 AND DB2 CRASH WITH MSGDSNV086E RC00F30801
PH443622022-06-13 UI80989N/A     ABEND 04E RC00E70005 AT DSNXOBM P030 WHEN QUERY HAS A CORRELATED PREDICATE THAT COULD BE USED AS A SPARSE INDEX KEY
PH44421  OPENOPEN DB2 12 FOR Z/OS NEW FUNCTION TO SUPPORT DB2Z AI
PH446182022-04-11 UI80090N/A    YABEND04E RC00E70005 DSNXESX4 P403 CAN OCCUR FOR AN OUTER JOIN QUERY REFERENCING A LOB COLUMN WITH A SHORT INLINE LOB LENGTH
PH446282022-04-11 UI80089N/A    YPRECONDITIONING APAR FOR A FUNCTION IMPROVEMENT OF IN-MEMORY RLFTABLE AUTO REFRESH IN DATA SHARING GROUP
PH446312022-06-06 UI80891N/A     ABEND0C4 RC00 AT DSNXRDEC OFFSET087F8 WHEN MIGRATING AN EXTERNALSQL PROCEDURE TO A NATIVE SQL PROCEDURE
PH447012022-04-01 UI79998N/A     ABEND04E RC00E70005 AT DSNXOGP ERQUALP666 MAY HAPPEN FOR THE QUERY WITH MORE THAN 1023 QUERY BLOCKS
PH448332022-12-08 UI83589UI83590 NEW FUNCTION FOR LIMITING THE NUMBER OF UDF RUNNING CONCURRENTLY
PH448402022-06-21PE PH48139UI81105N/A     ABEND0C4-3B IN DSNWARDS OFFSET024F8 – See PH48139
PH449162022-07-27 UI81675UI81676 EDM STORAGE CAN GROW BEYOND VALUE OF EDMCSTMTC AND BEING NOT CONTRACTED
PH449282022-04-11 UI80091N/A    YINCORRECT OUTPUT CAN OCCUR WHEN AN EXPRESSION-BASED INDEX IS USED
PH449722022-06-02 UI80862N/A     MESSAGE DSNP007I RC00D70002 ISSUED FOR WORKFILE EXTEND FAILURE ON 32K WORKFILES
PH449832022-04-20 UI80092N/A     DB2Z AI SYSTEM ASSESSMENT PERIODICALLY INSERTS RECORDS INTO TABLES MORE FREQUENTLY THAN THE STATIME_MAIN SYSTEM PARAMETER
PH449992022-04-28 UI80349N/A    YINCORRECT RESULT SET COULD BE RETURNED WHEN USING JSON SQL
PH450852022-04-05 UI80027N/A     SQLCODE=-725 MIGHT BE RETURNED WHEN OFFLOADING A QUERY WHICH REFERENCES A VIEW WITH CTE HINT
PH451002022-06-01 UI80840N/A    YABEND04E 00C90101 AT DSNICUMW ERQUAL 5003
PH452002022-06-02 UI80859N/A    YDB2 ABEND04E 00E2000F IN DSNSVSFB OFFSET00A6A AND DB2 CRASH MSGDSNV086E REASON 00F30801
PH452622022-04-25 UI80308N/A    YABEND0C4 AND STORAGE OVERLAY AFTER WILD BRANCH IN DSN9SCN9 TO INVOKE THE FRR ROUTINE DURING DISPLAY DB COMMAND EXECUTION
PH452722022-10-03 UI82679UI82680 USING QUERY ACCELERATION WITH PREPARE ATTRIBUTES CLAUSE ‘ CONCENTRATE STATEMENTS OFF ‘ MAY CAUSE PERFORMANCE IMPACT
PH453222022-11-03 UI83120UI83130 ABEND04E RC00E2000C AT DSNSVBK OFFSET00A54 MAY HAPPEN WHEN THREAD HANDLES A LARGE NUMBER OF ALTER STATEMENTS.
PH453582022-05-20 N/A    UI80601YWHEN RUNNING SQL DATA INSIGHTS Z16 WITH ZAIU GET ABEND=U4039
PH453692022-06-15 UI81024N/A     PERFORMANCE ISSUE WHEN SPARSE INDEX WAS CHOSEN BUT IT SPILLS INTO PHYSICAL WORKFILE
PH454832022-05-12 UI80499N/A     ABEND04E 00E20018 AT DSNSLD4 DSNSTKGG OFFSET00F76
PH455812022-05-06 UI80442N/A    YABEND04E RC009C0101 AT DSNKINSL ERQUAL5033 DURING INSERT
PH456432022-05-11 UI80483N/A     SQL STATEMENT POOR PERFORMANCE DUE TO INEFFICIENT JOIN ORDER FOR SUBQUERY IF THERE ARE TWO TABLES JOIN ON UNIQUE KEY.
PH457022022-05-03 N/A    N/A    YDB2 V11 REAL STORAGE CREEPS AFTER APPLY APAR PH42554/PTF UI78803 22/04/14 PTF PECHANGE – PH45702 Db2 11
PH457482022-05-11 UI80481N/A    YAN INCORRECT RESULT CAN BE RETURNED FROM A QUERY WITH UNION ALL THAT INCLUDES AN ORDER BY CLAUSE WITH OFFSET SPECIFIED.
PH457902022-05-11 UI80484N/A     DB2 MAY MATERIALIZE A SIDE WAY REFERENCED TABLE EXPRESSION/VIEW, WHILE JPP CAN PROVIDE BETTER PERFORMANCE.
PH460282022-05-28 N/A    UI80758 DB2Z AI SYSTEM ASSESSMENT PERIODICALLY INSERTS RECORDS INTO TABLES MORE FREQUENTLY THAN THE STATIME_MAIN SYSTEM PARAMETER
PH460582022-07-05 UI81303N/A    YCROSS LOADER (LOAD WITH INCURSOR) GETS ABENDS0C1 ABENDS0C6 PECHANGE 22/05/19 PTF
PH460992022-09-07 UI82304UI82305YDB2 SYSTEM HUNG WITH A LATCH.
PH461652022-06-16 UI81044N/A    YABEND04E RC00E70005 AT DSNXEPP M180 CAN OCCUR IN IN A V12/V13 COEXISTENCE ENVIRONMENT.
PH462062022-06-09 UI80959UI80960YAUTOMATIC GRECP RECOVERY 014.ASUTOGREC HANG UP AFTER ABEND0C4 PIC38 IN DSNIFLAA DUE TO ZERO PAGE SET CONTROL BLOCK POINTER
PH462092022-06-06 N/A    UI80890 UNEXPECTED VALUE WHEN QUERY FROM THE PLAN TABLE
PH462122022-06-02 N/A    UI80863 ABEND04E RC00E70005 DSNXOV0:M101 MAY OCCUR FOR SQL THAT REFERENCES AN UDF THAT CONTAINS AN XMLSERIALIZE FUNCTION
PH462212022-07-19 N/A    UI81536YIN A DATA SHARING GROUP, RLF WAS STARTED UNEXPECTEDLY BY OTHER MEMBER’S NOTIFICATION WITH SCOPE LOCAL
PH462872022-06-01 UI80832N/AYABENDS0D3 00000013 OR VARIOUS ABENDS AFTER APPLYING PH40667/UI79547
PH462952022-06-17 N/A    UI81087YABEND04E RC00E70005 DSNXESX4 P403 CAN OCCUR FOR AN OUTER JOIN QUERY REFERENCING A LOB COLUMN WITH A SHORT INLINE LOB LENGTH
PH463122022-05-28 N/A    UI80761 SQL STATEMENT POOR PERFORMANCE DUE TO INEFFICIENT JOIN ORDER FOR SUBQUERY IF THERE ARE TWO TABLES JOIN ON UNIQUE KEY.
PH463132022-05-28 N/A    UI80762 DB2 MAY MATERIALIZE A SIDE WAY REFERENCED TABLE EXPRESSION/VIEW, WHILE JPP CAN PROVIDE BETTER PERFORMANCE.
PH463142022-06-03 N/A    UI80871YAN INCORRECT RESULT CAN BE RETURNED FROM A QUERY WITH UNION ALL THAT INCLUDES AN ORDER BY CLAUSE WITH OFFSET SPECIFIED.
PH463192022-06-29 N/A    UI81245 ABEND0C4 RC04 AT DSNXOSEP OFFSET063C8 MAY OCCUR WHEN QUERY CONTAINS CTE OR TABLE EXPRESSIONS
PH463232022-06-02 N/A    UI80849YABEND04E 00C90101 AT DSNICUMW ERQUAL 5003
PH463482022-06-03 N/A    UI80880YABEND0C4 AND STORAGE OVERLAY AFTER WILD BRANCH IN DSN9SCN9 TO INVOKE THE FRR ROUTINE DURING DISPLAY DB COMMAND EXECUTION
PH463492022-06-01 N/A    UI80818 ABEND04E 00E20018 AT DSNSLD4 DSNSTKGG OFFSET00F76
PH463682022-06-29 UI81246UI81247YAN INCORRECT RESULT CAN BE RETURNED FROM THE TIMESTAMPADD BUILT-IN FUNCTION IF THE INPUT TIME EXPRESSION HAS HOUR 24.
PH463762022-07-08 UI81376UI81377 ADDITIONAL GETPAGES WHEN A NON RESULT SET STORED PROCEDURE CALLSADDITIONAL PROGRAMS (PACKAGES) DURING EXECUTION.
PH464122022-07-12 N/A    UI81433 ABEND 04E RC00E70005 AT DSNXOBM P030 WHEN QUERY HAS A CORRELATED PREDICATE THAT COULD BE USED AS A SPARSE INDEX KEY
PH464412022-06-07 N/A    UI80916 POOR QUERY PERFORMANCE FOR QUERY REFERENCING SPECIAL REGISTER SUCH AS CURRENT TIMESTAMP, WHEN NOT REOPT(ALWAYS) OR REOPT(ONCE)
PH464462022-09-29 UI82645N/A PRE-CONDITIONING APAR FOR A NEW FUNCTION (PBR changes)
PH464872022-07-19 N/A    UI81549 ACCESS PATH IS NOT REUSED ON REBIND PACKAGE WITH APREUSE FOR TABLES WHERE EXPANSION_REASON <> ‘ ‘ IN THE PLAN TABLE
PH465642022-05-31 UI80796N/A     DB2Z AI SYSTEM ASSESSMENT (SA) MISSING INFORMATION IN SOME TABLES THAT SUPPORT SA
PH465672023-02-15 UI90541    UI90542YEDM POOL ABEND04E RC00C90101 IN DSNGERBK ERQUAL 5015
PH465682023-01-23 N/AUI90223 UNNECESSARY OVERFLOW RECORD IS CREATED FOR PAGE LEVEL LOCKING OBJECTS
PH465702022-07-22 N/A    UI81635 IAG2 ABEND04E 00C90105 IN DSNIASFP ERQUAL 0CA4
PH466032022-05-31 N/A    UI80807 DB2Z AI SYSTEM ASSESSMENT (SA) MISSING INFORMATION IN SOME TABLES THAT SUPPORT SA
PH466042022-05-31 N/A    UI80813 ABEND0C4 RC38 AT DSNXOYP1 OFFSET09BF6 issued for CREATE OR REPLACE PROCEDURE
PH466182022-06-24 N/A    UI81417 PERFORMANCE ISSUE WHEN SPARSE INDEX WAS CHOSEN BUT IT SPILLS INTO PHYSICAL WORKFILE
PH466192022-06-21 N/A    UI81106 MESSAGE DSNP007I RC00D70002 ISSUED FOR WORKFILE EXTEND FAILURE ON 32K WORKFILES
PH46655  OPENOPEN(Y)WLMHEALTH IS BEING IMPROPERLY SET TO 1
PH467672022-08-31 UI82201UI82202 DROP INDEX LEAVES ORPHAN ROWS IN SYSIBM.SYSCOLDIST
PH468752022-06-29 N/A    UI81255YCROSS LOADER (LOAD WITH INCURSOR) GETS ABENDS0C1 ABENDS0C6
PH471002022-07-18 N/A    UI81528YDB2 ABEND04E 00E2000F IN DSNSVSFB OFFSET00A6A AND DB2 CRASH MSGDSNV086E REASON 00F30801
PH471412022-08-22 UI82057UI82058 PERFORMANCE ISSUE CAN OCCUR DUE TO LACK OF JOIN PREDICATE PUSH DOWN
PH471532022-07-12 UI81425UI81426 POOR QUERY PERFORMANCE MAY OCCUR FOR A QUERY THAT CONTAINS OR PREDICATE REFERENCING MULTIPLE TABLES.
PH472302022-07-07 N/A    UI81355YSQLCODE904 RC00C90084 TYPE100 FOR STATIC SQL STATMENT FROM A PACKAGE RUNNING WITH ISOLATION LEVEL RR
PH472492022-08-19 UI82028UI82029 (IAG2) ABEND04E RC00C90101 AT DSNIASFP ERQUAL5001
PH472642022-07-29 UI81719UI81720 ABEND04E 00F31100 AFTER ASSOCIATE CALL 22/07/28 PTF PECHANGE
PH473122022-11-08 UI83182UI83183 ABEND04E RC00E70005 AT DSNXOSR P020 FOR COMPLEX QUERY
PH473542022-08-16 UI81962UI81963 THE FILTER FACTOR FOR THE IN SUBQUERY PREDICATE MIGHT BE ESTIMATED TOO HIGH SO THE INEFFICIENT INDEX MIGHT BE PICKED UP.
PH47374  OPENOPEN(Y)ABEND04E RC00C90101 DSNOTFLA ERQUAL5021 FOR A QUERY THAT CONTAINS AN INLINE LOB AND THERE IS A WORK FILE INVOLVED
PH473972022-09-29 UI82646UI82647 INSERT ABEND04E RC00C90101 DSNISFPI ERQUAL5001 USING IAG2
PH476442022-07-21 UI81608UI81609 ABEND04E RC00E70005 MIGHT HAPPEN AT DSNNQTOP M679 FOR QUERY USING XMLTABLE
PH476862022-12-14 UI83753UI83754 DB2 ABEND04E RC00E20018 IN DSNSLD4.DSNSTKGG OFFSET00B98 OFFSET00BC2 (EXPLAIN of extremely large SQL)
PH477062022-08-17 UI81978UI81979 ABEND04E RC00E70005 ABEND HAPPENS ON A REBIND WITH A MERGE STATEMENT WITH AN INCLUDE COLUMN
PH477952023-01-04 UI83915UI83916YABEND04E 00C90101 AT DSNK1CNE ERQUAL 5005 DURING NORMAL DB2 PROCESSING (FTB)
PH479262022-08-05 UI81840UI81841 SQLCODE510 MAY BE ISSUED FOR AN AMBIGUOUS CURSOR WITH A COMMON TABLE EXPRESSION CTE
PH479522022-08-04 UI81820UI81821YINCREASED DB2 DIST ADDRESS SPACE CPU USAGE MAY BE OBSERVED AFTER PH40244 / UI80196 22/07/15 PTF
PH48013  OPENOPEN(Y)EXCESSIVE DB2 STORAGE BEING ALLOCATED BY BUFFER MANAGER IN ADMF GLOBAL CL1 POOL. THIS IS FOUND IN CSA/ECSA CAUSES OUT_OF_CSA
PH480732022-08-24 UI82094UI82095YWHEN UPDATE SET CLAUSE CONTAINS A SCQ WITH A MINUS SIGN, AN INCORROUT OR AN ABEND (ABEND0C4 AT DSNXGDT2 OFFSET00980) MAY
PH480782022-08-29 UI82129UI82130 FTB SIZE EXCEEDS THE VALUE OF ZPARM INDEX_MEMORY_CONTROL
PH480852022-08-17 UI81980UI81981 ABEND04E RC00E70005 IN DSNXRITV M106 CAN OCCUR FOR A TRIGGER
PH480992022-11-09 UI83218UI83219 POOR QUERY PERFORMANCE MAY OCCUR FOR A QUERY THAT COULD TAKE ADVANTAGE OF REVERSE INDEX SCAN
PH481392022-08-22PE PH49206UI82068UI82069YDB2 ABEND0C4-3B IN DSNWARDS OFFSET 00C5C (Caused by PH44840 UI81105)
PH481492022-09-07 UI82301UI82302 DIS STATS(ITC) DOES NOT CALCULATE INDEX TRAVERSE COUNT WHEN FTB FUNCTION IS DISABLED.
PH482612022-10-19 UI82887UI82888YINCORRECT OUT MAY OCCUR FOR A QUERY REFERENCING A VIEW OR CTE AND USING SET FUNCTION IN THE PREDICATE.
PH482892022-12-27 UI83868UI83869YDB2 CRASH WITH ABEND 00D10231
PH48384  OPENOPEN(Y)ABEND04E RC00C90101 IN DSNGEDM.DSNGEDYI:0000 CAN OCCUR WHEN RUNNING SQL WITH DYNAMIC PLAN STABILITY ENABLED
PH484932022-10-19 UI82898UI82899 ABEND04E RC00E72018 AT DSNXSING P040 MIGHT HAPPEN FOR A QUERY SATISFYING SOME CONDITIONS
PH486022022-09-06 N/AUI82284YINCORRECT OUTPUT CAN OCCUR WHEN AN EXPRESSION-BASED INDEX IS USED
PH487052022-12-12 UI83696UI83697YTABLE SCAN COULD MISS SOME RECORDS FOR HASH OBJECT DEFINED WITH PAGE LEVEL LOCKING
PH492062022-09-16 UI82437N/A DB2 ABEND0C4-00000004 IN DSNWARDS AT OFFSET 03846
PH493172022-11-11 N/AUI83270YSPRC VERSION INCORRECT
PH494422022-10-19 UI82901UI82902YSLOW DB2 SHUTDOWN DUE TO CHECKPOINT FOR WORKFILE
PH49479  OPENN/A(Y)DSNT225I BIND ERROR FOR PACKAGE DEPLEVEL OPTION NOT SUPPORTED RECEIVED AT A V13R1M501 OR LOWER DB2 FOR Z/OS SERVER
PH495432022-10-27 UI83046UI83047 ABEND04E RC00E70005 DSNXOEXB M200 OR SQLCODE206 FOR UPDATE OF VIEW W/ INSTEAD OF TRIGGERS AND UPDATE STMT CONTAINS CORRSUBQ
PH496022022-12-06 UI83591UI83592 SPARSE INDEX MIGHT BE CHOSEN AND CAUSE PERFORMANCE REGRESSION BECAUSE OF COST ESTIMATED TOO HIGH WITH NO PENALTY.
PH496192022-11-18 UI83368UI83369YSELECT REPLACE BIF HAVING MORE THAN 4K SEARCH STRING CAUSES DSNOLIKE ABEND0C4 PIC38 DUE TO AN OVERLAY.
PH496742022-11-21 UI83388UI83389YINCORRECT OUTPUT MAY OCCUR WHEN AN EXPRESSION-BASED INDEX IS USED WITH BETWEEN CLAUSE.
PH497212022-10-21 UI82954UI82955 SQL PROCEDURE, HAVING SIGNAL STATEMENT WITH XML TYPE VARIABLE REFERENCED IN MESSAGE_TEXT, GOT ABEND0C4 PIC4 AT DSNOGETD+028CE
PH497592022-11-23 UI83438UI83439 ACCESS PATH REGRESSION
PH497922022-12-07 UI83619UI83620 THE ABEND RC00E70005 IN DSNXGSFN MAY HAPPEN WHEN A SQL STATEMENT CONTAINS OUTER JOINS AND A CCSID CAST ON A FOR BIT DATA COLUMN.
PH49825  N/AOPEN ORPHANED ROWS IN SYSIBM.SYSPACKDEP AFTER DROP VIEW
PH499012022-12-14 UI83755UI83756 ABEND04E RC00E70005 IN DSNXRFMG M110 MAY OCCUR FOR SQL MERGE STATEMENT
PH499292022-12-21 UI83841UI83842 A LESS EFFICIENT INDEX MAY BE SELECTED WHILE ANOTHER INDEX CAN PROVIDE BETTER FILTERING.
PH499722022-11-18 N/AUI83371 DB2 13 FOR Z/OS NEW FUNCTION (PARENT_PLANNO usage in PLAN_TABLE for VIEW or Table Expression)
PH501292023-01-05 UI83939UI83940YABND=04E-00D31010 DSNLILLM.DSNLCDG2:0001 OR DSNLCDG2:0005
PH502392022-12-23 UI83861UI83862 DISCOURAGE THE USE OF SPARSE INDEX WHEN INNER CTE OR VIEW SIZE IS BIG WITH THE HUGE ESTIMATED ROWS AMOUNT AND SMJ IS ELIGIBLE.
PH503672023-01-20 UI90201UI90202 DSNT286I FOR REBIND PACKAGE WITH APREUSE(WARN) EVEN THOUGH RESULTING ACCESS PATH IS THE SAME
PH503822022-11-30 N/AUI83522YABEND 0C4 IN DSNVEUS1 AT OFFSET E34
PH50410  OPENN/A DIAGNOSTIC APAR TO PRODUCE A DUMP WHEN MSGDSNI055I IS ISSUED INDICATING IAG2 HAS BEEN DISABLED.
PH504992022-12-01 UI83543UI83544YABEND 04E RC00C90101 DSNILKTO ERQUAL1002 (After restart from disk failure – Local fix Conditional Restart with FORWARD=NO)
PH505832023-01-06 UI83947UI83948 DEGRADED PERFORMANCE FOR SOME QUERIES WITH A RANGE PREDICATE WITH BIFS: YEAR, DATE, OR SUBSTR ON THE LEFT HAND SIDE
PH506242023-01-23 UI90224UI90225 DSNIDLDU:5016 ABEND DURING LOAD RESUME IN DB2 12.
PH506272022-12-12 UI83688UI83689YABEND04E RC00C200D8 DSNB1SWS 22/11/02 PTF PECHANGE
PH507292023-01-09 UI83963UI86964 DB2 FOR Z/OS PROFILE (IFCID 402) STATISTICS ENHANCEMENT
PH507812023-02-13 UI90503UI90504YDB2 RECOVERY-LOOP WITH REPETITIVE ABEND0C4 RC10 AT DSNXRIHB OFFSET2D490 AT LEVEL UI65759
PH508822022-12-21 UI83844UI83845 ABEND04E RC00E70005 AT DSNXOIXP P030 COULD HAPPEN WHEN AN SQL STATEMENT CONTAINS A VIEW AND HUGE NUMBER OF PREDICATES
PH509592023-02-16 UI90575UI90576YSELECT ENTERED INTO A LOOP BETWEEN DSNKTRAV AND DSNKNXT2 IN FTB PROCESS (Non-unique support problem)
PH509732022-12-28 N/AUI83889 LOOP OCCURS IN DSNXOEXB DURING THE PREPARE OF A QUERY WHICH CONTAINS OUTER JOINS AND AN IN SUB-QUERY PREDICATE
PH509742022-12-27 N/AUI83883YABEND0C4 DSNURLOG+049D0 REORG TABLESPACE SHRLEVEL CHANGE DURING PBG TO PBR CONVERSION
PH509972022-12-28 UI83884UI83885 ABEND04E RC00E70005 AT DSNXGDT2 M205 FOR QUERY ON VIEW
PH51086  OPENN/A(Y)ABND=04E with RC00E50053 or RC00E20042 leads to DB2 crash with RC00E50727.
PH511082023-01-04 UI83921UI83922YACCESS DATABASE() SPACE() MODE(STATS) COMMAND DOES NOT UPDATE LASTDATACHANGE ON THE PARTITION WHICH IS CLOSED BY 010.PM2PCP01
PH511812023-02-08 UI90445UI90446YSERVICEABILITY APAR TO TAKE DIAGNOSTIC DUMP TO PREVENT DUPLICATEQWE PUT ON CHAIN LEADING TO LOOP IN PROCESSING
PH512642023-01-30 UI90327UI90328 ABEND04E RC00C90101 AT DSNIBCTD ERQUAL5007 MAY OCCUR FOR EXPLAIN AN SQL STATEMENT
PH51404  OPENN/A ALTER TABLESPACE MOVE TABLE MAY INPROPERLY HANDLE 3-PART TABLE NAMES
PH515652023-02-13PE PH52893N/AUI90510 FTB NEW FUNCTION
PH518602023-01-31 UI90345UI90346 ABEND04E RC00E70005 IN DSNXOUWF P009 MAY OCCUR FOR A QUERY WITH A SCROLLABLE CURSOR AND OUTER JOIN
PH520512023-02-16 UI90584N/A QUERY USING LISTAGG FUNCTION MAY ISSUE SQLCODE -137 DESPITE THAT PROPER LENGTH WAS PROVIDED ON INPUT.
PH52112 NewOPENN/A(Y)ABND0C4-04 IN DSN3AMGP.DSN3AUCM AT +00AF4
PH52126 NewOPENN/A(Y)QUERY RECEIVES SQLCODE -312 FOR A MERGE STATEMENT WITH HOST VARIABLES WHEN BATCH NODE IS ENABLED WITH DB2JCC4 DRIVER
PH521472023-03-01ClosedUI90806UI90807YSTORAGE OVERLAY ABEND04E RC00E2002F IN DSNSVSFM OFFSET00702 AND DB2 CRASH MSGDSNV086E RC00D96001
PH521552023-03-03ClosedUI90770UI90771YINCORROUT MAY OCCUR FOR A QUERY USING OUTER JOIN
PH52180 NewOPENN/A(Y)ABEND04E RC00E3007B DSNTGSCC DSNTABA
PH52273 NewOPENN/A(Y)ABEND0C4 RC38 IN DSNXRINT OFFSET00904 IN ALTER TABLESPACE STMT OF MAXPARTITIONS TO CONVERT AN EMPTY DEFINE NO TABLESPACE TO PBG
PH522752023-03-14ClosedUI90954UI90955YABEND0C4 RC38 IN DSN3EOT0 OFFSET0992
PH522792023-02-24 UI90721UI90722 SQLCODE418 MAY BE RETURNED FOR AN SQL STATEMENT THAT CONTAINS ANXMLTABLE JOINED WITH A VIEW OR TABLE EXPRESSION
PH523972023-03-13ClosedUI90932UI90933 DB2 MAY ISSUE SQLCODE -204 OR ABEND04E RC00E70005 IN DSNXGRDS.DSNXOFL:M110 FOR TUDF CONTAINING COMMON TABLE
PH52455  OPENN/A INLIST PREDICATE PUSHDOWN INTO VIEW OR TABLE EXPRESSION MAY BE INCORRECTLY HANDLED CAUSING ABEND0C4 RC00000010 IN DSNXOPRS+03A58
PH52534  OPENN/A ABEND04E RC00E70005 AT DSNXGRTS:M902 MAY HAPPEN FOR QUERY WITH OFFSET AND UNION ALL WHEN USED IN CURSOR WITH INSENSITIVE SCROLL
PH52590  OPENN/A IFCID0389 CONTAINS AN INCORRECT VALUE FOR QW0389LV (NUMBER OF INDEX LEVELS IN THE FAST TRAVERSAL BLOCK)
PH52650  OPENN/A INCONSISTENCIES AND VARIOUS ABEND04E RC00C90101 RC00C90216 RC00C9021C DUE TO OVERLAY CAUSED BY MASS DELETE
PH52651 NewOPENN/A(Y)DDF ABNORMALLY TERMINATED WITH ABEND04E RC00D30014 IN DSNLMTOK:0001 DUE TO LUWID TOKENS BEING EXHAUSTED.
PH52717  OPENN/A(Y)INCORROUT FOR QUERY REFERENCING COLUMNS THAT HAVE COLUMN MASKS DEFINED AND ONE REFERENCES A GLOBAL VARIABLE
PH528082023-03-14ClosedUI90958UI90959 MSGBPXF024I MESSAGES OCCUR IN DB2 WHEN ENABLING AND DISABLING MULTIPLE PACKAGES VERY QUICKLY IN SQL SCOPE SETTINGS FROM DB2ZAI
PH528932023-03-17New & ClosedN/AUI91002 OBSOLETE FTB MSGDSNI070I MESSAGE DISPLAYED FOR V13 23/02/27 PTF PECHANGE
PH52915 NewOPENN/A ABEND0C4 RC04 AT DSNXRSOR OFFSET00136 FOR QUERY THAT INCLUDES OUTER JOIN AND INNER JOIN, SUBQUERY PREDICATE AND DISTINCT
PH52988 NewOPENN/A DB2 MAY CHOOSE AN INDEX WITH ONLY MATCHING PREDICATES INSTEAD OF A BETTER PERFORMING INDEX WITH MATCHING&SCREENING PREDICATES
PH53069 NewN/AOPEN(Y)ABEND05E RC28 AND DB2 ABNORMAL TERMINATION RC00E50054 FOR SQL DATA INSIGHTS QUERY RUNNING FROM A DDF THREAD
PH53084 NewOPENN/A ABEND04E RC00C90101 IN DSNK1CNE 5005
PH53237 NewOPENN/A INEFFICIENT ACCESS PATH COULD BE CHOSEN FOR A SQL STATEMENT WITH CSWL ENABLED COMPARING TIMESTAMP(6) COLUMN WITH LITERAL VALUE.
PH53426 NewOPENN/A AFTER SPLITTING OF LAST PBR PARTITION (ALTER+ADD PARTITION), QUERY USING PARTITIONED INDEX ACCESS MIGHT NOT RETURN ALL ROWS.

If you have any comments or wishes please feel free to contact me!

TTFN,

Roy Boxwell

Posted on

2021-12 What’s in a Log anyway?

Hi! This month I wish to briefly delve into the inner workings of the D2b Log. The topic is very broad and complex and so this newsletter is only really skimming the surface of this topic!

What is the Log?

The Db2 Log is the central area where all data changes, plus a whole lot more, gets written away by Db2 as it does its normal work. Most shops allocate several large VSAM datasets for the Active Logs which, when full, get written off to Archive Logs.

Rules of the Log

How much Log do you need? The golden rules are all time based:

1) At least 24 hours of coverage on your Active Logs

2) At least 30 days of coverage on your Archive Logs

Any less and you could seriously run into trouble, breaking the 24 hour rule means that possibly normal ROLLBACKs might suddenly be requesting tape mounts which is not pretty and breaking the 30 days rule might put you into a world of pain when data sets get migrated off and scratched in a recovery scenario.

Take Care of Your Logs!

These Logs must be looked after and cared for as they save your company’s life on a daily and hourly basis. Normally, no-one really cares about the logs as they just “work” and that’s it! However, the Db2 Log is actually a bit of a bottleneck these days.

Remember LOG NO?

Many years ago, Roger Miller said “LOG NO datasets will be implemented in DB2 over my dead body” as he was pretty adament that LOG NO was a bad idea. The driver behind the requirement was just the sheer size of the logs being written by Db2 and the belief that writing fewer logs would make application faster.

How Many?

When you look at the history of Db2 you can see it started with between two and 31 active logs and between 10 and 1000 archive logs. We all thought “that will be more than enough”. Nowadays we have between two and 93 active and between 10 and 10,000 archives! Some shops rotate through their six byte RBAs in 24 hours and go through 1000’s of logs so we have grown up a little bit!

Before it Hits the Log…

Remember that there is another really important ZPARM that affects the LOG before the log is even hit – OUTBUFF it started out at a value between 40K and 4000K and is now between 400(K) and 400000(K). Just set it to the highest value you can! Db2 will always look here first before even looking at the active log and so if the data is here it is much faster than VSAM access!

Bottleneck?

Some customers were convinced that one area that was slowing down Db2 was the log write and the externalization of the data within. Roger was 100% against this but even he lost this battle and so the LOG NO space was born. However, if you ever do a ROLLBACK then the space goes into COPY pending which is horrible!

Checkpoint Charlie

The number of system checkpoints also has a direct impact on log size and usage. You must decide whether you wish to go time based (IBM-recommended way is three minutes) or transaction based – or even a mix of the two methods. I am in favour of keeping my life simple so I would always recommend the checkpoint every three minutes rule. You could argue that at “quiet times” overnight too many checkpoints will be taken, but I counter that argument with “When was the last time you ever had a quiet time on your machine?”

Index Split?

Believe it or not, index splits seem to take up some 55% of the Db2 Log at one shop I visited. We decided to try different index pages sizes , including compression, and this all really helped in reducing the log load pressure. This has a direct effect on DASD, elapsed and CPU time which was an allround winner!

Crystal Ball?

So, how do you look into the Db2 Log? If you have no tooling then you only get the IBM stuff which is – shall we say – basic. It was here that I decided to write a little COBOL program, that I have called Db2 Archive Log Viewer for Db2 z/OS, that would read all of the Archive logs (No VSAM – keep it simple!) and handle normal non-spanned log records to give me a glimpse into what on earth Db2 was writing into the darn things!

Log HealthCheck

So what does Db2 Archive Log Viewer for Db2 z/OS then do? Well, it reads as many Archive Logs as you can give it and reports on the contents of the Logs in absolute numbers and in size. Using this data enables you to get a new view into what your Db2 system is actually doing and who is causing all your logs to be written.

Surprise!

I was pretty surprised by what I saw inside my logs and I hope you find it interesting to peer inside your logs!

If you have any ideas or desires about log datasets, feel free to email me!

TTFN

Roy Boxwell

Posted on

GIVE and TAKE Program

1,2,3

Give and Take 2020

Information on the Give and Take 4,5,6,7

Previous Give & Take

We have “GIVEn” various free-of-charge Use Cases from our SQL WorkloadExpert for Db2 z/OS like:

1 Index Maintenance Costs

2 EXPLAIN Suppression

3 BIF Usage

Limited free-of-Charge Db2 Application

This Program started in Europe, during our 30th anniversary was such a success, that it is now being Extended for the benefit of North American Db2 z/OS sites.

<a href="http://www.seg.de/produkte/db2-zos-produkte/sql-workloadexpert-for-db2-zos/" target="

Index Maintenance Costs, EXPLAIN Suppression, BIF

Limited free-of-Charge DB2 Application

This Program started in Europe, during our 30th anniversary was such a success, that it is now being Extended for the benefit of North American DB2 z/OS sites.

SQL WorkloadExpert™ for DB2 z/OS (WLX) contains several “Use Cases”. We provided three of them, free of charge, for one month to different sites. In return, we received their results. We’d like to share this with you now.

We have “GIVEn” various free-of-charge Use Cases from SQL Workload Expert for DB2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage – This last one is still available		We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions

Inspiring experiences

Customer Statements

3BiF USAGE
News		Read the Customer Comments across the Industry 

 

  • Health Care
  • Insurance
  • Banking
  • Car Manufacturing

First results from DB2 z/OS sites

1Index Mantenance CostsNearly all the data we got back showed a positive result for created Indexes…
2EXPLAIN SuppressionAbout 10% of SQLs are actually “left over”…
3 BIF Usage When migrating to a new DB2 version, the BIFs are not always compatible and an extreme amount of data is produced.

 

The difficulty of near-time analysis to track down BIFs within dynamic SQL have been solved with the BIF Usage Use Case…

[Results from DB2 z/OS sites]

Program 3 – BIF Usage –  has now started

BIF-Usage

 

Presentation

BIF CompatibilityDB2 10 compatibility mode
Changes to the STRING formating of decimal data within the CHAR and VARCHAR built-in function and to the CAST specification with CHAR and VARCHAR result types as well as  UNSUPPORTED TIMESTAMP STRINGs.
White PaperFinding BIFsAnd How to Lead a Problem-Free Life With Them in the Future
Navigating the Challenges of moving to a new DB2 Release
Newsletter2015-01 – BIFCIDS – Where’s the BIF?How will you deal with loop-hole usage in production code?
VideoBIF Usage(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon

BIF Usage video

Posted on

2014-01: Complementing IBMs ACCESS PLAN Stability 1, for Dynamic SQL

 

In collaborazione con Expertise4IT

Con il package di salvataggio globale delle RUNSTATS implementato da SEG: Per quale ragione questa buona idea in qualche caso fallisce?

 

• La storia
• Cos’è accaduto realmente?
• Come risolvere il problema?
• Rescue Steps
• Un esempio (comprensivo di pannelli JCL)
• Il prossimo mese
Questo mese vi voglio raccontare una storia. La storia è vera, ma I nomi sono stati cambiati per proteggere gli innocenti

 

 

La storia

Una grande azienda schedula regolarmente la fasatura dei processi di produzione nella notte di Giovedì. Lo scorso anno, una notte tutto procedeva regolarmente ma … al Venerdì mattina …….

…i telefoni cominciarono a squillare perchè gli utenti si lamentavano di tempi di risposta lunghi o nulli (timeout) in un’applicazione piuttosto critica per il business.

Il problema rapidamente evolve dai normali ‘spegnete e riavviate’ e ‘avete cambiato qualcosa?’ ai senior managers che chiedono per quale motivo le cose non funzionano più.

Allo stato il Gruppo DBA non era stato ancora coinvolto, dal momento che il primo pensiero era che si trattasse di un “bad” package passato in produzione accidentalmente nella notte.

  • Il Gruppo di gestione della produzione, allora, riportava indietro I packages in gioco, ma l’operazione non produceva effetti….
  • I ritardi di consegna diventavano peggiori. Vengono quindi ‘stoppati’ quasi tutti i server WebSphere, almeno per permettere che un pò di lavoro venisse eseguito sul sistema sovraccarico. Essendo ormai giunti al livello di panico, i DBA and il team JAVA vengono coinvolti.
  • E questi trovano rapidamente il colpevole; si trattava di un SQL dinamico molto complesso che si era ‘comportato’ correttamente e con efficienza fino ad un certo istante della notte di Giovedì, e adesso performava in maniera disastrosa.
  •  I DBA riorganizzano le tabelle più consistenti fra quelle in gioco, nella speranza che le cose vadano meglio, ma, purtroppo, non ci sono risultati apprezzabili….

    Finalmente il DBA propone la creazione di un indice con relative RUNSTATS in produzione.

L’access path si modifica sostanzialmente e le performance tornano ad essere buone.
I server WebSphere vengono restartati e, gradualmente, si torna alla normalità.
Tutto questo processo é durato 2 giorni!
L’azienda deve rifasare la sua catena logistica e le consegne Just-In-Time, quindi il blocco ha avuto qualche seria ripercussione, ovviamente….  E questa è tutta la storia

 

Cosa è realmente avvenuto?

Il DBA, investigando sulle possibili cause del blocco, ha scoperto che ciò che realmente era accaduto, era che era andata in macchina una RUNSTATS solo su una piccola tabella che, fatta girare durante la notte di Giovedì in un momento inopportuno, aveva determinato un passaggio dell’access path allo stato di “pear-shaped”(aka Belly Up) per tutta la giornata di Venerdì e per metà del Sabato…

Come risolvere rapidamente e facilmente il problema?

Il DBA pensò ai modi in cui il problema, che avrebbe potuto riproporsi in futuro, potesse essere risolto in maniera facile e rapida. E qui comincia la mia parte della storia…

Questa azienda utilizza software proprio e possiede una licenza d’uso della componente Enterprise Statistics Distribution (ESD) del Bind ImpactExpert, che estrae, e opzionalmente converte, tutte i dati di catalogo di cui ha bisogno l’ottimizzatore per fare il proprio lavoro. Normalmente, I clienti utilizzano questo tool per copiare le statistiche di produzione in stile ‘sand box’, per vedere se un APAR o una migrazione di DB2 può causare problemi imprevisti. Per questo motivo, essi usano la componente Early-PreCheck del nostro tool Bind ImpactExpert per SQL statico e dinamico. Adesso facciamo un altro scenario, chiamato DSC (Dynamic Statement Cache) Protection, che dovrebbe realizzare quello che desidera il cliente, ma fa anche molto altro e, ovviamente, ha costi più elevati!

Ed è così che nacque l’idea di un tool ‘tascabile’ che abbiamo chiamato RUNSTATS Rescue. Ho sentito che chiedevate “Perchè si chiama PocketTool?”; la risposta è: “Perchè costa quanto il denaro che si tiene in tasca!” (aka Pin Money negliUSA o una “allowance” se preferite). Questi tools sono realmente molto economici!

Adesso, prima che smettiate di leggere questa newsletter e cominciate a lamentarvi del fatto che si tratti di una pura attività di marketing, per favore mettetevi in testa che quanto io descrivo in questo documento potrebbe anche essere stato scritto da voi e, quindi, avete solo bisogno di darmi credito sufficiente per condividere l’idea…

 

RUNSTATS Rescue

L’idea è usare l’EXPLAIN in qualunque modalità, shape or form, sia in SPUFI, che direttamente all’interno di qualsiasi monitor, semplicemente per ‘EXPLAINare’ l’SQL critico, e tenere a mente quale PLAN_TABLE owner state usando e, anche, il QUERYNO che avete appena usato.

Usando questi due inputs, RUNSTATS Rescue analizza l’output dell’ EXPLAIN per costruire una lista di control cards di estrazione e update per il nostro tool ESD, per tutte le tabelle usate e per I relative indici –anche se, ovviamente, non in uso! Infine viene anche generato un flusso di RUNSTATS DSC per tutti I tablespaces coinvolti nella query al fine di avere la certezza che la prossima volta che lo statement critico andrà in macchina, userà le corrette statistiche.

Ora, ovviamente, sorge la domanda: “Come faccio a sapere quail sono le statistiche da usare come Rescue statistics?”

La risposta è: “Quelle che c’erano prima prima che venisse eseguita una REORG con inline statistics o una RUNSTATS “. Questo è il punto chiave da tenere a mente: dovete semplicemente eseguire lo ESD extract prima che venga eseguita qualunque operazione di maintenance sul Database.
Molti siti hanno giorni, o weekends, in cui girano le procedure di maintenance, e non è certo un problema estrarre I dati e salvarli, per esempio,in un GENGROUP, al fine di facilitare l’individuazione di data e time in cui le statistiche erano ‘buone’ e poi, con il job di RUNSTATS Rescue, ripristinare molto rapidamente le statistiche necessarie. Questo consente al gruppo DBA di avere più tempo per capire cos’è realmente successo e apportare le giuste correzioni, quantomeno rimanendo a proprio agio.

10 Rescue Steps

  • 1. Selezionare il nuovo scenario di RUNSTATS Rescue
  • 2. Generare JCL
  • 3. Opzionalmente copiarle in GENGROUP dataset
  • 4. Inserire EXPLAIN TABLE-CREATOR e QUERYNO
  • 5. Lancio automatico del nostro browser di catalogo
  • 6. Drill down fino al livello degli indici
  • 7. Chiedere un “new” file name per le “rescue” statistics estratte
  • 8. Eseguire l’estrazione delle RUNSTATS Rescue
  • 9. Resettare le statistiche ed eseguire le RUNSTATS
  • 10. Le “Rescued” Statistics

 

Di seguito un esempio guidato di cosa avviene in realtà:

 

In basso nella schermata, si può osservare il nuovo scenario delle  RUNSTATS Rescue – Selezionandolo si attiva una finestrella con tre passi. Il primo deve essere eseguito solo una volta e va semplicemente inserito in un job produttivo esistente. Si raccomanda di farne il primo job della normale catena di maintenance del DB2 Database Maintenance.

1 – Select the new scenario RUNSTATS Rescue

 

2 – Generate some JCL

La prima opzione genera alcuni JCL come sotto illustrato:

 

3 – Opzionalmente copiare in un GENGROUP dataset

Esempio di step finale di copia su GENGROUP

 

4 – Insert the EXPLAIN TABLE-CREATOR and QUERYNO

Selezionare il secondo passo per preparare le RUNSTATS Rescue

5 – Lancio automatico della scansione di catalogo

Premendo “enter” si lancia il browser sul catalogo per consentire la vision degli oggetti usati dallo SQL…..

 

6 – Drill down to the Index level

…con I relativi indici, ovviamente!

 

7 – Ask “new” file name for the extracted “rescue” statistics

Uscendo con PF3 il tool chiede il dsname delle Production Statistics originali, come estratte dal job del primo passo, e un  “new” file name per le rescue statistics estratte:

 

 

8 – Perform the RUNSTATS Rescue extraction

Il JCL che esegue l’estrazione delle RUNSTATS Rescue, incluso il passo opzionale su saving su GDG, si presenta come sotto:

 

9 – Reset the statistics and executes the RUNSTATS

Infine, viene selezionato il terzo step, che azzera le statistiche ed esegue la RUNSTATS che provvede al flush della DSC

 

10 – The “Rescued” Statistics

Adesso, la prossima volta che lo statement apparirà, userà le “rescued” statistics e riprodurrà il vecchio Access Path.

 

Nel prossimo mese

Nel prossimo mese desidero espandere la problematica trattata con la possibilità di estenderne la soluzione allo SQL statico.

Il mese successive, scenderò nei dettagli della ‘DSC Protection scenario’ che ho citato prima. Non si tratta di un tool tascabile, ovviamente, ma è molto interessante!

Come sempre questions or comments sono benvenuti,

TTFN Roy Boxwell
Senior Software Architect