APAR Update

This is a normal month with a nice selection of HIPERs and other Query Performance improvements. All checked on the 25th of January 2023.

HIPERs:

PH47795 ABEND04E 00C90101 AT DSNK1CNE ERQUAL 5005 DURING NORMAL DB2 PROCESSING (FTB)

PH48289 DB2 CRASH WITH ABEND 00D10231

PH50974 ABEND0C4 DSNURLOG+049D0 REORG TABLESPACE SHRLEVEL CHANGE DURING PBG TO PBR CONVERSION

PH51108 ACCESS DATABASE() SPACE() MODE(STATS) COMMAND DOES NOT UPDATE LASTDATACHANGE [IN RTS] ON THE PARTITION WHICH IS CLOSED BY 010.PM2PCP01


Interesting for RTS:

See HIPERS.

Query performance:

PH49929 A LESS EFFICIENT INDEX MAY BE SELECTED WHILE ANOTHER INDEX CAN PROVIDE BETTER FILTERING. – Here it is recommended to attempt HINTs or CTE HINT to assess the index choice.

PH50239 DISCOURAGE THE USE OF SPARSE INDEX WHEN INNER CTE OR VIEW SIZE IS BIG WITH THE HUGE ESTIMATED ROWS AMOUNT AND SMJ IS ELIGIBLE.

PH50583 DEGRADED PERFORMANCE FOR SOME QUERIES WITH A RANGE PREDICATE WITH BIFS: YEAR, DATE, OR SUBSTR ON THE LEFT HAND SIDE

IAG2:

None.


FIT/FTB:

See HIPERS.

RUNSTATS:

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:

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-04New & ClosedUI83921UI83922YACCESS 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
PH51901 NEWOPENOPEN RUNSTATS INVALIDATECACHE FUNCTION CAUSES ACCESSPATH PROBLEMS
PH51905 NEWOPENOPEN PCTROWCOMP SET BY RUNSTATS MIGHT BE INACCURATE

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
PH46567  N/A    OPEN(Y)EDM POOL ABEND04E RC00C90101 IN DSNGERBK ERQUAL 5015
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-04ClosedUI83915UI83916YABEND04E 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-27ClosedUI83868UI83869YDB2 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
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-21ClosedUI83841UI83842 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-05New & ClosedUI83939UI83940YABND=04E-00D31010 DSNLILLM.DSNLCDG2:0001 OR DSNLCDG2:0005
PH502392022-12-23ClosedUI83861UI83862 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-20New & ClosedUI90201UI90202 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-06ClosedUI83947UI83948 DEGRADED PERFORMANCE FOR SOME QUERIES WITH A RANGE PREDICATE WITH BIFS: YEAR, DATE, OR SUBSTR ON THE LEFT HAND SIDE
PH506272022-12-12 UI83688UI83689YABEND04E RC00C200D8 DSNB1SWS 22/11/02 PTF PECHANGE
PH507292023-01-09New & ClosedUI83963UI86964 DB2 FOR Z/OS PROFILE (IFCID 402) STATISTICS ENHANCEMENT
PH50781 NewOPENN/A(Y)DB2 RECOVERY-LOOP WITH REPETITIVE ABEND0C4 RC10 AT DSNXRIHB OFFSET2D490 AT LEVEL UI65759
PH508822022-12-21ClosedUI83844UI83845 ABEND04E RC00E70005 AT DSNXOIXP P030 COULD HAPPEN WHEN AN SQL STATEMENT CONTAINS A VIEW AND HUGE NUMBER OF PREDICATES
PH50959  OPENN/A SELECT ENTERED INTO A LOOP BETWEEN DSNKTRAV AND DSNKNXT2 IN FTB PROCESS (Non-unique support problem)
PH509732022-12-28ClosedN/AUI83889 LOOP OCCURS IN DSNXOEXB DURING THE PREPARE OF A QUERY WHICH CONTAINS OUTER JOINS AND AN IN SUB-QUERY PREDICATE
PH509742022-12-27ClosedN/AUI83883YABEND0C4 DSNURLOG+049D0 REORG TABLESPACE SHRLEVEL CHANGE DURING PBG TO PBR CONVERSION
PH509972022-12-28New & ClosedUI83884UI83885 ABEND04E RC00E70005 AT DSNXGDT2 M205 FOR QUERY ON VIEW
PH51086 NewOPENN/A(Y)ABND=04E with RC00E50053 or RC00E20042 leads to DB2 crash with RC00E50727.
PH511082023-01-04ClosedUI83921UI83922YACCESS DATABASE() SPACE() MODE(STATS) COMMAND DOES NOT UPDATE LASTDATACHANGE ON THE PARTITION WHICH IS CLOSED BY 010.PM2PCP01
PH51404  OPENN/A ALTER TABLESPACE MOVE TABLE MAY INPROPERLY HANDLE 3-PART TABLE NAMES
PH51565 NewN/AOPEN FTB NEW FUNCTION
PH51860 NewOPENN/A ABEND04E RC00E70005 IN DSNXOUWF P009 MAY OCCUR FOR A QUERY WITH A SCROLLABLE CURSOR AND OUTER JOIN
PH52051 NewOPENN/A QUERY USING LISTAGG FUNCTION MAY ISSUE SQLCODE -137 DESPITE THAT PROPER LENGTH WAS PROVIDED ON INPUT.

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

TTFN,

Roy Boxwell

2023-01 IDUG EMEA 2022 review

Hi All!

This year (well, strictly speaking last year…) IDUG EMEA changed from running Monday to Wednesday to being from Sunday to Tuesday. This caught a few people out with travel plans, etc. but all in all it was ok as a “once off”. It was also held after a two-year COVID delay in the beautiful city of Edinburgh, where it normally rains a day longer than your stay but I only had rain on one day! Mind you, that did coincide with tornado style, umbrella-shredding winds that made going anywhere more like a swim, but what can I say? The Haggis was excellent, the Whisky’s were simply gorgeous and all the people were incredibly friendly. Not just the visitors to the IDUG either!

All a bit late, I know, but I have been terribly busy doing other “real” work… So, with no further ado, off we go through the Db2 for z/OS IDUG presentations!

Please remember, to use the links below you *must* have been at the IDUG 2022 EMEA and/or you are an IDUG Premium member and remember your IDUG Userid and password!

Starting with Track A: Db2 for z/OS I

A01 Db2 13 for z/OS and More! from Haakon Roberts and Steven Brazil gave a great intro to Db2 13, the history behind it and the AI-driving force within. Finishing off with Db2 13 highlights. Our very own Andre Kuerten rated Haakon as the best overall presenter by the way.

A02 Db2 13 for z/OS Performance Overview, from Akiko Hoshikawa did what it said. A deep dive through all the performance stuff, including updates on Db2 12 and synergy with z Hardware.

A03 Db2 13 for z/OS Migration, Function Levels and Continuous Delivery from “The Dynamic Duo” of Anthony Ciabattoni and John Lyle was a review of all the Db2 12 functions and then an update on how to do the migration to Db2 13 – Which should be faster and less troublesome than to Db2 12.

A04 Now You See It, Unveil New Insights Through SQL Data Insights from Akiko Hoshikawa was the first of many presentations to go into depth all about AI, as various bits of AI are now available out-of-the box with Db2 13. Still a few things to do of course… 50GB of USS data for the SPARK for example … but at least no need for machine learning (ML). At the very end was also a glimpse into the future and the next three BiFs coming our way soon!

A05 Getting Ready for Db2 13 from John Lyle was another review of continuous delivery and how to get to Db2 12 FL510, which is the migration point for Db2 13 FL100.

A06 Db2 for z/OS Utilities – What’s New? from Haakon Roberts was the usual excellent presentation and run down of all the latest new stuff on the utilities front and also any Db2 13 stuff as well. As always well worth a read!

A07 BMC Utilities Update was a complete review of all BMC utilities.

A08 The Latest Updates on Broadcom Db2 Utilities was a complete review of all Broadcom utilities.

A09 Db2 Z Network Encryption: Overview and How to Identify End-Users Using Open Port from Brian Laube was a fascinating presentation all about fully understanding this problem as it is, or will be, a problem in all shops at some point in the future! The presentation also included an example NETSTAT from TSO to help you find the “bad guys” before you switch to 100% SECPORT usage. At the end was a very nice list of AHA requests/ideas/wishes which I would also appreciate if some people voted for!

A10 Do you know? SMF Records, IFCIDs, Trace Classes – What Does it all Mean? from Sally Mir all about traces, SMF, IFCIDS etc etc etc.

A11 The Db2 12 and beyond with the latest real customer experiences … from Ute Kleyensteuber. Ute discussed the experiences with Db2 12 and some of the challenges she had to face, and then some Db2 13 early experiences.

A12 Db2 13 for z/OS Migrations – Major Process Changes Detailed from John Lyle. He explained what the Catalog and Directory are and how they have changed over the years, also how to pre-migrate check and then migrate your sub-system.

A13 Let Your Cloud Applications Get the Most From Your z Data – Introducing IBM Data Fabric from Sowmya Kameswaran was to introduce IBM Data Fabric with Cloud Pak for Data.

A14 Express Yourself from Marcus Davage was an excellent romp through the horrors of REGEX and ended up with a quick way to solve WORDLE…all a bit dubious if you ask me! What with the recursive SQL from Michael Tiefenbacher and Henrik Loeser solving Sudoku and now Marcus solving Wordle what is left for our brains to do??? The link to github for Sudoku is:

https://github.com/data-henrik/sql-recursion

Thanks to Michael and especially to Henrik for the link.

A15 A Row’s Life from Marcus Davage. This is what a row goes through in its daily life – fascinating! A deep technical dive into the data definition of pages etc etc.

A16 Db2 13 for z/OS Install and Migration Using z/OSMF Workflows from Sueli Almeida. This presentation showed how you can indeed use z/OSMF for provisioning now!

Now we switch to Track B Db2 for z/OS II where we start with B02 as there was no B01:

B02 Continuous Delivery – Navigating from Db2 12 to Db2 13 from Chris Crone. This was another review of CD in Db2 12 and 13 but included a review of ZPARMS that have been deleted/deprecated/updated in Db2 13.

B03 Db2 13 for z/OS Application Management Enhancements from Tammie Dang. The highlights for applications were reviewed, highlighting timeouts and deadlocks and the way that SYSTEM PROFILES can easily help you get what you want without massive REBINDs.

B05 Ready Player One for Db2 13! from Emil Kotrc. This was another recap of Db2 13 migration, CD, Db2 12 FL501 to FL510 but also with deprecated functions and incompatible changes.

B06 Getting RID of RID Pool RIDdles from Adrian Collett and Bart Steegmans was an entertaining sprint through What is an RID? and When do I have a real RID problem?

B07 Mastering the Setup for Integrated Synchronization in Data Sharing from Christian Michel was all about network setup for encryption and certificates, as well as high availability for multiple accelerators.

B08 Things About Db2 for z/OS I Wish I’d Remember When…. from Michael Cotignola. A quick run through interesting stuff that has changed over time especially RTS usage.

B09 Speed up your Image Copies consistently and non-disruptive! from Eva Bartulej and Chris Duellmann was a description of one way to Clone data in production down to test.

B10 Db2 for z/OS Data Sharing: Configurations and Common Issues from Mark Rader. Was a very interesting presentation about exactly how data sharing hangs together and the various ways you can break it or make it better. A Must Read if you have, or are planning on, going to a data sharing system! His anecdote about a forklift truck crashing through the back wall of the data center brought home that “disasters” are just waiting to happen…

B11 Get Cozy with Traces in Db2 for z/OS from Denis Tronin was a great intro into the usage of Traces for Db2 for z/OS. Included was also an update of which traces have been changed or introduced for the new Db2 12 and 13 features.

B12 Partitioning Advances: PBR and PBR RPN from Frank Rhodes. This gave an excellent review of the history of tablespaces in Db2. Then the new variant PBR RPN was introduced, and how to get there.

B13 Managing APPLCOMPAT for DRDA Application from Gareth Copplestone-Jones. Another review of CD and APPLCOMPAT but this time looking at NULLID packages and the special problems they give you!

B14 Afraid of Recovery? Redirect Your Fears! from Alex Lehmann. This showed the new redirected recovery feature with some background info and a summary about why it is so useful!

B15 Db2 SWAT Team Perspective on Db2 13 Features that Maximize Continuous Availability from Anthony Ciabattoni. A review of all the features that allow you to change things while the system is running with no outage.

B16 DB2 z/OS Recovery and Restart from Thomas Baumann. This is a *one* day seminar… The IDUG live talk was just the first hour (first 34 slides!) and if you ever wish to consider doing RECOVER – Read it all!

B17 Security and Compliance with Db2 13 for z/OS from Gayathiri Chandran. Was all about the IBM Compliance Center and a list of all Audit relevant info in Db2 (Encryption, Audit policies etc.)

Now off to Track E AppDev & Analytics I:

E01 When Microseconds Matter from Thomas Baumann. This was all about tuning a highly tuned system. Where, even if you have 50 microsecond CPU SQL times, you can squeeze even more out of the Lemon! Included are complete SQLs for examining your DSC for tuning candidates that “stayed below the Radar” before.

E02 Customer Experiences and best practices from Accelerator on Z Deployments from Cüneyt Göksu. This was all about Accelerator usage of course and included a bunch of “Lessons learned” when migrating through to V7.5.

E03 Our Migration journey towards UTS and DB2 12 FL510 : Approach and
Status
from Davy Goethals. This was all about the effort to get all TS’s to UTS and to get to Db2 12 FL510 ready for Db2 13. Especially interesting for the QMF data going to implicit TS’s now.

E04 Db2 for z/OS Locking for Application Developers from Gareth Copplestone-Jones. All about locking from the developer’s POV. Contains Lock Size recommendations and descriptions of Locking, and Cursors. A very good read!

