Schlagwort: Db2 audit

db2-zos-audit

Veröffentlicht am

2026-06 Db2 vNext Migration – Are YOU ready, Player One?

This month I wish to stroll along the dangerous and difficult road called Db2 Release Migration. We all love it as it means a new release with all new go-faster features and whizzbang things to play with but we also all hate it as it means we have things to do before we can actually migrate and get to this new promised land.

With Db2 for z/OS vNext coming up soon we are now back in the Driving Seat!

The list of deprecated items is pretty long and now about 80% of them have been finally deleted. If you have, or use, any of them you will *not* be joining the rest of us on the other side of the valley where the grass is definitely much greener!

The list of killed off items is:

  • Simple tablespaces
  • Segmented tablespaces
  • Classic partitioned tablespaces (With or without Index based partitioning)
  • Basic (Six byte) format
  • BRF (Basic Row Format so not yet at RRF – Reordered Row Format)
  • Hash Access
  • Synonyms
  • VTAM/SNA

Haakon Roberts shared this data at the IDUG EMEA 2024 in Valencia and it was the first time we had all seen an inkling of what would come. The real surprise, at least for me, was the requirement to remove VTAM/SNA support and move over to TCP/IP. The rest we had all known about for years but killing off VTAM/SNA was brand new!

CATMAINT to the Rescue?

Nope! None of these features will be automagically fixed by running a CATMAINT. It is all manual work and up to us, the DBAs fighting at the front, to fix „When we have some spare time…“

An ALTER a Day keeps the Dr at Bay

The basic „cure“ for most of these problem children is simply an ALTER and a REORG, but it *never* is that easy, is it? To fix segmented or simple tablespaces with just one table within them it is an ALTER to MAXPARTITIONS 1 which will kick the tablespace into the world of UTS PBG. Follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.

More than one?

If you have multi-table tablespaces then you must be at Db2 12 FL508 or higher and create a new set of tablespaces that match *exactly* the current ones for BUFFERPOOL, CCSID and LOGGED attributes. Then you use the ALTER … MOVE TABLE syntax for each table. An actioning REORG followed by a full RUNSTATS on each new tablespace afterwards with SHRLEVEL REFERENCE is then required to get the RTS statistics inline. Now do a REBIND of all invalidated packages and you are done.

Time for the Classics?

If you have any really, really old index-based partitioning tables, you must do two ALTERs within one commit scope. The first flipping the Partitioning Index to NOT CLUSTER and then back to CLUSTER. Now you are at table-based partitioning so read on!

Table-based partitioning

At this point it is an ALTER to SEGSIZE 64 which will kick these babies into the fun world of UTS PBR.

For both Classic cases, follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.

Six Byte RBA/LRSN, anyone?

If any of your tablespaces or indexspaces have not got 10 Byte RBA/LRSN then you must REORG them to action this. One little problem here is if the space is an XML space (Type = ‚P‘) then you must first check its base table’s tablespace to see if that is a UTS space. If so then all is good, otherwise you have a non-versioning XML space which will require this four-step fix:

1. DSN1COPY to generate image copy for XML data

2. Drop XML column from base table

3. Recreate the XML column in the base table

4. LOAD REPLACE to load the XML data from the image copy generated by step 1.

Invalidated Packages?

Yup! All of these ALTERs and the actioning REORGs with their inline/afterwards RUNSTATS will happily invalidate any and all packages that refer to the objects… This is a major pain as your access paths can then very easily go south! This is time for our tool BindImpactExpert (BIX) to ride to the rescue. As long as you are running with EXPLAIN(YES) – and I sincerely hope you are!!! – BIX can be used to highlight any and all changed access paths enabling you to be proactive with corrective measures.

Definitely evil DEFINE NO

DEFINE NO objects are great as SQL can SELECT from them really fast! They take up next to no disk space and require no REORG or COPY processing as there is no VSAM dataset. The problem with these little devils is that REORG is actually *not* permitted on them! One exception to this rule is if the TS/TP was created with BRF, as then you can REORG with the option ROWFORMAT RRF and this REORG will just flip a bit or two in the catalog/directory and nothing else.

And???

That means that all the ALTERs you might have done are „hanging in the wind“. IBM state that an INSERT/LOAD will materialize the VSAM cluster(s) but who wants to do that? The only way forward is to extract the DDL that created them, DROP them and then reCREATE them all as DEFINE NO again. As when recreated they will be valid for vNext, of course then a REBIND of all invalidated packages is also required and you are done.

Devil in the Details

For BRF partitions the fixing REORG will fail if any table has a validation procedure (VALPROC table column) or edit procedure (EDPROC tabel column) defined. If this is the case then the procedures must first be dropped before the REORG and afterwards added back.

Did they make a HASH of it?

HASH access came in with a big fanfare, we finally had an access path like IMS HDAM, but it died a death very quickly and the fix here is ALTER with DROP ORGANIZATION. Naturally, this drops the hash index and so you must then create a new index for SQL use and – guess what else you must do? Of course, a REORG with inline statistics and a REBIND of all invalidated packages and you are done.

Simply SYNONYMs

I wrote a Blog about all these beasts.

Yes, over ten years ago! The actual fix for every synonym is straightforward:

SET CURRENT SQLID = 'Synonym schema' ;
DROP SYNONYM 'Synonym name' ;
  COMMIT ;
CREATE ALIAS 'Synonym schema'.'Synonym name'
       FOR   'Table creator'.'Table name' ;
  COMMIT ;

The problems here are that the statement SET CURRENT SQLID requires SYSADM Authorization, so this is not something I would farm out to the next student doing work experience!!! Plus, guess what you have to do afterwards??? A REBIND of all invalidated packages and possibly a chain of dependent invalidated objects as well and you are done.

TCP/IP is the Future!

It came as a surprise but there are indeed shops out there who are still using VTAM/SNA for inter-Db2 communication. The problem here is that when it was released in 1974, we all trusted each other! If you were connected to DB2A and just hopped over to DB2B it happily trusted you as you „were already on a DB2A so you must be good guy!“ Sadly, those halcyon days are long gone. These days we have „Zero trust“ as the norm – a real shame but it is what it is!

Reason for Change?

TCP/IP has enhanced security (AT-TLS and JSON Web Tokens), it can use 64-bit communication buffers and is zIIP eligible. You will save CPU and be much more secure, so going to this is really no question – However, this is a project all on its own – not something for a Friday afternoon at 4 o’clock!

Big Switch?

