2024-03 I am fine, up to a certain DEGREE

This month is all about going parallel! In the Db2 world, we have had the ability to run SQLs using parallel processing for decades. It started off a bit wobbly and most people didn’t use it, or even like it, but these days it is extremely useful for certain cases.

Sort Yourself Out

Sort is very important here. If you can do a parallel sort with only one extra parallel task it will halve your elapsed time… if you can add more tasks it takes even less elapsed. Naturally, you do not save on CPU here, in fact it will probably “cost” more, but you are trading CPU for elapsed time.

Ground Rules

IBM states in the documentation that parallel processing is “Only for partitioned objects” but then they mention that even non-partitioned objects can benefit, as the access to the non-clustering index and the data can be done in parallel… which is not helpful if you only have clustering index access, of course!

Bells and Whistles!

There are quite a few things to adjust and play with on the road to parallel processing!

No Way!

If you declare your cursor as WITH HOLD and with isolation level RR or RS then there is *no* CPU parallelism allowed at all but you can still get parallel sorts.

ZPARM Time

First up is CDSSRDEF (CURRENT DEGREE) where the IBM recommendation must be read:

CURRENT DEGREE field (CDSSRDEF subsystem parameter)

The CDSSRDEF subsystem parameter determines the default value that is to be used for the CURRENT DEGREE special register. The default value is used when a degree is not explicitly set in the SQL statement SET CURRENT DEGREE.

Acceptable values: 1, ANY

Default: 1

Update: option 30 on panel DSNTIPB

DSNZPxxx: DSN6SPRM CDSSRDEF

1 Specifies that when a query is dynamically prepared, the execution of that query will not use parallelism. If this value is specified, Db2 does not use any optimization hints for parallelism.

ANY Specifies that when a query is dynamically prepared, the execution of that query can involve parallelism.

Recommendation: In almost all situations, accept the default value of 1. You should use parallelism selectively where it provides value, rather than globally. Although parallelism can provide a substantial reduction in elapsed time for some queries with only a modest overhead in processing time, parallelism does not always provide the intended benefit. For some queries, and in many other situations, query parallelism does not provide an improvement, or it uses too many resources. If you are using nearly all of your CPU, I/O, or storage resources, parallelism is more likely to cause degradation of performance. Use parallelism only where it is most likely to provide benefits.

The ZPARM INDEX_IO_PARALLELISM should get an honorable mention here as it was there right up until Db2 11 and it is, in fact, still within the Db2 12 and 13 indexes at the end of the installation guide pdf, but it has thrown off its mortal coil…

INDEX_IO_PARALLELISM

Specifies whether I/O parallelism is enabled for index insertion.

Acceptable values: YES, NO

Default: YES

DSNZPxxx: DSN6SPRM INDEX_IO_PARALLELISM

Security parameter: No

YES I/O parallelism is enabled for index processing. I/O parallelism allows concurrent insert operations on multiple indexes and can reduce I/O wait time when many indexes are defined in a table.

NO I/O parallelism is disabled for index processing.

Naturally, YES is what it should be set to!

To round out the dearly departed, there was also this one:

PARALLELISM EFFICIENCY field (PARA_EFF subsystem parameter)

Controls the efficiency that DB2 assumes for parallelism when DB2 chooses an access path. Valid values are integers 0 – 100. The integer represents a percentage efficiency.

It came in Db2 9 with PM16020 and started life with a default value of 100 before getting a default change to 50 in Db2 10. It got deprecated in Db2 12 and was removed in Db2 13.

Then PARAMDEG with a handy Top Tip:

MAX DEGREE field (PARAMDEG subsystem parameter)

The PARAMDEG subsystem parameter specifies the maximum degree of parallelism for a parallel group. When you specify a non-zero value for this parameter, you limit the degree of parallelism so that Db2 cannot create too many parallel tasks that use virtual storage.

Acceptable values: 0 – 254

Default: 0

Update: option 30 on panel DSNTIPB

DSNZPxxx: DSN6SPRM PARAMDEG