E05 Simplify database management with package rebind in an 24×7 application environment from Tammie Dang. This was all about the REBIND Phase-in which really, really helps keep your system 24×7! Also worth mentioning is the Db2 12 PTF UI73874 (APAR PH28693 Closed 2021-02-09) which *must* be installed!

E06 Mainframe Modernization – What about the data? from Greg DeBo. This was all about data and who has security, who owns it, needs it and what can you do with it when you no longer need it?

E07 Beginners guide to Ansible on z/OS from Sreenivas Javvaji started with the pre-reqs like Ubuntu and then adding Ansible and installing the IBM z/OS Core Collection finishing off with yaml.

E08 Sharing Early Experience with Db2 AI for z/OS from Fatih Aytemiz. This was the customer view of early usage of AI at their bank.

E09 Access Paths Meet Coding from Andy Green. Contains a DBA view of how application developers let access paths “slide” over time until incidents start to happen, and how to correct this by putting back “optimization” into the development process. Extremely useful presentation for Application Developers and SQL coders!

E10 SQL Injection and Db2 – Pathology and Prevention from Petr Plavjaník. A cross platform presentation all about SQL injection and how it can bite you… A very important take away is that it is not “just” an LUW or distributed problem. Dynamic SQL in COBOL can just as easily be injected…

E11 All you ever wanted to know about Accelerator on Z Monitoring from Cüneyt Göksu. Did what the label said!

E12 What Db2 Can Do; I Can Do Too – First Steps Towards Machine Learning from Toine Michielse. An introduction to AI and ML along with explaining all of the current Models and training mechanisms.

E13 How Can Python Help You with Db2? From Markéta Mužíková and Petr Plavjaník. Everything you ever wondered about Python but were afraid to ask! Included an installation list explaining some of the more weird environmental variables of the pyenv.sh

E14 Use Profiles to Monitor and Control Db2 Application Context from Maryela Weihrauch. This was all about one of the, in my personal opinion, most underused features of Db2 on z/OS. They have been around for years and they enable so much e.g. Global Variables, driver upgrades, RELEASE(DEALLOCATE) / RELEASE(COMMIT) etc etc

E15 -805 Explained from Emil Kotrc. This explained the whole background of program preparation Precompile, Compile, Link and BIND. Which consistency token goes where and when is it validated?

E16 The Exciting Journey Towards Devops on the Mainframe from Toine Michielse. This was all about DevOps and how the Open Mainframe is reflected in this environment with Zowe, git etc.

E17 Data, I just can’t get enough, Data Archiving in Db2 from Roberto Cason. Was all about data and when to archive it and more importantly – how?

Then Track F for AppDev & Analytics II:

F03 Is it worth to migrate CICS cobol app to Windows .net ? from Mateusz Książek. Naturally I am a little bit biased here, as I would always say “NO!”. However Mateusz goes on to explain the difficulty of monitoring and comparing the results. It was a bit like apples and oranges after all and he ended on a Pros and Cons slide where you must decide …

F04 COBOL abound from Eric Weyler. This was all about the remarkable life of COBOL and how it is *still* nailed to its perch! There are “new” forms like gnuCOBOL and new front ends like VS Code and of course Zowe and Web GUIs with z/OSMF.

F05 Getting the Most Value from Db2 for z/OS – No Matter what the Version or Function Level from Bob Bersano. This was a “call to arms” to actually use the existing functionality that you have in Db2 for z/OS versions 12 and 13.

F06 Declared Global Temporary Tables (DGTT) User Stories from Kurt Struyf. Explained the differences between CGTT and DGTT, why you would want to use a DGTT and things to know, especially EXPLAIN usage.

F07 War of the Worlds – Monolith vs Microservices from Bjarne Nelson. This session highlighted the intrinsic difficulties of going to microservices (Rest et al) in comparison to the “normal” DBMS ACID style. Finishing with “When to use microservices and when not to!”

F08 SYSCOPY: You cannot live without it! from Ramon Menendez. Detailed everything about this very important member of the Db2 Catalog. It also covered the new things in Db2 12 and 13 as well as a quick look at how SYSIBM.SYSUTILITIES interacts with it.

F09 Playing (with) FETCH from Chris Crone. This was an informative session all about FETCH where even I learnt something … shock, horror!

F10 A Tale of Two Extensions : Db2 Family Support in Visual Studio Code from Philip Nelson. This was a deep dive down into VS Code, IBM Db2 for z/OS Developer Extension and Db2 Connect (not the old one, the new one!)

F13 Your Statistics are Safe with Me, Statistics Profile Revealed from Nilufer Osken. Everything you wanted to know about Db2 statistics and statistic profile usage.

F15 Making Db2 on the Mainframe Great Again with the Linux Foundation Zowe Tooling from Joe Winchester. This was a great Zowe presentation about how you can modernize your mainframe. My personal favorite is naturally slide 35 with the Green Screen emulator…

F17 Explain explained from Julia Carter. This was an introduction in how to use and understand the EXPLAIN statement and its output to help in correcting badly-running SQL.

Finally Track G Daily Special (Sounds like a restaurant…) :

G01 Db2 for z/OS Security – An Introduction from Gayathiri Chandran. This is everything you need to know about security on z/OS.

G02 Back to Basics: High Performance Application Design and Programming from Tony Andrews. This was all about understanding SQL accesses and Db2 access paths using EXPLAIN. Then all the different type of joins were discussed as well as SORTs.

G03 Do I Really Need to Worry about my Commit Frequency? An Introduction to Db2 Logging from Andrew Badgley. Explained the BSDS, the Active and Archive logs and how they all interact with UOW. A recommendation here during Q&A was to COMMIT about every two seconds, and one war story was of a site that had an eight hour batch run which was then deemed to have gone rogue and was duly cancelled… It started rolling back and took a while… Db2 was then shut down – It didn’t of course… IRLM was then cancelled, Db2 came crashing down. Db2 was restarted… Hours passed as it *still* did its ROLLBACK, they cancelled it again… Then restarted and then waited hours for it to actually (re)start properly…

G04 Db2 Logging Basics & Exploitation Beyond Recovery from Steen Rasmussen. This explained how Db2 actually does logging and why we should be interested in it!

G05 The Trilogy of DB2’s “Originating” Address Spaces–Mainframe, DDF & Stored Procedures Measure/Tune from Thomas Halinski. Explained all the z/OS parts of Db2 and then listed out the different “areas” of tuning that are possible.

G17 Esoteric functions in Db2 for z/OS from Roy Boxwell. Naturally the best presentation of the EMEA *cough, cough* Could be a little bit biased here… If you wanted to know about weird stuff in Db2 for z/OS then this was your starting point. Any questions drop me a line!!!

My very own Oscar

I was also very proud, and happy, to be awarded an IBM Community Award as a “Newbie IBM Champion” for 2022! It was at the same time as John Campbell got his for lifetime achievement and was doubly good as I first met John waaaay back in the 1980’s at Westland Helicopters PLC with DB2 1.3 on MVS/XA – Those were the days!

Please drop me a line if you think I missed anything, or got something horribly wrong. I would love to hear from you!

TTFN,

Roy Boxwell

2022-12 Are you ready for Db2 13?

Hi all! Welcome to the end-of-year goody that we traditionally hand out. This year is a relaunch of the Migration HealthCheck that we first did over two years ago. I’ve also provided some news about Db2 13 UTS PBR RPN spaces that might be of interest to you!

DB2 12 UTS PBR RPN

You might well know that this was my single favorite feature of Db2 12. What I did not really appreciate, until now, was the fact that getting these very big partitions can come with a major price!

WHAT IS THE PROBLEM?

If you have a Data Sharing system (Who does not these days?) and you happen to use LOCKSIZE ROW on your UTS PBR RPN then you should take a good look at your performance monitor data. If you see a high number of P-Locks, and the number of false contentions is greater than your IRLM–SUSPENDS, then BINGO!

THE FIX IS?

Documented in the red book „Db2 13 Performance Topics“, Chapter 5 Data Sharing, 5.1 „Partition-by-range table space relative page numbering enhancements“ is the information above, and a lot more. The crux of the matter is a new Hash Algorithm, and to get to it you must simply REORG any UTS PBR RPN spaces that were created prior to Db2 13 FL500.

HERE IS SOME SQL

Here is some SQL to list out any and all of your UTS PBR RPN table partitions that were created prior to Db2 13 FL500 and have not yet been REORGed or LOAD REPLACED.

First check that you are actually *at* Db2 13 R1 FL500!

SELECT LU.EFFECTIVE_TIME 
FROM SYSIBM.SYSLEVELUPDATES LU
WHERE 1 = 1
AND LU.FUNCTION_LVL = 'V13R1M500'
AND LU.OPERATION_TYPE = 'F'
WITH UR
FOR FETCH ONLY
;

This simply returns the time when the FL500 was „activated“ in your Db2 13 system. If it returns no rows then you cannot do anything…

THE USUAL SUSPECTS…

Then we get the Partitions of interest:

SELECT SUBSTR(TP.DBNAME , 1 , 8) AS DBNAME 
,SUBSTR(TP.TSNAME , 1 , 8) AS TSNAME
,TP.PARTITION
,TP.CREATEDTS
,TP.REORG_LR_TS
FROM SYSIBM.SYSTABLESPACE TS
,SYSIBM.SYSTABLEPART TP
WHERE 1 = 1
AND TS.DBNAME = TP.DBNAME
AND TS.NAME = TP.TSNAME
AND TS.TYPE = 'R' -- UTS PBR ONLY
AND TS.LOCKRULE = 'R' -- ROW LEVEL LOCKING ONLY
AND TP.PAGENUM = 'R' -- UTS PBR RPN ONLY
AND TP.CREATEDTS < (SELECT LU.EFFECTIVE_TIME
FROM SYSIBM.SYSLEVELUPDATES LU
WHERE 1 = 1
AND LU.FUNCTION_LVL = 'V13R1M500'
AND LU.OPERATION_TYPE = 'F'
) -- CREATED BEFORE FL500 ACTIVATED
AND TP.REORG_LR_TS < (SELECT LU.EFFECTIVE_TIME
FROM SYSIBM.SYSLEVELUPDATES LU
WHERE 1 = 1
AND LU.FUNCTION_LVL = 'V13R1M500'
AND LU.OPERATION_TYPE = 'F'
) -- LAST REORG/LOAD BEFORE FL500 ACTIVATED
ORDER BY 1 , 2 , 3
WITH UR
FOR FETCH ONLY
;

This query uses the effective timestamp, created timestamp and the last reorg load replace timestamp to filter out all the partitions that do not need to be REORGed or LOAD REPLACEd. 

Please also remember you only need to do all this when you have ROW LEVEL locking in data sharing with high CPU p-locks. The red book shows some very impressive CPU savings!

BACK TO THE HOLIDAYS!

Over the last two years we have added and enhanced our Migration HealthCheck a lot. Improvements include testing what happens with DEFINE NO spaces when  they were created years and releases ago but would now be externalized. All of this means the output has changed, of course.

TELL ME MORE!

Here’s a list of all the deprecated (and semi-deprecated) items that should be checked and verified at your site:

  1. Use of SYNONYMS
  2. Use of HASH objects
  3. Use of segmented spaces
  4. Use of classic partitioned objects (not using table based partitioning)
  5. Use of simple spaces
  6. Use of six-byte RBA
  7. Use of BRF
  8. Use of LARGE objects (This is semi-deprecated)
  9. SQL EXTERNAL Procedures
  10. UNICODE (VARBIN Columns)
  11. Old RLF table defs
  12. Old PLAN_TABLE defs
  13. Old bound packages in use in the last 548 days
  14. Direct bound DBRMs (Yes they can still exist!) 
ANYTHING ELSE?

Well yes! You could also check how many empty implicit databases and how many empty tablespaces you have. While you are scanning your subsystem, it could also be cool to list out all the Db2 subsystem KPIs. What about seeing how many tables you actually have in multi-table tablespaces that, at some point, must also be migrated off into a UTS PBG or UTS PBR tablespace?

WE DO IT ALL!

Our little program does all of this for you. It runs through your Db2 Catalog in the blink of an eye and reports all of the data mentioned above.

WHAT DOES IT COST?

Nothing – It is our freeware for 2022/2023 and you only have to be registered on our website to request it along with a password to run it.

HOW DOES IT LOOK?

Here is an example output from one of my test systems here in Düsseldorf:

Db2 Migration HealthCheck V2.3 for SC1 V12R1M510 started at  
2022-12-14-10.56.00
Lines with *** are deprecated features

Number of DATABASES : 594
# of empty DATABASES : 237
# of implicit DATABASES : 385
# of empty implicit DATABASES: 207

Number of TABLESPACES : 4861
of which HASH organized : 0
of which PARTITIONED CLASSIC : 2 ***
# Partitions : 32 ***
of which SEGMENTED : 294 ***
of which SIMPLE : 0
of which LOB : 67
of which UTS PBG : 4467
# Partitions : 4477
of which UTS PBR (Absolute) : 5
# Partitions : 801
of which UTS PBR (Relative) : 6
# Partitions : 756
of which XML : 20

Number of tablespaces as LARGE : 8 ***
Number of empty tablespaces : 28
Number of multi-table TSs : 55
# of tables within these : 239
Number of incomplete TS : 1 XXX
Number of INSERT ALG 0 TS : 4861
Number of INSERT ALG 1 TS : 0
Number of INSERT ALG 2 TS : 0

Number of tables : 10293
of which ACCELERATOR ONLY : 0
of which ALIASes : 5307
of which ARCHIVEs : 1
of which AUXs : 60
of which CLONEs : 0
of which GTTs : 136
of which HISTORYs : 1
of which MQTs : 1
of which TABLEs : 4765
of which VIEWs : 2
of which XMLs : 20
Number of tables with Audit : 101
Number of tables with Data Cap : 0
Number of tables incomplete : 1 XXX
Number of tables with control : 0

Number of RLF DSNRLMT__ tables : 0
of which columns deprecated : 0
Number of RLF DSNRLST__ tables : 1
of which columns deprecated : 0