To switch off VTAM/SNA you should do several things. First clear out any and all old entries in the CDB tables LULIST, LUMODES, MODESELECT and all but the empty LUNAME entry from LUNAMES. Then you should do a BSDS update with DSNJU003 setting the IPNAME for that subsystem to be the real host name. Just doing that switches off VTAM/SNA for that member. If doing all this then also go to SECPORT usage at the same time as then your Db2s are truly secure! Your auditors will love you.

IBM Details

Are here.

Let’s get it sorted!

Yes, even all the sort workspaces must be cleaned-up and moved to PBG spaces. You really need a lot of 32k, a few 4k, and you need to know exactly how many FOR SORT and how many FOR DGTT (The new syntax in Db2 13 FL508) you require and need. No more guessing at which workload is going into which work tablespace.

  • FOR SORT specifies that the table space is used for processing other than declared global temporary table (DGTT) work, such as sort, joins, created global temporary tables, query parallelism, trigger transition tables, and so forth.
  • FOR DGTT specifies that the table space is used for DGTT work and processes that use internal temporary tables, such as scrollable cursors and INSTEAD OF triggers.

Remember that here no ALTER helps – you must DROP and CREATE the work spaces again… But hey! At least no REORG, RUNSTATS and REBINDs are required!

<PHEW> That’s a Ton of Stuff to do…

Wouldn’t it be cool if there was a nice small bit of freeware out there that listed out all the stuff you have to do to actually get ready for Db2 vNext! In fact, you cannot even get to Db2 13 FL511 as that will stop you with any of the above-mentioned items!

What Luck – SEG to the Rescue!

Yes indeed, we have a bit of freeware called MigrationReadiness HealthCheck for Db2 z/OS (MRHC) that runs through your Db2 subsystems and shows you all the KPIs you have as well as highlighting all the Migration Blockers, as I call them. There is also a pay-ware version that then actually creates all of the ALTERs, REORGS, RUNSTATS and REBINDs for you as well.

How does it look?

It looks cool, of course! Here are a few example screen grabs of how it looks in my little Db2 13 data-sharing test system and what it offers:

Db2 MigrationReadiness HealthCheck V2.1 for SD1 V13R1M509
               started at 2026-05-28-11.08.24
            Lines with *** are deprecated features                              
            Lines with MMM are migration blockers                               
            Lines with XXX are definition errors                                 
Number of DATABASES          :  225                                              
  # of empty DATABASES       :   48                                             
  # of implicit DATABASES    :  110                                              
  # of empty implicit DBs    :   46
                                              
Number of TABLESPACES        : 4995                                              
  of which HASH organized    :    0                                              
  of which PARTITION CLASSIC :    0                                              
    # Partitions             :    0                                              
  of which SEGMENTED         :   27 MMM                                          
  of which SIMPLE            :    3 MMM                                          
  of which LOB               :   99                                              
  of which UTS PBG           : 4833 
    # Partitions             : 4834                                              
  of which UTS PBR (Absolute):    1                                              
    # Partitions             :    6     
  of which UTS PBR (Relative):   10                                              
    # Partitions             : 2215                                              
  of which XML               :   22 
             
Number of TSs as LARGE       :    0                                              
Number of empty tablespaces  :    7                                              
Number of multi-table TSs    :   12 MMM                                          
  # of tables within these   :   49       
.
.
Number of table partitions   : 7211   
  of which DEFINE NO         : 2918   
  of which 6 byte RBA <11 NFM:    0   
  of which 6 byte RBA Basic  :    0   
  of which ten byte RBA      : 4293   
Number of TP in BRF          :   17 MMM
.
.
LULIST entries found         :    0   
LUMODES entries found        :    1 MMM
LUNAMES entries found        :    2 MMM
MODESELECT entries found     :    1 MMM
.
.
Total number of REORGs        21          
     REORGing                176 Cylinders
        also requiring      1409 REBINDs

   

Naturally, most of this is the Db2 Catalog and Directory. At the end it outputs a small list of KPIs with the Total number of REORGs required, the total numbers of Cylinders of space all the table and indexspaces take and how many REBINDs should then be done afterwards. This gives the experienced DBA a very good idea of how long and how potentially dangerous this whole thing could be!

Just the Facts, Ma’am!

Under DD card ALTERCAT in the pay-ware version are all the required actions, note that you must be at Db2 13 FL509 or above to get the CONVERTUTS REORG syntax:

-- REORG TABLESPACE DSNDB01.SCT02 SHRLEVEL CHANGE
--   CONVERTUTS 
-- REORG TABLESPACE DSNDB01.SYSUTILX SHRLEVEL CHANGE
--   CONVERTUTS 
-- REORG TABLESPACE DSNDB06.SYSALTER SHRLEVEL CHANGE
--   CONVERTUTS
-- REORG TABLESPACE DSNDB06.SYSCONTX SHRLEVEL CHANGE
--   CONVERTUTS 
--REBIND PACKAGE(IQA_COLLECTION_610.SQLDVDRC) APREUSE(WARN)       
--REBIND PACKAGE(IQA_COLLECTION_610.SQLDVDRC.(2026-01-21-06.55.23.099777)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDDLS.(2019-03-22-09.20.33.799998)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDDLS.(2022-08-01-06.08.43.796175)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDVCRC.(2018-08-21-07.21.16.927191)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDVDRC.(2022-08-01-06.21.52.371116)) APREUSE(WARN)
                                    

Notice that it always generates APREUSE(WARN) to try and keep the old access path.

Also, here are any of the nasty DEFINE NO REORG problems:

-- DEFINE NO TABLESPACE TS5941.TSTSDNQ REORG NOT POSSIBLE

and the SQL for CDB Clean-up:

-- DELETE FROM SYSIBM.LUMODES  
-- ;                           
-- COMMIT ;                    
-- DELETE FROM SYSIBM.LUNAMES  
-- WHERE NOT LUNAME = '        '
-- ;                           
-- COMMIT ;                    
-- DELETE FROM SYSIBM.MODESELECT
-- ;                           
-- COMMIT ;
  

Under DD card MIGRAREP are all the Migration Blockers listed out in detail:

