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 10Some 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 0Now 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 ? 1You 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