0 Specifies no limit to the maximum degree of parallelism that Db2 chooses based on the cost estimate for the query and the system configuration, in particular the number of processors online. Db2 counts both general purpose and zIIP processors equally, and applies further adjustment to determine the degree to use.

1 – 254 Specifies the maximum degree of parallelism that Db2 uses. When optimization hints for parallelism are used, the value of the PARAMDEG subsystem parameter does not limit the degree of parallelism at bind time. However, the value of the PARAMDEG subsystem parameter is enforced at execution time. So, if the value of the PARAMDEG subsystem parameter is lower than the degree of parallelism that is specified at bind time, the degree of parallelism is reduced at execution time.

Tip: For systems with more than two zIIP processors configured, use the number of zIIP processors as the starting value, and then adjust as needed for your response time requirements.

Basically, set this value to be one or two times the number of online available CPUs but take into account the ZiiPs.

Then its new DPSI baby brother:

MAX DEGREE FOR DPSI (PARAMDEG_DPSI subsystem parameter)

The PARAMDEG_DPSI subsystem parameter specifies the maximum degree of parallelism that you can specify for a parallel group in which a data partitioned secondary index (DPSI) is used to drive parallelism.

A DPSI is a non-partitioning index that is physically partitioned according to the partitioning scheme of the table. When you specify a value of greater than 0 for this parameter, you limit the degree of parallelism for DPSIs so that Db2 does not create too many parallel tasks that use virtual storage.

Acceptable values: 0-254, DISABLE

Default: 0

Update: option 30 on panel DSNTIPB

DSNZPxxx: DSN6SPRM PARAMDEG_DPSI

Data sharing scope: All members use the same setting

0 Specifies that Db2 uses the value that is specified for the PARAMDEG subsystem parameter, instead of PARAMDEG_DPSI, to control the degree of parallelism when DPSI is used to drive parallelism. This is the default value for the field.

1 Specifies that Db2 creates multiple child tasks but works on one task at a time when DPSI is used to drive parallelism.

2-254 Specifies that Db2 creates multiple child tasks and works concurrently on the tasks that are specified. The number of specified tasks may be larger or smaller than the number of tasks as specified in PARAMDEG. When PARAMDEG is set to 1, the rest of the query does not have any parallelism.

DISABLE Specifies that Db2 does not use DPSI to drive parallelism. Parallelism might still occur for the query if PARAMDEG is greater than 1.

This is for fine-tuning the DPSI use case. Remember, you can have 4096 partitions and so it could well be that a query goes crazy if it sees the ability to go massively parallel. Here you can limit, or even inhibit, that from happening. Only use if you have been bitten by a rogue SQL!

Then we get to Utility parallel processing which is not for all Utilities – but REORG TABLESPACE and COPY are there!

MAX UTILS PARALLELISM field (PARAMDEG_UTIL subsystem parameter)

The PARAMDEG_UTIL subsystem parameter specifies the maximum number of parallel subtasks for some utilities.

PARAMDEG_UTIL affects the following utilities:

• REORG TABLESPACE

• REBUILD INDEX

• CHECK INDEX

• UNLOAD

• LOAD

• COPY

• RECOVER

Acceptable values: 0 – 32767

Default: 99

Update: option 34 on panel DSNTIPB

DSNZPxxx: DSN6SPRM PARAMDEG_UTIL

0 No additional constraint is placed on the maximum degree of parallelism in a utility.

1 – 32767 Specifies the maximum number of parallel subtasks for all affected utilities.

Interesting default huh? In Db2 11 it was actually zero!

LOAD must get a special mention here, as back in Db2 11 it got a new keyword PARALLEL
( nnn ) which enabled parallel loading from a *single* input file (Initially not for PBGs but then that was allowed in Db2 12). This little chestnut has often been forgotten.

Further into the guts of REORG is this little one:

REORG LIST PROCESSING field (REORG_LIST_PROCESSING subsystem parameter)