Simple DB: DSNDB01 TS: SCT02      
Segmented DB: DSNDB01 TS: SYSUTILX
Segmented DB: DSNDB06 TS: SYSALTER
Segmented DB: DSNDB06 TS: SYSCONTX
Segmented DB: DSNDB06 TS: SYSDDF  
Segmented DB: DSNDB06 TS: SYSEBCDC
Simple DB: DSNDB06 TS: SYSGPAUT   
Segmented DB: DSNDB06 TS: SYSGRTNS
Segmented DB: DSNDB06 TS: SYSHIST 
Segmented DB: DSNDB06 TS: SYSJAVA 
Segmented DB: DSNDB06 TS: SYSROLES
Segmented DB: DSNDB06 TS: SYSSEQ  
Segmented DB: DSNDB06 TS: SYSSEQ2 
Segmented DB: DSNDB06 TS: SYSSTATS
Segmented DB: DSNDB06 TS: SYSTARG 
Segmented DB: DSNDB06 TS: SYSTSASC
Segmented DB: DSNDB06 TS: SYSTSUNI
Segmented DB: DSNDB06 TS: SYSTSXTM
Segmented DB: DSNDB06 TS: SYSTSXTS
Simple DB: DSNDB06 TS: SYSUSER    
Segmented DB: DSNDB06 TS: SYSXML  
Segmented DB: TS5941 TS: TSTSDNQ  
Segmented DB: WRKSD10 TS: DSN32K00
Segmented DB: WRKSD10 TS: DSN32K01
Segmented DB: WRKSD10 TS: DSN4K00 
Segmented DB: WRKSD10 TS: DSN4K01 
Segmented DB: WRKSD11 TS: DSN32K00
Segmented DB: WRKSD11 TS: DSN32K01
Segmented DB: WRKSD11 TS: DSN4K00 
Segmented DB: WRKSD11 TS: DSN4K01 
BRF tablespace DB: DSNDB01 TS: SCT02    
BRF tablespace DB: DSNDB01 TS: SYSUTILX 
BRF tablespace DB: DSNDB06 TS: SYSALTER 
BRF tablespace DB: DSNDB06 TS: SYSCONTX  
BRF tablespace DB: DSNDB06 TS: SYSDDF    
BRF tablespace DB: DSNDB06 TS: SYSEBCDC  
BRF tablespace DB: DSNDB06 TS: SYSGPAUT  
BRF tablespace DB: DSNDB06 TS: SYSGRTNS  
BRF tablespace DB: DSNDB06 TS: SYSHIST   
BRF tablespace DB: DSNDB06 TS: SYSJAVA   
BRF tablespace DB: DSNDB06 TS: SYSROLES  
BRF tablespace DB: DSNDB06 TS: SYSSEQ    
BRF tablespace DB: DSNDB06 TS: SYSSEQ2   
BRF tablespace DB: DSNDB06 TS: SYSSTATS  
BRF tablespace DB: DSNDB06 TS: SYSTARG   
BRF tablespace DB: DSNDB06 TS: SYSUSER   
BRF tablespace DB: DSNDB06 TS: SYSXML    
CDB table LUMODES has row - LUNAME DKLTEST MODENAME MODTEST.
CDB table LUNAMES has row - LUNAME DKLTEST SYSMODENAME MODTEST.
CDB table LUNAMES has row - LUNAME ROYTEST SYSMODENAME -none-.
CDB table MODESELECT has row - LUNAME DKLTEST AUTHID DKLTEST_AUTH_ID PLANNAME DKLTEST.

Note the WRKxxxx tablespaces…

Start to plan today!

Using our MigrationReadiness HealthCheck for Db2 z/OS freeware you can start to divvy up and plan all of the work that will be coming down the vNext road towards you. Start now and you still have over two years to get it all done and actioned. Start in two years and you will never make it – The choice is yours!

TTFN,

Roy Boxwell

Veröffentlicht am

2026-01 Things I learnt last year…

Hi all! This month I wish to go though a few of the interesting, annoying and odd things that I bumped into last year. Some were new for me and some were just interesting for me!

COMPRESS THIS!

One of my customers is now starting down the road of compressing their very, very large NPSI’s as the RECOVER utility is actually way faster than a REBUILD. Nothing new here, is there? But wait! What if they are using FLASH COPY?

It gets very, very ugly very, very quickly is what happens!

Why?

Remember how FLASH COPY works? It is sooooo blindingly fast because it does all the actual copy stuff „in the DS8000, or equivalent, box“ and *not* on your mainframe. This is really cool as you just shoot off a FLASH COPY and, as long as a few really basic rules are not broken, the copy is finished the moment it starts!

So, what about Indexes?

Firstly, if you want to do a FLASH COPY of a COMPRESS YES index you *cannot* also do a sequential copy. Further, remember that a FLASH COPY is a VSAM dataset and you cannot do a COPYTOCOPY of one of these either, meaning you have just one single VSAM dataset as a copy – This is, at least for me, a single point of failure and not good. But it gets much worse!

Really, how so?

Please now remember how index compression works… It is done purely „in memory“ in the bufferpool. This means that when you have an insert, delete, or key update in memory and it has not yet been externalized to disk that when you now do a FLASH COPY you are copying garbage… This is, to coin a phrase, „not good“ whereas a normal COPY index goes through the bufferpool and so a sequential copy is naturally ok!

Bottom Line

If using COMPRESS YES indexes in no way use FLASH COPY. Perhaps, at least for storage, dataset compression of the sequential copy datasets might save space…but then that defeats the purpose of COMPRESS YES on the index purely in the Db2 world which is primarily reducing I/O and secondarily reducing index page splits.

What about SYSTEM LEVEL BACKUPS?

Guess what? These are FLASH COPY as well! If you use SLBs and you have COMPRESS YES indexes you better be careful what you „replay“ and make sure to always REBUILD, or at least CHECK, all indexes after a RECOVER has been run!

Docu?

All of the above is documented of course but it is like Douglas Adams wrote „in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door in saying ‚Beware of the Shark.'“ – [The Shark is my idea geddit? Originally it was Leopard of course!]

Death By RUNSTATS

It is surprisingly easy to kill yourself with a simple RUNSTATS these days.

How?

Let’s say you have PROFILE on and you are using SYSSTATFEEDBACK for all your tables. I know you are as it is all on by default and who changes defaults?

And?

Now a third-party vendor sells you some software with ridiculously long VARCHAR fields containing possible NAMEs and ADDRESSes. In this case VARCHAR(2000) is being used. The SQL in question is using dynamic SQL with literals, not parameter markers, in the WHERE clause against these columns and SYSSTATFEEDBACK „sees“ the requirement for column groups as these columns have, naturally, no index and a column group is a „poor man’s“ index for frequencies and cardinalities, right?

So?

You end up with 23 column groups for a five partition table with over 120 million rows.

But what has that got to do with the Price of Beef?

