2017-10 Db2 log size: How big is your LOG?

 

How to validate that your Db2 LOGs are OK ?

Sizing of LOGs changed quite a bit in Db2 11 and Db2 12, so I thought a little recap about the correct sizing and ZPARMS was in order this month.

– Db2 Active logs – how many?

The perennial favourite “just enough” is of course the correct answer! Naturally you want to make sure that about 24 hours’ worth of data is available on your active logs as reading the archives, especially if they have been migrated to tape, is a real performance killer during restart and rollback processing!

The range of active logs you can have is from 2 to 93 with 3 being the default.

– Db2 Archive logs – how many?

Another perennial favourite “just enough” is of course the correct answer! (See more details later in this newsletter.)

The range of archive logs you can have is from 10 to 10,000, with 10,000 being the default.

Where am I?

How can you find out what your system is doing? Simply run the DSNJU004 BSDS stand-alone print routine to look at the current data. Here is an example JCL to do it all for you:

//BSDS     EXEC PGM=DSNJU004
//STEPLIB  DD DSN=<your.exit.lib>,DISP=SHR
//         DD DSN=<your.load.lib>,DISP=SHR
//SYSUT1   DD DSN=<your.BSDS 01>,DISP=SHR
//SYSUT2   DD DSN=<your.BSDS 02>,DISP=SHR
//SYSPRINT DD SYSOUT=*

And here is the output from my little Db2 12 subsystem, cut down to show just the bits I want to use!

ACTIVE LOG COPY 1 DATA SETS
START RBA/TIME       END RBA/TIME          DATE/LTIME DATA SET INFORMATION
-------------------- --------------------- ---------- --------------------
000000000013516C0000  0000000000135387FFFF  2016.068 DSN=DC10.LOGCOPY1.DS01
2017.307  12:32:08.6  2017.308  20:54:21.1  17:27    STATUS=REUSABLE
00000000001353880000  00000000001355A3FFFF  2016.068 DSN=DC10.LOGCOPY1.DS02
2017.308  20:54:21.1  2017.310  07:31:00.0  17:27    STATUS=REUSABLE
00000000001355A40000  00000000001357BFFFFF  2016.068 DSN=DC10.LOGCOPY1.DS03
2017.310  07:31:00.0  ........  ..........  17:27    STATUS=NOTREUSABLE

ARCHIVE LOG COPY 1 DATA SETS
START RBA/TIME        END RBA/TIME         DATE/LTIME DATA SET INFORMATION
--------------------  -------------------- ---------- --------------------
00000000000000000000  000000000000021BFFFF  2016.068  DSN=DC10.ARCHLOG1.D16068.T1743011.A0000001
2016.068  18:37:21.8  2016.068  18:43:00.9  17:43     VOL=SE121D UNIT=SYSALLDA
                                                                 CATALOGUED
000000000000021C0000  0000000000000437FFFF  2016.068  DSN=DC10.ARCHLOG1.D16068.T1743391.A0000002
2016.068  18:43:00.9  2016.068  18:43:39.1  17:43     VOL=SE121D UNIT=SYSALLD
                                                                 CATALOGUED
00000000000004380000  0000000000000653FFFF  2016.069  DSN=DC10.ARCHLOG1.D16069.T0756574.A0000003
2016.068  18:43:39.1  2016.069  08:56:57.3  7:56      VOL=SE121D UNIT=SYSALLD
                                                                 CATALOGUED

From this you can see that I have three active logs that last about one to two days, and the complete archives from the first install are still available – that will probably not be true in your case!

Now, much later in the output, you can see where I am today:

000000000013516C0000  0000000000135387FFFF  2017.308   
DSN=DC10.ARCHLOG1.D17308.T1954212.A0002638
2017.307  12:32:08.6   2017.308  20:54:21.1 19:54 VOL=SE123 UNIT=SYSALLD
                                                            CATALOGUED
00000000001353880000 00000000001355A3FFFF   2017.310
DSN=DC10.ARCHLOG1.D17310.T0631002.A0002639
2017.308 20:54:21.1    2017.310  07:31:00.0 6:31 VOL=SE123F UNIT=SYSALLD
                                                            CATALOGUED

Then the COPY 2 data where you can see that I do not even archive the COPY 2 logs. Again this should not be the case in your shop.

ACTIVE LOG COPY 2 DATA SETS
START RBA/TIME        END RBA/TIME         DATE/LTIME  DATA SET INFORMATION
--------------------  -------------------- ----------  -------------------- 
000000000013516C0000  0000000000135387FFFF  2016.068  DSN=DC10.LOGCOPY2.DS01
2017.307  12:32:08.6  2017.308  20:54:21.1  17:27     STATUS=REUSABLE
00000000001353880000  00000000001355A3FFFF  2016.068  DSN=DC10.LOGCOPY2.DS02
2017.308  20:54:21.1  2017.310  07:31:00.0  17:27     STATUS=REUSABLE
00000000001355A40000  00000000001357BFFFFF  2016.068  DSN=DC10.LOGCOPY2.DS03
2017.310  07:31:00.0    ........  ........  17:27     STATUS=NOTREUSABLE
ARCHIVE LOG COPY 2 DATA SETS
NO ARCHIVE DATA SETS DEFINED FOR THIS COPY

From this data you can work out a few simple things:

1)      How often do you fill an active log?

2)      How many archive logs do you have in the BSDS, and do they actually still exist?

 

  • You can now also check that all your active logs have the same dataset characteristics (Primary Quantity, Secondary, etc.).
  • If you are filling up more than one log per 10 minutes, you may want to check the size of your logs. The size of the active log can be set to an absolute physical maximum of 5,825 cylinders (87,375 TRKs) for non-EAV and 5,817 cylinders (87,255 TRKs) for EAV. Why the strange difference? Because EAV can only allocate in blocks of 21 cylinders due to the way the EAV volumes work. (See later for more information about using more than 65,535 TRKs for an archive log though!)
  • Space allocation:
    The initial allocation of space is done through the Install CLIST and the tailored sample JCL in the DSNTIJIN member. That is where you can override whatever values are generated by Db2 into “better” ones for your system. In the bad old days the allocation was in RECORDS and today I see KILOBYTES most of the time – which is still a bit of a headache when you want to allocate in CYLINDERS but it is all doable, as the DEFINE CLUSTER syntax allows these size definitions:
CYLINDERS(primary)
KILOBYTES(primary)
MEGABYTES(primary)
RECORDS(primary)
TRACKS(primary)

Log ZPARM of Interest

For the log, there is really only one ZPARM of any real importance when it comes to performance and that is OUTBUFF. Set this to be the biggest you can “afford” in terms of real memory, as it takes the memory as fixed! 400,000 KB is the current max, and this is the IBM recommended value. Reading from memory for rollback is a lot quicker than from active log, which is a lot faster than reading the archive log.

From the documentation:

The larger the output buffer, the more likely that a log read request can return the required log records without needing I/O operations. This is a particularly important consideration if the DB2 subsystem is to be used with a data replication product that uses IFCID 306. In DB2 12, log buffers are permanently page-fixed. When you estimate real storage usage, you must use the entire size that you specify for the OUTBUFF parameter. To avoid page-fixing more storage than necessary, carefully choose the setting for OUTBUFF.
 
