2023-07 Directory Details

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

What is it?

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

Whatya got?

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

Tables, Tables, Tables

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

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

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

Where’s the Beef?

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

So?

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

What is in it?

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

How many?

The SPTR had 6,630 tracks.

The SYSSPTSEC_DATA had 13,929 tracks.

The SYSSPTSEC_EXPL had 6,357 tracks.

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

What is “in” there?

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

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

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

Set to Stun!

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

FREE This!

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

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

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

FREE What?

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

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

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

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

REORGing the Directory

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

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

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

Pretty clear there!

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

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

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

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

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

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

How many after?

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

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

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

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

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

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

Directory Tips & Tricks

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

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

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

FASTSWITCH YES is ignored for both Catalog and Directory reorgs.

Any more Limits?

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

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

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

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

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

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

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

To REORG or not to REORG?

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

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

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

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

TTFN

Roy Boxwell

GIVE and TAKE Programs 4, 5, 6, 7

Db2 11+ 12 Audit+ SIEM, Access Path Recovery, Space Assurance, ZOWE and SQL Workload Performance


Limited free-of-Charge Db2 Applications


Previous Give & Take

This Program started in Europe in 2016. We have „GIVEn“ various free-of-charge Use Cases from our SQL WorkloadExpert for Db2 z/OS like:

1 Index Maintenance Costs

2 EXPLAIN Suppression

3 BIF Usage


What we GIVE in 2020

  • 90 days free trial – even in production
  • Two webinars covering installation and all pre-reqs
  • Two days – free of charge – onsite support
  • Offer of two days – free of charge – for potential realization of customer requests and enhancements

What we TAKE

  • Your Real World Experiences
  •  Your permission to use the gathered data in our presentations (Anonymous or, if you allow it, with your customer name)

In return, we receive the results. We’d like to share this inspiring experiences with you and communicate with local User Groups worldwide.


Current Give & Take 2020, Germany offers


4


Db2 11+12 Audit+ SIEM

with Optional Framework Eclipse or ZOWE IBM GUI

January-March 2020 (1Q) – Flyer Audit More


5


Access Path Recovery

April-June 2020 (2Q) – Presentation More


6


Space Assurance – K-no-w Limits

July – September 2020 (3Q) – PresentationFlyer SAX More

Db2 Space Assurance Recovery; give and Take Programm 4,5,6,7; SOFTWARE ENGINEERING GMBH


7


ZOWE IBM GUI and SQL Workload Performance for Db2 12

Oct.-December 2020 (4Q)


We TAKE the anonymized results for research

and will communicate with the local User Groups for discussions

Inspiring experiences

See the Customer Statements & more details on the past Give & Take


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:

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.


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

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

2018-08 SOUNDEX and other „cool“ features – Part six All new for Db2 12

Part six of my – everlasting – walk through of new Aggregate and Scalar functions.
Following with :  HASH & WRAP


Previous „SOUNDEX“ Newsletters

SCALARS

Now I move on to the new scalar functions, really only two new ones came with Db2 12, HASH and WRAP.

HASH it

Four new HASH Scalar functions are supplied. The first runs every time:

SELECT HEX(HASH_CRC32  ('ROY LIKES BEER A LOT!')) 
FROM SYSIBM.SYSDUMMYU;                            
---------+---------+---------+---------+---------+----------+---------+---------
4FA13CD
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Then I tried MD5

SELECT HEX(HASH_MD5    ('ROY LIKES BEER A LOT!'))
FROM SYSIBM.SYSDUMMYU;
---------+---------+---------+---------+---------+---------+---------+-----------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -20223, ERROR:  THE ENCRYPT_TDES OR DECRYPT FUNCTION
FAILED.  ENCRYPTION FACILITY NOT AVAILABLE 12 0
DSNT418I SQLSTATE   = 560BF SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXRBIN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 1032 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'00000408'  X'00000000'  X'00000000'  X'FFFFFFFF'
X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

Oops! What is that?

–  20223 THE OPERATION FAILED. ENCRYPTION FACILITY NOT AVAILABLE return-code, reason-code