So, dear, friends, what does our poor old RUNSTATS utility do now? It must farm out these column groups to DFSORT — and can you guess how it works out the allocation size??? You guessed it: 2000 + 8 for the maximum record size then multiplied by 23, for the number of column groups, then the result multiplied by the number of rows: 120,000,000. Do the math and you end up with a DFSORT storage requirement of over 5 PB (Yep that’s PETA bytes!), then the Storage Admin freaked out!

Easy fix: Delete all COLGROUP definitions for this table not backed by a real index. RUNSTATS DELETE PROFILE is a great help here! Then switch off SYSSTATFEEDBACK for this table and control *all* other column groups because as I like to say „where there is one, there are probably more“.

Bugblatter Beast of Traal

It is possible to wrap your head in a towel so the beast does not see you, but it is sometimes better to actually look for these things before they get really really bad. I hate to think how long this RUNSTATS was just „growing and growing and growing“ with no-one noticing that it was quite simply insane!

SYSSTATFEEDBACK is good but not that good!

Remember, it is doing the best it can based on the SQL usage and what the Db2 Optimizer thinks is missing or might improve performance. In this case, the excessive number of column groups and the excessive size of the groups was actually way more of a problem than a help!

Parameter Markers…

It would also have been fine if the developers had coded good SQL with parameter markers so that SYSSTATFEEDBACK would not have started the whole problem in the first place! As a secondary bonus, moving to parameter markers stops any SQL Injection attack vectors as you could do a lot of damage with a VARCHAR(2000) text field!!! Just using „A‘ OR ‚A‘ = ‚A“ would be nice and evil, wouldn’t it!!! Returns every single row because in the code it is stringed into a delimited string. So, if the customer gives A as input it builds this string:

SELECT all my columns FROM mytable WHERE ADDRESS = 'A' ;

Now add my injection code:

SELECT all my columns FROM mytable WHERE ADDRESS = 'A' OR 'A' = 'A' ;

What does that OR do? Yep – It is always true so every row is always returned…Not what you would want with 120 million rows…

Stale Stats?

A last bit about SYSSTATFEEDBACK is that it recommends STALE quite a lot and some of these bogus entries are, in fact, just created by a STALE recommendation so one other way of clearing them all out is to run a little SQL like the following:

-- THIS SQL WILL CORRECT THE PROBLEM OF BOGUS COLUMN COLGROUP CAUSING
-- EXCESSIVE SORT ALLOCATION AND FAILING RUNSTATS.
--
-- WHAT IT DOES IS:
--
--    1) STOP SYSSTATFEEDBACK GENERATION FOR A GIVEN TABLE
--    2) CLEAN UP SYSCOLDIST AND SYSCOLDISTSTATS "F" ENTRIES WHICH
--       ARE LISTED IN SYSSTATFEEDBACK WITH A "F" AND "STALE" ENTRY
--    3) DELETE ALL "F" AND "STALE" ENTRIES FROM SYSSTATFEEDBACK
--
-- TWO VARIABLES WILL BE CREATED AND USE THE DEFAULT FOR NAME AND
-- CREATOR:
--
CREATE VARIABLE TAB_NAME    VARCHAR(128)
   DEFAULT 'MY_BAD_TABLE'
;
CREATE VARIABLE TAB_CREATOR VARCHAR(128)
   DEFAULT 'MY_BAD_CREATOR'
;
--
-- STOP SYSSTATFEEDBACK PROCESSING FOR THIS TABLE
--
UPDATE SYSIBM.SYSTABLES
SET STATS_FEEDBACK = 'N' 
WHERE CREATOR      = TAB_CREATOR
  AND NAME         = TAB_NAME
  AND TYPE         = 'T'
;
COMMIT ;
--
-- DELETE ANY STALE COLDIST FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDIST A
WHERE A.TBOWNER    = TAB_CREATOR 
  AND A.TBNAME     = TAB_NAME
  AND A.TYPE       = 'F'
  AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
              WHERE B.TBCREATOR = TAB_CREATOR
                AND B.TBNAME    = TAB_NAME
                AND B.TYPE      = 'F'
                AND B.REASON    = 'STALE'
                AND B.TBCREATOR = A.TBOWNER
                AND B.TBNAME    = A.TBNAME
                AND B.COLNAME   = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE COLDISTSTATS FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDISTSTATS A
WHERE A.TBOWNER    = TAB_CREATOR
  AND A.TBNAME     = TAB_NAME
  AND A.TYPE       = 'F'
  AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
              WHERE B.TBCREATOR = TAB_CREATOR
                AND B.TBNAME    = TAB_NAME
                AND B.TYPE      = 'F' 
                AND B.REASON    = 'STALE'
                AND B.TBCREATOR = A.TBOWNER
                AND B.TBNAME    = A.TBNAME
                AND B.COLNAME   = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE SYSSTATFEEDBACK FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSSTATFEEDBACK
WHERE TBCREATOR    = TAB_CREATOR
  AND TBNAME       = TAB_NAME
  AND TYPE         = 'F'
  AND REASON       = 'STALE'
;
COMMIT ;
--
-- DROP THE CREATED VARS FOR NEXT RUN
--
DROP VARIABLE TAB_NAME    ;
DROP VARIABLE TAB_CREATOR ;
COMMIT ;

Take care out there!

Caveat Emptor!

Remember to always review DELETEs like this *before* you do them in production. Blindly deleting stuff is sometimes dangerous and hazardous to your career path!

I hope you found this info interesting on a cold and dark January day, at least here in Germany!

TTFN,

Roy Boxwell

Audit handling in Finance & Insurance

21. April 2022 @ 16:00 – 17:00 UTC

SOFTWARE ENGINEERING GmbH is exited to play an important role in IBM‘s z/Security ecosystem.

Be Smart, be Secure and join our webinar. In cooperation with one of our customers we’ll show how WLX Audit is used to audit DBA activities to fulfill the BaFin guidelines, answering the Auditors questions… Who did What, When and from Where exploiting efficient OPX technology.

Virtual Event

Veröffentlicht am

GIVE and TAKE Programs 4, 5, 6, 7

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

Limited free-of-Charge Db2 Applications

Previous Give & Take

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

1 Index Maintenance Costs

2 EXPLAIN Suppression

3 BIF Usage

What we GIVE in 2020

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

What we TAKE

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

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

Current Give & Take 2020, Germany offers

4

Db2 11+12 Audit+ SIEM

with Optional Framework Eclipse or ZOWE IBM GUI

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

