Author: Roy Boxwell

Posted on

2024-01 Happy New SQLCODE!

Aren’t I supposed to be wishing Happy New Year there? Not this month! One of my readers asked me a question about SQLCODEs and it opened up a quite fascinating can of worms!

What is the SQLCODE?

Anyone reading this blog should already know exactly what the SQLCODE is, however, for those of you out there using Google in 2045, the definition is, at least in COBOL:

01 SQLCA.
   05 SQLCAID     PIC X(8).
   05 SQLCABC     PIC S9(9) COMP-5.
   05 SQLCODE     PIC S9(9) COMP-5.
   05 SQLERRM.
      49 SQLERRML PIC S9(4) COMP-5.
      49 SQLERRMC PIC X(70).
   05 SQLERRP     PIC X(8).
   05 SQLERRD     OCCURS 6 TIMES
                  PIC S9(9) COMP-5.
   05 SQLWARN.
      10 SQLWARN0 PIC X.
      10 SQLWARN1 PIC X.
      10 SQLWARN2 PIC X.
      10 SQLWARN3 PIC X.
      10 SQLWARN4 PIC X.
      10 SQLWARN5 PIC X.
      10 SQLWARN6 PIC X.
      10 SQLWARN7 PIC X.
   05 SQLEXT.
      10 SQLWARN8 PIC X.
      10 SQLWARN9 PIC X.
      10 SQLWARNA PIC X.
      10 SQLSTATE PIC X(5).

The third field within the SQLCA is the SQLCODE, which is a four-byte signed integer and it is filled after every SQL call that a program/transaction makes.

All Clear So Far?

So far so good! In the documentation from IBM is this paragraph about how to handle and process SQLCODEs:

SQLCODE

Db2 returns the following codes in SQLCODE:

• If SQLCODE = 0, execution was successful.

• If SQLCODE > 0, execution was successful with a warning.

• If SQLCODE < 0, execution was not successful.

SQLCODE 100 indicates that no data was found.

The meaning of SQLCODEs, other than 0 and 100, varies with the particular product implementing SQL.

Db2 Application Programming and SQL Guide

So, every programmer I have ever talked to checks if the SQLCODE is 0 – Green! Everything is fine, if the SQLCODE is negative – Bad message and ROLLBACK, if the SQLCODE is +100 – End of cursor or not found by direct select/update/delete – normally 100% Ok, everything else issues a warning and is naturally very dependent on the application and business logic!

What’s Wrong With This Picture?

Well, sometimes zero is not really zero… I kid you not, dear readers! The sharp-eyed amongst you, will have noticed the last bytes of the SQLCA contain the SQLSTATE as five characters. Going back to the documentation:

An advantage to using the SQLCODE field is that it can provide more specific information than the SQLSTATE. Many of the SQLCODEs have associated tokens in the SQLCA that indicate, for example, which object incurred an SQL error. However, an SQL standard application uses only SQLSTATE.

So, it still seems fine, but then it turns out that SQLCODE 0 can have non-all zero SQLSTATEs!

New in the Documentation

At least for a few people this is a bit of a shock. From the SQLCODE 000 documentation:

SQLSTATE

  • 00000 for unqualified successful execution.
  • 01003, 01004, 01503, 01504, 01505, 01506, 01507, 01517, or 01524 for successful execution with warning.

Say What?

Yep, this is simply stating that you have got an SQLCODE 0 but up to nine different SQLSTATEs are possible… This is not good! Most error handling is pretty bad, but now having to theoretically add SQLSTATE into the mix makes it even worse!

What are the Bad Guys Then?

01003 Null values were eliminated from the argument of an aggregate function.

01004 The value of a string was truncated when assigned to another string data type with a shorter length.

01503 The number of result columns is larger than the number of variables provided.

01504 The UPDATE or DELETE statement does not include a WHERE clause.

01505 The statement was not executed because it is unacceptable in this environment.

01506 An adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation.

01507 One or more non-zero digits were eliminated from the fractional part of a number used as the operand of a multiply or divide operation.

01517 A character that could not be converted was replaced with a substitute character.

01524 The result of an aggregate function does not include the null values that were caused by evaluating the arithmetic expression implied by the column of the view.

Not Good!

From this list the 01004, 01503, 01506 and especially 01517 just jump right out and scream at you! Here in Europe, we have a right to have our names or addresses correctly written and, in Germany with all the umlauts, it can get difficult if you then have a 01517 but SQLCODE 0 result!

I hope you don’t find this newsletter too unsettling as, after all, Db2 and SQL normally works fine, but I do think that these SQLSTATEs should really have warranted a positive SQLCODE when they were first created…

What do you all think?

TTFN,

Roy Boxwell

Update:

One of my readers wonders how practicle these “errors” are. A good point, and so here is a nice and easy recreate for the 01003 problem:

CREATE TABLE ROY1 (KEY1   CHAR(8) NOT NULL,                 
                   VALUE1 INTEGER ,                         
                   VALUE2 INTEGER )                         
;                                                           
INSERT INTO ROY1 (KEY1)               VALUES ('A') ;        
INSERT INTO ROY1 (KEY1)               VALUES ('AA') ;       
INSERT INTO ROY1 (KEY1,VALUE1)        VALUES ('B', 1) ;     
INSERT INTO ROY1 (KEY1,VALUE1,VALUE2) VALUES ('BB', 1 , 1) ;
INSERT INTO ROY1 (KEY1,VALUE1)        VALUES ('C', 2) ;     
INSERT INTO ROY1 (KEY1,VALUE1,VALUE2) VALUES ('C', 2 , 2) ; 
SELECT * FROM ROY1                                          
;                                                           
CREATE VIEW ROYVIEW1 AS                                     
(SELECT AVG(VALUE1) AS AVGVAL1, AVG(VALUE2) AS AVGVAL2      
 FROM ROY1)                                                 
;                                                           
SELECT * FROM ROYVIEW1                                      
;

This set of SQL ends up with these outputs:

---------+---------+---------+--------
KEY1           VALUE1       VALUE2    
---------+---------+---------+--------
A         -----------  -----------    
AA        -----------  -----------    
B                   1  -----------    
BB                  1            1    
C                   2  -----------    
KEY1           VALUE1       VALUE2    
---------+---------+---------+--------
C                   2            2    
DSNE610I NUMBER OF ROWS DISPLAYED IS 6

---------+---------+---------+---------+---------+---------+-----
    AVGVAL1      AVGVAL2                                                
---------+---------+---------+---------+---------+---------+-----
          1            1                                                
DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                           
DSNT418I SQLSTATE   = 01003 SQLSTATE RETURN CODE                        
DSNT415I SQLERRP    = DSN SQL PROCEDURE DETECTING ERROR                 
DSNT416I SQLERRD    = 0 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION       
DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,W,,, SQL WARNINGS                              
DSNT417I SQLWARN6-A = ,,,,   SQL WARNINGS                               
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

This is not so good if you ask me…

Posted on

2023-12 Bufferpool Check

Hi all! Welcome to the end-of-year goody that we traditionally hand out to guarantee you have something to celebrate at the end-of-year party!

BPOOL

All Db2 releases since the very beginning of time have used Buffer Pools and when Data Sharing came along in DB2 V4 Group Bufferpools got invented. Since then bufferpools have always been with us and sadly no-one seems to really care about them anymore!

Checked?

When was the last time that you actually checked your local and group bufferpools? Have you really seen any problems? The fantastic thing about Db2 is that you can have 1000’s of problems occurring every second or two but it *still* just keeps on truckin’ !

Where to Begin?

I always like to begin at the beginning and so review where you are right now by downloading and running the SOFTWARE ENGINEERING BufferPool Health check program! It is an extremely light weight tool that you must simply run *locally* on each and every member and it simply lists out what it finds as being bad, horrible or evil!

Old Advice

Refer to my “old” blog to see what you could/should be checking and how bad it can really get as well.

Whaddya get?

The BufferPool HealthCheck is a one step program that simply lists out whatever it finds as being not good. At the end of the documentation is a table listing out all the checks it does. There is one important check it does *not* do and that is the cross-check between all local bufferpool sizes and the initial size of the related group bufferpool. That is just a little bit too complex for some freeware!

Want more?

If the results help you or you are interested in more, then our SQL WorkLoadExpert for Db2 z/OS product has a new separate licensable Use Case – Buffer Pool which contains all the checks plus the GBP Initial size one.

How does it look?

In this screen shot you see the list of Bufferpools being looked at and do you notice the highlights on a couple of tabs? Those yellow stripes should tell you that Bufferpool simulation is on in these two buffferpools. Simulation is really really good, it came in with Db2 11 but most people missed the launch event! Check it out as it is really good but beware! Do not simulate three or more Bufferpools at the same time!

Order By?

The order that you see is the importance list. In my humble opinion this is the list of KPIs that should be worked on from top to bottom and there are more than on this screen grab!

Fixes?

On the right-hand side, we then show you which corrective action is required, if any, to get the bufferpool running better, faster and cheaper!

On the overview highlights we list out what is Good, Bad and Ugly with direct links down to the details. Here will also come a ranking and a highlighting of the Tab to drag your eyes to the problem children that you undoubtedly have!

Here finally is then the first “top down” view of all your local bufferpools so that you can quickly see the sizes and usage of your bufferpools

Naturally it all looks better in RL!

I would dearly love to hear from any of you who run this software and get before and after reviews! I am 100% sure that buffer pools and especially group buffer pools are simply being ignored these days!

TTFN

Roy Boxwell

Posted on

2023-11 Are we really living in an agile world?

This month I want to run through all the purely SQL changes that we have received in Db2 12 and in Db2 13 right up until Db2 13 FL504. I was reading about agile development and how fast the deliveries are and so I wondered how many purely SQL changes have we, as SQL Users, actually received over the last six to seven years?

Back to the facts…

Ok, in Db2 12 FL501 we got LISTAGG which was very cool indeed, apart from the fact that you could not use ORDER BY which irritated all the SQL developers I know quite a bit! IBM also created a whole bunch of Accelerator only “pass-thru” functions that I cannot ever use as I do not know whether or not any of my customers actually has an Accelerator… so, for me, they do not really count. In total 28 BiF’s either got pass-thru or extra Accelerator support so if you *have* an accelerator good news indeed! This was all enabled over Db2 12 FL Levels 504 and 507 as well as APAR PH48480.

Uniwhat?

A bunch of, for me, weird things were the UNI_60 and UNI_90 support added to LOWER, TRANSLATE and UPPER for both Db2 12 and 13. There must be a use case out there but I am lucky enough not to have found it yet!

Super MERGE

MERGE got a major overhaul in Db2 12 with the addition of DELETE support but the story of MERGE did not end there! It still had a major performance problem if any of the index columns got updated as then it was forced to fallback to a tablespace scan. With Db2 13 FL504 (APAR PH47581) this problem was solved – nearly…

The following conditions must be met to enable Db2 to use an index for a MERGE operation when index key columns are being updated:
– The MERGE statement contains a corresponding predicate in one of the
following forms, for each updated index key column:
index-key-column = literal-value, where literal-value is a constant or any
expression that can be treated as a literal, including a host variable, parameter
marker, or non-column expression.
index-key-column IS NULL
– If a view is involved, WITH CHECK OPTION is not specified.

Db2 SQL Reference

MERGE has basically become one of the most powerful SQL statements out there and you can actually cause terrible trouble if you use DRDA with VALUES clauses and hard coded “FOR 10 ROWS” style of SQLs. All is very well documented and worth a read under the heading:

DRDA considerations when NOT ATOMIC CONTINUE ON SQLEXCEPTION is specified (or the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause is not specified and source-values (VALUES) is specified)

Db2 SQL Reference

This Db2 13 APAR also enabled the chance of getting List Prefetch as an access path which is, as far as I can tell, the only “new” access path in Db2 13.

Pagination anyone?

The use of OFFSET was a great innovation for Db2 but the “other” pagination was better! I mean data-dependent pagination which changed this old chestnut of an SQL:

WHERE (LASTNAME = 'SMITH' AND FIRSTNAME > 'JOHN') 
   OR (LASTNAME > 'SMITH')

Into this modern SQL:

WHERE (LASTNAME, FIRSTNAME) > ('SMITH', 'JOHN')

Much much better and for online generated dynamic SQL, I am talking to you CICS, a fantastic win! To verify this you get a range-list index scan “NR” in your ACCESSTYPE PLAN_TABLE column when you EXPLAIN it.

