Roy Boxwell, Autor bei SOFTWARE ENGINEERING

14. November 201914. Januar 2021

2019-11 Db2 Existence checks: SELECT for DUMMIES

Every now and again, the DBAs of the world meet with the developers of the world and try and bang some heads together…I already wrote a newsletter all about existence checks which has changed and evolved over the years and now I wish to investigate another age old problem: “Is Db2 there?”

Newsletter 2012-10: Existence check SQL – Through the ages

Reader test results: see the end of this newsletter for SELECT queries tested by one of our readers on Db2 10 and DB2 11.

Why?

First up, is “Why?” – Why would you want to know that?

Well think about our wonderful agile world these days. The JAVA developers do not even know what Db2 *is* and so when they write/script/generate/get generated for them SQL (Delete whatever is not appropriate), they have no idea *where* the code will run. To be fair, they should not really need to…the idea these days is that your back end can be anywhere and on any hardware platform.

So, wouldn’t it be cool to be able to tell the application that the required database is “not currently available” – This is when the “Is Db2 there?” SQL question, and all of its problems, was born.

Problems

How to do this ?
How often to do this ?
Do you need to do this ?
Haven’t you got better things to do than this?

1 – How to do this ?

The first problem was solved when a developer wrote this SQL:

SELECT * FROM SYSIBM.SYSDUMMY1

PREPAREd, OPENed, FETCHed it and CLOSEd it. Now remember, with dynamic SQL, everything must be in cursors so you really did have to define a cursor, open the cursor, fetch the cursor and, if you are being a good coder, close the cursor. If all of this was successful then you “knew” that Db2 on z/OS was up and running and available for all your further SQL requests.

This was ok when there was not so much dynamic SQL going on (Before Db2 V8), but naturally it started getting a bit out of hand very quickly.

The SQL then changed a little bit to be:

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1

The logic here was it could only return one row/value back and so made the network traffic less. Naturally Db2 went off and counted the single row in that table using a tablespace scan *every* time…

This slowly became a small, but very noticeable, overhead with 1,000,000’s of getpages against the smallest Db2 Catalog table ever invented…

Now Terry Purcell and the optimizer crew came up with the PRUNED access plan in Db2 9:

PRUNED

Db2 does not generate an access path for the query because the query is guaranteed to qualify zero rows, such as the case of an always-false WHERE clause.

For example:

WHERE 0 = 1

This happens when a “leg” of an SQL query cannot ever be true. In this case, the complete leg of the SQL query is thrown away. With this advance in optimizer logic the predicate:

WHERE 1 = 0

Actually became really useful, as it meant that the *entire* SQL was only run through the optimizer and the SQLCODE was set to +100 if doing a FETCH with the * style SQL or the value of your host variable was set to zero if using the COUNT(*) style. This meant that the getpages for SYSIBM.SYSDUMMY1 dropped down to zero – which is a very very good thing

IBM have been improving what PRUNED can do ever since, and Terry Purcell wrote :

“Another benefit of column pruning is that it can provide extra table pruning opportunities.
In Db2 10, an enhancement was delivered to prune outer join tables if no columns were required from that table and it was guaranteed that the table would not introduce duplicates. In Db2 12, this pruning enhancement is extended to outer joins to views/table expressions that are guaranteed not to return duplicates and no columns are required from the view/table expression.”

2 – How often to do this?

How long is a piece of string?

The problem is that when you do the “Is Db2 there?” test at the start of a long conversational transaction, it could well be that Db2 has disappeared at the end…

3 – Do you need to do this?

Again, an age-old question – Most JAVA frameworks do this (Like they do embedded COMMIT and ROLLBACK!) and so the simple answer is yes!

4 – Haven’t you got better things to do than this?

Of course you do! We all do, but these little changes can actually have a major impact on the overall machine throughput!

Measured Response

Here are some real word results of tests with dynamic SQL with/without the 1 = 0 and running a million times each. I have tried doing a variety of flavors, like Heinz…

First up are the SELECT * style selects:

SEL-867 SELECT * FROM SYSIBM.SYSDUMMY1 
SEL-868 SELECT * FROM SYSIBM.SYSDUMMY1
     WHERE 1 = 0     
SEL-869 SELECT * FROM SYSIBM.SYSDUMMY1
     WHERE 1 = 0
     WITH UR 
SEL-870 SELECT * FROM SYSIBM.SYSDUMMY1 
     WHERE 1 = 0 
     WITH UR 
FOR FETCH ONLY 
SEL-871 SELECT * FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                 
    WITH CS                      
SEL-872 SELECT * FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                 
    WITH CS                      
FOR FETCH ONLY               
SEL-873 SELECT * FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                  
    WITH RR                      
SEL-874 SELECT * FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                  
    WITH RR                      
FOR FETCH ONLY

Now the SELECT COUNT(*) style selects:

SEL-875 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1
SEL-876 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1
     WHERE 1 = 0     
SEL-877 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1
     WHERE 1 = 0
     WITH UR 
SEL-878 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 
     WHERE 1 = 0 
     WITH UR 
FOR FETCH ONLY 
SEL-879 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                 
    WITH CS                      
SEL-880 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                 
    WITH CS                      
FOR FETCH ONLY               
SEL-881 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                  
    WITH RR                      
SEL-882 SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 
    WHERE 1 = 0                  
    WITH RR                      
FOR FETCH ONLY

The results were:

                            Rows   Rows  Index  Tblsp   
    StmtID  Execs  Getpg    exam   proc  Scans  Scans   
----------  -----  -----   -----  -----  -----  -----   
       867  1000K     2M   1000K  1000K      0  1000K   
       868  1000K      0       0      0      0      0   
       869  1000K      0       0      0      0      0   
       870  1000K      0       0      0      0      0   
       871  1000K      0       0      0      0      0   
       872  1000K      0       0      0      0      0   
       873  1000K      0       0      0      0      0   
       874  1000K      0       0      0      0      0 

       875  1000K     2M   1000K  1000K      0  1000K   
       876  1000K      0       0  1000K      0      0   
       877  1000K      0       0  1000K      0      0   
       878  1000K      0       0  1000K      0      0   
       879  1000K      0       0  1000K      0      0   
       880  1000K      0       0  1000K      0      0   
       881  1000K      0       0  1000K      0      0   
       882  1000K      0       0  1000K      0      0

   StmtID   Tot. CPU  Avg. CPU   Tot. Elap  Avg. Elap  
----------  SS.mmmmmm  SS.mmmmmm  SS.mmmmmm  SS.mmmmmm  
       867   2.481068   0.000002  16.869174   0.000017  
       868   1.291817   0.000001   8.119036   0.000008  
       869   1.331707   0.000001   6.220308   0.000006  
       870   1.330709   0.000001   9.460538   0.000009  
       871   1.306633   0.000001   8.984930   0.000009  
       872   1.326517   0.000001   9.181043   0.000009  
       873   1.324213   0.000001   7.392330   0.000007  
       874   1.322115   0.000001   7.694403   0.000008
 
       875   3.066573   0.000003  18.824193   0.000019  
       876   1.467454   0.000001   6.168161   0.000006  
       877   1.478887   0.000001   7.714925   0.000008  
       878   1.480241   0.000001   9.903416   0.000010  
       879   1.477947   0.000001   9.965071   0.000010  
       880   1.469971   0.000001   9.195251   0.000009  
       881   1.467287   0.000001   8.687336   0.000009  
       882   1.487021   0.000001  11.742945   0.000012

The SELECT * with WHERE 1 = 0 WITH UR is actually the winner!

Remember – You never stop learning!

As always, I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

Results

Reader test results from Isaac Yassin:

Measured for dynamic SQL (SPUFI in UR mode – DSNESPUR), using Omegamon application trace. Same machine, same LPAR, same subsystem, measuring the 3rd consecutive execution of each SQL. Both do a „prepare/open/fetch/close“ according to trace.

select * from sysibm.sysdummy1 vs. select * from
sysibm.sysdummy1 where 0=1

The differences are:

For Db2 10

Without „where 0=1“

 PREPARE – InDB2 Time = 0.00048 InDB2 CPU = 0.00047
 FETCH - 2 fetches, InDB2 time = 0.00131 InDB2 CPU= 0.00116 (avg. 0.00058)
 LOCKs - Type = MDEL , Level = S , DB=DSNDB06 , PS=SYSEBCDC , Count = 1
 Sequential scan of data page = 2 pages scanned, 1 row qualified

With „where 0=1“

PREPARE - InDB2 Time = 0.00077 InDB2 CPU = 0.00049
FETCH - 1 fetch, InDB2 time = 0.00008 , InDB2 CPU = 0.00008
Locks - none
NO sequential scan

For Db2 11

Without „where 0=1“