Choose the largest size that your system can tolerate for the log output buffer. Because the pages for the log output buffer are permanently fixed in real storage, choose the largest size that you can dedicate in real storage. A larger size for the log output buffer might decrease the number of forced I/O operations that occur because additional buffers are unavailable, and can also reduce the number of wait conditions.

Archive logs are different

The size of the archive logs and how many are recorded in the BSDS are input parameters on installation panel DSNTIPA:

DSNTIPA INSTALL DB2 - ARCHIVE LOG DATA SET PARAMETERS
===> _
Enter data below:
1 PRIMARY QUANTITY ===> 125    Primary space allocation in cylinders
2 SECONDARY QTY    ===> 15     Secondary space allocation in cylinders
3 DEVICE TYPE 1    ===> TAPE   Unit name for COPY1 archive logs
4 DEVICE TYPE 2    ===>        Unit name for COPY2 archive logs
5 BLOCK SIZE       ===> 24576  Rounded up to 4096 multiple
6 READ TAPE UNITS  ===> 2      Number of allocated read tape units
7 DEALLOC PERIOD   ===> 0      Time interval to deallocate tape units
8 RECORDING MAX    ===> 10000  Number of data sets recorded in BSDS

In Db2 10 and Db2 11 you also had an allocation unit (ALCUNIT ZPARM) input field with valid values of BLK, TRK or CYL with a default of BLK.

The PRIQTY now has a range from 1 to 4,369 and a default of 125. (Remember the absolute physical maximum earlier of 5,825/5,817 CYLs? That is why you can/should edit the DSNTIJIN member!)

If migrating from Db2 11, the ALCUNIT value is used to convert from BLK or TRK to CYLs in Db2 12. If not yet in Db2 12 then the ALCUNIT tells you what the allocation unit is for the PRIQTY in either BLK, TRK or CYL with a range of 1 to 999,999.

Some further info about the size of the archive logs here. Remember that they are flat files *not* VSAM Linear Datasets:

By default, DFSMS Direct Access Device Space Management (DADSM) limits the space allocation on a single volume to less than 65535 tracks. Therefore, if the archive log data set size can be greater than or equal to 65535 tracks, you need to specify a primary space quantity of less than 65535 tracks. This action forces the archive log data set to extend to a second volume.

Alternatively, the archive log data sets can be allocated by a DFSMS data class that has a DSNMTYP (data set name type) setting of LARGE or EXT. In this case, you can specify a primary space quantity larger than 65535 tracks without extending to a second volume. LARGE indicates that data sets in the data class are to be allocated in large physical sequential format. EXT indicates that data sets in the data class are to be allocated in extended physical sequential format. A setting of EXT is recommended, and it is required for striping of data sets. If you specify EXT, also set the IFEXT (if extended) parameter to R (required) rather than P (preferred).

The SECQTY has a range from 1 to 4,369 and a default of 15. Same rules for ALCUNIT and switching to CYL in Db2 12 apply here as per the PRIQTY.

The recording max just tells you how many archive log dataset names the BSDS can remember. The documentation mentions two very important facts about this number:

When this number is exceeded, recording resumes at the beginning of the BSDS.

You must create image copies of all DB2 objects, probably several times, before the archive log data sets are discarded. If you fail to retain an adequate number of archive log data sets for all the image copies, you might need to cold start or reinstall DB2. In both cases, data is lost.

So there are two top tips for you! Check if you have had a wrap around and check if you have datasets hanging around that Db2 can no longer allocate and use.

Striped or not?

Finally, for logs, the striping of the active logs is nowadays not recommended, as devices are so much faster, but striping and/or compression of the archive logs is still recommended if space is an issue.

From the Db2 Documentation:

In most cases, do not stripe active log data sets. You can use DFSMS to the stripe the logs, but striping is generally unnecessary with the latest devices. Striping increases the number of I/Os, which can increase CPU time and lead to potentially greater DB2 commit times. Striping might improve the performance of batch insert jobs, but it might also harm the performance of online transaction processing. Striping is especially risky for performance if you replicate the logs over long distances.
Consider striping and compressing archive log data sets by using DFSMS. Doing so might speed up the time to offload the logs and the time to recover by using archive logs. However, the performance of DFSMS striping and compression depends on the z/OS release and the types of hardware that you use.

Check your CHECKPOINTS

Getting back to the output of the DSNJU004 job, a few more things like conditional restart recs etc. are output and then the Checkpoint Queue:

 

                    CHECKPOINT QUEUE                
                08:00:31 NOVEMBER 06, 2017          
TIME OF CHECKPOINT       07:31:03 NOVEMBER 06, 2017 
BEGIN CHECKPOINT RBA            00000000001355B750DC
END CHECKPOINT RBA              00000000001355B81B18
END CHECKPOINT STCK             00D365BB89179A529200
TIME OF CHECKPOINT       20:54:23 NOVEMBER 04, 2017 
BEGIN CHECKPOINT RBA            000000000013538813C5
END CHECKPOINT RBA              00000000001353889D76
END CHECKPOINT STCK             00D363EB5D1777CFB000
TIME OF CHECKPOINT       12:32:09 NOVEMBER 03, 2017 
BEGIN CHECKPOINT RBA            00000000001351723326
END CHECKPOINT RBA              0000000000135172D899
END CHECKPOINT STCK             00D362393D4E6F748C00
TIME OF CHECKPOINT       07:30:11 NOVEMBER 03, 2017 
BEGIN CHECKPOINT RBA            0000000000134F5915DD
END CHECKPOINT RBA              0000000000134F59C445
END CHECKPOINT STCK             00D361F5BEB5A941CA00
TIME OF CHECKPOINT       07:42:59 NOVEMBER 01, 2017 
BEGIN CHECKPOINT RBA            0000000000134D34EB53
END CHECKPOINT RBA              0000000000134D357022
END CHECKPOINT STCK             00D35F74E054AE870800

You can see how busy my Db2 system is, anyway your data will be a lot different I am sure!

Checkpoint ZPARMS of interest


CHKTYPE 

LOGRECS/MINUTES/BOTH         MINUTES is default and is what I recommend. This parameter can still have the old value “SINGLE” which then respects the CHKFREQ value as if it were minutes or log records depending on the size of the Number. Change this to Minutes!


CHKFREQ

If LOGRECS how many log records before a checkpoint 1,000 – 16,000,000
If MINUTES how many minutes before a checkpoint 1 – 60


CHKLOGR

If BOTH how many log records before a checkpoint 1,000 – 99,999,999


CHKMINS

If BOTH how many minutes before doing a Checkpoint. 1 – 1439


IBM, and I, recommend a checkpoint every five minutes or so to get optimum rollback and log usage. Naturally your mileage may vary. Also remember that Db2 writes a checkpoint at active log change as well, so try and guarantee that at least ten checkpoints’ “worth” of data fits onto one log dataset.

One extra note arrived in Db2 11:

If the CHECKPOINT TYPE field is set to BOTH, DB2 prevents log checkpoints from being taken too frequently and degrading performance. Log checkpoints are scheduled, but not necessarily immediately taken, when the number of minutes that passes reaches the value of CHKMINS.


Following all this advice should mean that your logs settle down and your system runs a little bit smoother!

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

 

Roy Boxwell