One at a time please

Piece-wise DELETE using the FETCH FIRST nnnn ROWS was also a really good idea instead of causing possible lock escalations and/or timeouts. A simple loop around the DELETE statement and Bob’s your uncle!

Db2 13 – What’s New?

The big stuff here was the extension of the PROFILE table (I keep talking about it don’t I?) as now it also handles Local things – This is a game changer! Starting with CURRENT_LOCK_TIMEOUT & DEADLOCK_RESOLUTION_PRIORITY but I am sure this list will grow and grow. The PROFILE table is just way to good not to use these days!

AI got a major boost

We got a bunch of AI stuff in Db2 13 (SQL Data Insights) but the first new “agile” one was in FL504 when AI_COMMONALITY was released. It will hopefully enable shops to find outliers in the data which were not there at training time.

Db2 V8 finally done!

Finally, the last thing that was started way back when in DB2 V8 was done! The length of a column name has been expanded from 30 up to 128 bytes. However, do not do this! The SQLDA is *not* designed for this and so it might look nice on paper but, depending on how you interface to them, it might cause serious grief!

Lower cadence higher quality

IBM have announced a cadence of two FL’s per year down from the 3 – 4 when Agile all started and so I am happy that the list of changes will keep getting longer and the quality of the code higher.

Just SQL!

Please remember all I am talking about here is SQL relevant enhancements – there are tons of others as well – just think about Utilities or FTB etc. etc. For a full list always download and read the latest “What’s New?” guide.

Did I miss anything? Drop me a line if you think so!

TTFN,

Roy Boxwell

Posted on

2023-10 IDUG EMEA – Roy’s report

Ahhh! So good to get back on the road again… I am now back to my pre-COVID travelling and it catches up with you! I am soooo glad that I only have the German GUIDE in December and then I have *no* more planned travelling this year – Hoorah!

I have not heard of any outbreaks of COVID from IDUG yet, unlike TechExchange where I heard a bunch of Champions all got the lurgy again…

Back to Tech Stuff!

Tons of really good technical talks all started for me on Sunday already, as I flew in to Prague

I met “The Usual Suspects” at the hotel bar, including Julian Stuhler who was there, as a retired guy, to hold the keynote on Monday morning. We had a nice night chatting and drinking. Then early the next day, off I was to the Prague Conference Center (PCC) to register and get the booth built.

Once there, I met the dynamic duo! Dan had flown in after winning Best Speaker at the IDUG 2023 NA and it was great to see him, and Denis, again!

Of course, as I was busy building the booth, I missed Julian’s key note but I heard it was very good indeed. I hope that someone uploads the PDF to the IDUG site so I can actually read what he said! I heard that it was all about Past, Present and Future – the Evolution of Db2 and IDUG and it must have been really good as it overran and was full!

Due Diligence

Please remember two important things:

1) To get the links to work you must be registered at IDUG *and* entitled to the content. Otherwise you will get a “forbidden” screen popping up! If that is the case you can then think about buying the “Premium” version of IDUG membership which does allow downloading of conference files without having to wait around two years.

2) I am only one person, so I did not get to see every presentation at the IDUG. If the presenter was swapped or the session cancelled I might not have caught that, so I apologize if I have forgotten anyone or anything!

Track A is Where it Always Starts!

A01 with Haakon Roberts doing his usual fantastic stuff about Db2 Past, Present and Future. For me, the highlight was the Db2 13 FL504 new Built-in Function (BiF) for SQL Data Insights (SQL DI) AI_COMMONALITY that I had seen rumors about but now it is coming this month! UPDATE! Now Available, Dec 2023

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=42c241e6-1e16-740c-ee14-940c436994c7

A02 was Akiko with a 40-Year review of performance, including some very nice comparisons from the IBM Benchmarks of 13 years ago and today. Amazing how fast things get in just 13 years – 4.2 times faster but with 2.5 times more logging! Another nugget from this was that SQL Data Insights goes into fake parallel mode to enable ZiiP eligible workloads…

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=67afc3f7-6ae7-72af-ff96-71726cf5a7b7

A03 was Haakon again, this time showing us the Past, Present and Future of schema evolution. My take-away from this, is due to moving from multi-table tablespaces (Think QMF here!) you might well start running out of OBIDs in your Database. We at SEG have a Migration Health Check that tells you if you are indeed going to be knocking on the door of some nasty internal limits!

https://www.seg.de/produkte/produktliste/migration-healthcheck/

His presentation is here: https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=f85bd909-f1db-c0d8-c3d9-650cd6300554

A04 Rotate away your problems from Johan Sundborg. This includes nearly all the JCL you need to set-up and run Partition Rotation for your good candidates. You can save massive amounts of CPU if you have the right Use Case.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=16518129-de14-7a39-9b96-6b13e660eb01

A05 Decompressing the Compression in Db2 for z/OS. Everything you ever wanted to know but were afraid to ask about compression on Db2!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=e161d84b-a78c-b73b-ac00-652ffd4f0d2c

A06 Utilities What’s new? Haakon Roberts telling us all the new stuff!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=05227c1c-b4a3-e053-df8b-8c7227fecca9

A09 Establishing Db2 recovery procedures using redirected recover. A very good overview of doing RECOVERY using REDIRECTED as well. A good list of nasty little APARs where “old” table defs can get ya! Plus, a page about Rookie mistakes…

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=9c3a3538-4f41-7d8c-b818-f2ad4343f182EU23A11S.pdf

A13 Monitoring your distributed workload for Db2 for z/OS. This was all about the fascinating world of trying to figure out who is connecting with which type and version of connector to your Db2 sub-system. A brief overview of the new IFCIDs 411 and 412 with the very good data that they have is also included. A bunch of example SQLs rounded off a very good presentation indeed!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=55f37cfe-f5b6-54fc-b826-f0348daeb32b

A15 Implicitly or Explicitly Defined Db2 Objects the Good, the Bad and the Ugly. Steen doing his thang! A good detailed look at the pro’s and con’s of implicit spaces and the many pitfalls of PBGs with LOBs – I avoid them at all costs!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=9826831b-9fe7-0e17-f39c-fd98aae967e0

A16 How to avoid the loss of data and minimize the costs associated with a Db2 outage. Top tips all about Backup and Recovery. Including the separation of catalogs that is required for successful BACKUP SYSTEM.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=4fb8d50b-6dea-c1ca-99e4-8b7ff550462d

A17 Sun Up to Sun Down The Chase to Achieve 24-7. This was a peek into parallel sysplex and how to guarantee you do not fail! Also includes a bunch of Db2 13 enhancements for continuous availability.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=cbadeaf5-ccf9-8cf7-a48d-670977187d23

Track B

B02 The A-Z of Logging for Db2 for z/OS explained everything about what is on and not on the Log and really contained an A to Z list. However, the values for J, K and Y were missing – Perhaps my trusty readers can come up with some values? E.g. J – JCL, K – KB and Y – Yottabyte! Further to this, we have at SEG another freeware that reads your archive logs and tells you what you have! You may well be surprised at the amount of stuff that gets logged!

https://www.seg.de/produkte/produktliste/db2-archive-log-viewer/

Julia’s presentation is here: https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=e70efe05-a89a-0821-3a8e-1251a36665cc

B03 Recovery From Z To A was a great presentation full of useful info all about doing the dangerous job of RECOVERY. Well worth a review!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=3bc5b1c9-030f-124c-dfaa-0b61b037549d

B04 A day in the life of a Db2 for z/OS schema was a really nice explanation of schema life from three different perspectives: Developer, DBA and System! Pretty neat!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=8ed0f8be-7c05-c9d5-913e-98ddde18e5e2

B05 Reorg Rabobank biggest Db2 table from APN to RPN. A really good real-life story about REORGing epically large tables. My highlight, was the fact that the elapsed time output of the reload message “rolls over” after 99 hours!!!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=6988c781-886b-a050-7803-34324f86a2b1

B06 Db2 For z/OS and Unicode – What you need to know. A complete review and overview of the ever-present Unicode code pages that are always with us all these days!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=02855744-2e25-19fd-8f3b-da0fa934430f

B07 Getting Ready For Db2 13 – Updates. This was a review of Db2 12 Agile, continuous delivery and the steps required for a painless migration to Db2 13.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=5c02aa69-074d-9e84-4901-b0dfdd4ace75

B08 Measuring is knowing – Db2 for z/OS instrumentation enhancements you may have missed. A very nice recap of all IFI changes in the past few years and also Commands and message changes. Please note that “Miscellaneous” was about 50% of the presentation!! Great data here and a reminder about being careful about RID overflows not actually being RID overflows.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=1f694955-7512-7476-8b09-0abe6b9394e4

B09 Who’s afraid of DDF? An explanation about DDF and how to get a handle on it. Another call to arms about using the PROFILE functionality (One of the best kept secrets in Db2 for z/OS IMHO!)

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=14ceba96-91a9-ce4b-9456-0fe782b3e735

B10 “Run it Back” Db2 for z/OS All new “2023 SWAT Tales” Was another great list of things from Anthony which you all should be doing. Included is a great visualization of Cyber Resiliency/”Safeguarded” Copies which we did not even need a few years ago! A very important point that he mentioned, is the PTFs that we must all apply. I can happily recommend you subscribe to my APAR Newsletter where I update, every month, all the APARs of interest including HIPER and PE ones! Just go here to see the latest and, if you want to, you can then register for email updates:

https://www.seg.de/apar-update/

Here’s Anthony’s presentation:

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=f4733d55-8200-318f-ea86-cbf08a3ca779

B11 Db2 13 for the production DBA. Has three points for the DBA today. PBG – PBR Conversion. DDL break in using the PROFILE tables again. Finally, Dynamic Query Stabilization. Great stuff!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=c14e1ec6-df91-734f-075f-2960700b287a

B12 How to Hack Db2 for z/OS – Lessons Learned from Mainframe Hackers. That grabbed your attention didn’t it? Slide 19 makes me laugh every time I see it! Seriously, the major problems are allowing uncontrolled access to APF load libraries (This has been true for decades of course, but these days with the new twist of USS +a usage! Slide 55 is extremely useful here.) the use of Magic SVCs (These I have used in the past…) and basic user id protection.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=e9815b49-c30f-6bb4-f19f-d8d23971ce7a

B13 Modernise your Db2 Environment – Top 20 Features People are Still not using. Adrian doing a great job of reminding us of what we have but are stubbornly ignoring! Slide 21 contained a very simple way of seeing if Parallel Access is for you! Slide 31 also reflects my opinion with RAM availability on z/OS. I am sure we (the DBAs) have much more RAM available than we are aware of. It can be *much* better used as an increased size for your buffer pools! Then slide 34 showed the real benefits…

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=79d5a8f5-cf4a-8b7f-e021-a61e328795a0

B14 Recovering to another subsystem with DSN1COPY. Sadly, not available at this time. A09 is lurking here!

B15 Db2 13 for z/OS: What is new in Security and Compliance? All about SMF type 1154 and its usage in compliance. Then the use of various caches within Db2 for Authorizations finally discusses PROFILE tables again!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=988aca9c-18f8-b088-efc3-7796b2c09374

B16 When reality “derails” capacity planning for Db2 zOS. No link available yet, but it is in the zip file as a download. My mantra – slides 26 – 35! Especially slide 35…

B17 Db2 13 for z/OS Utility History :What is it and how to use it. All about the new Utility History table and example SQLs about how to use it.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=cba21c87-c999-7f8b-950a-eab25d9c7b60

Track E

E02 SQL tuning : A business Case. Where Kurt held a plaidoyer for SQL Tuning and Training.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=b3baae65-1eab-c9ae-33b1-6850a9de028f

E04 Fun with Ansible and Db2 for z/OS. If it is possible to have fun… A ton of examples, tips and tricks etc. If you are going down the road of Ansible this should be your starting point!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=f505a697-6df7-1965-b91d-ee781a426a9d

E07 Near real time reporting system with Db2 data sources from both mainframe and AIX. Al about ELT and transferring data!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=88c98e8d-bb33-c3a7-c7d6-0afd4b3610df

E08 Db2 Analytics Accelerator – What is New, what are customers doing with that! Hard numbers and data from real life customer usage of the Accelerator. The performance boost of NVMe is impressive! Ending with a sneak peek at 7.5.12.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=b07fe65a-770f-9480-1f86-fc1989458db9