5

Access Path Recovery

April-June 2020 (2Q) – Presentation More

6

Space Assurance – K-no-w Limits

July – September 2020 (3Q) – Presentation – Flyer SAX More

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

7

ZOWE IBM GUI and SQL Workload Performance for Db2 12

Oct.-December 2020 (4Q)

We TAKE the anonymized results for research

and will communicate with the local User Groups for discussions

Inspiring experiences

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

Veröffentlicht am

2020-04 Four Flavors of Db2 Audit

These days there is a lot of talk about audit, specifically regarding Db2 on z/OS. So, in this newsletter, I wish to run through four different ways that you can “Get Audit Done”.

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

Four ways to get a Db2 z/OS Audit done

1- First up

First option is the simplest, cheapest and quickest:

Do nothing.

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

Naturally this is an absolute No No.

2- Then we have

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

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

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

3- Getting there 

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

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

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

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

4- Aha! The only true way 

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

Third party software that does it all for you.

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

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

Data Collected – Now what? 

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

First up 

There it is! 

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

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

Then we have 

A whole bunch of pre-written SQLs. 

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

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

Getting there

A whole bunch of pre-written and custom SQLs.

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

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

Aha! The only true way

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

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

This is the best way!

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

Db2 Audit with „GIVE&TAKE“ :

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

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

Give and Take 

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

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

More about Give&Take

TTFN, 

Roy Boxwell 

Tridex März 2019

TRIDEX  – Tri-State Db2 Technology Exchange – NY, USA – März 2019

SEGUS & SOFTWARE ENGINEERING präsentieren

1 – An Audit a day keeps the lawyers at bay!

> Pdf Präsentation

2 – ZOWE – The zGui (r)evolution – First hands on experience and best practices

> Pdf Präsentation

1 – An Audit a day keeps the lawyers at bay!

GDPR, GLB, HIPAA, PCI-DSS, Basel III, Sarbanes-Oxley, CA SB1386, Federal Information Security Management Act, “Red Flags”Rules confront us us with serious requirements to protect the data and to fulfil Auditors requests.

There are different ways and tools that promise they are able to do it, but what can they really do and what are the associated costs?

This presentation introduces Db2 technology exploitation that delivers DML, DDL, DCL activity in a Db2 environment along with identification details.

This presentation helps you understand the way Auditors look at Db2 and what they require in order to do their daily work. Learn how you can satisfy your Auditors needs, by interfacing with an SIEM system, like:

  • QRadar, Splunk, AlienVault, et al,
  • combining the Db2 information with RACF, SMF and Master Log data.
Mehr über Audit for Db2 z/OS

Speaker biography

Roy Boxwell has more than 33 years of experience in MVS, OS/390, and z/OS environments – 31 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.

2 – „ZOWE – The zGUI (r)evolution – First hands-on experience and best practices“

For a couple of years, the importance of a GUI for z/OS has seemed to grow significantly. This may be one of the most important factors if the z platform is to remain strategic over the next decade and beyond – probably less because of the potential benefit of a specific GUI implementation, but simply because recent generations of DBAs, SYSPROGS, and Programmers aren’t that familiar with the beloved green screen of those who’we been working with ISPF for decades.

DS, RDz, DSM, z/OSMF -to name just IBMs recent ones- always had a common downside, which I believe customers didn’t like at all: Users were unable to access all mainframe products from, and through, a common interface.

Bottom line:

We need a GUI that not only comes with a monitoring dashboard, job submission capabilities, includes a JES explorer, data set explorer and editor, scripting for automation and interaction with all major product, like CICS, Db2,

BUT it should be used by ALL vendors!

Of course, this GUI should also seamlessly integrate into all those robust security and resource management components we’re already familiar with. It needs to be the preferred interface for administrative tasks, development, test, and operation, no matter if using old applications, or brand-new ones.

Am I asking for too much?

ZOWE – THE z ecosystem to securely manage, control, script and develop – seems to be the way to go.

It comes with a RESTful API, an extensible command line interface and an HTML 5 web-based UI framework designed to fuse everything we already have with anything we want to do today and tomorrow. It is exactly what many of us have been asking for years. We see a clear commitment from some of the most important companies in the mainframe world and we also see the technical components required to do our daily work and to open up new opportunities for tomorrows modern apps.

  • All well-integrated into RLF, SAF and USS and
  • interfacing with MVS, Db2 , CICS, and JES
  • as well as products from a variety of other vendors
  • and by the way, it’s EPL-2.0 (Eclipse Public License)

Late 2018 our company strategically decided to build UIs based on the ZOWE ecosystem.

Learn how you can quickly define entire workflows and interact and control them using a Db2 z/OS cloning example along with workload capture and replay in a simulation environment – fully automated quality assurance as part of continuous delivery in an agile world.

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.

Veröffentlicht am

2018-06 – DST Db2 timestamp problems: I really hate Daylight Saving Time

How to avoid timestamp problems while going from winter to summer time in a Db2 for z/OS system ?

Is the CHAR or Timestamp use, the safest timestamp procedure ?

How do you fix it?

This year, as every year, the moment arrives for most of us when the clocks go forward and then in autumn back again. I really hate this, as I still have a bunch of clocks that do not automatically do it for me. My PC, phone, laptop, TV etc. all do it for me but the rest… anyway what has this got to do with Db2 I hear you all wonder? Well it really is quite a horrible little story coming up…

Same procedure as every year

At precisely 02:00 on the 25th of March a SET CLOCK console command was issued to change the UTC Offset to +2 thus leaping from 02:00 to 03:00 in the blink of an eye.

How long?

Now “how long” is the blink of an eye? For Db2 these days – too long!

Day of reckoning

At 2018-03-25-02.00.00.006999 a transaction was logged in the Audit system, in fact *lots* of transactions were in-flight at this time. Normally it is not a problem and, in fact, nothing happened until nearly three months later when someone found that there was possibly some data missing.

Alarm!

Alarm bells are ringing as these inventory checks cannot have missing data. The code is nowadays all JAVA and the developer in charge of the problem found out that the data was indeed missing!

Oh no it isn’t!

The DBA group were then involved, as it could be data corruption, and they looked and found the data – but it was not the same data as the developers had… then the penny dropped!

Clever old JAVA

In fact, the data the developer had was *exactly* one hour later than the data found by the DBA group. I mentioned earlier that the 25th March was the switch to summer time and, perhaps, the JAVA Driver is “helping” us, a bit too much help if you ask me!

Date Check