2017-04 Db2 ZPARM : Small ZPARM – Big effect!

Do you know how important it is to check the ZPARM EDM SKELETON POOL size to improve your I/O rate and lower your CPU?

Do you know how to set it?

From the frontline

I was checking and verifying our WorkLoadExpert (WLX) system at a customer’s site recently, and was struck by the size of the EDMPOOL Static SQL Cache.

SSC to you

I call this the „SSC“ (Static Statement Cache). What was *really* weird, is that it contained only about 450 statements – even though the EDMSTMTC was set to 1,500,000 KB !!! The Dynamic Statement Cache (DSC) contained about 38,000 Statements.

Different Horses, Different Courses

Now at the same time I happened to get an e-mail from our technical support, which contained a summary of a DSC and a SSC Snap at another customer site. They were getting 50,000 SSC statements, however the DSC size was 4,000,000 KB. This got me seriously wondering about why the SSC was so low at my current site…

Time to upgrade your EDMPOOL!

As luck would have it, an IBM Technical guy was also on site, and so we both peered into the innards of Db2.  I noticed right away that the I/O rate for the EDM SKELETON POOL was *crazy* high! The ZPARM EDM_SKELETON_POOL was set to 10,000 KB which is *crazy* low! (The default is 10,240 KB) We agreed to raise it to 150,000 KB on one member and, if all ok, roll out the change to all the other members in the Group.

Changeable online ZPARM and instantly used

It is an online changeable ZPARM that is instantly used, and as we watched, we saw the I/O to the EDM_SKELETON_POOL dropping and dropping and dropping until it flat lined! Yep – we got the I/O down to zero. Just think of the pay back saving Db2 from searching for free chains, externalizing IFCIDs etc.

Not just SSC got a boost

What I then noticed was that the DSC usage improved too! Why? Well remember that all SQL needs to be “attached” to a package? Normally a “dummy” like SYSLH… is one of many. But even these packages must be in the pool. When they are cast out then the related DSC entries are *also* cast out!

Big Bottom Line

End of the lesson was:


  • DSC now contains 78,000 SQLs

  • SSC now contains 70,000 SQLs

  • I/O rate to EDM_SKELETON_POOL effectively Zero

  • IFCID 401 (Flushed static SQL) from 3,200,000 per hour down to Zero

  • IFCID 316 (Flushed dynamic SQL) from 36,000 per hour down to 4,000

Just stop to think what this means for the System-wide CPU and I/O rates…

Check it now!

Set the Db2 ZPARM EDM_SKELETON_POOL size

Please check your EDM_SKELETON_POOL size now, introduced in Db2 9, with a default value of 10,240

 Increase it to at least 150,000 if you can!  –

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

 


Mehr über Db2 SQL Workload Analyse

SQL Workload Expert for Db2 z/OS offers a complete review of all KPIs (CPU, elapsed, IO etc.) from all SQL (Dynamic and Static) that have executed on the entire system.


BIF HealthCheck Licensed Freeware for DB2 10

Detect static and dynamic SQL and match to the relevant collection, packages,…

BIF/ICI HealthCheck overview (Built-in Function Checker for DB2 z/OS)

BIF/ICI HealthCheck reports the following BIF incompatibilities in DB2 10

  • Execution of the DB2 9 for z/OS version of SYSIBM.CHAR(DECIMAL-EXPR)
  • Execution of the DB2 9 for z/OS version of SYSIBM.VARCHAR(DECIMAL-EXPR), CAST (DECIMAL AS VARCHAR), OR CAST (DECIMAL AS CHAR)

  • Use of an unsupported character string representation of a TIMESTAMP
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name ARRAY_EXISTS
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name CUBE
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name ROLLUP

  • Execution of a non-Java client that called a Stored Procedure (SP) that is on the DB2 for z/OS Data Server, while subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV (the Data Server returned output argument values whose data types matched the data types of the call statement arguments).

  • Execution of a SQL statement by a client non-Java application that included an unsupported conversion from a string type to a numeric type, while the DB2 z/OS Data Server environment was one of the following (the Data Server issues SQLCODE -301)
    • In version 10 Conversion Mode (CM)
    • In version 10 New-Function Mode (NFM) and implicit casting was disabled because subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV, or DISABLE_IMPCAST_NJ

 

BIF/ICI incompatibilities in DB2 11

 

Mehr über BIF

BIF-Usage

Präsentation

BIF KompatibilitätDB2 10 Kompatibilität Mode

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von DB2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
„Give and Take
Program“
Seite
Give and Take
Program
We have „GIVEn“ various free-of-charge Use Cases from SQL Workload Expert for DB2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage 
BIF HealthCheck – This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions
BIF Usage

„Give and Take
Program 3“

Kundenmeinungen
Präsentation


Customer Comments
Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

 

 

BIF HealthCheck Licensed Freeware for DB2 11

Detect static and dynamic SQL and match to the relevant collection, packages,…

BIF HealtchCheck overview (Built-in Function Checker for DB2 z/OS)

BIF HealthCheck reports the following BIF incompatibilities in DB2 11

  • Execution of the DB2 9 for z/OS version of SYSIBM.CHAR(DECIMAL-EXPR)
  • Execution of the DB2 9 for z/OS version of SYSIBM.VARCHAR(DECIMAL-EXPR), CAST (DECIMAL AS VARCHAR), OR CAST (DECIMAL AS CHAR)

  • Use of an unsupported character string representation of a TIMESTAMP
  • Use of the DB2 10 for z/OS default SQL path instead of the V11 path, which has more implicit Schemas

  • Execution of a non-Java client that called a Stored Procedure (SP) that is on the DB2 for z/OS Data Server, while subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV (the Data Server returned output argument values whose data types matched the data types of the call statement arguments).
  • Execution of an insert statement that inserts into an XML column without the XMLDOCUMENT function, which generates SQLCODE -20345 on a DB2 release prior to V11, but does not generate an error starting in V11

  • V10 XPATH evaluation behavior was in effect, which resulted in an error (e.g. a data type conversion error occurred for a predicate that would otherwise be evaluated to false.). Starting in V11, such errors might be suppressed

  • Execution of a SQL statement by a client non-Java, or Java application that included an unsupported conversion from a string type to a numeric type, or from a numeric type to a string type while the DB2 z/OS Data Server environment was one of the following (the Data Server issues SQLCODE -301)

o The Data Server was in version 11 New-Function Mode (NFM)
o APPLICATION COMPATIBILITY was set to V10R1
o Implicit casting was disabled because subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV, or DISABLE_IMPCAST_NJV

BIF incompatibilities in DB2 10

 

Mehr über BIF

BIF-Usage

Präsentation

BIF KompatibilitätDB2 10 Kompatibilität Mode

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von DB2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
„Give and Take
Program“
Seite
Give and Take
Program
We have „GIVEn“ various free-of-charge Use Cases from SQL Workload Expert for DB2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage 
BIF HealtchCheck (Freeeware)– This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions
BIF Usage

„Give and Take
Program 3“

Kundenmeinungen
Präsentation

Inspiring experiences

[Customer Comments]

Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

2015-11 z/OS Quick Security Audit – part 2

Which security values and security settings should never be left as default?

Let’s list your ROLES and Authorization IDs with some SQL queries and check the security ZPARMs

 

