Category: News from the labs

Well, not me of course! I am speaking about the Db2 work file database!

Remember when we had just one work file database DSNDB07 and that was it? We only had 4K and 32K work files and life was simple and easy?

Then came data-sharing in Db2 V4.1 and they added the AS WORKFILE because each member needed a set of work files, of course.

AS TEMP was created in Db2 V6 purely for declared global temporary tables. It had to be used if you had any DGTTs (Declared Global Temporary Tables) and the tablespace(s) had to be segmented and there had to be at least one 8K sized one otherwise the very first DGTT would fail. A problem from the get-go was that you could only have one TEMP defined Database and you also needed a “normal” work file Database thus causing two databases to be required …

Static scrollable cursor usage arrived in Db2 V7 which also required the definition of a TEMP database because “under the covers” they are just using DGTTs, really!

In Db2 V9 the AS TEMP clause was removed and the restriction of “one 8K must be there for DGTTs” was removed and changed to “at least one 32K must be there” – obviously, because you cannot have 8K and 16K work files…

Look what the Cat dragged in

Here in Db2 V9, the “problems” started as they had now introduced Universal Tablespaces (UTS), they dropped the AS TEMP usage and they changed the minimum bufferpool size of a DGTT work file. To handle this, a bunch of APARs came out revolving around the SECQTY number being zero or non-zero to “force” Db2 to use one or other defined work file for a particular usage or not (PK70060 for example). It was very messy and we all hated it!

The RoT

The IBM Rule of Thumb at this time was then “80% of workfiles table spaces should be defined as 32K” and that hasn’t changed!

Work File Usage

Basically, anything that requires a SORT like DML clauses (GROUP BY, ORDER BY, DISTINCT, UNION etc.), CREATE INDEX (If the table is not empty it must also do a sort to create the B-tree), some JOINs, some VIEW Materialization and Common/Nested Table Expressions (Think CTEs with recursive SQL here!), Created Global Temporary Tables and their evil twin brother Declared Global Temporary Tables, Scrollable cursors (They are really DGTTs), some non-correlated sub-queries and SQL MERGE statements, of course, will use a work file.

More than you need?

Work files are great things and are the work horses of Db2 if you ask me! Every DISTINCT, ORDER BY, GROUP BY , UNION etc. (see above) requires at least one and you, dear reader, probably have 60+ at least!

They started off being “easy” to handle. Simple tablespaces that we allocated using IDCAMS in 4K and 32K for the “wide” stuff. Remember those good old days? All gone today, of course! We can now simply allocate and deallocate using STOGROUP definitions but user defined (IDCAMS) still exists, at least for the SORT usage, for work file datasets.

SORT or DGTT

This is the major question behind the work files. Which type do you need for which function? It used to be HORRIBLE especially in the change from simple/segmented -> PBG -> SECQTY 0 or non-zero… I hated it all and I am sure you all did as well.

Here’s an old picture (that is still valid!!!!)

Now in Db2 13 FL 508, we get the FOR DGTT or FOR SORT attribute in the create TABLESPACE DDL to “tell” the system which tablespace to use for which function. This goes hand-in-hand with the WFDBSEP parameter. If you set this to YES and your “preferred” space is not available you die and get a -904 but, if set to NO then Db2 will happily try and allocate a non-preferred tablespace instead and only if none are available will you get the dreaded -904.

Never DGTT?

If you define a PBG tablespace as FOR SORT it will *never* be used for DGTT work.

In the IBM documentation are these little tables which have now been enhanced with the FL 508 update. First if the ZPARM WFDBSEP is “NO”:

and then if “YES”:

If “YES” you will get a -904 if it cannot find one!

You must also be very aware which work files can span over to other work files:

• Large concurrent sorts and single large sorts
• Created temporary tables
• Some merge, star, and outer joins
• Non-correlated subqueries
• Materialized views
• Materialized nested table expressions
• Recursive Common Table Expressions
• Triggers with transition variables
• RID List processing Overflow
• Sparse Index usage

For these cases it makes a lot of sense to have multiple defined work files all with the preferred tablespace attributes.

The other side of the coin are these operations that cannot span work files:

• Declared global temporary tables
• Scrollable cursors (DGTTs)
• SQL MERGE statements
• instead-of triggers

These work files must simply be big enough to handle the data!

Also remember that if the combined record length (So that is data length + key length + prefix) is >100 then Db2 attempts to use 32K work file datasets otherwise it chooses 4K work file datasets. This means a lot of shops have probably way too many 4k work files and nowhere near enough 32k work files!

Sort Explained

In the input phase the ordered sets of rows are written out to one or more work files. At the end of the input phase if there is more than one work file they are merged together, and if the number of work files ever exceeds “maximum number of sort work files” then an intermediate merge happens to free up some work files for yet more sorting.

The buffer pool is used and, if you are lucky, all of the data stays in the buffer pool so no I/O is done. This is unlikely, though. Further, the bufferpool size limits the number of work files. Here bufferpool tuning rears its head! You must check your bufferpools, *especially* the special one(s) you did for work files!

Trace it?

There are two good IFCIDs available for us to see what on earth Db2 is doing with all the sorts. Namely, 95 & 96. As you can see from the numbers they are very, very old indeed and, as far as I have heard, low overhead (I only collect the 96 as I do not care about the “partnered” 95 one.)

Limits?

Sure are!

• Maximum number of tablespaces in the work file database – 500
• Maximum number of DGTT indexes – 10000
• Maximum number of tables per agent – 11767
• Maximum sort key length – 32707 bytes
• Maximum sort key length for XMLAGG – 4000 bytes
• Maximum sort record length (key + data + prefix) – 65529
• Maximum row length as a result of JOIN – 65529

If either of the last two are exceeded your SQL will get an SQLCODE -670  – THE RECORD LENGTH OF THE TABLE EXCEEDS THE PAGE SIZE LIMIT error message which I think is a little bit misleading.

Bufferpool Changes

Earlier, I mentioned Bufferpool tuning, and the old recommendation to have a BP “just for DSNDB07” is not really correct anymore! You must actually have four buffer pools – two for 4K/32K spannable work and another two for unspannable work.

Simple rule of thumb is: monitor everything! Have a few 4K and lots of 32K all mix-n-matched with SECQTY 0 and/or the new DDL Syntax.

Fun Fun Fun!

ZPARMs to help or hinder!

WFDBSEP Default: NO. Valid values: NO, YES. Set to YES to force Db2 to use only the preferred tablespaces, otherwise an SQLCODE -904 is returned.

MAXTEMPS Default: Zero. Range 0–2147483647. You can put in here a number of MB that is the limit an agent can allocate. This is quite handy for stopping run-away cartesian join style transactions.

WFSTGUSE_AGENT_THRESHOLD Default: Zero. Range 0 – 100. Db2 can send an alert when nn% of all work files are in use by a single agent. You could set this to, say, 30 and monitor the xxxxMSTR to see who is hogging the work file space and take corrective actions.

WFSTGUSE_SYSTEM_THRESHOLD Default: 90. Range 0 – 100. Db2 can send an alert when nn% of all work files are in use in the entire system. Set to zero to switch off alerts.

How do you look right now?

Just run one of these queries depending on whether or not your subsystem is data sharing or not. First for the data sharing folks:

SELECT SUBSTR(TP.VCATNAME , 1 , 8) AS VCAT   
      ,SUBSTR(TS.DBNAME , 1 , 8)   AS DBNAME 
      ,SUBSTR(TS.NAME , 1 , 8)     AS TSNAME 
      ,TS.PARTITIONS               AS PARTS  
      ,TP.PARTITION                AS PART   
      ,TS.MAXPARTITIONS            AS MAXPARTS
      ,TS.BPOOL                               
      ,TS.PGSIZE                             
      ,TS.TYPE                               
      ,TS.DSSIZE                             
      ,TP.PQTY                               
      ,TP.SQTY                                
      ,TP.FORMAT                   AS F      
      ,TP.RBA_FORMAT               AS R      
      ,TP.CREATEDTS                          