Number of PLAN_TABLES : 68
of which deprecated : 3 ***

Number of SYNONYMs : 1 ***

Number of UNICODE V11 Columns : 0

Number of PROCEDURES : 110
of which SQL EXTERNAL : 0
of which EXTERNAL : 108
of which NATIVE SQL : 2

Number of FUNCTIONS : 87
of which EXTERNAL TABLE : 38
of which EXTERNAL SCALAR : 42
of which SOURCED AGGREGATE : 0
of which SOURCED SCALAR : 0
of which SQL TABLE : 1
of which SQL SCALAR : 6
of which SYSTEM-GENERATED : 0

Number of Indexes : 23243
of which HASH : 0
of which type 2 : 23210
# of partitioned IXs : 6
# Partitions : 160
of which DPSI : 18
# Partitions : 164
of which PI : 15
# Partitions : 1138
Number of indexes COPY YES : 38
Number of indexes COMPRESS YES : 0

Number of table partitions : 6606
of which DEFINE NO : 2848
of which six byte RBA <11 NFM: 0
of which six byte RBA Basic : 0
of which ten byte RBA : 3759
Number of TP in BRF : 0
Number of TP with COMPRESS Y : 498
Number of TP with COMPRESS F : 0
Number of TP with COMPRESS H : 0
Number of TP with TRACKMOD YES : 2968

Number of index partitions : 24666
of which DEFINE NO : 20140
of which six byte RBA <11 NFM: 0
of which six byte RBA Basic : 0
of which ten byte RBA : 4527

Number of STOGROUPS : 10
Number of non-SMS VOLUMES : 0

Number of PLANs : 54
 of which DBRMs direct : 0
# of SQL statements : 0
Number of PACKAGES (total) : 5788
of which VALID = A : 42
of which VALID = H : 0
of which VALID = N : 44
of which VALID = Y : 5702
of which VALID = S : 0
of which OPERATIVE = N : 0
of which OPERATIVE = Y : 5788

Old RELBOUND executed packages : 0

Number of PACKAGES (distinct) : 480

Number of Original PACKAGES : 0
Number of Previous PACKAGES : 0
Number of Phased-out PACKAGES : 0
Total number of PACKCOPY : 0
of which VALID = A : 0
of which VALID = H : 0
of which VALID = N : 0
of which VALID = Y : 0
of which VALID = S : 0
of which OPERATIVE = N : 0
of which OPERATIVE = Y : 0
Number of SQL statements : 441833

Db2 Migration HealthCheck V2.3 for SC10 V12R1M510 ended at
2022-12-14-10.56.03

Db2 Migration HealthCheck ended with RC: 0

Any line with *** at the end means that you have something to do at some point in the future.  The names of all the found objects are written to DD card DEPRECAT so you can then start building a „to do“ list. I would start now to slowly „fix“ all of these before it is 03:00 in the morning, someone is migrating to Db2 14 FL 608 and it all goes horribly wrong…

Any line with XXX means that you have an incomplete definition for a tablespace and/or a table. These should be fixed as well, either by completing the definition or dropping the unfinished object(s).

WHAT’S WRONG WITH LARGE?

This is not actually deprecated but any tablespaces marked as LARGE tend to also not have a valid DSSIZE in them. This is fine if you have built a CASE construct to derive the value from the tablespace definition. But what you should do, is an ALTER and a REORG to „move“ the LARGE to a „proper“ tablespace. IBM and 3rd Party Software vendors hate having to remember that ancient tablespaces are still out there!

ALL ON MY OWN?

Naturally not! For example, after all the ALTERs have been done, a lot of the spaces are simply in Advisory REORG pending status and you could use our RealtimeDBAExpert (RTDX) software to automatically generate the required REORGs to action the changes.

SYNONYMS??

Well, you can do them all yourself by reading one of my older newsletters – just remember to watch out for the GRANTs afterwards.

HOW MANY DEPRECATED OBJECTS DO YOU HAVE?

I would love to get screenshots of the output at your sites which I would then all sum up and publish as an addendum to this newsletter. Just so that people can see how many Parrots we all have pining for the fjords!

TTFN, Happy Holidays!

Roy Boxwell

2022-11 First timer report

This month, I am turning over my blog to my colleague Andre Kuerten from Software Engineering’s German Labs based in Düsseldorf, Germany. I challenged him to write a blog all about his experiences as a “first time IDUG EMEA attendee” and all that that entailed as I thought this would make an interesting read for us all!

I’ll be Back!

Have no fear, dear readers, as I will be back next month with our annual Christmas give-away. The first blog of 2023 will be my comprehensive review of the 2022 IDUG EMEA in Edinburgh and the surprises found there-in.

It begins…

IDUG EMEA 2022 – First Timer Report

Pre-Preparation-Phase:

My firm gave me the chance to go the IDUG EMEA, additionally taking a Saturday workshop to educate myself in all things SQL. Surprisingly, my wife gave approval! Therefore, “the guy from techsupp” that I am, planned the trip to Edinburgh with all the hotel and flight bookings etc.

Saturday:

Unexpectedly, everything went smoothly and so I was standing in front of the Edinburgh International Conference Center (EICC) on Saturday morning, ready for my first IDUG in person after working for more than 15 years in the Db2 business, professionally developing software.

The general plan was for me to learn, or refresh my knowledge, about query optimization and finding the cause for poor performing SQL to be ready to work a little bit more intensively in this area. The first decision made was that I will take part in the “Query Optimization and Tuning Workshop” that will take the complete Saturday, covering themes like statistics, cardinalities, optimizer stuff, query EXPLAIN and strategies for performance tuning.

I entered the EICC and was registered by the very friendly and helpful IDUG employees, got my badge and a bag filled with little goodies – I think the practical value of the umbrella was unbeatable.

When looking around I have to admit that the EICC is a nice venue. Right behind the entrance is a big hall on the ground level which opens up for you, they arranged some high tables to put down your water/coffee, just a few more chairs would have been a good idea. But I was impressed by the sheer size of it all.

Sched is Your Friend!

The rooms all had Gaelic names, you just had to locate them on the map, so everything could be easily found. The Sched App was a perfect addition to the printed schedules, it made it very easy to get the day organized and to know where to go next. I really liked it and the connection to the website.

Workshop – z/OS???

Took the escalators to “Carrick” and got a seat in the workshop, looked around and discovered the usual mix of technical geeks, where the average age seems to be a little low… Additionally, there was no work station or material lying around that we would have to work with during the day (I had been told by the experienced colleagues that I would really have to work at the workshop), so I started to wonder… Chatting with some of the people in the room was fine, and I was glad I was not the only first timer (I must admit that I didn’t apply for a first timer badge, I do have my limits). Then it started. Instead of the expected z/OS hands-on workshop it slowly turned into an LUW daily presentation… At least I was not alone, as another z/OS guy was also not expecting LUW. It had not been made clear at early registration time that this was going to be just an LUW workshop. Anyway, since the topic was “SQL”, there was still valuable information here. So I listened and concentrated and got new ideas about how to start with SQL optimization and where to look first, starting from query optimization basics, discussing some database design alternatives and ending with cardinality estimation.

After a small lunch, taken in the big hall, the afternoon session started and was now going for the optimization of various operations like Scans, Sort, INSERT, UPDATE, DELETE and queries with outer joins, aggregation, distinct, correlated subqueries etc. I just had to take care to keep focused while the knowledge was being distributed. I think this would have been much easier if we could have done some practical exercises.

At the end the feelings about the workshop were a little bit mixed, but overall positive.

The Roy arrives

Starting in the late afternoon, I kept getting status messages from Roy (Boxwell, just in case anybody knows him 😊) who was on his way to Edinburgh, so our team size doubled in the evening.

The Booth…

Since my firm was a vendor at the IDUG I also got some experience from building the booth (where I really have to say thanks to the people from the EICC for all their help and kindness) and running it.

Sunday:

On Sunday the first normal IDUG Sessions were starting, I made it to the initial key-note in the “Pentland Main Auditorium” titled “Why Some Teams Are Successful While Others Struggle”, it was interesting, even though it was not a direct technical approach, it was about the people that you are working with and trusting your team. The auditorium (more like a cinema theater) was half full and they mentioned that we were 350+ onsite visitors which I found a good number, however I was told that there used to be many more in the past.

The next session, “Db2 13 for z/OS and More!” was summing up what I already knew from all the announced Db2 13 features, remarkable was one of the presenters, Haakon Roberts, he really did an excellent job and, looking back, I would say that his accent was the best that I heard during IDUG.

Noteworthy is that you soon get used to listening to speakers with different, strange to me, accents very fast, even if you are not a native speaker. This had been one of my fears beforehand which was, luckily, unfounded.

Back to “Carrick” and into the “Back to Basics: High Performance Application Design and Programming” presentation held by Tony Andrews. I mention him because of a reason: As expected the sessions differed in presentation style, quality of presentation etc., this is what you can tick on the evaluation cards. But the best ones, for me, were the ones where you could feel that the speaker was really deeply involved in the work with Db2 and knew what they were talking about like in this session, I heard a lot about the little things like row size, clustering order, all from a practical view, which was all very useful.

Do I Really Need to Worry about my Commit Frequency? An Introduction to Db2 Logging” confirmed my impression that the commit frequency is something to worry about, but the afternoon highlight was “COBOL abound”, demonstrating how you can develop in COBOL these days, of course, not only on the mainframe , but using Zowe (which I also use and explore at my firm) and the containers you can get there. This was really fascinating, although a little bit special…

Monday:

Day 3, Monday, I also went to the key-note “Behind The Birth Of An Accidental Enterprise”, covering some history of Db2’s evolution.
When talking about the next one, “Getting RID of RID Pool RIDdles”, I have to mention that this was done by two speakers and one of them, Adrian Collett, is known to me, we have worked together with some trial installations.
This was one of the big points: To meet people face to face for the first time, or again after the pandemic, especially customers who I have “talked to” for many years via email or telephone. I got the impression from everyone that they liked it very much to attend in person again.
The presentation itself shed some light on RID pool monitoring and tuning.
Sadly I have to say that this session was one of those that had to “speed up” towards the end (which was not Adrians fault!), so I am really looking forward to getting my hands on the PDFs as some speakers simply ran out of time and then rushed through their presentations. Also the notes taken correspond to slides that I need to see again, so this service (providing the presentations) should be continued for attendees, maybe IDUG could be just a little bit quicker making them available.

The next, very interesting, one was “Access Paths Meet Coding” which gave me practical insights about how to control some basic SQL rules which must be respected and how big the effect of it is in a productive environment.

Tuesday:

Then it was already Tuesday, off to listen to “SMF Records, IFCIDs, Trace Classes – What Does it all Mean?” which was obligatory for me, IFCIDs are utilized broadly in our products.

It should also be mentioned that, in my eyes, the technical equipment provided for the speakers was more than sufficient and the audio control was done by, always available, technical stuff from the congress center making a very good impression.

Get Cozy with Traces in Db2 for z/OS” reminded me of one way to get worthwhile information about what is going on (or going wrong) on the system.

I have worked with Db2 for some time “in real life” so the next sessions did not have very much new info for me, but I still took notes for “Partitioning Advances: PBR and PBR RPN”, “How to Keep Bad SQL Out of Production” and “Your Statistics are Safe with Me, Statistics Profile Revealed” and have to be checked again.

And the Oscar goes to…

For the evening, the IBM Db2 appreciation event was announced, so we took a walk after the conference day ended up going to the National Museum. The National Museum was, of course, closed when we arrived, so we ended up waiting in the crowd for something to happen. A bagpiper started to play: a perfect introduction for the evening. Worthy of note was the AC/DC part. Finally we made it to the grand hall, perfect location and ambience, food and drinks really well arranged.
Most impressive for me, was the lifetime award for Mr. John Campbell (he had already been honored previously at a keynote). Even I had read a lot from, and about, him (and the respect shown towards him from all of the audience was also good to see). At the end even Roy got an award: he did well as a “newbie” champion. I think they simply forgot to announce him for “some” years. Deserved without question, but why must he get a trophy for his desk that we have to look at (and that is mentioned by him) every single day?

Wednesday:

Back to the IDUG content on the next, and last day, Wednesday:

The last sessions for me at the IDUG were at “Moorfoot”, “Sidlaw” and “Kilsysth” rooms to be visited, following my path of getting new input to solve performance issues, “Db2 SWAT Team Perspective on Db2 13 Features that Maximize Continuous Availability” gave me an outlook to the future at customers sites.

 “The Exciting Journey Towards DevOps on the Mainframe” also explained some “modern” approaches. I regretted, a little bit, that I didn’t go to Roy’s “Esoteric functions in Db2 for z/OS“, instead I took “Explain explained” which was a bit of a basic session, getting the attention back to the little EXPLAIN data details.

At lunch time a, from my point of view, very interesting conference ended, giving me a lot of valuable information and “face-to-face” contacts, some minor negative items are normal I guess, but generally I really appreciated it.

So there you have it!

Many thanks to Andre for writing all that up! Coming soon will be Roy’s take on the EMEA 2022 where I go into technical details about the sessions etc.

I hope you enjoyed the guest blog this month and, as always, let me know what you think!

TTFN

Roy Boxwell & Andre Kuerten

2022-10 PROFILE Table usage Part Two

In part two of this newsletter, I wish to bring you up to speed on all the changes in the profile arena from Db2 11 right up until Db2 13.

Filters!

Profiles basically need some sort of “limit” to show Db2 which things should get which profile, and thus which keyword and attribute. To do this, we use Filters; for the filtering data there is an order of preference:

Multiple profiles?

When more than one profile applies to a thread or connection, the evaluation of the different profiles is not simultaneous. Instead, the profiles are evaluated in the following order, according to the criteria that are specified in the profile:

1. IP address or domain name, in the LOCATION column.

2. Product identifier, in the PRDID column.

3. Role and authorization identifier, in both ROLE and AUTHID columns.

4. Role, in the ROLE column only.