Here is a bit of SQL for you to recreate the problem and gaze in wonder at how cool/horrible (delete what is not applicable) JAVA really is.

CREATE TABLE BOXWELL.DAY_LIGHT                           
  (COL1 SMALLINT     NOT NULL                            
  ,COL2 TIMESTAMP    NOT NULL)                           
;                                                         
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (1 , '2018-03-25-01.59.59.999999');               
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (2 , '2018-03-25-02.00.00.006999');               
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (3 , '2018-03-25-03.00.00.000099');               
COMMIT ;                                                 

SELECT * FROM BOXWELL.DAY_LIGHT                           
ORDER BY 1                                               
;
---------+---------+---------+---------+---------+--------
  COL1   COL2                                         
---------+---------+---------+---------+---------+--------
     1   2018-03-25-01.59.59.999999                     
     2   2018-03-25-02.00.00.006999                      
     3   2018-03-25-03.00.00.000099                      
DSNE610I NUMBER OF ROWS DISPLAYED IS 3

The output of SPUFI looks great! Timestamps are correct and all is fine.

It is a GUI world

Now do the select using a JAVA driver of your choice, here I am using DataStudio:

DST Db2 timestamp problems - Char - Daylight Saving Time

And then running it gives:
DST Db2 timestamp problems - Char- Daylight Saving Time

Spot the difference!

Isn’t that

great/terrifying (delete what is not applicable)

as the JAVA driver is “looking” at the timestamp data and seeing “oh oh! That timestamp is impossible! I know – I will add one hour to correct it!”

This scares me a little…actually quite a lot!

Docu – What Docu?

The only place I could find anything about this was in a chapter about not using 24 as midnight and the problem of using timestamps between October 5th 1582 and October 14th 1582:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/java/src/tpc/imjcc_r0053436.html

If you read it you can find this one sentence:

If a string representation of a date, time, or timestamp value does not correspond to a real date or time, Java adjusts the value to a real date or time value.

Which, of course, explains everything!

The quick fix…

There is no quick fix!

1 – The customer must either change all SQL to use the CHAR function – Not good!

Or

2 – Check all of their important timestamp columns for the range 02.00.00.000000 -> 02.59.59.999999 data and then update them with plus one hour – Not good!

Faster and Faster : the best fix ?

This problem will get worse the faster the machines get and so my idea to solve it next year is simply issue a

SET LOG SUSPEND

at one second before 02:00 which flushes the log, issues a system checkpoint (non data-sharing), updates the BSDS and basically pauses the system. Then do the SET CLOCK command and then do a

SET LOG RESUME

It all takes about three seconds and so should not cause any timeouts.

 

I really hope that, one day, we simply get rid of daylight saving time…

 

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

TTFN,

Roy Boxwell

Veröffentlicht am

2018-05 Audit 2.0 – GDPR Audit guide and checklist for Db2 z/OS

Or: How I Learned to Stop Worrying and Love the Auditor

A guideline in 5 big Steps to Audit for Personal Data Protection

First up, this is not another Audit review and health check sell! We, and I mean especially in the European Union (EU), are about to get the biggest update to “my data”, “my right to know” and “my right to forget (delete)” that has ever happened, when the General Data Protection Regulation (GDPR) Kicks off on May 25th, 2018.

Who cares?

Well, we all should really! Very soon hordes of people could well start demanding to know if you have any data about them and if so where it is and who you give it to. Personal Data will be big business!

Age of Consent

Just assuming all is ok is also no longer valid or lawful – any personal data that you keep must be kept with the *active* consent of the data owner… This is going to get awfully messy awfully quickly! Do not forget that the definition of personal data has also changed quite a bit e.g. IP Addresses. For the record here is the actual text in the GDPR regulations:

Art. 4 GDPR Definitions

For the purposes of this Regulation:

1.      ‘personal data’ means any information relating to an identified or identifiable natural person (‘data subject’); an identifiable natural person is one who can be identified, directly or indirectly, in particular by reference to an identifier such as a name, an identification number, location data, an online identifier or to one or more factors specific to the physical, physiological, genetic, mental, economic, cultural or social identity of that natural person;

And

Art. 9 GDPR Processing of special categories of personal data

1.      Processing of personal data revealing racial or ethnic origin, political opinions, religious or philosophical beliefs, or trade union membership, and the processing of genetic data, biometric data for the purpose of uniquely identifying a natural person, data concerning health or data concerning a natural person’s sex life or sexual orientation shall be prohibited.

Security by design and Security of processing

Very important are Articles 25 and 32:

Art. 25 GDPR Data protection by design and by default

1.       Taking into account the state of the art, the cost of implementation and the nature, scope, context and purposes of processing as well as the risks of varying likelihood and severity for rights and freedoms of natural persons posed by the processing, the controller shall, both at the time of the determination of the means for processing and at the time of the processing itself, implement appropriate technical and organisational measures, such as pseudonymisation, which are designed to implement data-protection principles, such as data minimisation, in an effective manner and to integrate the necessary safeguards into the processing in order to meet the requirements of this Regulation and protect the rights of data subjects.

2.       The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual’s intervention to an indefinite number of natural persons.

Last part of Paragraph two is important here.

Art. 32 GDPR Security of processing

1.      Taking into account the state of the art, the costs of implementation and the nature, scope, context and purposes of processing as well as the risk of varying likelihood and severity for the rights and freedoms of natural persons, the controller and the processor shall implement appropriate technical and organisational measures to ensure a level of security appropriate to the risk, including inter alia as appropriate:

1.      the pseudonymisation and encryption of personal data;

2.      the ability to ensure the ongoing confidentiality, integrity, availability and resilience of processing systems and services;

3.      the ability to restore the availability and access to personal data in a timely manner in the event of a physical or technical incident;

4.      a process for regularly testing, assessing and evaluating the effectiveness of technical and organisational measures for ensuring the security of the processing.

2.      In assessing the appropriate level of security account shall be taken in particular of the risks that are presented by processing, in particular from accidental or unlawful destruction, loss, alteration, unauthorised disclosure of, or access to personal data transmitted, stored or otherwise processed.

Here paragraphs 1.2, 1.4 and 2 are the biggies!

A small or large fine for you today?

The fines are also amazingly high. First, for the “minor” problem of being over 72 hours late when data leaks have occurred (a breach), is 2% of global turnover or €10,000,000 – whichever is *higher* and, if you are really naughty, like disregarding basic data laws, moving data abroad or ignoring an individual’s rights then you get hit for 4% of global turnover or €20,000,000 – again whichever is *higher*