FROM SYSIBM.SYSTABLEPART  TP                 
    ,SYSIBM.SYSTABLESPACE TS                 
    ,SYSIBM.SYSDATABASE   DB                 
WHERE DB.TYPE   = 'W'                        
  AND TP.DBNAME = DB.NAME                    
  AND TS.DBNAME = DB.NAME                    
  AND TS.DBNAME = TP.DBNAME                  
  AND TS.NAME   = TP.TSNAME                   
ORDER BY 1 , 2 , 3 , 4                       
FOR FETCH ONLY                               
WITH UR                                      
;
                                        

and then for non-data sharing folks:

SELECT SUBSTR(TP.VCATNAME , 1 , 8) AS VCAT   
      ,SUBSTR(TS.DBNAME , 1 , 8)   AS DBNAME 
      ,SUBSTR(TS.NAME , 1 , 8)     AS TSNAME 
      ,TS.PARTITIONS               AS PARTS  
      ,TP.PARTITION                AS PART   
      ,TS.MAXPARTITIONS            AS MAXPARTS
      ,TS.BPOOL                              
      ,TS.PGSIZE                             
      ,TS.TYPE                               
      ,TS.DSSIZE                             
      ,TP.PQTY                                
      ,TP.SQTY                               
      ,TP.FORMAT                   AS F      
      ,TP.RBA_FORMAT               AS R      
      ,TP.CREATEDTS                          
FROM SYSIBM.SYSTABLEPART  TP                 
    ,SYSIBM.SYSTABLESPACE TS                 
WHERE TS.DBNAME = 'DSNDB07'                  
  AND TS.DBNAME = TP.DBNAME                  
  AND TS.NAME   = TP.TSNAME                  
ORDER BY 1 , 2 , 3 , 4                       
FOR FETCH ONLY                                
WITH UR                                      
;

Actually, you can most probably run the data-sharing SQL at most shops, it just depends on how old your DATABASE definition is!

On my system it looks like this:

---------+---------+---------+---------+---------+----
VCAT      DBNAME    TSNAME     PARTS    PART  MAXPARTS
---------+---------+---------+---------+---------+----
DB2DD1    DSNDB07   DSN32K00       0       0         0
DB2DD1    DSNDB07   DSN32K01       0       0         0
DB2DD1    DSNDB07   DSN32K02       1       1       254
DB2DD1    DSNDB07   DSN32K03       1       1       254
DB2DD1    DSNDB07   DSN32K04       0       0         0
DB2DD1    DSNDB07   DSN32K05       0       0         0
DB2DD1    DSNDB07   DSN4K00        0       0         0
DB2DD1    DSNDB07   DSN4K01        0       0         0
DB2DD1    DSNDB07   DSN4K02        0       0         0
DB2DD1    DSNDB07   DSN4K03        0       0         0
DSNE610I NUMBER OF ROWS DISPLAYED IS 10

Some of the other fields of interest then look like:

---+---------+---------+---------+---------+---------+--
BPOOL     PGSIZE  TYPE       DSSIZE         PQTY    SQTY
---+---------+---------+---------+---------+---------+--
BP32K         32                  0         5120      -1
BP32K         32                  0         5120       0
BP32K         32  G         4194304            0       0
BP32K         32  G         4194304            0       0
BP32K         32                  0         5120      -1
BP32K         32                  0         5120       0
BP0            4                  0         5120      -1
BP0            4                  0         5120       0
BP0            4                  0         5120      -1
BP0            4                  0         5120       0

Now remember, this is only half of the story as this is what Db2 has in the Catalog. Armed with this data you can now go to ISPF 3.4 and see how the VSAM world looks by using the VCAT name (or finding out which HLQ your datasets have by other means).

For example, in my test sub-system I enter DB2DD1.DSNDBD.DSNDB07 hit ENTER and then scroll once to the right I get:

--------------------------------------------------------------------
 Enter "/" to select action                        Tracks %Used   XT
--------------------------------------------------------------------
DB2DD1.DSNDBD.DSNDB07.DSN32K00.I0001.A001           33180    ?     9
DB2DD1.DSNDBD.DSNDB07.DSN32K01.I0001.A001             450    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN32K02.I0001.A001            4845    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN32K03.I0001.A001            4845    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN32K04.I0001.A001           33180    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN32K05.I0001.A001             450    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN4K00.I0001.A001              435    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN4K01.I0001.A001              435    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN4K02.I0001.A001              435    ?     1
DB2DD1.DSNDBD.DSNDB07.DSN4K03.I0001.A001              435    ?     1

You can see that my first 32K space DSN32K00 has gone into extents – almost certainly caused by a run-away SQL somewhere. Same is true for DSN32K04 but with SMS Extent Constraint Relief it rolled up all XTs into the first one. Both have -1 as SECQTY which lets “Db2 do the magic” – In this case I will simply STOP the spaces, DROP the spaces, CREATE the spaces and START the spaces.

But how?

The problem is that in Db2 13 you cannot simply create a segmented non-UTS table space anymore. You must do something like this:

--                                                            
-- IF A NON-UTS WORK FILE IS REQUIRED THEN YOU MUST SET THE
-- APPLCOMPAT BACK TO DB2 12 FL503 AND REMOVE THE MAXPARTITIONS
-- CLAUSE          
--                                                            
SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M503' ;         
DROP TABLESPACE "DSNDB07"."DSN32K01"                           
;                                                              
COMMIT
;                                                              
  CREATE TABLESPACE "DSN32K01" IN "DSNDB07"                   
    USING STOGROUP SYSDEFLT                                   
--                                                            
--              USE SECQTY 0  TO STOP ANY EXTENTS             
--                                                            
--  PRIQTY    20480 SECQTY 0                                  
--                                                            
--              USE SECQTY -1 TO ALLOW DB2 SIZED SECONDARY EXTENTS
--                                                            
    PRIQTY    20480 SECQTY -1                                 
--  MAXPARTITIONS 1                                           
    BUFFERPOOL BP32K    
--  FOR DGTT/SORT      -- IF DB2 13 FL508 OR HIGHER   
;                                                              
COMMIT ;                                                       
SET CURRENT APPLICATION COMPATIBILITY = 'V13R1M508'
;         
COMMIT
;

Note that nearly all other options in the CREATE TABLESPACE are ignored, or not actually allowed, for work files. SEGSIZE for example is always 16.                     

Queueing this all up in a batch job is the way I still go for user managed work files:

//*                                                             
//* STOP WORKFILE TABLESPACE, DROP WORKFILE TABLESAPCE         
//*                                                            
//DSNTIAD  EXEC PGM=IKJEFT01,DYNAMNBR=20                       
//STEPLIB  DD DISP=SHR,DSN=DSND1A.SDSNEXIT.DD10                
//         DD DISP=SHR,DSN=DSND1A.SDSNLOAD                     
//SYSTSPRT DD SYSOUT=*                                         
//SYSPRINT DD SYSOUT=*                                         
//SYSTSIN  DD DATA                                             
  DSN SYSTEM(DD10)                                             
  -STOP DATABASE(DSNDB07) SPACENAM(DSN32K01)                   
  RUN PROGRAM(DSNTIAD)  PLAN(DSNTIA13) PARM('RC0')  -          
       LIB('DSND1A.RUNLIB.LOAD')                               
  END                                                          