PREPARE - InDB2 Time = 0.00045 InDB2 CPU = 0.00045
FETCH - 2 fetches, InDB2 time = 0.00121 InDB2 CPU= 0.00120 (avg. 0.00060)
LOCKs - Type = MDEL , Level = S , DB=DSNDB06 , PS=SYSEBCDC , Count = 1 
Sequential scan of data page = 2 pages scanned, 1 row qualified

With „where 0=1“

PREPARE - InDB2 Time = 0.00044 InDB2 CPU = 0.00044
FETCH - 1 fetch, InDB2 time = 0.00007 , InDB2 CPU = 0.00007
Locks - none
NO sequential scan

select 1 from sysibm.sysdummy1 vs. select 1 from sysibm.sysdummy1 where 0=1

The differences are:

For Db2 10

Without „where 0=1“

PREPARE - InDB2 Time = 0.00057 InDB2 CPU = 0.00054
FETCH - 2 fetches, InDB2 time = 0.00015 InDB2 CPU= 0.00015 (avg. 0.00007)
LOCKs - None
No Sequential scan of data page

With „where 0=1“

PREPARE - InDB2 Time = 0.00060 InDB2 CPU = 0.00034
FETCH - 1 fetch, InDB2 time = 0.00006 , InDB2 CPU = 0.00006
Locks - none
NO sequential scan

For Db2 11

Without „where 0=1“

PREPARE - InDB2 Time = 0.00047 InDB2 CPU = 0.00047
FETCH - 2 fetches, InDB2 time = 0.00087 InDB2 CPU= 0.00082 (avg. 0.00041)
LOCKs - none
NO Sequential scan of data pages

With „where 0=1“

PREPARE - InDB2 Time = 0.00044 InDB2 CPU = 0.00044
FETCH - 1 fetch, InDB2 time = 0.00007 , InDB2 CPU = 0.00007
Locks - none
NO sequential scan of data pages

Bottom line

„select 1“ works better than „select *“ as you don’t really scan the data and not taking locks, using „where 0=1“ eliminates 1 fetch and the locks & scan as well.

Isaac Yassin

28. Oktober 201914. Januar 2021

2019-10 DECFLOAT examples: Fun with Numbers

This time I have a nice little real world story for you. Pull up your good old copy of the Db2 9 SQL Guide and I will begin…

What is a number?

Numbers used to be pretty straightforward and easy to spot.

If it was a numeric between -32768 and +32767 it was a SMALLINT or Small Integer, or even a half word for the greying people reading this.

If it was a numeric between -2147483648 and +2147483647 it was an INTEGER or full word. If it was a numeric between 1 – 10³¹ and 10³¹ – 1 it was a DECIMAL or packed, or comp-3

Along came BIGINT

After a few years we even got BIGINT (Between -9223372036854775808 and +9223372036854775807), which is much better than DECIMAL if you ask me and is also a double word. In all of these cases, you knew with 100% certainty that you were dealing with a number – Not a column name or anything else!

Then the brown stuff hit the fan… DECFLOAT.

DECFLOAT Arrives

In Db2 9 they introduced the horror that would become DECFLOAT to the Db2 z/OS world. It arrived with little fanfare and a massive amount of ifs and buts, but someone somewhere wanted it…

The problem?

The horrible problem that DECFLOAT dragged in to the party, was the fact that it could contain characters like :

SNaN or
NaN or
INF or
Infinity.

Yep, these are all valid DECFLOAT number definitions.

The law of numeric comparisons

The law of comparisons now looks like this:

-NaN < -SNaN < -INF < -0 < 0 < INF < SNaN < NaN

Clear on that?

NaN stands for „Not a Number“ with the „quiet“ attribute.
SNaN stands for “Signaling Not a Number” which “signals” when it is used (Imagine a Facebook wave here!) and
INF is simply infinity.

What problems does this cause?

Well, let’s imagine you have 100’s of tables in a huge Db2 data warehouse, all designed in 1983 with a column called NAN that stands for “National Advertising Notice” – NAN is a good size reduction, still clear about its contents and everything is fine right up until Db2 9 comes along!

Upper and Lower trouble

Now you might think, it is not that bad, IBM are using SNaN and NaN right? Column and Table names have always been upper and lower case sensitive so NAN is not the same as NaN right? Wrong!

The use of inf INFINITY SNAN NAN nAn snAN are all “recognized” and accepted as valid DECFLOAT numbers.

What can you do?

Delimit the column name with double apostrophes is the only correct way. “NAN” for example. Now you go and change 4000 COBOL programs etc.

If you know what you are doing…

Naturally if you are coding ok, you can happily “code” around the problem. Here is some example SQL to show you where you can get badly caught, and to introduce those of you who don’t know about DECFLOAT the joys you can have:

 CREATE TABLE BOXWELL.TESTCASE (NAN  CHAR(10) NOT NULL         
                               ,IBAN CHAR(32) NOT NULL)        
 ;                                                             
 INSERT INTO BOXWELL.TESTCASE VALUES ('TESTNAN' , 'TESTIBAN')
 ;
 COMMIT
 ;

Let’s do some SELECTs

First up, a normal select against the test table:

 SELECT  NAN                                                 
 FROM BOXWELL.TESTCASE                                       
 ;                                                           
 ---------+---------+---------+---------+---------+---------+
 NAN                                                         
 ---------+---------+---------+---------+---------+---------+
 TESTNAN                                                     
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Correct! Hoorah!

 ---------+---------+---------+---------+---------+---------+
 SELECT "NAN"                                                
 FROM BOXWELL.TESTCASE                                       
 ;                                                           
 ---------+---------+---------+---------+---------+---------+
 NAN                                                         
 ---------+---------+---------+---------+---------+---------+
 TESTNAN                                                     
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Also correct! Delimiters work as planned!

Now the really evil one:

 SELECT COUNT(*)                                                         
 FROM BOXWELL.TESTCASE                                                   
 WHERE IBAN IN (SELECT NAN                                               
                FROM SYSIBM.SYSDUMMY1)                                   
 ;                                                                       
 ---------+---------+---------+---------+---------+---------+---------+-
 DSNT404I SQLCODE = 12, WARNING:  THE UNQUALIFIED COLUMN NAME NAN WAS     
          INTERPRETED AS A CORRELATED REFERENCE                           
 DSNT418I SQLSTATE   = 01545 SQLSTATE RETURN CODE                         
 DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR             
 DSNT416I SQLERRD    = 0 0  1  1143510784  0  0 SQL DIAGNOSTIC INFORMATION
 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000001'  X'44289700' 
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION             
                                                                          
 ---------+---------+---------+---------+---------+---------+---------+-
           0                                                              
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1

You see what has happened here?

The SQL has “incorrectly” used NAN but gets only a +12 Warning from the parser. It then happily runs on and gives a result. Is this what should happen? I don’t think so!

It gets worse

Now try these queries:

 SELECT NAN                                                  
 FROM SYSIBM.SYSDUMMY1                                               ;                                                           
 ---------+---------+---------+---------+---------+---------+
                                                             
 ---------+---------+---------+---------+---------+---------+
 +NAN                                                        
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Imagine having typos in your SELECT lines…

 SELECT sNAN                                          
 FROM BOXWELL.TESTCASE                                
 ;                                                    
 ---------+---------+---------+---------+---------+---
                                                      
 ---------+---------+---------+---------+---------+---
 +SNAN                                                
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Cool huh?

What can you do?

Monitor SQLCODE +12 – These should *never* really happen in production! How? Well you can use our WorkLoadExpert to discover these problems in two distinct ways.

Capturing the SQL workload “sees” the SQLCODEs when processed
If you have the SQLCODE Use Case then you get all SQLCODEs that are output by Db2

Doing it right

The recommendation is actually to CAST any of these literal values like CAST(‘snan’ as DECFLOAT) which removes *any* chance of getting it “confused” with a column name or anything else!

 SELECT CAST('NAN' AS DECFLOAT)            
 FROM SYSIBM.SYSDUMMY1                     
 ;                                         
 ---------+---------+---------+---------+--
                                           
 ---------+---------+---------+---------+--
 +NAN                                      
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Where else?

Now that these beasts are also allowed in indexes you must really check all of their usage.

Have fun!

As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

12. September 201914. Januar 2021

2019-09 A DBA has got to know his limitations

Sorry, but I could not resist the Dirty Harry quote there…and yes I know it really should be “his/her” but that would not then be a quote, so please forgive me!

I have spoken/blogged about limitations in Db2 a few times over the years and we have a nice little tool called Space AssuranceExpert (SAX) that stops a whole bunch of nasty events (read -904’s) at sites around the world.

It grows and grows

SAX started out “just” monitoring the size and number of Extents/Linear Datasets that Db2 was allocating. It then either did a dynamic ALTER of the SECQTY to allow the dataset to get to its physical maximum size before running out of extents, or it warned people that the number of physical datasets was running out.

First enhancements

– SMS Storage groups Monitoring

We pretty quickly enhanced it to then also monitor SMS Storage groups as when your copypool runs out of space it can be a tad embarrassing!