First up?

I, for one, do not want to be the first company that makes the headlines… How about you?

Who are you afraid of?

Now I know that most of the talk about GDPR is “right to data” for the general public but we all know, as IT specialists, that the people to be really afraid of are ex-employees who did not leave on happy terms. They know *exactly* where the knife could best be put… They know exactly where, and on which platforms, all of the data really is.

Protect yourself – Due diligence

What can you do? Well the first thing is to actually understand what GDPR means to you and your firm’s data. Then you must be able to prove to auditors that you tried your very best. We will all probably get hacked at some time, but if you tried your best it is enough. Using all of the data you gather in a post-mortem, or for forensics, is also a very good idea as can be seen in paragraphs 2.3 and 2.4 below:

Art. 83 GDPR General conditions for imposing administrative fines

1. Each supervisory authority shall ensure that the imposition of administrative fines pursuant to this Article in respect of infringements of this Regulation referred to in paragraphs 4, 5 and 6 shall in each individual case be effective, proportionate and dissuasive.

 2. Administrative fines shall, depending on the circumstances of each individual case, be imposed in addition to, or instead of, measures referred to in points (a) to (h) and (j) of Article 58(2). When deciding whether to impose an administrative fine and deciding on the amount of the administrative fine in each individual case due regard shall be given to the following:

1. the nature, gravity and duration of the infringement taking into account the nature scope or purpose of the processing concerned as well as the number of data subjects affected and the level of damage suffered by them;

2. the intentional or negligent character of the infringement;

3. any action taken by the controller or processor to mitigate the damage suffered by data subjects;

4. the degree of responsibility of the controller or processor taking into account technical and organisational measures implemented by them pursuant to Articles 25 and 32;

The list goes on after these of course…

Db2 Audit – a new way?

Most sites use SMF and do daily cuts to then offload to a repository system of some sort on some sort of hardware. The problem here is that the amount of SMF data you are generating can swamp you and this method is not nearly quick enough. Waiting a day is 24 hours of the 72 that are available to you…

Faster, Better, Securer?

Can you do it faster?  Can you do it yourself faster?  Can you do it better? Maybe…

 

If you can handle OPx and a bit of High Level Assembler then “Bob’s your uncle!” as long as Bob is your Auditor of course…There are more than enough examples in the web about how to write and do OPx reading (There are even vendors that are willing to sell you their software 🙂 )

A guideline in 5 big steps to Audit in a new way for Personal Data protection :

1 – To do list – IFI commands, IFCIDS & Audit Class

Create a program that can issue the required IFI commands to start and stop traces and to issue the READA and READS IFI calls that you wish to have. If you don’t know which Audit Class is which IFCID – Here’s my handy list:

Audit ClassIFCIDsWhatWhy
1140Authorization failuresPossible brute force password attack
2141GRANTs and REVOKEsPossible temporary raising of privilege
3142CREATE/ALTER/DROP of table with AUDIT attributeRemoving the AUDIT attribute is a big red flag
41431st Change of table with AUDIT attributeSensitive data updates
51441st Select of table with AUDIT attributeSensitive data usage
6145BIND/PREPARE using table with AUDIT attributePossible usage of sensitive data
755, 83, 87, 169, 319SET CURRENT SQLID, end of identify, end of signon CICS/IMSElevation of privilege
823, 24, 25, 219, 220Db2 UtilitiesUNLOADing data to where?
9146, 392For customer usen/a
10269, 270, 271*

 

271 is not started automatically with Class 10

Establish reuse trusted context, trusted context CREATE/ALTER and Column Mask/Row Permission CREATE/DROP/ALTERAnything with MASKs or ROW Permissions must be checked
11361Successful accessOnly makes sense if working with AUDIT POLICY SYSADMIN or DBADMIN

So, what is missing from this picture?

Well, what about the COMMANDs to actually see what people are issuing on the machine? So you must also add IFCIDs 90 and 91. Then you do not see any of the DDL so you must start IFCID 62 to get that as you do want to see what people are ALTERing, creating and dropping don’t you?

2 – All done in design?

Once you have a system that gathers all these you must buffer them in memory and then do one of two things:

1) Write them out to a file for post-processing and enrichment later
2) Directly write them to your SIEM system of choice

I am not a fan of number 2 because if the SIEM system cannot accept the data then your data is lost…

With No. 1 you can keep processing even if the SIEM system cannot ingest, for whatever reason, and as long as you trigger yourself every 5 – 10 minutes or when the OPx buffer is full then that is “real time” enough for me!

3 – Care for something else for the weekend?

Plus you can enrich the data with extra stuff that the naked IFCIDs do not have attached to them e.g. map dbid/psid to a real database and tablespace etc.

Store all of this data in a bunch of Db2 tables

then kick-off a Db2 to a LEEF re-formatter program that reads all the audit data that has been collected since last time

writes it all out.

4 – Code Page – don’t forget the Code Page

Then you must do an ICONV for code page conversion, swiftly followed by a gzip to get it down to size and then copy it down to a USS file ready for your SIEM system to ingest its prey.

5 – Done, Finished and Complete

To prove that this system is always available you should also schedule a simple

GRANT SELECT ON SYSIBM.SYSDUMMY1 TO PUBLIC;

Every morning at 06:00 which you should see in the output every day. Remember – Due Diligence!

 

So now you are done and finished and can let the SIEM system do the completion!

 

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

TTFN,

Roy Boxwell

PS: Please note that the bold of parts of the GDPR text is only from me!

Southwest Db2 Users Group – Februar 2018

Db2 Forum.  Southwest Db2 Users Group – Februar 2018 – Grapevine (Dallas), TX, USA

SEGUS & SOFTWARE ENGINEERING sponsern diese Veranstaltung & präsentieren 

1 – Pdf Präsentation  –  Compliance with compliments! Viable Db2 z/OS workload tracking.

2 – Pdf Präsentation –   Db2 12 Continuous Delivery – New challenges for deployment.

3 – Pdf Präsentation –   Db2 z/OS Lies, Damn Lies, and Statistics… 

1 – Db2 z/OS Security Audit: Compliance with compliments! Viable Db2 z/OS workload tracking.

Audit and Compliance is a need that many companies want and have to fulfill.

There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces Db2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a Db2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.

Mehr über Db2 Audit