The REORG_LIST_PROCESSING subsystem parameter specifies the default setting for the PARALLEL option of the Db2 REORG TABLESPACE utility.

Acceptable values: PARALLEL, SERIAL

Default: PARALLEL

Update: option 37 on panel DSNTIPB

DSNZPxxx: DSN6SPRM REORG_LIST_PROCESSING

PARALLEL The default value PARALLEL specifies that the REORG TABLESPACE utility is to use a default PARALLEL YES option when the PARALLEL keyword is not specified in the utility control statement. The PARALLEL YES option specifies that the REORG TABLESPACE utility is to process all partitions that are specified in the input LISTDEF statement in a single execution of the utility.

SERIAL Specifies that the REORG TABLESPACE utility is to use a default PARALLEL NO option when the PARALLEL keyword is not specified in the utility control statement. The PARALLEL NO option specifies that each partition that is specified in the input LISTDEF statement is to be processed in a separate execution of the utility.

I would also happily stick with the default unless you have experienced serious problems with VTS or some such.

Don’t Forget the Bufferpool, Stupid!

BUFFERPOOLs play a major role. The setting of VPSEQT (Virtual bufferpool sequential steal threshold) and VPPSEQT (Virtual bufferpool parallel sequential threshold) might well both have to be raised. Remember that the VPPSEQT is a percentage of the VPSEQT available pages. The bufferpool size itself might well have to be raised (VPSIZE) if you do not see an improvement in the degree of parallelism.

All Ready?

So, you have set, checked, reviewed, changed the ZPARMS and are ready to go?

Where’s the ON Switch?

For Static SQL just bind or rebind with DEGREE(ANY), for dynamic SQL issue a

SET CURRENT DEGREE = ‘ANY’ ;

CDSSRDEF Zparm has the default for this parameter.

If you bind with isolation level CS, then also try and make sure you use CURRENTDATA(NO) as well. This helps performance anyway and also aids Db2 in working with ambiguous cursors. Explicit read-only is always better!

For sorts, where the big elapsed time gains come from, make sure you have sufficiently sized work files allocated! Here, even the WITH HOLD and isolation RR or RS can benefit.

Gotchas?

Always at least one problem isn’t there? If you do DEGREE(ANY) you can expect the EDMPOOL usage to go up between 50% and 70% due to run-time structures. Check your SYSPACKAGE AVGSIZE before and after the BIND/REBIND if you are worried. Always monitor this pool and make sure it is correctly sized for your workload!

Naturally, the CPU might well go up but you should see a good drop in elapsed times and, as far as sort is concerned, you might actually manage a successful parallel sort in a normally constrained sub-system!

Where’s the OFF Switch?

For Static SQL just bind or rebind with DEGREE(1), for dynamic SQL issue a

SET CURRENT DEGREE = ‘1’;

This is also the default value but what if someone changed your default?

If you shrink the VPPSEQT to 0 that will disable all parallel access for objects in that bufferpool.

Insert rows in the resource limit tables – Not recommended as this is a lot of work!

Are You Running in Parallel then? EXPLAIN is Your Friend!

There are a few “access patterns” that allow CP parallelism and they are all documented in the Managing Performance book – “Checklist of query restrictions for query CP parallelism” table.

How do You Check?

The columns of interest in the PLAN_TABLE are the ACCESS_DEGREE and JOIN_DEGREE. If either of these two is not NULL then you are using, or hoping to use, parallel processing! The moment you have more than one table then four other columns become interesting: ACCESS_PGROUP_ID, JOIN_PGROUP_ID, SORTN_PGROUP_ID and SORTC_PGROUP_ID. PGROUP is short for PARALLEL GROUP and for a given PLANNO step they all have the same number. Finally, the PARALLELISM_MODE reflects which type you are using which, these days, can *only* be ‘C’ for Query CP parallelism (Came in DB2 V4). It used to also have the values ‘I’ for parallel I/O operations (Came in DB2 V3)  and ‘X’ for Sysplex query parallelism (Came in DB2 V5) but they were deprecated in Db2 9 and are now both dead and buried!