E11 Successfully migrate from CDC to Integrated Synchronization IBM Db2 Analytics Accelerator. A nice plan to migrate from CDC to InSync, listing out the steps to take and how to get there.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=be93c0fa-44f1-80b0-1aad-b87897fe9bd7

E12 It’s AI Jim, but not as we know it! My little presentation all about the Hype and the Reality of AI and then showing how useful the three (four!) SQL Data Insights Built-in Functions that are supplied free of charge with Db2 13 for z/OS are.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=40c942dc-b33d-57b4-f6f0-3aacb125a58a

E13 Db2 and the Magic of Disk – This was all about Disk I/O from way back when to today. Very interesting indeed. Gotta try and reserve a table at Katja’s restaurant one day!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=218288ec-3859-b5f3-696a-0d14b0bf780e

E14 Db2 for z/OS and LUW Big Buttons for Application Performance. DanL strutting his stuff and basically trying to get us to reduce the number of SQL calls or the number of SQLs by using joins, views etc. as these are, by definition, the best way to save time and cpu!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=ba91de38-706e-3aa3-9575-746afea05b9d

E15 ‘The Art of War’ against Bad SQL. Cool title indeed! A very good presentation with real world examples about how simple changes can save mega-bucks – Plus plenty of cool quotes!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=a2269a6d-6186-5511-cd3d-e918da572510

E16 Accelerator on Z Monitoring and workload assessment update. A review of how to monitor the Accelerator(s) you have and make sure enough disk and memory are available and used!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=400e832d-08a6-29db-fc9b-e4f27b84ddf3

E17 How to Drive Down Database Development Dollars. An absolutely wonderful mix of tips and tricks in a wide variety of languages, all about getting more performance and less pain out of your system.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=df73726d-e847-88d1-4af3-9c19fcdf2d7b

Track F

F01 Db2 DevOps, Zowe CLI plugins, VS Code extensions and more! As the name suggests, a ton of stuff about DevOps, Zowe and VS Code extensions!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=61a09bac-0245-1c93-cf1e-c774363165bd

F05 SQL on Db2 for zOS: The Missing Parts and how to deal with them. Thomas lists a few SQL challenges he has had, how he solved them and how he would *like* them solved by IBM development! There is also a nice list of AHA! Ideas you may wish to vote for.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=3bb44f98-bf17-2a78-94df-835bc1498f13

F07 Db2 13 Application Development Topics. Gives a great review of Db2 13 and what SQL changes are contained within as well as PROFILE table again! Get the hint? Included at the end are 12 pages all about the “history” of SQL fitting in to the overall theme of “40 Years”

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=c2bc2f2d-5f8c-8089-2938-6c08c7166dfe

F08 The Business Value of Db2 SQL Data Insights. All about how to use the SQL DI in a real business case. Starts with football but ends with an Insurance Case. Well worth looking into for the examples contained within!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=f5a2dba5-3d4d-703e-d8dd-f1ededcfbb96

F09 Application Modernization – Considered those Db2 for z/OS Capabilities? Going modern but incrementally. She showed how many JAVA, Python, node.js and Go developers there are out there… Loads! As a bonus it mentions PROFILEs again…

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=7d923e7b-fc7b-b64b-3c93-433479360d1f

F11 Lessons learned while enabling DDF in an existing Db2 Datasharing environment. A good way to show how to set up DDF including security and log performance. The recommendations for application name and accounting to be used are very good and I can also recommend it! It is the *only* real chance to see which application is connecting to your host Db2 subsystem! Guess what? They also used PROFILE tables ….

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=130dc657-f750-d913-f1a4-9c1a9f4bd3c4

F12 CLPPLUS : The Other Db2 Client. A cross platform presentation and it contains what you need to know about getting LOAD on z/OS to work from LUW. Very cool and thin – unlike me …

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=faa4ba63-5ede-be6f-d791-2b1c439aaf35

F13 Back to Basics with Db2 buffer pools. This is a great intro and explanation of buffer pools. What they are, what to look for and what to do! I held a vendor presentation P06 which covers *exactly* the same ground! My vendor presentation is here:

https://www.seg.de/event/idug-2023-emea-db2-tech-conference/

And the original IDUG presentation is here:

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=96e5107b-1b98-bb4d-e533-a527937698a5

F14 Encrypting Db2 Connections with TLS/SSL. Everything about TLS/SL – It gets really interesting for z/OS people around slide 15 when “keyrings” get mentioned! Slides 41 to 44 are also very handy for trouble shooting.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=5161c4be-93d8-0809-6355-20d087a4e2cf

F16 Get Cozy with Ansible for z/OS. Apart from having the absolute coolest session code of the IDUG, this was all about going Ansible on the Mainframe. This is an introduction into the world of Ansible automation on the mainframe including all that goes with it. Impressive stuff!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=0305ef2d-6900-8f96-660b-040c9265bda9

F17 Rock & Roll with Db2 for z/OS “Hey Db2, suggest music like zzTop” – This was a deep dive into SQL Data Insights on z/OS using Spotify as an example. It also shows the internals of the ML system used. It finishes with a “call to arms” to get other people interested in the Host side of things. Slide 19 is very handy for all the Prereqs you will need.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=7db8326f-7170-2c94-0f8f-03782f103ee7

Track G

G01 Database Design Basics. An excellent intro into DBMS’s and very good ideas about design especially for OLTP and OLAP and, one of my faves, naming standards for columns and tables!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=7fd0c83e-9248-2500-5c5f-9062f282ee9b

G02 The Basics of SQL Queries: From Fetches to getpages and I/Os. Basically, an SQL 101 but contains a lot of good data. The use of buffer pools is well explained and how they can really help (see earlier presentations!) Strangely PROFILEs were *not* mentioned this time!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=1119b400-8b89-71e9-f2da-7c08a7076eb5

G03 DB2 SQL – go beyond the usual – My current TOP 40 SQL tips, tricks, and opinions. Brian gave a great presentation about the modern features of SQL that you all should be using! Well worth reading.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=2f992cd6-976a-f790-7cbf-09993cdce0ca

G04 Advanced Db2 Performance Tuning for Beginners. Joe doing his usual great work of showing how to really do SQL tuning. I especially liked the Filter Factors page!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=c1a5c6dd-8771-78fa-507b-fb242ac3b7b3

G05 Back To Basics Real Time Statistics: What are they and how are they used? Another 101 style presentation but this time covering everything about the RTS tables and how you can, and should, use the data within.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=33ac07e5-7967-60ec-b75c-2c9f6386ec9f

G10 IBM watsonx.data & Db2 Warehouse: Scale analytics and AI across the enterprise. All the low-down about watsonx including the “new” governance part that we will all need to get full transparency.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=3c9aa700-57c9-efb4-23d4-f18f30e239e6

G15 Db2 for z/OS administrative tooling strategy : Customer feedback, Roadmap, future direction & DEMO!! Naturally no demo here, but I am sure you can request one! All about Admin Foundation and the EOS of Data Server Manager in March 2024. It then went on to tell us why we should all be using Visual Studio Code and then adding in all the various extensions (Db2, SQL, COBOL etc. etc.). Here lies the future!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=5f4fcb40-c631-d784-8da4-dace8ce19ee8

G16 Db2 AI for z/OS Strategy and Technical Deep Dive. Akiko took us on a tour! The two AIs out there now for Db2: SQL Data Insights (For free) and Db2 AI for z/OS (Not for free) now at version 1.6 and the latter was the topic that was discussed including System Assessment.

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=3142d8ef-7b0a-6695-f2f1-e6a0cb6c6885

G17 Db2 and Zowe. Everything, everywhere at once ! A run-through of Zowe over the years and also Developer extensions. If you are starting down the road of Zowe or Admin Foundation and Visual Code this is a good starting point!

https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=13479e3a-344f-82f8-9c28-4783c710f2d2

It was another really good IDUG with loads of excellent and interesting z/OS sessions. I hope to see you in Valencia in 2024 for EMEA or Charlotte for NA !

Please remember: You must be authorized to access the IDUG data and if the links fail please contact IDUG and not me 🙂

TTFN,

Roy Boxwell

Posted on

2023-09 Toronto visit

Ahhh! What a great time I just had in sunny ol’ Toronto. I was extremely happy to be back at the Central Canada Db2 Users Group (CCDUG) meeting for 2023 held at the BMO Institute For Learning. It has always been a fantastic venue for presenters and attendees, and this year we had the added bonus of a “Coyote” warning… fun fun fun!

The CCDUG is a two day, three track mini-IDUG if you like, and the organizers added onto the second day an AppDev track as well. So you had Db2 for z/OS, Db2 for LUW, IMS and AppDev – A very nice mix of things indeed!

Just the Facts, Ma’am!

I am mainly a z/OS style of guy (old school!) and so went for the z/OS track of course! It all started, as normal, with a keynote from Michael Kwok that was all about “accidental discoveries” and how these often have had a tremendous impact on the world. Examples included Penicillin and Microwave cookers. It also included Db2!

He highlighted the environmental factors required for innovation to succeed:

01 Have an open mind

02 Use Strategy as a filter

03 Commit

Open Minded?

For the first point, Penicillin, having an open mind was important when Alexander Fleming, in September 1928, noticed the effect that a mold had on his old petri dishes. It was having an open mind that enabled him to really see the very first antibiotic in action. Sadly, it took another 14 years before it was actually used in a pure form and yet another year before mass production started in the second world war.

Strategy is Everything!

Using Strategy as a filter was highlighted by looking at how Bill Gates took advantage of the CP/M creators Digital Research people’s failure to meet and agree a deal with IBM to discuss a new OS for the IBM 5150 PC. IBM then contacted Microsoft, who agreed to write PC-DOS which evolved into MS-DOS which basically started the modern PC world we all now know today.

Commit is Not Just a Db2 Verb!

Commit was all about the invention of Microwave Cookers! Percy Spencer working at Raytheon in 1945 was working with Radar sets using high energy magnetrons (A British invention, again in the second world war) and he noticed that a chocolate bar in his pocket melted. It took an awful lot of commitment to eventually get the product to market and it first appeared in 1947 as the “Radarange” at $5000, 3kw and was water-cooled!

Oh oh! IBM at Work…

Into these three ideas comes IBM! Back in the 60’s and 70’s, data centers started storing more and more data and Tedd Codd wrote down the rules of the Relational Data Model in his seminal paper “A Relational Model of Data for Large Shared Data Banks” in June 1970. Now back in those days, IBM was not exactly well known as being Open Minded, Strategic or Committed!

Then, a few extraordinary individuals pulled it all together to actually get what was called System R, the granddaddy of all Relational Databases, into existence, starting in 1974 and got its first customer in 1977. It still took another six years to actually arrive at DB2 in 1983, and ten years later, we got DB2 for LUW as well. The actual GA date of DB2 1.1 was the 2nd of April 1985.

So, basically, Db2 was an accidental discovery!

Then it Begins!

The sessions at CCDUG then all kicked off, so here is my list of the ones I either attended or read up afterwards (naturally, I cannot attend every session!) All are linked directly to the CCDUG website and I thank them for allowing public access!

Database Administration Enhancements of Db2 13 for z/OS from Robert Catterall. An excellent run through everything you need to know about Db2 13 including more PROFILE support. This is probably one of the most under-used Db2 features out there.

Db2 for z/OS 101: Buffer Pools and Group Buffer Pools from Tori Felt and Keziah Knopp. Was a highly entertaining 101 level intro to the complex world of Buffer Pools, Managers and disk usage within Db2 – Highly recommended as these little details easily get lost nowadays!

It’s AI Jim, but not as we know it! from me. Obviously, a fantastic presentation all about AI – truth or hype? 😄

Analysis of DEADLOCK IFCID 172 in SMF with no special tool and low cost from Xiaodong Ma. This was all about reading the bits and bytes from the IFCID to find *all* the blockers and waiters – not just the first two you get in the DSNT375I messages.

Who is afraid of DDF? from Toine Michielse was all about controlling your DDF workload simply and easily. Use of PROFILE is also recommended …

End of Day One

Then we had a very pleasant evening with the Vendor Expo and prize draws before retiring to the bar to drink some cold beverages and play pool!

Next day, bright-eyed and bushy-tailed we started off again.