5. Authorization identifier, in the AUTHID column only.

6. Server location name, location alias, or database name, in the LOCATION column.

7. The location name of a requester, for monitored threads from a Db2 for z/OS requester. This is only for MONITOR THREADS and MONITOR IDLE THREADS.

8. Collection identifier and package name, in both COLLID and PKGNAME columns.

9. Collection identifier, in the COLLID column only.

10. Package name, in the PKGNAME column only.

11. Client application name, in the CLIENT_APPLNAME column.

12. Client user identifier, in the CLIENT_USERID column.

13. Client workstation name, in the CLIENT_WRKSTNNAME column.

First Come, First Served!

Only the first evaluated applicable profile is applied. Because the evaluation of multiple profiles is not simultaneous, the number of connections, or threads, on the subsystem might change during the evaluation of multiple profiles. Any profile that specifies a specific value in a particular column has precedence over a profile that specifies a single-byte asterisk value (‘*’) in the same column.

Destructive Overlap!

Further, each profile entry cannot have overlapping filter categories. From the precedence list there are eight categories formed from the numbers 1, 2, 3 – 5, 6 – 7, 8 – 10, 11 ,12 and 13, otherwise multiple rows must be inserted.

When you have multiple rows with overlapping filters from different categories then Db2 applies them all. Exact values are higher in priority than wildcard (*). As an example, for product id, PRDID, DSN13011 is before DSN* which is before *.

The More the Merrier!

Db2 also assumes that any rows with more filter values are higher priority than rows with some defaults or NULL values.

Finally, if everything is the same in the filter categories, Db2 will take the last inserted as it assumes this is “the most current version”.

Clear as Mud!

As you can easily see, it is very easy to tie yourself up in knots with this system! Good planning and good testing are paramount to a good, glitch-free implementation!

What Was New in Db2 11?

Well, Db2 11 brought in SPECIAL_REGISTER handling to the profile tables which is especially good for remote accessing threads. So you can now issue SET CURRENT APPLICATION COMPATIBILITY or SET CURRENT PACKAGE PATH, for example.

SET What You Want!

SPECIAL_REGISTER in the KEYWORDS column, ATTRIBUTE1 is any of the accepted SET statements, up to a maximum length of 1024 bytes. ATTRIBUTE2 and ATTRIBUTE3 are both NULL. The filtering is also not case sensitive.

Precedence of the SET special register:

1. Special register explicitly set by the application.

2. Special register set through Profile Support as above.

3. Special register set on the connection property level or data source level.

Buyer Beware!

Db2 11 also introduced warnings about not deleting rows from the _HISTORY tables to make sure you only delete rows that are really gone from the “normal” tables.

Additional Details

The MONITOR CONNECTIONS got a _DIAGLEVEL3 added:

MONITOR CONNECTIONS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes depending on the diagnosis level. An exception issues the diagnosis level and fails the connection request. The second part is either not there or it is_DIAGLEVEL1 which issues a DSNT771I console message, _DIAGLEVEL2 which issues a DSNT772I console message with more details every five minutes at most. _DIAGLEVEL3 issues, for a warning, a DSNT773I console message with more thread details for every thread, and for an exception a DSNT774I console message. ATTRIBUTE2 is a positive integer to indicate the threshold for the maximum number of remote connections. It must be less than or equal to CONDBAT. ATTRIBUTE3 is NULL. Filtering is only by the LOCATION column.

MONITOR IDLE THREADS got three new ATTRIBUTE1 values: EXCEPTION_ROLLBACK aborts any active idle threads and issues DSNT771I, EXCEPTION_ROLLBACK_DIAGLEVEL1 which is the same, and EXCEPTION_ROLLBACK_DIAGLEVEL2 with message DSNT772I.

MONITOR THREADS got _DIAGLEVEL3 added, which for an EXCEPTION issues a DSNT774I console message and, depending on the filtering, can be queued or suspended. WARNING issues the console message DSNT773I for every thread that exceeds the profile threshold.

Updated Info!

The info table about which columns can filter etc. got an overhaul with the note “The value is not case sensitive” for all values for MONITOR CONNECTIONS, MONITOR THREADS, MONITOR IDLE THREADS and SPECIAL_REGISTER.

IP6 Support!

It was also in Db2 11 that IPv6 got supported in the LOCATION field. So, it was now an IP Address: IPv4 dotted-decimal, or an IPv6 colon-hex, or a Domain Name, or a Location name.

What Was New in Db2 12?

In Db2 12, some new KEYWORDS options were introduced:

MONITOR ALL CONNECTIONS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes, depending on the diagnosis level. An exception issues the diagnosis level and fails the connection request. The second part is either not there or it is_DIAGLEVEL1 which issues a DSNT771I console message, _DIAGLEVEL2 which issues a DSNT772I console message with more details every five minutes, at most, and _DIAGLEVEL3 which issues a DSNT773I for WARNING and DSNT774I for EXCEPTION console message with more details. ATTRIBUTE2 is a positive integer to indicate the threshold for the total cumulative number of remote connections from all application servers . It must be less than or equal to CONDBAT. ATTRIBUTE3 is NULL. Filtering is only by the LOCATION column which must contain ‘*’, ‘:::0’ or ‘0.0.0.0’

MONITOR ALL THREADS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes, depending on the diagnosis level. An exception issues the diagnosis level and can cancel the thread, depending on the filtering criteria. Otherwise the thread is queued. The second part is either not there or it is _DIAGLEVEL1 which issues a DSNT771I console message, and _DIAGLEVEL2 which issues a DSNT772I console message with more details. _DIAGLEVEL3 issues a DSNT773I for WARNING and DSNT774I for EXCEPTION console message. ATTRIBUTE2 is a positive integer to indicate the threshold for the total cumulative number of active server threads. It must be less than or equal to MAXDBAT. ATTRIBUTE3 is NULL. Filtering is only by the LOCATION column which must contain ‘*’, ‘:::0’ or ‘0.0.0.0’.

Variable Support

GLOBAL_VARIABLE in the KEYWORDS column, ATTRIBUTE1 is a SET statement for a global variable. E.g. SET SYSIBMADM.GET_ARCHIVE = ‘Y’ or, if you are at Db2 12 FL507 or higher, SET SYSIBMADM.MAX_LOCKS_PER_TABLESPACE = 9000. See the SET documentation in the SQL Reference for more details. ATTRIBUTE2 and ATTRIBUTE3 are both NULL. These are only valid for remote applications.

SHARE_LOCKS in KEYWORDS column, ATTRIBUTE1 column contains a property that applies to global transactions in an RRS context, such as CICS through the External CICS interface. The property applies only to remote applications. The value must be in the following format: PROCEDURE_LIST=aaaa,bbbb,… each of the listed procs cannot be an external SQL proc and not a three part name. Maximum length is 1024 bytes. ATTRIBUTE2 and ATTRIBUTE3 are both NULL.

What Is New in Db2 13

FL500 introduced two extra keywords:

RELEASE_PACKAGE in the KEYWORDS column, ATTRIBUTE1 is COMMIT. ATTRIBUTE2 NULL means this is for remote threads only. 1 applies to local threads only (applies at package load) and 2 profile applies to both local and remote threads. ATTRIBUTE3 is NULL.

SPECIAL_REGISTER in the KEYWORDS column, ATTRIBUTE2 NULL means this is for remote threads only. 1 applies to local threads only (applies at package load) and 2 profile applies to both local and remote threads. Note that only SET CURRENT LOCK TIMEOUT (But not the WAIT, MODE or TO syntax) is currently supported for local threads.

FL 501 introduced local global variable support:

GLOBAL_VARIABLE in the KEYWORDS column, ATTRIBUTE2 NULL means this is for remote threads only. 1 applies to local threads only (applies at package load), and 2 profile applies to both local and remote threads. Note that only SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = xxx is currently supported for local threads.

The Future Is Bright!

It must be assumed that more and more things will end up in these profiles and it will get more and more interesting to use them, but the major problem is that they are not really transparent. It is very easy to incorrectly set them up and they are tricky, if not impossible, to test. All that being said, they are a very important tool in the tool-box of the modern, agile, DBA!

Examples

Use Case 1 : Evaluate a parameter change

You want to evaluate the impact of a modification at a system or application parameter. You can create a specific profile, with the values you want to activate, and an action level of type WARNING.  After starting the new profile, follow the message DSNT773I to monitor the future impact of your modification, without impacting the subsystem behaviour. 

Use Case 2 : Avoid to adapt attributes of your NULLID Collection

For dynamic SQL, it is not recommended to adapt parameters (APPLCOMPAT, CONCENTRATESTMT …) of the NULLID collection, as it would impact every client working with the default collection.  So, if you want an application to use specific options, you could duplicate the packages of the NULLID collection in a specific collection with the appropriate bind parameters.  Defining a profile that identifies the application, you can redirect to the new COLLID with the use of the special register PACKAGE PATH.  Should your new settings not be optimal, a simple stop of the profile will restore the situation.

Use Case3: High Performance DBAT but *not* everywhere!

The problem:
NULLID being used for all remote access and you wish to use High Performance DBATs for some of them but not all.
The solution:
Create a new collection, called e.g. HIGHPERDBAT, and bind into it any and all of the packages you want available for High Performance DBAT (so also with RELEASE(DEALLOCATE) naturally!)
Insert a Profile Id with a filter for the criteria you wish for, (see earlier in this blog). Then insert an attribute keyword of SPECIAL_REGISTER with ATTRIBUTE1 to be SET CURRENT PACKAGE PATH = ‘HIGHPERDBAT’
If the profile is started, then at next connection time, this new collection will be honored. If something goes awry, and you start getting DBAT problems, simply disable this profile entry and you are done!

Cool huh?

Remember that you must also *allow* High Performance DBATs by running with DDF parameters CMSTAT set to “INACTIVE” and PKGREL set to “BNDOPT” or “BNDPOOL”.

As always, I would love to hear any comments or criticism about this topic!

TTFN

Roy Boxwell

2022-09 PROFILE Table usage Part One

This month I begin a two-part topic because it is just too large to do in one blog entry!

In the Beginning

The DSN_PROFILE_TABLE was introduced sometime in DB2 V8, but it was not until DB2 9 that it started to be used for system profiling when IBM introduced three new commands: DISPLAY PROFILE, START PROFILE and STOP PROFILE. This first appearance of PROFILES was a bit limited and could control only a few ZPARMs – and four of those just for EXPLAIN purposes.

How does/did it look?

To get it working, you must first create all the required tables and indexes. (The DDL is in the db2hlq.SDSNSAMP member DSNTIJSG.) In bold and italics are the DB2 10 and higher versions:

SYSIBM.DSN_PROFILE_TABLE
CREATE TABLE SYSIBM.DSN_PROFILE_TABLE 
      ( "AUTHID"                VARCHAR(128)
       ,"PLANNAME"              VARCHAR(24)
       ,"COLLID"                VARCHAR(128)
       ,"PKGNAME"               VARCHAR(128)
       ,"LOCATION" "IPADDR"     VARCHAR(254)
       ,"PROFILEID"             INTEGER       NOT NULL
           PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
       ,"PROFILE_TIMESTAMP"     TIMESTAMP     NOT NULL WITH DEFAULT
       ,"PROFILE_ENABLED"       CHAR(1)       NOT NULL DEFAULT 'Y'
        ,"GROUP_MEMBER"          VARCHAR(24)                       
       ,"REMARKS"               VARCHAR(762)                       
       ,"ROLE"                  VARCHAR(128)                      
       ,"PRDID"                 CHAR(8)                           
       ,"CLIENT_APPLNAME"       VARCHAR(255)                      
       ,"CLIENT_USERID"         VARCHAR(255)                      
       ,"CLIENT_WRKSTNNAME"     VARCHAR(255)                      
      );                                                          
CREATE UNIQUE INDEX SYSIBM.DSN_PROFILE_TABLE_IX_ALL
      ON SYSIBM.DSN_PROFILE_TABLE                 
      ( "PROFILEID"                               
      );                                          
CREATE        INDEX SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
      ON SYSIBM.DSN_PROFILE_TABLE                 
      ( "PROFILE_ENABLED"                         
       ,"AUTHID"                                  
       ,"PLANNAME"                                
       ,"COLLID"                                  
       ,"PKGNAME"                                 
       ,"LOCATION" "IPADDR"                        
       ,"PRDID"                                   
       ,"ROLE"                                    
       ,"CLIENT_APPLNAME"                         
       ,"CLIENT_USERID"                            
       ,"CLIENT_WRKSTNNAME"                       
       ,"GROUP_MEMBER"                            
       ,"PROFILE_TIMESTAMP" DESC                  
      );                                          

SYSIBM.DSN_PROFILE_HISTORY – Same columns as DSN_PROFILE_TABLE apart from

REMARKS -> STATUS VARCHAR(254) and no index.
SYSIBM.DSN_PROFILE_ATTRIBUTES
CREATE TABLE SYSIBM.DSN_PROFILE_ATTRIBUTES                        
      ( "PROFILEID"             INTEGER       NOT NULL            
           REFERENCES SYSIBM.DSN_PROFILE_TABLE ON DELETE CASCADE  
       ,"KEYWORDS"              VARCHAR(128)  NOT NULL            
       ,"ATTRIBUTE1"            VARCHAR(1024)                     
       ,"ATTRIBUTE2"            INTEGER                           
       ,"ATTRIBUTE3"            FLOAT                             
       ,"ATTRIBUTE_TIMESTAMP"   TIMESTAMP     NOT NULL WITH DEFAULT
       ,"REMARKS"               VARCHAR(762)                       
      );                                                          
CREATE UNIQUE INDEX SYSIBM.DSN_PROFILE_ATTRIBUTES_IX_ALL          
      ON SYSIBM.DSN_PROFILE_ATTRIBUTES                            
      ( "PROFILEID"                                                
       ,"ATTRIBUTE_TIMESTAMP"   DESC                              
       ,"KEYWORDS"                                                
       ,"ATTRIBUTE1"                                              
       ,"ATTRIBUTE2"                                              
       ,"ATTRIBUTE3"                                              
      );                                                           

SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY same columns as DSN_PROFILE_ATTRIBUTES apart from

REMARKS -> STATUS VARCHAR(254)  and no index.

Notice the RI between the DSN_PROFILE_ATTRIBUTES and DSN_PROFILE_TABLE keyed on PROFILEID. Also notice that there are no indexes on the HISTORY tables and also no RI.

So What Could You Do?

With this new functionality you could use a profile to override four ZPARMs, namely NPGTHRSH, OPTIOWGT, STARJOIN and SJTABLES. To do so, you first inserted a row into the DSN_PROFILE_TABLE with some sort of filter, at this time only COLLID and PKGNAME, and then one or more inserts in the DSN_PROFILE_ATTRIBUTES table using the PROFILEID that you either just used, or got generated for you, in the DSN_PROFILE_TABLE using the KEYWORDS column and ATTRIBUTEn column(s).

Always on?

The column PROFILE_ENABLED in the DSN_PROFILE_TABLE informs Db2 whether or not to consider this profile when the START PROFILE command is issued. Setting it to N puts all of this profile’s records “to sleep”.

Not Just ZPARMs

It also enabled three global changes (no filters allowed) for BPname, MAX_RIDBLOCKS and SORT_POOL_SIZE. All of these are just for modelling production systems in test to then get a better, more accurate, EXPLAIN result and have *no* effect on the actual system at all.

Finally, IBM added some Accelerator-only support which had to be done with IBM involved.

Interestingly enough, there was a complete chapter about using profiles to monitor and report on SQL but there was also an update to the docu:

Important: The use of profile tables to monitor and capture information about the performance of SQL statements is deprecated, and not recommended.

So, I will not even bother going into detail about the monitor settings.

What Was the Difference?

The major difference between the SQL and ZPARM settings, was the ability to use different filter column values like AUTHID or IPADDR/LOCATION.

The DSN_PROFILE_HISTORY has the same columns as the DSN_PROFILE_TABLE, except that REMARKS is called STATUS and gets a value set by the START PROFILE command. Basically, a string that starts with REJECTED – or ACCEPTED – and then a text string describing why the profile was, or was not, accepted for use.

What’s in an ATTRIBUTE?

The DSN_PROFILE_ATTRIBUTES table contains the option that should be overridden when the Profile is active and the filtering allows it. The columns of interest are KEYWORDS and the three ATTRIBUTEn columns.

BUFFERPOOL Modelling

BPname (where name is any of the valid names like 0 through 49 or 32K1 through 32K9 etc.) in the KEYWORDS column. ATTRIBUTE1 and ATRIBUTE3 are set to NULL and ATTRIBUTE2 contains a positive integer value for the size of the BUFFERPOOL (for production modelling).

RIDPOOL Modelling

MAX_RIDBLOCKS in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 contains a value from 0 to the maximum value that you can set MAXRBLK in that subsystem (for production modelling).

STARJOIN Control

STAR JOIN in the KEYWORDS column, ATTRIBUTE2 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE1 set to DISABLE or ENABLE.

MIN STAR JOIN TABLES in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 contains a value from 3 to 225.

INDEX ACCESS Control

NPAGES THRESHOLD in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 contains one of the following values:

 -1 use index access if possible

 0 access path based on cost, the normal way Db2 works

 1 to nnnn Db2 should use index access on tables for which the total number of pages (NPAGES) is less than nnnn. Make sure that your Db2 Catalog statistics are up to date before you specify a value of 1 or greater.

IO Control

IO WEIGHTING in the KEYWORDS column, ATTRIBUTE2 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE1 is set to DISABLE or ENABLE (deprecated in DB2 10).

SRTPOOL Modelling

SORT_POOL_SIZE in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 set to a positive integer up to the maximum value of SRTPOOL. That is the new SRTPOOL (for production modelling).

Production Modelling

In this case, the EXPLAIN output got changed to output which PROFILE value was active at the time of the EXPLAIN. The REASON column in the DSN_STATEMNT_TABLE gets set to “PROFILEID nnnn” for the profile number that was active at the time of the EXPLAIN.

When Was this Done?

The DSN_PROFILE_ATTRIBUTES_HISTORY has the same columns as the DSN_PROFILE_ATTRIBUTES_TABLE except that REMARKS is called STATUS and gets a value set by the START PROFILE command. Basically, a string that starts with REJECTED – or ACCEPTED – and then a text string describing why the profile was, or was not, accepted for use.

So that was it for DB2 9 – not that much but a very good start if you ask me!

System Profile Monitoring

Then in DB2 10 came “system profile monitoring”, which is where this system got very useful indeed! It then got the ability to Monitor Connections, Monitor Threads and Monitor Idle Threads.

New Keywords for Connections and Threads!

MONITOR CONNECTIONS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes depending on the diagnosis level. An exception issues the diagnosis level and rejects any new incoming connection requests. The second part is either not there or it is _DIAGLEVEL1 which issues a DSNT771I console message and _DIAGLEVEL2 which issues a DSNT772I console message with more details. ATTRIBUTE2 is a positive integer to indicate the threshold for the maximum number of remote connections. It must be less than or equal to CONDBAT. ATTRIBUTE3 is NULL. Filtering is only by the LOCATION column.

MONITOR THREADS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes depending on the diagnosis level. An exception issues the diagnosis level and can cancel the thread depending on the filtering criteria otherwise the thread is queued. The second part is either not there or it is _DIAGLEVEL1 which issues a DSNT771I console message and _DIAGLEVEL2 which issues a DSNT772I console message with more details. ATTRIBUTE2 is a positive integer to indicate the threshold for the maximum number of server threads. It must be less than or equal to MAXDBAT. ATTRIBUTE3 is NULL. Filtering on nearly all columns is allowed.

Db2 11 Docu Update

In Db2 11, an extra bit of documentation was added when filtering by Collection identifier, package name, client user name, client application name or client workstation name. When the total number of queued and suspended threads exceeds the threshold, Db2 fails subsequent SQL statements and returns SQLCODE -30041 to the client.

For example, suppose that a profile for a package is started. That profile uses ATTRIBUTE2=2. If five threads request to run the package, two threads run concurrently, two threads are queued and suspended, and Db2 fails the SQL statements for the fifth thread.

And Finally IDLE?

MONITOR IDLE THREADS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes, depending on the diagnosis level. An exception issues the diagnosis level and cancels the idle thread. The second part is either not there or it is _DIAGLEVEL1 which issues a DSNT771I console message or _DIAGLEVEL2 which issues a DSNT772I console message with more details or WARNING_MESSAGE_FOR_IDLE_TIMEOUT (only for WARNING) which issues DSNT771I and/or DSNT773I. ATTRIBUTE2 is a positive integer to indicate the threshold for the maximum number of seconds an active server thread can stay idle.

That’s all for this month, next month I will go into detail about the Filters, the new stuff In Db2 11, 12 and 13 as well as examples of different things you can do nowadays.

As always I would love to hear any comments or criticism about this topic!

TTFN

Roy Boxwell

2022-08 IDUG Boston Review

This month is a quick run through of the z/OS presentations from the IDUG NA22 Boston – the first in-person event in three years!

It was great to actually meet and greet real people again! The only problem I had, was the extreme cold of the Hotel rooms: The expo was set to be like a freezer and, for a European person whose normal air-conditioning is an open window, it was a pretty uncomfortable experience.

If you were there physically, or even virtually, you can now download all the PDFs from all the tracks, so I have grabbed all the A, B, E Tracks and half of the F and G Tracks (Only the z/OS relevant stuff for me!)

Off we Go in Alphabetical Sequence

A01 Create value from data and where the DBA counts is an excellent overview of the modern world and where data and DBAs sit. It also contains a bunch of very nice SQL that you can indeed simply run in your shops as cut-and-paste. (I did!)
access content at IDUG.org (appropriate IDUG access required)

A02 was a very good intro into all the performance changes in Db2 12 and 13 (Check out my Db2 13 blog post as well for that matter!) and also on the hardware side with z15 and the brand new z16 box!
access content at IDUG.org (appropriate IDUG access required)

A03 & A04 were all about AI, including a bunch of example SQLs for the three new AI BiFs in Db2 13 and how to get it all working, as well as Distributed Connection control.
access content at IDUG.org (appropriate IDUG access required)
access content at IDUG.org (appropriate IDUG access required)

A05 was all about getting “value” from your Db2. Are you really using all the “newest” functionality that you could?
access content at IDUG.org (appropriate IDUG access required)

A06 from Haakon Roberts was an excellent update all around Utilities and latest APARs. The highlight being, at least for me, the ICLIMIT TAPE for REORG which finally enables easy migration to UTS PBR RPN Tablespaces. The heads up about the LOAD FORMAT DELIMITED was also good!
access content at IDUG.org (appropriate IDUG access required)

A07 concerned The Trilogy of originating SQLs and how to measure and tune them. At the end was an extra part called “Additional Tuning” that is well worth a read, as it fully explains the internal Db2 data flow from SQL to RDS to DM to Media manager.
access content at IDUG.org (appropriate IDUG access required)

A08 was all about SWAT Tales and had some great interaction! The best bit was one attendee stated that his Db2 system is going through 93 x 768 GB log datasets in less than 6 hours… The important take-away was to keep up to date with PTFs, especially HIPERs (Here you can subscribe to my monthly APAR reports to aid in this), and make sure you have enough logs! Plus, take care with high performance DBAT usage. Finally: Watch out for over- and mis-use of PBG spaces!
access content at IDUG.org (appropriate IDUG access required)

A10 was all about migrating to UTS and to Db2 12 – Do not underestimate the time and effort required to do this!
access content at IDUG.org (appropriate IDUG access required)

A11 contained details about using the RTS to work as a “monitor” enabling you to get a different view of GETPAGES for example.
access content at IDUG.org (appropriate IDUG access required)

A12 tied in with the A01 presentation and was all about Dynamic SQL problems and solutions including a nice way to “purge” single SQLs from the DSC! Included some very interesting SQLs to calculate your DSC KPIs.
access content at IDUG.org (appropriate IDUG access required)

A13 was all about configuring Data Sharing as well as solving some common issues with it. It was a great intro to everything DS. Plus, it contained a list of new and improved things that came along in Db2 12 and 13.
access content at IDUG.org (appropriate IDUG access required)

A14 was all about inactive data impacting your performance. A very interesting topic that all sites probably have an issue with without really knowing it! Archive Enabled Tables could be very useful… Towards the end were a couple of nice features in Db2 with UNION ALL.
access content at IDUG.org (appropriate IDUG access required)

A15 was a journey through TRACES, SMF and IFCIDs. If you ever wanted to know about any of these things then here’s the best starting place!
access content at IDUG.org (appropriate IDUG access required)

A16 launched Python at the DBAs and sysprogs! Scary stuff! All about installing Python on z/OS and latest bug fixes etc etc. My favorite bit was the “disable auto commit” and “remember to commit before disconnect”!
access content at IDUG.org (appropriate IDUG access required)

B-Track

B01 was all about getting prepared for Db2 13. Starting with a review of all the FLs of Db2 12 right up to FL510 which is the major prereq for Db2 13, of course!
access content at IDUG.org (appropriate IDUG access required)

B02 contained a ton of details all about the “Black hole” of Db2 statistics – Page Latch Suspensions, plus a very handy list of how to fix these suspensions – if at all possible…
access content at IDUG.org (appropriate IDUG access required)

B03 took you into the first steps of the Machine Learning (ML) world. Started off with penquins and then I got sort of lost … 🙂
access content at IDUG.org (appropriate IDUG access required)

B04 was another migration session about getting from Db2 12 FL501 to Db2 13, this time incorporating Deprecated functions and Incompatible changes etc.
access content at IDUG.org (appropriate IDUG access required)

B05 gave us four different ways to migrate away from multi-table tablespaces to PBGs. From Unload/Drop/Create/Load, MOVE TABLE (Db2 12 FL508), create “%_new” tables => INSERT from original => rename original to “%_old” => rename “%_new” to original => drop “%_old”, and lastly, using a vendor tool to do the work for you!
access content at IDUG.org (appropriate IDUG access required)

B06 was all about the pre-migration query DSNTIJPE and what you do, or don’t do, with the resulting 23 odd reports.
access content at IDUG.org (appropriate IDUG access required)

B07 showed how MasterCard monitors any and all Db2 Alerts to take proactive actions before things go pear-shaped. This includes disk space, messages, sql codes, access path changes, memory, storage, DDF, physical media limits (size, extents, volumes etc.) There is a very handy full list of “things to monitor” at the end of the presentation as well. Check out the RTDX SAX tool timings!
access content at IDUG.org (appropriate IDUG access required)

B08 Explain explained – Complete introduction as to how the Db2 Optimizer makes its cost-based decision. At the end were a couple of nice “best practices” slides summing it all up very well.
access content at IDUG.org (appropriate IDUG access required)

B10 Db2 for z/OS housekeeping. This was all about a methodology for REORG/RUNSTATS/REBIND. The interesting take away here was the idea to *never* run a RUNSTATS based soley on RTS counters from the last RUNSTATS. In other words, just do a RUNSTATS when you are doing a REORG.
access content at IDUG.org (appropriate IDUG access required)

B11 was all about client configuration and was a cross-platform presentation (naturally!) It contained all you need to know about the setup and installation and use of the db2cli among many other things!
access content at IDUG.org (appropriate IDUG access required)

B12 had Tips for DBAs and programmers to help reduce costs – Always a good topic! In here was also a nice tip about keeping up to date with your COBOL compiler!
access content at IDUG.org (appropriate IDUG access required)

B13 got secure on us by using Multi-factor Authentication for Db2 z/OS. This included setting up MFA and examples of when it works or does not work.
access content at IDUG.org (appropriate IDUG access required)