To complete this two part series, I want to do a deep dive today, down into the security innards of DB2, and to wrap up with a review of default values that can cause security concerns. A long time ago ROLEs were introduced, and, as you saw in the last newsletter, nearly all OWNERs have an associated indicator of whether they are a Role or an Authorization Id. With Roles came Trusted Contexts, and I hope that all of you out there using remote access have set up a whole bunch of Trusted Contexts.

Part 1 proposes a review of any and all GRANTs on the DB2 Directory and Catalog tables. Is your DB2 Catalog opened with a PUBLIC grant? Do you know how your DB2 z/OS System is looking on the security side ?

Role playing

To start this time, we will review the Roles that you have currently defined:

SELECT NAME
     , DEFINER
     , CASE DEFINERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DEFINERTYPE
 FROM SYSIBM.SYSROLES
 ORDER BY 1 , 2
 FETCH FIRST 50 ROWS ONLY
 WITH UR
 ;

If this finds no rows, then that is either brilliant – you have no dynamic access – or pretty bad – you are not using Roles.

Having found your list of Roles, make sure that they are all allowed and correct. Remove any that are not supposed to be there!

 

It’s all a matter of context

Now the three Trusted Context Tables are to be queried:

SELECT ENABLED
 , NAME
 , CONTEXTID
 , DEFINER
 , CASE DEFINERTYPE
 WHEN 'L' THEN 'ROLE   '
 WHEN ' ' THEN 'AUTH ID'
 ELSE          'UNKNOWN'
 END        AS DEFINERTYPE
 , DEFAULTROLE
 , CASE OBJECTOWNERTYPE
 WHEN 'L' THEN 'ROLE   '
 WHEN ' ' THEN 'AUTH ID'
 ELSE          'UNKNOWN'
 END        AS OBJECTOWNERTYPE
 , ALLOWPUBLIC
 , AUTHENTICATEPUBLIC
 , DEFAULTSECURITYLABEL
 FROM SYSIBM.SYSCONTEXT
 ORDER BY 3
 FETCH FIRST 50 ROWS ONLY
 WITH UR
 ;

This query just shows you what you have at the moment and whether or not it is active, plus the basic security information. The next two queries list out the actual details:

SELECT CONTEXTID
     , AUTHID 
     , AUTHENTICATE
     , ROLE
     , SECURITYLABEL
FROM SYSIBM.SYSCONTEXTAUTHIDS
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT CONTEXTID
     , NAME
     , VALUE
FROM SYSIBM.SYSCTXTTRUSTATTRS
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Note that the key is always the CONTEXTID. The NAME and VALUE pair is where you actually find the details of how a Trusted Context is defined. Again, check and validate that all these values – and especially the IP addresses – are still valid.

 

Do I have your Permission?

Masks and Permissions came in a while ago but haven’t gained much traction in the DB2 user community yet. However, here is an SQL to show you what you do have:

 

SELECT ENABLE
     , CASE CONTROL_TYPE
       WHEN 'R' THEN 'ROW PERMISSION'
       WHEN 'M' THEN 'COLUMN MASK   '
       ELSE          'UNKNOWN       '
       END        AS CONTROL_TYPE
     , SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSCONTROLS
ORDER BY 2 , 3 , 4
FETCH FIRST 50 ROWS ONLY
WITH UR
;

You can then easily see whether or not the permission/mask is active, or not.

 

SECADM to the rescue

If running with SECADM and Audit Policies, then this little query will show you what is going on:

SELECT AUDITPOLICYNAME
     , OBJECTSCHEMA
     , OBJECTNAME
     , CASE OBJECTTYPE
       WHEN 'C' THEN 'CLONE TABLE                  '
       WHEN 'P' THEN 'IMPLICIT TABLE FOR XML COLUMN'
       WHEN 'T' THEN 'TABLE                        '
       WHEN ' ' THEN 'XML, CLONE OR TABLE          '
       ELSE          'ERROR AT POLICY START TIME   '
       END AS OBJECTTYPE
     , CREATEDTS
     , ALTEREDTS
     , CASE DB2START
       WHEN 'Y' THEN 'STARTED AT DB2 START                '
       WHEN 'S' THEN 'STARTED AT DB2 START. SECADM TO STOP'
       WHEN 'N' THEN 'POLICY NOT STARTED AT DB2 START     '
       ELSE          'UNKNOWN                             '
       END AS DB2START
     , CASE CHECKING
       WHEN 'A' THEN 'AUDIT ALL FAILURES        '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS CHECKING
     , CASE VALIDATE
       WHEN 'A' THEN 'AUDIT ALL FAILURES        '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS VALIDATE
     , CASE OBJMAINT
       WHEN 'A' THEN 'AUDIT ALTER/DROP          '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS OBJMAINT
     , CASE EXECUTE
       WHEN 'A' THEN 'AUDIT FIRST ACCESS        '
       WHEN 'C' THEN 'AUDIT FIRST UPDATE        '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS EXECUTE
     , CASE CONTEXT
       WHEN 'A' THEN 'AUDIT ALL UTILITIES       '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS CONTEXT
     , CASE SECMAINT
       WHEN 'A' THEN 'AUDIT ALL                 '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS SECMAINT
     , SYSADMIN
     , DBADMIN
     , DBNAME
     , COLLID
FROM SYSIBM.SYSAUDITPOLICIES
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Now you can see what is happening with your policies, and are they working as designed?

 

Are you a ROLE model?

The next large set of queries returns *all* AUTH relevant DB2 catalog data from any DB2 Table that has any possible link to a ROLE:

 

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN 'P' THEN 'PACKAGE'
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , CREATOR
     , TNAME AS NAME
     , COLNAME
     , CASE PRIVILEGE
       WHEN 'R' THEN 'REFERENCES'
       WHEN ' ' THEN 'UPDATE    '
       ELSE          'UNKNOWN   '
       END AS PRIVILEGE
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSCOLAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR , NAME, COLNAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'C' THEN 'DBCTRL    '
       WHEN 'D' THEN 'DBADM     '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'M' THEN 'DBMAINT   '
       WHEN 'S' THEN 'SYSADM    '
       WHEN ' ' THEN 'N/A       '
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , CREATETABAUTH
     , CREATETSAUTH
     , DBADMAUTH
     , DBCTRLAUTH
     , DBMAINTAUTH
     , DISPLAYDBAUTH
     , DROPAUTH
     , IMAGCOPYAUTH
     , LOADAUTH
     , REORGAUTH
     , RECOVERDBAUTH
     , REPAIRAUTH
     , STARTDBAUTH
     , STATSAUTH
     , STOPAUTH
FROM SYSIBM.SYSDBAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN 'P' THEN 'PLAN   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , COLLID
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'A' THEN 'PACKADM (COLLECTION *)    '
       WHEN 'C' THEN 'DBCTRL                    '
       WHEN 'D' THEN 'DBADM                     '
       WHEN 'E' THEN 'SECADM                    '
       WHEN 'G' THEN 'ACCESSCTRL                '
       WHEN 'L' THEN 'SYSCTRL                   '
       WHEN 'M' THEN 'DBMAINT                   '
       WHEN 'P' THEN 'PACKADM (NOT COLLECTION *)'
       WHEN 'S' THEN 'SYSADM                    '
       WHEN 'T' THEN 'DATAACCESS                '
       WHEN ' ' THEN 'N/A                       '
       ELSE          'UNKNOWN                   '
       END AS AUTHHOWGOT
     , BINDAUTH
     , COPYAUTH
     , EXECUTEAUTH