Securing Db2 for z/OS Data: Encryption and Much More from Robert Catteral which contained a ton of great information all about encrypting and securing your data and communications – Great stuff!

An Audit a day keeps the lawyers at bay! from my colleague Ulf Heinrich listed out all the requirements for auditing a Db2 system from the auditor’s perspective. Including real-world examples and ending with a “how it looks in Zowe” bonus preview!

Db2 for z/OS Administrative and Developer tools strategy: Customer feedback, roadmap, and future direction from Sowmya Kameswaran was all about the ongoing project to unify the user experience in Db2. This includes the IBM Unified Management Server for z/OS, IBM Db2 Administration Foundation for z/OS, IBM Db2 DevOps Experience for z/OS, IBM Query Workload Tuner for z/OS , IBM Db2 Analytics Accelerator Administration services for z/OS , Zowe and IBM Db2 for z/OS Developer Extension! A ton of stuff here!

Partition By Range (PBR) and Relative Page Number (RPN) explained from Frank Rhodes drilled down into the nitty gritty details of PBG, PBR and the seven-byte RID!

Db2 For z/OS and Unicode – What you need to know from Chris Crone went into details about what exactly you need to know about UNICODE and how it affects you, your data, and your SQL applications! COLLATION_KEY is the really good one here!

Db2 for z/OS System Profile Monitoring: Overview and Db2 13 Enhancements from Mark Rader contained all the great new stuff in Db2 13 regarding PROFILE usage. Once again well worth a read as it is the best way to control DDF. Slide 15 is the highlight for me!

On the Application development front there were also a whole bunch of great presentations:

Db2 SQL and SQL PL – A Journey Through Db2 12 and 13 Functions Levels from Chris Crone where he runs through the new SQL and SQLPL stuff from Db2 12 before showing all the new stuff in Db2 13 as well.

Db2 for z/OS: REST and Hybrid Cloud from Tori Felt and Keziah Knopp was a huge presentation all about RESTful APIs and all the fun and games involved with them… Covered here was all the stuff on GET, POST, PUT etc. etc., also JSON, z/OS Connect et al!

Db2 Hot topics from Progressive Insurance from Bob Vargo and Dustin Ratliff. They discussed the challenges of how to get SQL Data Insights up and running and then got into FTB and PROFILE usage again (I love PROFILEs, have I mentioned this before?)

Explain explained from Toine Michielse. Does what it says on the side of the can! He shows, and explains, all the available access paths so that everyone is “on the same page” afterwards.

Bringing Db2 for z/OS-Based Applications Into the Modern Age from Robert Catterall where he showed the modern new way to access the data using REST services with plus and minus points of them. Then he went on to highlight the “new features” in Db2 for z/OS like System and Business time or XML columns and tables and finished off with a review of the advantages that SQL PL and Native SQL Procedures can give you.

DB2 SQL – go beyond the usual – My current TOP 40 SQL tips, tricks, and opinions from Brian Laube where he discussed all of the tricks and secrets he has learned over the years. Very worthy of a good read!

That’s all folks! I did not even mention all of the LUW or the IMS ones – Feel free to look for them as well if you are interested.

As always, a fantastic conference and I am already looking forward to the next one in 2024!

TTFN,

Roy Boxwell

Posted on

2023-08 Taking a dive into the bufferPOOL

This month I wish to chat about Db2 on z/OS BUFFERPOOLs and their misuse throughout the world.

One Size Fits All?

Bufferpool tuning tools have been available for decades, but I had the feeling that the usage has dropped off these days and the cut-and-paste generation has started to cause, shall we say, problems in the pool!

Oldies But Goldies

The standard set of rules *always* starts with “Thou shalt not pollute the pool!”

The idea here is very simple: Keep the Db2 Directory and Catalog apart from “user data” – the access patterns and sizes for the Directory and Catalog are different than normal user data and you do *not* want to mix’n’match here!

Keep Indexes and Tablespaces Apart

Just like teenagers, keeping them apart can help a lot! The typical access patterns for indexes (especially compressed indexes) and user data are always different. These should very rarely share a pool!

Know Your Access Types!

Are you a sequential access or a random access type? The difference is important – Db2 tracks all the different access types, so it is not that hard to see whether or not sequential access is pushing out your index random access leaf pages, for example!

Sort??

Sort and work spaces should also go into their own pool as sort tends to be 100% sequential.

What About Prefetch Minimum Size??

To activate a good prefetch chain, your bufferpool must meet minimum size limits. Smaller than the minimum size and your prefetch usage goes horribly wrong.

PGFIX – Yes

This has been described as “the no brainer No. 1” as Db2 basically *requires* enough memory for the BPs not to page, and so you can save a ton of CPU by setting PGFIX(YES) – Note that this is not same as setting PGSTEAL(NONE) !

LOB and XML Spaces

These are the ugly ducklings under the tablespace family tree and both should *not* be in any normal BP as the access methods for them are 100% different!

Check Again!

So now go and check your BUFFERPOOLs and see if you have broken any of the above seven BP commandments? I am 100% certain that you have…

Are You on the Same Page as Me?

If you have PAGE INs then that is normally, to quote IBM, sub-optimal. If the number of PAGE INs is greater than the BP size it is very, very bad indeed!

What About GROUP BUFFERPOOLS?

Always worth checking these and seeing if the Cross Invalidations is > 10% or if the storage counters are above zero. Also, check what your current RATIO is… Might be way too low or way too high!

Bufferpool Thresholds?

You do know your BP thresholds, don’t you?

Immediate write – 97.5% – Very very bad.

Data Management – 95% – Bad.

Prefetch disabled – 90% – Not good.

Then the Thresholds that You Can Set:

Sequential Steal threshold (VPSEQT) – 80% range 0 – 100. If zero then most, if not all, prefetch is switched off and no parallel prefetch is allowed.

Virtual Parallel Sequential threshold (VPPSEQT) – 50% range 0 – 100, zero means no parallel.

Deferred Write threshold (DWQT) 30% range 0 – 90.

Vertical Deferred Write threshold – 5% range 0 – 90 as a percentage, and if the first value is zero then the second value is absolute number of pages in the range 0 – 9999.

Sequential Steal

This is how much of the BP can be taken over by purely sequential data access pages. For SORT pools 90+ is fine. If you then see SYNC READ I/O going up slowly, lower back towards 80.

Deferred Write

The DWQT is for the complete bufferpool. If that percentage is hit, then an asynchnronous task is kicked off to write updated pages to disk, until it hits 10% less than this threshold. Now for some objects that is too large a number and can lead to flooding, so we also have the VDWQT which has the same control but at the object level. You specify how many pages per object can be updated before Db2 kicks off async writing.

Metrics …

In the beginning was the Hit Ratio but it is a bit of a red herring these days. Much better is to check the average page residency times. If the data is kicked out of the BP before 600 seconds it is probably time to do some changes! There are tons of other metrics but I will not go into the nitty gritty here!

Real World Results!

I asked a few of our customers do some -DISPLAY BUFFERPOOL commands and then send me the data. I then did an analysis of the data and was a little bit surpised at the results:

Management summary: Every customer has bufferpool problems, even those that swore everything was green at their site!

In the following graphics everything that does not have a green/white background is not good (Apart from the two Intensity heat columns where only the red ones are worth checking!)

Examples

Here you can see it is not that bad. The hit ratios are not good, (the negative Hit Ratio is caused by serious prefetch activity!) and the number of writes is below expectations, but the prefetch size is the big worry here! One good point is the near perfect VPSEQT compared to the calculated one. Very good!

The number of PAGE INs here is very bad but is caused, obviously, by the PAGEFIX(NO) defs.

The VDWQT per second rate is well above what it should be, prefetch size is very wrong and the Page Arrival rate is high!

The residency times are at both extremes here!

Here, there are way to many DWQT and VDWQT being hit per second.

And Finally

So what can you do? Well, you can contact me and I will tell you which commands to issue. Then I can send you back a PDF with the details above. Please note that I can only process the first ten requests! But I will allow five from EMEA and five from the USA to make the timezones a bit fairer!

Future Trends and Directions

We’re currently extending SQL WorkloadExpert with it’s Zowe front end, so that you then have the chance to fix things with a couple of clicks and even play around with simulated buffer pool sizing! Below I’m sharing a sneak preview:

and then drilling down:

Simulated?

I do hope that you are aware of the “simulated bufferpool” feature and have at least looked at it? It is one of the best things in years to have happened to bufferpool tuning!

So whaddy’all think? Is it time to go back to the drawing board with Bufferpool tuning or are yours all 100% correct?

I would love to hear from you!

TTFN

Roy Boxwell

Posted on

2023-07 Directory Details

This month, I am going to tell you a true story from our labs in Düsseldorf, where I learnt a few things about Db2 and how the Db2 Directory works…

What is it?

The Db2 Directory is the “shadow” catalog if you like. It is basically the machine-readable stuff that echoes what is in some of the Db2 Catalog tables that we all know and love and use nearly every day!

Whatya got?