At the same time, we changed the logic to work with the -1 -1 style allocations when IBM brought out their sliding scale logic.

– Universal support

When PBG Tablespaces came out, we again enhanced the product due to the special processing limits of these Universal Tablespaces.

Think MAXPARTITIONS here!

– Db2 12

Along came Db2 12 with its new RPN format and the ability to have a different DSSIZE for each partition, and each index got its own DSSIZE.

– Sequences and Identities

Then we were asked if we could also monitor Sequences and Identities that are defined as non-cyclic. A lot of these beasts are defined so that they just “hit the buffers” at a certain number. So we added that to the system as well.

– Numeric Primary Key support

Next came a request to monitor Numeric Primary Key columns. These were actually sequences before sequences were even invented! You have, typically, a SMALLINT field which keeps being incremented – Fine for the first 32,767 but after that?

No time to rest on our Laurels!

Are we now finished? No! There is *always* more to do!

What if you are nearing the column limit, or worse yet, the Index column limit?
What if the size of your index is getting dangerously close to the absolute physical maximum?
Talking about absolute physical maximums – What if you are running out of DBIDs?
Or, even worse, running out of OBIDs within a database – Do you even check these?
What happens when you try to ALTER it? Or create that new index?
Or even just a view with an INSTEAD of function?

BOOM! Is what happens!

SAX saves the day again!

We have enhanced SAX yet again, so that

you can now run a batch job to review all of these limits.

This gives you plenty of time to take corrective action before the bridges are burning…

What are the limits?

In the Db2 SQL Reference Guide in the Appendix “Limits in Db2 for z/OS” it lists them out, but here are the important ones for you all:

750 Columns in a table.

Actually it can be less, depending on the complexity of any VIEWs, but to keep it simple this value is used as the upper limit.

64 Columns in an index. This includes INCLUDE columns.

For a partitioning index (That is an “old style” index that is actually used to partition the data) you get a maximum size for PADDED indexes of 255 – n bytes and, if NOT PADDED, then 255 – n – 2m – 2d bytes.

For any other indexes you get a maximum size for PADDED indexes of 2000 – n bytes and, if NOT PADDED, then 2000 – n – 2m – 2d bytes.

Where:

n is the number of columns which are NULLable
m is the number of varying length columns
d is the number of DECFLOAT columns

An FTB (News from the Labs 2019-07) is only possible for indexes that are 64 bytes or less. If you ALTER add a one byte column to a 64 byte index then you have broken this limit and that index is no longer eligible for FTB processing.

65,217 Databases may be defined. This includes any widow databases of course (Typically DSNnnnnn style empty databases where the implicitly created tablespace has been dropped when the table was dropped, but the database was “forgotten”).

32,767 OBIDs within a single database. Remember, that the number of objects (OBIDs) within a database is not a simple 1:1 relationship. Each tablespace, index or referential relationship takes two, whereas each table, check constraint, aux for LOB, XML for XML, trigger or view with INSTEAD OF takes one.

Stay up to date

If Db2 development bring any new ones out, think Agile here, then our SAX will be enhanced quickly to check for it!

What is the plan?

Simply schedule the SAX limit checker to run once a week or so.

It will report all its findings and, if required,
issue WTOs to alert you to any dangers before hitting any of these limits.

And you?

What do you do at your shop now? Do you monitor these hard limits? Does anyone care?

Go ahead, make my day and install a SAX trial!

For a SAX trial please email to : techsupport@seg.de

Consult our Space AssuranceExpert (SAX) page

As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

20. August 201914. Januar 2021

2019-08 FTB (Fast Traversal Block): Just another TLA?

So we get a new TLA (Three Letter Abbreviation) to try and wrap our heads around in the busy busy world of mainframe databases. This time it comes with a twist, as FTB is actually not “Feed The Beast” sadly, but “Fast Traversal Block”.

In reality, it is referred to everywhere as:

FIT (Fast Index Traversal)

All clear on this and still with me? Good then I may begin!

The problem with modern indexes

The core problem these days is time… the time it takes to go through the leaf pages down to the actual data page is just too long. The vast amount of data we have forces upon the index a large number of layers which, in turn, forces a lot of non-leaf page accesses.

Index structure

We all know how indexes look in Db2, but remember they all have a root page (which is always technically speaking a non-leaf page) which points, normally, to non-leaf pages which, in turn, point to either more non-leaf pages – so you are getting extra levels in the index here – and finally they point to a leaf page which actually contains pairs of keys and RIDs so that Db2 can get the data from the table page.

Which level are you?

One of the quirks of Db2, is that the leaf pages are called Level 0 and then the non-leaf pages directly above them are Level 1. The root page above these is then Level 2 and so you have a “two level” index.

So how many fetches for a random data access?

The first thing Db2 does is read the root page, looking at the root page it sees which non-leaf page must be fetched and it repeats this until it lands on the leaf page and then it fetches the data. So in my two level index it takes four fetches to get to the data. Now imagine you have a seven level index? Yep you end up fetching a ton of non-leafs!

The solution? FTB!

In Db2 12, IBM introduced the Fast Traversal Block to keep a copy of the non-leaf data in a new area, separate from the buffer pools.

The major reasons for the expected performance boost, are the facts that the structure is L2 cache-aware and each page is equal in size to one cache line (256 bytes), and I/O is massively reduced for the “other” non-leaf pages.

One size fits all?

The size of the FTB is determined by the new ZPARM INDEX_MEMORY_CONTROL with valid values AUTO, DISABLE or 10 – 200,000 MBs. By default AUTO is on. With this value, the size of the FTB is either 20% the size of all available buffer pools or 10MB, whichever is the highest.

The devil is in the detail

To actually start working with FTBs, you have to wait until the FTB Daemon has found an eligible index. From all of your indexes there will be quite a few candidate indexes. These must follow the following rules:

It cannot be longer than 64 bytes
It cannot be versioned (So the OLDEST_VERSION and CURRENT_VERSION must be the same!)
It cannot contain a TIMESTAMP column with TIMEZONE
It cannot have more than 2,000,000 leaf pages

Once through that selection list, the Daemon starts seeing what is happening to this index, is it a random select? That’s is a good thing! Is it an index only access? That’s even better! A split! Oh dear, that’s very bad…from all this, every two minutes, the daemon decides whether or not to use FTB for this index.

Control is in your hands

This is all well and good, but there are a lot of people who like to see *exactly* what is going on and where! So IBM introduced a new command:

-DIS STATS(IMU)

From this you get to see which indexes are being processed and how much space they are using. The Daemon also spits out console messages so you can see what happened and why. Further there is a new Catalog table SYSIBM.SYSINDEXCONTROL where you can micro-manage exactly which indexes to attempt to force into FTB and which ones to exclude. I would try and avoid this option and just let Db2 do the business!

Gotcha’s?

Always! FTB is limited to 10,000 per member which is probably ok! When a MASS DELETE (or TRUNCATE TABLE) happens then the FTB is removed for the related indexes and, even more importantly, is to keep up with current Db2 maintenance! As always with new things, it takes a while to get the birthing pains out of the way!

INCLUDE can kill you!

Finally, remember that the length cannot be greater than 64 bytes. So if you decide to add that one byte character column to the index to get Index Only access, you might then push the index over the edge and up to 65 bytes…

Before ALTERing indexes always check if they are in the FTB (or were!) and check if your alteration will disallow the FTB!

A little SQL to show you what you have

WITH INPUT (NLEVELS, LENGTH, INDEX_NAME) AS                            
 (SELECT  COALESCE(E.NLEVELS , A.NLEVELS )  -- FIRST RTS THEN INDEXES   
       , SUM(CASE D.COLTYPE                                             
             WHEN 'DECIMAL ' THEN                                       
 -- IF , SEPERATOR           SMALLINT( CEILING(( D.LENGTH + 1,0 ) / 2 ))
                             SMALLINT( CEILING(( D.LENGTH + 1.0 ) / 2 ))
             WHEN 'GRAPHIC'  THEN D.LENGTH * 2                          
             WHEN 'VARG'     THEN D.LENGTH * 2                          
             WHEN 'LONGVARG' THEN D.LENGTH * 2                          
             ELSE D.LENGTH                                              
             END)                                                       
       + SUM(CASE A.PADDED                                              
             WHEN 'Y' THEN 0                                            
             ELSE                                                       
               CASE D.COLTYPE                                           
               WHEN 'VARG'     THEN 2                                   
               WHEN 'LONGVARG' THEN 2                                   
               WHEN 'VARCHAR'  THEN 2                                   
               WHEN 'LONGVAR'  THEN 2                                   
               WHEN 'VARBIN'   THEN 2                                   
               WHEN 'DECFLOAT' THEN 2                                   
               ELSE 0                                                   
               END                                                      
             END)                                                       
       + SUM(CASE D.NULLS                                               
             WHEN 'Y' THEN 1                                            
             ELSE 0    
                END) AS LENGTH  
       , STRIP(A.NAME) CONCAT '.' CONCAT STRIP(A.CREATOR) AS INDEX 
 FROM SYSIBM.SYSINDEXES        A                                       
     ,SYSIBM.SYSKEYS            C                                       
     ,SYSIBM.SYSCOLUMNS         D                                       
     ,SYSIBM.SYSINDEXSPACESTATS E                                       
 WHERE A.UNIQUERULE     <> 'D'              -- NOT DUPLICATE            
   AND D.COLTYPE        <> 'TIMESTZ'        -- NOT TIMEZONE             
   AND A.OLDEST_VERSION = A.CURRENT_VERSION -- NOT VERSIONED            
   AND A.TBNAME         = D.TBNAME                                      
   AND A.TBCREATOR      = D.TBCREATOR                                   
   AND A.NAME           = C.IXNAME                                      
   AND A.CREATOR        = C.IXCREATOR                                   
   AND A.NAME           = E.NAME                                        
   AND A.CREATOR        = E.CREATOR                                     
   AND C.COLNAME        = D.NAME                                        
 GROUP BY A.NAME, A.CREATOR , E.NLEVELS, A.NLEVELS)                     
 SELECT NLEVELS, LENGTH , INDEX_NAME                                    
 FROM INPUT                                                             
 WHERE LENGTH <= 64   
 ORDER BY NLEVELS DESC
 FOR FETCH ONLY       
 WITH UR              
 ;