B14 carried on the security theme by going into detail about how to protect yourself from Ransomware attacks. Here multi-layer protection is the best – MFA, Pervasive encryption, Separation of duties (SECADM usage…), Controlling access to Db2 datasets etc. etc.
access content at IDUG.org (appropriate IDUG access required)

B15 came back to more “normal” territory about stopping runaway applications by using the RLF tables DSNRLSTxx and/or DSNRLMTxx, including a nice selection of examples to give you a head start.
access content at IDUG.org (appropriate IDUG access required)

B16 presented a way to use MS Excel to help in analyzing performance data. A nice introduction into getting data down to the PC and then using advanced plug-ins like ToolPak.
access content at IDUG.org (appropriate IDUG access required)

E-Track

E01 was all about the Optimizer and its various access path and resultant performance. Tons of notes all about access paths make this well worth a read!
access content at IDUG.org (appropriate IDUG access required)

E02 was a recap of Continuous Delivery, going over the why’s and how’s including vendor responses, and then ran through all the FL levels that we have so far had.
access content at IDUG.org (appropriate IDUG access required)

E03 SQL Performance for application developers was an introduction, with examples, about what an application developer should know about SQL at a minimum!
access content at IDUG.org (appropriate IDUG access required)

E04 was one of my presentations all about esoteric Db2 functions – Db2 stuff that is rarely used or not well understood. Covering FIT/FTB, Spatial Indexes, REGEX, Clones and scrollable cursors. All good fun!
access content at IDUG.org (appropriate IDUG access required)

E05 was all about IBM Db2 Developer Extension and Db2 Administration Foundation – Obviously the live demos are missing but it gives you a good idea!
access content at IDUG.org (appropriate IDUG access required)

E06 Advanced Db2 Performance Tuning for Beginners – the title says it all. Six objectives done great by Joe – It covered both LUW and z/OS and contained a “Steps to Solve the Crime” section.
access content at IDUG.org (appropriate IDUG access required)

E07 was a run through of all good stuff we got in Db2 12 including comparisons between 11 and 12 and an introduction to RESTful calls.
access content at IDUG.org (appropriate IDUG access required)

E08 was a plea for testing. How to generate test data and how to actually test and measure. Included examples of PLSQL to generate test data, and proposes the mantra to Measure and Monitor what you are doing and what you have done.
access content at IDUG.org (appropriate IDUG access required)

E10 was another one of my presentations where I go into detail about all currently deprecated features of Db2 12 and 13. It gave pages of SQL that you can use to check your own Db2 subsystem, or you can download our freeware MHC2 Migration HealthCheck program that does it all for you. (This is continually updated whenever anything new is deprecated, by the way!)
access content at IDUG.org (appropriate IDUG access required)

E11 all about “Things your DBAs hear”. A very good, light-hearted look at the “normal craziness” of being a DBA these days!
access content at IDUG.org (appropriate IDUG access required)

E12 A DBA’s epic journey covered how to deal with SLOW SQL and then the taming of four common SQL “problem statements”.
access content at IDUG.org (appropriate IDUG access required)

E13 was a very apt Session code! All about the usage and requirement of RECOVER these days. It covered why you should be able to do it and preparing for it as it will be required at some time…
access content at IDUG.org (appropriate IDUG access required)

E14 was a modernization call for Db2 stored procedures and RESTful services. Examples were included as well as Hints & Tips especially around DSNULI, Parameters and File usage of existing stored procs.
access content at IDUG.org (appropriate IDUG access required)

E15 covered how to fall back from a schema change as quickly as possible! Use of high speed flash copies to a clone show you a way to handle this.
access content at IDUG.org (appropriate IDUG access required)

E16 An overview of a “true” HTAP system. This showed how using an accelerator processing the logs you can indeed get to the Holy Grail of Transactional and Analytical processing happening at the same time on the same data.
access content at IDUG.org (appropriate IDUG access required)

F-Track

F01 was all about JAVA performance – and we all need better JAVA performance these days! Kudos for the callouts on Spring Batch and Hibernate.
access content at IDUG.org (appropriate IDUG access required)

F04 Back to basics with Db2 Buffer Pools – Covered everything you would ever need to know about Db2 Buffer Pools! Set-up, Monitor, Configure and Tune.
access content at IDUG.org (appropriate IDUG access required)

F06 explained the use of Indexes, how they look internally and all about performance, including when to REORG them at the optimal moment.
access content at IDUG.org (appropriate IDUG access required)

F07 SQL went crazy using Pivot and Transpose, some for z/OS some for LUW – a real smorgasbord of SQLs!
access content at IDUG.org (appropriate IDUG access required)

F11 contained a ton of detail about connecting Clients to Servers which is not quite as straightforward as some people think…
access content at IDUG.org (appropriate IDUG access required)

F12 Ran through the Db2 Catalog and Directory as it was, as it is and how to migrate to Db2 13.
access content at IDUG.org (appropriate IDUG access required)

F13 covered how to use the TRACE facility of Db2 including all the information you could ever want to know about which Trace is which class is which IFCID…
access content at IDUG.org (appropriate IDUG access required)

F14 was all about the perennial problem of Db2 logging and Commit frequency including full information about what is logged, what is written in the BSDS and adding/removing Active Logs.
access content at IDUG.org (appropriate IDUG access required)

F16 was DSC (Dynamic Statement Cache) usage, how it actually works, how to improve it and a quick glimpse into using the IDAA (Accelerator).
access content at IDUG.org (appropriate IDUG access required)

G-Track

G02 discussed the requirement for a Next Generation DBA. Having fewer people with the skills drives the demand for AI to help out.
access content at IDUG.org (appropriate IDUG access required)

G03 was very interesting as it was all about setting up Encryption through the SECPORT which is becoming standard these days. Full of configuration Hints & Tips. Also contained a full example of running NETSTAT and loading the output up into a Db2 table every few minutes so you can analyze who is accessing using just the TCPPORT – Heaven!
access content at IDUG.org (appropriate IDUG access required)

G05 AI again but this time protecting your systems from bad DBAT problems.
access content at IDUG.org (appropriate IDUG access required)

G06 Running through old Db2 releases up to current with special regard to the problem of RECOVERY and availability as they have changed over the years.
access content at IDUG.org (appropriate IDUG access required)

G12 went into depth about cutting back-up costs by using a hybrid-cloud multi-temperature storage system. Using Db2 for z/OS Data Gate delivered through IBM Cloud Pak for Data enables all of this. The big idea here, was to take your rarely used archive data and move it into the cloud.
access content at IDUG.org (appropriate IDUG access required)

G13 brought up the use of Redirected Recovery to ease your fears of recovery. You can simply validate, with no system interruptions of any kind, whether or not you are indeed even recoverable and, most importantly, how long it really takes.
access content at IDUG.org (appropriate IDUG access required)

G14 went into the IBM Cloud Pak world again, this time with virtualization being the main theme. The fact that the data lake has “dried up” due to various problems (GDPR being amongst them!) leads to virtualization being the way forward. DaaS – Data as a Service.
access content at IDUG.org (appropriate IDUG access required)

G16 and finally… we get to the last one, and it is a *very* big one all about Db2 Security Best Practices from David Beulke. An absolute treasure trove of Do’s and Do Not’s all related to the world of Audit. Our WLX Audit also gets a shout out so well done for that!
access content at IDUG.org (appropriate IDUG access required)

Summary

All in all it was a vast amount of information to try and take-in. IDUGs are always places of learning and I always learn stuff – I am now really looking forward to the IDUG EMEA 2022 in Edinburgh coming up from October the 22nd through to the 26th.

I hope to see you there!

TTFN,

Roy Boxwell

2022-07 IBM problem data requests…

Most of us have been there … something somewhere goes wrong … things are checked, changes are undone, tests are re-run and in the end you have no idea why a failure happens.

Who You Gonna Call?

Yep, it is time to open a Case at IBM technical support … So you open a Case and you type in as much detail as possible about when and what happened but it is *never* enough! In the world of Db2, the first question that *always* comes back is “Please supply us with further information”, like:

  • SYSLOG
  • Master Log
  • MEPL
  • Detailed EREP
  • Complete SVC dump

WTF? (“What’s That For” before anyone complains)

SYSLOG

The syslog is the console of a z/OS system and any and all interesting, and sometimes not so interesting, messages from *all* running “things” are in here – it is normally enormous! The problem begins when IBM Technical Support asks “please provide us with the SYSLOG from 06:00 to 06:30 on the day of the event”.

SDSF

SDSF is your friend here and I really mean it! All you do is go to SDSF and then enter primary command LOG. From this panel you enter three primary commands, one after another, and you are done!

  • PT ODSN ‘your.dataset.name’ * NEW
  • PT 06.00.00 22/06/2022 06.30.00 22/06/2022
  • PT CLOSE

That is it! Your dataset will then just have the data from between those times. This is *extremely* handy! Note that the date format is locale-dependent and, as I am in Europe, we have DD/MM/YYYY. I am sure you know your own date format!

Master LOG

This is the first SDSF dataset in your ssidMSTR STC. So, once more in SDSF, using *MSTR as a prefix and then putting line command ? next to the sub-system in question shows you three DDNAMEs. The first one, JESMSGLG, is the one they normally need. Here you use line command XDC to get an SDSF Open Print Data Set window:

xxxxMSTR STC09394           SDSF Open Print Data Set                         
COMMAND INPUT ===>                                         SCROLL ===> CSR
                                                                             
                                                                             
Data set name  ===> 'xxxxxxx.SYSLOG.PRINT'                                   
Member to use  ===>                                                          
Disposition    ===> NEW        (OLD, NEW, SHR, MOD)                          
                                                                             
Management class     ===>           (Blank for default management class)     
Storage class        ===>           (Blank for default storage class)        
  Volume serial      ===>           (Blank for authorized default volume)    
  Device type        ===>           (Generic unit or device address)         
Data class           ===>           (Blank for default data class)           
  Space units        ===> CYLS      (BLKS, TRKS, CYLS, BY, KB, or MB)        
  Primary quantity   ===> 19        (In above units)                         
  Secondary quantity ===> 19        (In above units)                         
  Directory blocks   ===>           (Zero for sequential data set)           
  Record format      ===> FBA                                                
  Record length      ===> 121                                                
  Block size         ===>                                                    
Data set name type   ===>           (LIBRARY, blank, ... See Help for more)  
Extended attributes  ===>           (NO, OPT, or blank)                      

Here you can see I choose type FBA, LRECL 121 and a disposition of NEW for a new dataset. Hit ENTER and SDSF tells you how many lines it just wrote to that file:

PRINT CLOSED  23025 LINE

View the file and max down to the bottom:

023019 0------ JES2 JOB STATISTICS ------        
023020 -  17 MAY 2022 JOB EXECUTION DATE         
023021 -            2 CARDS READ                 
023022 -       28,616 SYSOUT PRINT RECORDS       
023023 -            0 SYSOUT PUNCH RECORDS       
023024 -        3,099 SYSOUT SPOOL KBYTES        
023025 -    50,488.70 MINUTES EXECUTION TIME     

So we know we are in the correct file! Here you can do some updating of “sensitive” data like IP address, User Name etc. Remember to just change the data, not blindly delete it! Naturally, you can delete stuff *after* the event of interest and probably a ton of stuff from *before* but be careful what you delete!

MEPL

Say what? MEPL is the Module Entry Point List and IBM need it to see which PTFs and APARs have been applied in the application address space and the Db2 system. To get a MEPL I use a normal Utility job jcl with DIAGNOSE and a DISPLAY MEPL like this:

//MEPL     EXEC PGM=DSNUTILB,REGION=32M,       
//         PARM=(ssss,'DIAGNOSEMEPL')          
//STEPLIB  DD DISP=SHR,DSN=DSNsss.SDSNEXIT.ssss
//         DD DISP=SHR,DSN=DSNsss.SDSNLOAD     
//CEEDUMP  DD SYSOUT=*                         
//SYSUDUMP DD SYSOUT=*                         
//SYSPRINT DD SYSOUT=*                         
//SYSIN    DD *                                
 DIAGNOSE                                      
    DISPLAY MEPL                               
 DIAGNOSE END                                  