Explanation: The encryption facility is not available, or not able to service the encryption or decryption request.

System action: The statement cannot be processed.

Programmer response: If the encryption facility is not installed, install it before you use the ENCRYPT_TDES, DECRYPT, HASH_CRC32, HASH_MD5, HASH_SHA1, HASH_SHA256 functions or data set encryption. If the encryption facility is installed, verify that it is working correctly. The returncode and reason-code might give further information about why this message was issued.

Digging deeper into the “ICSF and cryptographic coprocessor return and reason codes” docu

Reason codes for return code C (12)
Reason Code Hex (Decimal)Description
0 (0)ICSF is not available. One of the following situations is possible:

  • ICSF is not started

Yep – We have no crypto here in the labs, so no big surprise! But hey, at least the CRC32 worked!

Let’s WRAP it up

WRAP is useful for people like me that write software that other people buy! If you are using any of these:

  • CREATE FUNCTION (compiled SQL scalar)
  • CREATE FUNCTION (inlined SQL scalar)
  • CREATE PROCEDURE (SQL - native)
  • CREATE PROCEDURE (SQL table)
  • CREATE TRIGGER (basic)
  • CREATE TRIGGER (advanced)

Then you are aware that the code is in clear text and easy to read – Not good for my intellectual property rights! WRAP solves this dilemma by letting me obfuscate the code completely.

Here are two examples with the problems they can pose:

SELECT WRAP ('CREATE FUNCTION SALARY(WAGE DECFLOAT) RETURNS DECFLOAT RETURN WAGE * 40 * 52')
FROM SYSIBM.SYSDUMMY1
;
SELECT WRAP('
CREATE TRIGGER BOXWEL2.TESTTRIGH
   AFTER
   UPDATE OF COL1 ON BOXWEL2.TESTTRIG
   REFERENCING NEW AS NTABLE
   FOR EACH ROW
   MODE DB2SQL
   WHEN (NTABLE.COL1 = ''2'')
   UPDATE MVNXTEST.MVNXT80 A
   SET UT_STATUS = ''X''
')
FROM SYSIBM.SYSDUMMY1
;

The output looks like this:

CREATE FUNCTION SALARY(WAGE DECFLOAT) WRAPPED DSN12100 
ablGWmdiWmtGTmdCTmtaTmtmUntqUmJeUmZi2mdKZidaWmdaWmdaWmZG1mIaGicaGi6TRm8Eu4e30 AmlGUcEdToUnWtd2:hIfdEN_bBCpEmtl1lKH:UAzaqaa

One very long line that you must then “reverse” parse to get it back into, e.g. 72 byte chunks for SPUFI use.

The second example is even better, because of the whitespace being not encoded/encoded as well:

                                                            CREATE TRIGGER
BOXWEL2.TESTTRIGH                                           WRAPPED DSN1210
ablGWmdiWmtGTmdCTmtaTmtmUntqUmJeUmZm0otKXidaWmdaWmda1nJKGicaGicaGQ:TRO87Axb:VP8p_d8E9N887FL:EnRZ:8ltM:4sMnV5iyw3QGPUu0hDP4uwUK4lkDz0xvk3PU6lCMBGOCZar_sbJSQyaHYumRRSkATFXus8DesyjqaYmOLMD2HWdLt6GFDTxkNr4g8ht874tPZXN5ZIpJW4Xx15CI:VZ4f7ENGxV6_jxL4tVN0MFF2:tFR6EcD:g3nZurpPzOd2PYTuslWXKWXEWsWk2Q1KuT1VjPlY_MeNhCnpppEFEoQgp:dudFUcPhFyesEPIrPYTZxKAHlj1sDOKQTp1fYWoQ5nQBXOZGGtBreN2j9oSdSJNgf3roCpVJS8EFxdZ5DuoI_PDP5t7d1DPTL7Vtlp2EMsESTnl6s80KH4O13Wr72s4y56iQwBwB0KuOUYKxUZb:zNdchpguBUNZom2p2yyTpOXXkcHhKGnFnbBaUd2rlMbRmKZdgDiNwi7rEKHTLYulpLOa7kqrHKLymXZKurj64TGpF2IjXUmYkMbgbn4CFnOLk20rArSOIWIzbpajemWcmyHWmWdWfhyr8dJWZ8ghXJ5mF_nKr3ZyNUNw7Co9OqUxuEPuocFy4vUswVtOmonhr23d4VgBQz3Zf8nV2p5AVOEYLsJ7QDnNq7Bb132t3R7Rn603gF0PUi_PdCH6ef8Kmk4a8uWr2hkicHj8apO7hQf5w3Tc2tsvOCvOe5RaopCLKgg5BmnnYYzAl7lLiNGAvob1_Dd7PkoX51LDZ3QlBJUugQm5WsPJKKtwDjM81Wa


Notice that I had to double up the apostrophes due to this being passed as input to WRAP now – so you do have to watch out for that.

Remember too, that this is not encryption, it is just encoding and can be broken – It just makes it a tick harder to read your code, that is all. IBM writes:

Important
The encoding of the statement is meant to obfuscate the content and is not considered a form of strong encryption.


 

Well that wraps up this newsletter! Sorry….

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

2018-07 – SOUNDEX and other „cool“ features – Part five All new for Db2 12

Part five of my – everlasting – walk through of new Aggregate and Scalar functions. This time I will start with the new AGGREGATE functions introduced in Db2 12.

LISTAGG

This is mainly of interest due to the fact that it was the very first Agile Function Level feature in Db2. You must be at FL501 to use this in SQL otherwise you get a

DSNT408I SQLCODE = -4700 ATTEMPT TO USE NEW FUNCTION BEFORE FUNCTION LEVEL IS ACTIVATED

Or a

DSNT408I SQLCODE = -4743, ERROR:  ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL

What this function does, is basically a horizontal recursive join which can be sorted in a different sequence. Here’s the SQL example:

SELECT WORKDEPT,                                       
LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
AS EMPLOYEES                                           
FROM SAMPLE.EMP                                        
GROUP BY WORKDEPT;

Which returns:

---------+---------+---------+---------+---------+---------+---------+-----+
WORKDEPT  EMPLOYEES                                                        
---------+---------+---------+---------+---------+---------+---------+-----+
A00       HAAS, LUCCHESI, O'CONNELL 
B01       THOMPSON 
C01       KWAN, NICHOLLS, QUINTANA    
D11       ADAMSON, BROWN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER,
          YOSHIMURA
D21       JEFFERSON, JOHNSON, MARINO, PEREZ, PULASKI, SMITH
E01       GEYER  
E11       HENDERSON, PARKER, SCHNEIDER, SETRIGHT, SMITH  
E21       GOUNOT, LEE, MEHTA, SPENSER 
DSNE610I NUMBER OF ROWS DISPLAYED IS 8

So you can see it adds the LASTNAME and a comma repeatedly for each WORKDEPT value.

Db2 Catalog Docu

This function is actually very handy for one of the things I create for our programmers here in the labs. It is always a good idea to “know the catalog” and so I created a little word document years ago with all the tablespaces, tables, indexes and index columns with sort order so that they did not have to look in three separate books to find the data. Here is the SQL for listing out the tables with indexes:

SELECT SUBSTR(A.DBNAME, 1, 8) AS DB      
      ,SUBSTR(A.NAME, 1, 8 ) AS TS       
      ,SUBSTR(B.NAME, 1, 18) AS TABLE    
      ,SUBSTR(C.NAME, 1, 8 ) AS INDEX    
      ,B.TYPE                            
      ,CASE C.UNIQUERULE                 
        WHEN 'D' THEN '-'                
        ELSE 'Y'                         
       END AS U                          
      ,D.COLSEQ                          
      ,SUBSTR(D.COLNAME, 1, 18) AS COL   
      ,CASE D.ORDERING                   
        WHEN ' ' THEN 'I'                
        WHEN 'A' THEN '-'                
        WHEN 'D' THEN 'D'                
        WHEN 'R' THEN 'R'                
       END AS ORDER                      
FROM SYSIBM.SYSTABLESPACE A              
    ,SYSIBM.SYSTABLES     B              
    ,SYSIBM.SYSINDEXES    C              
    ,SYSIBM.SYSKEYS       D              
WHERE A.DBNAME IN ('DSNDB01','DSNDB06')  
  AND A.DBNAME  = B.DBNAME               
  AND A.NAME    = B.TSNAME               
  AND B.CREATOR = 'SYSIBM'               
  AND B.NAME    = C.TBNAME               
  AND B.CREATOR = C.TBCREATOR            
  AND C.NAME    = D.IXNAME                
  AND C.CREATOR = D.IXCREATOR            
ORDER BY DB, TS , TABLE , INDEX, D.COLSEQ ;

It returns 855 rows of data on my test Db2 12 Fl501 system:

---------+-------+-------+---+-------+------+--------+------------------+--
DB        TS      TABLE         INDEX     TYPE  U  COLSEQ  COL       ORDER
---------+-------+-------+---+-------+------+--------+------------------+--
DSNDB01   DBD01   DBDR          DSNDB01X   T   Y      1   DBID         -
DSNDB01   DBD01   DBDR          DSNDB01X   T   Y      2   SECTION      -
DSNDB01   SCT02   SCTR          DSNSCT02   T   Y      1   SCTNAME      -
DSNDB01   SPT01   SPTR          DSNSPT01   T   Y      1   SPTLOCID     -
DSNDB01   SPT01   SPTR          DSNSPT01   T   Y      2   SPTCOLID     -
DSNDB01   SPT01   SPTR          DSNSPT01   T   Y      3   SPTNAME      -

And ending with

DSNDB06  SYSXML   SYSXMLRELS    DSNXRX02   T   -      1  XMLTBOWNER    -
DSNDB06  SYSXML   SYSXMLRELS    DSNXRX02   T   -      2  XMLTBNAME     -
DSNDB06  SYSXML   SYSXMLSTRINGS DSNXSX01   T   Y      1  STRINGID      -
DSNDB06  SYSXML   SYSXMLSTRINGS DSNXSX02   T   Y      1  STRING        -
DSNE610I NUMBER OF ROWS DISPLAYED IS 855

Then I used my amazing WORD skills to move all the COL columns after each other to get a DOC file that looks like this:

DSNDB0n.     SYSIBM.       SYSIBM.      U  INDEX FIELD
DBD01        DBDR          DSNDB01X     Y  DBID.SECTION
SCT02        SCTR          DSNSCT02     Y  SCTNAME
SPT01        SPTR          DSNSPT01     Y  SPTLOCID.SPTCOLID.SPTNAME.etc.

Which, of course, the programmers loved, as it enabled them to quickly find which columns, in which sort order, (I convert the ORDER D COL column to be in bold by the way), are available on the catalog and directory tables. I started this with DB2 V5…

Now with LISTAGG the query looks like:

SELECT SUBSTR(A.DBNAME, 1, 8)                                 
      ,SUBSTR(A.NAME, 1, 8 )                                  
      ,SUBSTR(B.NAME, 1, 18)                                  
      ,SUBSTR(C.NAME, 1, 8 )                                  
      ,B.TYPE AS T                                            
      ,CASE C.UNIQUERULE                                      
        WHEN 'D' THEN '-'                                     
        ELSE 'Y'                                              
       END AS U                                               
      ,LISTAGG( CHAR(SUBSTR(D.COLNAME, 1, 18) CONCAT           
                     CASE D.ORDERING                          
                     WHEN ' ' THEN 'I'                        
                     WHEN 'A' THEN '-'                        
                     WHEN 'D' THEN 'D'                        
                     WHEN 'R' THEN 'R'                        
                     END                                      
                    )                                         
               , ' ') WITHIN GROUP (ORDER BY D.COLSEQ ASC)    
FROM SYSIBM.SYSTABLESPACE A                                   
    ,SYSIBM.SYSTABLES     B                                   
    ,SYSIBM.SYSINDEXES    C                                   
    ,SYSIBM.SYSKEYS       D                                   
WHERE A.DBNAME IN ('DSNDB01','DSNDB06')                       
  AND A.DBNAME  = B.DBNAME                                    
  AND A.NAME    = B.TSNAME                                    
  AND B.CREATOR = 'SYSIBM'                                    
  AND B.NAME    = C.TBNAME                                    
  AND B.CREATOR = C.TBCREATOR                                 
  AND C.NAME    = D.IXNAME
  AND C.CREATOR = D.IXCREATOR                                 
GROUP BY A.DBNAME, A.NAME, B.NAME, C.NAME, B.TYPE, C.UNIQUERULE
;

And the output:

---------+---------+-------+-----------+---------------------------+---
                                            T  U 
---------+---------+-------+-----------+---------------------------+---
DSNDB01   DBD01   DBDR            DSNDB01X  T  Y  DBID     - SECTION  -     
DSNDB01   SCT02   SCTR            DSNSCT02  T  Y  SCTNAME  -  
DSNDB01   SPT01   SPTR            DSNSPT01  T  Y  SPTLOCID - SPTCOLID - SPTNAME

And ends

DSNDB06   SYSXML  SYSXMLRELS      DSNXRX02  T  -  XMLTBOWNER - XMLTBNAME
DSNDB06   SYSXML  SYSXMLSTRINGS   DSNXSX01  T  Y  STRINGID   -         
DSNDB06   SYSXML  SYSXMLSTRINGS   DSNXSX02  T  Y  STRING     -         
DSNE610I NUMBER OF ROWS DISPLAYED IS 287

Which is a lot less data for my poor little fingers to work with!

Of course it is not perfect… the really big GOTCHA, is ORDER BY is *not* allowed!

PERCENTILES

For the next couple of examples, I will be using the table SAMPLE.EMP that contains these rows and columns of interest:

---------+---------+---------+---------+---------+-----
EMPNO   WORKDEPT       SALARY        BONUS         COMM
---------+---------+---------+---------+---------+-----
000210  D11          18270.00       400.00      1462.00
000190  D11          20450.00       400.00      1636.00
000180  D11          21340.00       500.00      1707.00
000160  D11          22250.00       400.00      1780.00
000170  D11          24680.00       500.00      1974.00
000150  D11          25280.00       500.00      2022.00
000200  D11          27740.00       600.00      2217.00
000220  D11          29840.00       600.00      2387.00
000060  D11          32250.00       600.00      2580.
DSNE610I NUMBER OF ROWS DISPLAYED IS 9

PERCENTILE_CONT

Calculates the requested percentile as a continuous value. Use this if you want a calculated value based upon your input. E.g.:

SELECT PERCENTILE_CONT(0,50)
WITHIN GROUP (ORDER BY SALARY ASC )
FROM SAMPLE.EMP
WHERE WORKDEPT = 'D11'
;
---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------
+0.2468000000000000E+05                    (EMPLOYEE 150 row 5)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Here you can see that I require the 50th percentile, and as the data happens to have nine rows it would be the 5th row If you look at the data you will see that that is indeed the case.

Now, reversing the direction of the percentile, I want the 90th descending value. This row does not exist in the input, so the function computes the value that will probably best fit

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------
SELECT PERCENTILE_CONT(0,90)                              
       WITHIN GROUP (ORDER BY SALARY DESC)                
FROM SAMPLE.EMP                                           
WHERE WORKDEPT = 'D11'                                    
;                                                         
---------+---------+---------+---------+---------+---------
                                                           
---------+---------+---------+---------+---------+---------
+0.2001400000000000E+05     (value between first and second rows)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

PERCENTILE_DISC

Calculates the requested percentile as a discrete value. Use this if you do not want a calculated value based upon your input. E.g.:

SELECT PERCENTILE_DISC(0,50)             
       WITHIN GROUP (ORDER BY SALARY ASC )
FROM SAMPLE.EMP                          
WHERE WORKDEPT = 'D11'                   
;                                        
---------+---------+---------+---------+--
                                        
---------+---------+---------+---------+--
   24680.00                 (EMPLOYEE 150 row 5)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Here you can see that I still require the 50th percentile, and, as the data happens to still have nine rows, it would be the 5th row again.

Now, reversing the direction of the percentile, I want the 90th descending value. This row does not exist in the input so the function returns the nearest input value.

DSNE616I STATEMENT EXECUTION WAS SUCCESSFU
---------+---------+---------+---------+--
SELECT PERCENTILE_DISC(0,90)             
       WITHIN GROUP (ORDER BY SALARY DESC)
FROM SAMPLE.EMP                          
WHERE WORKDEPT = 'D11'                   
;                                        
---------+---------+---------+---------+--
                                       
---------+---------+---------+---------+--
   18270.00        (90th is not in the input data but this is the nearest)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Michigan Db2 Users Group – Juni 2018

MDUG – Michigan Db2 Users Group – 6. Juni 2018

SEGUS & SOFTWARE ENGINEERING präsentieren

> Pdf Präsentation – 360° Audit

> Pdf Präsentation – Db2 12 – 12 months agile production experience with more focus on the Data and less on the base


360° Audit

  1. „360 degrees Audit“ which starts with an overview of what you should know about audit and what the new GDPR means to us as DBAs.

  2. Then goes a little into Auditor requirements,

  3. then shows you which IFCIDS give which data that you should have running at your shops,

  4. then runs through an example system and frame work
    See our Audit Product and documents


  5. then a list of latest KPMG Auditor questions from a couple of our customers which shows that they are getting agile as well…

  6. before rounding off with a run through from some GUI screen shots and the LEEF SIEM interface.

Db2 12 – 12 months agile production experience with more focus on the Data and less on the base

Migrating to Db2 12 is pretty much the same procedure like every 3 years. Then we get introduced to a variety of levels that control new features and functions being delivered and realize that the every-3-years-same-procedure is history. The levels, their dependencies and behavior are a fundamental change, which was implemented to control agile and more specifically it’s associated Continuous Delivery of future upgrades and fixes. As usual, it comes with benefits, but also challenges.

This presentation not only covers customers experiences migrating to Db2 12, but also how the changed maintenance approach has forced them to change existing procedures.

The second part is the daily operation and exploitation using Db2 12 enhancements from customers of different industries.

The over-all results aren’t bad, but a major conclusion is also that this Db2 version is significantly different than other versions before. It’s the first agile version with all its pros and cons. According to IBMs July 2017 announcement, the new database design “represents the elemental nature of Db2 (think periodic table) and connotes the fundamental importance of hybrid data management“. Where does this materialize in our daily work with Db2? Join this presentation and benefit from the experience of other Db2 customers around the world running Db2 12.


Mehr über Db2 Continuous Delivery – CD

Presentation Outline

  1. a – Db2 migration and maintenance – meet the agile and continuous delivery face of Db2.
    b – Db2 Code, Catalog, Function and Application Levels – differences, dependencies and how to successfully manage them.
  2. Running Db2 12 and exploiting new features – what’s hot and how to exploit it best?
  3. Emphasizing the Data – what has changed since July and what are the hybrid aspects managing Data?
  4. Top 10 list of tips and gotchas from a year of Db2 12 production Data bases around the world

Audience Experience:   Intermediate Advanced
Platform:                         Db2 z/OS
Presentation Length:     60 minutes
Presentation Category:  Database Administration, Performance Management, Db2 Migration

Speaker biography

Roy Boxwell has more than 32 years of experience in MVS, OS/390, and z/OS environments – 30 of those in Db2. He specializes in installation, migration, and performance monitoring and tuning. Roy leads the SEG development team responsible for the real time database maintenance solutions. He is also an active participant, speaker and contributor on the IDUG Db2 Listserv and sends out a monthly Db2 z/OS Newsletter.

Northeast Ohio Database Users Group – Juni 2018

NEODBUG – Northeast Ohio Database Users Group, OH, USA – 7. Juni 2018

SEGUS & SOFTWARE ENGINEERING präsentieren

Db2 12 – 12 months agile production experience with more focus on the Data and less on the base

> Pdf Präsentation

Migrating to Db2 12 is pretty much the same procedure like every 3 years. Then we get introduced to a variety of levels that control new features and functions being delivered and realize that the every-3-years-same-procedure is history. The levels, their dependencies and behavior are a fundamental change, which was implemented to control agile and more specifically it’s associated Continuous Delivery of future upgrades and fixes. As usual, it comes with benefits, but also challenges.

This presentation not only covers customers experiences migrating to Db2 12, but also how the changed maintenance approach has forced them to change existing procedures.

The second part is the daily operation and exploitation using Db2 12 enhancements from customers of different industries.

The over-all results aren’t bad, but a major conclusion is also that this Db2 version is significantly different than other versions before. It’s the first agile version with all its pros and cons. According to IBMs July 2017 announcement, the new database design “represents the elemental nature of Db2 (think periodic table) and connotes the fundamental importance of hybrid data management“. Where does this materialize in our daily work with Db2? Join this presentation and benefit from the experience of other Db2 customers around the world running Db2 12.


Mehr über Db2 Continuous Delivery – CD

Presentation Outline

  1. a – Db2 migration and maintenance – meet the agile and continuous delivery face of Db2.
    b – Db2 Code, Catalog, Function and Application Levels – differences, dependencies and how to successfully manage them.
  2. Running Db2 12 and exploiting new features – what’s hot and how to exploit it best?
  3. Emphasizing the Data – what has changed since July and what are the hybrid aspects managing Data?
  4. Top 10 list of tips and gotchas from a year of Db2 12 production Data bases around the world

Audience Experience:   Intermediate Advanced
Platform:                         Db2 z/OS
Presentation Length:     60 minutes
Presentation Category:  Database Administration, Performance Management, Db2 Migration

Speaker biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in Db2 operations and performance tuning, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under realworld recovery pressure. His activities cover EMEA, as well as North America through SE’s U.S. subsidiary, SEGUS Inc. As a member of SE’s Request Board he’s working closely with customers and the development labs.

Heart of America Db2 Users Group – Juni 2018

HOADb2UG – Heart of America Db2 Users Group, KS, USA – Juni 2018

SEGUS & SOFTWARE ENGINEERING präsentieren

Db2 12 – 12 months agile production experience with more focus on the Data and less on the base

> Pdf Präsentation

Migrating to Db2 12 is pretty much the same procedure like every 3 years. Then we get introduced to a variety of levels that control new features and functions being delivered and realize that the every-3-years-same-procedure is history. The levels, their dependencies and behavior are a fundamental change, which was implemented to control agile and more specifically it’s associated Continuous Delivery of future upgrades and fixes. As usual, it comes with benefits, but also challenges.

This presentation not only covers customers experiences migrating to Db2 12, but also how the changed maintenance approach has forced them to change existing procedures.

The second part is the daily operation and exploitation using Db2 12 enhancements from customers of different industries.

The over-all results aren’t bad, but a major conclusion is also that this Db2 version is significantly different than other versions before. It’s the first agile version with all its pros and cons. According to IBMs July 2017 announcement, the new database design “represents the elemental nature of Db2 (think periodic table) and connotes the fundamental importance of hybrid data management“. Where does this materialize in our daily work with Db2? Join this presentation and benefit from the experience of other Db2 customers around the world running Db2 12.


Mehr über Db2 Continuous Delivery – CD

Presentation Outline

  1. a – Db2 migration and maintenance – meet the agile and continuous delivery face of Db2.
    b – Db2 Code, Catalog, Function and Application Levels – differences, dependencies and how to successfully manage them.
  2. Running Db2 12 and exploiting new features – what’s hot and how to exploit it best?
  3. Emphasizing the Data – what has changed since July and what are the hybrid aspects managing Data?
  4. Top 10 list of tips and gotchas from a year of Db2 12 production Data bases around the world

Audience Experience:   Intermediate Advanced
Platform:                         Db2 z/OS
Presentation Length:     60 minutes
Presentation Category:  Database Administration, Performance Management, Db2 Migration

Speaker biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in Db2 operations and performance tuning, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under realworld recovery pressure. His activities cover EMEA, as well as North America through SE’s U.S. subsidiary, SEGUS Inc. As a member of SE’s Request Board he’s working closely with customers and the development labs.