I have limited it to just show you just the eligible unique, non timezone, non-versioned indexes with a length of 64 or less of course!

Blog time

Here’s an excellent write up from John Campbell with even more technical details:

https://www.ibm.com/developerworks/community/blogs/897a7c98-57af-4523-9cfa-07ebc3f996b4/entry/Db2_12_greatest_hits_with_John_Campbell_fast_index_traversal?lang=en

and from Akiko Hoshikawa: a very good IDUG Blog:

https://www.idug.org/p/bl/et/blogaid=646

As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

18. Juli 201914. Januar 2021

2019-06 A little quote from Terry Purcell: It just takes one

This is a little quote from Terry Purcell which I think should be understood much better than it possibly is today!

Really? Just one?

The quote is based on the idea that :

One single SQL statement can bring your entire system to its knees.

Many people think this is a huge exaggeration – However it is not!

It happened to me

This is not a friend of a friend story, but a story that really happened. Names have, of course, been changed but the story itself is 100% accurate.

The story

It was a normal Db2 PTF Maintenance night…

At this shop they always put maintenance in at midnight on Saturdays. All done automatically, and then roll the update around the data-sharing group so that, relatively quickly, all sub-systems have the same PTF level.

So far so normal

Sunday is not that busy at the site and no-one noticed any change.

Monday was different

Monday morning the machine started acting up. Customers could not login and the help desk was 100% busy. The lead DBA, George, had by around 08:00 a.m. received the first escalation to management and so the number of calls went up.

The bad guy

Using our WorkloadExpert (WLX) software together with our Bind ImpactExpert (BIX) the other DBAs, Fred and Ringo, quickly identified one single SQL statement that was taking 30,000 times more resources than in the prior week!

This was an SQL with table functions and LEFT, INNER JOINs etc.

What was it?

The comparison in BIX showed that all that had changed was one index access was now matchcols one instead of matchcols two! That was it! This one tiny change on one little SQL killed this machine…

The fix?

They decided to roll back the PTFs and quickly did this and everything returned to normal…

The lesson?

Always test any PTF before you go live – as just one SQL can kill ya!

Terry had indeed warned us all!

How can we help?

Well, thank you for asking: Our product CDDC contains an SQL replay and compare function that would have spotted this SQL in two different complementary ways. Firstly the BIX part of CDDC would have spotted it straightaway and secondly the replay itself would have thrown this out as a major outlier and bad guy candidate!

Different ways to Rome

They could possibly even have fixed this without backing out the PTF apply. They could have tried using our RUNSTATS Rescue to attempt to use older statistics and see if one of them would have given them a matchcols two access path. This time, however, with all the managers breathing down their necks it was decided – Undo all the changes!

In your shop

How would you have handled this situation in your shop?

Would/Could you have seen this before it happened?

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

16. Mai 201914. Januar 2021

Machine Learning for Db2 z/OS: Artificial Intelligence – Hype or Reality?

Now that the Db2 Optimizer has gone all Artificial Intelligent (AI from now on) on us, I thought it would be interesting to review where we are in this brave new world!

Keeps you out of a BIND

My firm created our BindImpactExpert (BIX) software many moons ago to compare access paths, typically before a program non SQL change or a REBIND after RUNSTATS etc. Just to warn you not to do the REBIND or whatever you were going to do, as it would change the access plan to something you may well not really want to have. This means not just no-brainers like index access to tablespace scan, but also different index usage or table access order changes.

Apples and Oranges

So, what has that got to do with AI?
Well, we used a marketing slogan of trying to compare oranges and apples to try and make it clear :

how tough it really is to try and match SQL statements across different program releases and to compare the results.

ML and AI – The new buzzwords

Now in Db2 12, the optimizer has got machine learning (ML from now on) to help it study which host variables are used the most and how many rows are usually returned by a given query.

Do you “know” your data?

When the optimizer “knows” this info it can of course make much better “guesses” at good repeatable access plans that help everyone! Now the usage of AI and ML always gets me interested because of all the fun you can have with them.

There was recently a great article in my favourite science magazine “New Scientist” all about how to fool image recognition AI:

https://www.newscientist.com/article/mg24232270-200-machine-mind-hack-the-new-threat-that-could-scupper-the-ai-revolution/
(subscribe to read the whole article)

Now you should see the connection back to our BindImpactExpert slogan. So much for Oranges and Apples – AI cannot even see that an Orange is an Orange…

this is not good or even perhaps “suboptimal”…

Adversarial Images and the fun you can have with them…

From 2018 this article

https://www.wired.co.uk/article/artificial-intelligence-hacking-machine-learning-adversarial

Within this article is a great paper all about graffiti changed road signs! Here is a direct link to the article https://arxiv.org/pdf/1707.08945.pdf

Fooling Deep Learning „

Another great paper where Deep Learning AIs jump off the deep end:

http://www.evolvingai.org/fooling

Disappearing Humans

and finally this joyous article and a link to a You Tube video:

https://www.theverge.com/2019/4/23/18512472/fool-ai-surveillance-adversarial-example-yolov2-person-detection

But do not wear this picture as a T-Shirt down the street anytime soon!!!

Would you sit in a driverless car?

What this all boils down to is that I, for sure, will never sit in a driverless car within the next 40 years!
Why?

Firstly, I believe a true AI does not exist yet and will not exist for at least another 40 years.
Secondly, when I see an Orange I see an Orange because I “know” what an Orange looks like! As you have seen AIs have no concept of what an Orange *is* and so can easily see a drill instead or mistake skiers for dogs…
Thirdly, I am a programmer and someone somewhere is writing and testing – I hope! – the code… need I say more? Don’t even get me started with Agile or KANBAN.

extract from © 2019 – „We need to talk, AI“ – Dr. Julia Schneider und Lena Kadriye Ziyal
A Comic Essay on Artificial Intelligence

https://weneedtotalk.ai/

Room full of CTO’s

Have you heard about the hopefully apocryphal story of a room full of CTO’s at a software conference when they were asked “Would you fly in a computer controlled aircraft?” All their hands went up. Then they were asked “Would you fly if it was your software teams that had written the flight control software?” all their hands went down!

Flying is strangely different

While talking about flying my answer would be different?

Why?
Because in the air you have no-one else apart from other highly automated devices anywhere near you! This is the one hope that I can see for the whole driverless car stuff – Driving on the Interstate, Motorway or Autobahn. In the air, the environment is much more rigid. No lights, no crossings, no opposite traffic, no foot traffic, no children chasing a ball across the road etc.

They will come…

I am sure that driverless cars will come soon and I am equally sure that people will die because of them – However, I am also sure that lots of accidents will be avoided as well. After all the AI will always react faster than a human.

With this comes the ethics of AI and ML of course which will also play a major role. If the car has to decide in a crash scenario :

who gets hurt, should it protect itself and its cargo above all others?
What about a group of playing children or a bus full of nuns?
How can it decide who lives and who dies?

Looking on the bright side at least it will not get drunk, or high, and drive! I would also seriously hope that AI does not get road rage!

But even with these “bonus” points, I still have massive doubts and serious worries about the quality of the software and the true image recognition potential of current AIs.

AI everywhere

We were recently asked by a customer to add AI and ML to our SpaceAssuranceExpert (SAX) software. This has been happily running for years, capturing dataset allocations and extent usage in Db2 and making sure they never ran out of space or extents without getting told about it well beforehand.

I had to do a real double take and think “How on earth could AI help here?”

You are approaching 32 Linear Datasets with your segmented tablespace. You have to ALTER and REORG before you use up all the space in the 32^nd. Where can AI help here?
We already have extrapolation from current usage…