/*                                             

This will output the MEPL to SYSPRINT which starts like this:

DSNU000I    173 12:57:08.82 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DIAGNOSEMEPL                               
DSNU1044I   173 12:57:08.83 DSNUGTIS - PROCESSING SYSIN AS EBCDIC                                                      
DSNU050I    173 12:57:08.83 DSNUGUTC -  DIAGNOSE DISPLAY MEPL                                                          
DSNU861I    173 12:57:08.84 DSNUDIAG - DISPLAY MEPL FOR SUBSYSTEM xxxx                                                 
    0000 20B92820 C2C5D7D3 0140D4C5 D7D360D3  C9D2C540 C6D6D940 C4E2D5E4 E3C9D3C2    *....BEPL. MEPL-LIKE FOR DSNUTILB*
    0020 28100000 C4E2D5C1 C1404040 F0F761F1  F461F1F6 E4C9F3F9 F3F9F340 00000000    *....DSNAA   07/14/16UI39393 ....*
    0040 28100100 C4E2D5C1 D7D9C840 F1F261F2  F361F1F5 F1F34BF4 F6404040 00000000    *....DSNAPRH 12/23/1513.46   ....*
    0060 28100200 C4E2D5C6 D4D5C6D4 F1F061F1  F761F1F8 E4C9F5F8 F8F4F040 00000000    *....DSNFMNFM10/17/18UI58840 ....*
    0080 28100240 C4E2D5C6 D7D4E2C7 F1F061F1  F761F1F8 E4C9F5F8 F8F4F040 00000000    *... DSNFPMSG10/17/18UI58840 ....*
.
.
.

It is quite long! Here in my test system nearly 5000 lines are written to SYSPRINT. Then, like with the ssidMSTR, I use ? against the job and then XDC against the SYSPRINT DD card this time to create another file with type FBA and LRECL 133 to get your.mepl.list.

Detailed EREP

Now it gets interesting… The EREP (Environmental Record Editing and Printing Program) is the API to the system LOGREC dataset where all “events of interest” on a z/OS LPAR are recorded. It contains far less than the console log but is a treasure trove of data for the IBM Technical Support.

Here’s my job to simply do a Detailed EREP as per IBM standards:

//*------------------------------------------------------------------*/
//*  EREP: DETAILED REP PRINT                                        */
//*------------------------------------------------------------------*/
//EREP     EXEC PGM=IFCEREP1,PARM='CARD'                               
//SERLOG   DD DISP=SHR,DSN=xxxxxxxx.LOGREC                            
//DIRECTWK DD DISP=(NEW,DELETE),UNIT=SYSDA,SPACE=(CYL,(50,50))         
//EREPPT   DD SYSOUT=*,DCB=BLKSIZE=133                                 
//TOURIST  DD SYSOUT=*,DCB=BLKSIZE=133                                 
//SYSIN    DD *                                                        
ACC=N                                                                  
HIST=N                                                                 
ZERO=N                                                                 
PRINT=PS                                                               
TYPE=S                                                                 
/*                                                                     
//* IF REQUIRED YOU CAN ADD DATE, TIME RANGES TO FILTER DOWN           
//* WITHIN THE SYSIN LIKE:                                             
//* DATE=(YYDDD,YYDDD)                                                 
//* TIME=(HHMM-HHMM)                                                   

Do not forget to give your LOGREC DSN for the SERLOG DD. Most of the time I get just a few rows of output and then create another file using XDC from the EREPPT DD name but this time with type FB and LRECL 133 to get the.erep.list

Complete SVC Dump

If your Db2 system receives a dump, for whatever reason, it normally writes out an SVC dump to a special dataset that can be used to analyze what went wrong. It is very important that the SVC dump is complete and *not* partial …

Default Size

The default size is only 500MB which is way too small for a halfway decent production Db2 sub-system these days. It must normally be increased to at least 16000MB. To change this you issue a console command like:

CHNGDUMP SET,SDUMP,MAXSPACE=16000M

But make sure you have enough local page datasets space to handle your normal load PLUS the size of this dump dataset…auxilliary swapping (paging) while dumping is a painfully slow experience you do not want to suffer!

If successful, the SVC dump will be COMPLETE and then you are nearly done …

File Transfer

Most of the files I have described so far are quite small but the SVC dump is a monster. You must TERSE it using JCL like:

//AMATERSE  EXEC PGM=AMATERSE,PARM='SPACK'
//SYSPRINT  DD SYSOUT=*                   
//SYSUT1    DD DISP=SHR,                  
//             DSN=xxxxxxxx.xxxxxxxx       
//SYSUT2    DD DISP=(,CATLG),UNIT=SYSDA,             
//             DSN=xxxxxxxx.xxxxxxxx.TRS,  
//             SPACE=(CYL,(99,99),RLSE)   

I use the SPACK parameter which is, according to the documentation, much better at compression than the PACK parameter. Fun factoid of the day: SPACK is the “complex” format whereas PACK is the “simple” format – Gotta love IBM for that! IBM do prefer the TERSE style of compression, and please do *not* change the file ending! Then doing a ZIP has no real bonus and just confuses the automatic systems at IBM. Leave “.TRS” at the end and they know it has been TERSED.

Then download the xxxxx.xxxxx.TRS file as BINARY to the PC and all the other files as TEXT to the PC. Then simply upload by drag-and-drop to your IBM Case and you are ready for the next question!

Have you Switched it Off and On again?

I wish I never hear this about a mainframe Db2 problem!

I hope this was of some interest, and if you have any other Tips & Tricks about getting “standard” data to IBM, I would love to hear from you!

TTFN

Roy Boxwell

2022-06 Apollo 13 has landed!

Yes, I admit it, I was surprised that IBM actually called it Db2 13 for z/OS in the end. I know IMS also had a 13 release but I still believed they would jump to 14! After all Apollo 13 was a disaster, albeit with a happy ending caused by a whole bunch of engineers working really well with the hardware and the design. I guess that was the reason for the “Apollo” code name of this release of Db2. Anyways, on to my review of all things new and interesting in Db2 13.

What’ve We Got?

The most important bit is that you *must* be on Db2 12 FL510 to even think about getting to Db2 13. This is done to make the migration as easy as possible with no gotcha’s happening along the way!

System Check First!

Remember that automatic rebind will occur for any packages created before Db2 11. This should not happen but is never a good idea in production! Further, a whole bunch of ZPARMs have been removed and so you must check to see if you set these differently than default, if so then check what will happen in your shop with the “new” values. Here’s a direct link to the docu all about new, changed and deprecated ZPARMs: https://www.ibm.com/docs/en/db2-for-zos/13?topic=13-subsystem-parameter-changes-in-db2

FL100 Hits

FL100 allows you to fallback to Db2 12 FL510 and also permits co-existence in data-sharing. This enables 24×7 availability as you go round-robin with the load libraries and bounce your Db2 members.

What Else do You get at FL100?

Index look-aside optimizations are there for INSERT, UPDATE and DELETE.

Sort

It gets seriously enhanced:

  • the ability to generate machine code enabling DECFLOAT
  • if you use grouping set, multiple distincts and PERCENTILE you also get generated machine code
  • Sort can use its very own workfile
  • larger sort tree size
  • a check for ordered data on first iteration
  • LISTAGG gets SUBSTR support
  • Ability to avoid rereading a workfile, but only if Watson Machine learning is enabled
  • Shrinking the length of long varchars (over 100 bytes), again only if Watson Machine learning is enabled
  • z15 expanded support of SORTL, this also requires Watson Machine learning to be enabled

PBG Insert

The insert mechanism for PBGs got a nice update to drive a retry if the partition lock fails on the first insert attempt. This could well stop excessive growth of PBGs with heavy insert activity.

Below-the-Bar (BTB) Reduced and Above-the-Bar (ATB) Enhanced

As in all releases of Db2, the amount of BTB storage has been reduced primarily by reducing the agent BTB storage to use ATB storage instead. This applies to dynamic SQL statement text and attribute strings. When BTB storage exceeds 64% Db2 will automatically trigger a storage contraction of all private storage pools. Further, if you are at z/OS 2.5 you can use a new dynamic allocation function by updating the ALLOCxx parmlib member and set the SYSTEM SWBSTORAGE to ATB. This reduces the overhead per open dataset from 5KB down to 4KB. It might not sound much but when you have 200,000 open datasets it all adds up! The ATB storage clean-up also got improved to stop the usage of the IARV64 REQUEST(DISCARDDATA) during thread deallocation. Now a system level timer is used to trigger these clean-ups.

ECSA Reduced

For IFI users the ECSA usage has also been reduced from 50MB down to 8MB, however, you must make sure that you set aside around 50MB in HVCOMMON and 25MB for private storage to compensate for this. There is no such thing as a free lunch after all! The DDF per thread ECSA is now down to be the same as a local thread whereas before it was always 2KB more for a remote than a local thread. When the ECSA exceeds 85% Db2 will automatically trigger a storage contraction of all allocated pools within the ECSA.

DBAT Optimizations

These reduce the frequency and number of terminations and also flatten the spike when a surge of short-term DBATs increase usage quickly.

External Security Improvements

Db2 13 now caches the plan authorization checks as long as you have z/OS 2.5 and zparm AUTHEXIT_CACHEREFRESH set to ALL. At the same time, more IDs per plan are now cached.

Enhance RECOVERY Feature

RECOVER is enhanced so that it can use TP/IP level copies when a DSNUM ALL recover is requested. Pre-requisite is that it is only allowed on objects that are based on a UTS. Previously, RECOVER required a TS/IX level Image Copy to process this. Now it will check to see if it can do the RECOVER based upon the TP/IP copies that have been done.

FL500 Hits

AI

Artificial Intelligence finally comes to Db2 for z/OS with SQL data insights. These give cognitive intelligence to Built-in-Functions and can be used in SQL queries. More on these new functions later!

Role-Based Package Ownership

This completes the ability to use Trusted Context which assigns a ROLE to an inbound connection. It can now use this role as the package owner.

Utility Changes

Page sampling is now allowed for inline statistics. Within a REORG TABLESPACE or a LOAD utility you can now use page sampling (Like you can within RUNSTATS). Before this, only row sampling was allowed and page sampling can give a much bigger saving in CPU and elapsed time.

Online Conversion from PBG to PBR

As long as you have some sort of usable partitioning key you can now use ALTER to migrate from PBGs, which are getting very big and unwieldy, to the new and much better PBR RPN type UTS spaces. After the ALTER, a REORG simply completes the migration.

FTB Expanded

Unique index maximum size for FTB is now raised up to 128 bytes and for duplicate indexes up to 120 bytes. This greatly increases the possible scope of FTB usage.

Lock Timeout Controls

CURRENT LOCK TIMEOUT special register can be used to change the TIMEOUT by the application or even the SQL Statement. This can help reduce lock contention.

Profile Updates

The profile table can now be used for local threads as long as DDF is started with AUTO or COMMAND. These tables can now also contain CURRENT LOCK TIMEOUT and also the RELEASE_PACKAGE keyword with one of the COMMIT attributes.

Active LOG Delete

You can now delete an active log while Db2 is running, using the new REMOVELOG option from the -SET LOG command without having to stop and start D2b afterwards. This is very handy when you wish to resize all of your active logs!

How fast? – Very fast!

The first “real” FL version 500 has *no* catalog changes! This simplifies migration again as there is no “real” CATMAINT. There is still a CATMAINT job but it just sets internal flags and does *not* do any changes to the Catalog.

FL501 Hits

Deadlock Priority

New built-in global variable SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY gives you the ability to set your relative weighting about “who should die” when a deadlock is detected. This is also added to the profile tables by the way.

Catalog Changes!

One duplicate catalog index is, finally, dropped and the brand new SYSIBM.SYSUTILITIES table is created and will start to be INSERTed into, more about this table later! Some of the real-time statistics (RTS) statistical columns got uprated from INTEGER to BIGINT or SMALLINT to INTEGER. This helps for really big shops where the RTS numbers just maxxed out all the time! Further, lock escalation is disabled on all RTS and RTS History tables.

Index Splits

The RTS also got three new columns in the SYSINDEXSPACESTATS table:

  • REORGTOTALSPLITS – How many index splits since last REORG
  • REORGSPLITTIME – Aggregated elapsed time for all index splits since last REORG
  • REORGEXCSPLITS – Number of abnormally long (over one second) index splits since last REORG

All of these columns give good data about your index structures and possible changes to their definitions.

AI BiFs

We get three new Built-in Functions in Db2 13 FL500:

  • AI_ANALOGY – Computes an analogy score between two values
  • AI_SEMANTIC_CLUSTER – Computes a semantic clustering score of a member argument against a set of clustering arguments
  • AI_SIMILARITY – Computes a similarity score between two values

In fact, these are the *only* BiF changes in Db2 13 up to FL501! I will not go into detail about how they work here but there is a bit of work that must be done “behind the scenes” to get these beasts working… again – no such thing as a free lunch!

SYSIBM.SYSUTILITIES

Is a very interesting catalog table that contains a list of all Utilities that have run on the machine. It is updateable by anyone with the necessary rights so I recommend at least a yearly purge or it could grow to crazy sizes… The first support is pretty basic, just EVENTID, NAME, JOBNAME, UTILID, USERID, STARTTS, ENDTS, ELAPSEDTIME, CPUTIME, ZIIPTIME, RETURNCODE, CONDITION: Blank – Active or stopped utility, E – Execution has ended, F – Execution terminated by -STA db(x) sp(x) ACCESS(FORCE) or T – Execution terminated by -TERM UTILITY, RESTART: N – not restarted, Y – restarted, NUMOBJECTS, LISTNAME, STARTLOGPOINT: RBA for non-data-sharing or LRSN for data-sharing, GROUP_MEMBER, SORTNAME, SORTCPUTIME and SORTZIIPTIME. All of the other columns are “for future use” but with those columns that are filled you can do some interesting Utility analysis! The interesting factoid about this table is that all the time columns were originally planned as being CHAR(8)… Thankfully IBM decided to change them to BIGINT columns containing microseconds thus making the table actually usable!

Getting there…

Of course you have to get to Db2 13 before you can start using all the stuff I have just been discussing. To make sure you get there as quickly and painlessly as possible, why not download our Migration HealthCheck software? It is free-of-charge, with licenced extensions, software that lists out the “state” of your Db2. This includes all the prereqs for Db2 12 FL510 which must be reached before going to Db2 13 FL100 as well as all other deprecated features!

Any Plans?

What are your plans for going to Db2 13? I would be delighted to hear from you!

TTFN

Roy Boxwell

Update: One of my valued readers asked me if I could check whether or not LISTAGG accepts ORDER BY or not in Db2 13. Sadly I can confirm that only SUBSTR was added. ORDER BY still dies a death with an SQLCODE -390. My reader had even opened an AHA Idea request for this support, which I also voted for by the way, but it was rejected by Db2 support. The question that both of us have is “Why not?” What is so hard about adding sort support? My personal theory is that, internally, it uses a LOB style object format which is not sortable, but why not just tell us? In Boston I will ask around…

2022-05 Let’s get hashed

This month I will delve into the wonderful new field QUERY_HASH in the SYSIBM.SYSPACKSTMT Db2 Catalog table.

Isn’t it Old?

The QUERY_HASH column first appeared back in the Db2 11 tables SYSQUERY and DSN_STATEMENT_CACHE_TABLE where it is used to identify dynamic SQL and enable joining between these two tables.

Now it Gets Interesting

In Db2 12 it was added throughout the Catalog to tables SYSDYNQRY, SYSPACKSTMT and DSN_STATEMNT_TABLE, thus adding the availability to Static SQL as well as finishing the Dynamic SQL support.

For Static SQL the column is actually very interesting. I will now show you a list of little queries that you can use to see what it is and how to use it at your site.

First up: Baseline

How many static SQL statements do you have in the Db2 catalog at this moment?

SELECT COUNT(*)
FROM SYSIBM.SYSPACKSTMT A
FOR FETCH ONLY
WITH UR
;

Gives me:

---------+---------+---------+---------+---------+---------+
324675
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

0 = 0 = 0 ?

But that includes the “dummy” stuff in all packages, so let’s rerun with a better predicate:

SELECT COUNT(*)
FROM SYSIBM.SYSPACKSTMT A
WHERE NOT (A.SEQNO  = 0
       AND A.STMTNO = 0
       AND A.SECTNO = 0)
FOR FETCH ONLY
WITH UR
;

Which shows my true list of actual SQLs:

---------+---------+---------+---------+---------+---------
319015
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

So how many different hashes do I have?

SELECT COUNT(*)
     , HEX(A.QUERY_HASH) AS QUERY_HASH
FROM SYSIBM.SYSPACKSTMT A
WHERE NOT (A.SEQNO  = 0 
       AND A.STMTNO = 0 
       AND A.SECTNO = 0)
GROUP BY A.QUERY_HASH
ORDER BY 1 DESC
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY
WITH UR
;

Gives me:

---------+---------+---------+---------+-----
      QUERY_HASH
---------+---------+---------+---------+-----
73671 00000000000000000000000000000000
27228 40404040404040404040404040404040
12161 00000000000000000000000025B72000
 9821 40C7C5D7C9E240404040404040404040
 7372 00000000000000000000000024CC5000
 5989 000000000000000000000000257CD000
 5324 000000000000000000000000257CB000
 4530 40404070580000800000000000000000
 4265 1372041862047A61177D116D03002220
 4033 00000000000000000000000024E2F000
 3791 000000000000000000000000257EB000
 3114 27B830EAC5C4D44040C7C5D7C9E24040
 3089 7A69031C0174677E6844533C59555533
 2881 7B7F67796A17051E04077C027E142055
 2690 1473780D166A031F575A2F432047382F
 2446 6C166B000A6E13186161751F1A255340
 2264 6D760D7A75066A7A111A691E62592154
 2248 27B8353AC5C4D44040C7C5D7C9E24040
 2098 000000000000000000000000257BA000

Now I was a bit worried about all the low-values and all the 4040 entries but thought “The low-values are probably not bound or not executable or some such.” The spaces were more worrying! Then I noticed the rows with lots of leading zeroes…

Details, Details…

At this point I thought we needed to break down the SQLs between true “real” SQLs and “fake” ones – FETCH, OPEN, CLOSE, SET etc. which are not EXPLAINable. So I added the EXPLAINABLE column to the select to see if I was right:

SELECT COUNT(*)
     , A.EXPLAINABLE
     , HEX(A.QUERY_HASH) AS QUERY_HASH
FROM SYSIBM.SYSPACKSTMT A
WHERE NOT (A.SEQNO  = 0 
       AND A.STMTNO = 0 
       AND A.SECTNO = 0)
GROUP BY A.EXPLAINABLE, A.QUERY_HASH
ORDER BY 1 DESC, 2
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY
WITH UR
;

Gives me:

---------+---------+---------+---------+---------+--------
      EXPLAINABLE QUERY_HASH
---------+---------+---------+---------+---------+--------
73485 N           00000000000000000000000000000000
27228 N           40404040404040404040404040404040
12161 N           00000000000000000000000025B72000
 9821 N           40C7C5D7C9E240404040404040404040
 7372 N           00000000000000000000000024CC5000
 5989 N           000000000000000000000000257CD000
 5324 N           000000000000000000000000257CB000
 4530 N           40404070580000800000000000000000
 4055 N           1372041862047A61177D116D03002220
 4033 N           00000000000000000000000024E2F000
 3791 N           000000000000000000000000257EB000

Aha! So I guessed right all these, well over a third of *all* SQLs are not actually explainable and so a QUERY_HASH would be a little bit pointless.

Getting There…

So, now I added a predicate to remove all those:

SELECT COUNT(*)
     , A.EXPLAINABLE
     , HEX(A.QUERY_HASH) AS QUERY_HASH
FROM SYSIBM.SYSPACKSTMT A
WHERE NOT (A.SEQNO  = 0 
       AND A.STMTNO = 0 
       AND A.SECTNO = 0)
  AND NOT A.EXPLAINABLE = 'N'
GROUP BY A.EXPLAINABLE, A.QUERY_HASH
ORDER BY 1 DESC, 2
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY
WITH UR
;

Which gives me much better data:

---------+---------+---------+---------+---------+--------
    EXPLAINABLE QUERY_HASH
---------+---------+---------+---------+---------+--------
372 Y           6014030D641C1325583D214B504C3750
372 Y           3E4E5C30101603600A60620574076268
312 Y           70106E0C106E150F7274790C53255340
307 Y           49335C5B4A1C6B6276101914001D6D73
248 Y           53473E64001574120C191862767E1360

Enhanced It All!

Then I enhanced the query to now join back to the SYSPACKAGE and show me columns of interest from there, especially TYPE as I had a suspicion!

SELECT A.COLLID, A.NAME, A.CONTOKEN, A.TIMESTAMP
      ,A.BINDTIME, A.VALID, A.OPERATIVE, A.LASTUSED
      ,A.TYPE, B.STATUS, B.EXPLAINABLE, B.STATEMENT
FROM SYSIBM.SYSPACKAGE  A
    ,SYSIBM.SYSPACKSTMT B
WHERE NOT (B.SEQNO  = 0 
       AND B.STMTNO = 0 
       AND B.SECTNO = 0)
  AND     A.LOCATION    = ''
  AND     A.LOCATION    = B.LOCATION
  AND     A.COLLID      = B.COLLID
  AND     A.NAME        = B.NAME
  AND     A.CONTOKEN    = B.CONTOKEN
  AND     B.QUERY_HASH  = X'00000000000000000000000000000000'
  AND NOT B.EXPLAINABLE = 'N'
ORDER BY 2 , 1
FOR FETCH ONLY
WITH UR
;

Not my TYPE

Scrolling right to the TYPE column:

----+---------+---------+---------+---------+---------+---------+----
VALID OPERATIVE LASTUSED   TYPE STATUS EXPLAINABLE STATEMENT
----+---------+---------+---------+---------+---------+---------+----
Y     Y         2021-12-23      C      Y      DECLARE DB2JCCCURSOR8 C
Y     Y         0001-01-01      H      Y      DECLARE DB2JCCCURSOR1 C
Y     Y         0001-01-01      C      Y      DECLARE DB2JCCCURSOR3 C
Y     Y         2016-09-02 1
Y     Y         2016-09-02 1
Y     Y         2016-09-02 1
Y     Y         0001-01-01 1
Y     Y         0001-01-01 1
Y     Y         2022-05-12 N
Y     Y         2022-05-12 N
Y     Y         0001-01-01 T
Y     Y         0001-01-01 T

Aha! Advanced Triggers (1) , Procedures (N) and Normal Triggers (T). Functions(F) would also be there. There’s a Gotcha here, too: RESTful Services identify themselves like a normal package but with HOSTLANG=’R’, yet they do not have a usable Hash. So now remove all of these from the picture like this:

SELECT A.COLLID, A.NAME, A.CONTOKEN, A.TIMESTAMP
      ,A.BINDTIME, A.VALID, A.OPERATIVE, A.LASTUSED
      ,HEX(B.QUERY_HASH) AS QUERY_HASH
      ,B.STATUS, B.STATEMENT
FROM SYSIBM.SYSPACKAGE  A
    ,SYSIBM.SYSPACKSTMT B
WHERE NOT (B.SEQNO  = 0 
       AND B.STMTNO = 0 
       AND B.SECTNO = 0)
  AND A.LOCATION    = ''
  AND A.LOCATION    = B.LOCATION
  AND A.COLLID      = B.COLLID
  AND A.NAME        = B.NAME
  AND A.CONTOKEN    = B.CONTOKEN
  AND A.TYPE        = ' ' -- ONLY REAL PACKAGES
  AND B.EXPLAINABLE = 'Y' -- ONLY EXPLAINABLE SQL
  AND NOT B.HOSTLANG = 'R' -- NO RESTFUL SERVICES 
ORDER BY 2 , 1
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY
WITH UR
;

Now I get the real data out:

---------+---------+---------+---------+---------+---------+---------
COLLID     NAME    CONTOKEN TIMESTAMP
---------+---------+---------+---------+---------+---------+---------
PTFCOLL008 ADMDMEM +oæ (p8  2021-10-08-12.40.14.562716
PTFCOLL008 ADMDMEM +oæ (p8  2021-10-08-12.40.14.562716
PTFCOLL008 BUILDS  ?À ã     2016-12-28-08.50.58.486446
PTFCOLL008 CLEAN   â­â  r4   2016-12-28-08.50.59.911739
PTFCOLL008 CLEAN   â­â  r4   2016-12-28-08.50.59.911739
-+---------+---------+---------+---------+--
BINDTIME                   VALID OPERATIVE
-+---------+---------+---------+---------+--
2022-01-20-15.26.20.128052 Y     Y
2022-01-20-15.26.20.128052 Y     Y
2022-01-20-15.26.20.533383 Y     Y
2022-01-20-15.26.20.639940 Y     Y
2022-01-20-15.26.20.639940 Y     Y
-----+---------+---------+---------+---------+------
LASTUSED   QUERY_HASH                       STATUS
-----+---------+---------+---------+---------+------
2022-03-21 48534A5F7A741F0E75131067686F066A C
2022-03-21 585850457A760F066F091067686F0668 C
2022-02-18 6A0A777E720B7B671E703E40232C584B C
0001-01-01 59283E494E332341514B37572A29376F C
0001-01-01 6F051C041E1C136A0024374B293F3742 C
--------+---------+---------+---------+---------+---
STATEMENT
--------+---------+---------+---------+---------+---
DECLARE PTFTOOL-02 CURSOR FOR SELECT A . PMEMBERNAME
DECLARE PTFTOOL-01 CURSOR FOR SELECT A . PMEMBERNAME
DECLARE GET-MEMBER CURSOR FOR SELECT A . RMEMBERNAME
DECLARE PTFTOOL-02 CURSOR FOR SELECT D . PTFNO , D .
DECLARE PTFTOOL-01 CURSOR WITH HOLD FOR SELECT D . P

Finally, I get my “real” useful HASH data with text.

And Now?

So what can you do with the QUERY_HASH? One simple thing is to just use it to pull out all the duplicate (see note below!) SQLs that you have in different collections or packages:

SELECT A.COLLID, A.NAME, A.CONTOKEN, A.TIMESTAMP
      ,A.BINDTIME, A.VALID, A.OPERATIVE, A.LASTUSED
      ,B.STATUS, B.STATEMENT
FROM SYSIBM.SYSPACKAGE  A
    ,SYSIBM.SYSPACKSTMT B
WHERE NOT (B.SEQNO  = 0 
       AND B.STMTNO = 0 
       AND B.SECTNO = 0)
  AND A.LOCATION    = ''
  AND A.LOCATION    = B.LOCATION
  AND A.COLLID      = B.COLLID
  AND A.NAME        = B.NAME
  AND A.CONTOKEN    = B.CONTOKEN
  AND A.TYPE        = ' ' -- ONLY REAL PACKAGES
  AND B.QUERY_HASH  = X'621B6C6564170F63151C5E45544E4A40'
  AND B.EXPLAINABLE = 'Y' -- ONLY EXPLAINABLE SQL
  AND NOT B.HOSTLANG = 'R' -- NO RESTFUL SERVICES 
ORDER BY 2 , 1
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY
WITH UR
;

This shows me all the packages with the selected QUERY_HASH value:

---------+---------+---------+---------+
COLLID           NAME     CONTOKEN
---------+---------+---------+---------+
MDB2VNEX_TEST    DSMALTER ) }_8
RTDX0510RCH      DSMALTER î Ö t Y
RTDX0510_AT      DSMALTER À´ Â ¢
RTDX0510_BE      DSMALTER _K W y
RTDX0510_COLL_AN DSMALTER À´ Â ¢
RTDX0510_DA      DSMALTER î Ö t Y
---------+---------+---------+---------+---------+----
TIMESTAMP                  BINDTIME
---------+---------+---------+---------+---------+----
2019-04-08-14.40.11.723943 2022-01-20-15.23.56.457297
2021-11-10-08.34.22.949593 2022-02-28-09.51.35.203521
2021-11-09-10.25.16.043349 2022-05-10-09.05.47.146861
2021-11-17-06.25.25.922112 2022-03-21-13.44.10.759957
2017-11-09-13.17.35.820393 2022-04-25-14.27.48.875174
2021-12-01-11.36.44.218374 2022-03-21-13.44.49.788358
---+---------+---------+---------+--
VALID OPERATIVE LASTUSED   STATUS
---+---------+---------+---------+--
Y     Y         2021-11-22 C
Y     Y         0001-01-01 C
Y     Y         0001-01-01 C
Y     Y         0001-01-01 H
Y     Y         2021-03-24 C
Y     Y         0001-01-01 H
--+---------+---------+---------+---
STATEMENT
--+---------+---------+---------+---
INSERT INTO DSM_ALTER VALUES ( : H ,
INSERT INTO DSM_ALTER VALUES ( : H ,
INSERT INTO DSM_ALTER VALUES ( : H ,
INSERT INTO DSM_ALTER VALUES ( : H ,
INSERT INTO DSM_ALTER VALUES ( : H ,
INSERT INTO DSM_ALTER VALUES ( : H ,

This does add an extra, pretty neat, element to the DBA’s tool kit.

Useful for You?

Do you think you will be using this feature or just let external tooling handle all of this for your system?

I would be very interested to hear any, and all, of your thoughts!

TTFN

Roy Boxwell

Note: One thing that I have noticed is that the hash algorythm is not actually that good! I get duplicates which are not actually duplicate SQLs when only one – four characters are different (typically table names!) Not really a major problem but something you had all better be aware of!