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

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

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 – June 2018

MDUG – Michigan Db2 Users Group – June 6th, 2018

SEGUS & SOFTWARE ENGINEERING present:

> Pdf presentation – 360° Audit

> Pdf presentation – 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.

    Then goes a little into Auditor requirements,


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

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


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

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


More about 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 – June 2018

NEODBUG – Northeast Ohio Database Users Group, OH, USA – June 7th, 2018

SEGUS & SOFTWARE ENGINEERING present:

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

> Pdf Presentation

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.


More about 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 – June 2018

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

SEGUS & SOFTWARE ENGINEERING present:

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

> Pdf Presentation

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.


More about 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.

Atlanta DB2 Users Group June 2018

ADUG – Atlanta DB2 Users Group, June 2018 in Atlanta, Georgia, USA.
SEGUS & SOFTWARE ENGINEERING present

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

> Pdf Presentation

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.


More about 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.

Midwest Database Users Group – June 2018

MWDUG – Midwest Database Users Group, IL, USA – June 2018

SEGUS & SOFTWARE ENGINEERING present:

Total environment simulation – Workload Replay in an agile world

> Pdf Presentation

Is your production cloning for development/testing a quick and fully automated procedure? Does it consider differences of the underlying hardware and does it allow to choose and automatically run SQL workloads? Maybe your Db2 cloning needs an upgrade! By using a highly customizable XML script and by exploiting virtualization features we can make a clone YOUR clone and even make it behave like production. However, most importantly SQL Capture/Replay fills the gap of automatically running your production workload for testing. It captures production SQL 24/7 by exploiting highly efficient IFCID-via-OPx technology and it filters and prepares statements for replay. This enables shops to capture any SQL being ever executed and keep them in sets of workloads to be selected for testing. As an integrated part of Db2 cloning, this adds up to a comprehensive cloning solution, not only for objects and data, but even for production workloads. Learn how to integrate individual task for flexible automation and how to choose from four different levels of testing. The levels include simple anomaly alerting, access path verification, cloning and workload capture/replay to easily discover different behavior resulting from changes, like new application releases, or environment upgrades. Find out how easily you can pick and choose from KPI based test automation.


More about Db2 Continuous Delivery – CD

Presentation Outline

  1. Testing, virtualizing and simulating – the aspects of reliable quality assurance
  2. Db2 database/object cloning – what’s state of the art and what’s beyond
  3. XML commander – The comprehensive automation of flexibility, covering
    • FTP/Routing
    • JCL
    • ISPF file tailoring, panels, messages
    • Db2 Commands
  4. Emphasizing the Data – what has changed since July and what are the hybrid aspects managing Data
    – Anomaly alerting based on Incompatibility Change Indicators (ICIs)
    – Dyn./Stat.Access Path Change Detection e.g.via Plan Management
    – Cloning exploiting Backup System
    – Workload-KPI verification using SQL replay and KPI comparison
  5. 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.

Wisconsin Db2 Users Group – June 2018

WDUG – Wisconsin Db2 Users Group, WI, USA – June 2018

SEGUS & SOFTWARE ENGINEERING present

> Pdf Presentation

Total environment simulation – Workload Replay in an agile world

Is your production cloning for development/testing a quick and fully automated procedure? Does it consider differences of the underlying hardware and does it allow to choose and automatically run SQL workloads? Maybe your Db2 cloning needs an upgrade! By using a highly customizable XML script and by exploiting virtualization features we can make a clone YOUR clone and even make it behave like production. However, most importantly SQL Capture/Replay fills the gap of automatically running your production workload for testing. It captures production SQL 24/7 by exploiting highly efficient IFCID-via-OPx technology and it filters and prepares statements for replay. This enables shops to capture any SQL being ever executed and keep them in sets of workloads to be selected for testing. As an integrated part of Db2 cloning, this adds up to a comprehensive cloning solution, not only for objects and data, but even for production workloads. Learn how to integrate individual task for flexible automation and how to choose from four different levels of testing. The levels include simple anomaly alerting, access path verification, cloning and workload capture/replay to easily discover different behavior resulting from changes, like new application releases, or environment upgrades. Find out how easily you can pick and choose from KPI based test automation.


More about Db2 Continuous Delivery – CD

Presentation Outline

  1. Testing, virtualizing and simulating – the aspects of reliable quality assurance
  2. Db2 database/object cloning – what’s state of the art and what’s beyond
  3. XML commander – The comprehensive automation of flexibility, covering
    • FTP/Routing
    • JCL
    • ISPF file tailoring, panels, messages
    • Db2 Commands
  4. Emphasizing the Data – what has changed since July and what are the hybrid aspects managing Data
    – Anomaly alerting based on Incompatibility Change Indicators (ICIs)
    – Dyn./Stat.Access Path Change Detection e.g.via Plan Management
    – Cloning exploiting Backup System
    – Workload-KPI verification using SQL replay and KPI comparison
  5. 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.