/*                                                             
//SYSIN    DD DATA                                             
  DROP TABLESPACE DSNDB07.DSN32K01
;                           
/*                                                             
//*                                                            
//* IDCAMS CREATE WORKFILE TABLESPACE                           
//*                                                            
//IDCAMS   EXEC PGM=IDCAMS,COND=((0,NE))                       
//SYSPRINT DD SYSOUT=*                                         
//SYSIN    DD DATA                                             
  DELETE DB2DD1.DSNDBC.DSNDB07.DSN32K01.I0001.A001 CLUSTER 
  SET MAXCC=0                                                   
  DEFINE CLUSTER -                                             
       (NAME(DB2DD1.DSNDBC.DSNDB07.DSN32K01.I0001.A001) -      
              LINEAR REUSE       -                             
              VOLUMES(DD1011)    -                              
              RECORDS(24500 0)   -                             
              SHAREOPTIONS(3 3)) -                             
  DATA (NAME(DB2DD1.DSNDBD.DSNDB07.DSN32K01.I0001.A001))       
/*                                                              
//*                                                            
//* STOP WORKFILE DB, CREATE TABLESPACE, START WORKFILE DB     
//*                                                            
//DSNTIAD  EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=((0,NE))         
//STEPLIB  DD DISP=SHR,DSN=DSND1A.SDSNEXIT.DD10                
//         DD DISP=SHR,DSN=DSND1A.SDSNLOAD                     
//SYSTSPRT DD SYSOUT=*                                         
//SYSPRINT DD SYSOUT=*                                          
//SYSTSIN  DD DATA                                             
  DSN SYSTEM(DD10)                                             
  -STOP  DATABASE(DSNDB07)                                      
  RUN PROGRAM(DSNTIAD) PLAN(DSNTIA13) -
    LIB('DSND1A.RUNLIB.LOAD')
  -START DATABASE(DSNDB07)                                    
  END                                                         
/*                                                            
//SYSIN    DD DATA                                            
  CREATE TABLESPACE DSN32K01 IN DSNDB07                       
         BUFFERPOOL BP32K                                     
         USING VCAT DB2DD1                                    
;  
  COMMIT
;                                                      
/*

Or going with UTS and STOGROUP defined spaces:

//*                                                             
//* STOP WF TS, DROP WF TS, CREATE WF TS, START WF TS         
//*                                                            
//DSNTIAD  EXEC PGM=IKJEFT01,DYNAMNBR=20                       
//STEPLIB  DD DISP=SHR,DSN=DSND1A.SDSNEXIT.DD10                
//         DD DISP=SHR,DSN=DSND1A.SDSNLOAD                     
//SYSTSPRT DD SYSOUT=*                                         
//SYSPRINT DD SYSOUT=*                                         
//SYSTSIN  DD DATA                                             
  DSN SYSTEM(DD10)                                             
  -STOP  DATABASE(DSNDB07) SPACENAM(DSN32K01)                   
  RUN PROGRAM(DSNTIAD) PLAN(DSNTIA13) - 
      LIB('DSND1A.RUNLIB.LOAD')                               
  -START DATABASE(DSNDB07) SPACENAM(DSN32K01)                   
  END                                                          
/*                                                             
//SYSIN    DD DATA                                              
  DROP TABLESPACE DSNDB07.DSN32K01
;
  COMMIT
;                           
  CREATE TABLESPACE "DSN32K01" IN "DSNDB07"                   
    USING STOGROUP SYSDEFLT                                   
    PRIQTY    20480 SECQTY 0                                 
    MAXPARTITIONS 254                                           
    BUFFERPOOL BP32K    
;                         
  COMMIT
;                           
/*

So, when are you planning on going “all in UTS”? Or have you made it and found anything interesting? I would love to hear from you!

TTFN,

Roy Boxwell

This month I wish to delve down into the depths of threads. Going into as much detail as I think easily possible! Many thanks to one of my esteemed readers who wished this as a sort of Db2 Thread 101 boot camp style thing!

Remember: if you ever have a wish about some detail or topic you would like more data about – just drop me an e-mail and I will, most probably, add it to my list of things to do… These lists never ever get shorter, do they?

What is a Thread?

To start with, there are two types of thread…

1. Allied threads
2. Database access threads (DBATs)

Allied threads mean subsystems like TSO, Batch, IMS, CICS, CAF and RRSAF, and for the DBATs, these are remote access requests.

How does Db2 handle it?

It goes through a list of checks to see if it has enough scope for the request. Checks here are against various ZPARMs. Starting with the biggies:

CTHREAD (Default 200 Range 1 – 20000) Limit the number of allied threads.

MAXDBAT (Default 200 Range 0 – 19999) Limit the number of database access threads. Setting this to zero basically “hides” this subsystem from the outside world.

The sum of these two cannot exceed 20000.

If in data sharing you also get the bonus of queueing client connections:

MAXCONQN (Default OFF, Values: OFF, ON, 1 – 19999) If the number of in-use DBATs reached the MAXDBAT value then how many to allow waiting in a queue. If the number of “waiters” exceeds this value, then the oldest will simply be closed.

MAXCONQW (Default OFF, Values: OFF, ON, 5 – 3600) The time duration in seconds that a client connection waits before the client connection is closed.

For TSO and CAF you use:

IDFORE (Default 50 Range 1 – 20000) Limits the number of threads that are from Db2 TSO Foreground. This includes DB2I, QMF and any foreground applications.

IDBACK (Default 50 Range 1 – 20000) Limits the number of threads from batch. This includes batch jobs and utilities.

These two ZPARMS limit the number of threads indirectly.

For inbound DDF connections we have:

CONDBAT (Default 10000 Range 0 – 150000) Limits the maximum number of concurrent inbound DDF connections. It must be greater than or equal to MAXDBAT.

Finally, a couple of “master switches” are available to us:

IDTHTOIN (Default none Range 0 – 9999) This is the time out value on TCP/IP synchronous receive operations. If exceeded, the thread is cancelled and all locks and cursors are released. Threads are checked roughly every two minutes.

TCPKPALV (Default 120, Values: ENABLE, DISABLE, 1 – 65534) Is the TCP/IP config KeepAlive value to be overridden with a different value? ENABLE – the TCP/IP KeepAlive config value is not overridden. DISABLE – KeepAlive probing is disabled. Any other value – The TCP/IP KeepAlive config value is overridden with this value of seconds. If used, consider setting close to IDTHTOIN value. Top tip: Avoid using small values otherwise severe overhead may be caused.

Talking about Connections…

For Db2 to do any work for anyone, you must first “connect” to it. Quite how you connect to it depends on lots of things but there are two absolute classics:

1. Running a batch program on the mainframe
2. Running dynamic SQL that “wants” to run on the mainframe

There are others, of course, and we will get to them in good time, dear readers!

How does it work?

Well, if you want a program to talk to Db2, the two must be introduced to each other just like humans used to be introduced by exchanging letters. Programs and Db2 do it by “connecting” to each other. There are multiple connection possibilities which are actually called “z/OS attachment facilities”. They are:

• CICS (Customer Information Control System)
• IMS (Information Management System)
• TSO (Time Sharing Option)
• CAF (Call Attachment Facility)
• RRS (Resource Recovery Services)

Who you gonna call?

Depending on “where” you are running, you must pick one of these to “start the conversation”. For example, WebSphere users will all actually be connecting using RRS, whereas for TSO you can use TSO, CAF or even RRS. Which facility you can, or may, use really does depend on what you want to do and exactly where, logically, you are running.

The Others…

I mentioned earlier that there are other ways of talking to Db2 and this is, as far as I am aware, the full list:

• Static SQL
• Embedded Dynamic SQL
• ODBC (Excel etc.)
• JDBC (JAVA)
• SQLJ (Also JAVA)
• Db2 for Linux, UNIX and Windows (Dynamic distributed SQL)

The first two and the last in the list are the “normal” ways, but however you connect, you must build a thread first!

Threads in CICS

Every transaction in CICS needs its own thread. They are created by transactions at the point when the application issues its first SQL or Db2 command request. The thread stays active until it is no longer required which is normally a SYNCPOINT. Thread creation costs CPU, so when a thread is about to be released, CICS checks to see if it could be reused. If no other use exists then the thread is terminated, unless it is designated as “protected”, in which case it “hangs around” until the protection time limit expires, (by default this is two check cycles which equates to around 45 seconds). These protected threads make a lot of sense as it saves the high CPU cost of always creating and terminating a thread.

CICS has three types of thread:

Pool threads – These are your standard low volume CICS transaction threads unless you are doing something special. They are defined in the pool threads section of the DB2CONN definition.

Entry threads – These are a little bit special and are designed for fast response and high-volume transactions. They are defined using a DB2ENTRY definition.

Command threads – These are special as they are reserved for the CICS Db2 attachment facility for issuing commands to the DSNC transaction. If a command thread is not available it will automatically overflow to use one of the pool threads.

In CICS, you can use the CICS RDO (resource definition online) to tune and define the threads you have.

There is no “simple” ZPARM limit for CICS threads, it is controlled by the RCT (resource control table) TYPE=INIT THRDMAX value.

Threads in IMS

They are created by transactions at the point when the application issues its first SQL. The thread stays active until the application terminates.

The number of IMS regions is the maximum number of concurrent threads.

COMMIT

If the package controlling the SQL is bound with RELEASE(COMMIT), then at COMMIT everything is freed including the thread storage. If bound with RELEASE(DEALLOCATE), then thread storage can be released. This is where high performance DBATs are able to score points!

Re-use it or Lose it!

For allied thread re-use bind with RELEASE(DEALLOCATE) but watch out for the cursor table getting large. If using created temporary tables, the logical work file space is not released until the thread is deallocated.

For DBATs use thread pooling – Make sure ZPARM CMTSTAT is INACTIVE (The ACTIVE option is deprecated these days!). That’s it!

Deep Dive on DBATs

DBATs come with Inactive connection support by splitting out DDF connections from the actual threads that are doing the work. This creates a pool of DBATs created for inbound DRDA connections. A connection makes temporary use of a DBAT to execute a unit-of-work and then releases it straight back to the pool at COMMIT or ROLLBACK. The DBAT is counted as being in-use only when it is actively processing a request.

The benefit of this is obvious: a few DBATs and a large throughput! Each DDF connection uses just 7.5KB of xxxxDIST memory whereas each DBAT uses about 200KB.

All of this adds up to large CPU, real memory & virtual memory savings. There are, as always, a few annoying exceptions that “block” an inactive connection:

1. A hop to another Location
2. An open and HELD cursor, a held LOB locator or a package bound with KEEPDYNAMIC(YES)
3. A declared temporary table is active

Are you INACTIVE?

If you are using CMTSTAT = INACTIVE, and I hope you are, the DBATs can then come in three different groups: Ordinary Pooled, KEEPDYNAMIC-refresh and High-Performance:

1. Ordinary Pooled – If not being used it “hangs around” until the POOLINAC ZPARM is reached or, if being used, after processing 500 units-of-work.
2. KEEPDYNAMIC-refresh – To enable this you must first make sure that the you have enabled “automatic client reroute”, either in sysplex workload balancing or seamless failover for the group. These DBATs stay until unused for more than one hour plus a random number from 0 – 60 of minutes, or no new transaction for 20 minutes plus a random number from 0 – 20 of minutes is added. This option is very cool due to the point 2) mentioned in the list above as KEEPDYNAMIC(YES) is great for SQL Reuse but kills inactive DBATs. [The random numbers mentioned here are sometimes documented, sometimes not…] Using this, the client will seamlessly re-route the work to another DBAT without any impact to the application and also allow clean-up of the old DBAT.
3. High-Performance – For this the DDF PKGREL option must be set to be BNDOPT or BNDPOOL and the package must be bound with RELEASE(DEALLOCATE). Then the DBATs are associated with a package for the life of the DBAT. They are only terminated on a clean transaction boundary after 500 units of work, or the POOLINAC ZPARM is reached -unless it is set to zero, in which case 120 seconds is used. One last thing: BNDOPT will allow the DBAT to be deallocated when the connection terminates and BNDPOOL will return the DBAT to the pool when the connection terminates.

High Performance DBATs – Problem?

Of course! No such thing as a free lunch, is there? The problem with these beasts, is that they hold package locks and table space intent locks that basically kill you when you try to do ALTERs on objects, or some utilities. or even package REBINDs. You can be saved here by a temporary MODIFY DDF PKGREL(COMMIT) command to switch it off, do your important work and then switch it back on again! Do not forget this last step!!!

I hope you found this little stroll down the threading road interesting!

TTFN,

Roy Boxwell

Spoiler Alert: I didn’t win anything!!!

This month, I wish to review all the Good, Bad and Very Pretty things that happened in “my back yard” at the 2025 IDUG EMEA in Düsseldorf, Germany. I always really enjoy the IDUGs, where I get to meet all my co-workers, customers and friends from all over the world. All gathered just to learn more about Db2 and chat for a few days! This year, it was held in Düsseldorf, Germany which is where I work, so it was a bit of a busman’s holiday for me… That also meant that the sight-seeing part of going to beautiful cities like Nice, Florence, Rome, Prague, Las Vegas etc. sort of disappeared! Oh well – At least the food and drinks were free!!!

We, SEGUS Inc / SOFTWARE ENGINEERING GmbH, also took along four of our developers to Bathe in the Knowledge of all the Db2 Gurus there. I asked them all after it had finished what they thought:

Just us!

“Attending IDUG for the first time was an amazing experience! I met so many great people, learned a lot from the sessions, and really enjoyed the friendly and inspiring atmosphere.

It was a perfect mix of knowledge sharing and networking—I’m glad I joined and can’t wait for the next one!”

     “K3 Women in technology (WIT): Db2’s New Faces: Fresh Talent, Future Perspectives

• This was not, as some might have feared, a ladies’ tea party!
• Finding new co-workers may involve some of these strategies:
• Look for a mindset, not necessarily experience, (you can teach skill but you can’t teach talent)
• Mentor your candidates
• Keep teaching! Stay curious, keep them curious
• Can your company contact local universities about candidates? Or even just show universities that there is a NEED for mainframers?
• Make candidates/students work with green screen for a week before giving them a choice of tools
• Apparently, there is a European Mainframe Academy?

A203 Workshop – Next Generation Services for Db2 for z/OS Administration and Development – Workshop

• Learning how to use Admin Foundation
• That Visual Explain looks like it came straight from Netscape times. Considering that I remember those times, I now feel old …”

“Overall, a very worthwhile and interesting IDUG!

The sessions included the “usual” topics such as trends and directions, performance, and Db2 for z/OS utilities updates, etc. from IBM.

Noteworthy, was the continuing trend toward profile tables, including for monitoring Db2 connections, as well as the announcement of further enhancements in the Expert Panel.

The “Real Customer Experiences” from Commerzbank and the SWAT Tales and Personal Experience from Steen Rasmussen were also very interesting; real-life examples are very valuable.

The presentation on how to do Db2 development using Visual Code was exceptional in that it included a live demo that gave a good impression (once you’ve set up the environment) – very good.

Before the last keynote, there was a very entertaining and informative session on quantum computers.”

There you have it! Basically a great time was had by all – You read it here first!

Caveats

Now come my usual warnings and notices:

• I did not manage to attend *every* session, but I will do a small write up of each – If that session wasn’t held or the presenter was swapped out – I aplogize!
• To access all the presentation files, first open the IDUG website and click on “Events” and select IDUG EMEA 2025 then click on “Access the IDUG Presentation Library” where you must then give your logon credentials as you *must* be a member of IDUG and logged in, otherwise you will *not* be able to download the files! Once logged in, click on “Collections” to see the six different tracks and then simply download the presentations that grab your interest.

Starting at the Start with Track A Db2 for z/OS:

01 Haakon Roberts Trends and directions (No download available yet!) Haakon doing his usual great “pep” talk about where we are, and where we are going, without saying the number 14. Just remember the deprecated stuff that will stop you going there! Visit our web site and download our free software MigrationReadiness HealthCheck to find out all the blockers – way before they cause you any grief!

02 Db2 13 for z/OS Experience with New Features for Availability, Resilience and Performance with the great John Campbell. I missed this one, of course, because I was in the next room holding my presentation. I heard it was the usual great stuff though! John also mentioned my personal bug bear with page-level sampling on slide 14…

03 Db2 13 for z/OS: Five Key Features to Drive Performance and Innovation with Preetham Kannan. The highlight for me, was the Package Validity at Statement level and the lively discussion around this point! He reminded us all that Autobind Phase-In is also a game-changer!

04 The latest Db2 13 Online schema evolution and application performance enhancements with Frances Villafuerte. Frances started off with a brief history of tablespaces and how to easily migrate to UTS, as all other forms are nailed to their perches and will soon cease to be! Then she went through why to move from PBG to PBR. Further, the idea of ROWID as a hidden partitioning key completely hidden from the application was discussed before then going through the back-flip of PBR RPN to PBG! She finished off with a very nice explanation of why IAG2 can be good for you!

05 Db2 Analytics Accelerator: product updates, new version V8, and experiences from the customers with Cuneyt Goksu and Björn Broll. This was all about whether or not your workload may benefit from having an Accelerator or not, using the Workload Assessment via SMF data. Then they compared the two flavors on IBM Z or on LinuxONE before show casing the improvements with z17 and IDAA Version 8 review. Including very nice, flashy orange lines… pretending to be LOB data, I think! Then green lines appeared as data was cloned directly from IDAA to IDAA nice AOT (Accelerator Only Tables) data!

06 Optimizing SQL Pagination in Db2 for z/OS for Performance Gains from Emil Kotrc. A very entertaining walk down the history of paging forwards and backwards. Sounds simple, but actually it is a real minefield! Db2 has got much better, but there are still things you gotta watch out for and take care of, especially mixing multi-row and normal fetch, by accident normally, and OFFSET. A very good presentation indeed. (Yes, you guessed it, I was in this one and so was Joe!)

07 Db2 13 latest real customer experiences – new functions, best practices and some more… from Ute Kleyensteuber. Another goodie-filled presentation all about Db2 13, and a sneak peak of the FL508 stuff that was actually released on the 28^th October. Temporal support for the _AUTH tables came in with Db2 13 FL505. REORGs with DISCARD and a SECADM user id will be required!! Last Used for PLANs finally arrived as well in Db2 13 FL507 but watch out for invalid date formats… Then she detailed a year’s history of FTB usage and the new Image Copy ZiiP CPU savings at 55% – 60%! Ended up by giving us a nice sneak peek at the correct solution to split work file usage…

08 Db2 z/OS Dynamic SQL Monitoring: Best Practices from Michal Bialecki. He explained everything you ever wanted to know, but were afraid to ask, about Dynamic SQL! At the end is the link to the AHA idea 1796 – Please go and vote for it!

09 Modern System and Application monitoring: THE POWER OF DATA at Garanti BBVA with Hakan Kahraman and Toine Michielse. This was a deep dive into collected data from various sources. Lots of redacted graphics towards the end!

12 Db2 for z/OS Utilities: Unveiling Recent Updates and Current Developments with Haakon Roberts. Haakon ran through all the recent updates to the IBM utils, including APARs, for Db2 12 & 13 where required or even an FL required. RBDPM, for example. He then rounded off with a glimpse into the future…

14 Tools Maintenance Our Way with Martin Ålund. This is with notes! Here he describes the methodology to maintain your utils! Lots of SMP/E stuff – and scroll past the last page for some handy JCL for SMP/E Backup and Restore!

15 Billions of XMLs: How Do You Manage That? from Philip Nelson – a brief intro to why and what of XML and then off down the rabbit hole that are the differences between “normal” data and “xml” data in the z/OS context. UNLOAD/LOAD > 32 KB – nasty. Xpath index lengths – nasty.  Load from cursor fails with XML – nasty. Reorg Discard fails with XML – nasty. However, he shows you work-arounds for nearly all of these!

16 All about the Db2 Log: Updates, Commits, and Best Practices for Data Integrity from Emil Kotrc. A full explanation of what is actually LOGged, and why, plus who uses it anyway? Then off to DSN1LOGP usage and physical structure of log records. Then, repeating what we have very often heard: COMMIT, COMMIT, COMMIT! Lastly, a run through ZPARMs and messaging.

17 Claims, Drains and Automobiles: How Db2 Keeps Order in a Chaotic World with Marcus Davage. Here, Marcus took us on a voyage of discovery, all about the silent policemen who steer & control our data to do their thing! An excellent intro and overview of this, very often misunderstood, group of functions! Also included speakers notes as free extra bonus on the Blu Ray edition.

Track B Db2 for z/OS

No B01 as A01 is always parallel to it. We start therefore with:

02 RUNSTATS Master – reloaded, from my very good self! Learn all you ever wanted to know about RUNSTATS, and probably some you do not want to know! Contains a handy single slide look-up for all Optimizer used stats from the Db2 Catalog as a free bonus! Full of notes that all got sadly chopped by the upload to the IDUG server…

03 Key Performance Updates, z Synergy and Best Practices for Db2 for z/OS from Akiko Hoshikawa. Akiko doing her usual great stuff! z17 highlights, DS8K G10 highlights, Db2 13 Performance updates of course! This included the IRLM Lock Structure Rebuild boost, then the “hidden” CDDS feature that can now be used by everyone! Open Telemetry support also for RESTFul.

04 Taming Page Splits: Reduced Stress for DBAs in Db2 13 from Saurabh Pandey. B-Tree for beginners, and then a full discussion of the how and why of index page split leading to deeper, wider indexes. Even with asymmetric split still a lot of work especially if the split goes up the branch to the root causing a new level to be made! All of the logged pages are synchronous log writes (Enforces write-ahead logging!) Basically IFCID 396 and the new columns in RTS in Db2 13 FL501 are there to help!

05 Build a lightweight monitor to identify SQL workload tuning potential from Kai Stroh. This session showed how you can roll your own Db2 DSC monitor and how to use it to see if you have SQL problems – As we all do!

06 Db2 Under Siege from David Lea and Marcus Davage. All about cyber threats, how to protect yourself and how to recover in the worst case. Slides 16 – 18 are a classic list!

07 Mastering Access Path Management in Db2 for z/OS: Simplify, Optimize, Succeed from Denis Tronin. All about access path, EXPLAIN and its very many varied tables, use of Catalog stats, RUNSTATS, the two FEEDBACK tables and use of the BIND/REBIND control parameters APREUSE and APCOMPARE. For Dynamic SQL there are stabilized Dynamic SQLs. He then rounded off with a list of HINT methods. Very interesting indeed!

08 Db2 for z/OS all new “2025 SWAT Tales” from Anthony Ciabattoni. As always, a wonderful run through various things that might have saved ya from a serious problem! REBIND parameters, Statement level invalidation and then a nice list of things that are good for us, like recovery boost at IPL or Db2 Log sizing & management.

09 Protecting your Db2 for z/OS Environment from Cyber Attacks from Patric Becker. Ransomware and how Cyber Security and Cyber resilience can help you. Then all about Cyber Vault Immutable copies and either Surgical recovery or… Catastrophic recovery. You will require more storage though!

10 Partitioning Update from David Simpson. A quick run through the various deprecated TS types and then onto the different partitioning methods, including the differences between PI, DPSI and NPSI. Then a review of the PBR RPN and some example SQLs to review what you actually have, and finally, how to migrate to and from UTS spaces.

11 Personal Experience: 40 Years of Battle Scars from Managing Db2 for z/OS from Steen Rasmussen. Steen’s usual, very entertaining, round-up of 40 years of fun at the front! I loved slides 29 and 31 the best!

12 In memory table: What did you Expect? from Laurent Kuperberg. (I got a name check in this presentation!) This was all about configuring your BUFFERPOOL size to get a memory table. Why do it? How to do it? and Is it worth it? Spoiler alert: Yes, but not for all tables!

15 Who is in Your Db2? Auditing z/OS Like a Mainframe Maestro from Joern Thyssen and Christoph Theisen. Another Auditing session all about the stuff we must all do…Lists out all the IFCIDs and CLASSes you should look into – Like our very own WorkLoadExpert Audit Use Case for example! Also includes a nice section all about Audit Policies.

16 ISBANK’s Journey to implement CDC IIDR Remote Capture with a Resilient Architecture from Önder Çağatay and Gülfem Öğütgen. A very in-depth presentation about how their bank has implemented this solution and why they did it.

17 Automating Excellence: Real-world z/OSMF Workflows for Efficient Provisioning and Maintenance (a Db2 use-case) from Josiane Rodrigues and Kumari Anjali Maharaj. This was all about z/OSMF – Why they did it, who they did it with, and how it hangs together, especially for Db2 using VSCODE Workflows4z.

Track E “Themes I”

03 Db2 Universal Translator between z/OS and LUW from Dale McInnis and Jerome Gilbert. This was basically a side-by-side comparison of Db2 for z/OS and LUW. Contains everything about both systems. Very interesting indeed, especially the z / Common / LUW slide 35 and the Conclusion on 50.

04 Fear no Threads: Secure and Monitor Db2 Connections with Profile Tables from Toine Michielse. This was a very nice run through all the stuff that PROFILE tables now give us and how to use it to master DDF problems. New in Db2 13 was the ability to control/change local connections. A game changer for RELEASE(DEALLOCATE) and RELEASE(COMMIT) changes for example. The profile support for modelling ZPARMS, slide 14, is not 100% complete and you can go and vote/review my Aha Idea about this “DB24ZOS-I-1781 Complete PROFILE support for SQL tuning ZPARMS” Currently denied but I have no idea why!

06 Automating and operationalizing data-driven AI with Db2 SQL Data Insights – new APIs for full control from Steffen Exner and Christian Lenke. AI rears its head in Db2 for z/OS… It definitely has its uses and it will get better and better I am sure. This covers all you need for the tricky bits of authentication. Pro tip: Do not use Db2 UID/ PWD as clear text! Not even in test!!!

07 Transforming your Db2 image Copies to Data Pipelines for Generative AI from Mikhael  Liberman. With notes!! This follows on from E06 and delves into the Hows and Whys of data trustworthiness etc. Basically, structured data is much better for learning – No real surprise there! And what do we tend to have on Db2 for z/OS? Structured data! Sadly, the presentation got really ruined by the Monday Morning “quick transform” but it is still readable…  

08 Deep Dive Into SQL Data Insights from Thomas Baumann. Now we dive into real world of Db2 SQL Data Insights usage at Swiss Mobiliar with Thomas. Great stuff indeed! SQL examples of all the functions and real-world examples and walk-throughs of doing all the work. Essential reading if you wish to start with SDI! Ended with another use case of Bufferpool allocation types.

09 Unlocking the Power of AI with Db2 for z/OS from Akiko Hoshikawa. Yet more AI for you! Including the reveal that the next version will also be able to use IDAA for Vector Tables, and that the next version might well recommend Index and Runstats. System assessment and Performance insights explained in depth, and use of the Best Practices dashboards as well.

10 A Deep dive into Db2 Connect Best Practices from Shilu Mathai. Absolutely everything you will ever want to know about Db2 Connect – and with Notes! Included three slides just listing the different versions and how to bind the packages – very handy!

11 The Db2 for z/OS Agent Lets have a Chat with the Catalog! From Daniel Martin with notes! This is all about the IBM Db2 for z/OS Agent that is an AI powered “teammate” for troubleshooting and collaboration.

12 Mastering SQL Performance on IBM Z Analyzing and Optimizing Queries for Maximum Throughput from Saurabh Pandey. A great guide into how and why SQL does its thing and then goes on into EXPLAIN territory before branching off into all different types of access that Db2 uses.

14 The Ins and Outs of High Performance DBATs from Bart Steegmans and Gareth Copplestone-Jones. Another excellent presentation telling you absolutely everything you need to know to decide when and how to implement High Performance DBATs. It starts with a very nice description of what a DBAT is, with a full discussion of terms and meanings – useful stuff! Then introduces High Performance DBATs with slide 13 summing up implementation. A very important, and often completely forgotten/ignored, point about WLM Velocity goal changes for HPDBAT workloads is on slides 36 and 37.

15 Achieving Resilience with DORA and Db2 Tools: Enhancing Operational Continuity and Compliance from Julia Carter and Jose Arias. Ahhh! I love Audit!!! A run through everything you should be doing by now! One tiny point where I disagree, is on slide 26 where EXTSEC set to YES. I actually recommend NO, as YES gives away Db2’s existence in an attack. Better not to give the hacker any feedback at all and live with the fact that an end user cannot change the password using DRDA (Which I think is better anyway – Password changes should be centrally controlled!). Bottom line is: we all must do more, really…

Track F “Themes II”

02 Strategies for Making Db2 Data Accessible with APIs from Chris Crone. All about REST APIs and also with notes! A ton of info with examples galore about RESTful APIs – The Wall of Inefficiency will stay with me for a while!

04 Db2 z/OS in a Hybrid Cloud – A Survey of Architecture Options across AWS, Azure, Google and IBM Cloud from Daniel Martin. Another presentation with notes – I think Themes II is winning on this front! Shows you different way of storing your data off-premise and in a cloud – Plus and Minus points for all variants but leaning towards IBM of course 🙂

05 Db2 Joins In Depth from Tony Andrews. Full of notes, as I expect from Tony! Also full of JOIN info and predicate details. Essential reading for all SQL coders! Towards the end (Slides 43 and on) are some great Sparse Index explanations.

06 A day in the life of an MFA enabled DBA from Jørn Thyssen. This is all about understanding and using MFA for all your normal day-to-day work. MFA is ubiquitous and we must all use it nowadays – just due to Audit requirements. Jørn takes us through it all – History of passwords on z and the introduction and integration of Passtickets. Then into the brave new world of certificates… shudder… Then, for z/OS Developers, a couple of useful hints and tips on slides 48 to 51 are well worth reviewing!

07 Route to the roots…DSNZPARM from Manuel Gómez Burriel. A presentation which reviews and recaps some of the 300+ ZPARMs we have heard of and some forgotten! REALSTORAGE_MAX is an interesting candidate. Included are SET SQL commands that override IRLM (ZPARM) settings as well!… danger…

08 Tales of a DBA with Stored Procedures and UDFs from Soledad Martinez. She takes us through the whole Functions and Procedures methodology including trouble shooting and Migration. Handy tip for setting STAY RESIDENT NO in DEV but YES in PROD. Nice nod to the IVP DSNTEJ2U as well – Showing you how you can create your own nifty UDFs! Slide 51 is a handy xref for NUMTCB setting as well.

09 Modernize Db2 for z/OS Development with VS Code with Scott Davidson and Brian Jagos. The brave new world of GUI is charging headlong into the green screen crowd! We have to join the throng of VSCode people sooner or later – Better is sooner! Lots of side bar notes and then it ends in a great demo that obviously does not work in a PDF!

10 How to access Db2 for z/OS (and other Z oriented) data in the cloud from Cuneyt Goksu. All about where data can live and be secure and useful. Basically, stating that the application coders just using RESTful services no longer need to know, or even care about, where their data is, or even who is holding it! It is just “plumbing”…Adding IDAA into the mix also for “legacy” VSAM and IMS data is also a winner!

12 Enhance Performance with Db2 Multi-Row Processing from Chris Crone. Yet another great practical presentation all about multi-row coding. From first principles and examples, with test results as well. Spoiler alert – about 100 is the sweet spot! 🙂

14 Create Stored Procedure to ‘ReorgTable’ including table function for Select Reorg() and REST-Services from Veit Blaeser. The ability to let developers, just by single clicking a line in an excel table, fire off a REORG – Scary stuff, but great in test! Full of notes and example code but the last line of slide 25 is legend! (plus the note text!). Using this and the other REST/UDF presentations together gives you a very good cook book for doing a ton of things automagically! Slide 40 then gets pretty metaphysical…

15 Modernizing Db2 for z/OS System Management with Ansible from Marcus Davage. Once more dragged kicking and screaming into the harsh modern world! Includes notes though…and demos…

16 Pedal to The Metal – this is not your Daddy’s Accelerator! From Adrian Collett. A brief history of Accelerators and then all the new stuff and what you can do on them nowadays. Includes doing a self-assessment to see if it would help you (It will!) Then a whole bunch of real-world examples.

In Conclusion

Over 450 people, it was busy and I had a great time!

My name has obviously just slipped off of the bottom due to font problems… <cough> <cough>

and the winner was….

Congrats to all of the Speakers and many, many thanks to all the “behind the scenes” Guys and Gals that make an IDUG even possible, from the IDUG Staff to the Moderators and Speakers to the Sound and Lighting people. It really takes a lot of people to pull it off.

I hope you enjoyed my little review. Next month is our Happy Holiday Present Edition of my monthly Newsletter, with our traditional end-of-year-goodie, so stay tuned, folks!

TTFN,

Roy Boxwell

This month I wish to wander through the various types of data that Db2 squirrels away for our use. One of my favorite tables is the SYSPACKSTMT where, like its name suggests, every statement for every package is stored away. Within are a couple of very interesting, for me anyway, columns: STATUS and EXPLAINABLE.

Private Property!

First rule of SYSPACKSTMT is that any row where SEQNO, STMTNO and SECTNO are all equal to zero is internal for IBM use and must be ignored from all investigation or further use! In other words, the SQL must always look a little like this:

SELECT PS.* FROM SYSIBM.SYSPACKSTMT PS                        
WHERE NOT (PS.SEQNO  = 0
       AND PS.STMTNO = 0
       AND PS.SECTNO = 0)
  AND PS.EXPLAINABLE = ' '                        
LIMIT 5000                                        
;                                      

 What’s in a name?

Starting with EXLAINABLE – It is what it says on the box! If the statement can be used with the EXPLAIN statement, and I sincerely hope *all* of your production SQL has been explained at BIND/REBIND time dear readers, then this column has a “Y”. If, on the other hand, it is not EXPLAINable then you simply get an “N” in it and if it was last bound prior to Version 7 it will contain a Blank – I also really hope that none of your in-use production packages has a blank in it…

Filter it down…

This is a useful column to filter your SQL in this table. Basically, the logic here is that any SQL that is EXPLAINable does stuff that you, as a DBA, can check or alter. Otherwise, you cannot really affect it at all with normal means. An example is a FETCH Cursor statement. You cannot “tune” these, your only chance is in the cursor declare that the FETCH is using. Further simple examples are COMMIT and ROLLBACK – Very nice to know that your code is doing COMMIT and ROLLBACK but nothing to see here – move along please!

Sub-optimal docu ahead!

Ok, here’s the first bit of info… The docu is not really being honest with you when it states “Blank means last bound prior to Version 7.” and it also contains the text for QUERYNO – “If -1 this statement was bound before Version 7.” These are not 100% true, what it really means is “or is a non-standard package”. What you must do is *also* ignore the rows where SEQNO = 1 if the TYPE of the package is a “N”, “F” or “T” (“N” is a Native SQL routine package, “F” is a compiled SQL scalar function and “T” is a Trigger package). Further for Advanced Triggers you must also ignore all rows with a 2 in SEQNO as well!

How does it look?

So now you have this SQL that should only find all really non-explainable SQL, it still finds esoteric weirdoes but in my test installations they are really only ROLLBACK and COMMIT within Native SQL routines!

SELECT SUBSTR(PS.COLLID   , 1 , 18) AS COLLID
     , SUBSTR(PS.NAME     , 1 ,  8) AS NAME  
     , PS.CONTOKEN                           
     , PS.STATUS
     , PK.BINDTIME                           
     , PK.TYPE                               
     , PK.VALID                               
     , PK.OPERATIVE                          
     , PK.VALIDATE                           
     , PK.HOSTLANG                           
     , PK.REMOTE                             
     , PK.REOPTVAR                           
     , PK.LASTUSED                           
     , PS.STMT_ID                            
     , PS.QUERYNO                            
     , PS.EXPANSION_REASON                   
     , PS.STATEMENT                           
FROM SYSIBM.SYSPACKAGE  PK                   
    ,SYSIBM.SYSPACKSTMT PS                   
WHERE PK.LOCATION = PS.LOCATION              
  AND PK.COLLID   = PS.COLLID                
  AND PK.NAME     = PS.NAME                  
  AND PK.CONTOKEN = PS.CONTOKEN              
  AND PS.EXPLAINABLE = ' '                   
  AND NOT (PS.SEQNO  = 0                     
       AND PS.STMTNO = 0                     
       AND PS.SECTNO = 0)                    
  AND NOT (PK.TYPE IN ('N' , 'F' , 'T' , '1')
       AND PS.SEQNO  = 1                     
       AND PS.STMTNO = 0                     
       AND PS.SECTNO = 0)                    
  AND NOT (PK.TYPE = '1'                     
       AND PS.SEQNO  = 2                     
       AND PS.STMTNO = 0                     
       AND PS.SECTNO = 0)                    
ORDER BY 1 , 2 , BINDTIME DESC               
LIMIT 5000                                   
;

 It’s a matter of STATUS

Once you have studied all your EXPLAINABLE = ‘ ‘ rows we can then move on to “the meat and potatoes” of this month’s newsletter! What are all the different STATUS column values and what do they actually mean?

blank    The statement is non-executable, or was bound in a Db2 release prior to Version 5.

A            Distributed – statement uses Db2 private protocol access. The statement will be parsed and executed at the server using defaults for input variables during access path selection. [This was deprecated in DB2 V9 and dropped from support in DB2 10.]

B            Distributed – statement uses Db2 private protocol access. The statement will be parsed and executed at the server using values for input variables during access path selection. [This was deprecated in DB2 V9 and dropped from support in DB2 10.]

C            Compiled – statement was bound successfully using defaults for input variables during access path selection.

D            Distributed – statement references a remote object using a three-part name. Db2 will implicitly use DRDA access either because the DBPROTOCOL bind option was not specified (defaults to DRDA), or the bind option DBPROTOCOL(DRDA) was explicitly specified. This option allows the use of three-part names with DRDA access but it requires that the package be bound at the target remote site.

E            Explain – statement is an SQL EXPLAIN statement. The explain is done at bind time using defaults for input variables during access path selection.

F            Parsed – statement did not bind successfully and VALIDATE(RUN) was used. The statement will be rebound at execution time using values for input variables during access path selection.

G           Compiled – statement bound successfully, but REOPT is specified. The statement will be rebound at execution time using values for input variables during access path selection.

H           Parsed – statement is either a data definition statement or a statement that did not bind successfully and VALIDATE(RUN) was used. The statement will be rebound at execution time using defaults for input variables during access path selection. Data manipulation statements use defaults for input variables during access path selection.

I             Indefinite – statement is dynamic. The statement will be bound at execution time using defaults for input variables during access path selection.

J             Indefinite – statement is dynamic. The statement will be bound at execution time using values for input variables during access path selection. [Not documented, but I believe these entries are all the same as I entries but with REOPTVAR = ‘Y’.]

K            Control – CALL statement. [Not really CALL statements here!]

L            Bad – the statement has some allowable error. The bind continues but the statement cannot be executed.

M          Parsed – statement references a table that is qualified with SESSION and was not bound because the table reference is for a declared temporary table that will not be defined until the package or plan is run. The SQL statement will be rebound at execution time using values for input variables during access path selection.

O           Compiled for acceleration. The static query was bound successfully for acceleration and will be routed to an accelerator when executed.

Show us the data!

Let us re-write this SQL this time with the EXPLAINABLE negated and adding in a limiter to drop all the “DSN%” stuff as we are not really interested in all the IBM packages:

SELECT PS.STATUS,
       COUNT(*)                             
FROM SYSIBM.SYSPACKAGE  PK                  
    ,SYSIBM.SYSPACKSTMT PS                  
WHERE PK.LOCATION = PS.LOCATION             
  AND PK.COLLID   = PS.COLLID               
  AND PK.NAME     = PS.NAME                 
  AND PK.CONTOKEN = PS.CONTOKEN             
  AND NOT PS.NAME LIKE 'DSN%'               
  AND NOT PS.EXPLAINABLE = ' '              
  AND NOT (PS.SEQNO  = 0                    
       AND PS.STMTNO = 0                    
       AND PS.SECTNO = 0)                   
  AND NOT (PK.TYPE IN ('N' , 'F' , 'T' , '1')
       AND PS.SEQNO  = 1                    
       AND PS.STMTNO = 0                    
       AND PS.SECTNO = 0)                   
  AND NOT (PK.TYPE = '1'                    
       AND PS.SEQNO  = 2                    
       AND PS.STMTNO = 0                    
       AND PS.SECTNO = 0)                   
GROUP BY PS.STATUS                          
ORDER BY PS.STATUS                           
;                             

From my little test system, I get a nice smattering of values:

---------+---------+---------+---------+-----STATUS                                      
---------+---------+---------+---------+-----
              27822                         
C             99838                         
H              3210                         
I             63355                         
J                23                         
K                 6                         
M              2346                         
DSNE610I NUMBER OF ROWS DISPLAYED IS 7

Break it down!

I have 27822 Blank entries none of which were bound before DB2 V5! They are COMMIT (2914), CONNECT (778), DECLARE (21574), RELEASE (72), ROLLBACK (517), SET (66) and WHENEVER (1901) statements. All of the WHENEVERs are old code as I have removed all WHENEVER usage from our code base.

Naturally no-one should ever have A and B values anymore as they were deprecated in DB2 V9 and removed from support in DB2 10 (It was the DBPROTOCOL(PRIVATE) BIND/REBIND option if you want details)

C is where for a static shop most package statements will be found and you can see that it has the Lions share in my test system.

I have no three-part name usage, no EXPLAINs embedded in programs, no failed binds with VALIDATE(RUN), no bad SQL and also no Accelerator so no D, E, F, L or O status values.

The H‘s are CALL (124), CLOSE (659), DECLARE (830) Including DGTTs, FETCH (796) and OPEN (708). Naturally at BIND time there is nothing to do and so they all get marked with an “H” for Run time. There are also 93 “normal” SQL Statements here SELECT, INSERT, UPDATE etc. which should trigger a check to be done! In my case I know they are all for test cases and so all can be happily ignored.

I is our bread-and-butter – SQL Dynamic SQL ready to be prepared and run. I have CLOSE (4095), DECLARE (12810), DESCRIBE (68), EXECUTE xxxx (11829), EXECUTE IMMEDIATE (526), FETCH (12084), OPEN (4081) and PREPARE (17862).

J, for me at least, is just Assembler EXECUTE IMMEDIATE (23) statements all with REOPTVAR = Y.

K are all NULLID collection SYSSTAT statements all to do with FREE LOCATOR and SAVEPOINT statements with REMOTE = Y. No CALLs at all…

M is all of our SESSION table usage so we have DECLARE (530), DROP TABLE SESSION.xxxx (330), INSERT INTO SESSION.xxx (716), SELECT from SESSION.xxxx (435) and UPDATE SESSION.xxxx (335). This data is also filled if someone has decided to be evil and created a table with the CREATOR “SESSION”. It is not documented anywhere but then it is assumed by Db2 that it is a transient table and all package SQL ends up here as well – This is probably not what the application designer was thinking of and is *not* recommended for normal use! If you find any “normal” table usage here you have trouble brewing! On the other hand, it could be an extremely clever way of getting REOPT(ALWAYS). As an extra bonus/problem any SQL referring to SESSION tables (Including erroneously defined ones) also never makes it into the two Db2 caches!

IBM Use as well?

If you check the “DSN%” packages as well, then you will also see two G Status entries for DSNTIAUL where it is looking up the DEFAULT value for a ROWID column in a table. I am guessing that DSNTIAUL needs this to see if the DEFAULT is “A” – ROWID is GENERATED ALWAYS or “D” – ROWID is GENERATED BY DEFAULT.

Trust is good – Control is better!

The ones to control are the H non-standard records, the unexpected J entries and the M entries especially if the table is not really a SESSION table!

H like Hotel

Sounds like a dodgy Hotel to me! Sometimes rogue SQL makes it to production or you have very old packages hanging around and, in both cases, you can get H status records. In nearly all of these cases a DROP of the package cures the “problem”.

J like Juliett

What about poor smitten Juliett? Re-optimizing at every prepare, basically ignoring the DSC, can get ridiculously expensive and so these must all be checked to see “why?” and “How often?” Do they really need this functionality? In 99.99% of the cases probably not – It is only costing you every second time and onwards that they run for almost certainly no real benefit and should all be changed to be REOPT(NONE)

M like Mike

And as for Mike… Well, the mini-bind that happens every time does cost you CPU and elapsed time and if it can be reduced by, for example, the creation of a CREATE GLOBAL TEMPORARY TABLE xxxxx (CGTT) instead of a DECLARE GLOBAL TEMPORARY TABLE xxxxx (DGTT). This can be a real game changer. The differences in handling are small but very important to know between a CGTT and DGTT. Here is a great blog from Brian Laube on the IDUG web site all about these.

Well worth a read! After you have read this and reviewed your workload you can then action a plan to move from CGTT to DGTT or vice versa! As always, your mileage may vary, the cheque is in the post and It Depends!

I also wrote a newsletter way back in 2019 all about CGTT and DGTT performance.

Time to Check!

I hope this month’s newsletter has been of interest and spurred a review of your SQL Statements!

TTFN,

Roy Boxwell