Don’t get me wrong

I am not against AI and ML…
but I think we *all* need to get a bit more skeptical about what can actually be delivered now.

On the one hand host variable contents and row counts – Yes indeed, but even here outliers will cause grief and then on the other hand the complete and utter science fiction of self-driving cars in our towns and cities.

The money being spent is also amazing, in 2019 it is estimated, by Syncsort,

that the AI budget will be $35.8 Billion 44% more than in 2018.

What do you think?

I am sure that autonomous cars will be a plague upon us – are you?

TTFN,
Roy Boxwell
Senior Architect

PS: I am looking forward to the first Adversarial Images for Db2 z/OS !!!

4. April 201914. Januar 2021

2019-04 ZOWE for Continuous Delivery – It’s worse than that – he’s tested Jim!

Terrible quote usage I know… But now, with the all new and Agile Db2 12 being picked up and used globally, I think it is time to review a few things that companies and individuals must do, or at least plan, in this “Brave New World” of ours.

To wrap up at the end, I will delve into the idea of using ZOWE to help you test!

This Db2 12 testing checklist, helps to navigate into the Db2 Continuous Delivery world, well supported by Zowe, the modern IBM z/OS GUI

Table of contents – Db2 12-testing-checklist

The Db2 12 Testing Checklist

1. Do not be afraid of the clones!

To even begin to start testing Db2 12, you’ll need to start with a complete production clone. I know a lot of people get suddenly scared about this due to audit requirements or, more commonly, space requirements. I mean, who can possibly support a complete 100% data clone of their productive system?

A real clone or a partial clone?

Of course you do not actually need all of your data! All you must have is the Db2 Catalog and Directory, all of the user objects should be DEFINE NO style empty shells. After all, do you really need TBs of productive data to do a test run? Nope! So now we have removed two major problems (Space and Audit) from the list of testing problems.

2. Collect as much workload as you can

You’ll need to gather as much of your current executed SQL as you possibly can. At best 13 months is a good target. “Not possible” I hear you all scream – “Oh yes it is!” say I.

3. Dynamic & Static SQL: Store it cleverly away

You do not need the same SQL statement 12,000,000 times. Just once, but with an execution count. Store the dynamic SQLs and the retrieved Static SQLs away somewhere nice and safe with a COMPRESS YES to save room, and then you can easily get 13 months of data. Another major problem gone from the list of testing problems.

4. Gather your friends close but your enemies closer – IFCID376

Make sure you are running with IFCID 376 always on and everywhere! Test, QA and Prod. The moment it starts spitting out possible “problems”, start trying to track down the causing event (SQL) and verify that it is OK, or get a code change implemented. This only has a little to do with agile Db2 12 but should be on at every shop anyway!

But I don’t get the Dynamic SQL!

You do if you are fast and good enough! If you are permanently snapping the DSC and catching the flushed statements, then you can indeed find out the SQL that caused the IFCID. Another major problem disappears from the list of testing problems.

5. Access Paths a go go

Now it really makes a lot of sense to EXPLAIN all of the SQL that you have before and after the Db2 12 Functional Level (FL) change to see if just flipping to, for example, FL 504 caused access paths to head south… This is something you should all be doing now as well of course. Always validate the access path before being forced to do a REBIND or PREPARE. This way you will not get any nasty surprises… Another major problem vanishes from the list of testing problems.

6. Execute the SQLs

I have one last thing for you…

Take all of the SQL that you have just processed and transform it so it can actually execute.

Logarithmically reduce the execution count and execute all of the SQL while monitoring all of the Db2 KPIs – On the Clone, of course.
Then reset the world back to your starting Clone, do the FL change and then re-execute all the SQL again.
Once finished, compare all of the KPIs – looking for outliers as there has only been the FL change then.

Any differences you see should be just “background noise” but there could also be some nasties in there. This will light them up so that the root problem(s) can be found and corrected way before you actually do the FL change in production.

Well done, you’ve made it!

That’s it! With all these processes set up and fully automated you can actually do repetitive pipeline tests within hours instead of weeks or months or never!

7 – Db2 12 test-Review:

What does this mean for me?

Well,…

You need to get a fast, good, automated Cloning system in place.
You need to get a fast, good, automated IFCID 376 system in place.
You need to get a fast, good, automated SQL workload collection system in place.
You need to get a fast, good, automated Access Path comparison system in place.
You need to get a fast, good, automated SQL replay system in place.

… this means :

“ Continuous testing in a continuous development world „…

You might, in fact, need to actually buy some software that does all this for you…

May I introduce you to our newest member of the SEGUS/SEG Family:

Db2 Continuous Delivery Deployment Check

is the software that does all of this, and more!

Check out our website for details of how this software really helps in :

Reducing the time to test

Enabling you to actually go forward with a good, clear conscience!

Visit our CDDC

CDDC for Db2 z/OS - Continuous Delivery Deployment Check - Agile & Environment simulation, Zowe IBM mainframe GUI

8 – ZOWE – Open Source zUI – to the rescue?

You have probably heard of ZOWE by now, the first open source software on z/OS from IBM, Rocket Software and CA Technologies (A Broadcom company). It was launched last year and is growing very quickly.

Introduction to Zowe:

Zowe – The zGUI (r)evolution – First hands-on experience and best practices
This presentation shows you the basics you need to understand ZOWE, summarizes the steps you need to use it and illustrates how ISPF online and batch applications are supported from the GUI world. Overview

https://www.youtube.com/watch?v=NX20ZMRoTtk

My firm is also using it now and all future developments will also be ZOWE enabled.

In a nutshell,

it allows users to interact with the Mainframe using a modern GUI.

This is based upon z/OSMF and uses work flows to actually do stuff. You define a “micro service” to do one thing and then you can string as many of these together as you like to get something done like, e.g. Provisioning a Db2 system or Cloning a Db2 subsystem etc.

Zowe at SEGUS and

SOFTWARE ENGINEERING?

We will be using ZOWE for two things:

1 – For the Installation and Maintenance of our software at customer sites

ZOWE enables people to do things that normally require “green screen” and we are keenly aware that green screen people are disappearing. If we want the z/OS platform to survive, it must be dragged, kicking and screaming, into the modern world.

2 – For using ZOWE as the front end for the users of our products

The CDDC product that I described in this newsletter will be built around ZOWE, thus expanding its usability in the market of tomorrow.

We are convinced that ZOWE is the way to go – are you?

TTFN,
Roy Boxwell
Senior Architect

7. März 201914. Januar 2021

2019-03 EXPLAIN data review

A quick history of EXPLAIN and a new standard Db2 EXPLAIN Query to bring out the best and most underused columns.

EXPLAIN has been with us from nearly the start of Db2 (DB2 V2R1 when the b was B!) and, over the years, the number of Explain tables has mushroomed up to 20 in Db2 12. Not *all* of this data is really useful but there are bits and pieces that are well worth adding to your standard repertoire of explain queries!

Table of contents

PLAN_TABLE

This can be used to see in which release the PLAN_TABLE was created:

V2.1 – 25 column format
V2.2 – 28 column format
V2.3 – 30 column format
V3.1 – 34 column format
V4.1 – 43 column format
V5.1 – 46 column format
V6.1 – 49 column format
V7.1 – 51 column format
V8.1 – 58 column format
V9.1 – 59 column format
10 – 64 column format ending with MERGN
11 – 66 Column format ending with EXPANSION_REASON
12 – 67 column format ending with PER_STMT_ID

What could be interesting here?? Well what about:

PREFETCH D, S, L, U, or Blank?

D for Dynamic
S for pure Sequential
L for through a page list
U for unsorted RID list
Blank for Unknown or no prefetch

COLUMN_FN_EVAL R, S, X, Y, or Blank?

R for while data is read
S for while performing a sort
X for while data is read but using OFFSET
Y for while performing a sort but using OFFSET
Blank for after data retrieval and any sort

PAGE_RANGE Y or Blank.

Y for yes the table qualifies for page range screening
so that only the needed partitions are scanned
Blank for no

PRIMARY_ACCESSTYPE D, P, S, T, Blank. Is direct row access attempted first:

D it tries to use direct row access with a rowid column. If it cannot do this it uses the access path that is described in the ACCESSTYPE column
P it uses a DPSI and a part-level operation to access the data
S it uses sparse index access for a sideways table reference
T the base table or result file is materialized into a work file, and the work file is accessed via sparse index access. If a base table is involved, then ACCESSTYPE indicates how the base table is accessed
Blank it does not try to use direct row access by using a rowid column or sparse index access for a work file.

DSN_DETCOST_TABLE

There are a ton of interesting columns here but most are “IBM internal only”, however, these are available for our enjoyment:

