Once more into the breach, dear friends! Yes, we are doing a very quick EXPLAIN update again … This is one topic that never ever ever gets old or cold! This month it was triggered by a DBA colleague who emailed me for help as he tried to use the CTE Opthints that I wrote about way way back in 2021 and hit trouble …
The Story begins …
Nearly all good stories start with a misdirected email … Then it was finally forwarded to me, and I saw the SQL attempt that he was making. Now it is a bit odd that he *wants* multiple index access because that is like Hybrid Join … That’s not really „up there“ with desired access paths!!! However, he made the case very well in this one very, very special situation, and with host variables, that the data skew was so bad that multiple index access worked best.
How did it look?
Here’s the DDL and SQL, redacted to protect one and all so you can try this one at home!
Table:
CREATE TABLE CTETEST.CTETABTEST ( COL1 TIMESTAMP NOT NULL ,COL2 TIMESTAMP NOT NULL ,COL3 TIMESTAMP NOT NULL WITH DEFAULT '9999-12-31-24.00.00.000000' ,COL4 TIMESTAMP NOT NULL ,COL5 CHAR(24) NOT NULL WITH DEFAULT ,COL6 TIMESTAMP NOT NULL WITH DEFAULT '0001-01-01-00.00.00.000000' ,COL7 TIMESTAMP NOT NULL WITH DEFAULT '0001-01-01-00.00.00.000000' ,COL8 TIMESTAMP NOT NULL WITH DEFAULT '0001-01-01-00.00.00.000000' ,COL9 TIMESTAMP NOT NULL WITH DEFAULT '0001-01-01-00.00.00.000000' ) CCSID EBCDIC ;
All Indexes:
CREATE UNIQUE INDEX CTETEST.INDEX000 ON CTETEST.CTETABTEST ( COL1 ASC ,COL2 ASC ,COL3 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX001 ON CTETEST.CTETABTEST ( COL9 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX002 ON CTETEST.CTETABTEST ( COL1 ASC ,COL4 ASC ,COL3 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX003 ON CTETEST.CTETABTEST ( COL2 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX004 ON CTETEST.CTETABTEST ( COL5 ASC ,COL3 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX005 ON CTETEST.CTETABTEST ( COL4 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX006 ON CTETEST.CTETABTEST ( COL6 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX007 ON CTETEST.CTETABTEST ( COL7 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; CREATE INDEX CTETEST.INDEX008 ON CTETEST.CTETABTEST ( COL8 ASC ) USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 NOT CLUSTER BUFFERPOOL BP0 ; COMMIT ;
Feel free to only use the last four for any tests you want to run!
The Code?
The SQL looked like:
SELECT COL1
, COL2
, COL3
, COL4
, COL6
, COL7
, COL8
FROM CTETEST.CTETABTEST
WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP
AND ( COL4 = ?
OR COL6 = ?
OR COL7 = ?
OR COL8 = ? )
;
And the Aim of the Game was?
He wanted a multiple index access path on all four of the OR predicates. If you EXPLAIN the above query „asis“ without data or RUNSTATS:
SELECT ACCESSTYPE FROM PLAN_TABLE WHERE QUERYNO = 1 ; ---------+---------+---------+-------- ACCESSTYPE ---------+---------+---------+-------- R DSNE610I NUMBER OF ROWS DISPLAYED IS 1
Surprise, surprise! You get a Tablespace scan (R for Relational scan in the Jargon).
First up!
First attempted SQL code looked like:
-- FIRST ATTEMPT AT GETTING MULTI-INDEX ACCESS EXPLAIN ALL SET QUERYNO = 2 FOR WITH DSN_INLINE_OPT_HINT (QBLOCKNO,TABLE_NAME, ACCESS_TYPE, ACCESS_NAME) AS ( VALUES (1, 'CTETABTEST' , 'MULTI_INDEX' ), (2, 'CTETABTEST' , 'INDEX', 'INDEX005' ), (3, 'CTETABTEST' , 'INDEX', 'INDEX006' ), (4, 'CTETABTEST' , 'INDEX', 'INDEX007' ), (5, 'CTETABTEST' , 'INDEX', 'INDEX008' )) SELECT COL1 , COL2 , COL3 , COL4 , COL6 , COL7 , COL8 FROM CTETEST.CTETABTEST WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP AND ( COL4 = ? OR COL6 = ? OR COL7 = ? OR COL8 = ? ) ; ---------+---------+---------+---------+---------+------- DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
And???
Things to note: SQLCODE IS 0 should never be output for an inline CTE Opthint. If you read my old Newsletter, you will see you should only get a +394 or +395. In this case it simply ignored the CTE completely!
The result of the above EXPLAIN:
SELECT MIXOPSEQ, ACCESSTYPE, ACCESSNAME FROM PLAN_TABLE WHERE QUERYNO = 2 ; ---------+---------+---------+--------- MIXOPSEQ ACCESSTYPE ACCESSNAME ---------+---------+---------+--------- 0 R DSNE610I NUMBER OF ROWS DISPLAYED IS 1
No change here!
Next up!
My try:
-- SECOND ATTEMPT AT GETTING MULTI-INDEX ACCESS EXPLAIN ALL SET QUERYNO = 3 FOR WITH DSN_INLINE_OPT_HINT ( ACCESS_TYPE, ACCESS_NAME) AS ( VALUES ( 'MULTI_INDEX', 'INDEX005' ), ( 'MULTI_INDEX', 'INDEX006' ), ( 'MULTI_INDEX', 'INDEX007' ), ( 'MULTI_INDEX', 'INDEX008' )) SELECT COL1 , COL2 , COL3 , COL4 , COL6 , COL7 , COL8 FROM CTETEST.CTETABTEST WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP AND ( COL4 = ? OR COL6 = ? OR COL7 = ? OR COL8 = ? ) ; ---------+---------+---------+---------+---------+---------+-+------ DSNT404I SQLCODE = 394, WARNING: USER SPECIFIED OPTIMIZATION HINTS USED DURING ACCESS PATH SELECTION DSNT418I SQLSTATE = 01629 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXOPCO SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 20 0 502 1115050228 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000014' X'00000000' X'000001F6' X'427650F4' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
The result of this EXPLAIN:
SELECT MIXOPSEQ, ACCESSTYPE, ACCESSNAME FROM PLAN_TABLE WHERE QUERYNO = 3 ; ---------+---------+---------+--------- MIXOPSEQ ACCESSTYPE ACCESSNAME ---------+---------+---------+--------- 0 M 1 MX INDEX005 2 MX INDEX006 3 MU 4 MX INDEX007 5 MU 6 MX INDEX008 7 MU DSNE610I NUMBER OF ROWS DISPLAYED IS 8
BINGO!
As you can see, I reduced the input to the CTE to the absolute minimum just telling the Db2 for z/OS Optimizer the four index names I wanted it to use in a MULTI_INDEX access type and Voila! It worked!
Really?
I emailed my test results to the DBA and he confirmed that my changes work in production and he is now a very happy bunny!
First Time for Everything!
This was the first time I have tried to force multiple index access for a query but you never know if this might be of use to some other DBA fighting the fight and struggling with dodgy access path decisions!
Quantum is being really heavily hyped at the moment and even my good self has been caught up in some of the Hoo-ha around it! Specifically which parts of Db2 are not „quantum safe“?
Quantum?
Yep, not like the TV Series „Quantum Leap“ at all! The idea of quantum data goes back many, many decades but has only recently really started earning a living instead of being a scientific curiosity! There are six basic concepts that you must know a little about to start to „grasp the basics“ of Quantum Computing.
Have you Qubit off more than you can chew?
The most important bit about quantum computers is the fact that they have qubits and not bits! The difference is staggeringly huge and mind-bendingly weird but boils down to the simple fact that a bit can be 0 (off) or 1 (on) – That’s it folks! On the other hand, a qubit can be anywhere between 0 (off) and 1 (on), in fact an infinite number, and it gets weirder when you have more qubits to play with. They do not join linearly but exponentially! This means that with 1,024 Qubits you have a mind-meltingly high number of possible values.
Ice Ice Baby!
You gotta keep qubits cold and I mean really cold… best is 0o Kelvin (Absolute zero) which is pretty nippy in July! For me in Germany that is -273.15o Celsius and for those of you „over the pond“ a very large negative number of Fahrenheit! Ok, the actual number is -459.67o Fahrenheit. Naturally, we never can reach 0o Kelvin but we can get pretty close these days and at this temperature it minimizes error rates and enables superconducting behavior as well. The warmer they get the more they are prone to losing their quantum state, or decoherence in the jargon.
Superposition – the first Concept
Qubits are in a superposition of all possible values between 0 and 1 until they are measured. Then they snap back, the quantum state collapses is the correct term, and resolve to a 0 or 1 – This gives you the „answer“ if you like. We can never fully predict the outcome but we can calculate the probability of a given outcome.
Entangled – the second Concept
Entanglement is crucial to getting Quantum computers to do any meaningful work for us humans. It is, at a minimum, two particles that are paired up in such a way that the quantum state of one particle cannot be determined independently of the state of the other, irrespective of the distance between the two. In other words, you can work out the quantum state of a particle when it is a light year away from its partner! Einstein famously wrote in 1947 about „spukhafte Fernwirkung“, in English „spooky action at a distance“ as he could not believe this! But it has now been proven over and over that entanglement does happen and with many more than just two qubits.
Exponential speed-up?
Using entanglement puts two or more qubits into a single shared quantum state, so they stop behaving independently and combined with interference (coming up) that is where the real speed-up lives. Please also remember that measuring one does instantly pin down the others, but no usable information actually travels faster than light. For one qubit we have probabilities distributed over two states. For two qubits the distribution goes over four states, for three qubits it is eight states etc. In general, the rule is for N qubits the probability is over 2n states.
Interference – the third Concept
We have all thrown stones into a pond or the sea and seen the ripples spread outwards. When the waves bounce back from the edge of a wall or boat you might have noticed that the waves interact with each other. When the peaks meet, you get a big wave. When troughs meet, you get a big hole. And when a peak meets a trough – nothing.
Give us a Wave!
In the quantum world this a wave function which is a mathematic description based on polynomials. To measure the entangled qubits, you just add the individual wave functions for every qubit until you have a single wave function of a single quantum state. This adding of the wave function gives us an interference pattern where we can change probabilities of the correct answer by using constructive interference (The peaks of the wave add up) or reduce the probability of a wrong answer by using destructive interreference (Where the two waves cancel each other out).
Tunneling – the fourth Concept
Now it gets weird! (Only now, you ask? Well …) This is where an object can pass through an energy barrier that within normal classical physics it should not be able to pass through! Think of a high mountain and then imagine that someone has bored a tunnel at the base of the mountain right on through. If the object can use the tunnel, it will make it much faster to the other side! Remember, the wave function is a probability so when this function encounters a potential barrier and if the barrier is thin enough it can indeed tunnel through and then there is a chance that the particle will appear on the other side.
Examples abound!
Nuclear fusion in stars is where protons overcome their electrostatic repulsion which allows the sun to shine, or radioactive decay where Alpha particles tunnel out of the nucleus. Modern electronics rely on this phenomena for tunnel diodes which were invented in 1957, for scanning tunneling microscopes invented in 1981 and also for flash memory as well.
Synchronization – the fifth Concept
We have all been to concerts where people start clapping. At some point all the clapping synchronizes and gets very loud! This is where the wave functions of independent particles can all „march to the same beat“ and spontaneously synchronize – this is very important when using certain algorithms.
Quantum Parallelization – the sixth Concept
Behind this is the really weird idea of parallel universes where each universe does one calculation and with N parallel universes the time of one calculation is enough for *all* calculations. At the moment of „observation“ the qubit ends in a „collapsed“ state and you get a result. This is also used in quantum chip design where the second, or more, input can be influenced by the first input!
The Traveling Salesman gets his Route!
The Traveling Salesman is a very old problem, from 1930, where you must calculate the optimal route for a poor traveling salesman to visit N cities with the shortest route, visit each city only once and return to the starting city. It sounds „easy“ but is actually NP-Hard. In math terms this means it is not solvable in polynomial time and for every city added the time goes up exponentially. If you have three cities anyone can do it but when you have just 20 it gets practically unsolvable!
Give me numbers!
A raw brute force „try every possible route“ approach takes a running time within a polynomial factor of O(n!) where O is the Order of growth in Landau notation – fittingly, it comes from the German word „Ordnung“ (Order). So you can see that after 20 Cities the numbers get silly. The Held-Karp algorithm from 1962 solves it in time O(N22N) whereas the latest quantum algorithm from Ambainis et al. from 2019 runs in just O(1.728N).
Cities
Brute Force
Held-Karp
Quantum
3
6
72
5
10
3,628,800
102,400
237
20
2.43 x 1018
419,430,400
56,347
As you can clearly see, Quantum Computers will greatly help with this problem and all derivatives in logistics, and DNA sequencing, for example. Note that the quantum solution is still exponential but just not as bad as before as it is a NP-Hard problem after all!
Spinning on your Heels?
To get this level of improvement the Quantum Computer uses an Ising Machine where spinning particles are each a city and they will eventually all synchronize on the most efficient path. Calibration is on various factors but for the Traveling Salesman problem it is on distance.
Now we are Experts – Why?
Factoring of numbers is the number one reason, as of today, for doing all this! But, unlike the Traveling Salesman, factoring is not actually NP-Hard – it sits in a curious middle ground, and it is exactly that hidden structure a quantum computer can exploit (quantum computers are not believed to crack genuine NP-Hard problems). Encryption is basically two large prime numbers multiplied together. Computers can do that very easily! Going back, or factoring, from a huge semi-prime number to the two starting primes is, however, very difficult for a classic computer. Shor’s algorithm lets a quantum computer recover those prime factors efficiently – not by trying everything at once, but by exploiting interference – and that breaks the public-key (asymmetric) encryption such as RSA and ECC which protects key exchange and digital certificates.
Grover’s Algorithm
Lov Grover created this in 1996 and it is basically an enhanced search with O(√N) evaluations but it can brute-force a 128-bit key in 264 iterations or 256-bit in 2128 iterations. It is quadratically better than simple brute-force and there are improvements on their way – especially the class of non-local hidden variable quantum computers could do the search in O(∛N) – the cube root of N – evaluations instead of O(√N) evaluations!
Finally Db2!
Yeah! I made it nearly to the end before even getting into the reason for the newsletter!!! The problem is that all of our data is encrypted, most of you are using 128-bit keys which are ok and are still pretty hard to crack. Anything less than 128-bit keys is crackable now in a brute force attack. The recommended way forward and to remain „Quantum Safe“ is to change all your encryption today to use at least 256-bit keys. These will be safe from Quantum Computers for a long time.
Post-quantum…
One nuance worth stating plainly: the Db2 ENCRYPT and HASH functions listed below are symmetric, where a quantum computer (Grover) only halves the effective key strength – so moving to 256-bit gives you 128-bit of real protection and is exactly the right fix here. The factoring threat from Shor described above instead breaks asymmetric public-key crypto such as RSA and ECC, where larger keys do not help and post-quantum algorithms are required – that protects your TLS handshakes and certificates and is a separate piece of work.
How long is long?
The so-called Q-Day is expected in 2030 when anyone with a Quantum Computer can then read all the data you are *now* storing…
Stuff to do, then?
Yes indeed, any use of the following is no longer really secure for „tomorrow“ anymore and steps must be taken to raise the level of encryption. If any of the following scalar functions are being used in your shops it is time to start a project group „Quantum Safe Encryption“:
If you find any of these in your SQL workload you must get the application teams to replace them with:
DECRYPT_DATAKEY_type With the new full list of types.
ENCRYPT_DATAKEY(xxxx,
key-label-name,
AES256R or AES256D) All 256 Bits. Using AES256D enables EQUAL
usage as the same value will encrypt to the
same encrypted value but is not as secure
as AES256R.
HASH(xxxx,2) Secure Hash Algorithm 2 with 256 Bits
HASH_SHA256(xxxx) Secure Hash Algorithm 2 with 256 Bits
The highest Bits have it!
As you can see, anything less than 256-bit keys is nowadays not deemed „Quantum Safe“ which means someone will be able to easily decode your emails, data, transactions, logs etc. when that Q-Day comes – and it may come sooner than you would think or like!
Not easy to do!
So now, if you find any of these functions you must decrypt the data and then re-encrypt the data with one of the 256-bit keys so you are „safe for a while“ – Of course how long we will be „Quantum Safe“ with 256-bit keys is a very open question indeed!
This month I wish to stroll along the dangerous and difficult road called Db2 Release Migration. We all love it as it means a new release with all new go-faster features and whizzbang things to play with but we also all hate it as it means we have things to do before we can actually migrate and get to this new promised land.
With Db2 for z/OS vNext coming up soon we are now back in the Driving Seat!
The list of deprecated items is pretty long and now about 80% of them have been finally deleted. If you have, or use, any of them you will *not* be joining the rest of us on the other side of the valley where the grass is definitely much greener!
The list of killed off items is:
Simple tablespaces
Segmented tablespaces
Classic partitioned tablespaces (With or without Index based partitioning)
Basic (Six byte) format
BRF (Basic Row Format so not yet at RRF – Reordered Row Format)
Hash Access
Synonyms
VTAM/SNA
Haakon Roberts shared this data at the IDUG EMEA 2024 in Valencia and it was the first time we had all seen an inkling of what would come. The real surprise, at least for me, was the requirement to remove VTAM/SNA support and move over to TCP/IP. The rest we had all known about for years but killing off VTAM/SNA was brand new!
CATMAINT to the Rescue?
Nope! None of these features will be automagically fixed by running a CATMAINT. It is all manual work and up to us, the DBAs fighting at the front, to fix „When we have some spare time…“
An ALTER a Day keeps the Dr at Bay
The basic „cure“ for most of these problem children is simply an ALTER and a REORG, but it *never* is that easy, is it? To fix segmented or simple tablespaces with just one table within them it is an ALTER to MAXPARTITIONS 1 which will kick the tablespace into the world of UTS PBG. Follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.
More than one?
If you have multi-table tablespaces then you must be at Db2 12 FL508 or higher and create a new set of tablespaces that match *exactly* the current ones for BUFFERPOOL, CCSID and LOGGED attributes. Then you use the ALTER … MOVE TABLE syntax for each table. An actioning REORG followed by a full RUNSTATS on each new tablespace afterwards with SHRLEVEL REFERENCE is then required to get the RTS statistics inline. Now do a REBIND of all invalidated packages and you are done.
Time for the Classics?
If you have any really, really old index-based partitioning tables, you must do two ALTERs within one commit scope. The first flipping the Partitioning Index to NOT CLUSTER and then back to CLUSTER. Now you are at table-based partitioning so read on!
Table-based partitioning
At this point it is an ALTER to SEGSIZE 64 which will kick these babies into the fun world of UTS PBR.
For both Classic cases, follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.
Six Byte RBA/LRSN, anyone?
If any of your tablespaces or indexspaces have not got 10 Byte RBA/LRSN then you must REORG them to action this. One little problem here is if the space is an XML space (Type = ‚P‘) then you must first check its base table’s tablespace to see if that is a UTS space. If so then all is good, otherwise you have a non-versioning XML space which will require this four-step fix:
1. DSN1COPY to generate image copy for XML data
2. Drop XML column from base table
3. Recreate the XML column in the base table
4. LOAD REPLACE to load the XML data from the image copy generated by step 1.
Invalidated Packages?
Yup! All of these ALTERs and the actioning REORGs with their inline/afterwards RUNSTATS will happily invalidate any and all packages that refer to the objects… This is a major pain as your access paths can then very easily go south! This is time for our tool BindImpactExpert (BIX) to ride to the rescue. As long as you are running with EXPLAIN(YES) – and I sincerely hope you are!!! – BIX can be used to highlight any and all changed access paths enabling you to be proactive with corrective measures.
Definitely evil DEFINE NO
DEFINE NO objects are great as SQL can SELECT from them really fast! They take up next to no disk space and require no REORG or COPY processing as there is no VSAM dataset. The problem with these little devils is that REORG is actually *not* permitted on them! One exception to this rule is if the TS/TP was created with BRF, as then you can REORG with the option ROWFORMAT RRF and this REORG will just flip a bit or two in the catalog/directory and nothing else.
And???
That means that all the ALTERs you might have done are „hanging in the wind“. IBM state that an INSERT/LOAD will materialize the VSAM cluster(s) but who wants to do that? The only way forward is to extract the DDL that created them, DROP them and then reCREATE them all as DEFINE NO again. As when recreated they will be valid for vNext, of course then a REBIND of all invalidated packages is also required and you are done.
Devil in the Details
For BRF partitions the fixing REORG will fail if any table has a validation procedure (VALPROC table column) or edit procedure (EDPROC tabel column) defined. If this is the case then the procedures must first be dropped before the REORG and afterwards added back.
Did they make a HASH of it?
HASH access came in with a big fanfare, we finally had an access path like IMS HDAM, but it died a death very quickly and the fix here is ALTER with DROP ORGANIZATION. Naturally, this drops the hash index and so you must then create a new index for SQL use and – guess what else you must do? Of course, a REORG with inline statistics and a REBIND of all invalidated packages and you are done.
Yes, over ten years ago! The actual fix for every synonym is straightforward:
SET CURRENT SQLID = 'Synonym schema' ;
DROP SYNONYM 'Synonym name' ;
COMMIT ;
CREATE ALIAS 'Synonym schema'.'Synonym name'
FOR 'Table creator'.'Table name' ;
COMMIT ;
The problems here are that the statement SET CURRENT SQLID requires SYSADM Authorization, so this is not something I would farm out to the next student doing work experience!!! Plus, guess what you have to do afterwards??? A REBIND of all invalidated packages and possibly a chain of dependent invalidated objects as well and you are done.
TCP/IP is the Future!
It came as a surprise but there are indeed shops out there who are still using VTAM/SNA for inter-Db2 communication. The problem here is that when it was released in 1974, we all trusted each other! If you were connected to DB2A and just hopped over to DB2B it happily trusted you as you „were already on a DB2A so you must be good guy!“ Sadly, those halcyon days are long gone. These days we have „Zero trust“ as the norm – a real shame but it is what it is!
Reason for Change?
TCP/IP has enhanced security (AT-TLS and JSON Web Tokens), it can use 64-bit communication buffers and is zIIP eligible. You will save CPU and be much more secure, so going to this is really no question – However, this is a project all on its own – not something for a Friday afternoon at 4 o’clock!
Big Switch?
To switch off VTAM/SNA you should do several things. First clear out any and all old entries in the CDB tables LULIST, LUMODES, MODESELECT and all but the empty LUNAME entry from LUNAMES. Then you should do a BSDS update with DSNJU003 setting the IPNAME for that subsystem to be the real host name. Just doing that switches off VTAM/SNA for that member. If doing all this then also go to SECPORT usage at the same time as then your Db2s are truly secure! Your auditors will love you.
Yes, even all the sort workspaces must be cleaned-up and moved to PBG spaces. You really need a lot of 32k, a few 4k, and you need to know exactly how many FOR SORT and how many FOR DGTT (The new syntax in Db2 13 FL508) you require and need. No more guessing at which workload is going into which work tablespace.
FOR SORT specifies that the table space is used for processing other than declared global temporary table (DGTT) work, such as sort, joins, created global temporary tables, query parallelism, trigger transition tables, and so forth.
FOR DGTT specifies that the table space is used for DGTT work and processes that use internal temporary tables, such as scrollable cursors and INSTEAD OF triggers.
Remember that here no ALTER helps – you must DROP and CREATE the work spaces again… But hey! At least no REORG, RUNSTATS and REBINDs are required!
<PHEW> That’s a Ton of Stuff to do…
Wouldn’t it be cool if there was a nice small bit of freeware out there that listed out all the stuff you have to do to actually get ready for Db2 vNext! In fact, you cannot even get to Db2 13 FL511 as that will stop you with any of the above-mentioned items!
What Luck – SEG to the Rescue!
Yes indeed, we have a bit of freeware called MigrationReadiness HealthCheck for Db2 z/OS (MRHC) that runs through your Db2 subsystems and shows you all the KPIs you have as well as highlighting all the Migration Blockers, as I call them. There is also a pay-ware version that then actually creates all of the ALTERs, REORGS, RUNSTATS and REBINDs for you as well.
How does it look?
It looks cool, of course! Here are a few example screen grabs of how it looks in my little Db2 13 data-sharing test system and what it offers:
Db2 MigrationReadiness HealthCheck V2.1 for SD1 V13R1M509
started at 2026-05-28-11.08.24
Lines with *** are deprecated features
Lines with MMM are migration blockers
Lines with XXX are definition errors
Number of DATABASES : 225
# of empty DATABASES : 48
# of implicit DATABASES : 110
# of empty implicit DBs : 46
Number of TABLESPACES : 4995
of which HASH organized : 0
of which PARTITION CLASSIC : 0
# Partitions : 0
of which SEGMENTED : 27 MMM
of which SIMPLE : 3 MMM
of which LOB : 99
of which UTS PBG : 4833
# Partitions : 4834
of which UTS PBR (Absolute): 1
# Partitions : 6
of which UTS PBR (Relative): 10
# Partitions : 2215
of which XML : 22
Number of TSs as LARGE : 0
Number of empty tablespaces : 7
Number of multi-table TSs : 12 MMM
# of tables within these : 49
.
.
Number of table partitions : 7211
of which DEFINE NO : 2918
of which 6 byte RBA <11 NFM: 0
of which 6 byte RBA Basic : 0
of which ten byte RBA : 4293
Number of TP in BRF : 17 MMM
.
.
LULIST entries found : 0
LUMODES entries found : 1 MMM
LUNAMES entries found : 2 MMM
MODESELECT entries found : 1 MMM
.
.
Total number of REORGs 21
REORGing 176 Cylinders
also requiring 1409 REBINDs
Naturally, most of this is the Db2 Catalog and Directory. At the end it outputs a small list of KPIs with the Total number of REORGs required, the total numbers of Cylinders of space all the table and indexspaces take and how many REBINDs should then be done afterwards. This gives the experienced DBA a very good idea of how long and how potentially dangerous this whole thing could be!
Just the Facts, Ma’am!
Under DD card ALTERCAT in the pay-ware version are all the required actions, note that you must be at Db2 13 FL509 or above to get the CONVERTUTS REORG syntax:
Using our MigrationReadiness HealthCheck for Db2 z/OS freeware you can start to divvy up and plan all of the work that will be coming down the vNext road towards you. Start now and you still have over two years to get it all done and actioned. Start in two years and you will never make it – The choice is yours!
I love this User Group – Great people, great location at the IBM Lab in Toronto and , this year, a great lunch! (You had to be there last year to understand this…)
This year they went all out and we got a full „Canadian Database Day“ on the Monday, split into a Db2 track called „Db2 for z/OS, Everywhere, All at Once!“ – which covered, for Db2 z/OS, Bufferpools, Application dev, DDF Applications and Locking, Logging and Security. For Db2 LUW you got Semantic search, AI Systems and Amazon RDS. Then in the parallel IMS track everything about IMS as this old bird is still firmly nailed to its perch and rumors of its death have been greatly exaggerated!
And then?
Then we kicked off on Tuesday and Wednesday into the normal grid style with four parallel tracks. Now, as always, I must state that I did not go into *every* session so what I report here might just be a cut-and-paste from the grid and changes might well have happened that I did not know about! Further, I did not go to any IMS or LUW tracks even though I started my Mainframe career writing IMS accessing COBOL programs with IMS 1.3 – Yep – I am THAT old!
Unlike the IDUG all the above links *should* take you directly to the download file. If not please contact the CCDB2 group and not my good self.
The missing link?
As of the 26th May the following presentations are *not* at the link address, just the abstract and speaker details:
ZOS 02 and 03
ZMISC 01, 02, 08 and 09
I hope that these presenters get their slides off to the CCDB2 organizers as soon as possible.
Reviews
ZOS-01 Akiko gave us her usual excellent stuff all about what we have and where we are going. Interestingly, she mentioned that with Db2 13 FL509 we must run the REORGs for directory and catalog migration to UTS. This is *not* done by a CATMAINT. A nice tidbit was that the two new RTS columns NSYNCREADIO are not updated for Utilities which makes them very useful for utility decisions! She also reiterated that the CDDS functionality is now included in the base Db2 install and so we can all use this feature to get easy reachable copies of our compression dictionaries! Check out my blog all about using this facility called „2025-01 Compress and Replicate This!“ at this link www.segus.com
She then went into what she called „Part 2“ and mentioned that the new DDL Clauses FOR DGTT or FOR SORT got two APARs for problems: PH65632 and PH70190.
Also note here that FOR SORT should use more than one for the MAXPARTITIONS value to benefit from the above performance improvement fixes.
Looking a little bit into the future she listed a few Scalar functions which are no longer deemed „Quantum safe“ meaning that perhaps not today but certainly soon this level of encryption will be easily broken by a quantum computer, ENCRYPT_TDES or ENCRYPT, for example.
Then she mentioned that they are looking at changing the Lock Structure to get multiple versions which would make Datasharing even more scalable and resilient! Finally, she touched on a new possibility to access indexes using different column orders. No need for one index on COL1, COL2 & COL3 and another index on COL3, COL1 and COL2. This would be incredibly cool if they pull it off!
ZOS-02 Mark and Tori showing us all the fantastic things you can now do with System Profile Monitoring. If you have not looked into this, start now! Just remember that START PROFILE starts ‚em all and STOP PROFILE stops ‚em all! This might *not* be what you wanted or expected! It was also repeated that users *must* start using Client Info fields. This should be standard but some shops still have one technical user id for *all* their CICS transactions so it is *impossible* to filter correctly or usefully. The other bug bear that is always asked is „Why cannot I filter on User Id and Location?“ The answer is when it connects it knows just the location then a long time later it finally gets the User Id …Basically the code paths cannot handle this. If you are using cloud the Location address is normally very bad for filtering and so you must get Client Info added! A few interesting bits is that most occurrences of MAXDBATs being hit apparently are „ghost“ transactions that just connect and sometimes run an SQL against SYSIBM.SYSDUMMY1, then sit there doing nothing. Purely to see if Db2 for z/OS is there or not. Db2 for z/OS is always there, so if you can turn off these „ping“ SQLs it would be a very good idea! Last bit was that the number of active profiles is limited to 4096… Do not exceed this number!
ZMISC-03 Ulf showed a relatively simple way to completely, safely and with no risk, copy your entire production metadata down to a sandbox and still guarantee access paths as if from production. Lots of people forget silly things like RTS or number of CPs & BUFFERPOOL numbers and sizes. Check it out to see how it can help you pre-check your next set of Db2 for z/OS APARs, Db2 Version release or machine upgrade or even downgrade if consolidating.
ZOS-04 Richard showed me how old I was as my data strategy is very, very old indeed! More buzzwords than you can hit with a large stick! The brave new world of data everywhere all over and only replicated when it absolutely must be. Great stuff! Favorite statistic is „25% of all CPU MIPS is spent replicating data“. Discussion topic – Latency: 0 if virtualized to „it depends“. It depends on z/OS load, Network load and Target system (normally LUW) load.
ZOS-05 John doing his usual fantastic stuff this time taking aim at High Performance DBATs. One Golden Rule: Only for high volume simple transactions. The problem is to find them in all of your workload! They are a trade-off : More memory & More DBATs versus Lower CPU. Tracing is good with IFCIDs 411, 412 and the best is 365. The one disadvantage is that at least one of the packages they use MUST be bound with RELEASE(DEALLOCATE).
He then mentioned a small change in behavior that crept in with the NULLID packages in that they all got changed to RELEASE(DEALLOCATE) and he strongly advises everyone to REBIND them all with RELEASE(COMMIT) and, if you want High Performance DBATs, create a new COLLID where the packages are there bound with RELEASE(DEALLOCATE) and use System Monitoring to redirect to this new COLLID or change the JDBC path.
When you issue the -MODIFY DDF PKGREL(COMMIT) this is not an *instant* fix to switch these things off. It will take time for all accessing threads to eventually be reset. Remember the DBAT only gets recycled after 500 times in Db2 13, before it used to be after 200.
ZMISC-06 Michael walked us through a great review of things over the years that come and sometimes go! Things that people tend to neglect or forget. ZPARM DISABLE_EDMRTS for example is a very good one! RUNSTATS not updating the RTS in certain scenarios is another. He gave a quick review of all the new and snazzy commands we have now got including DISPLAY BLOCKERs of course. This is my personal favorite due to it being the *only* command that outputs the Timestamp when it was executed. Sounds minor but if all you have is command outputs and you have to work out who did what when and where then timestamps are great. The details and hints about use of RECOVER with BACKOUT was also very illuminating!
ZOS-07 Roy doing his usual great stuff (What else am I going to say? 😊 ) and wrestling with the darn mic as it did not want to work with the British guy! Nice questions about how long til doomsday etc etc. Just download and run our freeware tool: MigrationReadiness HealthCheck for Db2 z/OS at www.segus.com to find out, very quickly, exactly where you currently stand. The worst things are SYNONYMs, Work tablespaces and TCP/IP. I have customers up in the 8,000 synonyms and some with zero. I guess it all goes back to when you started down the road of Db2! With work files if you have 400 on 87 subsystems that could be a lot of work. Here the DB2 13 FL508 DDL enhancement FOR WORK / FOR DGTT will help as well but you must still DROP those spaces!
ZMISC-08 Mark and Anna with standing room only! Absolutely packed room where no-one even offered me a chair – The Audacity!!! The dynamic duo gave us all their data about the lifecycle of packages for static and dynamic SQL diving down into all the grubby details that we all love so much! The question of the day was „How does Python on the Mainframe get allocated to a Package, and which one?“ No one in the room knew the answer! The hope is directly to some package or possibly even using Type 4 going out and back in again on the network! I have noted this down as something to try and find out!
Update: Found out from Jørn Thyssen that it will use ODBC and then CAF or RRSAF with default PLAN DSNACLI and one of the seven packages within. The BINDs are all in <your Db2 HLQ>.SDSNSAMP(DSNTIJCL). Sadly I still have no idea which package but probably DSNCLIC1. Here is a great BLOG all about Python including, right at the end, a hint about APF Authorizing its load lib to get Python ZiiP enabled… Pretty neat! Python ibm_db on IBM z/OS UNIX (USS): a field ready install & troubleshooting guide
ZMISC-09 Kranthi showed us all the details and intricacies behind the „certificate“ problems we all have or will have! With the lifecycle of certificates dropping very quickly you must try and get automated systems to cope with the renewal. For a few PCs or laptops and phones it is ok to do it yourself but in a firm? Fun fact: How many certificates does your average lap-top have installed? Between 100 and 600 is normal(!)
They think it’s all over!
Then we had to leave to get our flights but I was very happy indeed to be there again and I already look forward to next year and being there once again!
The Food?
Chris, the food was excellent this time but I still remember that Beef Bacon horror at the IFL!!!
Many, many thanks to all the volunteers who put on the whole show and IBM for allowing the whole thing to be run from their Labs – and I personally thank all the IBM Employees who were asked nicely to „leave the Canteen at 12:30“ so a bunch of DBA crazies could sit down and have lunch!
Lest we forget!
For those who are now retiring and not there next year:
Thanks for all you have done and enjoy your curling!
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!
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:
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!
This month, I wish to talk all about RESTful APIs and why they are sooooo cool. At the end I will give you a github link to a German GUIDE DBA Colleague who wrote some fantastic stuff using REST services and SQL to trigger REORGs from Excel… I kid you not!!!
Give it a REST
These beasts arrived in Db2 11 and are basically „one shot wonders“ as a REST (Representational State Transfer) service has no „brain“ and cannot remember anything. In SQL terms, is one single static SQL statement. This is really called a RESTful API using HTTP GET, PUT, POST and DELETE. Fun factoid to start with: Db2 only supports the POST method and so is really REST and not RESTful! An example REST looks like:
POST https://<host>:<port>/account/update
This is a URI (Uniform Resource Identifier). Please remember that just because Db2 only accepts POST it does not mean you cannot run any SQL you like!
What is JSON?
JavaScript Object Notation (JSON) is what is used in all of the payloads. Here’s an example:
Data is represented as a series of name/value pairs. This is serialized and passed in with the URI or returned with a response.
I don’t GET it…
Well, actually, GET can be used for „some system related functions“:
https://<host>:<port>/services
But what can make RESTful real, is when you use z/OS Connect, as that is 100% RESTful but is out of scope for my newsletter!
Pretty dull, then?
Not actually! The beauty of REST services (giving them their proper name) is that they can execute any static SQL statement *including* stored procedure calls! Try doing that in DSNTEP2 or SPUFI!!! For this reason alone, they are worth looking at!
Special Rules?
Of course, nothing is „normal“ in the Db2 for z/OS world, is it?
In Db2 11 we got BIND SERVICE and FREE SERVICE as they are not normal „packages“ as we know and love them but special. REBIND PACKAGE worked but EXPLAIN(ONLY) was not possible, the only way to see an access path was to BIND/REBIND completely!
In Db2 12 we got START, STOP and DISPLAY for the RESTSVC to review all of our REST services.
The SQL gets squirreled away in a Db2 pseudo-catalog table called SYSIBM.DSNSERVICE. This data is also the „link“ to a real package which, after being bound, can *only* be executed as a service (This is also a security feature.)
With APAR PH34544 we got the RESTSERVICEDEFAULT option added to REBIND PACKAGE which was nice to be able to change the default version from V1.
In Db2 13:
With APAR PH63990 the EXPLAIN(ONLY) syntax was allowed for REST services allowing access path extraction with no outages.
With APAR PH54129 the FREE PACKAGE was extended to free inactive REST services. Before this, you had to issue a FREE SERVICE, which freed everything – not just the phased out inactive services.
Any authorized user can then discover and invoke these services using a REST HTTP client. They support a buffer size of up to 2GB for the complete request and reply content as well.
Enable them first!
Make sure you are up to date on IBM Service (use keyword RESTful on your search) and make sure that APARs PI70652, PI98649 are applied. Even better for Db2 13 is PH68057 (UO06137) and all its prereqs to give you a good level. Use the DSNTIJRS job in your SDSNSAMP library to create the aforementioned pseudo-catalog table and its index. Then run the DSNTIJR2 sample job to enable Db2 REST service versioning support, only if you want versioning, of course!
REST while you work!
Versioning enables you to „swap out“ a REST service while the old one is still running. Before this, you had an outage as you must first FREE the service and then BIND the new one. Not good for 24×7 shops! It also enables full versioning, of course, so that you can decide which version for which application etc. but I would never walk down that road personally… Too much like micro-management for me. If you need a different SQL or parameter etc. then just create a new REST service!
Authorized to REST?
Naturally, it all must be authorized! You can do this with your local RACF, TOPSECRET or ACF2 system. The defaults supplied for RACF are adding a REST profile to the DSNR resource class. This is purely to allow a user to access Db2 REST services and not the APIs themselves. That is done with either:
HTTP basic authentication using User ID password, RACF PassTicket etc. all in the HTTP Header in clear text or Base64. Db2 for z/OS then uses the System Authorization Facility (SAF) to authenticate these. or
Db2 REST Client certificate authentication. This is used if: 1. It is a secure connection to Db2 using HTTPS and AT-TLS. 2. The client certificate is registered with RACF, TOPSECRET or ACF2.
The second approach is the recommended one due to the obvious security concerns of HTTP Header usage! This then requires SECPORT to be set up of course!
Do you trust me?
Here you can, and probably should, start using Trusted Contexts as REST can exploit these and REST services do lend themselves to ROLEs.
Here’s one I prepared earlier…
To save me typing, here’s a direct link to the IBM Docu all about „Creating a Db2 REST service“:
Bad pun I know… CORS support was rolled out in Db2 13 as well! Up until then this failed, due to the fact that Cross-Origin Resource Sharing (CORS) was not implemented. To get it up and running, you will need APAR PH59837: NEW FUNCTION (Great title huh?) PTF UI96535 applied. You will then need another RACF change, as it introduces a new Resource Class: DSNRAUTH with ACTIVE and RACLISTED. (If you intend to use generic then you must also enable GENERIC). It uses the user id of all your xxxxDDF address spaces for this as it is subsystem-wide and not user-specific. Then you must create one or more RACF resource profiles for the Db2 REST service CORS and Permit the DDF User Id READ access to these just created resource profiles. A quick refresh:
SETROPTS RACLIST(DSNRAUTH) REFRESH
and you are done!
If you wish to trace all of this CORS stuff then switch on IFCID 416 Audit Class 12.
Am I in Trouble?
Using these beasts, you will end up with a new list of error codes and messages…
The HTTP world has a few that you need to know:
1xx "Informational" Communicates transfer protocol-level info
2xx "Success" Hooray!
3xx "Redirection" Oh Oh - The client must do something else in order
to complete the request
400 "Bad request" is normally badly formatted input
401 "Unauthorized" The user authentication failed
403 "Forbidden" The user might not have the required permission…
4xx "Client Error" any other 400'er numbers
5xx "Internal Server Error" comes normally with an SQLCODE so you
can work out what has gone wrong!
Does my PROFILE look good?
These work brilliantly with system profile tables! Product identifier for example is HTP01010 for non-secure HTTP which you should never use! Better is HTS01010 for HTTPS secure connections.
Look-a-here
DISPLAY RESTSVC by default shows you all the REST services that exist in your Db2 subsystem:
DSNL601I -SD10 DISPLAY RESTSVC REPORT FOLLOWS-
DSNL610I -SD10
---- COLLECTION=MDB2VNEX_TEST
SERVICE VERSION STATUS
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_ANY V1 STARTED*
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_NONE V1 STARTED*
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_ONLY V1 STARTED*
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_VARI V1 STARTED*
ABLE
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_ANY V1 STARTED*
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_NONE V1 STARTED*
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_ONLY V1 STARTED*
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_VARIA V1 STARTED*
BLE
SAX_PTF_LEVEL V1 STARTED*
SAX_USECASES_BY_LOCALE_PRODUCT V1 STARTED*
DSNL610I -SD10
---- COLLECTION=MDU_COLL
SERVICE VERSION STATUS
MDUMMY V1 STARTED*
UPD_SAL_3 V1 STARTED*
DSN9022I -SD10 DSNLJDSS 'DISPLAY RESTSVC' NORMAL COMPLETION
We have a few on my test system! A little heads up about this command, is that if you are running with data sharing then it is executed on all the remote sites under the SYSOPR authorization id! This is also shown in IFCID 90 „Command Trace“ as being correlation ID „016.TLPKN5F“ – This requires that a SYSOPR Id is defined at the remote sites.
What can You do with it all?
As I mentioned right at the very start of this blog, at the last German Db2 Guide, a DBA did a great presentation all about bundling all this knowledge, including a bunch of stored procedures to allow, for example, an Excel table entry to trigger a REORG on the host. All great stuff and available from github with a ton of excellent documentation. Something for the weekend I would say!
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…
Allied threads
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:
Running a batch program on the mainframe
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:
A hop to another Location
An open and HELD cursor, a held LOB locator or a package bound with KEEPDYNAMIC(YES)
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:
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.
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.
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!
Hi all! This month I wish to go though a few of the interesting, annoying and odd things that I bumped into last year. Some were new for me and some were just interesting for me!
COMPRESS THIS!
One of my customers is now starting down the road of compressing their very, very large NPSI’s as the RECOVER utility is actually way faster than a REBUILD. Nothing new here, is there? But wait! What if they are using FLASH COPY?
It gets very, very ugly very, very quickly is what happens!
Why?
Remember how FLASH COPY works? It is sooooo blindingly fast because it does all the actual copy stuff „in the DS8000, or equivalent, box“ and *not* on your mainframe. This is really cool as you just shoot off a FLASH COPY and, as long as a few really basic rules are not broken, the copy is finished the moment it starts!
So, what about Indexes?
Firstly, if you want to do a FLASH COPY of a COMPRESS YES index you *cannot* also do a sequential copy. Further, remember that a FLASH COPY is a VSAM dataset and you cannot do a COPYTOCOPY of one of these either, meaning you have just one single VSAM dataset as a copy – This is, at least for me, a single point of failure and not good. But it gets much worse!
Really, how so?
Please now remember how index compression works… It is done purely „in memory“ in the bufferpool. This means that when you have an insert, delete, or key update in memory and it has not yet been externalized to disk that when you now do a FLASH COPY you are copying garbage… This is, to coin a phrase, „not good“ whereas a normal COPY index goes through the bufferpool and so a sequential copy is naturally ok!
Bottom Line
If using COMPRESS YES indexes in no way use FLASH COPY. Perhaps, at least for storage, dataset compression of the sequential copy datasets might save space…but then that defeats the purpose of COMPRESS YES on the index purely in the Db2 world which is primarily reducing I/O and secondarily reducing index page splits.
What about SYSTEM LEVEL BACKUPS?
Guess what? These are FLASH COPY as well! If you use SLBs and you have COMPRESS YES indexes you better be careful what you „replay“ and make sure to always REBUILD, or at least CHECK, all indexes after a RECOVER has been run!
Docu?
All of the above is documented of course but it is like Douglas Adams wrote „in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door in saying ‚Beware of the Shark.'“ – [The Shark is my idea geddit? Originally it was Leopard of course!]
Death By RUNSTATS
It is surprisingly easy to kill yourself with a simple RUNSTATS these days.
How?
Let’s say you have PROFILE on and you are using SYSSTATFEEDBACK for all your tables. I know you are as it is all on by default and who changes defaults?
And?
Now a third-party vendor sells you some software with ridiculously long VARCHAR fields containing possible NAMEs and ADDRESSes. In this case VARCHAR(2000) is being used. The SQL in question is using dynamic SQL with literals, not parameter markers, in the WHERE clause against these columns and SYSSTATFEEDBACK „sees“ the requirement for column groups as these columns have, naturally, no index and a column group is a „poor man’s“ index for frequencies and cardinalities, right?
So?
You end up with 23 column groups for a five partition table with over 120 million rows.
But what has that got to do with the Price of Beef?
So, dear, friends, what does our poor old RUNSTATS utility do now? It must farm out these column groups to DFSORT — and can you guess how it works out the allocation size??? You guessed it: 2000 + 8 for the maximum record size then multiplied by 23, for the number of column groups, then the result multiplied by the number of rows: 120,000,000. Do the math and you end up with a DFSORT storage requirement of over 5 PB (Yep that’s PETA bytes!), then the Storage Admin freaked out!
Easy fix: Delete all COLGROUP definitions for this table not backed by a real index. RUNSTATS DELETE PROFILE is a great help here! Then switch off SYSSTATFEEDBACK for this table and control *all* other column groups because as I like to say „where there is one, there are probably more“.
Bugblatter Beast of Traal
It is possible to wrap your head in a towel so the beast does not see you, but it is sometimes better to actually look for these things before they get really really bad. I hate to think how long this RUNSTATS was just „growing and growing and growing“ with no-one noticing that it was quite simply insane!
SYSSTATFEEDBACK is good but not that good!
Remember, it is doing the best it can based on the SQL usage and what the Db2 Optimizer thinks is missing or might improve performance. In this case, the excessive number of column groups and the excessive size of the groups was actually way more of a problem than a help!
Parameter Markers…
It would also have been fine if the developers had coded good SQL with parameter markers so that SYSSTATFEEDBACK would not have started the whole problem in the first place! As a secondary bonus, moving to parameter markers stops any SQL Injection attack vectors as you could do a lot of damage with a VARCHAR(2000) text field!!! Just using „A‘ OR ‚A‘ = ‚A“ would be nice and evil, wouldn’t it!!! Returns every single row because in the code it is stringed into a delimited string. So, if the customer gives A as input it builds this string:
SELECT all my columns FROM mytable WHERE ADDRESS = 'A' ;
Now add my injection code:
SELECT all my columns FROM mytable WHERE ADDRESS = 'A' OR 'A' = 'A' ;
What does that OR do? Yep – It is always true so every row is always returned…Not what you would want with 120 million rows…
Stale Stats?
A last bit about SYSSTATFEEDBACK is that it recommends STALE quite a lot and some of these bogus entries are, in fact, just created by a STALE recommendation so one other way of clearing them all out is to run a little SQL like the following:
-- THIS SQL WILL CORRECT THE PROBLEM OF BOGUS COLUMN COLGROUP CAUSING -- EXCESSIVE SORT ALLOCATION AND FAILING RUNSTATS. -- -- WHAT IT DOES IS: -- -- 1) STOP SYSSTATFEEDBACK GENERATION FOR A GIVEN TABLE -- 2) CLEAN UP SYSCOLDIST AND SYSCOLDISTSTATS "F" ENTRIES WHICH -- ARE LISTED IN SYSSTATFEEDBACK WITH A "F" AND "STALE" ENTRY -- 3) DELETE ALL "F" AND "STALE" ENTRIES FROM SYSSTATFEEDBACK -- -- TWO VARIABLES WILL BE CREATED AND USE THE DEFAULT FOR NAME AND -- CREATOR: -- CREATE VARIABLE TAB_NAME VARCHAR(128) DEFAULT 'MY_BAD_TABLE' ; CREATE VARIABLE TAB_CREATOR VARCHAR(128) DEFAULT 'MY_BAD_CREATOR' ; -- -- STOP SYSSTATFEEDBACK PROCESSING FOR THIS TABLE -- UPDATE SYSIBM.SYSTABLES SET STATS_FEEDBACK = 'N' WHERE CREATOR = TAB_CREATOR AND NAME = TAB_NAME AND TYPE = 'T' ; COMMIT ; -- -- DELETE ANY STALE COLDIST FREQ VALS FOR THIS TABLE -- DELETE FROM SYSIBM.SYSCOLDIST A WHERE A.TBOWNER = TAB_CREATOR AND A.TBNAME = TAB_NAME AND A.TYPE = 'F' AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B WHERE B.TBCREATOR = TAB_CREATOR AND B.TBNAME = TAB_NAME AND B.TYPE = 'F' AND B.REASON = 'STALE' AND B.TBCREATOR = A.TBOWNER AND B.TBNAME = A.TBNAME AND B.COLNAME = A.NAME) ; COMMIT ; -- -- DELETE ANY STALE COLDISTSTATS FREQ VALS FOR THIS TABLE -- DELETE FROM SYSIBM.SYSCOLDISTSTATS A WHERE A.TBOWNER = TAB_CREATOR AND A.TBNAME = TAB_NAME AND A.TYPE = 'F' AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B WHERE B.TBCREATOR = TAB_CREATOR AND B.TBNAME = TAB_NAME AND B.TYPE = 'F' AND B.REASON = 'STALE' AND B.TBCREATOR = A.TBOWNER AND B.TBNAME = A.TBNAME AND B.COLNAME = A.NAME) ; COMMIT ; -- -- DELETE ANY STALE SYSSTATFEEDBACK FREQ VALS FOR THIS TABLE -- DELETE FROM SYSIBM.SYSSTATFEEDBACK WHERE TBCREATOR = TAB_CREATOR AND TBNAME = TAB_NAME AND TYPE = 'F' AND REASON = 'STALE' ; COMMIT ; -- -- DROP THE CREATED VARS FOR NEXT RUN -- DROP VARIABLE TAB_NAME ; DROP VARIABLE TAB_CREATOR ; COMMIT ;
Take care out there!
Caveat Emptor!
Remember to always review DELETEs like this *before* you do them in production. Blindly deleting stuff is sometimes dangerous and hazardous to your career path!
I hope you found this info interesting on a cold and dark January day, at least here in Germany!
This year’s end-of-year goody giveaway is a revamped and updated version of our 2020 Happy Holiday Present. This time focused on the migration blockers that will stop you getting to vNext!
What are the Problems?
The number one problem with Db2 system migrations, is the deprecated features that are still firmly nailed to their perches but are 100% dead. They will not cause a problem today, or tomorrow, but at some point they will start to smell … and I mean smell really bad!
Tell me More!
Here’s a list of all the deprecated (and semi-deprecated) items that should be checked and verified at your site:
Use of SYNONYMS
Use of HASH objects
Use of segmented spaces
Use of classic partitioned objects (not using table-based partitioning)
Use of simple spaces
Use of six byte RBA
Use of BRF
Use of LARGE objects (This is semi-deprecated)
IBM, well actually Haakon Roberts at the IDUG EMEA in 2024, announced a list of deprecated features or functionality that will block migration to Db2 vNext:
This list includes nearly all of the first list and added SNA/VTAM usage.
Anything Else?
Well, yes! You could also check how many empty implicit databases you have and how many empty tablespaces you have while you are checking your subsystem out. While you are scanning, it could also be cool to list out all the Db2 subsystem KPIs, and 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 new 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 plus the five deprecated ZPARMs that you should also verify these days!
What does it cost?
Nothing – It is our licensed freeware for 2025/2026 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 Readiness HealthCheck V1.0 for SD1 V13R1M507 started at 2025-08-27-10.05.32 Lines with *** are deprecated features Lines with MMM are migration blockers Lines with XXX are definition errors
Number of DATABASES : 122 # of empty DATABASES : 17 # of implicit DATABASES : 65 # of empty implicit DATABASES: 15
Number of TABLESPACES : 2216 of which HASH organized : 0 of which PARTITIONED CLASSIC : 0 # Partitions : 0 of which SEGMENTED : 19 MMM of which SIMPLE : 3 MMM of which LOB : 63 of which UTS PBG : 2115 # Partitions : 2115 of which UTS PBR (Absolute) : 0 # Partitions : 0 of which UTS PBR (Relative) : 4 # Partitions : 24 of which XML : 12
Number of tablespaces as LARGE : 0 Number of empty tablespaces : 6 Number of multi-table TSs : 12 # of tables within these : 49 Number of incomplete TS : 7 XXX Number of INSERT ALG 0 TS : 2206 Number of INSERT ALG 1 TS : 10 Number of INSERT ALG 2 TS : 0
Number of tables : 4520 of which ACCELERATOR ONLY : 0 of which ALIASes : 2164 of which ARCHIVEs : 0 of which AUXs : 63 of which CLONEs : 0 of which GTTs : 100 of which HISTORYs : 1 of which MQTs : 1 of which TABLEs : 2170 of which VIEWs : 9 of which XMLs : 12 Number of tables with Audit : 1 Number of tables with Data Cap : 2 Number of tables incomplete : 1 XXX Number of tables with control : 1
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 : 34 of which deprecated : 27 ***
Number of SYNONYMs : 0
Number of UNICODE V11 Columns : 0
Number of PROCEDURES : 116 of which SQL EXTERNAL : 0 of which EXTERNAL : 109 of which NATIVE SQL : 7
Number of FUNCTIONS : 87 of which EXTERNAL TABLE : 39 of which EXTERNAL SCALAR : 42 of which SOURCED AGGREGATE : 0 of which SOURCED SCALAR : 0 of which SQL TABLE : 0 of which SQL SCALAR : 2 of which SYSTEM-GENERATED : 4
Number of Indexes : 2594 of which HASH : 0 of which type 2 : 2594 # of partitioned IXs : 0 # Partitions : 0 of which DPSI : 0 # Partitions : 0 of which PI : 0 # Partitions : 0 Number of indexes COPY YES : 7 Number of indexes COMPRESS YES : 0
Number of table partitions : 2236 of which DEFINE NO : 1024 of which six byte RBA <11 NFM: 0 of which six byte RBA Basic : 0 of which ten byte RBA : 1212 Number of TP in BRF : 17 MMM Number of TP with COMPRESS Y : 43 Number of TP with COMPRESS F : 0 Number of TP with COMPRESS H : 0 Number of TP with TRACKMOD YES : 2234
Number of index partitions : 2594 of which DEFINE NO : 1324 of which six byte RBA <11 NFM: 0 of which six byte RBA Basic : 0 of which ten byte RBA : 1270
Number of STOGROUPS : 2 Number of non-SMS VOLUMES : 0
Number of PLANs : 39 of which DBRMs direct : 0 # of SQL statements : 0
Number of PACKAGES (total) : 2697 of which VALID = A : 12 of which VALID = H : 0 of which VALID = N : 6 of which VALID = Y : 2679 of which VALID = S : 0 of which OPERATIVE = N : 0 of which OPERATIVE = Y : 2697 of which OPERATIVE = R : 0
Old RELBOUND executed packages : 0
Number of PACKAGES (distinct) : 482
Number of Original PACKAGES : 278 Number of Previous PACKAGES : 278 Number of Phased-out PACKAGES : 271 Total number of PACKCOPY : 827 of which VALID = A : 30 of which VALID = H : 0 of which VALID = N : 0 of which VALID = Y : 797 of which VALID = S : 0 of which OPERATIVE = N : 0 of which OPERATIVE = Y : 827 of which OPERATIVE = R : 0
Number of SQL statements : 109143
LULIST entries found : 1 MMM LUMODES entries found : 1 MMM LUNAMES entries found : 7 MMM MODESELECT entries found : 1 MMM
ZPARM CHECK_FASTREPLICATION set to REQUIRED is at correct value REQUIRED and Ok. ZPARM CMTSTAT set to INACTIVE is at correct value INACTIVE and Ok. ZPARM DISALLOW_SEL_INTO_UNION set to YES is at correct value YES and Ok. ZPARM MATERIALIZE_NODET_SQLTUDF set to NO is not at correct value YES. *** ZPARM PREVENT_NEW_IXCTRL_PART set to YES is at correct value YES and Ok.
DDF command prefix -SD10 the IPNAME is not set to "-NONE" and Ok.
Db2 Migration Readiness HealthCheck V1.0 for SD1 V13R1M507 ended at 2025-08-27-10.05.34
Migration to vNext is not possible
Db2 Migration Readiness HealthCheck ended with RC: 4
Note that any MMM will be flagged as Return Code 4 with the message that „Migration to vNext is not possible“
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…
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 – 2016-01 Simply Synonyms in DB2 z/OS – (again) just remember to watch out for the GRANTs afterwards.
That’s a Huge Amount of Work!
Well, there is also a licenced version that creates all the ALTERs, REORGs, RUNSTATS and REBINDs for you – Costs a bit of money, but makes the entire project much easier to handle!
How many Blockers 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!
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, 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!
„As a first-time attendee, I can’t say too much because I’ve only been working with mainframes for about five months. But what I saw, is that people are really trying to bring mainframes onto modernization tracks
— for example, using VS Code, SQL tuning tools, cloud technologies, AI, DB2 management tools, and dashboards.
There was a lot of talk about AI — it’s like if you say “AI,” ten more people immediately become interested in your workshop.
I also saw some really good things that I’d love to have on the mainframe — like Zowe, a debugger in VS Code with a tree representation of the source code, and dashboards.
I think young people often skip the mainframe because they believe it takes a lot of time to learn how to work in that environment, and that switching companies or technologies later would be difficult.
I used to think the mainframe was like a big old giant that needed to think ten times before making a move and wouldn’t take a step toward becoming more “modern” — but now I see it can actually become a trend for young people who want to be part of the mainframe world.“
„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 28th 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
Um unsere Webseite für Sie optimal zu gestalten und fortlaufend verbessern zu können, verwenden wir Cookies. Lehnen Sie Cookies ab, stehen einige Funktionen der Website nicht zur Verfügung. Weitere Informationen hierzu erhalten Sie in unserer Datenschutzerklärung.