Well, the Db2 Directory is the DSNDB01 database and, up until Db2 10, was completely hidden from view when looking at it with SQL. The VSAM datasets were there but you could not select from them – Pretty useless! My company, Software Engineering GmbH, actually wrote an assembler program to read the SYSLGRNX table and output the interesting data therein so that it could be used for image copy decisions etc. But, then IBM finally decided to open up the Db2 Directory to our prying eyes! (Sad footnote: They still output the LGRDBID and LGRPSID as CHAR(2) fields!!! Completely useless for joining of course – See my older blogs all about SYSLGRNX and doing the conversion to a correct SMALLINT way of doing it!

Tables, Tables, Tables

You actually do not have that much data available for use with it!

U is Unique index Y or – for Duplicates allowed and AUX for the standard LOB AUX Index. Bold field names are DESC order.

This table gives you an overview of what you get and also shows the two tablespaces that were, for me at least, of special interest!

Where’s the Beef?

On my test system, the tablespaces SYSSPUXA and SYSSPUXB were both getting larger and larger. Now the task is to understand why you need to know which of the above tables is “linked” to which other ones, and then which link to the Db2 Catalog tables. Time for another table!

So?

What you can see from this, is that the DSNDB01.SPT01 (which we know is the SYSIBM.SPTR) is linked to a whole bunch of Package-related tables and this is all documented – so far, so good! What got me interested, were the LOB tablespaces SYSSPUXA and SYSSPUXB. In my system they were taking up 13,929 and 6,357 Tracks respectively. Might not sound much to a real shop out there, but for me with only 118,000 rows in the SPTR it piqued my interest!

What is in it?

The SYSSPUXA (Table SYSSPTSEC_DATA) contains the machine-readable access paths generated by BIND/REBIND with versioning etc. so that being quite big was, sort of, OK. The SYSSPUXB (Table SYSSPTSEC_EXPL) contains *only* the EXPLAIN-related information for the access path. This was added a few Db2 releases ago so that you could extract the current access path without doing a REBIND EXPLAIN(YES) as that would show the access path “right now” as opposed to what it was, and still is, from, say, five years ago. These two access paths might well be completely different!

How many?

The SPTR had 6,630 tracks.

The SYSSPTSEC_DATA had 13,929 tracks.

The SYSSPTSEC_EXPL had 6,357 tracks.

This is a total of 1,795 Cylinders for 118,553 rows of data – for me, that’s a huge amount.

What is “in” there?

I quickly saw that there were *lots* of versions of packages and some very odd “ghosts” lurking in the data. Here’s a little query to give you a glimpse:

SELECT SUBSTR(SP.SPTCOLID, 1, 18)  AS COLLID    
     , SUBSTR(SP.SPTNAME, 1, 8)    AS NAME      
     , SUBSTR(SP.SPTVER, 1 , 26)   AS VERSION   
     , HEX(SP.SPTRESV)             AS RESERVED  
FROM SYSIBM.SPTR SP                             
WHERE 1 = 1                                     
--  AND NOT SP.SPTRESV  = X'0000'               
  AND NOT SP.SPTCOLID LIKE 'DSN%'               
  AND NOT SP.SPTCOLID LIKE 'SYS%'               
LIMIT 100                                       
;                                                

Now, the weird thing is, that the SPTRESV (“RESERVED”) column obviously actually contains the Plan Management number. So, you have “normally” up to three entries. Zero for Original, One for Previous and Two for Current. What I saw, was a large number of Fours!

Set to Stun!

Where did they all come from? A quick bit of looking around revealed that it was Package Phase-In! They have to keep the old and the new executables somewhere… So then, I started trying to work out how to get rid of any old rubbish I had lying around.

FREE This!

First up was a simple FREE generator for old versions of programs deliberating excluding a few of our own packages that require versions for cross-system communications.

WITH NEWEST_PACKAGES (COLLID                      
                     ,NAME                        
                     ,CONTOKEN ) AS               
 (SELECT SP.SPTCOLID                              
        ,SP.SPTNAME                               
        ,MAX(SP.SPTCONID)                         
  FROM SYSIBM.SPTR       SP                       
  WHERE NOT SP.SPTCOLID LIKE 'DSN%'               
    AND NOT SP.SPTCOLID LIKE 'SYS%'               
    AND NOT SP.SPTNAME IN ('IQADBACP' , 'IQAXPLN')
  GROUP BY SP.SPTCOLID                            
          ,SP.SPTNAME                             
 )                                                
SELECT DISTINCT 'FREE PACKAGE(' CONCAT SQ.SPTCOLID
                CONCAT '.'      CONCAT SQ.SPTNAME 
                CONCAT '.('     CONCAT SQ.SPTVER  
                CONCAT '))'                       
FROM NEWEST_PACKAGES   NP                         
    ,SYSIBM.SPTR       SQ                         
    ,SYSIBM.SYSPACKAGE PK                         
WHERE NP.COLLID   = SQ.SPTCOLID                   
  AND NP.NAME     = SQ.SPTNAME                    
  AND NP.CONTOKEN > SQ.SPTCONID                   
  AND SQ.SPTCOLID = PK.COLLID                     
  AND SQ.SPTNAME  = PK.NAME                       
  AND PK.CONTOKEN > SQ.SPTCONID                   
  AND PK.LASTUSED < CURRENT DATE - 180 DAYS       
--LIMIT 100                                       
;                                                

Note that this excludes all IBM packages and my two “SEGUS suspects” and pulls out all duplicates that have also not been executed for 180 days. Running it and then executing the generated FREEs got rid of a fair few, but those “Four” entries were all still there!

FREE What?

Then I found a nice new, well for me anyways, use of the FREE PACKAGE command. You have to be brave, you have to trust the documentation and you trust me because I have run it multiple times now! The syntax must be:

FREE PACKAGE(*.*.(*)) PLANMGMTSCOPE(PHASEOUT)

Do *not* forget that last part!!! Or make sure your resume is up to date!

This then gets rid of all the junk lying around! Was I finished? Of course not… Once it had all been deleted I then had to run a REORG of all these table spaces and so now we come to part two of the BLOG…

REORGing the Directory

Firstly, if you are in Db2 13 you must Reorg the SPT01 and SYSLGRNX anyway to get the new DSSIZE 256GB activated. Secondly, Db2 is clever, so for certain table spaces, it will actually check the LOG to make sure you have taken a COPY:

“Before you run REORG on a catalog or directory table space, you must take an image copy. For the DSNDB06.SYSTSCPY catalog table space and the DSNDB01.DBD01 and DSNDB01.SYSDBDXA directory table spaces, REORG scans logs to verify that an image copy is available. If the scan of the logs does not find an image copy, Db2 requests archive logs.”

Db2 for z/OS Utility Guide and Reference “Before running REORG TABLESPACE”

Pretty clear there!

We are good to go as we only have the SPT01 and its LOBs. Here is an example Utility Syntax for doing the deed:

REORG      TABLESPACE DSNDB01.SPT01       
           SHRLEVEL REFERENCE AUX YES     
           SORTDEVT SYSALLDA SORTNUM 3    
           COPYDDN (SYSC1001)                   

Pretty simple as the AUX YES takes care of the LOBs. Remember to COPY all objects afterwards as well!

COPY       TABLESPACE DSNDB01.SPT01 
           COPYDDN (SYSC1001)       
           FULL YES                 
           SHRLEVEL REFERENCE       

COPY       TABLESPACE DSNDB01.SYSSPUXA
           COPYDDN (SYSC1001)         
           FULL YES                   
           SHRLEVEL REFERENCE         

COPY       TABLESPACE DSNDB01.SYSSPUXB
           COPYDDN (SYSC1001)         
           FULL YES                   
           SHRLEVEL REFERENCE

How many after?

Once these were all done, I looked back at the track usage:

The SPTR had 4,485 tracks (was 6,630)

The SYSSPTSEC_DATA had 7,575 tracks (was 13,929)

The SYSSPTSEC_EXPL had 4,635 tracks (was 6,357)

This is a total of 1,113 Cylinders (was 1,795) for 90,858 (was 118,553) rows of data.

This is very nice saving of 25% which was worth it for me!

Directory Tips & Tricks

Finally, a mix-n-match of all things Directory and Catalog.

Remember to always reorg the Directory and the Catalog table spaces in tandem.

Remember to always do a COPY before you do any reorgs!

FASTSWITCH YES is ignored for both Catalog and Directory reorgs.

Any more Limits?

Yep, you cannot REORG the DSNDB01.SYSUTILX at all. Only hope here is IDCAMS Delete and Define – dangerous!

LOG YES is required if SHRLEVEL NONE is specified for the catalog LOB table spaces.

If SHRLEVEL REFERENCE is specified, LOG NO must be specified.

The SORTDEVT and SORTNUM options are ignored for the following catalog and directory table spaces:

The COPYDDN and RECOVERYDDN options are valid for the preceding catalog and directory tables if SHRLEVEL REFERENCE is also specified.

Inline statistics with REORG TABLESPACE are not allowed on the following table spaces:

IBM now pack a complete Catalog and Directory REORG with the product to make it nice and easy to schedule and run! Look at member <your.db2.hlq>.SDSNSAMP(DSNTIJCV) for details.

To REORG or not to REORG?

This is the eternal question! For Db2 13 you must do at least two table space REORGs, as previously mentioned, but the hard and fast rule about the complete Db2 Catalog and Directory is: about once per year is normally sufficient. If you notice BIND/PREPARE times starting to go horribly wrong then a REORG is probably worth it, and it may be time to check the amount of COLGROUP statistics you have!

The recommendation from IBM is, “before a Catalog Migration or once every couple of years, and do more REORG INDEX than REORG TS.”

I am now keeping an eagle eye on my Db2 Directory LOBs!

If you have any Directory/Catalog Hints & Tips I would love to hear from you.

TTFN

Roy Boxwell

Posted on

2023-06: IDUG 2023 North America – Roy review

Hi all! Now safely back from Philly and, as ever, I learned a lot there! Already looking forward to Praque and the EMEA IDUG this October.

As always, I have listed out all of the Db2 for z/OS presentations I could find and added a few review sentences to them. Any links herein require you have a password and userid at IDUG with the required rights to access the data. This means you must have been either an attendee or virtual attendee with full access – Just being a member of IDUG is *not* enough!

SP01 | Db2 for z/OS Update: The Latest From The Lab

Speakers: Akiko Hoshikawa, Haakon Roberts

A01 | Data Fabric in 60 mins for Db2 for z/OS DBAs!

Speakers: Cuneyt Goksu

Data Fabric is one of the trend topics in IT Industry as part of Digital Transformation. This session summarizes both architectural, use case and product level discussions in the context of IBM Z and Db2 for z/OS Eco system.

This contains some great info about secure ports, certificates and the use of system profiles for remote access.

A02 | Who does that? Using SQL Data Insights to spot unusual behavior

Speakers: Mike Behne

This session explores SQL Data Insights (SQLDI) capabilities, reporting on early efforts to apply SQLDI to learn more from available data.

Basically, saying what I found as well: Training is expensive and you gotta “Know your data” before you really start!

A03 | Db2 for z/OS Utilities – The Very Latest News

Speakers: Haakon Roberts

The Db2 Utilities team continues to deliver significant new function, availability and performance improvements after the GA of Db2 13. This session will cover the very latest developments and also look forward to some of what you can expect to see later in 2023.

As usual a great presentation with all the newest stuff you just need to know! This included a free 30 minute long power outage which didn’t fluster Haakon at all! However, it caused Chris Muncan to motor through his next presentation!!

A04 | Recovering to another subsystem with DSN1COPY

Speakers: Chris Muncan

Ever have a major production problem that you didn’t know about and found out a month later and need to restore the data but to not restore on top of production? We did and here’s how we did it!

World record speed speech about DSN1COPY – Quicker than SSDs!!! Due to a small mistake in the timing app Chris thought he had even less time than he already had… He told me the session would be re-recorded at a saner pace! Even so, there were glorious nuggets like the requirement to issue the ALTER xxx-xxx ADDVOLUMES( * , * ) which catches people out all the time these days!

A05 | Data Modernization: Embrace the Mesh!

Speakers: Greg DeBo

Data Mesh is the new hot term for Data, I’m gonna talk about how to integrate mainframe data into your Data Mesh.

This was all about moving or not moving data around. Especially interesting was all the IMS stuff! Still nailed to its perch!

A06 | Db2 for z/OS Performance Latest Updates

Speakers: Akiko Hoshikawa

The latest performance and capacity planning updates from Db2 for z/OS (both Db2 12 and Db2 13) as well as IBM zSystems updates that you could take advantages of. The session will explain the key items with the reference of instrumentation updates to evaluate the features.

Aikiko with the normal list of great and good things. Heads up for the CFLEVEL 25 level size change. You had *all* better check your CF Sizes!!!

A07 | Use profiles to monitor and control Db2 application context

Speakers: Maryela Weihrauch

With increased popularity of distributed applications, related Db2 system and application definitions were defined in distributed application servers. Sub-optimal definitions could impact on overall Db2 health. Db2 z/OS provides capabilities to create profiles to monitor and control various aspects of a Db2 specific system and application contexts in Db2 profile tables. Db2 13 extends the profile tables to new attributes for local and remote applications. Maryela will review existing profile capabilities and introduce Db2 13 extensions as well as discuss use case examples.

Reviewed all about system profiles and some use cases to stop DoS attacks – Good stuff!

A08 | Best Practices for Applying Db2 for z/OS Software Maintenance

Speakers: Robert Tilkes

Discuss best practices for Db2 for z/OS maintenance strategy, SMP/e environment configuration, patch management and deployment.

All about applying fixes, rsus and hipers to Db2 for z/OS and the sad fact that we are *all* behind here! Included some nice sample JCL at the end to check your own SMP/E system.

A10 | Db2 13 for z/OS install and migration using z/OSMF workflows

Speakers: Sueli Almeida

In this session we will demonstrate how you can exploit IBM Z/OSMF workflows to install and / or migrate a Db2 subsystem or members of a data sharing group. We will illustrate how the workflow artifacts are created. Next, we will show these artifacts are assembled into a workflow. Finally we will show how the execution of the workflow progression can be tracked or monitored.

A very brave idea, in my opinion, of automating Db2 upgrades and deployment using z/OSMF.

A11 | Migrating to Db2 13

Speakers: John Lyle

Presenting the Db2 13 migration process. Note: This was a top 10 presentation at IDUG EMEA. I’ve improved the content and added some new detail.

All about getting there, including the new -DIS GROUP DETAIL output to help you see “where you are”.

A12 | Copy up – Buttercup

Speakers: Chad Reiber

Presentation about Db2 z/OS image copies. The how’s and why’s image copies are important. What and when they should be taken.

Absolutely everything you were afraid to know, but asked anyway, about COPY!

A13 | Db2 Attachment Facilities – Advanced Topics

Speakers: Emil Kotrc

Db2 Attachment Facilities are the interfaces between the application programs and Db2 for z/OS. In this presentation we will go through the basics of the most common attachment facilities, we will show how and when to use them, and we will also cover some advanced topics such as security implications, thread reuse, connection switching.

A deep deep dive into Db2 attachment details! Contains even assembler examples…

A14 | Db2 Logging Basics & Exploitation Beyond Recovery

Speakers: Steen Rasmussen

This is a “beginner” session describing why the LOG is one of the crucial components of Db2 and cover some log basics of what the log contains as well as get an understanding of how the log can be exploited for other tasks.

Steen introduces the Db2 log and how you can use it (or better yet buy a log analysis tool and let it do the work!)

A15 | Database Administration Enhancements of Db2 13 for z/OS

Speakers: Robert Catterall

This session will focus on the Db2 13 enhancements that pertain to database administration: online conversion of PBG table spaces to PBR, online removal of active log data sets, profile table support for local-to-Db2 applications, instrumentation improvements, and more.

Robert ran through all of the goodies in Db2 13 that could impact DBAs, including the new insert logic for PBGs that had always annoyed me in the past!

A16 | Who’s afraid of DDF

Speakers: Toine Michielse

For those who are being confronted with DDF workload, this presentation will discuss pitfalls, new resources to monitor and tune and application changes to be considered.

Highlights of using, and getting better performance from, DDF to connect.

B01 | Db2 Analytics Accelerator – Newest Enhancements

Speakers: Eberhard Hechler

This presentation is discussing the newest functions and features of the Db2 Analytics Accelerator, such as enhancements of the IBM Integrated Synchronization (InSync) engine (e.g., ALTER TABLE ROTATE PARTITION support), query acceleration improvements (e.g., ability to add Db2 unique constraints on the Accelerator), collecting actual explain information, Db2 Analytics Accelerator on IBM zSystems enhancements (e.g., improved I/O performance and reduced CPU consumption), reducing overall trace collection time, new and enhanced stored procedures, and administration enhancements. The presentation ends with an outlook to future enhancements.

This was all about Accelerators, specifically 7.5.8, 7.5.9 & 7.5.10 in this case!

B02 | Back to basics – Real life battle experience

Speakers: Joe Huang

I am currently training 3 junior z/OS DBAs in our company on various DB2 topics. I like to combine 6 of the training material into one and make a 60-minute presentation for the beginner or semi-seasoned DBAs.

Excellent real world experiences including work files, and all the ZPARMs involved as well as unique rowid pitfalls.

B03 | DBA’s Epic Journey

Speakers: Leila Hosseini

There are lots of performance tuning hints that we are aware of. Maybe we read the IBM Manuals or IDUG resources that we can leverage as a possible solution to our issues. As a DBA all of us know lots of performance and tuning tips and tricks , but when Unexplainable performance degradation happens! What would be a DBA’s reactions? It is the Art of DBA to put together all his/her knowledge and observations , analyze the situation and Resolve the issue.

A DBA needs to find out answer for following questions:

1-what is the main cause of the issue?

2- how it could be resolved?

Real world SQL tuning experiences and problem solving. With four example super bad SQLs and how to fix them up!

B05 | Db2 13 Early Customer Experiences

Speakers: Anthony Ciabattoni

The presentation will concentrate on Db2 13 early customer experiences discussing what went well, what they liked and also the things they wish they knew and could have avoided.

A quick run though the pre-reqs of Db2 13 and then some of the highlights including correct amounts of copying to avoid ransomware style attacks.

B06 | Db2 Utilities in Practice

Speakers: Hendrik Mynhardt

This session will cover not just what is new, but also how to apply best practices for all your Db2 utilities in a real environment.

Lots of data about the new SYSUTILITIES table including reminding us to clean it up! Followed by a run-through of the big utilities and how they have been enhanced recently.

B07 | Db2 12+/13 for z/OS Database Design and Application Performance: Features and Usage

Speakers: Susan Lawson

With every new release and function level, (12+/13) of Db2 we look to see what features will allow us to improve the capabilities and performance of our existing applications as well as the availability of our data. We also have to plan to utilize new features in our development efforts.

Great to see Susan again as she is one of my favourite presenters. A ton of info in the presentation. Well worth a read afterwards!

B08 | Back-To-Basics: Table Space and Index Fundamentals

Speakers: Louise Comeaux

A review of the various types of tablespaces and the indexes that are defined to them.

A very nice run through all current DB, Tablespace, table, and Index types including PBR RPN, DPSIs etc. Note that LOB and XML were not covered as this was, after all, a back-to-basics presentation.

B10 | When Microseconds Matter

Speakers: Thomas Baumann

Imagine a well-tuned Db2 z/OS SQL workload where the most frequently executed queries use 50 microseconds CPU per query or even less. Is it worth further tuning? And what are the tuning techniques to be applied? This presentation starts at exactly that point and – without touching the SQL query text – demonstrates how another 10% of CPU resources were squeezed out of that workload. And we will also discuss at what point we can truly decide that a query runs at maximum speed and no further tuning is possible.

All about squeezing more juice out of that lemon! Great example methodology and example SQLs make it easy to start doing this all!

B11 | Db2 for z/OS 101: Buffer Pools and Group Buffer Pools

Speakers: Mark Rader

Are you new to Db2 for z/OS? Or new to Db2 for z/OS data sharing? Want a refresher on buffer pools? Buffer pools, and group buffer pools for data sharing, are key to supporting Db2 workloads. Come learn the basics for these important resources.

Great four-way presentation with really good visualization. Played to a packed house and was very well received. More presentations like this please!

B12 | Db2 for z/os System Profile, The New ZPARMs and More!

Speakers: Paul Bartak

A practical look at implementing Db2 system profile rules to customize your experience with Db2 for z/OS. I started presenting on Db2 System Profiles with Db2 10. This presentation will cover updates through Db2 13.

Another presentation talking all about system profiling. It is one of the most under-used fantastic features of Db2 so I fully understand this!

B13 | Large tables – Obstacles and Strategies to win!

Speakers: Scott Walker

Data is ever growing and challenges with large tables make our jobs more difficult. Success with these monsters is imperative. Everything matters with table design/maintenance. Perhaps you are stuck with an old design that is not prepared for influx of data or you’re building a new table and want to build something scalable and low maintenance. I will give you a few items to consider as well as pain points I’ve lived through. Additionally, this session will be interactive – audience participation will benefit the conversation.

An intro and exposé of all things HUGE in Db2! Tips and tricks included!

B14 | Monitoring your distributed workload for Db2 for z/OS

Speakers: Jørn Thyssen

Distributed workload is becoming more prevalent, and for many customers it is a significant part of the overall Db2 for z/OS workload. In this session we will explore the various options available with Db2 for z/OS to understand and monitor your distributed workload. Our focus is on the system side to help you protect your Db2 system.

This introduced all the new and varied ways of seeing from where remote SQL is coming from and a nice set of ways to check if your drivers are up to date.

B15 | Things About Db2 for zOS I Wish I’d Remember When….

Speakers: Michael Cotignola

This presentation will cover some of the more overlooked or forgotten options, commands, syntax that could prove to be invaluable in managing your Db2 environment. Intended target audience is for people new to Db2 for zOS, application developers who may benefit from knowing more about the internals of Db2, or people like myself who just can’t remember a command, syntax or option and need a refresher.

A nice stroll down the, sometimes unfamiliar, road of things we forget or use very rarely.

B16 | Db2 13 for z/OS Application Management Enhancements

Speakers: Tammie Dang

Application development and management are always important topics with Db2 for z/OS due to the complex process and volume of applications exposed on the platform. Certain legacy applications are difficult to change and all changes to applications typically require following a strategic process from development to test,before deploying to the production environment. In a cloud environment that hosts multi-tenancy, applications are typically different in characteristics. These applications can each access different database objects and have their own concurrency requirements and toleration.

You can now use Db2 13 to set application-granularity lock controls such as timeout interval and deadlock resolution priority to match the individual application’s need. And you can do this without the cost of changing the application’s source code. Db2 13 also introduces a mechanism to optimize for the success of DDL break-in without needing to duplicate versions of the application packages and without impacting non-dependent applications.

This presentation went through the new and changed options including system profiles again… You get the idea that people are trying to tell you something here??? Plus DDL break-in explained!

C13 | Db2 for z/OS availability, efficiency and application stability enhancements

Speakers: Frances Villafuerte

Db2 13 provides many new features to accommodate application workload growth and simplify processes for DBAs. This session gives you an overview of key features.

Started with a nice list of removed ZPARMs and which values they now have in perpetuity, then a list of changed ZPARM values so you can easily verify that you are not “living in the past” . My personal favourite is EDM_SKELETON_POOL from 51200 to 81920. I know of sites which still have 10240! Check out my blog about “Small ZPARM – Big effect!” https://www.segus.com/2017-04-db2-zparm-edmpool-size/  for details of what this ZPARM actually controls and enables! It is not really that clear from the docu at all! This was then followed up with all the problems with PBG spaces, including the horrible inserted empty partition problem,  and how some of these problems are solved in Db2 13. John Campbell stated before he retired “MAXPARTITIONS 1 DSSIZE 64GB no other setting is good!”. Then it continued into the PBG -> PBR Migration scenarios as IBM, at least long term, want us off PBGs completely!

E01 | Optimization 101. What makes Db2 Choose Certain Access Paths

Speakers: Tony Andrews

This is a great ‘Back to Basics’ presentation (and then some) for understanding the logic of the optimizer. The Db2 optimizer is a cost based optimizer estimating the cost of many possible access paths for an SQL query, ultimately choosing what it thinks to be the least expensive access path. But what determines the choices, and what makes one cheaper than another? Come learn the basics of the Db2 optimizer, and what you can do to help and influence its logic to the most efficient paths. Come learn the basics of performance tuning queries, programs, and applications.

Optimizer 101 introducing you to everything the optimizer uses to make its decision on access paths.

E02 | Db2 13 Application Development Topics

Speakers: Emil Kotrc

What is new in Db2 13 for application developers? Let’s explore these topics in this session. We will cover SQL related enhancements as well as performance improvements that application developers can benefit from.

This was a real potpourri of Db2 13 stuff! CD, Current Lock timeout, Profile tables (again!), SQL DI and APARs!

E03 | Taming Dynamic SQL with Db2 V12

Speakers: Steve Loesch

This presentation will contain an overview of the techniques to capture and observe performance of Dynamic SQL in Db2 V12 implemented at Navy Federal Credit Union. Navy Federal Credit Union has many mission critical applications using Dynamic SQL. Examples of Db2 features such as Dynamic SQL statement stabilization, creation of Dynamic SQL history, a cross reference of SYSDYNQRY tables and the DSN_STATEMENT_CACHE table, and a SQL statement that will show that a table is used in packages and/or SYSDYNQRY statements.

Was all about getting, explaining and tuning your dynamic SQL by looking at the use of stabilized queries.

E04 | Db2 Java High Performance Best Practices Volume X

Speakers: Dave Beulke

This presentation details Db2 and Java performance best practices and discusses how to optimize your processing to run 100x faster. Db2 design, partitioning strategies, coding best practices, java class frameworks and debugging/tracing practices will be presented that can immediately eliminate your bottlenecks and enhance performance. After this discussion you will be able to dramatically improve your Db2 access, Java runtimes, minimize CPU and quickly access/process billions of rows with the best performance possible.

I just loved the 13th and 14th slides… I am also not a fan of those “things”… If you want to tune Java on the Mainframe *this* is your best starting point!

E05 | Do your Db2z application developers like Python? Sure let’s build z applications using it!

Speakers: Sowmya Kameswaran

Python is one of the top programming languages in the world. It’s easy to learn; for DBAs similar to REXX; and has a robust set of libraries that enable delivering business value; specifically surrounding data rapidly and easily. This presentation will use a Python on z/OS with the python-ibm_db library; along with a few visualization libraries to provide some fun demonstrations that also show the power and ease of use Python.

The first 10 slides were just setting the ground for working with Python on z! Not really difficult, but different from what we as “Hostees” are used to methinks! However it contained some really cool stuff as well as calling Visual Explain!

E06 | Is it worth it to migrate CICS cobol app to Windows .net?

Speakers: Mateusz Ksiazek

The presentation will show the real production approach for migration from CICS Cobol application to Windows .net.

After seeing all the graphics, you have to really wonder: Was it all worth it? Naturally, it all looks more modern but you still have to pay the ferryman at the end of the day!

E08 | Db2 for z/OS and LUW Big Buttons for Application Performance

Speakers: Daniel L Luksetich

This is not A deep dive into application performance. It is the simple but huge right things to do to have a dramatic positive impact to application performance!

Dan gives his great hints and tips about general SQL performance here. Not doing the call, coding a JOIN, use of ARRAY types etc etc.

E10 | Db2 Developer’s Top Tens

Speakers: Tony Andrews

There are many areas of Db2 application development that developers, testers, business analysts, etc, should know about. This presentation lays out my top 10 for the areas that are so important in performance and developing a good application. Areas of importance being SQL tuning tips, programming tips, and of course performance and the Db2 optimizer.

This was another run through of everything optimizer and then lists of things to check and do or not do for SQL Tuning. Quite excellent!

E12 | Code Your Db2 Applications for Performance from the Start!

Speakers: Craig Mullins

Most developers do not seriously consider Db2 performance implications until it is too late. But there are best practices that can be used to build performance into your programs even as from the very beginning.

Craig gives his great explanations as to how to code for performance from the start.

E13 | Db2 SQL Performance for Application Developers

Speakers: David Morris

Db2 SQL Performance for Application Developers. Developers can learn Db2 SQL performance tricks and best practices. When working with DBAs, developers will write better performing SQL, know what an Explain plan is, optimize SQL queries.

A nice run through the do’s and don’ts of SQL for Application developers.

F01 | What Db2 can do; I can do too – first steps towards machine learning

Speaker: Toine Michielse

In this presentation I give an overview of software and ideas that can be used to get yourself started in exploiting both data and software in your day to day life.

This got all “snaky” when Anaconda and Python re-appeared. But it was really all about first steps in Machine Learning!

F02 | Database Trends 2023 – Things Are Changing and You Better Keep Up!

Speakers: Craig Mullins

What are the predominant trends in 2023 that impact data professionals and their usage of DBMSes.

With over 350 different DBMSs out there this was a great review of the state-of-the-art! Slide 15 is my fave!

F03 | Encrypting Db2 Connections with TLS – what a Db2 DBA should know

Speakers: Christoph Theisen

The presentation shows what is needed from a Db2 z/OS and Db2 LUW perspective to set up TLS encryption successfully. The main focus is on the Db2 Client side but we also cover the most important server-side topics.

This was all about TLS, certificates, Keyrings and Key stores! Fascinating stuff! Slide 53 point one was my fave because it’s so true … Please note that Christoph was a little bit confusing in his page numbering… The Slide 53 is really the PDF slide number *not* the “slide number” you see in his presentation…

F04 | Db2 SQL – go beyond the usual – My current TOP 40 SQL tips, tricks, and opinions

Speakers: Brian Laube

Modern SQL is a powerful tool on its own for the DBA and application developer. Keeping on top of modern SQL techniques and functionality lets us move beyond the usual comfortable SQL. The presentation will go over my top SQL tricks and tips for producing useful output and answer your questions about your data and environment. In addition, I will provide a list of definitions and opinions that are important to agree upon when discussing Db2 and SQL. Some are obvious and some are not. But it is good to agree on terms.

A great show of all the “modern” SQL you can use these days but mostly don’t…at the end was Brian’s Wish List for enhancements – Check ‘em out and go vote!!!

F06 | Declared Global Temporary Tables (DGTT) user stories

Speakers: Kurt Struyf

Declared Global Temporary Tables (DGTT) have been around for some time, this presentation will focus on best use cases from customers. We will address the different kind of Temporary Tables in Db2, together with their advantages and disadvantages. This presentation will show some performance use cases, where DGTTs, brought a big performance benefit to customers.

Great interaction here with IBM Development, where various members of the audience shouted out a wish list… Gotta see if IBM Development took enough notes!!! Also interesting, was the idea of setting WFDBSP to YES to help manage these beasts. I am not a fan of workfile separation but I can see here that there is a use case. Another good take-away was the easy ability to rewrite IN LIST with 4000 items (I have also seen CRAZY in-lists coming out of generated code…) to a DGTT and even get index access! Naturally EXPLAIN is really hard as they do not really “exist” but the presentation explains how you can do it!

F07 | IBM Champions: Building technical eminence through advocacy

Speakers: Libby Ingrassia

Learn why and how to build technical eminence through advocacy – and how that can lead you to the IBM Champions program.

As a fellow champion I can only fully agree here! You get more than you put in but you must put something in!!!

F10 | Eliminate Risk when Migrating from IMS to Db2

Speakers: Bill Bostridge

Businesses looking to modernize their IBM System Z platform by moving from IMS to Db2 need a rapid and efficient migration solution. They need to eliminate the traditional risks and costs associated with rewriting applications to support Db2.

Showed a very nice way of migrating your data to Db2 but *not* changing your current IMS applications. Nice indeed!

F11 | Db2 for z/OS – Keeping your remote access secure

Speakers: Gayathiri Chandran

This session will discuss establishing secure remote connections to Db2 for z/OS.

A great overview of AT-TLS and MFA when accessing from remote. Security is always worth reading up on! And also, you get yet another review of system Profiling.

F12 | COBOL abound

Speakers: Erik Weyler

If we put a little effort into creating easy to use tools, our developers can be so much more productive. But how are the tools created and where do they run? In this inspirational talk, examples of tools, techniques and environments will be discussed. We will take a journey from ISPF, to PC and VS Code, to zCX. We will learn a little about GnuCOBOL, Zowe, and how data in a relational database, regarding the use of a hierarchical database, can be visualized in a graph database. In a container. On the mainframe.

Crazy what you can do with COBOL these days! I learned a lot from this session and intend to use it the moment I get the chance! SonarQube is the starting point…

F15 | Playing (with) FETCH

Speakers: Chris Crone

This session will delve into the many ways to get data out of Db2. These vary from SELECT INTO, to SELECT FROM FINAL TABLE, to FETCH FOR :N ROWS, to RESULT SETS. Db2 has evolved over the years and there are many ways to get data from Db2 – this session will be both a primer and a review.

Chris telling us way more than I ever wanted to learn about FETCH in SQL!!!

PSP11 | It’s AI Jim, but Not as We Know It!

Speakers: Roy Boxwell

Ahhh! My good self waffling on about how much AI is not really actually Intelligent!

TTFN,

Roy Boxwell

Posted on

2023-05 ZPARMs never stop changing part II

This month, I want to go through some of the absolutely most important ZPARMs that control how your Db2 systems behave in a very significant manner. All of the following ZPARMs have a performance impact of some sort. We are always trying to squeeze the last drop of performance out of our Db2 sub-systems, aren’t we?

Db2 13 and Some Db2 12 Updates Ahead!

Since this Newsletter topic first came out, in March 2022, out of the ten ZPARMs listed *five* have got new defaults! I have highlighted all these changed defaults. I have also added three new “Usual Suspects” to the list of ZPARMs that must be checked…

Starting with the Easy Stuff…

CACHEDYN. YES/NO, default YES. Should always be set to YES – unless you do not care about saving dynamic SQL performance. Back a few decades ago, the recommendation was to have this set to NO as default! Hard to believe that these days, where most shops have 80% – 90% dynamic SQL during the day!

Now we Get to the Numerics!

OUTBUFF. 400 – 400,000, default 102,400. This is *extremely* important and you really should set it to the highest possible value you can afford in real memory! As a minimum, it should be 102,400 KB (100MB). This is the buffer that Db2 uses to write log records before they are “really” written to disk. The larger the buffer, the greater the chance that, in case of a ROLLBACK, the data required is in the buffer and not on disk.

Skeletons in the Closet?

EDM_SKELETON_POOL. 5,120 – 4,194,304, default 81,920. This is one of my personal favorites, (I wrote a newsletter solely on this a few years ago). I personally recommend at least 150,000 KB and actually even more if you can back it with real memory. Just like OUTBUFF, pour your memory in here but keep an eye on paging! If Db2 starts to page, you are in serious trouble! Raising this can really help with keeping your DSC in control.

DBDs are Getting Bigger…

EDMDBDC. 5,000 – 4,194,304, default 40,960. The DBD Cache is getting more and more important as, due to UTS usage, the size of DBDs is increasing all the time.

DSC is Always Too Small!

EDMSTMTC. 5,000 – 4,194,304, default 113,386. The EDM Statement Cache (really the Dynamic Statement Cache) is where Db2 keeps a copy of the prepared statements that have been executed. So when the exact same SQL statement with the exact same set of flags and qualifiers is executed, Db2 can avoid the full prepare and just re-execute the statement. This is basically a no-brainer and should be set to at least 122,880 KB. Even up to 2TB is perfectly OK. Remember: A read from here is *much* faster than a full prepare, so you get a very quick ROI and great value for the memory invested! Keep raising the value until your flushing rates for DSC drop down to just 100’s per hour, if you can! Remember to cross check with the EDM_SKELETON_POOL ZPARM as well. It always takes two to Tango…

How Many SQLs?

MAXKEEPD. 0 – 204,800, default 5,000. The Max Kept Dyn Stmts parameter is how many prepared SQLs to keep past commit or rollback. It should be set to a minimum of 8,000 or so. Raising this might well cause a large memory demand in the ssidDBM1 address space so care must be taken.

RIDs Keep Getting Longer…

MAXRBLK. 0, 128 – 2,000,000, default 1,000,000. RID POOL SIZE is the maximum amount of memory to be available for RID Block entries. It should be at least 1,000,000 and, if you can, push it to the maximum of 2,000,000. Unless you want to switch off all RID Block access plans, in which case you set it to zero – Obviously not really recommended!

Sorts Always Need More Space

MAXSORT_IN_MEMORY. 1000 to SRTPOOL. Default 2000. The maximum in-memory sort size is the largest available space to complete ORDER BY, GROUP BY or both SQL Clauses. Remember that this is per thread, so you must have enough memory for lots of these in parallel. The number should be between 1,000 and 2,000, but whatever value you choose, it must be less than or equal to the SRTPOOL size.

Sparse or Pair-wise Access?

MXDTCACH. 0 – 512, default 20. Max data caching is the maximum size of the sparse index or pair-wise join data cache in megabytes. If you do not use sparse index, pair-wise join, or you are not a data warehouse shop, then you can leave this at its default. Otherwise, set it to be 41 MB or higher. If it is a data warehouse subsystem, then you could set this as high as 512 MB. (This ZPARM replaced the short-lived SJMXPOOL, by the way.)

Sort Node Expansion

SRTPOOL. 240 – 128,000, default 20,000. SORT POOL SIZE is the available memory that is needed for the sort pool. IFCID 96 can really help you size this parameter. Remember that the number of sort nodes leapt up from 32,000 in Db2 11 to 512,000 nodes for non-parallelism sorts and 128,000 nodes for a sort within a parallel child task in Db2 12. This means raising this ZPARM can have an even greater positive effect than before.

The Three New Guys on the Block!

To the MAX!

DSMAX used to be around 20,000 and can now be between 1 – 400,000. Remember that you will never actually reach this maximum limit as it is 31-bit memory-constrained.

Thrashing Around…

NPGTHRSH. Valid values are 0 or 1 – 2147483647. Default up to Db2 11 was 0, from Db2 12 default is now 1. SAP systems use a default of 10. The big change here, was in Db2 12 when the change from “no statistics ever ran” of -1 forced the value to be the “optimizer default” of 501 instead of the real value -1. This is also why the default is now 1 ,so that this ZPARM has a normal use! Setting it to 0 means that the access path chosen will always only be cost based.

Lock ’em Up and Throw Away the Key!

NUMLKUS. 0 – 104857600, with a default of 20,000. Just be careful raising this value too high, as each lock will take 540 bytes of storage in the IRLM!

Your “Top Ten List” + Three

These thirteen ZPARMs really influence how your Db2 system works and so must always be checked and changed with great care and attention to detail. Always do a before and after appraisal to see whether or not changing them helped or hindered your system!

If you have any comments, or other ZPARMs you think are also important for performance, feel free to drop me a line!

IDUG 2023 NA

IDUG is nearly upon again. I will be there in Philadelphia at the SEGUS booth and doing a fair bit of moderating as well. Drop on by, have a chat and pick up some of our swag and join me at the “Roy reviews AI with our WorkloadExpert” PSP on Thursday for a chance to win some cool stuff.

Hope to see some of you there!

TTFN,

Roy Boxwell

Posted on

2023-04 AI Performance

Hi! Continuing on with my AI blog (last one. I promise!) I wish to delve into the innards of the USS part of the SQL Data Insights experience and show you what it all costs!

A Quick Review Perhaps?

Please check my older newsletters for everything about install etc. of SQL DI, and one important thing which is the latest Vector Prefetch APARs (also see my last newsletter for details). Now. I will be doing “before and after” performance reviews with this feature on and off.

Bad News First!

What I have found, is that when I take a 500,000 row table into SQL DI and choose 17 columns, it takes the *entire* machine as well as all local page datasets and I was forced to cancel it after five hours…

Looking in the Logs…

If you go trawling around your Unix Directories, you will trip over these paths:

/u/work/sqldi

Home is Where the Spark is!

This is “home” where all of the SQL DI stuff is “installed”, naturally your name might be different!

Under here is the next layer of interest to me for the Spark processing.

It is Magic!

/u/work/sqldi/spark – Now this is where Spark does “the magic” and actually computes all your vector table data. It runs in stages and the first is the Base10 (I guess numeric analysis) part. For my test data it looks like this:

Scroll down to the bottom:

So, this ran really quickly!

Internals…

Then it does a ton of internal stuff and it starts actually doing the learning, which is “progressed” in a file like this:

/u/work/sqldi/spark/worker/driver-20230315063441-0002/ibm_data2vec-15-03-2023_07:15:01.log

Just Sitting There, Typing REF and Pressing ENTER…

Of course your name will be different, but just sitting there in OMVS and using the REF command you will see this file grow in size every now and again. When it does, quickly Browse on in and you will see stuff like this:

ibm-data2Vec (1.1.0 for zOS) starting execution using file /var/sqldi/temp/training/DSNAIDB_AIDB_DAI
ibm-data2vec found the required library: libzaio.so. Proceeding with the training..                 
ibm-data2vec will use following mode: CBLAS                                                         
User has not provided training chunk size. Using 1 GB chunk size for reading training file.         
ibm-data2Vec is preallocating space for the model using user-provided value 1230314                 
ibm-data2Vec starting execution using file /var/sqldi/temp/training/DSNAIDB_AIDB_DAIN0610_IQATW001_1
83951683 ! 2023-03-15 07:17:27 ! Time elapsed learning vocab from train file = 145.91525s           
Processed 13103200 words in the training file. There are 1213852 unique words in the vocabulary: Pri
Model training code will generate vectors for row-identifier (pk_id) or user-specified primary keys 
83951683 ! 2023-03-15 07:17:27 ! Stage 1 completed. Time elapsed during file reading = 145.91643s   
Training the database embedding (db2Vec) model using 12 CPU thread(s)

Whole Machine Gone – Oh Oh!

Now, in my case, it just sat there for a while taking all paging, all frames, all ziip and cp cpu and then it wrote out:

Epoch 0 learning rate Alpha=0.024704 Training Progress=5.00%                                        
Epoch 0 learning rate Alpha=0.024404 Training Progress=10.00%                                       
Epoch 0 learning rate Alpha=0.024099 Training Progress=15.00%                                       
Epoch 0 learning rate Alpha=0.023791 Training Progress=20.00%                                       
Epoch 0 learning rate Alpha=0.023486 Training Progress=25.00%                                       
Epoch 0 learning rate Alpha=0.023182 Training Progress=30.00%                                       
Epoch 0 learning rate Alpha=0.022885 Training Progress=35.00%                                       
Epoch 0 learning rate Alpha=0.022582 Training Progress=40.00%                                       
Epoch 0 learning rate Alpha=0.022286 Training Progress=45.00%                                       
Epoch 0 learning rate Alpha=0.021980 Training Progress=50.00%                                       
Epoch 0 learning rate Alpha=0.021673 Training Progress=55.00%

That last line was written out at 12:42 and after starting at 07:17 you can see that I still had nearly a five hour wait ahead of me. Time to cancel and rethink this!

Restart!

Thankfully, on the GUI interface (where you cannot see this progress info, sadly!) the “Stop training” button worked after a while. If it does not respond then you can just issue the

S SQLDAPPS,OPTION='SQLDSTOP'

command to stop it. Then, once all stopped, and the cpus have cooled down a bit, you can select a smaller data set and retry learning!

Smaller is Sometimes Better!

And with 40.000 rows it is much faster:

50397300 ! 2023-03-15 12:17:16 ! Stage 1 completed. Time elapsed during file reading = 26.992490s 
Training the database embedding (db2Vec) model using 12 CPU thread(s)                             
Epoch 0 learning rate Alpha=0.024765 Training Progress=5.00%                                      
Epoch 0 learning rate Alpha=0.024539 Training Progress=10.00%                                     
Epoch 0 learning rate Alpha=0.024308 Training Progress=15.00%                                     
Epoch 0 learning rate Alpha=0.024073 Training Progress=20.00%                                     
Epoch 0 learning rate Alpha=0.023826 Training Progress=25.00%                                     
Epoch 0 learning rate Alpha=0.023591 Training Progress=30.00%                                     
Epoch 0 learning rate Alpha=0.023354 Training Progress=35.00%                                     
Epoch 0 learning rate Alpha=0.023115 Training Progress=40.00%                                     
Epoch 0 learning rate Alpha=0.022878 Training Progress=45.00%                                     
Epoch 0 learning rate Alpha=0.022637 Training Progress=50.00%                                     
Epoch 0 learning rate Alpha=0.022406 Training Progress=55.00%

Naturally, this is heavily dependent on the machine you have, the memory you have and the size of your local paging dataset.

EXPLAIN Yourself!

So now to do some EXPLAIN runs and then a quick comparison of the “double” AI Whammy that I have, quickly followed by the “New” PTF that, hopefully, sorts it all out.

Double Trouble?

You might have noticed that in my test SQLs I have to use the BiF AI twice. Once for the SELECT and once for the WHERE. This is because the use of the AI_VALUE column is not supported in the WHERE predicate.

Naturally, you can re-write the query to look like this:

SELECT * FROM                                      
(SELECT AI_SEMANTIC_CLUSTER( PROGRAM,              
                           'DSNTIAUL',             
                           'DSN§EP2L',             
                           'DSN§EP4L') AS AI_VALUE 
      ,A.WLX_TIMESTAMP                             
      ,A.STMT_ID                                   
      ,A.STMT_TIMESTAMP                            
      ,SUBSTR(A.PRIM_AUTHOR , 1 , 8) AS PRIM_AUTHOR
      ,SUBSTR(A.PROGRAM , 1 , 8) AS PROGRAM        
      ,SUBSTR(A.REF_TABLE , 1 , 18) AS REF_TABLE   
      ,A.EXECUTIONS                                
      ,A.GETP_OPERATIONS                           
      ,A.ELAPSE_TIME                               
      ,A.CPU_TIME                                  
      ,A.STMT_TEXT                                 
FROM DAIN0610.IQATW001 A                           
WHERE 1 = 1                                        
  AND A.PROGRAM NOT IN    ('DSNTIAUL',             
                           'DSN§EP2L',             
                           'DSN§EP4L')             
  AND A.STMT_ORIGIN = 'D'                          
  )                                                
WHERE AI_VALUE IS NOT NULL                         
ORDER BY 1 DESC -- SHOW BEST FIRST                 
--ORDER BY 1 -- SHOW WORST FIRST                   
FETCH FIRST 10 ROWS ONLY ;

Does My Work File Look Big to You?

The problem is that now you have a HUGE work file… In my tests it was always much quicker to code the AI BiF twice. After all, it is always “Your Mileage May Vary”, “The Cheque is in the post” or “It depends”, isn’t it?

AI Does Use the Optimizer!

EXPLAIN Output… The AI Does indeed get output by EXPLAIN (I was surprised about this to be honest!) for the following query:

SELECT AI_SEMANTIC_CLUSTER( PROGRAM,               
                           'DSNTIAUL',             
                           'DSN§EP2L',             
                           'DSN§EP4L') AS AI_VALUE 
      ,A.WLX_TIMESTAMP                             
      ,A.STMT_ID                                   
      ,A.STMT_TIMESTAMP                            
      ,SUBSTR(A.PRIM_AUTHOR , 1 , 8) AS PRIM_AUTHOR
      ,SUBSTR(A.PROGRAM , 1 , 8) AS PROGRAM        
      ,SUBSTR(A.REF_TABLE , 1 , 18) AS REF_TABLE   
      ,A.EXECUTIONS                                
      ,A.GETP_OPERATIONS                           
      ,A.ELAPSE_TIME                               
      ,A.CPU_TIME                                  
      ,A.STMT_TEXT                                 
FROM DAIN0610.IQATW001 A                           
WHERE 1 = 1                                        
  AND A.PROGRAM NOT IN    ('DSNTIAUL',             
                           'DSN§EP2L',             
                           'DSN§EP4L')             
  AND AI_SEMANTIC_CLUSTER( PROGRAM,                
                           'DSNTIAUL',             
                           'DSN§EP2L',             
                           'DSN§EP4L')             
      IS NOT NULL                                  
  AND A.STMT_ORIGIN = 'D'                          
ORDER BY 1 DESC -- SHOW BEST FIRST                 
--ORDER BY 1 -- SHOW WORST FIRST                   
FETCH FIRST 10 ROWS ONLY ;

The EXPLAIN output looks like:

Then it gets an interesting STAGE2 RANGE predicate!

which resolves into:

So here we see what the BiF is doing from the perspective of the Optimizer! If you run the nested table version of the query then this line does *not* appear at all!

Notice here that the RANGE is now a STAGE1!

Optimize This!

So IBM Db2 has incorporated it into the Optimizer which is a good thing. But please remember: your SQL can have local predicates that cut down the size of the work file and so evens out the access times… Basically, you must code both and test to see which of the solutions is better for typical usage (As always really…)

Time, Measure, Repeat

Ok, now just doing one execute of the double query requires 2.58 seconds of CPU and 15.35 seconds elapsed. The statement is *in* the DSC so prepare time can be ignored. Here you can see it has been executed twice so we have average values but I am using the CPU from the batch job as it is more precise.

Changing the query to now fetch back all rows instead of first ten requires 7.06 seconds of CPU and 48.78 seconds elapsed. But it returned over 200K rows!

While the query was running you can see the SQLD SQL DI in SDSF taking quite large chunks of zIIP time…

Now I will enable Vector Prefetch with a value of 10GB to see if it makes an impact for these queries. To do this you must update the ZPARM MXAIDTCACH and then enable the changed ZPARM.

That is Not What I was Expecting!

First query is now 2.56 CPU and 15.26 Elapsed. More like background noise than an improvement. And now with the FETCH FIRST removed 7.07 and 49.36 seconds. I guess my queries are not improved with Vector Prefetch!

Could be Me…

From the IBM Vector Prefetch docu:

With vector prefetch enabled, CPU performance for AI queries with AI function invocation on qualified rows improves particularly when the ratio of the cardinality of qualified rows to the total number of numeric vectors for the column is high.

https://community.ibm.com/community/user/datamanagement/blogs/neena-cherian/2023/03/07/accelerating-db2-ai-queries-with-the-new-vector-pr

Time to Join the Real World!

Now let’s try and see if I can discover something new in real data! Anything sensitive has been obfuscated!

SELECT AI_SIMILARITY( PROGRAM,                     
                     'IQADBACP') AS AI_VALUE       
      ,SUBSTR(A.PRIM_AUTHOR , 1 , 8) AS PRIM_AUTHOR
      ,SUBSTR(A.PROGRAM , 1 , 8) AS PROGRAM        
      ,SUBSTR(A.REF_TABLE , 1 , 18) AS REF_TABLE   
      ,A.WLX_TIMESTAMP                             
      ,A.STMT_ID                                   
      ,A.STMT_TIMESTAMP            
      ,A.EXECUTIONS                                
      ,A.GETP_OPERATIONS                           
      ,A.ELAPSE_TIME                               
      ,A.CPU_TIME                                  
      ,A.STMT_TEXT                                 
FROM DAIN0610.IQATW001 A                           
WHERE 1 = 1                                        
  AND NOT A.PROGRAM     = 'IQADBACP'               
  AND     AI_SIMILARITY  ( PROGRAM,                
                          'IQADBACP')              
       IS NOT NULL                                 
  AND     A.STMT_ORIGIN = 'D'                      
ORDER BY 1 DESC -- SHOW BEST FIRST                 
--ORDER BY 1 -- SHOW WORST FIRST                   
FETCH FIRST 10 ROWS ONLY;

This is similar to my test from last month but now on real data. Note that I have added a predicate A.STMT_ORIGIN = ‘D’ as I only want Dynamic SQL programs:

Dynamic Hits?

Here you can see that it has found a variety of programs that also do dynamic SQL but I also “helped” it by only asking for dynamic SQL. So now once again but this time without the predicate A.STMT_ORIGIN = ‘D’:

Success!

It has found nearly all from the first list but also different ones, crucially it has *not* found any Static SQL!

So, that’s enough of AI for the next few months for me. However, if you have any questions or ideas that I could try out feel free to email!

TTFN,

Roy Boxwell