FROM SYSIBM.SYSPACKAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'C' THEN 'DBCTRL    '
       WHEN 'D' THEN 'DBADM     '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'M' THEN 'DBMAINT   '
       WHEN 'S' THEN 'SYSADM    '
       WHEN ' ' THEN 'N/A       '
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , BINDAUTH
     , EXECUTEAUTH
FROM SYSIBM.SYSPLANAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , QUALIFIER
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'A' THEN 'PACKADM (COLLECTION *)    '
       WHEN 'C' THEN 'DBCTRL                    '
       WHEN 'D' THEN 'DBADM                     '
       WHEN 'E' THEN 'SECADM                    '
       WHEN 'G' THEN 'ACCESSCTRL                '
       WHEN 'L' THEN 'SYSCTRL                   '
       WHEN 'M' THEN 'DBMAINT                   '
       WHEN 'P' THEN 'PACKADM (NOT COLLECTION *)'
       WHEN 'S' THEN 'SYSADM                    '
       WHEN 'T' THEN 'DATAACCESS                '
       WHEN ' ' THEN 'N/A                       '
       ELSE          'UNKNOWN                   '
       END AS AUTHHOWGOT
     , CASE OBTYPE
       WHEN 'B' THEN 'BUFFER POOL  '
       WHEN 'C' THEN 'COLLECTION   '
       WHEN 'D' THEN 'DISTINCT TYPE'
       WHEN 'R' THEN 'TABLE SPACE  '
       WHEN 'S' THEN 'STORAGE GROUP'
       WHEN 'J' THEN 'JAR FILE     '
       ELSE          'UNKNOWN      '
       END        AS OBJECT_TYPE
     , USEAUTH
FROM SYSIBM.SYSRESAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY QUALIFIER, NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE             '
       WHEN 'P' THEN 'PLAN/PACKAGE     '
       WHEN 'R' THEN 'INTERNAL USE ONLY'
       WHEN ' ' THEN 'AUTH ID          '
       ELSE          'UNKNOWN          '
       END AS GRANTEETYPE
     , SCHEMA       AS CREATOR
     , SPECIFICNAME AS NAME
     , CASE ROUTINETYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION'
       WHEN 'P' THEN 'STORED PROCEDURE    '
       ELSE          'UNKNOWN             '
       END        AS ROUTINETYPE
     , CASE AUTHHOWGOT     
       WHEN '1' THEN 'GRANTOR SCHEMA.* AT TIME'
       WHEN 'E' THEN 'SECADM                  '
       WHEN 'G' THEN 'ACCESSCTRL              '
       WHEN 'L' THEN 'SYSCTRL                 '
       WHEN 'S' THEN 'SYSADM                  '
       WHEN 'T' THEN 'DATAACCESS              '
       WHEN ' ' THEN 'N/A                     '
       ELSE          'UNKNOWN                 '
       END AS AUTHHOWGOT
     , EXECUTEAUTH
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSROUTINEAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR , NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , SCHEMANAME
     , CASE AUTHHOWGOT
       WHEN '1' THEN 'GRANTOR SCHEMA.* AT TIME'
       WHEN 'E' THEN 'SECADM                  '
       WHEN 'G' THEN 'ACCESSCTRL              '
       WHEN 'L' THEN 'SYSCTRL                 '
       WHEN 'S' THEN 'SYSADM                  '
       ELSE          'UNKNOWN                 '
       END AS AUTHHOWGOT
     , CREATEINAUTH
     , ALTERINAUTH
     , DROPINAUTH
FROM SYSIBM.SYSSCHEMAAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY SCHEMANAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE             '
       WHEN 'P' THEN 'PLAN/PACKAGE     '
       WHEN 'R' THEN 'INTERNAL USE ONLY'
       WHEN ' ' THEN 'AUTH ID          '
       ELSE          'UNKNOWN          '
       END AS GRANTEETYPE
     , SCHEMA AS CREATOR
     , NAME
     , CASE AUTHHOWGOT
       WHEN ' ' THEN 'N/A       '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'S' THEN 'SYSADM    '
       WHEN 'T' THEN 'DATAACCESS'
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , ALTERAUTH
     , USEAUTH
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSSEQUENCEAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR, NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE        '
       WHEN 'P' THEN 'PLAN/PACKAGE'
       WHEN ' ' THEN 'AUTH ID     '
       ELSE          'UNKNOWN     '
       END AS GRANTEETYPE
     , DBNAME
     , TCREATOR AS CREATOR
     , TTNAME   AS NAME
     , CASE AUTHHOWGOT
       WHEN ' ' THEN 'N/A         '
       WHEN 'B' THEN 'SYSTEM DBADM'
       WHEN 'C' THEN 'DBCTRL      '
       WHEN 'E' THEN 'SECADM      '
       WHEN 'G' THEN 'ACCESSCTRL  '
       WHEN 'K' THEN 'SQLADM      '
       WHEN 'L' THEN 'SYSCTRL     '
       WHEN 'M' THEN 'DBMAINT     '
       WHEN 'S' THEN 'SYSADM      '
       WHEN 'T' THEN 'DATAACCESS  '
       ELSE          'UNKNOWN     '
       END AS AUTHHOWGOT
     , ALTERAUTH
     , DELETEAUTH
     , INDEXAUTH
     , INSERTAUTH
     , SELECTAUTH
     , UPDATEAUTH
     , REFERENCESAUTH
     , TRIGGERAUTH
     , UPDATECOLS
     , REFCOLS
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSTABAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR, NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE        '
       WHEN 'P' THEN 'PLAN/PACKAGE'
       WHEN ' ' THEN 'AUTH ID     '
       ELSE          'UNKNOWN     '
       END AS GRANTEETYPE
     , CASE AUTHHOWGOT
       WHEN ' ' THEN 'N/A       '
       WHEN 'C' THEN 'DBCTRL    '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'K' THEN 'SQLADM    '
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'M' THEN 'DBMAINT   '
       WHEN 'O' THEN 'SYSOPR    '
       WHEN 'S' THEN 'SYSADM    '
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , BINDADDAUTH
     , BSDSAUTH
     , CREATEDBAAUTH
     , CREATEDBCAUTH
     , CREATESGAUTH
     , DISPLAYAUTH
     , RECOVERAUTH
     , STOPALLAUTH
     , STOSPACEAUTH
     , SYSADMAUTH
     , SYSOPRAUTH
     , TRACEAUTH
     , MON1AUTH
     , MON2AUTH
     , CREATEALIASAUTH
     , SYSCTRLAUTH
     , BINDAGENTAUTH
     , ARCHIVEAUTH
     , CREATETMTABAUTH
     , DEBUGSESSIONAUTH
     , EXPLAINAUTH
     , SQLADMAUTH
     , SDBADMAUTH
     , DATAACCESSAUTH
     , ACCESSCTRLAUTH
     , CREATESECUREAUTH
FROM SYSIBM.SYSUSERAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

 