ONECOMPROWS	The number of rows qualified after applying local predicates.
IMLEAF	The number of index leaf pages scanned by Data Manager.
IMFF	The filter factor of matching predicates only.
IMFFADJ	The filter factor of matching and screening
DMROWS	The number of data manager rows returned (after all stage 1 predicates are applied).
RDSROW	The number of data manager rows returned (after all stage 1 predicates are applied).
IXSCAN_SKIP_DUPS	Whether duplicate index key values are skipped during an index scan. Y Duplicate key values are skipped N Duplicate key values are not skipped
IXCAN_SKIP_SCREEN	Whether key ranges that are disqualified by index screening predicates are skipped during an index scan. Y Disqualified key ranges are skipped N Key ranges are not skipped
EARLY_OUT	Whether fetching from the table stops after the first qualified row. Y Internal fetching stops after the first qualified row N Internal fetching continues after the first qualified row
BLOCK_FETCH	Y or N Was block fetch used?

DSN_FILTER_TABLE

Sometimes it is really interesting to see when the predicate is applied and whether or not it could be pushed down.

STAGE	The stage that the predicate was evaluated. MATCHING, SCREENING, PAGERANGE, STAGE1, or STAGE2.
PUSHDOWN	Whether the predicate was pushed down. I for the Index Manager evaluates it D for the Data Manager evaluates it Blank means no push down was used

DSN_PREDICAT_TABLE

Here lives the really good stuff that most people do not use! The Bubble Up is normally an SQL coding error and the T is “forgetfulness”…

ADDED_PRED	If this column is non-blank it means that Db2 has rewritten the query to some extent. This is not good as it adds CPU to the process. In my opinion any “added predicate“ should *already* be coded correctly in the SQL! Blank Db2 did not add the predicate B for bubble up C for correlation J for join K for LIKE for expression-based index L for localization P for push down R for page range S for simplification T for transitive closure
CLAUSE	Is this a SELECT, HAVING, ON, or WHERE clause?
ORIGIN	Where did it come from? C for a column mask R for a Row Permission U specified by User Blank generated by Db2

New Standard Explain SQL

Just adding these to your standard Explain query like this:

SET CURRENT SQLID = 'BOXWELL' ;                                   
 SELECT SUBSTR(P.PROGNAME, 1 , 8 )        AS PROGNAME              
       ,SUBSTR(DIGITS(P.QUERYNO), 6, 5)   AS LINE                  
       ,SUBSTR(DIGITS(P.QBLOCKNO), 4, 2)  AS QNO                   
       ,SUBSTR(DIGITS(P.PLANNO), 4, 2)    AS PNO                   
       ,SUBSTR(DIGITS(P.MIXOPSEQ), 4, 2)  AS SQ                    
       ,SUBSTR(DIGITS(P.METHOD), 5, 1)    AS M                     
       ,SUBSTR(P.TNAME, 1, 18)            AS TABLE_NAME            
       ,P.ACCESSTYPE                      AS A                     
       ,P.PRIMARY_ACCESSTYPE              AS PA                    
       ,SUBSTR(DIGITS(P.MATCHCOLS), 4, 2) AS CS                    
       ,SUBSTR(P.ACCESSNAME, 1, 12)       AS INDEX                 
       ,P.INDEXONLY                       AS IO                    
       ,SUBSTR(CASE SORTN_UNIQ    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_JOIN    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END   
                                 , 1 , 4) AS UJOG                  
       ,SUBSTR(CASE SORTC_UNIQ    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_JOIN    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END   
                                 , 1 , 4) AS UJOGC                 
       ,P.PREFETCH                        AS P                     
       ,P.COLUMN_FN_EVAL                  AS CE                    
       ,P.PAGE_RANGE                      AS PR                    
       ,P.QBLOCK_TYPE                     AS TYPE                  
       ,P.MERGE_JOIN_COLS                 AS MJC                   
       ,S.PROCMS                          AS MS                    
       ,S.PROCSU                          AS SU                    
       ,D.EARLY_OUT                       AS EO                    
       ,D.BLOCK_FETCH                     AS BF                    
       ,F.ORDERNO                         AS ON                    
       ,F.PREDNO                          AS PN                    
       ,F.STAGE                           AS STAGE                 
       ,F.PUSHDOWN                        AS PD                    
       ,R.TYPE                            AS TYPE                  
       ,R.ADDED_PRED                      AS AP                    
       ,R.CLAUSE                          AS CLAUSE                
       ,R.ORIGIN                          AS OR                    
       ,R.REDUNDANT_PRED                  AS RP                    
       ,R.TEXT                            AS TRANSFORMED_PREDICATE 
 FROM PLAN_TABLE         P                                         
 FULL OUTER JOIN                                                   
      DSN_STATEMNT_TABLE S               
 ON    P.QUERYNO      = S.QUERYNO        
   AND P.APPLNAME     = S.APPLNAME       
   AND P.PROGNAME     = S.PROGNAME       
   AND P.COLLID       = S.COLLID         
   AND P.GROUP_MEMBER = S.GROUP_MEMBER   
   AND P.SECTNOI      = S.SECTNOI        
   AND P.VERSION      = S.VERSION        
   AND P.EXPLAIN_TIME = S.EXPLAIN_TIME   
 FULL OUTER JOIN                         
      DSN_DETCOST_TABLE  D               
 ON    P.QUERYNO      = D.QUERYNO        
   AND P.APPLNAME     = D.APPLNAME       
   AND P.PROGNAME     = D.PROGNAME       
   AND P.COLLID       = D.COLLID         
   AND P.GROUP_MEMBER = D.GROUP_MEMBER   
   AND P.SECTNOI      = D.SECTNOI        
   AND P.VERSION      = D.VERSION        
   AND P.EXPLAIN_TIME = D.EXPLAIN_TIME   
   AND P.QBLOCKNO     = D.QBLOCKNO       
   AND P.PLANNO       = D.PLANNO         
 FULL OUTER JOIN                         
      DSN_FILTER_TABLE   F               
 ON    P.QUERYNO      = F.QUERYNO        
   AND P.APPLNAME     = F.APPLNAME       
   AND P.PROGNAME     = F.PROGNAME       
   AND P.COLLID       = F.COLLID         
   AND P.GROUP_MEMBER = F.GROUP_MEMBER   
   AND P.SECTNOI      = F.SECTNOI        
   AND P.VERSION      = F.VERSION        
   AND P.EXPLAIN_TIME = F.EXPLAIN_TIME   
   AND P.QBLOCKNO     = F.QBLOCKNO       
   AND P.PLANNO       = F.PLANNO         
 FULL OUTER JOIN                         
      DSN_PREDICAT_TABLE R               
 ON    F.QUERYNO      = R.QUERYNO        
   AND F.APPLNAME     = R.APPLNAME       
   AND F.PROGNAME     = R.PROGNAME       
   AND F.COLLID       = R.COLLID         
   AND F.GROUP_MEMBER = R.GROUP_MEMBER   
   AND F.SECTNOI      = R.SECTNOI        
   AND F.VERSION      = R.VERSION        
   AND F.EXPLAIN_TIME = R.EXPLAIN_TIME   
   AND F.QBLOCKNO     = R.QBLOCKNO       
   AND F.PREDNO       = R.PREDNO         
 WHERE 1 = 1                             
   AND P.QUERYNO  IN (1 , 2 )                               
 ORDER BY 1 , 2 , 3 , 4 , 5 , 24 , 25                       
 ;

Here I limit it to just the QUERYNO 1 and 2 as these were the numbers used for the EXPLAIN command:

EXPLAIN ALL SET QUERYNO = 1 FOR              
 SELECT INSTANCE, CLONE                       
 FROM SYSIBM.SYSTABLESPACE A                  
 WHERE ( SELECT B.TSNAME                      
         FROM SYSIBM.SYSTABLES  B             
             ,SYSIBM.SYSINDEXES C             
         WHERE C.CREATOR   = ?                
           AND C.NAME      = ?                
           AND C.TBCREATOR = B.CREATOR        
           AND C.TBNAME    = B.NAME ) = A.NAME
 AND A.DBNAME = ?                             
 ;                                            
 EXPLAIN ALL SET QUERYNO = 2 FOR              
 SELECT A.INSTANCE, A.CLONE                   
 FROM SYSIBM.SYSTABLESPACE A                  
     ,SYSIBM.SYSTABLES     B                  
     ,SYSIBM.SYSINDEXES    C                  
 WHERE C.CREATOR   = ?                        
   AND C.NAME      = ?                        
   AND C.TBCREATOR = B.CREATOR                
   AND C.TBNAME    = B.NAME                   
   AND A.DBNAME    = ?                        
   AND A.NAME      = B.TSNAME                 
   AND A.DBNAME    = B.DBNAME                 
 WITH UR                                      
 ;

--------+--------+------+--------+---------+---------+--------+------+---
PROGNAME LINE QNO PNO SQ M  TABLE_NAME    A PA CS INDEX    IO UJOG  UJOGC
--------+--------+------+--------+---------+---------+--------+------+---
DSNESM68 00001 01 01  00 0  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00001 01 01  00 0  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00001 02 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00001 02 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00001 02 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00001 02 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00002 01 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 03  00 1  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00002 01 03  00 1  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNE610I NUMBER OF ROWS DISPLAYED IS 13