Presentation Outline

  • Audit needs and musts Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5.
  • Solution overview and their Pros/Cons Get an overview about the existing solutions and understand how they work.
  • The viable way – let Db2 do the magic! Learn about Db2 enhancements in Db2 10/11 that deliver the Db2 workload being processed and understand why it’s so efficient.
  • Customer results from the banking industry Receive some experience from a large banking company and how they successfully replaced their Db2 Audit feature based reporting by a modern SQL tracking and analytics process.

 

2 – Db2 12 Continuous Delivery – New challenges for deployment.

Fundamental changes in the Db2 z world often lead to concerns. Let’s face it – some changes force us to change! While a Db2 version migration usually took months, or even years, there will be no new Db2 version after 12, but continuous code drops.

This will have a tremendous impact on migration strategies, because we have to find a reliable way to test these code deliveries in a fraction of time. If we make it, Business Divisions will become enthused at how quickly new technology becomes available for new applications. This presentation will describe the difference between Code, Catalog, Function and Application Levels, how you can control them and how you can fallback in case of anomalies. It also illustrates how we still can be pro-active in testing without burning weeks and months.
Learn how to choose from four different levels of testing and a new way of automation. CD-Screening allows you to pick and choose from KPI based test automation. The levels include simple anomaly alerting, access path verification, clone Pre-apply and even workload capture/replay to easily discover different behaviour resulting from a new code Level.

Mehr über Db2 Continuous Delivery – CD

Presentation Outline

Joining this presentation, you’ll learn how to align Continuous Delivery to your Continuous Availability.

  • Agile, Continuous Delivery, DevOps – just buzz words, or new methodologies?
  • Db2 Code, Catalog, Function and Application Levels – differences and dependencies.
  • Activation/Deactivation of new code and how to fallback and when you can’t.
  • Different flavors of (pro-active) CD-Screening and how it can be automated:

* Anomaly alerting based on Incompatibility Change Indicators (ICIs)
* Dyn./Stat.Access Path Change Detection e.g.via Plan Management
* Clone based code change pre-apply exploiting Backup System
* Workload-KPI verification using SQL replay and KPI comparison

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

 

3 – Db2 z/OS Lies, Damn Lies, and Statistics…

– Benjamin Disraeli, Prime Minister of England (1868, 1874-1880)

The above line may, or may not, have been spoken well over 100 years ago, but the need for statistics and, above all else, accurate statistics is more important than ever in the Db2 world of today.

Mehr über Db2 RUNSTATS

Presentation Outline

  • Db2 RUNSTATS basics & catalog tables and Columns used for access path
  • IBM recommendations through the ages : from Db2 V3 to Db2 12
  • Db2 RUNSTATS advanced
  • SYSCOLDIST explained
  • RUNSTATS real world Q&A :
    use of SAMPLE, COLGROUP, PROFILE, REOPT (ONCE), TABLESAMPLE SYSTEM, HISTOGRAM, …
  • RUNSTATS reversal

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.

Heart of Texas Db2 Users Group – Februar 2018

HOTDUG – Heart of Texas Db2 User Group – Februar 2018 –  Austin, TX, USA

SEGUS & SOFTWARE ENGINEERING sponsern diese Veranstaltung & präsentieren

1 – Pdf Präsentation : Compliance with compliments! Viable Db2 z/OS workload tracking.

2 – Pdf Präsentation : Db2 12 Continuous Delivery – New challenges for deployment.

3 – Pdf Präsentation : Db2 z/OS Lies, Damn lies, and Statistics… 

1 – Db2 z/OS Security Audit: Compliance with compliments! Viable Db2 z/OS workload tracking.

Audit and Compliance is a need that many companies want and have to fulfill.

There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces Db2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a Db2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.

Mehr über Db2 Audit

Presentation Outline

  • Audit needs and musts Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5.
  • Solution overview and their Pros/Cons Get an overview about the existing solutions and understand how they work.
  • The viable way – let Db2 do the magic! Learn about Db2 enhancements in Db2 10/11 that deliver the Db2 workload being processed and understand why it’s so efficient.
  • Customer results from the banking industry Receive some experience from a large banking company and how they successfully replaced their Db2 Audit feature based reporting by a modern SQL tracking and analytics process.

 

2 – Db2 12 Continuous Delivery – New challenges for deployment.

Fundamental changes in the Db2 z world often lead to concerns. Let’s face it – some changes force us to change! While a Db2 version migration usually took months, or even years, there will be no new Db2 version after 12, but continuous code drops.

This will have a tremendous impact on migration strategies, because we have to find a reliable way to test these code deliveries in a fraction of time. If we make it, Business Divisions will become enthused at how quickly new technology becomes available for new applications. This presentation will describe the difference between Code, Catalog, Function and Application Levels, how you can control them and how you can fallback in case of anomalies. It also illustrates how we still can be pro-active in testing without burning weeks and months.
Learn how to choose from four different levels of testing and a new way of automation. CD-Screening allows you to pick and choose from KPI based test automation. The levels include simple anomaly alerting, access path verification, clone Pre-apply and even workload capture/replay to easily discover different behaviour resulting from a new code Level.

Mehr über Db2 Continuous Delivery – CD

Presentation Outline

Joining this presentation, you’ll learn how to align Continuous Delivery to your Continuous Availability.

  • Agile, Continuous Delivery, DevOps – just buzz words, or new methodologies?
  • Db2 Code, Catalog, Function and Application Levels – differences and dependencies.
  • Activation/Deactivation of new code and how to fallback and when you can’t.
  • Different flavors of (pro-active) CD-Screening and how it can be automated:

* Anomaly alerting based on Incompatibility Change Indicators (ICIs)
* Dyn./Stat.Access Path Change Detection e.g.via Plan Management
* Clone based code change pre-apply exploiting Backup System
* Workload-KPI verification using SQL replay and KPI comparison

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

 

3 – Db2 z/OS Lies, Damn lies, and Statistics…

– Benjamin Disraeli, Prime Minister of England (1868, 1874-1880)

The above line may, or may not, have been spoken well over 100 years ago, but the need for statistics and, above all else, accurate statistics is more important than ever in the Db2 world of today.

Mehr über Db2 RUNSTATS

Presentation Outline

  • Db2 RUNSTATS basics & catalog tables and Columns used for access path
  • IBM recommendations through the ages : from Db2 V3 to Db2 12
  • Db2 RUNSTATS advanced
  • SYSCOLDIST explained
  • RUNSTATS real world Q&A :
    use of SAMPLE, COLGROUP, PROFILE, REOPT (ONCE), TABLESAMPLE SYSTEM, HISTOGRAM, …
  • RUNSTATS reversal

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.