It’s a pretty good idea to modify all of the above with an appropriate GRANTOR = or GRANTEE = predicate, as otherwise there is too much data.

 

You can DEPend on me!

Nearly all objects in DB2 have a dependency on another object. All of this data is stored in the xxxxxDEP tables. Some of them also have ROLE based data that could well be of interest to the security minded Person:

SELECT BSCHEMA
     , BNAME
     , BTYPE
     , DTBCREATOR AS CREATOR
     , DTBNAME    AS NAME
     , DTBOWNER   AS OWNER
     , CASE OWNERTYPE
       WHEN 'R' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSCONSTDEP
ORDER BY DTBCREATOR , DTBNAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT BSCHEMA   AS CREATOR
     , BNAME      AS NAME
     , BCOLNAME   AS COLUMN_NAME
     , CASE BTYPE
       WHEN 'A' THEN 'ALIAS                   '
       WHEN 'C' THEN 'COLUMN                  '
       WHEN 'F' THEN 'FUNCTION                '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE  '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'Q' THEN 'SEQUENCE                '
       WHEN 'S' THEN 'SYNONYM                 '
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD      '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD    '
       ELSE          'UNKNOWN                 '
       END        AS TYPE
     , BOWNER     AS OWNER
     , CASE BOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS BOWNERTYPE
     , DSCHEMA    AS DEP_CREATOR
     , DNAME      AS DEP_NAME
     , DCOLNAME   AS DEP_COLUMN_NAME
     , CASE DTYPE
       WHEN 'C' THEN 'GENERATED COLUMN'
       WHEN 'F' THEN 'FUNCTION        '
       WHEN 'I' THEN 'INDEX           '
       WHEN 'X' THEN 'ROW PERMISSION  '
       WHEN 'Y' THEN 'COLUMN MASK     '
       ELSE          'UNKNOWN         '
       END        AS DEP_TYPE
     , DOWNER     AS DEP_OWNER
     , CASE DOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DEP_OWNERTYPE
FROM SYSIBM.SYSDEPENDENCIES
ORDER BY 1 , 2 , 3 , 5 , 7 , 8 , 9 , 11
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT ROLENAME
     , DEFINER
     , CASE DEFINERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DEFINERTYPE
     , DSCHEMA    AS CREATOR
     , DNAME      AS NAME
     , CASE DTYPE
       WHEN 'A' THEN 'ALIAS                   '
       WHEN 'B' THEN 'TRIGGER                 '
       WHEN 'D' THEN 'DATABASE                '
       WHEN 'E' THEN 'DISTINCT TYPE           '
       WHEN 'F' THEN 'USER-DEFINED FUNCTION   '
       WHEN 'I' THEN 'INDEX                   '
       WHEN 'J' THEN 'JAR FILE                '
       WHEN 'L' THEN 'ROLE                    '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'N' THEN 'TRUSTED CONTEXT         '
       WHEN 'O' THEN 'STORED PROCEDURE        '
       WHEN 'Q' THEN 'SEQUENCE              '
       WHEN 'R' THEN 'TABLE SPACE             '
       WHEN 'S' THEN 'STORAGE GROUP           '
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'X' THEN 'ROW PERMISSION          '
       WHEN 'Y' THEN 'COLUMN MASK             '
       ELSE          'UNKNOWN                 '
       END        AS TYPE
FROM SYSIBM.SYSOBJROLEDEP
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT BQUALIFIER AS CREATOR
     , BNAME      AS NAME
    , CASE BTYPE
       WHEN 'A' THEN 'ALIAS                      '
       WHEN 'B' THEN 'BUSINESS_TIME              '
       WHEN 'C' THEN 'SYSTEM_TIME                '
       WHEN 'F' THEN 'UDF OR CAST FUNCTION       '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE     '
       WHEN 'I' THEN 'INDEX                      '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE   '
       WHEN 'O' THEN 'STORED PROCEDURE           '
       WHEN 'P' THEN 'LARGE OR DSSIZE PART. SPACE'
       WHEN 'Q' THEN 'SEQUENCE OBJECT            '
       WHEN 'R' THEN 'TABLESPACE                 '
       WHEN 'S' THEN 'SYNONYM                    '
       WHEN 'T' THEN 'TABLE                      '
       WHEN 'U' THEN 'DISTINCT TYPE              '
       WHEN 'V' THEN 'VIEW                      '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD         '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD       '
       ELSE          'UNKNOWN                    '
       END        AS OBJECT_TYPE
     , DCOLLID AS COLLECTION
     , DNAME   AS PACKAGE
     , HEX(DCONTOKEN) AS DCONTOKEN
     , CASE DTYPE
       WHEN 'F' THEN 'COMPILED SQL SCALAR FUNCTION    '
       WHEN 'N' THEN 'NATIVE SQL ROUTINE PACKAGE      '
       WHEN 'O' THEN 'ORIGINAL COPY OF A PACKAGE      '
       WHEN 'P' THEN 'PREVIOUS COPY OF A PACKAGE      '
       WHEN 'R' THEN 'RESERVED FOR IBM USE            '
       WHEN 'T' THEN 'TRIGGER PACKAGE                 '
       WHEN ' ' THEN 'NOT A TRIGGER/NATIVE SQL PACKAGE'
       ELSE          'UNKNOWN                         '
       END        AS PACKAGE_TYPE
     , DOWNER     AS OWNER
     , CASE DOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DOWNERTYPE
FROM SYSIBM.SYSPACKDEP
ORDER BY 1 , 2 , 7
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT BCREATOR   AS CREATOR
     , BNAME      AS NAME
     , CASE BTYPE
       WHEN 'A' THEN 'ALIAS                      '
       WHEN 'E' THEN 'INSTEAD OF TRIGGER         '
       WHEN 'F' THEN 'UDF OR CAST FUNCTION       '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE     '
       WHEN 'I' THEN 'INDEX                      '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE   '
       WHEN 'O' THEN 'STORED PROCEDURE           '
       WHEN 'P' THEN 'LARGE OR DSSIZE PART. SPACE'
       WHEN 'Q' THEN 'SEQUENCE OBJECT            '
       WHEN 'R' THEN 'TABLESPACE                 '
       WHEN 'S' THEN 'SYNONYM                    '
       WHEN 'T' THEN 'TABLE                      '
       WHEN 'V' THEN 'VIEW                       '
       ELSE          'UNKNOWN                    '
       END        AS OBJECT_TYPE
     , DNAME   AS PLAN_NAME
FROM SYSIBM.SYSPLANDEP
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT DCREATOR   AS CREATOR
     , DNAME      AS NAME
     , DCOLNAME   AS COLNAME
     , CASE DTYPE
       WHEN 'F' THEN 'SQL FUNCTION       '
       WHEN 'I' THEN 'IDENTITY COLUMN    '
       WHEN 'X' THEN 'IMPLICIT DOCID     '
       WHEN ' ' THEN 'OLD IDENTITY COLUMN'
       ELSE          'UNKNOWN            '
       END        AS SEQUENCETYPE
     , DOWNER     AS OWNER
     , CASE DOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSSEQUENCESDEP
ORDER BY CREATOR , NAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT BCREATOR   AS CREATOR
     , BNAME      AS NAME
     , CASE BTYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION    '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE  '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD      '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD    '
       ELSE          'UNKNOWN                 '
       END        AS OBJECT_TYPE
     , DNAME    AS VIEW_NAME
     , DCREATOR AS VIEW_CREATOR
     , CASE DTYPE
       WHEN 'F' THEN 'COMPILED SQL SCALAR FUNCTION'
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE    '
       WHEN 'V' THEN 'VIEW                      '
       ELSE          'UNKNOWN                     '
       END        AS TABLE_TYPE
     , DOWNER     AS OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSVIEWDEP
ORDER BY 1 , 2 , 4 , 5
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Again, judicious use of extra WHERE predicates is recommended!

 

ROLE based meta-data

Then there is the “base” data of the objects themselves, which can have ROLE based information in it.

SELECT NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
FROM SYSIBM.SYSDATABASE
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSDATATYPES
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT NAME
     , PLNAME AS PLAN_NAME
     , PLCREATOR AS CREATOR
     , CASE PLCREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS PLCREATORTYPE
FROM SYSIBM.SYSDBRM
ORDER BY NAME, 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSINDEXES
ORDER BY 3 , 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT JARSCHEMA AS CREATOR
     , JAR_ID AS NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSJAROBJECTS
ORDER BY 3 , 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT COLLID AS COLLECTION
     , NAME   AS PACKAGE
     , HEX(CONTOKEN) AS CONTOKEN
     , CREATOR
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSPACKAGE
ORDER BY 1 , 2 , 3
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT COLLID AS COLLECTION
     , NAME   AS PACKAGE
     , HEX(CONTOKEN) AS CONTOKEN
     , COPYID
     , CREATOR
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSPACKCOPY
ORDER BY 1 , 2 , 3 , 4
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT SCHEMA     AS CREATOR
     , NAME       AS NAME
     , OWNER      AS OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSPARMS
ORDER BY 1 , 2 , 3
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , NAME       AS NAME
     , BOUNDBY
FROM SYSIBM.SYSPLAN
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , ACTIVE
     , CASE ROUTINETYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION'
       WHEN 'P' THEN 'STORED PROCEDURE    '
       ELSE          'UNKNOWN             '
       END        AS ROUTINETYPE
     , CASE EXTERNAL_SECURITY
       WHEN 'D' THEN 'DB2 - AUTH ID FROM WLM               '
       WHEN 'U' THEN 'SESSION_USER - AUTH ID OF INVOKER    '
       WHEN 'C' THEN 'DEFINER - AUTH ID OF OWNER OF ROUTINE'
       WHEN ' ' THEN 'N/A                                  '
       ELSE          'UNKNOWN                              '
       END        AS ROUTINETYPE
     , SPECIFICNAME
FROM SYSIBM.SYSROUTINES
ORDER BY CREATOR , NAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , CASE SEQTYPE
       WHEN 'I' THEN 'IDENTITY COLUMN      '
       WHEN 'S' THEN 'USER-DEFINED SEQUENCE'
       WHEN 'X' THEN 'IMPLICIT DOCID       '
       ELSE          'UNKNOWN              '
       END        AS SEQUENCETYPE
FROM SYSIBM.SYSSEQUENCES
ORDER BY CREATOR , NAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT NAME       AS CREATOR
     , PLNAME     AS PLAN_NAME
     , PLCREATOR  AS CREATOR
     , CASE PLCREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS PLCREATORTYPE
FROM SYSIBM.SYSSTMT
ORDER BY 2 , 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , CREATEDBY
FROM SYSIBM.SYSSTOGROUP
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , TBCREATOR
     , TBNAME
     , CREATEDBY
FROM SYSIBM.SYSSYNONYMS
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , NAME
     , CREATOR
     , CASE TYPE
       WHEN 'A' THEN 'ALIAS                                    '
       WHEN 'C' THEN 'CLONE TABLE                              '
       WHEN 'G' THEN 'CREATED GLOBAL TEMPORARY TABLE           '
       WHEN 'H' THEN 'HISTORY TABLE                            '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE                 '
       WHEN 'P' THEN 'TABLE THAT WAS IMPLICITLY CREATED FOR XML'
       WHEN 'T' THEN 'TABLE                                    '
       WHEN 'V' THEN 'VIEW                                     '
       WHEN 'X' THEN 'AUXILIARY TABLE                          '
       ELSE          'UNKNOWN                                  '
       END AS TABLE_TYPE
     , CASE AUDITING
       WHEN ' ' THEN '            '
       WHEN 'A' THEN 'AUDIT ALL   '
       WHEN 'C' THEN 'AUDIT CHANGE'
       ELSE          'UNKNOWN     '
       END AS AUDIT_LEVEL
     , TABLESTATUS
     , CASE SECURITY_LABEL
       WHEN ' ' THEN '       '
       WHEN 'R' THEN 'MLS    '
       ELSE          'UNKNOWN'
       END AS SECURITY_LABEL
     , CASE CONTROL
       WHEN ' ' THEN 'NO ACCESS CONTROL ENFORCEMENT'
       WHEN 'B' THEN 'ROW AND COLUMN ACCESS CONTROL'
       WHEN 'C' THEN 'COLUMN ACCESS CONTROL        '
       WHEN 'R' THEN 'ROW ACCESS CONTROL           '
       ELSE          'UNKNOWN                      '
       END AS SECURITY_LABEL
     , CREATEDBY
FROM SYSIBM.SYSTABLES
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT
       DBNAME
     , NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , CASE TYPE
       WHEN ' ' THEN 'NORMAL '
       WHEN 'G' THEN 'PBG    '
       WHEN 'L' THEN 'LARGE  '
       WHEN 'O' THEN 'LOB    '
       WHEN 'P' THEN 'XML PBG'
       WHEN 'R' THEN 'PBR    '
       ELSE          'UNKNOWN'
       END AS SPACE_TYPE     , CASE STATUS
       WHEN 'A' THEN 'OK                    '
       WHEN 'C' THEN 'PI MISSING            '
       WHEN 'P' THEN 'CHECKP STATUS         '
       WHEN 'S' THEN 'CHECKP STATS ON A PART'
       WHEN 'T' THEN 'NO TABLE DEFINED      '
       ELSE          'UNKNOWN               '
       END AS STATUS
     , CREATEDBY
FROM SYSIBM.SYSTABLESPACE
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT SCHEMA AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , SECURE
     , CASE TRIGTIME
       WHEN 'A' THEN 'AFTER  '
       WHEN 'B' THEN 'BEFORE '
       WHEN 'I' THEN 'INSTEAD'
       ELSE          'UNKNOWN'
       END AS TRIGGER_TIME
     , CREATEDBY
FROM SYSIBM.SYSTRIGGERS
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , CASE TYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION    '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'V' THEN 'VIEW                    '
       ELSE          'UNKNOWN                 '
       END        AS TABLE_TYPE
FROM SYSIBM.SYSVIEWS
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Once more: Add your own WHERE predicates here please!

 

Lost in translation

Finally, and the last SQL for this newsletter, is for the Inbound and Outbound AUTHID translation table:

SELECT AUTHID
     , CASE TYPE
       WHEN 'I' THEN 'INBOUND AND COME-FROM CHECKING               '
       WHEN 'O' THEN 'OUTBOUND TRANSLATION                         '
       WHEN 'S' THEN 'OUTBOUND SYSTEM AUTHID FOR TRUSTED CONNECTION'
       ELSE          'UNKNOWN                                      '
       END        AS NAME_TYPE
     , NEWAUTHID
FROM SYSIBM.USERNAMES
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

 

We have now gone through all the Role based, and some more, DB2 Catalog tables. What about seeing what is happening on your machine? Have you anything in place that can give you 100% coverage of audit issues? With Software Engineering’s SQL WorkloadExpert for DB2 z/OS you can audit everything that is running in your enterprise. For example here is a view of the Audit pop-up window:
DB2 z/OS newsletter 2015-11: DB2 Quick Security Audit -Part 2

 

In the bottom half you can see the Authorization failures radio button. This shows all -551 SQL Codes and also the +562 “ping” of a double GRANT. Why this? Well, to prove that the Audit is actually alive and well you should do a “dummy” GRANT every day like, for example,

GRANT SELECT ON SYSIBM.SYDUMMY1 TO PUBLIC ;

that is already there and therefore gets a +562 which is also externalized and available in this selection. This way auditors can see that the Audit trace was running and so any -551 would also be there.

You also get GRANTs and REVOKEs and DDL that is running – optionally the software can even generate the DDL so you can follow the lifecycle of changes over time. All very handy stuff and available real-time!

 

Death by Default?

Now onto the last part of these newsletters: Default settings which can harm security.

Here’s a little list of any security ZPARM or BSDS settings that should never ever be left “as default”.

What Default  Where
 Catalog Alias DSNCAT CATALOG Zparm
 Group Name DSNCAT GRPNAME Zparm
 Member Name DSN1 MEMBNAME Zparm
 SSID DSN1 DSNHDECP module
 Command prefix –DSN1 n/a
 SYSADM 1 SYSADM SYSADM Zparm
 SYSADM 2 SYSADM SYSADM2 Zparm
 SYSOPER 1 SYSOPER SYSOPER Zparm
SYSOPER 2SYSOPERSYSOPER2 Zparm
Security Admin 1SECADMSECADM1 Zparm
Security Admin 2SECADMSECADM2 Zparm
Unknown UseridIBMUSERDEFLTID Zparm
If using DDF:
DB2 Location NameLOC1BSDS
DB2 LU NameLU1BSDS
DRDA Port446BSDS
SECURE Port448BSDS

 

If you have any of the above values in your shop – Time to plan some changes!

I hope you enjoyed this last part of a Quick Security Audit. Thanks again for reaching the end.

As always, any questions or comments would be most welcome!

 

TTFN,

Roy Boxwell

GIVE and TAKE Programme 1, 2, 3


Give and Take 2020

Information on the Give and Take Programs 4,5,6,7


 

Previous Give & Take

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

Limited free-of-Charge Db2 Application

This Program started in Europe, during our 30th anniversary was such a success, that it is now being Extended for the benefit of North American Db2 z/OS sites.

SQL WorkloadExpert for Db2 z/OS (WLX) contains several “Use Cases”. We provided three of them, free of charge, for one month to different sites.

In return, we received their results. We’d like to share this inspiring experiences with you now.


Inspiring experiences

We TAKE the anonymized results for research

and will communicate with the local User Groups for discussions

Kundenmeinungen

3BIF USAGE
News
Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren     [Customer Comments]

 

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

Erste Ergebnisse von Db2 z/OS Kunden

1Index Mantenance CostsNearly all the data we got back showed a positive result for created Indexes…
2EXPLAIN SuppressionAbout 10% of SQLs are actually “left over”…
3BIF Usage When migrating to a new Db2 version, the BIFs are not always compatible and an extreme amount of data is produced.

 

The difficulty of near-time analysis to track down BIFs within dynamic SQL have been solved with the BIF Usage Use Case…

[Ergebnisse von Db2 z/OS Kunden (engl)]

Program 3 – BIF Usage   

BIF-Usage

 

Präsentation

BIF KompatibilitätDb2 10 Kompatibilität Mode

 

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von Db2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon

BIF Usage Video

2015-05 Top 10 Things to Ignore for DB2 z/OS

 

This newsletter was inspired by a recent article I read in the “Enterprise Systems Magazine” called “Top 10 Ways to Waste Money on CPU”. Why not the Top 10 things to ignore?

 

DB2 z/OS things you could ignore but most definitely should not!

So here’s my little list, in no particular order, of things you could ignore but most definitely should not!

  1SQL DELETE statements in mega-million  tablesSQL DELETE statements in mega-million  tables when a REORG DISCARD would kill two birds with one stone. (I love that phrase) Anyway, after 500,000 singleton deletes the tablespace probably needs a REORG anyway and so why not do two in one? A bit of a no-brainer really.
  2LOB columnsLOB columns, whose size would *easily* fit inside an inline LOB or even a VARCHAR. LOBs are still slow and cumbersome to use, but inline LOBs are great. If you can use ‘em – do so!
  3BP0 being used for *everything* by default…BP0 being used for *everything* by default… Please split the BP s into groups!!! BP0 is only, and I mean ONLY, for the Catalog and Directory. That way you can actually keep the size low and spare some memory for other BPs. LOB and XML tablespaces get their own BP. Tables and Indexes are split. Sort gets its own. You get the idea ?
  4Utility jobs still based on 1990’s ideasUtility jobs still based on 1990’s ideas. Are you still running a RUNSTATS to see if a REORG is needed? Are you running REORGs without inline RUNSTATS? Are your RUNSTATS using FREQVAL and, if required, HISTOGRAM?
  5Death by “indexiphication”.Death by “indexiphication”. Do you have tables with more than three indexes? Do you have ten or more indexes? Time to look for INCLUDE usage and LASTUSED Timestamps here!
  6PLAN_TABLE explosionPLAN_TABLE explosion. Do you have multiple PLAN_TABLEs in production? Are you REORGing, RUNSTATSing and Image Copying them on a regular basis? Are you purging them of rubbish data on a regular basis?
  7Are your ZPARMs up to date?Have you checked the Rules of Thumb in regard to ZPARMS since they were last set back in the 80’s? Now is the time to do a review of all the ZPARMS to see where you can really get performance boosts. (For example the default SRTPOOL In DB2 10 is now 10,000k but in DB2 V8 and 9 it was just 2,000k)
 8Are you removing garbage from the DB2 Catalog and Directory ?Are you removing garbage from the DB2 Catalog and Directory ? Do you really need all the packages and versions of those packages from 1989 these days? If a table gets RUNSTATSed that these ancient, never executed, packages uses then it should trigger a review of the access paths, which could, of course, flag up problems where no real problem exists.
  9 COMMIT frequency.You never need to check or change this do you…
 10 TrainingIDUG, Insight, and RUGs etc. you can never ever get enough info about how things work and how to make things better.

 

One thing you should certainly NOT ignore, is my newsletter! I have lots of exciting topics coming up in 2015 and I’ll also let you know about our webinars.

Upcoming Newsletters

  • SOUNDEX and other cool features part 4 – update for DB2 10 & all new for DB2 11
  • BAD Data Day
  • Overloaded Log
  • A real CLUSTER Buster

 

As usual, any comments or questions are welcome!

TTFN

Roy Boxwell