Which SQLs are Best for Parallel Processing?

SQLs that are I/O intensive and scan lots of pages while returning just a few rows, SQLs that have lots of aggregate functions, and naturally SQLs that require Sort – all of these are good candidates.

Trial it DUMMY!

Best thing to do is a trial rebind to a dummy collection of all your SQL in a sandbox system with production statistics copied over and with parallel processing enabled (Do not forget the bufferpools!). This will then quickly reveal which queries could indeed benefit from going parallel and enable you to activate it only at the package/SQL level that you require in production.

Apply and Test

Once you have your candidate list, and I hope it is not that long, you can simply enable it all in production and do a live run through and review. First with EXPLAIN and then really live.

Remember that it is not good for *all* SQLs but it can really help when it hits the spot!

My Favorite Table Ever!

Db2 11 introduced an automatic five level control system for parallel queries:

Level 1 OK: Query runs with planned parallel degree

Level 2 Mild warning: Reduce parallel degree by ¼

Level 3 Moderate warning: Reduce parallel degree by ½ or to degree 2

Level 4 Severe warning: Reduce to sequential run

Level 5 Melt down: Reduce to sequential run

Level 5 is sub-optimal!

The original table is here: (Do a search for “Melt”)

https://www.redbooks.ibm.com/redbooks/pdfs/sg248222.pdf

How to Tame the Beast?

For static SQL, just bind/rebind with DEGREE(1) to switch off or DEGREE(ANY) to switch on.

For dynamic SQL, if you cannot add the SET CURRENT DEGREE = ‘ANY’ and RLF tables do not work for you then the only way is to assign the tables in question to their own bufferpools and set the VPPSEQT to a value, or leave at default 50, and for *all* other bufferpools set the VPPSEQT to 0 which switches off parallel processing.

Another way of handling dynamic, but a bit over the top for my taste, is a new data-sharing member where the ZPARM CDSSRDEF is set to ‘ANY’. Then any dynamic work that should be allowed to go parallel is simply routed to just this member.

Whaddya all think?

Going to start testing out parallel processing anytime soon?

I’d love to hear from you!

TTFN

Roy Boxwell

Feedback:

One of my readers mentioned that the primary reason they use parallelism is to increase zIIP offload which offers not just elapsed reduction but also saves the cost of the cpu as well.

Naturally, if you get this, then you are really laughing all the way to the bank!

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…

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

2021-05 Soundex and other cool features part 7 for Db2 12

In this, obviously, never ending series of new features, I will roll up all the new ones since my “SOUNDEX and other „cool“ features – Part six All new for Db2 12” newsletter from 2018-08 where I first covered the new stuff in Db2 12.

What was new in FL100

At that level, a new table function called BLOCKING_THREADS was introduced, which is primarily used by the command DISPLAY BLOCKERS to help in looking for bad guys who are ready to BLOCK your change to FLxxx

FL500 Changed Scalars

Two scalar BiF got changed: TRANSLATE and UPPER can now both use UNI_60 as a locale.

FL500 New Scalars

In FL500, we got a nice bunch of new Scalar functions:

GENERATE_UNIQUE_BINARY (like GENERATE_UNIQUE except it returns a BINARY(16) value)
HASH_CRC32
HASH_MD5
HASH_SHA1
HASH_SHA256
PERCENTILE_CONT
PERCENTILE_DISC
WRAP

FL500 Changed Aggregates

One aggregate BiF got updated: ARRAY_AGG can now be used for associative arrays.

FL501?

It brought in just one new Aggregate BiF, which was the very famous LISTAGG. This is famous, as it was the “proof of concept” that Agile actually worked and could function in the wild.

FL502?

Two changes to Scalar BiFs in this release:

GRAPHIC The first argument now accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT.
VARGRAPHIC The first argument now accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT.

FL503?

Nothing new for us in that release!

FL504?

It brought in a brand new concept for Db2 on z/OS. The concept of “pass-through BiFs”. These are just checked for valid syntax before being passed on down to the Accelerator that would actually execute them. Obviously, you must be careful about their usage!

