Uncategorized Archives - SOFTWARE ENGINEERING

27/09/202329/09/2023

APAR Update

This is a mega-HIPER month with a couple of SQL fixes and a bunch of HIPERs. All checked on the 27th September 2023.

HIPERs:

PH53525 ABEND0C4 RC00000038 IN DSNGESLC OFFSET0226A WHEN A PLAN WAS CANCELLED IN A DATA SHARING ENVIRONMENT

PH54700 REORG SHRLEVEL CHANGE OF DSNDB06.DBD01 LEAVES RESIDUAL RTS ROWS AND ORPHAN OBDS FOR THE MAPPING OBJECTS IN THE DBD

PH55294 AFTER ROLLBACK OF ALTER INDEX PIECESIZE OF NPI, AN OBD FIELD MAY BE SET INCORRECTLY, CAUSING ERROR ON REPAIR DBD REPAIR/TEST

PH55385 ABEND04E RC00C200C6 DSNB5SCM OFFSET04EE0 AFTER CANCEL OF BIND JOB

PH55639 RESOURCE UNAVAILABLE ERROR SQLCODE904 WITH REASON 00000000 DURING INDEX SPLIT AND 00C20023 PAGE LATCH NOT GRANTED

PH55886 LOAD GENERATES INCORRECT TRAILING BLANKS FOR A UNICODE COLUMN IN EBCDIC TABLE.

PH56059 HIGH NUMBER OF SPACEMAP GETPAGE REQUEST CAUSED BY INSERT INTO PBG MEMBER CLUSTER APPEND NO OBJECTS

Db2 12 only PH56078 WITH UI91174 APPLIED FOR DB2 12, DB2 MAY TERMINATE WITH DSNV086E REASON=00E50702 23/08/16 PTF PECHANGE

PH56094 IRLM: SERVICEABILITY APAR TO AID IN IDENTIFYING PLOCK MEMBER HOLDING LOCK

Db2 13 only PH56118 UTILITY JOBS ABNORMALLY TERMINATED WITH 00E20004.

Db2 13 only PH56179 DB2 SERVICEABILITY APAR TO ASSIST IN DIAGNOSING BM PLOCK HANG

PH56366 SERVICEABILITY IMPROVEMENTS FOR OVERLAY DETECTION.

Db2 13 only PH56389 WITH UI91175 APPLIED FOR DB2 13, DB2 MAY TERMINATE WITH DSNV086E REASON=00E50702

Interesting for RTS:

See HIPERs.

RUNSTATS:

None.

Query performance:

PH53968 IMPROVE PERFORMANCE OF STORAGE POOL FOR RMID=BB1RMID

Db2 13 only PH54429 ABEND04E RC00E2000F FOR DSNSVSFB OFFSET 009B8 MAY ISSUE FOR BIND/REBIND USING PLANMGMT WITH APREUSE

PH54783 (This is the PE’d PH46351) REORG WITH STATISTICS INDEX OPTION MISSES STATISTCICS OF SOME KEY COLUMNS COMBINATIONS IN SYSCOLDIST.

IAG2:

PH55078 DIAGNOSTIC APAR TO ADD MORE IAG2 INFORMATION IN THE DIAGNOSTIC LOG RECORDS WHEN DSNI055I IS ISSUED.

PH56318 MESSAGE MSGDSNI055I REPORTS INVALID URID OF DISABLING AGENT

FIT/FTB:

None.

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:

APAR	CLOSED	STATUS	Db2 12	Db2 13	HIPER	Description
PH31124	2021‑01‑28		UI73674	N/A		REBUILD INDEX SHRLEVEL REFERENCE OF DIRECTORY INDEX DSNDB1XA OR DSNDB01X GETS DSNU590I WITH RC00C900AE BUT COMPLETES SUCCESSFULL
PH31498	2021‑01‑06		UI73361	N/A		NO RTS UPDATE DURING RUNSTATS TABLESPACE SHRLEVEL REFERENCE UPDATE ALL IN V12 FL505 AND ABOVE.
PH34807	2021‑03‑29		UI74649	N/A		EXTRA CPU COST OF MASS DELETE
PH35589	2021‑05‑04		UI75212	N/A		AFTER A PIT RECOVER , RTS REORGLASTTIME IS NULL AND DSNACCOX DOES NOT EXTRACT THE OBJECT FOR REORG
PH39503	2021-08-23		UI76887	N/A		ABEND04E RC0090101 IN DSNILKPD:1001 CAN OCCUR ON A QUERY IF STATS SAY TABLE IS EMPTY AND BUFFERPOOL SIZE IS ZERO
PH41480	2021-11-12		UI78060	N/A	Y	ABEND04E RC00C90D01 DSNONLLE ERQUAL53AC DURING DISASTER RECOVERYRECOVER OF DB2 CATALOG AND DIRECTORY
PH45916	2022‑06‑29		UI81253	UI81254		COPYUPDATEDPAGES IS INCORRECT AFTER LOAD WITH INLINE COPY, INCREMENTAL COPY GETS SKIPPED, RECOVER REQUIRES LOG APPLY
PH47090	2022‑08‑08		UI81865	UI81866		SYSIBM.SYSINDEXSPACESTATS(STATSLASTTIME) DOES NOT GET UPDATED FROM LOAD REPLACE WITH INLINE STATISTICS SPECIFIED.
PH51108	2023‑01‑04		UI83921	UI83922	Y	ACCESS DATABASE() SPACE() MODE(STATS) COMMAND DOES NOT UPDATE LASTDATACHANGE ON THE PARTITION WHICH IS CLOSED BY 010.PM2PCP01
PH53837	2023‑06‑08		UI92151	UI92152		RIGHT AFTER START DB2 WITH ZPARM RETRY_STOPPED_OBJECTS YES, STOP DB(DSNDB01) SP(*) NEEDS LONG TIME TO COMPLETE.
PH54700	2023‑09‑07	Closed	UI93485	UI93486	Y	REORG SHRLEVEL CHANGE OF DSNDB06.DBD01 LEAVES RESIDUAL RTS ROWS AND ORPHAN OBDS FOR THE MAPPING OBJECTS IN THE DBD
PH57012		New	OPEN	OPEN		THE NLEVELS COLUMN IN THE SYSIBM.SYSINDEXSPACESTATS CATALOG TABLE HAS A HIGH VALUE.

RUNSTATS APARs:

APAR	CLOSED	STATUS	Db2 12	Db2 13	HIPER	Description
PH32448	2021‑01‑07		UI73367	N/A		RUNSTATS UTILITY IS NOT COLLECTING THE SYSCOLDISTSTAT THAT IS BEING SPECIFIED (EG. FREQVAL COUNT 10 MOST)
PH33226	2021‑02‑26		UI74173	N/A	Y	DB2 BUFFER MANAGER OPEN PAGESET STORAGE LEAK
PH34123	2021‑03‑01		UI74210	N/A		RUNSTATS DOES NOT COLLECT DISTRIBUTION STATISTICS FOR SINGLE COLUMN COLGROUP FOR SEGMENTED MULTI-TABLE TABLE SPACES
PH35267	2021‑06‑01		UI75649	N/A		RUNSTATS DOES NOT UPDATE COLUMN SYSCOLDIST.CARDF CORRECTLY WHEN NUMCOLUMNS > 1
PH37545	2021‑07‑14		UI76330	N/A		RUNSTATS ABENDS0CF RC0000000F IN DSNUSEOF.
PH40189	2022‑03‑30		UI79958	N/A	Y	RUNSTATS AT PARTITION LEVEL GOT ABEND04E RC00E40213 WITH CAUSE 00C9004F
PH43503	2022‑03‑31		UI79966	N/A		RUNSTATS TABLESPACE REGISTER NO FAILS WITH RC00C90637 OR ABEND04E WITH RC00C90101 AT DSNIOW ERQUAL 2002
PH44246	2022‑04‑01		UI79986	N/A	Y	ABEND04E RC00E2000F DSNSVSFB+00A2A DURING A RUNSTATS TABLESPACE WITH EXCLUDE NULL KEYS INDEX
PH45533	2022‑05‑20		UI80643	N/A		INCORRECT VALUE OF CARDF IN SYSIBM.SYSCOLDISTSTATS WHEN RUNSTATS COLLECTS KEYCARD STATISTICS ON DPSI INDEX
PH46351	2022‑06‑01	PE PH54783	N/A	UI80817		INCORRECT VALUE OF CARDF IN SYSIBM.SYSCOLDIST WHEN RUNSTATS COLLECTS KEYCARD STATISTICS ON DPSI INDEX
PH46767	2022‑08‑31		UI82201	UI82202		DROP INDEX LEAVES ORPHAN ROWS IN SYSIBM.SYSCOLDIST
PH47183	2022‑07‑19		UI81531	UI81532		RUNSTATS USE PROFILE GOT TIMEOUT ON WAITING SYSCOLDISTSTATS TABLE X LOCK DURING LOCK ESCALATION.
PH48407	2022‑09‑02		UI82237	UI82238		RUNSTATS AT PARTITION LEVEL GETS ABEND04E RC00E40213 WITH CAUSE 00C9004F
PH49119	2022‑11‑04		UI83135	UI83136		COLCARDF IN SYSIBM.SYSCOLUMNS IS INCORRECT (RESET TO ZERO) AFTER A REORG AT PARTITION LEVEL USING INLINE STATISTICS
PH49410	2022‑11‑07		UI83157	UI83158		ABEND04E RC00C90101 AT DSNIOW ERQUAL2002 DURING RUNSTATS SHRLEVEL CHANGE REGISTER NO
PH51750	2023‑05‑16		N/A	UI91849		UTILITY HISTORY IS NOT COLLECTED WHEN A UTILITY TARGET IS AN INDEX AND THE INDEX CREATOR IS OMITTED.
PH51766	2023-03-29	PE see PH56078 PH56389	UI91174	UI91175	Y	EXCESS STORAGE CONSUMPTION BY THOUSANDS OF 022.WDFND THREADS
PH51901	2023‑02‑22		UI90670	UI90671		RUNSTATS INVALIDATECACHE FUNCTION CAUSES ACCESSPATH PROBLEMS
PH51905	2023‑05‑11		UI91777	UI91778		PCTROWCOMP SET BY RUNSTATS MIGHT BE INACCURATE
PH52439	2023‑05‑12		UI91785	UI91786		TERM UTILITY COMMAND HANGS
PH53677	2023‑06‑08		UI92149	UI92150		ABEND04E RC00C90205 DSNIOST2 ERQUAL504E ON A SELECT – Actually a parallel DELETE and RUNSTATS
PH55076	2023‑07‑07		UI92645	UI92646		RECURRING ROW WITH REASON=CONFLICT IN SYSIBM.SYSSTATFEEDBACK AFTER RUNSTATS
PH55296	2023‑07‑22		UI92859	UI92860		RUNSTATS UPDATE PROFILE MESSAGE MSGDSNU1360I DISPLAYED IN OUTPUT
PH55914	2023‑08‑16		N/A	UI93217		NEW FUNCTION (actually two RUNSTATS bugs!)

SQL PERFORMANCE and general HIPERs:

APAR	CLOSED	STATUS	Db2 12	Db2 13	HIPER	Description
PH11864			OPEN	OPEN		SQL SELECT RETURNS INCORROUT AFTER TABLE WITH LOB COLUMNS IS UPDATED
PH21793	2021-01-05		UI73335	N/A		ABEND04E RC00E2000D AT DSNXESTR DSNSVSFB OFFSET00A18
PH26180			OPEN	OPEN		CARTESIAN PRODUCT RESULTING FROM INCORRECT JOIN ORDER
PH26498			OPEN	OPEN	(Y)	ABEND04E RC00C90101 DSNISFPI ERQUAL5019 WHEN USING INSERT ALGORITHM 2 IAG2
PH27840	2021-02-05		UI73843	N/A		INCORROUT FROM QUERY THAT REFERENCES A LARGE VARCHAR FOR BIT DATA COLUMN AS A JOIN PREDICATE FOR SORT MERGE JOIN
PH28731	2021-01-07		UI73383	N/A		ABEND04E RC00C90101 AT DSNIDM DSNISRID ERQUAL500A OCCURRED FOR A SECOND FETCH FROM A ROWSET CURSOR
PH28810	2021-01-28		UI73671	N/A		MSGDSNT286I INDICATES APREUSE WAS NOT SUCCESSFUL WHEN THE ACCESSPATH BEING REUSED CONTAINS CORRELATED SPARSE INDEX IN TBL EXPR
PH29918	2021-01-19		UI73538	N/A		ABEND0C7 AT DSNXRDEC OFFSET8960 MAY BE ISSUED FOR A QUERY THAT REF A VIEW OR AN SQL TABLE UDF THAT CONTAINS RECURSIVE CTE
PH30396	2021-01-06		UI73347	N/A		OPTIMAL ACCESS PATH MIGHT NOT BE USED FOR A JOIN QUERY WHICH CONTAINS BOTH LOCAL PREDICATE AND JOIN PREDICATE ON SAME COLUMN.
PH30978	2021-06-01		UI75643	N/A		SUBSYSTEM PARAMETER TO ENABLE INDEX IN-MEMORY OPTIMIZATION (FTB) FOR NON-UNIQUE INDEXES
PH31118	2021-01-06		UI73357	N/A		INEFFICIENT JOIN ORDER AND JOIN TYPE CAN BE SELECTED AFTER QUERYBLOCK MERGE
PH31146	2021-01-20		UI73548	N/A		ABEND0C4 RC0000003A RC04 PIC3A DSNSVSVB +00CB0 FOR THE QUERY WITH HUGE NUMBER OF UDFS
PH31490	2021-01-06		UI73359	N/A		ABEND0C4 DSNXRSOR +146 @ UI62958
PH31566	2022-01-12		UI78898	N/A	Y	USER RECEIVED AN UNEXPECTED ABEND04E RC00E70100 IN DSNXGRDS DSNXISB7 P110 ON AN INSERT STATEMENT
PH31621	2021-01-07		UI73365	N/A		POOR PERFORMING QUERY WHEN DB2 SELECTS INDEX + LIST PREFETCH AP FOR LEADING TABLE WHILE INDEX IS CLUSTERING AND CAN AVOID SORT
PH31680	2021-02-15		UI73982	N/A		POOR PERFORMANCE CAN OCCUR FOR A QUERY WITH TABLES HAVING DIFFERENT CCSID’S
PH31775	2022-01-05		UI78823	N/A	Y	A LINEAR INDEX WITH > 255 PIECES IS NOT RECOVERED BY RESTORE SYSTEM UTILITY
PH31796	2021-01-08		UI73397	N/A	Y	A QUERY USING DEGREE=’ANY’ MAY HAVE IDENTICAL ROWS RETURNED
PH31872	2021-02-02		UI73756	N/A		AN INEFFICIENT ACCESS PATH IS CHOSEN AFTER A TABLE EXPRESSION ISMERGED
PH32097	2021-02-02		UI73760	N/A		THE QUERY USING OFFSET CLAUSE WITH UNION AND HOST-VARIABLE CAN RETURN EMPTY RESULT SET
PH32355	2021-02-12		UI73972	N/A	Y	INCORRECT RESULT WITH NO ROWS RETURNED FOR QUERY FROM TABLE FUNCTION USING LITERAL
PH32376	2021-01-06		UI73362	N/A	Y	ADDITIONAL TAG SORT AND SORT POOL VERIFICATION.
PH32468	2021-01-20		UI73559	N/A		INEFFICIENT ACCESS CAN BE CHOSEN FOR A QUERY CONTAINING A FF1R CLAUSE
PH32661	2021-01-07		UI73394	N/A		ABEND04E RC00E70005 AT DSNXEFDA OFFSET035FC FOR A QUERY THAT REQUIRES A VERY LARGE RID SORT.
PH32688	2021-03-03		UI74227	N/A		POOR PERFORMANCE CAN OCCUR FOR A QUERY WITH FF1R.
PH32690	2021-02-18		UI74060	N/A	Y	INCORROUT WHEN A SAME EXPRESSION ALIAS APPEARS IN THE SELECTION LIST MULTIPLE TIMES AND THIS QUERY RUNS ON PARALLELISM
PH32726	2021-03-03		UI74226	N/A		POOR PERFORMANCE CAN BE SEEN WHEN A QUERY CONTAINS MULTIPLE PREDICATES.
PH32982	2021-01-20		UI73560	N/A		POOR PERFORMING SQL QUERY WHEN THE QUERY HAS MULTIPLE RANGE OR BETWEEN PREDICATES WHICH QUALIFIED MULTI-INDEX ACCESS
PH32985	2021-02-03		UI73782	N/A		ABND0C4 REASON00000004 AT DSNXGRDS DSNXOLIT OFFSET004DC MAY OCCUR FOR QUERY WITH STATEMENT CONCENTRATION ENABLED
PH33128	2021-02-10	PE PH41952	UI73918	N/A		POOR PERFORMANCE CAN OCCUR FOR A QUERY CONTAINING A FFNR
PH33196	2021-04-19		UI74989	N/A		PERFORMANCE ISSUE WHEN SPARSE INDEX WAS CHOSEN AND THERE EXISTS PREDICATES THAT ARE CAST TO VERY LONG LENGTHS
PH33285	2021-03-11		UI74393	N/A		INCORRECT ACCESS PATH WHEN A VALUE COMPARE OR A BETWEEN PREDICATE WITH ARITHMETIC EXPRESSION ON DECIMAL VARIABLES AND A
PH33339	2021-03-03		UI74234	N/A		LACK OF IFCID 376 REASON 3 (TIMESTAMP) IN DB2 V12 TRACE WHEN APPLCOMPAT = V10R1 AND BIF_COMPAT IS NOT CURRENT
PH33414	2021-03-29		UI74634	N/A	Y	RECOVER UTILITY DOES NOT CORRECTLY RECOVER THE COPY YES INDEXES CAUSING DATA/INDEX MISMATCH
PH33443	2021-02-10		UI73921	N/A		ABEND04E RC00C900D0 AT DSNXROHB OFFSET23DC4 MAY OCCUR FOR NOT NULL LOB/XML COLUMN INVOLVING IN OUTER JOIN
PH33636	2021-03-08		UI74325	N/A		INCORRECT OUTPUT FOR AN SQL STATEMENT THAT REFERENCES A VIEW WITH MULTIPLE OUTER JOINS
PH34045	2021-03-15		UI74443	N/A	Y	INCORRECT SORT ORDER IN OUTPUT OF SELECT STATEMENT
PH34046	2021-03-29		UI74636	N/A		INCORROUT FOR A QUERY WITH EXISTS PREDICATE IN CASE EXPRESSION AND OUTER JOINS
PH34056	2021-04-02		UI74752	N/A		ABEND04E RC00E70005 AT DSNXOW2D P333 MAY HAPPEN WHEN UPDATING A VIEW WITH AN ISNULL PREDICATE
PH34096	2021-02-16		UI74002	N/A		ABEND0C4 RC38 DSNXOGA OFFSETAE6C AE6C WHILE RUNNING COMPLEX QUERY
PH34178	2021-05-03		UI75206	N/A		SUB-OPTIMAL ACCESS PATH CAN BE CHOSEN FOR THE QUERY
PH34189	2021-04-07		UI74812	N/A		ABEND04E RC00C90101 AT DSNIDM DSNIWNRF ERQUAL5006 MIGHT HAPPEN WHEN A QUERY WITH LEFT OUTER JOIN AND SORT MERGE JOIN IS USED
PH34241	2021-04-12		UI74891	N/A		ABEND0C4 RC38 DSNXECST OFFSET09F6 09F6 WHILE RUNNING COMPLEX QUERY
PH34247	2021-03-12		UI74421	N/A		INEFFICIENT INDEX CHOSEN IN A RANGE-LIST ACCESS PATH.
PH34292	2021-06-23		UI76040	N/A	Y	ROLLBACK AFTER ALTER TABLE ALTER COLUMN SET WITH DEFAULT DOES NOT RESTORE PRIOR DEFAULT VALUE
PH34425	2021-03-08		UI74327	N/A		INCORRECT OUTPUT OF CASE WHEN COMPARING VARCHAR COLUMNS
PH34465	2021-04-16		UI74966	N/A	Y	ABENDS0C4 IN DSNIASFP OFFSET001A8 DURING INSERT WORKLOAD USING INSERT ALGORITHM 2 – IAG2
PH34468	2021-04-20		UI75007	N/A		ABEND04E RC00C90101 AT DSNKTRAV ERQUAL5021 VIA FTB TRAVERSAL
PH34584	2021-03-30		UI74673	N/A	Y	ABEND04E AT DSNXOB2 M105 ON A REBIND PACKAGE 21/03/23 PTF PECHANGE
PH34642	2021-05-17		UI75437	N/A		INCORROUT WRONG ROWS RETURNED FOR A QUERY WITH MULTIPLE JOINS WITH NOT EXISTS CORRELATED SUBQUERY
PH34689	2021-04-02		UI74759	N/A	Y	INCORRECT OUTPUT FROM SELECT WHEN NULL HOST VARIABLE WAS USED IN A PREDICATE AGAINST NOT NULL COLUMN
PH34807	2021-03-29		UI74649	N/A		EXTRA CPU COST OF MASS DELETE
PH34859	2021-05-05		UI75254	N/A		DB2 12 FOR Z/OS NEW FUNCTION FOR FTB (FAST TRAVERSE BLOCKS)
PH34903	2021-03-17		UI74490	N/A		ABEND04E RC00E70005 DSNXOFL M120 OCCURS ON UNION ALL
PH34908	2021-04-13		UI74918	N/A		ABEND0C4 RC04 IN DSNXSRME OFFSET061A FOR SELECT USING TABLE EXPRESSION AND HYBRID JOIN
PH35080	2021-03-23		UI74586	N/A		SQLCODE16061 ISSUED FOR A QUERY USING XMLCAST
PH35088	2021-04-07		UI74809	N/A	Y	HIGH CPU DUE TO LONG PREPARES AND LOW HIT RATIO IN DYNAMIC STATEMENT CACHE WHEN DYNAMIC SQL PLAN STABILITY IS ENABLED
PH35464	2021-04-20		UI75013	N/A		INEFFICIENT INDEX CHOSEN BETWEEN COMPETING INDEXES WHEN ONE CANDIDATE PROVIDES INDEX ONLY
PH35596	2021-04-07		UI74814	N/A	Y	INSERT SPLITTING PAGE INTO FTB LEAF NODE GOT DSNKFTIN:5002 ABEND BECAUSE OLD PAGE THAT CAUSE THE PAGE SPLIT WAT MISSING IN FTB.
PH35872	2021-06-17		UI75961	N/A	Y	ABEND0C4 RC4 DSNURWBF+0B012 IN PARALLEL LOAD TO THE SAME PARTITION
PH35959	2021-04-13		UI74908	N/A	Y	INCORROUT FOR QUERY WHERE LEADING TABLE IN ACCESS PATH IS ACCESSED WITH AN EQUAL UNIQUE INDEX AND NEXT TABLE HAS PREFETCH
PH36093	2021-07-30		UI76541	N/A		INEFFICIENT JOIN TYPE CHOSEN WHEN THE SAME INDEX IS USED WITH LESS MATCHING COLUMNS
PH36147	2021-04-29		UI75178	N/A		ABEND04E RC00E20018 AT DSNSTKGG OFFSET00D76 ISSUED FOR A QUERY ON VIEW AND ITERATING ENTRIES OF DSNXOV1 IN FORMATTED DUMP TRACE
PH36179	2021-06-02		UI75667	N/A		DB2 12 FOR Z/OS ENHANCEMENT – APREUSE supports page range screening
PH36212	2021-10-28		UI77851	N/A		JOIN PREDICATE PUSHDOWN IS BEING ELIMINATED UNDER SOME CIRCUMSTANCES.
PH36222	2021-05-14		UI75411	N/A	Y	INCORRECT OUTPUT (TOO FEW ROWS RETURNED) POSSIBLE ON FULL OUTER JOIN RUNNING W/PARALLELISM, OUTER & INNER TABLES W/INDEX ACCESS
PH36297	2021-06-23		UI76046	N/A		INEFFICIENT ACCESS PATH WHEN A POOR FILTERING INDEX LEG FOR RID LIST ACCESS IS CHOSEN FOR A JOIN 21/05/27 PTF PECHANGE
PH36300	2021-08-11		UI76686	N/A		UNNECESSARY MULTI-INDEX LEGS ARE GENERATED WHEN A QUERY CONTAINSA HIGH UNCERTAINTY PREDICATE
PH36356	2021-05-24		UI75545	N/A	Y	UNEXPECTED NULL RESULT IN QUERY RESULT.
PH36406	2021-05-07		UI75288	N/A		INSERT KEY INTO FTB PROCESS DETECTING INCONSISTENT STRUCTURE MODIFICATION NUMBER THEN GOT DSNKFTIN:5043 ABEND
PH36430	2021-05-13		UI75393	N/A	Y	INCORROUT WHEN A TABLE EXPRESSION IS ACCESSED BY ‘O’ ACCESS TYPE WHILE ITS BODY IS A SINGLE TABLE QUERYBLOCK AND INDEX IS USED
PH36434	2021-05-13		UI75392	N/A		DB2 12 FOR Z/OS INTERNAL SERVICEABILITY UPDATE (Improve Create / Free FTB log recs)
PH36507	2021-08-18		UI76828	N/A	Y	INCORROUT ON QUERY WITH MULTIPLE LEFT JOINS, COALESCE AND SPARSE INDEX
PH36531	2021-05-13		UI75391	N/A	Y	ABEND04E RC00C90101 AT DSNKINSN ERQUAL5009 AND DSNKFTIN ERQUAL5066 FOR FTB INSERT PLOCK FAILURE
PH36540	2021-05-11		UI75345	N/A		AN INEFFICIENT ACCESS PATH USING LIST PREFETCH+SORT IS CHOSEN WHEN DIRECT INDEX ACCESS PROVIDES BETTER PERFORMANCE.
PH36737	2021-07-19		UI76363	N/A		RIDPOOL FAILURES FOR QUERY WITH MULTI-INDEX ANDING AND NO QUALI FYING RIDS FOR SECOND INDEX
PH36908	2021-08-03		UI76579	N/A		BACKING OUT THE BEHAVIOR FOR LOAD FORMAT DELIMITED FOR DECIMAL FIELDS WITHOUT A DECIMAL POINT THAT WAS IN 21/05/04 PTF PECHANGE
PH36978	2021-06-18		UI75978	N/A		FTB MESSAGE MSGDSNT351I ISSUED INCORRECTLY
PH37019	2021-08-03		UI76581	N/A		DB2 12 FOR Z/OS NEW FUNCTION FOR APREUSE
PH37116	2021-07-06		UI76220	N/A		ABEND0C4 RC38 IN DSNSVSFB OFFSET0758 WHILE EXECUTING DYNAMIC SQL AND DYNAMIC PLAN STABILITY IS ACTIVE
PH37119	2021-08-03		UI77291	N/A		INCONSISTENT ACCESS PATH FOR THE QUERY USING BETWEEN PREDICATE VERSUS RANGE PREDICATE
PH37151	2021-08-24		UI76897	N/A		IAG2 ABEND04E RC00C90101 DSNIBHRE ERQUAL5007
PH37171	2021-06-15		UI75893	N/A		INCORROUT MAY OCCUR FOR A QUERY USING INLIST OR BETWEEN WHICH CONTAINS COLUMNS
PH37416	2021-11-02		UI77911	N/A		EXPLAIN PROVIDES INCORRECT ESTIMATE OF SU/TIME FOR QUERY.
PH37472	2021-06-25		UI76090	N/A		WORKFILE ABENDS0C4 PIC4 AT DSNIWNRF OFFSET0DE50
PH37852	2021-07-22		UI76410	N/A		DSNTIAUL INCORRECT OUTPUT CAN HAPPEN FOR SELECT FROM UTF-16 VARGRAPHIC COLUMN
PH38003	2021-09-21		UI77249	N/A		SLOW QUERY PERFORMANCE FOR LEFT OUTER JOIN QUERY
PH38212	2021-07-07		UI76239	N/A	Y	ABEND04E RC00C90101 AT DSNKFTBU ERQUAL5061 AND DSNK1CNE ERQUAL5006 DURING FTB CREATION
PH38261	2021-10-18		UI77686	N/A	Y	DSNIIDIS:5002 OCCURRED DUE TO A MISSING INDEX ENTRY CAUSED BY A FAILURE OF SERIALIZATION BETWEEN CREATE INIDEX AND INSERT.
PH38274	2021-08-04		UI76596	N/A		ABEND04E RC00E2000C AT DSNXRSPL DSNSVSVB OFFSET00A54 WHEN INVOKING AN ADVANCED TRIGGER WITH HANDLER
PH38415	2021-10-25		UI77810	N/A		POOR INDEX MAY BE CHOSEN FOR QUERY WITH MULTIPLE PREDICATES ON THE SAME COLUMN
PH38551	2021-08-06		UI76633	N/A		ABEND04E RC00E2000F IN DSNSVSFB +00A2A WHILE RUNNING STORED PROCEDURES
PH38640	2021-08-09		UI76642	N/A		DB2 PARALLELISM TASK ABEND04E RC00C90101 IN WORKILE MODULE DSNIWNRF ERQUAL 5010
PH38872	2021-09-02		UI77010	N/A		UPDATE THE LENGTH OF THE ASCE AND PRHW – Fix in error see PH40706
PH39105	2021-10-18		UI77687	N/A		DB2 12 FTB INDEXTRAVERSECOUNT = 4294967295 FOR OBJECTS NOT ENABLED FOR FTB
PH39111	2021-09-15		UI77157	N/A		POOR PERFORMANCE OF AN UDPATE STATEMENT THAT CONTAINS A TABLE EXPRESSION ON THE NULL PADDING SIDE OF AN OUTER JOIN
PH39286	2021-08-23		UI76886	N/A	Y	ABEND0C4 RC003B AT DSNXODN OFFSET00600 AND ABEND04E RC00E2000D DSNLXDAL DSNSVSFB OFFSET00A18 LEAD TO DB2 ABTERM RC00E50702
PH39303	2021-10-18		UI77691	N/A		ABEND04E RC00E70005 AT DSNXOP1 M150 FOR A CREATE FUNCTION
PH39413	2021-09-02		UI77014	N/A		SQLCODE104 ISSUED WHEN REBIND ADVANCED TRIGGER WHICH CONTAINS A CALL STATEMENT WITH TABLE LOCATOR AS ITS PARAMETER
PH39584	2021-09-21		UI77253	N/A		INEFFICIENT ACCESS PATH CHOSEN WHEN A QUERY CONTAINS A FF1R CLAUSE AND AN INDEX ACCESS CAN BE MATCHING
PH39631	2022-01-28		UI79120	N/A	Y	ABND0C4 RC10 IN DSNVXUL0 +00324 AFTER CANCEL THREAD
PH39677	2021-09-07		UI77044	N/A		AN ALL ZERO PAGE ERROR – ABEND DSNKTRAV:5021
PH39751	2021-09-14		UI77134	N/A		THE INEFFICIENT JOIN SEQUENCE MAY BE CHOSEN, WHEN THE JOIN TABLE HAS A CORRELATED PREDICATE
PH39822	2021-09-27		UI77334	N/A		DB2 12 SERVICEABILITY FOR INFLUENCING ACCESS PATH SELECTION AND SUPPORTING INDEXABILITY FOR SQL STMTS USING IMPLICIT CAST
PH39875	2021-09-02		UI77013	N/A		ABEND04E RC00E70005 DSNXOACM P070 WHEN A COLUMN MASK IS DEFINED
PH40003	2021-09-27		UI77336	N/A	Y	CORRELATED SQL ISSUE, WHEN A TRANSITIVE CLOSURE PREDICATE IS GENERATED IN A SUB-QUERY INSIDE A CASE-WHEN CLAUSE.
PH40216	2022-01-21		UI79013	N/A		BLOCK JOIN PREDICATE PUSHDOWN WHEN THE TARGET SUBQUERY ACCESS PATH IS TABLESPACE SCAN
PH40243	2022-04-19		UI80210	N/A		DCC NEW FUNCTION FOR DB2ZAI V1.5
PH40269	2021-09-16		UI77189	N/A	Y	ABEND04E RC00E72068 AT DSNXSRME OFFSET01024 DUE TO A TIMING WINDOW WHEN USING INDEX FAST TRAVERSE BLOCK (FTB)
PH40273	2021-11-09		UI78000	N/A		IMPROVE PERFORMANCE OF FTB STORAGE POOL ADMF INDEX MANAGER CL20
PH40274	2021-11-01		UI77882	N/A		SQL0119N MIGHT BE ISSUED WHEN THE QUERY REFERENCES A VIEW AND THERE IS GROUP BY AND UNION ALL IN THE VIEW
PH40282	2021-09-21		UI77258	N/A	Y	ABEND04E RC00E2000C DUE TO STORAGE LEAK IN SUBPOOL ADMF AGL 64.
PH40432	2021-09-21		UI77254	N/A		POOR PERFORMING QUERY WHEN AN INEFFICIENT JOIN ORDER IS CHOSEN FOR QUERY WITH FETCH FIRST N ROWS ONLY CLAUSE
PH40461	2021-11-02		UI77909	N/A		ABEND04E RC00E70005 DSNXOSTP M210 CAN OCCUR ON A CREATE TRIGGER WHICH CONTAINS MORE THAN ONE CALL STATEMENT
PH40527	2021-10-26		UI77824	N/A	Y	INCORRECT OUTPUT RETURNED BY A QUERY WHOSE ACCESS PATH IS BASED ON A DB2ZAI HOST VARIABLE MODEL
PH40539	2021-10-07		UI77500	N/A		FTB DEADLOCK OCCURS WITH SYSTEM ITASK – CORRID=014.IFTOMK01
PH40543	2021-10-01		UI77407	N/A		SLOW QUERY PERFORMANCE COULD OCCUR WHEN LIST PREFETCH IS CHOSEN
PH40667	2022-03-02	PE PH46287	UI79547	N/A		ENHANCEMENT TO DB2 STACK STORAGE HANDLING
PH40706	2021-11-10		UI78014	N/A		ABEND0C4-04 IN DSN3ID80 WITH PTF UI77010 APPLIED 21/09/17 PTF PECHANGE
PH40981	2021-12-14		UI78548	N/A		ABEND0C4 AT DSNXRSOR OFFSET00136 FOR QUERY WITH VIRTUAL TABLE ACCESS ON TABLE EXPRESSION WITH CASE EXPRESSION
PH41055	2021-12-27		UI78742	N/A	Y	STORAGE LEAK WHILE RUNNING DRIVER PACKAGES WITH INCORRECT CLIENTAPPLCOMPAT SETTING
PH41205	2022-02-14		UI79308	N/A		ABEND04E RC00E72068 AT DSNXSRME OFFSET0106E CAN OCCUR WHEN A TABLE EXPRESSION OR A VIEW CONTAINS UNIONALL
PH41212	2022-01-12		UI78897	N/A	Y	INCORRECT OUTPUT MAY OCCUR FOR A QUERY USING XMLTABLE FUNCTION.
PH41216	2021-11-17		UI78137	N/A		ABENDS0C4 RC04 PIC04 RC00000004 IN DSN3ID80
PH41307	2021-11-17		UI78136	N/A		PERFORMANCE ISSUE WHEN THE JPP IS NOT ALLOWED FOR QUERY USING VIEW WITH UNION ALL INCLUDING A SMALL TABLE
PH41331	2021-12-08		UI78449	N/A		ABEND0C4 RC11 DSNXOSL OFFSET074C8 ON A CREATE TRIGGER STATEMENT
PH41335	2021-12-04		UI78403	N/A		ABEND04E 00C90101 AT DSNICMTC ERQUAL 5004
PH41350	2021-11-29		UI78295	N/A	Y	INCORRECT OUTPUT MAY OCCUR WHEN IMPLICIT CAST IS PERFORMED FROM SMALL INT TO INT
PH41436	2022-01-24		UI78933	N/A	Y	ABEND04E RC00E70005 AT DSNXRFN:M509 MIGHT HAPPEN FOR A QUERY WITH A CORRELATED SUBQUERY CONTAINING FETCH FIRST N ROWS ONLY
PH41446	2021-12-22		UI78714	N/A	Y	POSTPONED ABORT ENTERED INTO AN INFINITE LOOP IN DSNB1CLM DUE TODB2 INTERNAL CONTROL BLOCK POINTING TO ITSELF
PH41448	2021-11-02		UI77914	N/A		ABEND0C4 RC10 AT DSNXOCS OFFSET03770 WHEN RUNNING QUERIES WITH VIEWS OR TABLE EXPRESSIONS WITH JOIN RELATIONS
PH41535	2021-12-09		UI78457	N/A	Y	HIGH ECSA USAGE AFTER DB2 ABNORMAL TERMINATION
PH41567	2021-11-23		UI78244	N/A		DB2ZAI: A LONG RUNNING DB2ZAI DAEMON THREAD CANNOT BE PROPERLY HANDLED EVEN AFTER -STOP ML COMMAND
PH41627	2022-02-21		UI79152	N/A	Y	DB2 SHOULD PROCESS DIFFERENT TIMESTAMP DIGITS WITH SAME LENGTH, BUT ACTUALLY NOT
PH41670	2021-12-14		UI78560	N/A		ABEND04E RC00E70005 AT DSNXOSSF M909 WHEN A FNMEDIAN OR PERCENTILE_CONT/PERCENTILE_DISC IN A TABLE EXPRESSION IS NOT
PH41751	2021-12-01		UI78344	N/A		DB2 12 FOR Z/OS NEW FUNCTION
PH41793	2022-05-26		UI80317	N/A	Y	SQLCODE904 RC00C90084 TYPE100 FOR STATIC SQL STATMENT FROM A PACKAGE RUNNING WITH ISOLATION LEVEL RR
PH41943	2021-11-22		UI78223	N/A	Y	INCORRECT OUTPUT MAY OCCUR FOR SUBSEQUENT QUERIES WITH A SINGLE UNION ALL AND DB2ZAI ENABLED
PH41952	2022-01-07		UI78851	N/A		DB2 MAY CHOOSE DIRECT INDEX ACCESS OVER INDEX PLAN WITH SORT WITH LOWER COST
PH41978	2021-12-07		UI78424	N/A		DISTRIBUTED CONNECTION CONTROL (DCC) PROCESSING OF IFCID0402 FORDB2ZAI DESIGN CHANGE TO PRODUCE DELTA VALUES
PH42046	2022-01-27		UI79100	N/A		ABEND04E RC00C90101 AT DSNOTFLA ERQUAL5001 FOR QUERY WITH A COMBINATION OF XMLSERIALIZE AND ORDER BY
PH42081	2022-01-27		UI79099	N/A	Y	AN UNEXPECTED SQLCODE406 RESULTS FROM USING THE MULTIPLY_ALT BUILT-IN FUNCTION WHEN USING BIGINT ARGUMENTS.
PH42198	2021-12-28		UI78765	N/A		SQLCODE -805 MIGHT ISSUE FOR A DROPPED TRIGGER IN SMALL TIME WINDOW
PH42469	2022-01-24		UI79037	N/A	Y	INCORROUT MAY HAPPEN WHEN THERE IS CORRELATED SUBQUERY WITH EXISTS OR NOT EXISTS WITH FFNR CLAUSE AND A HYBRID JOIN IS USED.
PH42517	2022-03-23		UI79856	N/A	Y	INCORROUT MAY HAPPEN FOR QUERY USE HYBRID JOIN AND IOE INDEX
PH42572	2021-12-28		UI78763	N/A		INEFFICIENT R-SCAN IS SELECTED FOR TABLES OF 3RD VIEW JOINED
PH42554	2022-01-04	PE PH45702	N/A	N/A	Y	DB2 V11 ABEND04E RC00E2000F ON DSNSVSFB OFFSET08C6 DURING DBET PROCESS.
PH42578	2022-02-14		UI79305	N/A	Y	LATCH CONTENTION AFTER DB2 RESTART AND LPL RECOVERY HAS COMPLETED
PH42582	2022-02-01		UI79151	N/A		ABEND04E RC00E72068 AT DSNXSMUA OFFSET017F0 MAY HAPPEN TO SQL STATEMENT THAT CONTAINS ORDER BY ON THE RESULT OF UNION ALL
PH42975	2022-01-27		UI79112	N/A		SMF FIELD QISTFTBSIZE EXCEEDS ZPARM INDEX_MEMORY_CONTROL
PH42999	2022-03-09		UI79656	N/A		ABEND04E RC00E70005 IN DSNXOSJT M110 MAY OCCUR FOR A QUERY WITH MULTIPLE OUTER JOINS WHEN STAR JOIN IS ENABLE
PH43083	2022-03-10		UI79673	N/A		LOOP OCCURS IN DSNXOEXB DURING THE PREPARE OF A QUERY WHICH CONTAINS OUTER JOINS AND AN IN SUB-QUERY PREDICATE
PH43338	2022-02-14		UI79313	N/A	Y	INCONSISTENT DATA AND VARIOUS ABENDS IN DSNIRNXT FOR PBR RPN TABLESPACE AFTER RECOVER USING AN INLINE COPY CREATED BY REORG.
PH43460	2022-07-11		UI81408	UI81409	Y	ABEND04E RC000C90101 AT DSNGEPLC ERQUAL5064 MIGHT HAPPEN WHEN EXECUTING A PACKAGE IF REBIND IS OCCURRING AT THE SAME TIME
PH43495	2022-03-10		UI79683	N/A		COMMIT LSN ENHANCEMENT IN V12 IS DISABLED.
PH43547	2022-02-17		UI79400	N/A		INEFFICIENT ACCESS PLAN CAN BE PICKED FOR QUERIES WITH FETCH FIRST N ROWS ONLY / OPTIMIZE FOR N ROWS CLAUSE
PH43562	2022-02-22		UI79465	N/A	Y	INCORROUT OR ABEND04E RC00C20305 MAY OCCUR FOR A PREDICATE COMPARING ROWIDS FROM DIFFERENT TABLES, DIRECT ROW ACCESS IS USE
PH43565	2022-02-14		UI79317	N/A	Y	INCORROUT WITH FTB AND NON-UNIQUE INDEXES WITH GREATER THAN PREDICATE
PH43569	2022-03-04		UI79586	N/A		ABEND04E RC00E70005 MIGHT HAPPEN AT DSNXOMB M020 FOR A QUERY WHEN HYBRID JOIN IS SELECTED
PH43663	2022-02-08		UI79219	N/A	Y	DB2 THREAD ABEND ABND=0C1-00000001 from ssidDIST asid
PH43706	2022-05-27		UI80740	N/A		IAG2 ABEND04E 00C90105 IN DSNIASFP ERQUAL 0CA4
PH43728	2022-02-22		UI79437	N/A		WORKFILE ABEND04E RC00C90101 DSNISFW ERQUAL500B
PH43735	2022-03-10		UI79674	N/A		AFTER ISSUING A DISPLAY STATISTICS COMMAND DISPLAY STATS(ITC) LIMIT(*), DB2 INVALIDLY ISSUES AN ABEND04E 00F9000C
PH43797	2022-02-22	PE PH47264	UI79458	N/A	Y	ABEND04E 00F31100 – Plus Early APAR PH41216
PH43798	2022-03-16		UI79767	N/A		ACCESS PATH IS NOT REUSED ON REBIND PACKAGE WITH APREUSE FOR TABLES WHERE EXPANSION_REASON <> ‘ ‘ IN THE PLAN TABLE
PH43806	2022-03-24		UI79873	N/A	Y	DB2 SUBSYSTEM ABNORMAL TERMINATION WITH RC00D94001 AFTER ABEND04E RC00E20028 DUE TO AN OVERLAY BY REORG REBALANCE
PH43949	2022-03-24		UI79862	N/A	Y	DB2Z AI SQL OPTIMIZATION DASHBOARD DISPLAYS THE PACKAGE ERROR MESSAGE
PH44062	2022-02-24		UI79486	N/A		ABEND04E RC00E70005 DSNXOV0:M101 MAY OCCUR FOR SQL THAT REFERENCES AN UDF THAT CONTAINS AN XMLSERIALIZE FUNCTION
PH44073	2022-03-08		UI79643	N/A	Y	DB2 CRASHES WITH ABEND04E RC00E50079 DSNURMPG +386 AND 04F RC00E50054 AFTER RESTART(CURRENT) OF LOAD UTILITY
PH44109	2022-03-31		UI79969	N/A	Y	ALLEVIATE QUIESCING BEHAVIOR CHANGES DURING THE POPULATION OF NEW V12 SPACE LEVEL ATTRIBUTE FOR ALTER TABLESPACE PROCESSING
PH44119	2022-06-07		UI80920	UI80921		SYSIBM.SYSTABLEPART LIMITKEY COLUMN HAS AN INCORRECT VALUE AFTERREORG ON A SUBSYSTEM WITH DECIMAL=COMMA IN THE DSNHDECP
PH44137	2022-03-14		UI79717	N/A		SQLCODE904 WITH RC00D50001 AND RESOURCE 0000090A MIGHT OCCUR WHEN XMLCAST AND XMLQUERY OCCURS IN THE WHERE CLAUSE
PH44181	2022-04-01		UI79984	N/A		ABEND04E RC00C90101 IN DSNICUBC ERQUAL5004
PH44211	2022-03-14		UI79710	N/A	Y	QUERY PREDICATE PUSHDOWN INTO CTE SUBQUERY UNION LEG, FOR SUBQUERY WITH FFNR, MAY CAUSE INCORRECT OUTPUT
PH44231	2022-03-14		UI79718	N/A		ABEND0C4 RC38 AT DSNXOYP1 OFFSET09BF6 issued for CREATE OR REPLACE PROCEDURE
PH44260	2022-05-17		UI80553	N/A		ABEND0C4 RC04 AT DSNXOSEP OFFSET063C8 MAY OCCUR WHEN QUERY CONTAINS CTE OR TABLE EXPRESSIONS
PH44291	2022-07-20		UI81583	UI81584	Y	ABENDS0C4 RC00000038 RC38 AT DSNIACCH+19B92 OR ABEND04E RC00C90101 AT DSNISRTI ERQUAL534C
PH44356	2022-03-22		UI79841	N/A	Y	WORKFILE ABEND04E RC00C90101 IN DSNIWKFD ERQUAL5005 AND DB2 CRASH WITH MSGDSNV086E RC00F30801
PH44362	2022-06-13		UI80989	N/A		ABEND 04E RC00E70005 AT DSNXOBM P030 WHEN QUERY HAS A CORRELATED PREDICATE THAT COULD BE USED AS A SPARSE INDEX KEY
PH44421			OPEN	OPEN		DB2 12 FOR Z/OS NEW FUNCTION TO SUPPORT DB2Z AI
PH44618	2022-04-11		UI80090	N/A	Y	ABEND04E RC00E70005 DSNXESX4 P403 CAN OCCUR FOR AN OUTER JOIN QUERY REFERENCING A LOB COLUMN WITH A SHORT INLINE LOB LENGTH
PH44628	2022-04-11		UI80089	N/A	Y	PRECONDITIONING APAR FOR A FUNCTION IMPROVEMENT OF IN-MEMORY RLFTABLE AUTO REFRESH IN DATA SHARING GROUP
PH44631	2022-06-06		UI80891	N/A		ABEND0C4 RC00 AT DSNXRDEC OFFSET087F8 WHEN MIGRATING AN EXTERNALSQL PROCEDURE TO A NATIVE SQL PROCEDURE
PH44701	2022-04-01		UI79998	N/A		ABEND04E RC00E70005 AT DSNXOGP ERQUALP666 MAY HAPPEN FOR THE QUERY WITH MORE THAN 1023 QUERY BLOCKS
PH44833	2022-12-08	PE PH53318	UI83589	UI83590		NEW FUNCTION FOR LIMITING THE NUMBER OF UDF RUNNING CONCURRENTLY
PH44840	2022-06-21	PE PH48139	UI81105	N/A		ABEND0C4-3B IN DSNWARDS OFFSET024F8 – See PH48139
PH44916	2022-07-27		UI81675	UI81676		EDM STORAGE CAN GROW BEYOND VALUE OF EDMCSTMTC AND BEING NOT CONTRACTED
PH44928	2022-04-11		UI80091	N/A	Y	INCORRECT OUTPUT CAN OCCUR WHEN AN EXPRESSION-BASED INDEX IS USED
PH44972	2022-06-02		UI80862	N/A		MESSAGE DSNP007I RC00D70002 ISSUED FOR WORKFILE EXTEND FAILURE ON 32K WORKFILES
PH44983	2022-04-20		UI80092	N/A		DB2Z AI SYSTEM ASSESSMENT PERIODICALLY INSERTS RECORDS INTO TABLES MORE FREQUENTLY THAN THE STATIME_MAIN SYSTEM PARAMETER
PH44999	2022-04-28		UI80349	N/A	Y	INCORRECT RESULT SET COULD BE RETURNED WHEN USING JSON SQL
PH45085	2022-04-05		UI80027	N/A		SQLCODE=-725 MIGHT BE RETURNED WHEN OFFLOADING A QUERY WHICH REFERENCES A VIEW WITH CTE HINT
PH45100	2022-06-01		UI80840	N/A	Y	ABEND04E 00C90101 AT DSNICUMW ERQUAL 5003
PH45200	2022-06-02		UI80859	N/A	Y	DB2 ABEND04E 00E2000F IN DSNSVSFB OFFSET00A6A AND DB2 CRASH MSGDSNV086E REASON 00F30801
PH45262	2022-04-25		UI80308	N/A	Y	ABEND0C4 AND STORAGE OVERLAY AFTER WILD BRANCH IN DSN9SCN9 TO INVOKE THE FRR ROUTINE DURING DISPLAY DB COMMAND EXECUTION
PH45272	2022-10-03		UI82679	UI82680		USING QUERY ACCELERATION WITH PREPARE ATTRIBUTES CLAUSE ‘ CONCENTRATE STATEMENTS OFF ‘ MAY CAUSE PERFORMANCE IMPACT
PH45322	2022-11-03		UI83120	UI83130		ABEND04E RC00E2000C AT DSNSVBK OFFSET00A54 MAY HAPPEN WHEN THREAD HANDLES A LARGE NUMBER OF ALTER STATEMENTS.
PH45358	2022-05-20		N/A	UI80601	Y	WHEN RUNNING SQL DATA INSIGHTS Z16 WITH ZAIU GET ABEND=U4039
PH45369	2022-06-15		UI81024	N/A		PERFORMANCE ISSUE WHEN SPARSE INDEX WAS CHOSEN BUT IT SPILLS INTO PHYSICAL WORKFILE
PH45483	2022-05-12		UI80499	N/A		ABEND04E 00E20018 AT DSNSLD4 DSNSTKGG OFFSET00F76
PH45581	2022-05-06		UI80442	N/A	Y	ABEND04E RC009C0101 AT DSNKINSL ERQUAL5033 DURING INSERT
PH45643	2022-05-11		UI80483	N/A		SQL STATEMENT POOR PERFORMANCE DUE TO INEFFICIENT JOIN ORDER FOR SUBQUERY IF THERE ARE TWO TABLES JOIN ON UNIQUE KEY.
PH45702	2022-05-03		N/A	N/A	Y	DB2 V11 REAL STORAGE CREEPS AFTER APPLY APAR PH42554/PTF UI78803 22/04/14 PTF PECHANGE – PH45702 Db2 11
PH45748	2022-05-11		UI80481	N/A	Y	AN INCORRECT RESULT CAN BE RETURNED FROM A QUERY WITH UNION ALL THAT INCLUDES AN ORDER BY CLAUSE WITH OFFSET SPECIFIED.
PH45790	2022-05-11		UI80484	N/A		DB2 MAY MATERIALIZE A SIDE WAY REFERENCED TABLE EXPRESSION/VIEW, WHILE JPP CAN PROVIDE BETTER PERFORMANCE.
PH46028	2022-05-28		N/A	UI80758		DB2Z AI SYSTEM ASSESSMENT PERIODICALLY INSERTS RECORDS INTO TABLES MORE FREQUENTLY THAN THE STATIME_MAIN SYSTEM PARAMETER
PH46058	2022-07-05		UI81303	N/A	Y	CROSS LOADER (LOAD WITH INCURSOR) GETS ABENDS0C1 ABENDS0C6 PECHANGE 22/05/19 PTF
PH46099	2022-09-07		UI82304	UI82305	Y	DB2 SYSTEM HUNG WITH A LATCH.
PH46165	2022-06-16		UI81044	N/A	Y	ABEND04E RC00E70005 AT DSNXEPP M180 CAN OCCUR IN IN A V12/V13 COEXISTENCE ENVIRONMENT.
PH46206	2022-06-09		UI80959	UI80960	Y	AUTOMATIC GRECP RECOVERY 014.ASUTOGREC HANG UP AFTER ABEND0C4 PIC38 IN DSNIFLAA DUE TO ZERO PAGE SET CONTROL BLOCK POINTER
PH46209	2022-06-06		N/A	UI80890		UNEXPECTED VALUE WHEN QUERY FROM THE PLAN TABLE
PH46212	2022-06-02		N/A	UI80863		ABEND04E RC00E70005 DSNXOV0:M101 MAY OCCUR FOR SQL THAT REFERENCES AN UDF THAT CONTAINS AN XMLSERIALIZE FUNCTION
PH46221	2022-07-19		N/A	UI81536	Y	IN A DATA SHARING GROUP, RLF WAS STARTED UNEXPECTEDLY BY OTHER MEMBER’S NOTIFICATION WITH SCOPE LOCAL
PH46287	2022-06-01		UI80832	N/A	Y	ABENDS0D3 00000013 OR VARIOUS ABENDS AFTER APPLYING PH40667/UI79547
PH46295	2022-06-17		N/A	UI81087	Y	ABEND04E RC00E70005 DSNXESX4 P403 CAN OCCUR FOR AN OUTER JOIN QUERY REFERENCING A LOB COLUMN WITH A SHORT INLINE LOB LENGTH
PH46312	2022-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.
PH46313	2022-05-28		N/A	UI80762		DB2 MAY MATERIALIZE A SIDE WAY REFERENCED TABLE EXPRESSION/VIEW, WHILE JPP CAN PROVIDE BETTER PERFORMANCE.
PH46314	2022-06-03		N/A	UI80871	Y	AN INCORRECT RESULT CAN BE RETURNED FROM A QUERY WITH UNION ALL THAT INCLUDES AN ORDER BY CLAUSE WITH OFFSET SPECIFIED.
PH46319	2022-06-29		N/A	UI81245		ABEND0C4 RC04 AT DSNXOSEP OFFSET063C8 MAY OCCUR WHEN QUERY CONTAINS CTE OR TABLE EXPRESSIONS
PH46323	2022-06-02		N/A	UI80849	Y	ABEND04E 00C90101 AT DSNICUMW ERQUAL 5003
PH46348	2022-06-03		N/A	UI80880	Y	ABEND0C4 AND STORAGE OVERLAY AFTER WILD BRANCH IN DSN9SCN9 TO INVOKE THE FRR ROUTINE DURING DISPLAY DB COMMAND EXECUTION
PH46349	2022-06-01		N/A	UI80818		ABEND04E 00E20018 AT DSNSLD4 DSNSTKGG OFFSET00F76
PH46368	2022-06-29		UI81246	UI81247	Y	AN INCORRECT RESULT CAN BE RETURNED FROM THE TIMESTAMPADD BUILT-IN FUNCTION IF THE INPUT TIME EXPRESSION HAS HOUR 24.
PH46376	2022-07-08		UI81376	UI81377		ADDITIONAL GETPAGES WHEN A NON RESULT SET STORED PROCEDURE CALLSADDITIONAL PROGRAMS (PACKAGES) DURING EXECUTION.
PH46412	2022-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
PH46441	2022-06-07		N/A	UI80916		POOR QUERY PERFORMANCE FOR QUERY REFERENCING SPECIAL REGISTER SUCH AS CURRENT TIMESTAMP, WHEN NOT REOPT(ALWAYS) OR REOPT(ONCE)
PH46446	2022-09-29		UI82645	N/A		PRE-CONDITIONING APAR FOR A NEW FUNCTION (PBR changes)
PH46487	2022-07-19		N/A	UI81549		ACCESS PATH IS NOT REUSED ON REBIND PACKAGE WITH APREUSE FOR TABLES WHERE EXPANSION_REASON <> ‘ ‘ IN THE PLAN TABLE
PH46564	2022-05-31		UI80796	N/A		DB2Z AI SYSTEM ASSESSMENT (SA) MISSING INFORMATION IN SOME TABLES THAT SUPPORT SA
PH46567	2023-02-15		UI90541	UI90542	Y	EDM POOL ABEND04E RC00C90101 IN DSNGERBK ERQUAL 5015
PH46568	2023-01-23		N/A	UI90223		UNNECESSARY OVERFLOW RECORD IS CREATED FOR PAGE LEVEL LOCKING OBJECTS
PH46570	2022-07-22		N/A	UI81635		IAG2 ABEND04E 00C90105 IN DSNIASFP ERQUAL 0CA4
PH46603	2022-05-31		N/A	UI80807		DB2Z AI SYSTEM ASSESSMENT (SA) MISSING INFORMATION IN SOME TABLES THAT SUPPORT SA
PH46604	2022-05-31		N/A	UI80813		ABEND0C4 RC38 AT DSNXOYP1 OFFSET09BF6 issued for CREATE OR REPLACE PROCEDURE
PH46618	2022-06-24		N/A	UI81417		PERFORMANCE ISSUE WHEN SPARSE INDEX WAS CHOSEN BUT IT SPILLS INTO PHYSICAL WORKFILE
PH46619	2022-06-21		N/A	UI81106		MESSAGE DSNP007I RC00D70002 ISSUED FOR WORKFILE EXTEND FAILURE ON 32K WORKFILES
PH46655			OPEN	OPEN	(Y)	WLMHEALTH IS BEING IMPROPERLY SET TO 1
PH46767	2022-08-31		UI82201	UI82202		DROP INDEX LEAVES ORPHAN ROWS IN SYSIBM.SYSCOLDIST
PH46875	2022-06-29		N/A	UI81255	Y	CROSS LOADER (LOAD WITH INCURSOR) GETS ABENDS0C1 ABENDS0C6
PH47100	2022-07-18		N/A	UI81528	Y	DB2 ABEND04E 00E2000F IN DSNSVSFB OFFSET00A6A AND DB2 CRASH MSGDSNV086E REASON 00F30801
PH47141	2022-08-22		UI82057	UI82058		PERFORMANCE ISSUE CAN OCCUR DUE TO LACK OF JOIN PREDICATE PUSH DOWN
PH47153	2022-07-12		UI81425	UI81426		POOR QUERY PERFORMANCE MAY OCCUR FOR A QUERY THAT CONTAINS OR PREDICATE REFERENCING MULTIPLE TABLES.
PH47230	2022-07-07		N/A	UI81355	Y	SQLCODE904 RC00C90084 TYPE100 FOR STATIC SQL STATMENT FROM A PACKAGE RUNNING WITH ISOLATION LEVEL RR
PH47249	2022-08-19		UI82028	UI82029		(IAG2) ABEND04E RC00C90101 AT DSNIASFP ERQUAL5001
PH47264	2022-07-29		UI81719	UI81720		ABEND04E 00F31100 AFTER ASSOCIATE CALL 22/07/28 PTF PECHANGE
PH47312	2022-11-08		UI83182	UI83183		ABEND04E RC00E70005 AT DSNXOSR P020 FOR COMPLEX QUERY
PH47354	2022-08-16		UI81962	UI81963		THE FILTER FACTOR FOR THE IN SUBQUERY PREDICATE MIGHT BE ESTIMATED TOO HIGH SO THE INEFFICIENT INDEX MIGHT BE PICKED UP.
PH47374			OPEN	OPEN	(Y)	ABEND04E RC00C90101 DSNOTFLA ERQUAL5021 FOR A QUERY THAT CONTAINS AN INLINE LOB AND THERE IS A WORK FILE INVOLVED
PH47397	2022-09-29		UI82646	UI82647		INSERT ABEND04E RC00C90101 DSNISFPI ERQUAL5001 USING IAG2
PH47644	2022-07-21		UI81608	UI81609		ABEND04E RC00E70005 MIGHT HAPPEN AT DSNNQTOP M679 FOR QUERY USING XMLTABLE
PH47686	2022-12-14		UI83753	UI83754		DB2 ABEND04E RC00E20018 IN DSNSLD4.DSNSTKGG OFFSET00B98 OFFSET00BC2 (EXPLAIN of extremely large SQL)
PH47706	2022-08-17		UI81978	UI81979		ABEND04E RC00E70005 ABEND HAPPENS ON A REBIND WITH A MERGE STATEMENT WITH AN INCLUDE COLUMN
PH47795	2023-01-04		UI83915	UI83916	Y	ABEND04E 00C90101 AT DSNK1CNE ERQUAL 5005 DURING NORMAL DB2 PROCESSING (FTB)
PH47926	2022-08-05		UI81840	UI81841		SQLCODE510 MAY BE ISSUED FOR AN AMBIGUOUS CURSOR WITH A COMMON TABLE EXPRESSION CTE
PH47952	2022-08-04	PE PH48157	UI81820	UI81821	Y	INCREASED DB2 DIST ADDRESS SPACE CPU USAGE MAY BE OBSERVED AFTER PH40244 / UI80196 22/07/15 PTF
PH48013			OPEN	OPEN	(Y)	EXCESSIVE DB2 STORAGE BEING ALLOCATED BY BUFFER MANAGER IN ADMF GLOBAL CL1 POOL. THIS IS FOUND IN CSA/ECSA CAUSES OUT_OF_CSA
PH48073	2022-08-24		UI82094	UI82095	Y	WHEN UPDATE SET CLAUSE CONTAINS A SCQ WITH A MINUS SIGN, AN INCORROUT OR AN ABEND (ABEND0C4 AT DSNXGDT2 OFFSET00980) MAY
PH48078	2022-08-29		UI82129	UI82130		FTB SIZE EXCEEDS THE VALUE OF ZPARM INDEX_MEMORY_CONTROL
PH48085	2022-08-17		UI81980	UI81981		ABEND04E RC00E70005 IN DSNXRITV M106 CAN OCCUR FOR A TRIGGER
PH48099	2022-11-09		UI83218	UI83219		POOR QUERY PERFORMANCE MAY OCCUR FOR A QUERY THAT COULD TAKE ADVANTAGE OF REVERSE INDEX SCAN
PH48139	2022-08-22	PE PH49206	UI82068	UI82069	Y	DB2 ABEND0C4-3B IN DSNWARDS OFFSET 00C5C (Caused by PH44840 UI81105)
PH48149	2022-09-07		UI82301	UI82302		DIS STATS(ITC) DOES NOT CALCULATE INDEX TRAVERSE COUNT WHEN FTB FUNCTION IS DISABLED.
PH48157	2022-12-23		UI83859	UI83860	Y	READS FOR IFCID 411 OR IFCID 412 RETURNING INCORRECT DATA 23/06/21 PTF PECHANGE
PH48261	2022-10-19		UI82887	UI82888	Y	INCORRECT OUT MAY OCCUR FOR A QUERY REFERENCING A VIEW OR CTE AND USING SET FUNCTION IN THE PREDICATE.
PH48289	2022-12-27		UI83868	UI83869	Y	DB2 CRASH WITH ABEND 00D10231
PH48384			OPEN	OPEN	(Y)	ABEND04E RC00C90101 IN DSNGEDM.DSNGEDYI:0000 CAN OCCUR WHEN RUNNING SQL WITH DYNAMIC PLAN STABILITY ENABLED
PH48493	2022-10-19		UI82898	UI82899		ABEND04E RC00E72018 AT DSNXSING P040 MIGHT HAPPEN FOR A QUERY SATISFYING SOME CONDITIONS
PH48602	2022-09-06		N/A	UI82284	Y	INCORRECT OUTPUT CAN OCCUR WHEN AN EXPRESSION-BASED INDEX IS USED
PH48705	2022-12-12		UI83696	UI83697	Y	TABLE SCAN COULD MISS SOME RECORDS FOR HASH OBJECT DEFINED WITH PAGE LEVEL LOCKING
PH49206	2022-09-16		UI82437	N/A		DB2 ABEND0C4-00000004 IN DSNWARDS AT OFFSET 03846
PH49317	2022-11-11		N/A	UI83270	Y	SPRC VERSION INCORRECT
PH49442	2022-10-19		UI82901	UI82902	Y	SLOW DB2 SHUTDOWN DUE TO CHECKPOINT FOR WORKFILE
PH49479			OPEN	N/A	(Y)	DSNT225I BIND ERROR FOR PACKAGE DEPLEVEL OPTION NOT SUPPORTED RECEIVED AT A V13R1M501 OR LOWER DB2 FOR Z/OS SERVER
PH49543	2022-10-27		UI83046	UI83047		ABEND04E RC00E70005 DSNXOEXB M200 OR SQLCODE206 FOR UPDATE OF VIEW W/ INSTEAD OF TRIGGERS AND UPDATE STMT CONTAINS CORRSUBQ
PH49602	2022-12-06		UI83591	UI83592		SPARSE INDEX MIGHT BE CHOSEN AND CAUSE PERFORMANCE REGRESSION BECAUSE OF COST ESTIMATED TOO HIGH WITH NO PENALTY.
PH49619	2022-11-18		UI83368	UI83369	Y	SELECT REPLACE BIF HAVING MORE THAN 4K SEARCH STRING CAUSES DSNOLIKE ABEND0C4 PIC38 DUE TO AN OVERLAY.
PH49674	2022-11-21		UI83388	UI83389	Y	INCORRECT OUTPUT MAY OCCUR WHEN AN EXPRESSION-BASED INDEX IS USED WITH BETWEEN CLAUSE.
PH49721	2022-10-21		UI82954	UI82955		SQL PROCEDURE, HAVING SIGNAL STATEMENT WITH XML TYPE VARIABLE REFERENCED IN MESSAGE_TEXT, GOT ABEND0C4 PIC4 AT DSNOGETD+028CE
PH49759	2022-11-23		UI83438	UI83439		ACCESS PATH REGRESSION
PH49792	2022-12-07		UI83619	UI83620		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	2023-04-10		UI91369	UI91370		ORPHANED ROWS IN SYSIBM.SYSPACKDEP AFTER DROP VIEW
PH49901	2022-12-14		UI83755	UI83756		ABEND04E RC00E70005 IN DSNXRFMG M110 MAY OCCUR FOR SQL MERGE STATEMENT
PH49929	2022-12-21		UI83841	UI83842		A LESS EFFICIENT INDEX MAY BE SELECTED WHILE ANOTHER INDEX CAN PROVIDE BETTER FILTERING.
PH49972	2022-11-18		N/A	UI83371		DB2 13 FOR Z/OS NEW FUNCTION (PARENT_PLANNO usage in PLAN_TABLE for VIEW or Table Expression)
PH50129	2023-01-05		UI83939	UI83940	Y	ABND=04E-00D31010 DSNLILLM.DSNLCDG2:0001 OR DSNLCDG2:0005
PH50239	2022-12-23		UI83861	UI83862		DISCOURAGE THE USE OF SPARSE INDEX WHEN INNER CTE OR VIEW SIZE IS BIG WITH THE HUGE ESTIMATED ROWS AMOUNT AND SMJ IS ELIGIBLE.
PH50367	2023-01-20		UI90201	UI90202		DSNT286I FOR REBIND PACKAGE WITH APREUSE(WARN) EVEN THOUGH RESULTING ACCESS PATH IS THE SAME
PH50382	2022-11-30		N/A	UI83522	Y	ABEND 0C4 IN DSNVEUS1 AT OFFSET E34
PH50410	2023-05-15		UI91799	N/A		DIAGNOSTIC APAR TO PRODUCE A DUMP WHEN MSGDSNI055I IS ISSUED INDICATING IAG2 HAS BEEN DISABLED.
PH50499	2022-12-01		UI83543	UI83544	Y	ABEND 04E RC00C90101 DSNILKTO ERQUAL1002 (After restart from disk failure – Local fix Conditional Restart with FORWARD=NO)
PH50583	2023-01-06		UI83947	UI83948		DEGRADED PERFORMANCE FOR SOME QUERIES WITH A RANGE PREDICATE WITH BIFS: YEAR, DATE, OR SUBSTR ON THE LEFT HAND SIDE
PH50624	2023-01-23		UI90224	UI90225		DSNIDLDU:5016 ABEND DURING LOAD RESUME IN DB2 12.
PH50627	2022-12-12	PE PH53176	UI83688	UI83689	Y	ABEND04E RC00C200D8 DSNB1SWS 22/11/02 PTF PECHANGE
PH50729	2023-01-09		UI83963	UI86964		DB2 FOR Z/OS PROFILE (IFCID 402) STATISTICS ENHANCEMENT
PH50781	2023-02-13		UI90503	UI90504	Y	DB2 RECOVERY-LOOP WITH REPETITIVE ABEND0C4 RC10 AT DSNXRIHB OFFSET2D490 AT LEVEL UI65759
PH50882	2022-12-21		UI83844	UI83845		ABEND04E RC00E70005 AT DSNXOIXP P030 COULD HAPPEN WHEN AN SQL STATEMENT CONTAINS A VIEW AND HUGE NUMBER OF PREDICATES
PH50959	2023-02-16		UI90575	UI90576	Y	SELECT ENTERED INTO A LOOP BETWEEN DSNKTRAV AND DSNKNXT2 IN FTB PROCESS (Non-unique support problem)
PH50973	2022-12-28		N/A	UI83889		LOOP OCCURS IN DSNXOEXB DURING THE PREPARE OF A QUERY WHICH CONTAINS OUTER JOINS AND AN IN SUB-QUERY PREDICATE
PH50974	2022-12-27		N/A	UI83883	Y	ABEND0C4 DSNURLOG+049D0 REORG TABLESPACE SHRLEVEL CHANGE DURING PBG TO PBR CONVERSION
PH50997	2022-12-28		UI83884	UI83885		ABEND04E RC00E70005 AT DSNXGDT2 M205 FOR QUERY ON VIEW
PH51086	2023-04-06		UI91132	UI91133	Y	ABND=04E with RC00E50053 or RC00E20042 leads to DB2 crash with RC00E50727.
PH51088	2023-04-07		UI91344	UI91345		ABEND0C4 RC00000010 MAY HAPPEN IN DSNXRRID OFFSET007618 FOR A QUERY USING PREFETCH=’L’ ACCESS
PH51108	2023-01-04		UI83921	UI83922	Y	ACCESS DATABASE() SPACE() MODE(STATS) COMMAND DOES NOT UPDATE LASTDATACHANGE ON THE PARTITION WHICH IS CLOSED BY 010.PM2PCP01
PH51181	2023-02-08		UI90445	UI90446	Y	SERVICEABILITY APAR TO TAKE DIAGNOSTIC DUMP TO PREVENT DUPLICATEQWE PUT ON CHAIN LEADING TO LOOP IN PROCESSING
PH51264	2023-01-30		UI90327	UI90328		ABEND04E RC00C90101 AT DSNIBCTD ERQUAL5007 MAY OCCUR FOR EXPLAIN AN SQL STATEMENT
PH51404	2023-03-23		UI91051	UI91052		ALTER TABLESPACE MOVE TABLE MAY INPROPERLY HANDLE 3-PART TABLE NAMES
PH51565	2023-02-13	PE PH52893	N/A	UI90510		FTB NEW FUNCTION
PH51766	2023-03-29	See PH56078	UI91174	UI91175	Y	EXCESS STORAGE CONSUMPTION BY THOUSANDS OF 022.WDFND THREADS
PH51860	2023-01-31		UI90345	UI90346		ABEND04E RC00E70005 IN DSNXOUWF P009 MAY OCCUR FOR A QUERY WITH A SCROLLABLE CURSOR AND OUTER JOIN
PH52051	2023‑02‑16		UI90584	N/A		QUERY USING LISTAGG FUNCTION MAY ISSUE SQLCODE ‑137 DESPITE THAT PROPER LENGTH WAS PROVIDED ON INPUT.
PH52112	2023‑04‑07		UI91333	UI91334	Y	ABND0C4‑04 IN DSN3AMGP.DSN3AUCM AT +00AF4
PH52126	2023‑04‑07		UI91331	UI91332	Y	QUERY RECEIVES SQLCODE ‑312 FOR A MERGE STATEMENT WITH HOST VARIABLES WHEN BATCH NODE IS ENABLED WITH DB2JCC4 DRIVER
PH52147	2023‑03‑01		UI90806	UI90807	Y	STORAGE OVERLAY ABEND04E RC00E2002F IN DSNSVSFM OFFSET00702 AND DB2 CRASH MSGDSNV086E RC00D96001
PH52155	2023‑03‑03		UI90770	UI90771	Y	INCORROUT MAY OCCUR FOR A QUERY USING OUTER JOIN
PH52180	2023‑05‑03		UI91674	UI91675	Y	ABEND04E RC00E3007B DSNTGSCC DSNTABA
PH52273	2023‑03‑23		UI91053	UI91054	Y	ABEND0C4 RC38 IN DSNXRINT OFFSET00904 IN ALTER TABLESPACE STMT OF MAXPARTITIONS TO CONVERT AN EMPTY DEFINE NO TABLESPACE TO PBG
PH52275	2023‑03‑14	See PH54566	UI90954	UI90955	Y	ABEND0C4 RC38 IN DSN3EOT0 OFFSET0992
PH52279	2023‑02‑24		UI90721	UI90722		SQLCODE418 MAY BE RETURNED FOR AN SQL STATEMENT THAT CONTAINS ANXMLTABLE JOINED WITH A VIEW OR TABLE EXPRESSION
PH52397	2023‑03‑13		UI90932	UI90933		DB2 MAY ISSUE SQLCODE ‑204 OR ABEND04E RC00E70005 IN DSNXGRDS.DSNXOFL:M110 FOR TUDF CONTAINING COMMON TABLE
PH52442	2023‑04‑03		UI91102	UI91103		ABEND04 RC00C90101 AT DSNIDEFS:5001
PH52455	2023‑04‑07		UI91348	UI91349		INLIST PREDICATE PUSHDOWN INTO VIEW OR TABLE EXPRESSION MAY BE INCORRECTLY HANDLED CAUSING ABEND0C4 RC00000010 IN DSNXOPRS+03A58
PH52534	2023‑03‑24		UI91123	UI91124		ABEND04E RC00E70005 AT DSNXGRTS:M902 MAY HAPPEN FOR QUERY WITH OFFSET AND UNION ALL WHEN USED IN CURSOR WITH INSENSITIVE SCROLL
PH52590			OPEN	N/A		IFCID0389 CONTAINS AN INCORRECT VALUE FOR QW0389LV (NUMBER OF INDEX LEVELS IN THE FAST TRAVERSAL BLOCK)
PH52650	2023‑04‑07		UI91342	UI91343		INCONSISTENCIES AND VARIOUS ABEND04E RC00C90101 RC00C90216 RC00C9021C DUE TO OVERLAY CAUSED BY MASS DELETE
PH52651	2023‑04‑14		UI91460	UI91461	Y	DDF ABNORMALLY TERMINATED WITH ABEND04E RC00D30014 IN DSNLMTOK:0001 DUE TO LUWID TOKENS BEING EXHAUSTED.
PH52653	2023‑04‑27		UI91589	UI91590	Y	PERFORMANCE DEGRADATION FOR FREE PACKAGE ‑ INCREASED I/O ON THE DB2 CATALOG (SYSIBM.SYSPACKAUTH) 23/04/18 PTF PECHANGE
PH52717	2023‑04‑07	PE PH54823	UI91352	UI91353	Y	INCORROUT FOR QUERY REFERENCING COLUMNS THAT HAVE COLUMN MASKS DEFINED AND ONE REFERENCES A GLOBAL VARIABLE
PH52808	2023‑03‑14		UI90958	UI90959		MSGBPXF024I MESSAGES OCCUR IN DB2 WHEN ENABLING AND DISABLING MULTIPLE PACKAGES VERY QUICKLY IN SQL SCOPE SETTINGS FROM DB2ZAI
PH52821	2023‑05‑05		UI91713	UI91714	Y	THREADS HUNG IN DSNB1LTS AND DSNB1LTX WAITING ON A PAGE LATCH
PH52893	2023‑03‑17		N/A	UI91002		OBSOLETE FTB MSGDSNI070I MESSAGE DISPLAYED FOR V13 23/02/27 PTF PECHANGE
PH52915	2023‑04‑28		UI91610	UI91611		ABEND0C4 RC04 AT DSNXRSOR OFFSET00136 FOR QUERY THAT INCLUDES OUTER JOIN AND INNER JOIN, SUBQUERY PREDICATE AND DISTINCT
PH52988	2023‑04‑12		UI91424	UI91425		DB2 MAY CHOOSE AN INDEX WITH ONLY MATCHING PREDICATES INSTEAD OF A BETTER PERFORMING INDEX WITH MATCHING&SCREENING PREDICATES
PH53069	2023‑04‑14		N/A	UI91462	Y	ABEND05E RC28 AND DB2 ABNORMAL TERMINATION RC00E50054 FOR SQL DATA INSIGHTS QUERY RUNNING FROM A DDF THREAD
PH53084	2023‑06‑07		UI92131	UI92132		ABEND04E RC00C90101 IN DSNK1CNE 5005
PH53176	2023‑05‑16		UI91834	UI91835	Y	RC00C90206 DSNIIDIS ERQUAL5002 INDEX DATA INCONSISTENCIES ARE REPORTED DUE TO PAGE REGRESSION 23/03/12 PTF PECHANGE
PH53237	2023‑04‑13		UI91447	UI91448		INEFFICIENT ACCESS PATH COULD BE CHOSEN FOR A SQL STATEMENT WITH CSWL ENABLED COMPARING TIMESTAMP(6) COLUMN WITH LITERAL VALUE.
PH53318	2023‑04‑12		UI91421	UI91422		AFTER APPLYING PH44833/UI83589 SQLCODE904 WITH RC00E70082 MAY OCCUR DUE TO NOT DECREMENTING UDF INTERNAL COUNTER
PH53426			OPEN	N/A		AFTER SPLITTING OF LAST PBR PARTITION (ALTER+ADD PARTITION), QUERY USING PARTITIONED INDEX ACCESS MIGHT NOT RETURN ALL ROWS.
PH53525	2023-08-31	Closed	UI93410	UI93411	Y	ABEND0C4 RC00000038 IN DSNGESLC OFFSET0226A WHEN A PLAN WAS CANCELLED IN A DATA SHARING ENVIRONMENT
PH53561	2023‑05‑01		UI91627	UI91628		ABEND04E RC00E70005 AT DSNXEBR M666 COULD HAPPEN WHEN THERE ARE TWO CONDITIONS RAISED DURING THE OR PREDICATE (This is for XML)
PH53566			OPEN	N/A		Db2 zAI 1.5 New Function
PH53572	2023‑04‑30		UI91621	UI91622		ABEND04E RC00C90101 IN DSNIWIPR ERQUAL5064 RUNNIG A VERY COMPLEX QUERY
PH53621	2023‑05‑25		UI91977	UI91978	Y	INCORROUT MAY HAPPEN WHEN QUERY USES HINT WHERE QBLOCK_TYPE OF CHILD QUERY BLOCK IS NOT VALID FOR PARENT QUERY BLOCK
PH53717	2023‑04‑28		N/A	UI91614	Y	WORKFILE SORT ERROR MSGDSNT501I 0000006C 00000D02 DSNDB07
PH53747	2023‑05‑01		UI91629	UI91630	Y	ABEND04E RC00C90101 DSNGEDFR:50FF, ABEND04E RC00E3000A AT DSNTFRCV OFFSETF92 AND ABEND0C4 RC38 AT DSNTFRCV OFFSETF76
PH53779	2023‑06‑26		UI92474	UI92475		REBIND WITH OPTHINT CAN BE SUCCESSFUL EVEN THOUGH MULTI‑INDEX PLAN IS NOT THE SAME
PH53809	2023‑05‑16		UI91846	UI91847		ABEND0C7 RC00 IN DSNXRDEC OFFSET08A4C MAY OCCUR WHILE RUNNING SQL SELECT WITH PARALLELISM
PH53968	2023-09-20	Closed	UI93657	UI93658		IMPROVE PERFORMANCE OF STORAGE POOL FOR RMID=BB1RMID
PH54023	2023‑05‑17		N/A	UI91868		FTB NEW FUNCTION
PH54370	2023‑06‑24		UI92432	UI92433		ABEND04E RC00E70005 AT DSNXRFF M105 FOR AN ADVANCED TRIGGER WHEN SPECIAL REG CURRENT PACKAGE PATH IS SET ON TRIGGERING SQL
PH54397	2023-08-30	New & Closed	UI93365	UI93366		LESS ROWS MIGHT BE RETURNED FOR FETCH FROM PARTITIONED TABLE WITH A DPSI INDEX WHEN THERE IS CROSS-PARTITION UPDATE.
PH54410	2023‑06‑28		UI92539	UI92540		PREPARE THE SIMPLIFICATION OF DB2ZAI(DB2 AI FOR Z/OS)
PH54429	2023-08-08	New & Closed	N/A	UI93105		ABEND04E RC00E2000F FOR DSNSVSFB OFFSET 009B8 MAY ISSUE FOR BIND/REBIND USING PLANMGMT WITH APREUSE
PH54439	2023‑06‑23		UI92428	UI92429		ABEND04E RC00E70005 AT DSNXOBM P110 CAN HAPPEN WHEN QUERY CONTAINS AN OUTER JOIN AND A BETWEEN PREDICATE ON THE OUTER JOIN
PH54507	2023‑06‑09	PE See PH56254	UI92181	UI92182		SERIOUS PERFORMANCE DEGRADATION WHEN RUNNING DB2 QUERIES
PH54511	2023‑06‑23		UI92418	UI92419		FOR TABLE WITH MANY INDEXES, INDEX PRUNING MAY GET RID OF GOOD INDEX AND DEGRADE PERFORMANCE.
PH54566	2023‑06‑28		UI92532	UI92533	Y	ABEND0C4 RC04 IN DSN3ID50 OFFSET0568 23/05/18 PTF PECHANGE
PH54670	2023‑06‑22		UI92401	UI92402	Y	HYBRID JOIN MAY CAUSE INCORRECT OUTPUT OF LOB COLUMN
PH54701	2023‑08‑28	Closed	UI93325	UI93326		DB2XML.HTTPGETCLOB MAY RETURN SQLCODE ‑4302
PH54783	2023-09-11	Closed	UI93531	UI93532		REORG WITH STATISTICS INDEX OPTION MISSES STATISTCICS OF SOME KEY COLUMNS COMBINATIONS IN SYSCOLDIST
PH54823	2023‑08‑08		UI93101	UI93102		ABEND04E RC00E70005 AT DSNXOV1 M600 WITH UI91352 (V12) OR UI91353 (V13) APPLIED
PH54863	2023‑07‑06		N/A	UI92616		NEW FUNCTION
PH55018	2023‑07‑21		UI92854	UI92855		ABEND0C4 RC38 IN DSNXOSOB OFFSET020A0 COULD HAPPEN FOR AN SQL STMT THAT CONTAINS AN EXISTS SUBQUERY PREDICATE WITH UNION/UNION
PH55049	2023‑07‑13		UI92713	UI92714		INCORRECT FTB DMCFTOM LATCH CLASS
PH55078	2023‑08‑30	Closed	N/A	UI93373		DIAGNOSTIC APAR TO ADD MORE IAG2 INFORMATION IN THE DIAGNOSTIC LOG RECORDS WHEN DSNI055I IS ISSUED.
PH55205	2023‑08‑11		UI93158	UI93159	Y	ABEND0C4 RC38 AT DSNXECWA OFFSET0A636 DURING ROLLBACK PROCESSING. SUBSYSTEM TERMINATED WITH ABEND04F RC00E50702
PH55215	2023‑08‑10		UI93125	UI93126	Y	INCORRECT OUTPUT MAY OCCUR FOR SQL STATEMENT REFERENCING A SET FN WITH A DECIMAL ARITHMETIC EXPR CONTAINING A PARAMETER MARKER
PH55255	2023-08-23	Closed	UI93292	N/A		FTB NEW FUNCTION Add FTB Factor to DISPLAY STATS(ITC) output and IFCID 389
PH55294	2023-09-18	New & Closed	UI93601	UI93602	Y	AFTER ROLLBACK OF ALTER INDEX PIECESIZE OF NPI, AN OBD FIELD MAY BE SET INCORRECTLY, CAUSING ERROR ON REPAIR DBD REPAIR/TEST
PH55379			OPEN	OPEN		ABEND04E RC00E70005 DSNXOTS P666 CAN HAPPEN WHEN PROCESSING SORTMERGE JOIN PREDICATES DIFFERENTLY WHEN JOIN PREDICATE PUSHDOWN
PH55385	2023-08-29	New & Closed	UI93355	UI93356	Y	ABEND04E RC00C200C6 DSNB5SCM OFFSET04EE0 AFTER CANCEL OF BIND JOB
PH55427			OPEN	OPEN		COUNT() WITH GROUP BY MIGHT RETURN INCORRECT RESULTS WHEN THE COUNT RESULT IS HUGE.
PH55479	2023‑07‑17		UI92754	UI92755	Y	SELECT GOT 00C90D01 ABEND AT DSNONLLE+035D4 AFTER REORG SHRLEVELCHANGE AND CONCURRENT INSERT COMPLETED.
PH55498	2023‑07‑25		UI92906	UI92907	Y	ABEND0C4‑4 IN MODULE DSNTLTIM AT OFFSET 00728 IN DB2 V13 FL503
PH55631	2023-08-04	New & Closed	UI93057	UI93058	Y	The SYSREC generated by REORG UNLOAD EXTERNAL misses the first one-byte data for the variable length NOT NULL column.
PH55639	2023-07-27	New & Closed	UI92918	UI92919	Y	RESOURCE UNAVAILABLE ERROR SQLCODE904 WITH REASON 00000000 DURING INDEX SPLIT AND 00C20023 PAGE LATCH NOT GRANTED
PH55854	2023-09-08	New & Closed	UI93498	UI93499		SQLCODE -20442 RECEIVED WHEN RUNNING A SELECT STATEMENT REFERENCING AN UNNEST FUNCTION
PH55886	2023-09-08	Closed	UI93495	UI93496	Y	LOAD GENERATES INCORRECT TRAILING BLANKS FOR A UNICODE COLUMN IN EBCDIC TABLE.
PH55977	2023-09-04	New & Closed	UI93428	UI93429		ABND=04E-00E20018 AT DSNSLD4 .DSNSTKGG+00DB0 [XMLQUERY bug]
PH56025	2023‑09‑01	Closed	UI93415	UI93416		SQLCODE420 MAY OCCUR FOR A QUERY THAT HAS A PREDICATE COMPARING A DECFLOAT OR A FLOAT COLUMN WITH A DECIMAL VALUE.
PH56059	2023-08-28	Closed	UI93329	UI93330	Y	HIGH NUMBER OF SPACEMAP GETPAGE REQUEST CAUSED BY INSERT INTO PBG MEMBER CLUSTER APPEND NO OBJECTS
PH56078	2023-08-17	Closed	UI93229	N/A	Y	WITH UI91174 APPLIED FOR DB2 12, DB2 MAY TERMINATE WITH DSNV086E REASON=00E50702 23/08/16 PTF PECHANGE
PH56079	2023-09-13	New & Closed	UI93544	UI93545		SELECT A VIEW WITH OPTHINT CREATED BY TEP2/3/SPUFI ISSUES ABEND 00E20027 AT DSNSVBK
PH56094	2023-08-15	New & Closed	UI93211	UI93211	Y	IRLM: SERVICEABILITY APAR TO AID IN IDENTIFYING PLOCK MEMBER HOLDING LOCK
PH56095	2023‑08‑17	See PH56179	UI93228	N/A	Y	DB2 SERVICEABILITY APAR TO ASSIST IN DIAGNOSING BM PLOCK HANG
PH56118	2023-09-04	New & Closed	N/A	UI93430	Y	UTILITY JOBS ABNORMALLY TERMINATED WITH 00E20004.
PH56150			OPEN	N/A		ACCES PATH HAS PREFETCH=’O’ IN PLAN TABLE
PH56179	2023-08-31	New & Closed	N/A	UI93409	Y	DB2 SERVICEABILITY APAR TO ASSIST IN DIAGNOSING BM PLOCK HANG
PH56254	2023‑09‑11	Closed	UI93527	UI93528		INEFFICIENT ACCESS PATH MAY BE CHOSEN AFTER APPLICATION OF PTF FOR APAR PH54507. 23/08/08 PTF PECHANGE
PH56280			OPEN	N/A		INEFFICIENT ACCESS PATH MAY BE CHOSEN FOR QUERY FILTERING DATA WITH PREDICATE ON VARIABLE LENGTH COLUMN.
PH56318	2023‑09‑18	Closed	UI93607	UI93608		MESSAGE MSGDSNI055I REPORTS INVALID URID OF DISABLING AGENT
PH56328		New	OPEN	OPEN		REDUCE QEH SAMPLING ON AP LOCKED SQLS AND ADD VERIFICATION CHECKON HOSTVAR MODEL VALUES
PH56347		New	OPEN	OPEN	(Y)	ABEND04E RC00C90101 DSNIPSI:5002 CAUSED BY STORAGE OVERLAY FOLLOWING LOAD PARALLEL SHRLEVEL NONE AND BACKOUT
PH56366	2023-09-13	New & Closed	UI93553	UI93554	Y	SERVICEABILITY IMPROVEMENTS FOR OVERLAY DETECTION.
PH56389	2023-09-01	Closed	N/A	UI93413	Y	WITH UI91175 APPLIED FOR DB2 13, DB2 MAY TERMINATE WITH DSNV086E REASON=00E50702
PH56443			OPEN	N/A		ABEND04E RC00C90101 DSNKINSL ERQUAL5033 OR DSNIOW ERQUAL5003 USING INSERT ALGORITHM 2 IAG2
PH56559	2023-09-21	New & Closed	UI93665	UI93683		ABEND0C4 RC10 IN DSNXOPRS +114E8 CAN OCCUR ON BIND/PREPARE OF A QUERY WITH MULTI-COLUMN FREQ STATS ON NONMATCHING INDEX
PH56653		New	OPEN	OPEN		INCORRECT OUTPUT AFTER REORG REBALANCE DUE TO PLAN STABILIZATIONQUERY NOT BEING INVALIDATED
PH56742		New	OPEN	OPEN		UNEXPECTED ACCESS PATH FOR AN UPDATE STATEMENT ON VOLATILE TABLEWITH AN UNIQUE INDEX WHICH CAN MATCH ALL PREDICATES
PH56796		New	OPEN	OPEN	(Y)	INCORROUT OCCURS QUERY INVOLVING CORRELATED PREDICATE REFERENCING SET FUNCTION WITH ACCESSTYPE ‘O’
PH56945		New	OPEN	OPEN		ABEND0C4 RC0000003B RC3B IN DSNISFPI OFFSET010A2 REMOVING A PAGEFROM AN IAG2 PIPE THAT WAS DISABLED BY ANOTHER IAG2 THREAD
PH56978		New	OPEN	OPEN		AN INCORROUT OR A DATA LOSS MAY HAPPEN WHEN A SAME SECTION IS REUSED TO EXECUTE IMMEDIATE A DIFFERENT STATEMENT.
PH57015		New	OPEN	OPEN		AN ABEND0C4 MAY HAPPEN IN DSNXGRDS.DSNXRFHJ+0045A WHEN AN INVALID UNSUPPORTED ACCESS PLAN WAS CHOSEN.
PH57026		New	OPEN	OPEN		AN INTENTIONAL ABEND0C4 MAY HAPPEN FOR DSNXIAX2 P213 FOR AN INDEX WITH A LONG SCALAR EXPRESSION WHEN PROCESSING AN ALTER
PH57059		New	OPEN	OPEN		AN INVALID COLUMN LENGTH MAY BE CALCULATED FOR A VIEW WITH RECURSIVE COMMON TABLE EXPRESSION AND STORED IN CATALOG.
PH57071		New	N/A	OPEN		CHANGE READING FTB SYSIBM.SYSINDEXCONTROL TABLE FROM 10 MINUTES TO EVERY 2 MINUTES

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

TTFN,

Roy Boxwell

15/12/202116/12/2021

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

17/09/201522/06/2021

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

BiF USAGE
News

Read the Customer Comments across the Industry

Health Care
Insurance
Banking
Car Manufacturing

First results from DB2 z/OS sites

1	Index Mantenance Costs	Nearly all the data we got back showed a positive result for created Indexes…
2	EXPLAIN Suppression	About 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 Compatibility	DB2 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 Paper	Finding BIFs	And How to Lead a Problem-Free Life With Them in the Future Navigating the Challenges of moving to a new DB2 Release
Newsletter	2015-01 – BIFCIDS – Where’s the BIF?	How will you deal with loop-hole usage in production code?
Video	BIF Usage	(11min.) Trap and correct the BIFs that will cause belly-ache one day soon

BIF Usage video

17/02/201522/06/2021

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