--+---------+---------+---------+---------+---------+---------+--------
 P  CE  PR  TYPE      MJC            MS           SU  EO  BF        ON
--+---------+---------+--------+---------+---------+---------+---------
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            NCOSUB  ------            1            2  N   N          1
            NCOSUB  ------            1            2  N   N          2
            NCOSUB  ------            1            2  N   N          1
            NCOSUB  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          3
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2

---------+---------+---------+---------+---------+---------+---------+-----
PN  STAGE  PD TYPE  AP  CLAUSE    OR  RP  TRANSFORMED_PREDICATE 
---------+---------+---------+---------+---------+---------+---------+-----
3  MATCHING   EQUAL     WHERE     U   N   "A"."DBNAME"=(EXPR) 
2  MATCHING   EQUAL     WHERE     U   N   "A"."NAME"=(SELECT "B"."TSNAME"
5  MATCHING   EQUAL     WHERE     U   N   "C"."CREATOR"=(EXPR)           
6  MATCHING   EQUAL     WHERE     U   N   "C"."NAME"=(EXPR)              
7  MATCHING   EQUAL     WHERE     U   N   "C"."TBCREATOR"="B"."CREATOR"
8  MATCHING   EQUAL     WHERE     U   N   "C"."TBNAME"="B"."NAME"        
2  MATCHING   EQUAL     WHERE     U   N   "C"."CREATOR"=(EXPR)           
3  MATCH      EQUAL     WHERE     U   N   "C"."NAME"=(EXPR)              
4  MATCH      EQUAL     WHERE     U   N   "C"."TBCREATOR"="B"."CREATOR"  
5  MATCHING   EQUAL     WHERE     U   N   "C"."TBNAME"="B"."NAME"        
9  STAGE1     EQUAL  T  WHERE         N   "B"."DBNAME"=(EXPR)            
6  MATCHING   EQUAL     WHERE     U   N   "A"."DBNAME"=(EXPR)            
7  MATCHING   EQUAL     WHERE     U   N   "A"."NAME"="B"."TSNAME"

It shows you a lot more data than the normal bare bones Explain PLAN_TABLE data especially here the TYPE, STAGE and ADDED_PREDICATE (AP Column).

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

20. Februar 201914. Januar 2021

2019-02 Global Temporary Confusion

Global Temporary Table usage in Db2 z/OS

Learn, through examples, why CGTT (CREATEd Global Temporary Tables) are possibly better than DGTT (DECLAREd Global Temporary Tables) and how to use them to speed up performance.

When Global Temporary Tables were first introduced (DB2 V5.1) it was all pretty clear! You used normal DDL to CREATE a GLOBAL TEMPORARY Table that existed in the Db2 Catalog, but every process got “their” own copy. There were limitations like No UPDATE, No Single DELETE, no Default Values and ROLLBACK / COMMIT and data reuse was not simple but it all sort of worked…

Global – but not as you know it!

Then along came the brand new Global Temporary Table (DB2 V6.1) which, just to make matters clearer, they prefixed with DECLARE so that it was 100% obvious that it had *nothing* to do with the “other” temporary table… I tell ya, if I ever meet the developer who came up with these names in a dark alley one night…. But I digress… So with the new DECLARE version you suddenly *could* do Update and Delete and it came with an ON COMMIT clause to make it simpler to handle data with COMMIT – Hoorah!

Global Temporary Table in Db2 12 – All new and improved

As of Db2 12, the full list of things you can do with a CREATE GLOBAL TEMPORARY TABLE (from now on simply CGTT) as opposed to a DECLARE GLOBAL TEMPORARY TABLE (from now on simply DGTT) is:

 What            CGTT           DGTT
 MERGE           No             Yes
 UPDATE          No             Yes
 Single DELETE   No             Yes
 DEFAULTs        No             Yes
 ROLLBACK        Deletes all    Yes (Plus NOT LOGGED ON ROLLBACK)
 COMMIT          Deletes all    Depends on ON COMMIT
                 (unless WITH HOLD)
 Indexes?        No             Yes
 WITH RETURN     Yes            Yes <- This is very good news
 TO CLIENT

CGTT why?

So, looking at the list, you have to wonder – “Why would anyone be using CGTTs?”
Well the reason is “Performance!” –
DGTTs might do all the tricks but they also run like a dog!

Belief is ok, test is trust!

I wrote a couple of small COBOL programs that did the same thing: one using a DGTT and one using a CGTT. All the programs did, was call a section 1000 times that DECLARED the table (or obviously not!) and then called another section that inserted 10 rows and opened a cursor to then fetch these rows right back with an ORDER BY. I chose this test as most DGTT/CGTT usage is low-volume but high called rate (Think stored procedures here!) so I wanted to see what sort of overhead 1000 DGTTs caused.

No EDMPOOL here!

Monitoring is not easy as DGTT usage is not in the EDMPOOL, so I did it the “good old way” by running my tests when I was alone on the system and five times each. I was a bit shocked by the results…

What               CGTT                   DGTT
DBM1 EXCPs          0                      8,817
MSTR EXCPs          0                     59,653
Db2 CPU             0.140 secs             3.546 secs
Program cpu         4.506 secs           101.124 secs
Program elapsed     4.958 secs           118.730 secs

I then changed the DGTT to also be NOT LOGGED

What                   DGTT 
DBM1 EXCPs               66
MSTR EXCPs               47
Db2 CPU               2.446 secs
Program cpu         100.818 secs
Program elapsed     116.426 secs

Not that much better! But remember, I did not do any DELETE or UPDATE processing so your performance data may be better than mine.

Impressive!

Now the reason for all of the I/O and CPU is
all of the internals that Db2 must do for the DGTT, remember the CGTT has *already* been declared and exists in the catalog. The DGTT naturally not…
This can, as be seen, add a lot of overhead!

ROT still correct

Now the good old Rule of Thumb still holds true:

A CGTT is great for doing zero updates and sequential access!

Db2 10 improvement

When a cursor is declared with the attribute RETURN TO CLIENT then this cursor is available from the originating calling client even if the current stored procedure is down line. This is very neat as the cursor is “invisible” to all the intermediate procedures.

Death by DGTT

The problem in the past was the “hand shaking” of the final stored procedure result set. It had to be read out and put into another DGTT and then this handling was repeated all the way back up the calling chain. Now, if you have a “final cursor”, you can simply declare it as RETURN TO CLIENT – This saves tons of CPU and elapsed time!

Final tip!

Remember if you use STATIC SCROLLABLE cursors then they *must* use DGTTs in the background to actually work!

Even more work…

Then a colleague who proof-reads all my newsletters, a nasty job but someone must do it, asked me:

“I wonder what the numbers would be if you declared the GTT only once, then INSERTED/SELECTED/DELETED a bunch of rows 1000 times? Because that’s how I’m using it …“

So then I changed the programs to only DECLARE once, insert a 1000 rows, Select a 1000 rows and then do a MASS DELETE of the data.

What             CGTT           DGTT            DGTT NOT LOGGED
DBM1 EXCPs       7,572          7,720           7,769
MSTR EXCPs       0                672             238
Db2 CPU          0.220 secs     0.310 secs      0.410 secs
Program cpu      0.346 secs     0.482 secs      0.468 secs
Program elapsed  0.704 secs     0.836 secs      0.818 secs

Much closer, in terms of performance, but still the CGTT is quicker – Time to check your usage of these Temporary Tables if you ask me!

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

14. Januar 201931. Mai 2021

2019-01: Regular Expressions in Db2 SQL – Regulate this!

Impress your JAVA colleagues with Regular Expressions in Db2 SQL – Become a regex specialist with these samples (regex usage in the TSO Editor & regex as a predicate).

The references to blogs focusing on this topic will help round out your knowledge all about „Regular Expressions in Db2 SQL“
(advanced pattern matching, regex testing and REGEXP_LIKE usage).

I have never really been a fan of regular expressions (regex) as, by default, I work on Mainframes and regex was always “just for the small boxes that you can lift”, however, since Db2 12 I have started to play around with them in various abstruse places.

TSO? You gotta be kidding?

Yep, even in the TSO editor you can use regex. This example will find all occurrences of data that starts with an M, contains DB and ends with at least two numerics. After the pipe (an OR in regex) it does the same but for strings that start with an O followed by 2DB and ends with two numerics:

VIEW       SE.MDB2VNEX.TCOBOL(#GOALL) - 01.00              Columns 00001 00072
Command ===> F R'^M[DB].*[0-9]{2}|^O[2DB].*[0-9]{2}' all  Scroll===> 
****** ***************************** Top of Data ********************
001600 MDB2DBRI  EXPD=N                                             
001700 MDB2DBRT  EXPD=N                                              
001800 MDB2DBSC  EXPD=N                                              
001900 MDB2DBSF  EXPD=N                                              
002000 MDB2DBSG  EXPD=N                                              
002100 MDB2DBTA  EXPD=N                                              
002200 MDB2DBTP  EXPD=N                                              
002300 MDB2DBTS  EXPD=N                                              
002400 MDB2DB01  EXPD=N                                              
002500 MDB2DB02  EXPD=N                                              
002600 MDB2DB03  EXPD=N                                              
002700 MDB2DB04  EXPD=N

Note that changing the R to be RC then makes the regex case sensitive.

Using “x all” first, so you can see what it finds…

VIEW SE.MDB2VNEX.TCOBOL(#GOALL) - 01.00 28 CHARS '^M[DB].*[0-9]{
Command ===>                                     Scroll ===> CSR 
002400 MDB2DB01 EXPD=N 
002500 MDB2DB02 EXPD=N 
002600 MDB2DB03 EXPD=N 
002700 MDB2DB04 EXPD=N 
002800 MDB2DB05 EXPD=N 
002900 MDB2DB06 EXPD=N 
003000 MDB2DB07 EXPD=N 
003100 MDB2DB08 EXPD=N 
003200 MDB2DB09 EXPD=N 
003300 MDB2DB10 EXPD=N 
003400 MDB2DB11 EXPD=N 
003500 MDB2DB12 EXPD=N 
003600 MDB2DB13 EXPD=N 
- - - - - - - - - - - - - - - - - - - 7 Line(s) not Displayed
009900 MDB2U001 EXPD=N 
010000 MDB2U003 EXPD=N 
010100 MDB2U005 EXPD=N 
010200 MDB2U006 EXPD=N 
010300 MDB2U007 EXPD=N 
- - - - - - - - - - - - - - - - - - - 4 Line(s) not Displayed
019800 O2DB60 EXPD=N 
019900 O2DB61 EXPD=N 
020000 O2DB62 EXPD=N 
020100 O2DB63 EXPD=N 
020200 O2DB64 EXPD=N 
020300 O2DB65 EXPD=N 
020400 O2DB66 EXPD=N 
020500 O2DB67 EXPD=N 
020600 O2DB68 EXPD=N 
020700 O2DB69 EXPD=N

And the Message looks like:

All pretty cool! Naturally you can use nearly any format of regex wherever you like and so it is very powerful. They are fully supported in Find, Change (But not for the second argument in a Change!) and Exclude.

Further infos about regex

Usage in TSO: For more info about their usage in TSO check out the KC entry

https://www.ibm.com/docs/en/zos/2.1.0?topic=string-regular-expressions-string1#useofr1

REXX Usage: Rexx usage is also available and Martin Packer wrote a great little Blog all about a regex testing routine that you can use to play with as well:
https://mainframeperformancetopics.com/2020/01/05/tso-regular-expression-testing-tool/

So what about Db2?

Well… on the “boxes you can lift” they have had regex for a long time. All built into the Db2 Engine. Fred Sobotka’s article “Advanced Pattern Matching with Regular Expressions in DB2 11.1 for LUW” in the IDUG Blog shows lots of really cool ways of using REGEXP_LIKE and its brethren and is well worth a read.

What about “real” Db2?

Well… we have it… sort of… not really what you would think but it is indeed there! You just have to jump over some fences and avoid some rocks and twist your required data into XML and Bob’s your uncle!

Say what? Regex lists out useful tables like:

Ok, ok, enough delaying… here is a regex that lists out all tables that start with between two and five characters ranging from B to Z and then ends with just two numerics:

SELECT NAME, creator from SYSIBM.SYSTABLES 
WHERE 
XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING
        XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")
        as "newXDoc")    
order by 1     
--fetch first 10 rows onl
;
---------+---------+---------+---------+---------+---------+--------
NAME
---------+---------+---------+---------+---------+---------+--------
CERNT01  
CERNT02 
CERNT03
CERNT04  
CERNT05 
CERNT06

It will not win a beauty contest any day soon but, hey, it works!

How does that work?

SELECT NAME, creator from SYSIBM.SYSTABLES
WHERE

Just a run-of-the-mill, bog standard SELECT, FROM and WHERE – Nothing new or changed here!

XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING 
XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")
as "newXDoc")

This must be viewed as two separate statements, first the inner statement:

XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")

This “translates” the column NAME into an XML construct with the element name xmltbname ready for the regex which we do by “faking it” through an XMLEXISTS:

XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING 
         …
         as "newXDoc")