CUME_DIST
CUME_DIST (aggregate)
FIRST_VALUE
LAG
LAST_VALUE
LEAD
NTH_VALUE
NTILE
PERCENT_RANK
PERCENT_RANK (aggregate)
RATIO_TO_REPORT
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR

FL505?

Brought in a whole bunch of encryption and decryption BiFs:

ENCRYPT_DATAKEY converts a block of plain text to a block of encrypted text using a specified algorithm and key label.
DECRYPT_DATAKEY_datatype functions return the block of encrypted text from ENCRYPT_DATAKEY as a block of the datatype specified by the function used. List of datatypes: DECRYPT_DATAKEY_INTEGER, DECRYPT_DATAKEY_BIGINT, DECRYPT_DATAKEY_DECIMAL, DECRYPT_DATAKEY_VARCHAR, DECRYPT_DATAKEY_CLOB, DECRYPT_DATAKEY_VARGRAPHIC, DECRYPT_DATAKEY_DBCLOB, and DECRYPT_DATAKEY_BIT.

FL506?

Here IBM came up with a slew of “alternative names” for existing BiFs and a new method of calling HASH routines. The background here was to make porting to Db2 on z/OS that little bit easier!

CHAR_LENGTH CHARACTER_LENGTH, which returns the length of its argument in the number of string units that are specified
COVAR_POP COVARIANCE or COVAR, which return the population covariance of a set of number pairs
HASH HASH_MD5, HASH_SHA1, or HASH_SHA256, which return the result of applying a hash algorithm to an input argument, depending on the value specified for the second argument for the HASH function:

        0 (default) HASH_MD5 Returns VARBINARY(16) instead of BINARY(16)
        1 HASH_SHA1          Returns VARBINARY(20) instead of BINARY(20)
        2 HASH_SHA256        Returns VARBINARY(32) instead of BINARY(32)

POW POWER, which returns the value of one argument raised to the power of a second argument
RANDOM RAND, which returns a double precision floating-point random number
STRLEFT LEFT, which returns a string that consists of the specified number of leftmost bytes or the specified string units
STRPOS POSSTR, which returns the position of the first occurrence of an argument within another argument
STRRIGHT RIGHT, which returns a string that consists of the specified number of rightmost bytes or specified string units
TO_CLOB CLOB, which returns a CLOB representation of the first argument
TO_TIMESTAMP TIMESTAMP_FORMAT, which returns a timestamp for a character string expression, using a specified format to interpret the string

FL507?

Here IBM development added another slew of “pass through” BiFs:

ADD_DAYS
BTRIM
DAYS_BETWEEN
NEXT_MONTH
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT or REGR_ICPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
ROUND_TIMESTAMP if invoked with a date expression

FL508?

None that I have read of yet!

FL509?

None yet…

Naturally I will be keeping this newsletter up-to-date, as necessary.

Any questions or ideas, do not hesitate to drop me a line,

TTFN,

Roy Boxwell

2020-04 Four Flavors of Db2 Audit

As well as simply getting it done, I will also run through the four different ways that you can process the gathered data.


Four ways to get a Db2 z/OS Audit done


1- First up

First option is the simplest, cheapest and quickest:

Do nothing.

Whether or not this will help your company is a non-trivial question of course!

Naturally this is an absolute No No.


2- Then we have

Next option is relatively simple and cheap, but requires a bit of work: 

Write it all yourself but based on existing data that some other process already extracts for you, (SMF for example). 

If you happen to have the skills for extracting the required audit data from existing data that is being collected anyway, then this might well be the best method if you are really strapped for resources. 


3- Getting there 

Then we have not so simple, still cheap, but a ton of work: 

Write it all yourself and add all the IFCIDs you actually need to audit your system as well as capturing all the SQL. 

This needs a serious amount of skills to get and keep up with the agile world of Db2. You will also need to take care of the amount of data that you will be collecting.

However, the auditor will be happy as you have everything they could ask for.


4- Aha! The only true way 

Last option is simple, not so cheap but very quick: 

Third party software that does it all for you.

This is my preferred solution, especially as we just happen to sell one (WorkLoadExpert Audit).

This is actually the only real way to go. You probably don’t have the time to keep all these things up-to-date and running correctly. 

Data Collected – Now what? 

So, you have chosen one of these ways to gather the data. Now you must evaluate what you got. Here again we have four separate ways to go forward: 

First up 

There it is! 

Do nothing. Just point at the datasets, print outs, database objects and say “It is all in there…” 

This is not really a solution and any auditor worth his, or her, salt would quite rightly be extremely upset! 

Then we have 

A whole bunch of pre-written SQLs. 

SPUFI is ok, but much better would be to see these in a GUI where graphical viewing is built in and saving and sharing results is much easier.  

This is not bad, but still a manual “island” process. Just Db2 and nothing else plus it must be triggered by humans. 

Getting there

A whole bunch of pre-written and custom SQLs.

This time, all run in Batch and the results are emailed to the auditor directly. These emails can “just sit there” until the auditor checks the results. Naturally, if anything is found, then the underlying data must still be there for a detailed analysis.

Better, as it is getting automatic but still not really “round”, as it is still Db2 in isolation…

Aha! The only true way

Use of LEEF or SYSLOGGER-style formats to export all audit data.

The data is then in a data-lake where SPLUNK, QRADAR et al can happily slice and dice their way through the data.

This is the best way!

You also get an extra bonus point for *removing* the data from the mainframe. As auditors *love* a single point of control, this is the only real way forward. It also pushes the Db2 data into the world of other data that auditors use and require.


Db2 Audit with “GIVE&TAKE” :


Software Engineering GmbH and SEGUS Inc are launching a new free Give&Take which this time is the Audit support from WorkLoadExpert.

If you would like to take part, then please just fire off an email to db2support@segus.com telling us who you are and which firm you work for and we will get in touch!

Give and Take 

By the way, it is called “Give&Take” because :

  • we Give you the software, for free, to run for a trial period, and
  • we would like to Take away what you think, feel, and find about the software after the trial period. 

More about Give&Take


TTFN, 

Roy Boxwell 

2019-12 Fun with Db2 12 PBR RPN

I have recently enhanced our SpaceAssuranceExpert (SAX) product to automatically help out if partitions, or partitioned indexes, are getting too big for their boots in a productive system.

SAX – What is it?

The idea behind SAX, is to stop any and all chances of getting a dreaded SQLCODE -904 in production, especially “out of the blue”.

Our idea was, in Db2 12, with the new PBR RPN to do an on-the-fly ALTER to the DSSIZE, just like SAX does now with the SECQTY to avoid running out of extents.

RTFM Time

A quick look in the manual tells you that :


it is an immediate ALTER (as long as you make the new DSSIZE larger than the old one!) and there are no package invalidations or REORGS required.


This is fantastic!

So I created a nice little PBR and then ran a horrible Cartesian join SPUFI to flood the first partition with data. This join had a TIME card of (,1) to limit it to one second of CPU before getting an Abend S322.

SAX Monitor

The SAX monitor reacted perfectly and did the TP alter to 513 GB DSSIZE (I had an increment size of 512 as a test), and got an SQLCODE -666 (I just love that SQLCODE…), as the INSERT was still running.

This ALTER was then internally queued to be attempted later, like in any other failure case.


All well and good.

Wham! Nasty errors !

Then I did the same for a DPSI on my PBR RPN… Oh dear!I got a nasty IO Error and then an even nastier ROLLBACK loop, meaning I had to cancel the IRLM to stop Db2… (There is an APAR for this problem PH18977.)

Rollback loop?

The ROLLBACK loop was caused by me choosing to use NOT LOGGED as a tablespace attribute. We have a finite amount of log space, and when the transaction was S322’d after one second of CPU,

the rollback could not find one of the archive logs and then we had to cold start Db2

– Not pretty!

Proper test!