This uses the fn:matches function which will actually do the regex.

The data in the regex ^[B-Z]{2,5}[0-9]{2}$ is a normal regex and it uses the xmltbname as input. fn:matches is boolean and so returns True or False. This is then a “normal” predicate as any row that is “true” must have passed the regex.

Clear as mud!

It is a bit weird and I can recommend some more reading,

firstly the excellent regex site where you can learn all about the joys and dangers of these beasts:

http://www.rexegg.com/

And the IBM XQuery docu that describes how the fn:matches works in detail:

https://www.ibm.com/docs/en/db2-for-zos/12?topic=expressions-regular

Both are well worth a read – be especially careful about “explosive quantifiers”!

CPU?

Yep, you guessed it. There is no such thing as a free lunch!

The use of this method is *not* cpu light.
It should only ever be used if normal LIKE, REPLACE or TRANSLATE cannot easily get the job done and if you end up coding a regex like:
^(?=(?!(.)\1)([^\DO:105-93+30])(?-1)(?<!\d(?<=(?![5-90-3])\d))).[^\WHY?]$

Then do not be surprised if your colleagues all start to hate you!

😉 😉

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Every now and again, the DBAs of the world meet with the developers of the world and try and bang some heads together…I already wrote a newsletter all about existence checks which has changed and evolved over the years and now I wish to investigate another age old problem: “Is Db2 there?”

Newsletter 2012-10: Existence check SQL – Through the ages

Reader test results: see the end of this newsletter for SELECT queries tested by one of our readers on Db2 10 and DB2 11.

Why?

Problems

How to do this ?

How often to do this ?

Do you need to do this ?

Haven’t you got better things to do than this?

1 – How to do this ?

The SQL then changed a little bit to be:

Now Terry Purcell and the optimizer crew came up with the PRUNED access plan in Db2 9:

PRUNED

IBM have been improving what PRUNED can do ever since, and Terry Purcell wrote :

2 – How often to do this?

3 – Do you need to do this?

4 – Haven’t you got better things to do than this?

Measured Response

First up are the SELECT * style selects:

Now the SELECT COUNT(*) style selects:

The results were:

Reader test results from Isaac Yassin:

Measured for dynamic SQL (SPUFI in UR mode – DSNESPUR), using Omegamon application trace. Same machine, same LPAR, same subsystem, measuring the 3rd consecutive execution of each SQL. Both do a „prepare/open/fetch/close“ according to trace.

The differences are:

For Db2 10

Without „where 0=1“

With „where 0=1“

For Db2 11

Without „where 0=1“

With „where 0=1“

The differences are:

For Db2 10

Without „where 0=1“

With „where 0=1“

For Db2 11

Without „where 0=1“

With „where 0=1“

Bottom line

This time I have a nice little real world story for you. Pull up your good old copy of the Db2 9 SQL Guide and I will begin…

What is a number?

Along came BIGINT

DECFLOAT Arrives

The problem?

The horrible problem that DECFLOAT dragged in to the party, was the fact that it could contain characters like :

The law of numeric comparisons

What problems does this cause?

Upper and Lower trouble

What can you do?

If you know what you are doing…

Let’s do some SELECTs

Now the really evil one:

It gets worse

What can you do?

Capturing the SQL workload “sees” the SQLCODEs when processed

If you have the SQLCODE Use Case then you get all SQLCODEs that are output by Db2

Doing it right

Where else?

I have spoken/blogged about limitations in Db2 a few times over the years and we have a nice little tool called Space AssuranceExpert (SAX) that stops a whole bunch of nasty events (read -904’s) at sites around the world.

It grows and grows

First enhancements

– SMS Storage groups Monitoring

– Universal support

– Db2 12

– Sequences and Identities

– Numeric Primary Key support

No time to rest on our Laurels!

SAX saves the day again!

you can now run a batch job to review all of these limits.

What are the limits?

In the Db2 SQL Reference Guide in the Appendix “Limits in Db2 for z/OS” it lists them out, but here are the important ones for you all:

750 Columns in a table.

64 Columns in an index. This includes INCLUDE columns.

Stay up to date

What is the plan?

And you?

Go ahead, make my day and install a SAX trial!

So we get a new TLA (Three Letter Abbreviation) to try and wrap our heads around in the busy busy world of mainframe databases. This time it comes with a twist, as FTB is actually not “Feed The Beast” sadly, but “Fast Traversal Block”.

FIT (Fast Index Traversal)

The problem with modern indexes

Index structure