I then wrote a couple of little test programs that actually COMMITted after 5000 inserts and then the ALTERs all worked as designed.


Do I worry too much about extended format and extended accessibility?

Next, I worried about the ominous “extended format and extended addressability“ attributes in the DATACLASS for a PBR RPN, and wondered what would happen if a customer has SAX running and it happily ALTERs a TP to say 6 GB when they can only address 4 GB…


IBM to the rescue!

Luckily for us, Db2 development had thought about this!

  • If you attempt to create a PBR RPN (even with a very small size) and your DATACLASS does not have the two attributes set, you get an error message 00D70008 telling you this detail.

  • If you ALTER an existing tablespace to be a PBR RPN and your DATACLASS does not have the two attributes set, then the ALTER works fine. But remember, this is a pending alter and you *must* do a REORG at the TS level with inline TP level copies. This REORG then fails – also with 00D70008.

So, in other words, SAX cannot hurt you here!


What about PBGs?

After all this we also considered PBGs. They have a limit as well – MAXPARTITIONS in their case. So we added an ability to also ALTER MAXPARTITIONS as well. Here you must be more careful though, as these ALTERs are still immediate *but* they invalidate any referring packages!

ABIND YES or NO?

If you work with ABIND set to YES all is good as the ALTER comes in, Db2 invalidates your packages and the auto rebind happens so fast that you do not even notice it happening, however, if you work with ABIND NO then any packages, even the package actually running and doing the inserts, will fail! If you are just using dynamic SQL then it is 100% OK otherwise – Buyer beware!

Aha!

I have opened an Analytics Idea (DB24ZOS-I-1057) to try and get this loophole closed, as I cannot see what access path change could be affected by going from 32 to, say, 36 MAXPARTITIONS.

Db2 keeps adding the parts dynamically and nothing happens then… Go figure… Anyway, if you would like it – Please vote for it!


There are already some nice comments attached to it:

  • DP commented

    this is just a limit in the catalog.  So how could access path be affected?  The actual growth of a partition doesn’t invalidate the package so how just changing the limit do so ?
  • BW commented

    I opened a Case on this asking why packages are being invalidated in this situation and it is still open waiting for a reply.
  • BD commented

    With only impact to catalog, not sure how Access Path would be impacted.  Seems wasteful and counter productive to invalidate packages.

Remember – You never stop learning!

As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

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:


  1. It cannot be longer than 64 bytes

  2. It cannot be versioned (So the OLDEST_VERSION and CURRENT_VERSION must be the same!)

  3. It cannot contain a TIMESTAMP column with TIMEZONE

  4. 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

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/


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.

Artificial Intelligence - Machine Learning for Db2 z/OS
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 32nd. 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 !!!

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
  1. Cloning
  2. Workload collection
  3. Dynamic & Static SQL storage
  4. IFCID376 & co
  5. EXPLAIN-& Access Path
  6. Execute the SQLs
  7. Db2-12 Test Review
  8. Zowe – Open Source z GUI – for CD

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.

  1. Logarithmically reduce the execution count and execute all of the SQL while monitoring all of the Db2 KPIs – On the Clone, of course.
  2. Then reset the world back to your starting Clone, do the FL change and then re-execute all the SQL again.
  3. 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:

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.


The IBM web based UI for Db2 z/OS


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

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
  1. PLAN_TABLE
  2. DSN_DETCOST_TABLE
  3. DSN_FILTER_TABLE
  4. DSN_PREDICAT_TABLE
  5. New Standard Explain SQL

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:

ONECOMPROWSThe number of rows qualified after applying
local predicates.
IMLEAFThe 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).
RDSROWThe number of data manager rows returned
(after all stage 1 predicates are applied).
IXSCAN_SKIP_DUPSWhether 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_SCREENWhether 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_OUTWhether 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 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.

STAGEThe stage that the predicate was evaluated.
MATCHING, SCREENING, PAGERANGE,
STAGE1, or STAGE2.
PUSHDOWNWhether 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_PREDIf 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