Category: News from the Labs

Each month, SOFTWARE ENGINEERING sends out valuable technical DB2 z/OS tips and news based on knowledge gained by the experienced developers and technicians in our product labs. We would like to share this newsletter with other DB2 users, like yourself.

Posted on

2016-08 – Is it Safe? How to recover accidently dropped tables

Do you have the DDL anywhere?
Was there a “recent” image copy or disk back-up? Who can you call for help?

The newsletter title this month is really nothing to do with the film “Marathon Man”, but sometimes backup and recovery can feel just like having your teeth drilled… Anyway, the title is actually meant to get you to think again about your site’s back-up and recovery definitions—specifically that age-old chestnut about “accidently” dropped objects.

RECOVER from DROP?

I saw in LISTSERV a discussion about recovering from dropped tables and tablespaces. It is a pretty horrible situation when you realize that, you just confirmed the drop of a test table to then suddenly see that as your finger is descending towards the ENTER key that it is, in fact, a different name…
recover accidently dropped db2 zos table based on DDL extraction

DDL and Back-ups Handy?

Normally, at this point, the air is filled with colorful language and interesting local metaphors. Once it has calmed down a bit the real work starts: Do you have the DDL anywhere? Was there a “recent” image copy, or a disk back-up? Who can you call for help?

Mirror Mirror on the wall

Remember that mirroring etc. will not help you as the DROP has also been successfully mirrored. So within a moment, the data was also dropped at your disaster recovery site. (Argh!)

Newbies then start looking in SYSIBM.SYSCOPY for the last image copies, while us grey-haired oldies more mature experts start looking in production control copy libraries and BETA92!

The heat is on!

At this point a couple of things happen: The telephone starts ringing and a manager-type person materializes to ask annoying questions all the time. Typically: “How long will it take to get the data back?”

Now you have two possibilities:

1.You are in luck! Someone somewhere extracted all the DDL for the table(s) with DBID, PSID, and OBID(s) so you could generate a DSN1COPY job from the last found Image Copy dataset

recover accidently dropped db2 zos table based on DDL extraction

2. You are *not* in luck! You have no idea how the table looks, and you cannot see if anyone ALTERed it in the last five years or so.

Crashed and Burned

If you are in position 2, it is now a good time to update your CV and make sure your desk is neat and tidy… Now you might have access to some nifty third party vendor tools, but for that you must at least have the dataset name of the last Image Copy and—of course—the third party tool itself! Or the ability to read the DB2 log and “resurrect” the table defs from there—Not a pretty place to go I assure you!

Back up that chain of thought for a Moment

So let’s rewind and imagine that you are doing this all differently… What about beginning today with an extract of all the DDL on your system? Then capture all of the IFCID 62’s that show any DDL changes. Going further: what about getting all of the IFCID 220’s to get dataset allocations correlated with Utility IDs and DBIDs and PSIDs.

Imagine what you could then do?

Wow! Cavalry over the hill

Yep, you have a ”history” of all the DDL that has happened on your machine right up to the point when your object was DROPed *and* you have the dataset name(s) of the last image copies as well as all the internal IDs to enable a successful DSN1COPY job complete with OBID translation! Cool huh? So suddenly you are now a hero instead of a villain!

recover accidently dropped db2 zos table based on DDL extraction

recover accidently dropped db2 zos table based on DDL extraction

DIY or Buy in?

So much for the theory – What about in practice? Well you can write it all yourself or you could use this as a sort of useful side effect from our WorkloadExpert (WLX) software which has all this built in! We already get all these IFCIDs, we already have a DDL Generator bundled with the WLX software for the Audit Use Cases and so it really kills two birds with one stone!

Restricted movement?

Now, of course, you could argue:“Wait! I have RESTRICT ON DROP set for all my productive tables!” Now this works really well for accidental drops, but I have seen lots of places where it should be used but was in fact forgotten. How can you check? Run this little SQL to validate that what you think is true really is true:

recover accidently dropped db2 zos table based on DDL extraction

SELECT COUNT(*) AS TABLES                                     
       ,COUNT(CASE WHEN CLUSTERTYPE = 'Y' THEN 1 ELSE NULL END) 
                 AS DROP_RESTRICT                               
       ,COUNT(CASE WHEN CLUSTERTYPE = ' ' THEN 1 ELSE NULL END) 
                 AS NOT_DROP_RESTRICT                           
FROM SYSIBM.SYSTABLES                                         
WHERE     TYPE   IN ('T' , 'M' , 'H' , 'R' )

  AND NOT DBNAME IN ('DSNDB01' , 'DSNDB06' )

WITH UR
 ;

 

I hope the results don’t have you feeling like Dustin Hoffman in the movie.

As usual any questions or comments are welcome,

TTFN Roy Boxwell

Posted on

2016-07 – Migration Planning : Stuck in a BIND

Migration planning to DB2 11 z/OS:

Two queries to list the NULLID and the PLAN packages that need a DB2 REBIND

I was at a customer site the other week and we were getting weird data out of the IFCIDs to read the Dynamic Statement Cache (DSC) – in fact, it was ending with an RC 00E60833, which was very odd as that has nothing to do with the DSC!

Old Structure problem?

Anyway, I had a hunch that perhaps old structures were causing grief as, at this site, the problems were happening only for about four hours overnight; never during peak load and absolutely never, ever in Test or QA.

HealthCheck time!

So I ran our little PerformanceHealthCheck freeware and, among a ton of other info, got the following data:

PHC009W Packages last bound in DB2 V0710         :     358

Now this is OK, as long as they are *not* being executed! So, looking into our WorkloadExpert tool, we could see that there were lots of SQLs—both static and dynamic—that were running in these DB2 V7 last bound packages.

NULLID Problem?

What was especially worrying, was the high number of NULLID packages that were in the list. These NULLID Collections are normally used by JAVA and other remote access routines. They are normally always allocated and in-use—making a REBIND a bit of a challenge. I wrote a little SQL to list out the possible bad guys:

SELECT SUBSTR(A.COLLID, 1, 18) AS COLLID 
, SUBSTR(A.NAME, 1, 8) AS PACKAGE
, A.RELBOUND 
, A.LASTUSED
FROM SYSIBM.SYSPACKAGE A 
WHERE A.RELBOUND IN (' ', 'K', 'L', 'M', 'O')
AND   A.LASTUSED > CURRENT DATE - 14 DAYS 
ORDER BY A.LASTUSED DESC, A.NAME 
WITH UR
;

The contents of the column RELBOUND are:

Blank is before DB2 V7, K is DB2 V7, L is DB2 V8, M is DB2 9, O is DB2 10, and P is DB2 11.

Of course your query must be changed, depending on what level of DB2 system you are currently running on. If running on a DB2 10 NFM then remove the ‘O’ in the IN list. It will then return all executed (so in use!) packages that have run within the last 14 days – Feel free to change the ORDER BY too, if you like.

Our output was quite scary as lots of packages were there.

REBIND the world

The DBAs rebound all of the static packages that had been executed this year, (not quite the world, but still way more than I thought could still be running anywhere in the world!) and then, on a Sunday morning, when they could “break in” – they rebound all of the NULLID packages on the list.

Since then – no problem! But is this the end of my story? Naturally not!

Now this particular customer is on DB2 10 NFM, and so these very old structures made we wonder…I did a post on LISTSERV about this. Pat Bossman, from IBM, answered (Corrections from Pat came in a later mail and I have cut out some extra clutter) :

Hello,

For migration to DB2 10, packages last bound [before] DB2 6 or higher are autobound.

http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.inst
/src/tpc/db2z_relincompatapplsqlfromv9.dita?lang=en

For migration to DB2 11, it’s [before] Version 9 and higher.

http://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.inst/
src/tpc/db2z_relincompatapplsqlfromv10.dita?lang=en

With RELBOUND of V7, you should hit it in on migration to DB2 11. So yes, you should REBIND those packages on DB2 10 to avoid post-migration autobind.

I’m looking into the nullid package issue. The assumption if a structure does not have embedded SQL it does not require REBIND is false. We still require PLAN REBINDs, or they also are autobound on migration – even if the PLAN does not have DBRMs anymore. There are still structures in there that need updating.

(Don’t forget about PLAN REBINDs!)

Best regards,

Pat

PLAN Ahead

So now you know! Even PLANs should get a REBIND *before* you migrate, here you can use our EarlyPrecheck or BIX software to aid in the pre-migration checklist, in order to stop any AUTOBIND funnies that could occur. In fact, I would recommend REBINDing the PLAN after successful completion of the DB2 release migration. It will save you a step in a year or two.

 

Finally I wrote another little SQL to give the “overview” of all “old” PLANs:

-- LIST OUT ALL EXECUTED PLANS BOUND IN "OLDER VERSIONS"
 -- OF DB2
 SELECT C.NAME AS PLAN
 FROM SYSIBM.SYSPLAN     C
 WHERE C.RELBOUND IN (' ', 'K', 'L', 'M', 'O'))
 ORDER BY 1
 WITH UR
 ;

(Again, remove the ‘O’ is you are in DB2 10 NFM.) This then gives you, hopefully, a small list of PLANs that need a REBIND and you are ready to go!

 

I hope you liked this month’s topic.

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

TTFN,

Roy Boxwell

 

Posted on

Can you guess how interesting the DSN_PREDICAT_TABLE really is?

DB2 z/OS literal replacement:

Do you know the queries to list the Dynamic SQL which have literals or Parameter markers?

Here is a very handy method to (fairly) quickly see if you have a literal problem that is killing the DSC or causing access path problems.

 

I was asked by a customer the other week, to help them out with a classic DB2 SQL Performance problem involving dynamic SQL. Should Literals or Parameter markers be used in dynamic SQL? Now, of course, the classic answer is: “It depends.” But this customer wanted to go a stage further and get a list of all the dynamic SQL which had literals in the text.

 

EXPLAIN to the rescue!

Naturally an EXPLAIN must be the starting point, because manually parsing the SQL text is incredibly difficult. If you have paid for the DB2 Optimizer—and the ability of EXPLAIN to output to seventeen tables—why not use it and see what you get?

Manual time

After trolling through various manuals, it quickly becomes apparent that the table of choice is the DSN_PREDICAT_TABLE, as it contains at least one row for each predicate and, after all, it is predicates that are truly interesting for the optimizer. (Naturally DSC usage also hangs on the use, or not, of literals also in the SELECT—but that is another story!)

What is in the table?

The contents are quite simple really: 

SELECT  A.QUERYNO                 Identification

      , A.QBLOCKNO                Identification

      , A.APPLNAME                Identification

      , A.PROGNAME                Identification

      , A.PREDNO                  Identification

      , A.TYPE                    Type of op AND, OR, EQUAL etc

      , A.LEFT_HAND_SIDE          Column/Value/colexp etc

      , A.LEFT_HAND_PNO           Child predicate number

      , A.LHS_TABNO               Table no.

      , A.LHS_QBNO                Query Block no.

      , A.RIGHT_HAND_SIDE         As left hand

      , A.RIGHT_HAND_PNO          As left hand

      , A.RHS_TABNO               As left hand

      , A.RHS_QBNO                As left hand

      , A.FILTER_FACTOR           Estimated FF

      , A.BOOLEAN_TERM            Whole WHERE is Boolean?

      , A.SEARCHARG               DM or RDS?

      , A.JOIN                    Simple join or not?

      , A.AFTER_JOIN              Predicate after/during join?

      , A.ADDED_PRED              T Transitive clos., B Bubble,
                                  C correlation,

                                  J Join, K like, L local, P push down,

                                  R page range, S simplification

      , A.REDUNDANT_PRED          Is the predicate redundant?

      , A.DIRECT_ACCESS           ROWID Possible?

      , A.KEYFIELD                Is the predicate in indexes?

      , A.EXPLAIN_TIME            Identification

      , A.CATEGORY                IBM Internal use

      , A.CATEGORY_B              IBM Internal use

      , A.TEXT                    First 2000 bytes of text

      , A.PRED_ENCODE             IBM Internal use

      , A.PRED_CCSID              IBM Internal use

      , A.PRED_MCCSID             IBM Internal use

      , A.MARKER                  Host vars, parameter markers,
                                  special regs

      , A.PARENT_PNO              If a root predicate then zero

      , A.NEGATION                Is NOT used?

      , A.LITERALS                Literals separated by colons

      , A.CLAUSE                  HAVING, ON, WHERE or SELECT

      , A.GROUP_MEMBER            Identification

      , A.ORIGIN                  Origin of predicate. Blank, C, R or U

      , A.UNCERTAINTY             Level of uncertainty

      , A.SECTNOI                 Identification

      , A.COLLID                  Identification

      , A.VERSION                 Identification

--V11 , A.EXPANSION_REASON        Archive or Temporal table indicator

FROM BOXWELL.DSN_PREDICAT_TABLE A

 

The first four columns are used for joining to your existing EXPLAIN tables (PLAN_TABLE etc.)

Columns of interest

Now what interested me straightaway, were the columns MARKER and LITERALS. Looking in the Managing Performance documentation you see that these columns:

MARKER CHAR(1) NOT NULL WITH DEFAULT

Whether this predicate includes host variables, parameter markers, or special Registers.

LITERALS VARCHAR(128) NOT NULL

This column indicates the literal value or literal values separated by colon symbols.

 

So now it looks quite easy just do a select where LITERALS is non blank. Of course that fails miserably…

First attempt

Here’s an SQL that shows what comes out:
DSN; predicat table, query1-DB2-z-OS-literal-replacement

DSN; predicat table, query1-DB2-z-OS-literal-replacement

This looks like lots of literals, but is actually just one WHERE predicate and one ON being broken down. So I thought “Aha! The PARENT_PNO must be the problem.” Sure enough, when you add AND PARENT_PNO = 0 to the SQL it now Returns:

DSN; predicat table, query1-DB2-z-OS-literal-replacement

Now all of the ON data has vanished, so you must add an OR into the query:

Second attempt

Giving us now this Output:

DSN; predicat table, query1-DB2-z-OS-literal-replacement

The COLEXP row SQL text looks like:

SUBSTR(DIGITS("A"."CORR_TYPE"),1,10)

So we can also exclude these rows from the select.

DSN; predicat table, query1-DB2-z-OS-literal-replacement

Still duplicates?

This*still* looks like duplicates, but now pushed down to the ON clause in this context. Final fix is to make sure that the LHS_TABNO and RHS_TABNO are both equal to zero. Now we have the “literal finder” SQL:
DSN; predicat table, query1-DB2-z-OS-literal-replacement

Host with no colon?

Which gives (excluding the first columns for clarity!):
DSN; predicat table, query1-DB2-z-OS-literal-replacement

See the HV2 and then the list of HVs? Now this is not documented at all! A comma separated list of host variables… super…

All we can now do, at least in standard SQL, is split the output into two distinct blocks, one where MARKER is ‘N’ so no host variables or special registers are involved, and one where they are! It should be easy to remove the HVs and then see if the LITERALS column is empty—or not—but that is a bit more than a simple newsletter can possibly do!

And Finally

So now the final two queries:

SELECT QUERYNO , QBLOCKNO , PREDNO                 
      ,LITERALS
      ,TEXT                                        
FROM BOXWELL.DSN_PREDICAT_TABLE                    
WHERE NOT LITERALS = ''                            
  AND ((CLAUSE = 'WHERE'                           
    AND PARENT_PNO = 0)                            
    OR CLAUSE = 'ON')                              
  AND NOT RIGHT_HAND_SIDE = 'COLEXP'               
  AND LHS_TABNO = 0                                
  AND RHS_TABNO = 0                                
  AND MARKER    = 'N'                              
ORDER BY QUERYNO , QBLOCKNO , PREDNO               
;                                                  
SELECT QUERYNO , QBLOCKNO , PREDNO                 
      ,LITERALS
      ,TEXT                                        
FROM BOXWELL.DSN_PREDICAT_TABLE                    
WHERE NOT LITERALS = ''                            
  AND ((CLAUSE = 'WHERE'                           
    AND PARENT_PNO = 0)                            
    OR CLAUSE = 'ON')                              
  AND NOT RIGHT_HAND_SIDE = 'COLEXP'               
  AND LHS_TABNO = 0                                
  AND RHS_TABNO = 0                                
  AND MARKER    = 'Y'                              
ORDER BY QUERYNO , QBLOCKNO , PREDNO               
;

This gives you a very handy method to (fairly) quickly see if you have a literal problem that is killing the DSC or causing access path problems at your shop.

I hope you liked this month’s topic.

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

TTFN,

Roy Boxwell

 

Once again for your cut and paste :

Query 1

SELECT CLAUSE, PARENT_PNO, SUBSTR(RIGHT_HAND_SIDE , 1 , 8)
      ,LHS_TABNO, RHS_TABNO                               
      ,MARKER, LITERALS                             
FROM BOXWELL.DSN_PREDICAT_TABLE                           
WHERE NOT LITERALS = ''    

Output1                               
-----+-------+---------+---------+---------+---------+---------+
CLAUSE PARENT_PNO        LHS_TABNO  RHS_TABNO  MARKER  LITERALS
-----+-----+---------+---------+---------+---------+-----------+
WHERE      0                    0           0  N       0,1,'W' 
WHERE      1  VALUE             2           0  N       0,1     
WHERE      1  VALUE             3           0  N       'W'     
ON         1  COLEXP            2           0  N       1,10    
ON         1  VALUE             2           0  N       'F'     
ON         1  VALUE             2           0  N       'en_US'
Output2
-----+---------+---------+---------+---------+---------+---------+------
 CLAUSE     PARENT_PNO            LHS_TABNO  RHS_TABNO  MARKER  LITERALS
-----+---------+---------+---------+---------+---------+---------+------
 WHERE               0                    0          0  N       0,1,'W'

Output3
-----+--------+--------+---------+---------+-----------------
 CLAUSE PARENT_PNO     LHS_TABNO RHS_TABNO MARKER LITERALS
-----+-------+--------+---------+----------------------------
 WHERE    0                 0            0  N     'X',' ',
 220,219,1,10,'F’
 ON       1  COLEXP         2            0  N      1,10
 ON       1  VALUE          2            0  N     'F'
 ON       1  VALUE          2            0  N     'en_US'
 WHERE    0  VALUE          3            0  N     'X'
Output4
-----+---------+---------+---------+---------+----------+----
 CLAUSE PARENT_PNO    LHS_TABNO  RHS_TABNO  MARKER LITERALS
-----+---------+---------+---------+---------+---------+----
 WHERE   0                0              0  N      'X','
                                                   ',220,219,1,10,'F’
 ON      1  VALUE         2              0  N      'F'
 ON      1  VALUE         2              0  N      'en_US'
 WHERE   0  VALUE         3              0  N      'X'

Query 2

SELECT QUERYNO , QBLOCKNO , PREDNO                 
      ,MARKER, SUBSTR(LITERALS, 1 , 32) AS LITERALS
      ,TEXT                                        
FROM BOXWELL.DSN_PREDICAT_TABLE                    
WHERE NOT LITERALS = ''                            
  AND ((CLAUSE = 'WHERE'                           
    AND PARENT_PNO = 0)                            
    OR CLAUSE = 'ON')                              
  AND NOT RIGHT_HAND_SIDE = 'COLEXP'               
  AND LHS_TABNO = 0                                
  AND RHS_TABNO = 0                                
ORDER BY QUERYNO , QBLOCKNO , PREDNO               
;

Output5
MARKER  LITERALS                          TEXT                                    
---+---------+---------+---------+---------+---------+---------+--------
N 'X','X','T'                  (((((((((((("B"."WLX_TIMESTAMP"=(SELECT 
N 'X','X','T'                  (((((((((((("B"."WLX_TIMESTAMP"=(SELECT 
Y  HV2,'S'                     ("IQA0610"."IQATW001"."WLX_TIMESTAMP"=(E
N 'X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
N 'X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
N 'X','X','I','  '             ((((((((((("B"."WLX_TIMESTAMP"=(SELECT M
N 'X','X','I','  '             ((((((((((("B"."WLX_TIMESTAMP"=(SELECT M
N 'X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
N  X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
Y  HV1,HV2,HV3,HV4,HV5,'F',HV6 ((((("SYSIBM"."SYSCOPY"."DBNAME"=(EXPR)
Posted on

2016-05 What’s in an Acronym? ICI

Do you know the difference between BIF & ICI?

Release migration: Do you know which role ICIs are playing in the Db2 compatibility?

Just over a year ago I did a little newsletter all about IFICDs 366 and 376, what I call BIFCIDs, that report usage of a function that has changed or a function that works now, but will either fail or work differently, at some point in the future, e.g.: after a Db2 release migration.

When is a BiF not a BiF?

Now, back then I labelled these guys “BIFCIDs” and asked: “Where’s the BiF?” I see now that the name I chose was actually pretty inaccurate! If you take the time to study the macro versions of 366 and 376 (all described in the SDSNMACS library member DSNDQW05) you see that from the 22 IFCIDs only six are actually for a Built-in Function “problem” – all the rest are really ICIs.

What’s an ICI? It’s a TLA (another nice acronym), from Db2 development and stands for “Incompatible Change Indicator”. A really catchy name for a really nasty problem! So let us review the IFCID details, but now from the perspective of “Are you a BIFCID or are you an ICI?”

 

The details for BIFCIDs – die BIFs der IFCIDS list

***********************************************************************
**  QW0366FN = 1                                                      *
**    Indicates that the pre Version 10 CHAR built-in function has    *
**    been invoked. There is an incompatible change to the output of  *
**    the CHAR function for some decimal data. The zparm              *
**    BIF_COMPATIBILITY and/or the SYSCOMPAT_V9 schema have been used *
**    by this application to get the old behavior. Please make the    *
**    appropriate changes and rebind with the SYSCURRENT schema to    *
**    use the Version 10 CHAR(decimal) built-in function.             *
**    (PM29124 V10 only, usermod V8/V9)                               *
**  QW0366FN = 2                                                      *
**    Indicates that the pre Version 10 VARCHAR built-in function or  *
**    CAST(decimal AS CHAR or VARCHAR) has been invoked.              *
**  QW0366FN = 10                                                     *
**   RTRIM, LTRIM or STRIP version 9 being used with mixed data       *
**  QW0366FN = 1109                                                   *
**    Indicates that CAST(string AS TIMESTAMP) was processed for the  *
**    input string of length 8 and input was treated as a store clock *
**    value (or input string was of length 13 and was treated as a    *
**    GENERATE_UNIQUE value). This behavior is incorrect for a CAST   *
**    and is valid for TIMESTAMP built-in function only. This behavior*
**    is being corrected in Db2 11 so that input to CAST is not       *
**    treated as a store clock value nor GENERATE_UNIQUE.             *
**  QW0366FN = 1110                                                   *
**    Indicates the integer argument of SPACE function is greater     *
**    than 32764.                                                     *
**  QW0366FN = 1111                                                   *
**    Indicates the optional integer argument of VARCHAR function     *
**    has a value greater than 32764.                                 *
***********************************************************************

 

IFCIDS list follow: the details for ICIs

***********************************************************************
**  QW0366FN = 3                                                      *
**    Indicates that an unsupported character representation of a     *
**    timestamp string was used. PM48741 V10 only.                    *
**  QW0366FN = 4                                                      *
**   A QW0366FN 4 record indicates that the statement uses the        *
**    word ARRAY_EXISTS as an unqualified user-defined function name  *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 5                                                      *
**    A QW0366FN 5 record indicates that the statement uses the       *
**    word CUBE as an unqualified user-defined function name          *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 6                                                      *
**   A QW0366FN 6 record indicates that the statement uses the        *
**    word ROLLUP as an unqualified user-defined function name        *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 7                                                      *
**   A QW0366FN 7 record indicates that Db2 for z/OS server issued    *
**   a SQLCODE -301 for incompatible data type conversion from        *
**   string data type (e.g. CHAR, VARCHAR, GRAPHIC, VARGRAPHIC        *
**   etc.) to numeric data type in V10 CM mode when implicit          *
**   cast is not supported or V10 NFM mode when DDF_COMPATIBILITY     *
**   zparm is set to DISABLE_IMPCAST_NJV or SP_PARMS_NJV to           *
**   disable implicit cast, and the client is CLI driver              *
**   or v11 NFM mode & APPLCOMPAT = V10R1 when DDF_COMPATIBILITY      *
**   is set to SP_PARMS_NJV or DISABLE_IMPCAST_NJV to disable         *
**   implicit cast either from string data type to numeric or         *
**   from numeric data type to string data type.                      *
**  QW0366FN = 8                                                      *
**   A QW0366FN 8 record indicates that Db2 for z/OS server           *
**   returned output data match the data types of the                 *
**   corresponding CALL statement arguments when DDF_COMPATIBILITY    *
**   zparm is set to SP_PARMS_NJV.                                    *
**                                                                    *
**  QW0366FN = 9                                                      *
**    A QW0366FN 9 record indicates a data type conversion from       *
**    a TIMESTAMP WITH TIME ZONE input to a TIMESTAMP data            *
**    during input host variable bind-in process on server when       *
**    DDF_COMPATIBILITY zparm is set to IGNORE_TZ to ignore the       *
**    time zone information sent by Java IBM Data Server Driver.      *
**  QW0366FN = 1101                                                   *
**    Indicates that the INSERT statement that inserts into an XML    *
**    column without XMLDOCUMENT function has been processed (which   *
**    should result in SQLCODE -20345 when run on Db2 release prior   *
**    to V11). Starting with V11, SQL error will no longer be issued. *
**    Application will no longer recieve SQLCODE for this statement.  *
**  QW0366FN = 1102                                                   *
**    Indicates that V10 XPath evaluation behavior was in effect which*
**    resulted in an error. For instance, a data type conversion error*
**    could have occured for a predicate that would otherwise be      *
**    evaluated to false. Starting from V11, such "irrelevant" errors *
**    might be suppressed so an application might no longer recieve   *
**    the SQLCODE for this statement.                                 *
**  QW0366FN = 1103                                                   *
**    Indicates that a dynamic SQL uses the ASUTime limit that has    *
**    been set for the entire thread for RLF reactive governing.      *
**    For instance, when a dynamic SQL is processed from package A,   *
**    if the ASUTime limit is already set during other dynamic SQL    *
**    processing from package B in the same thread, the SQL from      *
**    package A will use the ASUTime limit set during the SQL         *
**    processing from package B. Stating with v11, dynamic SQLs from  *
**    multiple packages will use the ASUTime limit that is set        *
**    considering its own package information.                        *
**  QW0366FN = 1104, 1105, 1106, 1107                                 *
**    Indicates that CLIENT special register (CLIENT_USERID,          *
**    CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set *
**    to a value that is longer than what is supported prior to V11.  *
**    A shorter value has been used instead.                          *
**  QW0366FN = 1108                                                   *
**    Indicates that CLIENT special register (CLIENT_USERID,          *
**    CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set *
**    to a value that is longer than what is supported prior to V11.  *
**    Truncated values upto the supported lengths prior to v11 have   *
**    been used for RLF table search instead.                         *
**  QW0366FN = 1112                                                   *
**   Indicates the empty XML element is serialized to <X></X>         *
**   instead of <X/>.                                                 *
***********************************************************************

What does this all mean?

You might well be wondering: “What’s the point?” Well, the point is to try and get the ICI’s acknowledged in your company and to start checking for them now. Why do I say this? Because the APPLCOMPAT is only going to work for two down levels. What that means is that APPLCOMPAT at Db2 10 is good for Db2 11 and Db2 12 but *dies* horribly at Db2 12 +1 which, if IBM stick to their track record of Db2 delivery dates, will be October 2019. Not that far away in the future is it?

 

The BiF problem is fixed – long live the ICI Problem!

OK, you might have traced and tracked your BIFs, but what about the ICIs? At one firm I visited, all the BIFCIDs were clean until this February which, as a leap year, had 29 days. This caused different programs to run and…Tra La! New BIFCIDs came creeping out of the woodwork…along with some new ICIs…

 

Freeware to the rescue

To help you save time, just download and test our little BiF Freeware, which tracks down all ICIs and BiFs for you. Register, download, and then see where your system stands in the ICI rankings. We have also changed our Use Case “BIF usage” in WorkloadExpert to be only for BiFs and have introduced a new Use Case called “BIF/ICI occurrences” which shows them all.

As usual, any question or comments gladly welcome!

 

TTFN

Roy Boxwell

Posted on

2016-04 DB2 SYSCOPY – Do you know what is in it? Redux

DB2 10, DB2 11 Migration & DB2 Database maintenance

A special query to check and clear out the DB2 Catalog

 

This is a redux of my original Newsletter from February 2013 with additional data at the very end – The reason for this relaunch? Simple – I have had numerous emails about this topic, especially with regard to migrating from DB2 10 to DB2 11, and it is obviously still very important to check and to clear out *before* you start your DB2 migration. So now enjoy the redux version…

 

DB2 Database Maintenance and the DB2 Catalog

If you have written your own DB2 database maintenance programs then you almost certainly run SQL queries against the DB2 Catalog. If you are also checking for Incremental Image Copies (IIC) or Full Image Copies (FIC) then you will probably be using a mix of Real-Time Statistics tables (RTS) and the SYSIBM.SYSCOPY to figure out which type of utility to generate. Further if you are in DB2 10 (any mode! CM8, CM9, or NF) then this newsletter is for you!

 

I had a problem in one of our test centers with a cursor that I noticed was taking a long time to finish and so I went into our Analyze+ tool and extracted the EDM Pool data (this is the new data in DB2 10 NF that is synonymous with the Dynamic Statement Cache counters and statistics) and sorted by Total Elapsed Time descending to get this:

 

Analyze+ for DB2 z/OS ----- EDM Pool (6/12) -------- Stmt 1 from 316 
Command ===>                                         Scroll ===> CSR  
                                                           DB2: QA1B 
Primary cmd: END, SE(tup), Z(oom), L(ocate) 
             total elapse time                 
Line    cmd: Z(oom), A(nalyze), D(ynamic Analyze), E(dit Statement), 
             P(ackage), S(tatement Text)
                                                               
                     Total       Average         Total       Average
     StmtID   Elapsed Time  Elapsed Time      CPU Time      CPU Time
----------- HHHH:MM:SS.ttt HHH:MM:SS.ttt HHH:MM:SS.ttt HHH:MM:SS.ttt
     115967    1:28.107705     29.369235   1:12.151391     24.050464
     114910       8.367834      0.000331      6.779229      0.000268
      79642       7.998559      0.054412      6.346829      0.043176
     114907       5.760045      0.000238      4.378691      0.000181
     115974       5.031890      2.515945      2.937258      1.468629
       5439       4.037261      0.000739      2.685938      0.000492

 

Over one hour total and over 29 minutes average for our small amount of test data set alarm bells ringing – so I drilled down to the SQL:

 

Analyze+ for DB2 z/OS -- View EDM-Pool Statement LINE 00000001 COL 001 080
Command ===>                                             Scroll ===> CSR
                                                         DB2: QA1B
Primary cmd: END
Collection:RTDX0510_PTFTOOL
Package   :M2DBSC09 
Contoken  :194C89620AE53D88  PrecompileTS: 2012-10-29-15.34.40.938230
StmtID    :          115967  StmtNo      :      1223  SectNo:       2
---------------------------------------------------------------------
DECLARE
  SYSCOPY-IC-MODI-9N
CURSOR WITH HOLD FOR 
SELECT                                                                       
  T1.N1 , T1.N2 , T1.N3 , T1.N4 , T1.N5 , T1.N6 , T1.N7 , T1.N8 , T1.N9 
  , T1.N10 , T1.N11 , T1.N12 
FROM (
  SELECT       
    ICTS.DBNAME AS N1
  , ICTS.TSNAME AS N2
  , ICTS.TIMESTAMP AS N3
  , ' ' AS N4
  , ICTS.DSNUM AS N5
 , ICTS.ICTYPE AS N6
 , DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICTS.TIMESTAMP ) AS N7
 , ICTS.OTYPE AS N8
 , ICTS.DSNAME AS N9
 , ICTS.ICUNIT AS N10
 , ICTS.INSTANCE AS N11
 , ICTS.STYPE AS N12                                                                    
  FROM SYSIBM.SYSCOPY ICTS                                                     
  WHERE ICTS.ICBACKUP IN ( '  ' , 'LB' , 'FC' )                            
  AND  ICTS.OTYPE = 'T' 
  UNION                    
   SELECT                           
    ICIX.DBNAME AS N1
  , CAST ( TABLES.TSNAME AS CHAR ( 8 ) CCSID EBCDIC ) AS N2
  , ICIX.TIMESTAMP AS N3
  , ICIX.TSNAME AS N4
  , ICIX.DSNUM AS N5
  , ICIX.ICTYPE AS N6
  , DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICIX.TIMESTAMP ) AS N7
  , ICIX.OTYPE AS N8
  , ICIX.DSNAME AS N9
  , ICIXS.ICUNIT AS N10
  , ICIX.INSTANCE AS N11
  , ICIX.STYPE AS N12
   FROM SYSIBM.SYSCOPY ICIX
      , SYSIBM.SYSINDEXES INDEXES
      , SYSIBM.SYSTABLES TABLES           
   WHERE ICIX.ICBACKUP IN ( '  ' , 'LB' , 'FC' )                          
   AND  ICIX.OTYPE = 'I' 
   AND VARCHAR ( ICIX.DBNAME , 24 ) = INDEXES.DBNAME       
   AND VARCHAR ( ICIX.TSNAME , 24 ) = INDEXES.INDEXSPACE 
   AND INDEXES.TBNAME = TABLES.NAME
   AND INDEXES.TBCREATOR = TABLES.CREATOR           
   AND  TABLES.TYPE IN ( 'H' , 'M' , 'P' , 'T' , 'X' ) )    
 AS T1                                                   
 ORDER BY CAST (T1.N1 AS CHAR ( 8 ) CCSID EBCDIC )
        , CAST (T1.N2 AS CHAR ( 8 ) CCSID EBCDIC )
        , N3 DESC 
 FOR FETCH ONLY       
 WITH UR                                                             



 HOSTVARIABLE NAME             NULLABLE  TYPE           LENGTH  SCALE
 ---------------------------  --------  --------------  -----  -----
 WORK-CURRENT-DATE             NO        CHAR              26       
 WORK-CURRENT-DATE             NO        CHAR              26       
******************************** Bottom of Data **********************

 

Ok, ok this SQL is not going to win a beauty contest any day soon but it used to run just fine…so now I explained it:

Analyze+ for DB2 z/OS -- Explain Data (1/6) --------- Entry 1 from 7  
Command ===>                                         Scroll ===> CSR   
EXPLAIN: DYNAMIC     MODE: CATALOG                         DB2: QA1B  
Primary cmd: END, T(Explain Text), V(iolations), R(unstats), 
             P(redicates), S(tatement Text), C(atalog Data),
             M(ode Catalog/History),Z(oom), PR(int Reports), 
             SAVExxx, SHOWxxx
             
Line    cmd: Z(oom), C(osts), I(ndexes of table), S(hort catalog),
             T(able), V(irtual indexes of table), 
             X(IndeX)                               
Collection : RTDX0510_PTFTOOL   Package : M2DBSC09   Stmt :     1223
Version    : - NONE -         
Milliseconds:  77519  Service Units: 220222  Cost Category: B
                                                                                
  QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO 
  PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ 
  ---- ------ -------- ---------- ----- ---- -- ---- ---- ---- ---- --- 
     1 SELECT R510PTFT T1         R        0 N     0    0 W    S      0 
     1 5 
     1 SELECT                              0 N     3    0 -           0 
     2 0  
     2 UNION                               0       3    1 -           0 
     1 0 
     3 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0 
     1 1         
     4 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0 
     1 2        
     4 NCOSUB SYSIBM   SYSINDEXES I        2 N     1    2 T           0 
     2 3      SYSIBM   DSNDXX02       
     4 NCOSUB SYSIBM   SYSTABLES  I        2 N     1    2 T           0 
     3 4      SYSIBM   DSNDTX01      
  ---- ------ -------- ---------- ----- ---- -- ---- ---- ---- ---- ---

 

This is *after* I had REORGed the SYSCOPY, SYSTSIXS and SYSTSTAB and then run the RUNSTATS on the SYSTSIXS and SYSTSTAB as you cannot do inline RUNSTATS on those two of course!

 

Two tablespace scans against the SYSCOPY is not brilliant of course but in this system we only have 4,000 table spaces and 2,500 indexes… so then I used the Catalog primary command to have another look at the catalog data:

TS   : DSNDB06 .SYSCOPY 
Stats: 2013-02-04-10.49.32.600316 
  Partitions:  0 , Tables: 1 , NACTIVEF: 18.272 pages 
  Type      :  Neither a LOB nor a MEMBER CLUSTER.
  RTS data TOTALROWS : 347.087 , Pages: 18.268
              
Table: SYSIBM.SYSCOPY 
Stats: 2013-02-04-10.49.32.600316
  No. of rows (CARDF): 347.082 , Pages: 18.268 
  Index: SYSIBM.DSNUCH01 
  Stats: 2013-02-04-10.49.32.600316     Type: Type-2 index
    Levels: 3 , Leaf pages: 3.945 
    FIRSTKEYCARDF: 101 , FULLKEYCARDF: 347.082 
    RTS data Levels: 3 , Leaf pages: 3.945 , TOTALENTRIES: 347.087 
    CLUSTERING: Y , CLUSTERED: Y , CLUSTERRATIO = 100,00% 
    DATAREPEATFACTORF: 18.268 
    Indexcolumn  ! Format        ! Dist. Values ! A/D ! NL ! Stats  
    -------------+---------------+--------------+-----+----+-------
    DBNAME       ! CHAR(8)       !          101 ! ASC ! N  ! OK  
    TSNAME       ! CHAR(8)       !          712 ! ASC ! N  ! OK  
    START_RBA    ! CHAR(6)       !       72.398 ! DSC ! N  ! OK  
    TIMESTAMP    ! TIMESTAMP(6)  !      347.082 ! DSC ! N  ! OK  
                                                                               
  Index: SYSIBM.DSNUCX01                                                 
  Stats: 2013-02-04-10.49.32.600316   Type: Type-2 index 
    Levels: 3 , Leaf pages: 509
    FIRSTKEYCARDF: 1.820 , FULLKEYCARDF: 1.820
    RTS data Levels: 3 , Leaf pages: 509 , TOTALENTRIES: 347.087 
    CLUSTERING: N , CLUSTERED: Y , CLUSTERRATIO = 100,00%
    DATAREPEATFACTORF: 18.275 
    Indexcolumn  ! Format        ! Dist. Values ! A/D ! NL ! Stats 
    -------------+---------- ----+--------------+-----+----+-------
    DSNAME       ! CHAR(44)      !        1.820 ! ASC ! N  ! OK    
                                                

Here I had a heart attack! 347,087 rows?!?!?!?!?!? How in the wide wide world of sports did that happen? Time to drill down into the contents of SYSCOPY with this little query:

SELECT ICTYPE , STYPE,  COUNT(*) 
FROM SYSIBM.SYSCOPY  
GROUP BY ICTYPE , STYPE            
;

Which returned these rather surprising results:

---------+---------+---------+
ICTYPE  STYPE                          
---------+---------+---------+
A       A                4             
B                       46             
C       L             1669             
C       O                4             
F                      100             
F       W               16             
I                        4             
L       M           344723             
M       R               18             
R                      151             
S                       62             
W                       18             
W       S                1             
Y                        2             
Z                      269             
DSNE610I NUMBER OF ROWS DISPLAYED IS 15

The L and M combination appears 344,723 times!!!

Grab your handy DB2 10 SQL reference and page on down to DB2 Catalog tables, SYSIBM.SYSCOPY and you will see:

ICTYPE CHAR(1) NOT NULL

Type of Operation:

A ALTER
B REBUILD INDEX
C CREATE
D CHECK DATA LOG(NO) (no log records for the range are available for RECOVER utility)
E RECOVER (to current point)
F COPY FULL YES
I COPY FULL NO
L SQL (type of operation)
M MODIFY RECOVERY utility
P RECOVER TOCOPY or RECOVER TORBA (partial recovery point)
Q QUIESCE
R LOAD REPLACE LOG(YES)
S LOAD REPLACE LOG(NO)
T TERM UTILITY command
V REPAIR VERSIONS utility
W REORG LOG(NO)
X REORG LOG(YES)
Y LOAD LOG(NO)
Z LOAD LOG(YES)

Now in my version the L entry has a ‘|’ by it to signify it is new. Scroll on down further to STYPE to read

STYPE CHAR(1) NOT NULL

Sub-type of operation:

When ICTYPE=L, the value is:

M Mass DELETE, TRUNCATE TABLE, DROP TABLE, or ALTER TABLE ROTATE PARTITION.
The LOWDSNUM column contains the table OBID of the affected table.

So, in other words, every time a program does a MASS DELETE it inserts a row into SYSCOPY. So then I ran another query to see when this all began and, hopefully, ended:

SELECT MAX(ICDATE), MIN(ICDATE) 
FROM SYSIBM.SYSCOPY                   
WHERE ICTYPE = 'L'                    
;                                     
---------+---------+---------+--------
                                      
---------+---------+---------+--------
121107  120828                        
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

 

So we started getting records on the 28th August 2012 and the last one was the 7th November 2012 so in just about ten weeks even we managed 344,723 Mass Deletes!

So now, with my Sherlock Holmes deer stalker hat on, the question was “Why did it stop in November?” Happily we have a history here of APARs and that’s when this PMR bubbled to the Surface:

PM52724: MASS DELETES ENDS UP WITH LOCK ESCALATION ON SYSCOPY IN V10. BECAUSE PM30991 INTALLED CODE INSERTING L 12/01/04 PTF PECHANGE

 

I will let you go and read the text but suffice it to say IBM realized what a disaster this “logging” of Mass Deletes was and HIPERed a quick fix to stop it! Plus you can see the APAR that “brought in the dead mouse” PM30991.

PM30991 UK66327 Closed 2011-03-30

PM52724 UK80113 Closed 2012-07-03

So if you installed the PM30991 and not the PM52724 you probably have some cleaning up to do…

By the way I also rewrote the Ugly Duckling SQL:

  SELECT  T1.N1      
         ,T1.N2         
         ,T1.N3              
         ,T1.N4         
         ,T1.N5        
         ,T1.N6       
         ,T1.N7 
         ,T1.N8      
         ,T1.N9     
         ,T1.N10         
         ,T1.N11        
         ,T1.N12       
    FROM (                                   
   SELECT ICTS.DBNAME    AS N1   
         ,ICTS.TSNAME    AS N2      
         ,ICTS.TIMESTAMP AS N3      
         ,' '            AS N4     
         ,ICTS.DSNUM     AS N5     
         ,ICTS.ICTYPE    AS N6     
         ,DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICTS.TIMESTAMP ) AS N7
         ,ICTS.OTYPE     AS N8             
         ,ICTS.DSNAME    AS N9               
         ,ICTS.ICUNIT    AS N10              
         ,ICTS.INSTANCE  AS N11              
         ,ICTS.STYPE     AS N12              
     FROM SYSIBM.SYSCOPY ICTS                
    WHERE ICTS.ICBACKUP IN ('  ','LB','FC')  
      AND ICTS.OTYPE    = 'T'
UNION ALL    
   SELECT ICIX.DBNAME     AS N1    
         ,CAST(TABLES.TSNAME                 
          AS CHAR(8) CCSID EBCDIC) AS N2  
         ,ICIX.TIMESTAMP  AS N3      
         ,ICIX.TSNAME     AS N4
         ,ICIX.DSNUM      AS N5              
         ,ICIX.ICTYPE     AS N6              
         ,DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICIX.TIMESTAMP ) AS N7
         ,ICIX.OTYPE      AS N8              
         ,ICIX.DSNAME    AS N9               
         ,ICIX.ICUNIT    AS N10              
         ,ICIX.INSTANCE  AS N11              
        ,ICIX.STYPE     AS N12                
    FROM SYSIBM.SYSCOPY ICIX                  
        ,SYSIBM.SYSINDEXES INDEXES            
        ,SYSIBM.SYSTABLES TABLES              
   WHERE ICIX.ICBACKUP IN ('  ','LB','FC')    
     AND ICIX.OTYPE        = 'I'              
     AND ICIX.DBNAME      = INDEXES.DBNAME    
     AND ICIX.TSNAME      = INDEXES.INDEXSPACE
     AND INDEXES.TBNAME    = TABLES.NAME      
     AND INDEXES.TBCREATOR = TABLES.CREATOR   
 ) AS T1                                      
ORDER BY CAST(T1.N1 AS CHAR(8) CCSID EBCDIC)  
        ,CAST(T1.N2 AS CHAR(8) CCSID EBCDIC)  
        ,        N3 DESC                      
  FOR FETCH ONLY                              
  WITH UR                                     
  ;

 

To now perform like this:

Milliseconds:  55911  Service Units:   158836  Cost Category: A 
                                                                                 
QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO 
PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ 
---- ------ -------- ---------- ----- ---- -- ---- ---- ---- ---- ---
   1 NCOSUB SYSIBM   SYSINDEXES I        0 N    0     2 T    S      0 
   1 3      SYSIBM   DSNDXX07   
   1 NCOSUB SYSIBM   SYSTABLES  I        2 N    1     2 T           0 
   2 4      SYSIBM   DSNDTX01
   1 NCOSUB SYSIBM   SYSCOPY    I        2 N    1     2 T    S      0 
   3 2      SYSIBM   DSNUCH01    
   2 UNIONA                              0 N    3     0 -           0 
   1 0   
   5 NCOSUB SYSIBM   SYSCOPY    R        0 N    0     2 T    S      0 
   1 1         
   ------ -------- ----------  ------ ---- -- ---- ---- ---- ----- ---

 

I am sure once I have deleted all the SYSCOPY rows (Note that we do not need to RECOVER on our test machine so I have the luxury of being able to delete the data – You, of course, cannot!) that it will return to being a nice little SQL!

 

After a large DELETE run which left only 2,365 rows followed by a REORG with inline RUNSTATS the original SQL now looks like:

 

Milliseconds:       672  Service Units:       1909  Cost Category: B  
                                                                               
QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO
PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ
---- ------ -------- ---------- ----- ----- ---- -- ---- ---- ---- --
   1 SELECT R510PTFT T1         R        0 N     0    0 W    S      0
   1 5   
   1 SELECT                              0 N     3    0 -           0
   2 0   
   2 UNION                               0       3    1 -           0
   1 0 
   3 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 1  
   4 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 2  
   4 NCOSUB SYSIBM   SYSINDEXES I        2 N     1    2 T           0
   2 3      SYSIBM   DSNDXX02                 
   4 NCOSUB SYSIBM   SYSTABLES  I        2 N     1    2 T           0
   3 4      SYSIBM   DSNDTX01      
  ---- ------ -------- ------------------ ----- ---- -- ---- ---- ----

And my version:

Milliseconds:      631  Service Units:     1792  Cost Category: A          
                                                                                
QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO
PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ
---- ------ -------- ---------- ----- ---- ---- -- ---- ---- ---- ---- 
   1 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 2       
   1 NCOSUB SYSIBM   SYSINDEXES I        2 N     1    2 T           0
   2 3      SYSIBM   DSNDXX02  
   1 NCOSUB SYSIBM   SYSTABLES  I        2 N     1    2 T           0
   3 4      SYSIBM   DSNDTX01 
   2 UNIONA                              0 N     3    0 -           0
   1 0             
   5 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 1   
   ---- ------ -------- ------------------ ----- ---- -- ---- ---- ----

Doesn’t look quite so impressive now…sniff…sniff

 

Here’s my SYSCOPY query for all cases:

SELECT ICTYPE, STYPE, MIN(ICDATE) AS OLDEST, MAX(ICDATE) AS NEWEST
     , COUNT(*) AS COUNT                                          
FROM SYSIBM.SYSCOPY                                               
GROUP BY ICTYPE , STYPE                                           
;                                                                 
---------+---------+---------+---------+---------+
ICTYPE  STYPE  OLDEST  NEWEST        COUNT           
---------+---------+---------+---------+---------+
A       A      121228  121228            4        
B              121228  130128           46       
C       L      100809  130204         1669    
C       O      120827  120827            4    
F              100809  130204          100     
F       W      100809  130204           16   
I              130131  130204            4
M       R      130102  130131           18         
R              120829  130130          151        
S              120829  130131           62      
W              100809  130204           18       
W       S      100809  100809            1     
Y              120828  120828            2    
Z              120828  130201          269    
DSNE610I NUMBER OF ROWS DISPLAYED IS 14

 

Clean Up

OK, so what can you do if you have 1000’s of these records? Well I would start with MODIFY RECOVER utilities to delete the bad guys. Your “normal” DB2 Database Maintenance jobs should take care of this for you but if you do not run these on a regular basis then start with this query:

SELECT A.DBNAME, A.TSNAME, A.DSNUM                              
      , MAX(DATE(A.TIMESTAMP)), MIN(DATE(A.TIMESTAMP)), COUNT(*)
FROM SYSIBM.SYSCOPY A                                           
WHERE A.ICTYPE = 'L'                                            
  AND A.STYPE  = 'M'                                            
  AND EXISTS (SELECT 1                                          
              FROM SYSIBM.SYSCOPY B                             
              WHERE A.DBNAME = B.DBNAME                         
                AND A.TSNAME = B.TSNAME                         
                AND (A.DSNUM  = B.DSNUM                         
                 OR (A.DSNUM > 0                                
                 AND B.DSNUM = 0 ))                             
                AND A.START_RBA < B.START_RBA                   
                AND B.ICTYPE = 'F')                             
GROUP BY A.DBNAME, A.TSNAME, A.DSNUM                            
FOR FETCH ONLY                                                  
WITH UR                                                         
;

The output shows you the DBNAME, TSNAME, DSNUM and counts for all of the “bad guys” that have at least one Full Image Copy *after* the bad guy was inserted:

---------+---------+---------+---------+---------+---------+---------+
DBNAME    TSNAME          DSNUM                                       
---------+---------+---------+---------+---------+---------+---------+
R510D0PT  R510S04             0  2016-01-26  2015-11-11           75  
R510D0PT  R510S10             0  2016-01-26  2015-11-11           75  
R510D0PT  R510S12             0  2016-01-26  2015-11-11           75  
R510D0PT  R510S14             0  2016-01-26  2015-11-11           75

 

This gives you the needed input to write a simple MODIFY RECOVER utility input like this:

 

MODIFY RECOVERY TABLESPACE R510D0PT.R510S04 DELETE DATE 20151203

Why did I use 2015-12-03 when in the query output I have 2015-11-11? That’s because I do not want to delete *all* of the Image Copy data in SYSCOPY – just the data from 2015-11-11 to 2015-12-02 (remember that MODIFY works up to the date *before* you enter). The output from the MODIFY looked like this:

 

DSNU000I    028 09:10:43.97 DSNUGUTC - OUTPUT START FOR UTILITY, 
            UTILID = PTFMO000MOU001
DSNU1044I   028 09:10:44.16 DSNUGTIS - PROCESSING SYSIN AS EBCDIC
DSNU050I    028 09:10:44.17 DSNUGUTC -  MODIFY RECOVERY TABLESPACE
            R510D0PT.R510S04 DELETE DATE 20151203
DSNU517I    -QA1B 028 09:10:44.28 DSNUMDEL 
            - SYSCOPY RECORD DELETED BY MODIFY UTILITY.
DSN=SETEST.R510D0PT.R510S04.D15142.T0635, VOL=SER=(CATLG), FILESEQNO=0
DSNU575I   -QA1B 028 09:10:44.49 DSNUMODA - MODIFY COMPLETED SUCCESSFULLY
DSNU010I    028 09:10:44.52 DSNUGBAC - UTILITY EXECUTION COMPLETE, 
            HIGHEST RETURN CODE=0

 

Now you must also IDCAMS Delete any, and all, of the dataset names output here.

Now re-running the original query gives:

---------+---------+---------+---------+---------+---------+---------
DBNAME    TSNAME          DSNUM                                      
---------+---------+---------+---------+---------+---------+---------
R510D0PT  R510S04             0  2016-01-26  2015-12-03           53 
R510D0PT  R510S10             0  2016-01-26  2015-11-11           75 
R510D0PT  R510S12             0  2016-01-26  2015-11-11           75 
R510D0PT  R510S14             0  2016-01-26  2015-11-11           75

 

24 type L & M records gone from between 2015-11-11 and 2015-12-02 and of course the object is not in COPY Pending status!

However, there are cases where you cannot do this straightaway (Perhaps never been image copied?). In these cases, the simplest method is to do an Image copy and then a Modify, but if the object is to big then you must make the DB2 Catalog updateable (ask your friendly Sysprog about this,) and simply use an SQL DELETE to remove all of the entries for an object *before* the last Full Image Copy.

Once you have removed all the fluff, remember to REORG SYSCOPY and RUNSTAT the indexes. Then it should be a *lot* thinner and make migrating a tad easier!

 

As always if you have any comments or questions please email me!

TTFN

Roy Boxwell

 

Posted on

2016-03 DB2 z/OS Real Time Statistics (RTS) Revisited – Information missing (part 2)

DB2 z/OS Real Time Statistics (RTS) –  NULL initialization made easy

A second RTS query to set this time *all* of your RTS KPIs and counters to enable good DB2 Database Maintenance before a REORG, RUNSTAT or a DB2 Migration for DB2 10 and DB2 11

Following on from last month’s Newsletter (first RTS query) where we inserted any missing data rows into the RTS tables-the next thing that I see on a, sadly, regular basis is NULL values in the RTS columns.

Now when RTS was introduced, way back in DB2 V7, the argument from DB2 Development was “If we do not know the exact count we will set the column to NULL.” I have always strongly disagreed with this approach as whenever you add +1 to NULL you get NULL, whenever you add +1000000 to NULL you get NULL. I said then, and I repeat it now, zero is orders of magnitude better than NULL because when you add +1 to 0 you get +1 and when you add +1000000 to 0 you get +1000000. This enables your DB2 Database Maintenance system to actually *work* and then, after a REORG, the counters are actually 100% correct. I am a firm believer in the “I don’t care if it is 99% inaccurate as long as it *counts*!” methodology, and please remember that a SHRLEVEL CHANGE RUNSTATS does *not* set the TOTALROWS or TOTALENTRIES!

 

RTS Database Maintenance

The following SQLs in this month’s newsletter will intelligently set *all* of your RTS KPIs and counters to enable good DB2 Database Maintenance. It is written for DB2 10 with a couple of commented out lines for DB2 11. (In the RTS tables, there are only two actual new columns and these are the “info” only columns UPDATESIZE and LASTDATACHANGE in SYSTABLESPACESTATS. The commented out lines just set the UPDATESIZE to zero.)

 

RTS NULL initialization made easy

Now here are the UPDATE SQLs broken down to be one UPDATE SQL per KPI column and one “mass update” for all of the counter columns:

-- TOTALENTRIES
UPDATE SYSIBM.SYSINDEXSPACESTATS E                  
SET TOTALENTRIES =                                        
   (SELECT CASE A.CARDF                                   
           WHEN -1 THEN 0                                 
           ELSE A.CARDF                                   
           END                                            
    FROM SYSIBM.SYSINDEXPART  A                           
        ,SYSIBM.SYSINDEXES    B                           
    WHERE B.DBNAME     = E.DBNAME                         
      AND B.INDEXSPACE = E.INDEXSPACE                     
      AND B.CREATOR    = A.IXCREATOR                      
      AND B.NAME       = A.IXNAME                         
      AND A.PARTITION  = E.PARTITION)                     
WHERE TOTALENTRIES IS NULL                                
;                                                         
-- NLEVELS
UPDATE SYSIBM.SYSINDEXSPACESTATS E                        
SET NLEVELS =                                             
   (SELECT CASE B.NLEVELS                                 
           WHEN -1 THEN 1                                 
           ELSE B.NLEVELS                                 
           END                                            
    FROM SYSIBM.SYSINDEXES    B                           
    WHERE B.DBNAME     = E.DBNAME                         
      AND B.INDEXSPACE = E.INDEXSPACE )                   
WHERE NLEVELS IS NULL                                     
; 
-- NLEAF
UPDATE SYSIBM.SYSINDEXSPACESTATS E                        
SET NLEAF =                                               
   (SELECT CASE B.NLEAF                                   
           WHEN -1 THEN 1                                 
           ELSE B.NLEAF                                   
           END                                            
    FROM SYSIBM.SYSINDEXES    B                           
    WHERE B.DBNAME     = E.DBNAME                         
      AND B.INDEXSPACE = E.INDEXSPACE )                   
WHERE NLEAF IS NULL                                       
;                                                         
-- SPACE: USE SPACE IF SPACEF -1 WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSINDEXSPACESTATS E                        
SET SPACE =                                               
   (SELECT CASE A.SPACEF                                  
           WHEN -1 THEN CASE A.SPACE                      
                        WHEN 0  THEN 0                    
                        ELSE A.SPACE                      
                        END                               
           ELSE MAX(MIN(2147483647 , A.SPACEF ) , A.SPACE)
           END                                            
    FROM SYSIBM.SYSINDEXPART  A                           
        ,SYSIBM.SYSINDEXES    B                                     
  WHERE B.DBNAME     = E.DBNAME                                   
    AND B.INDEXSPACE = E.INDEXSPACE                               
    AND B.CREATOR    = A.IXCREATOR                                
    AND B.NAME       = A.IXNAME                                   
    AND A.PARTITION  = E.PARTITION)                               
WHERE SPACE IS NULL                                                 
;
-- EXTENTS: AT LEAST ONE EXTENT
UPDATE SYSIBM.SYSINDEXSPACESTATS E                                  
SET EXTENTS =                                                       
   (SELECT CASE A.EXTENTS                                           
           WHEN -1 THEN 1                                           
           ELSE A.EXTENTS                                           
           END                                                      
    FROM SYSIBM.SYSINDEXPART  A                                     
        ,SYSIBM.SYSINDEXES    B                                     
    WHERE B.DBNAME     = E.DBNAME                                   
      AND B.INDEXSPACE = E.INDEXSPACE                               
      AND B.CREATOR    = A.IXCREATOR                                
      AND B.NAME       = A.IXNAME                                   
      AND A.PARTITION  = E.PARTITION)                               
WHERE EXTENTS IS NULL                                               
;                                                                   
-- NPAGES: KPI SO SET TO ZERO IF NULL
UPDATE SYSIBM.SYSINDEXSPACESTATS E                                  
SET NPAGES             = COALESCE(NPAGES             , 0)           
WHERE NPAGES             IS NULL
;                                                                   
-- COUNTERS: SET TO ZERO IF NULL
UPDATE SYSIBM.SYSINDEXSPACESTATS E                                  
SET REORGINSERTS       = COALESCE(REORGINSERTS       , 0)           
  , REORGDELETES       = COALESCE(REORGDELETES       , 0)
  , REORGAPPENDINSERT  = COALESCE(REORGAPPENDINSERT  , 0)
  , REORGPSEUDODELETES = COALESCE(REORGPSEUDODELETES , 0)
  , REORGMASSDELETE    = COALESCE(REORGMASSDELETE    , 0)
  , REORGLEAFNEAR      = COALESCE(REORGLEAFNEAR      , 0)
  , REORGLEAFFAR       = COALESCE(REORGLEAFFAR       , 0)
  , REORGNUMLEVELS     = COALESCE(REORGNUMLEVELS     , 0)
  , REORGINDEXACCESS   = COALESCE(REORGINDEXACCESS   , 0)
  , STATSINSERTS       = COALESCE(STATSINSERTS       , 0)
  , STATSDELETES       = COALESCE(STATSDELETES       , 0)
  , STATSMASSDELETE    = COALESCE(STATSMASSDELETE    , 0)
  , COPYUPDATEDPAGES   = COALESCE(COPYUPDATEDPAGES   , 0)
  , COPYCHANGES        = COALESCE(COPYCHANGES        , 0)
WHERE REORGINSERTS       IS NULL                         
   OR REORGDELETES       IS NULL                         
   OR REORGAPPENDINSERT  IS NULL                         
   OR REORGPSEUDODELETES IS NULL                         
   OR REORGMASSDELETE    IS NULL                         
   OR REORGLEAFNEAR      IS NULL                         
   OR REORGLEAFFAR       IS NULL                         
   OR REORGNUMLEVELS     IS NULL                         
   OR REORGINDEXACCESS   IS NULL                         
   OR STATSINSERTS       IS NULL                         
   OR STATSDELETES       IS NULL                         
   OR STATSMASSDELETE    IS NULL                         
   OR COPYUPDATEDPAGES   IS NULL                         
   OR COPYCHANGES        IS NULL                         
;
-- NOTE THAT FOR TABLESPACES DSNDB01.SYSUTILX IS NOT
--      SET AND ANY WORK DATABASE IS ALSO IGNORED
--
-- TOTALROWS
UPDATE SYSIBM.SYSTABLESPACESTATS C                       
SET TOTALROWS =                                          
(SELECT CASE A.CARDF                                     
        WHEN -1 THEN 0                                   
        ELSE A.CARDF                                     
        END                                              
 FROM SYSIBM.SYSTABLEPART A                              
 WHERE A.DBNAME    = C.DBNAME                            
   AND A.TSNAME    = C.NAME                              
   AND A.PARTITION = C.PARTITION )                       
WHERE TOTALROWS IS NULL                                  
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX') 
  AND NOT EXISTS (SELECT 1                               
                  FROM SYSIBM.SYSDATABASE D              
                  WHERE C.DBNAME = D.NAME                
                    AND D.TYPE   = 'W')                  
;
-- SPACE: SPACE IF SPACEF IS -1 WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSTABLESPACESTATS C                       
SET SPACE =                                              
(SELECT CASE A.SPACEF                                                 
        WHEN -1 THEN A.SPACE                                          
        ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)             
        END                                                           
 FROM SYSIBM.SYSTABLEPART A                                           
 WHERE A.DBNAME    = C.DBNAME                                         
   AND A.TSNAME    = C.NAME                                           
   AND A.PARTITION = C.PARTITION )                                    
WHERE SPACE IS NULL                                                   
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')              
  AND NOT EXISTS (SELECT 1                                            
                  FROM SYSIBM.SYSDATABASE D                           
                  WHERE C.DBNAME = D.NAME                             
                    AND D.TYPE   = 'W')                               
;                                                                     
-- NACTIVE: SPACE IF SPACEF IS -1 / PGSIZE WATCH OUT FOR OVERFLOW
UPDATE SYSIBM.SYSTABLESPACESTATS C                                    
SET NACTIVE =                                                         
(SELECT CASE A.SPACEF                                                 
       WHEN -1 THEN CASE A.SPACE                                      
               WHEN 0 THEN 0                                          
               ELSE A.SPACE / B.PGSIZE                                
               END                                                    
       ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
       END                                                            
FROM SYSIBM.SYSTABLEPART  A                                           
    ,SYSIBM.SYSTABLESPACE B                                           
WHERE A.DBNAME    = C.DBNAME                                          
  AND A.TSNAME    = C.NAME                                            
  AND A.PARTITION = C.PARTITION                                       
  AND A.DBNAME    = B.DBNAME                                          
  AND A.TSNAME    = B.NAME )                                          
WHERE NACTIVE IS NULL                                                 
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')              
  AND NOT EXISTS (SELECT 1                                            
                  FROM SYSIBM.SYSDATABASE D                           
                  WHERE C.DBNAME = D.NAME                             
                    AND D.TYPE   = 'W')                               
;
-- EXTENTS: AT LEAST ONE EXTENT
UPDATE SYSIBM.SYSTABLESPACESTATS C                                    
SET EXTENTS =                                                         
(SELECT CASE A.EXTENTS                                                
        WHEN -1 THEN 1                                                
        ELSE A.EXTENTS                                                
        END                                                           
FROM SYSIBM.SYSTABLEPART  A                                           
WHERE A.DBNAME    = C.DBNAME                                          
  AND A.TSNAME    = C.NAME                                            
  AND A.PARTITION = C.PARTITION )                       
WHERE EXTENTS IS NULL                                   
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
  AND NOT EXISTS (SELECT 1                              
                  FROM SYSIBM.SYSDATABASE D             
                  WHERE C.DBNAME = D.NAME               
                    AND D.TYPE   = 'W')                 
;                                                       
-- NPAGES: KPI SO SET TO ZERO IF NULL
UPDATE SYSIBM.SYSTABLESPACESTATS C                      
SET NPAGES           = COALESCE(NPAGES           , 0 )  
WHERE NPAGES IS NULL                                    
  AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')
  AND NOT EXISTS (SELECT 1                              
                  FROM SYSIBM.SYSDATABASE D             
                  WHERE C.DBNAME = D.NAME               
                    AND D.TYPE   = 'W')                 
;                                                       
-- COUNTERS: SET TO ZERO IF NULL
UPDATE SYSIBM.SYSTABLESPACESTATS C                      
 SET REORGINSERTS     = COALESCE(REORGINSERTS     , 0 )  
   , REORGDELETES     = COALESCE(REORGDELETES     , 0 )  
   , REORGUPDATES     = COALESCE(REORGUPDATES     , 0 )  
   , REORGUNCLUSTINS  = COALESCE(REORGUNCLUSTINS  , 0 )  
   , REORGDISORGLOB   = COALESCE(REORGDISORGLOB   , 0 )  
   , REORGMASSDELETE  = COALESCE(REORGMASSDELETE  , 0 )  
   , REORGNEARINDREF  = COALESCE(REORGNEARINDREF  , 0 )  
   , REORGFARINDREF   = COALESCE(REORGFARINDREF   , 0 )  
   , REORGCLUSTERSENS = COALESCE(REORGCLUSTERSENS , 0 )  
   , REORGSCANACCESS  = COALESCE(REORGSCANACCESS  , 0 )  
   , REORGHASHACCESS  = COALESCE(REORGHASHACCESS  , 0 )  
   , STATSINSERTS     = COALESCE(STATSINSERTS     , 0 )  
   , STATSDELETES     = COALESCE(STATSDELETES     , 0 )  
   , STATSUPDATES     = COALESCE(STATSUPDATES     , 0 )  
   , STATSMASSDELETE  = COALESCE(STATSMASSDELETE  , 0 )  
-- , UPDATESIZE       = COALESCE(UPDATESIZE       , 0 )  
   , COPYUPDATEDPAGES = COALESCE(COPYUPDATEDPAGES , 0 )  
   , COPYCHANGES      = COALESCE(COPYCHANGES      , 0 )  
 WHERE (REORGINSERTS     IS NULL                         
    OR  REORGDELETES     IS NULL                         
    OR  REORGUPDATES     IS NULL                         
    OR  REORGUNCLUSTINS  IS NULL                         
    OR  REORGDISORGLOB   IS NULL                         
    OR  REORGMASSDELETE  IS NULL                         
    OR  REORGNEARINDREF  IS NULL                         
    OR  REORGFARINDREF   IS NULL                         
    OR  REORGCLUSTERSENS IS NULL                         
    OR  REORGSCANACCESS  IS NULL                         
    OR  REORGHASHACCESS  IS NULL                         
    OR  STATSINSERTS     IS NULL                         
    OR  STATSDELETES     IS NULL                           
    OR  STATSUPDATES     IS NULL                           
    OR  STATSMASSDELETE  IS NULL                           
--  OR  UPDATESIZE       IS NULL                           
    OR  COPYUPDATEDPAGES IS NULL                           
    OR  COPYCHANGES      IS NULL)                          
   AND NOT (C.DBNAME = 'DSNDB01' AND C.NAME = 'SYSUTILX')  
   AND NOT EXISTS (SELECT 1                                
                   FROM SYSIBM.SYSDATABASE D               
                   WHERE C.DBNAME = D.NAME                 
                     AND D.TYPE   = 'W')                   
 ;

It is a very good idea to run all of these queries on a regular basis…just in case!

I would like to know how many rows this UPDATEd at your shops. Here in the Düsseldorf labs it updated hundreds in a DB2 11 NFM system.

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

TTFN,

Roy Boxwell

Posted on

2016-02 Real Time Statistics (RTS) Revisited – Information missing (part 1)

Easy Real Time Statistics (RTS) data initialization from DB2 9 to DB2 11:

Special query to run before a REORG, RUNSTAT or a DB2 Migration. How many RTS rows did you find?

 

Hands up who knows nothing about the RTS? Good, all hands are down! I had an interesting experience the other day with one of my customers as they are in the process of doing the big bang “REORG the world” to get from a six byte RBA/LRSN to a 10 Byte RBA/LRSN due to problems with data cloning in a mixed DB2 release Environment.

 

RTS Database Maintenance

They use the RTS to drive the creation of REORG, RUNSTAT, and COPY utilities as this is the modern and correct way to go, right? Well, they ended up with a bunch of objects that refused to REORG. I looked high and low for *any* reason as to why they would be excluded from processing and found none. Well, actually, I lie – there was one, and that was the fact that the candidate list was based upon a SELECT from the RTS tables and then joining to the DB2 Catalog to refine the data and then finally generating the required REORG jobs.

 

RTS data missing

It was noticed that these tablespaces were either empty or very small and it was seen that they did not even *exist* in the RTS! Now cast your mind way way way back to DB2 V7 when the RTS were introduced as an “optional” feature. I wrote a little SQL INSERT to populate the RTS for any missing elements as the IBM way of populating the RTS was to “REORG the world” (remember those halcyon days?) Anyway these days, about 11 years later, it is *always* assumed that:

– The RTS data exists
– The RTS data is correct (mainly!)
– RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

------------------------------------------------------------------------
-- THESE TWO QUERIES WILL FILL THE RTS TABLES SYSIBM.SYSTABLESPACESTATS
-- AND SYSIBM.SYSINDEXSPACESTATS WITH DEFAULT AND, WHEN POSSIBLE,     --
-- WITH CATALOG DATA FOR MISSING ENTRIES                              --
-- (OBJECTS FOUND IN THE CATALOG BUT NOT IN RTS TABLES)               --
------------------------------------------------------------------------
-- LOCK TABLE SYSIBM.SYSTABLESPACESTATS IN EXCLUSIVE MODE ;         

INSERT INTO SYSIBM.SYSTABLESPACESTATS
 (UPDATESTATSTIME,NACTIVE,EXTENTS)
 ,LOADRLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGUPDATES,REORGUNCLUSTINS
 ,REORGDISORGLOB,REORGMASSDELETE,REORGNEARINDREF,REORGFARINDREF
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSUPDATES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,PSID,PARTITION,INSTANCE,SPACE,TOTALROWS 
 ,DBNAME,NAME)
 SELECT CURRENT TIMESTAMP 
 ,CASE A.SPACEF 
  WHEN -1 THEN CASE A.SPACE 
               WHEN 0 THEN NULL 
               ELSE A.SPACE / B.PGSIZE 
               END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.EXTENTS
  WHEN -1 THEN NULL
  ELSE A.EXTENTS
  END
  ,TIMESTAMP('0001-01-01-00.00.00.000000')
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0 , 0   
  , 0 , 0 , 0 , 0  
  ,CASE 
    WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') 
    THEN A.STATSTIME 
    WHEN A.STATSTIME < A.CREATEDTS THEN 
                       TIMESTAMP('0001-01-01-00.00.00.000000')
    ELSE A.STATSTIME
    END
  , 0 , 0 , 0 , 0 
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0   
  , 'N'   
  ,B.DBID,B.PSID,A.PARTITION,B.INSTANCE  
  ,CASE A.SPACEF 
    WHEN -1 THEN CASE A.SPACE 
                 WHEN 0  THEN NULL  
                 ELSE A.SPACE 
                 END 
    ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
    END 
   ,CASE A.CARDF 
    WHEN -1 THEN NULL  
    ELSE A.CARDF 
    END 
   ,A.DBNAME,A.TSNAME  
    FROM SYSIBM.SYSTABLEPART  A 
        ,SYSIBM.SYSTABLESPACE B 
    WHERE NOT EXISTS (SELECT C.*  
                     FROM SYSIBM.SYSTABLESPACESTATS C 
                     WHERE A.DBNAME = C.DBNAME 
                       AND A.TSNAME = C.NAME  
                       AND A.PARTITION = C.PARTITION)    
     AND NOT A.SPACE  = -1   
     AND A.DBNAME     = B.DBNAME   
     AND A.TSNAME     = B.NAME    
  ; 
COMMIT ;
-- LOCK TABLE SYSIBM.SYSINDEXSPACESTATS IN EXCLUSIVE MODE ;
INSERT INTO SYSIBM.SYSINDEXSPACESTATS
 (UPDATESTATSTIME
 ,NLEVELS,NLEAF,NACTIVE,SPACE,EXTENTS
 ,LOADRLASTTIME
 ,REBUILDLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGAPPENDINSERT
 ,REORGPSEUDODELETES,REORGMASSDELETE,REORGLEAFNEAR,REORGLEAFFAR
 ,REORGNUMLEVELS
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,ISOBID,PSID,PARTITION,INSTANCE
 ,TOTALENTRIES,DBNAME,NAME,CREATOR,INDEXSPACE)
  SELECT CURRENT TIMESTAMP
 ,CASE B.NLEVELS
   WHEN -1 THEN NULL
   ELSE B.NLEVELS
   END
  ,CASE B.NLEAF
   WHEN -1 THEN NULL
   ELSE B.NLEAF
   END
  ,CASE A.SPACEF
   WHEN -1 THEN CASE A.SPACE
                WHEN 0  THEN NULL
                ELSE A.SPACE / B.PGSIZE
                END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.SPACEF
  WHEN -1 THEN CASE A.SPACE
               WHEN 0  THEN NULL
               ELSE A.SPACE
               END
  ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
  END
 ,CASE A.EXTENTS
   WHEN -1 THEN NULL
   ELSE A.EXTENTS
   END
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0
 , 0 , 0 , 0 , 0 , 0
 ,CASE
  WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000')
  THEN A.STATSTIME
  WHEN A.STATSTIME < A.CREATEDTS THEN
                     TIMESTAMP('0001-01-01-00.00.00.000000')
  ELSE A.STATSTIME
  END
 , 0 , 0 , 0
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0
 , 'N'
 ,B.DBID,B.ISOBID, C.PSID
 ,A.PARTITION,C.INSTANCE
 ,CASE A.CARDF
  WHEN -1 THEN NULL
  ELSE A.CARDF
  END
 ,B.DBNAME,B.NAME,B.CREATOR,B.INDEXSPACE
  FROM SYSIBM.SYSINDEXPART  A
      ,SYSIBM.SYSINDEXES    B
      ,SYSIBM.SYSTABLESPACE C
      ,SYSIBM.SYSTABLES     D
 WHERE NOT EXISTS (SELECT E.*
                   FROM SYSIBM.SYSINDEXSPACESTATS E
                   WHERE B.DBNAME = E.DBNAME
                     AND B.INDEXSPACE = E.INDEXSPACE
                     AND A.PARTITION  = E.PARTITION)
   AND B.CREATOR    = A.IXCREATOR
   AND B.NAME       = A.IXNAME
   AND NOT A.SPACE  = -1
   AND B.TBCREATOR  = D.CREATOR
   AND B.TBNAME     = D.NAME
   AND D.DBNAME     = C.DBNAME
   AND D.TSNAME     = C.NAME
;
COMMIT ;

 

It may even be a good idea to run these two queries on a regular basis… just in case!

I would like to know how many rows these queries INSERTed at your shops – Here in Düsseldorf, in the labs, it found two TS’s and three IX’s in a DB2 11 NFM system.

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

TTFN,

Roy Boxwell

Posted on

2016-01 Simply Synonyms in DB2 z/OS (again)

As SYNONYMS are dying out, it is a good idea to start DROPping them as soon as you can…

In this short newsletter I wish to remind you all that SYNONYMS are dead! They may still be nailed to their perch (old Monty Python reference here!), but they are dead! Even the DB2 documentation has removed them and a big sign says DEPRECATED over the SQL syntax. So much for the preamble… The question is: What can you do? I will attempt to show you how to discover what size of problem you have, and provide some help in sorting out the mess of synonyms.

UPDATE!

On Friday the 08.04.2016 Pat posted this update on the DB2-L LISTSERV:

Hello,

I wanted to share an update on synonyms.

Synonyms are similar to aliases, but are supported only for compatibility with previous releases. Synonyms behave differently with DB2 for z/OS than with the other DB2 family products. It is recommended that you not create or use synonyms when writing new SQL statements or creating portable applications; use aliases instead. The publications currently state that synonyms are deprecated. The publications are being updated to reflect that synonyms are not being removed from DB2 for z/OS at this time and are no longer considered deprecated.

Best regards,

Pat Bossman
DB2 for z/OS Query Optimizer Team at IBM’s Silicon Valley Lab (SVL)

Synonyms were a good idea many years ago but they brought with them a few problems: one is that you lose the connection between a synonym and the alias it is created on, another is that GRANTs on synonyms actually are recorded in the DB2 Catalog against the base tables, and finally the SYNONYM did not agree with the rest of the world’s SQL Standards.

On Thursday the 28.04.2016 Pat posted this correction on the DB2-L LISTSERV:

Hello,

I need to issue a correction on information I previously disseminated.  I apologize for inconvenience this caused.

Update on synonyms: I understand there are a lot of questions about the status of synonyms now. In hindsight, the original communication that synonyms were being removed from the deprecation list was based on an internal misunderstanding and miscommunication. This resulted in premature dissemination of inaccurate information. This disclosure should have been more thoroughly vetted, and I apologize for the confusion caused.

What is the status of synonyms? Synonyms remain deprecated. However, synonyms continue to be supported for compatibility with prior releases in DB2 12. Removing synonyms from the product is an incompatible change and that incompatible change has not yet been scheduled or planned for implementation.

Documentation changes coming to the effect of: Synonyms are similar to aliases, but are supported only for compatibility with previous releases. Synonyms behave differently with DB2 for z/OS than with the other DB2 family products. Aliases behave the same for the DB2 family of products. Recommendation: When writing new SQL statements or creating portable applications, use aliases instead. The publications currently state that synonyms are deprecated, however, IBM has no current plans to remove support for synonyms from DB2 for z/OS.

Addendum: There has been some confusion related to the meaning of deprecation as it relates to a feature of software, as opposed to removing support for a feature of software. Deprecation is the discouragement of use of some feature, design or practice, typically because it has been superseded or is no longer considered safe, without (at least for the time being) removing it from the system of which it is a part or prohibiting its use. This is distinct from blocking or removing support, where the feature is no longer available. For clarity, a definition of deprecation will be to our software Gallery.

Patrick Bossman
DB2 for z/OS Query Optimizer Team at IBM’s Silicon Valley Lab (SVL)

 

Finding the bad guys

Here’s a triplet of SQLs to find all the synonym-dependent items:

 

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                
WHERE BTYPE = 'S'                             
ORDER BY 1 , 2 , 3 , 5 , 7 , 8 , 9 , 11       
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                                
WHERE BTYPE = 'S'                                     
ORDER BY 1 , 2 , 7                                    
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
WHERE BTYPE = 'S'
ORDER BY 1 , 2
WITH UR
;

If you get no data from using these queries, then that is very good indeed! If you do get data, then you must make a note of all the objects listed, as they will be affected by what we are going to do next. “Affected”, in this case, could be as simple as a REBIND, but could also be as complex as recreating UDFs.

 

Generate the drop

-- CREATE DRIVER TABLE FOR CARTESIAN JOIN PROCESSING
DECLARE GLOBAL TEMPORARY TABLE DRIVER (NUMBER SMALLINT) ;
INSERT INTO SESSION.DRIVER VALUES 1 ;
INSERT INTO SESSION.DRIVER VALUES 2 ;
INSERT INTO SESSION.DRIVER VALUES 3 ;
INSERT INTO SESSION.DRIVER VALUES 4 ;
INSERT INTO SESSION.DRIVER VALUES 5 ;
-- NOW GENERATE DROP AND CREATE ALIAS COMMANDS
WITH T1 ( CREATOR
        , NAME
        , ALIAS_NAME
        , ALIAS_TABLE
          )
     AS ( SELECT STRIP(CREATOR)
               , STRIP(NAME)
               , STRIP(CREATOR)   CONCAT '.' CONCAT STRIP(NAME)
               , STRIP(TBCREATOR) CONCAT '.' CONCAT STRIP(TBNAME)
          FROM SYSIBM.SYSSYNONYMS
          ORDER BY 1
        )
SELECT CAST(CASE NUMBER
            WHEN 1 THEN 'SET CURRENT SQLID = '''
                        CONCAT CREATOR CONCAT ''' $'
            WHEN 2 THEN 'DROP SYNONYM ' CONCAT NAME CONCAT ' $'
            WHEN 3 THEN 'CREATE ALIAS ' CONCAT ALIAS_NAME
            WHEN 4 THEN 'FOR ' CONCAT ALIAS_TABLE
            WHEN 5 THEN '$'
            END AS CHAR(72))
FROM T1, SESSION.DRIVER
;

 

The output of this contains the “$” as command terminator, so you must either use a “C ALL $ ;” style ISPF command, or just use a “–#SET TERMINATOR $” line in the SPUFI.

One thing to remember, before executing the generated output, is the problem of the GRANTs. Remember that all GRANTs are recorded at the table level not at the synonym level, so you have two choices here:

  1. Ignore the GRANTs and clean up later
  2. Analyze the SYSIBM.SYSTABAUTH table to see if any GRANTs are there

Whichever way you choose, there will be work involved! However, as SYNONYMS are dying out, it is a good idea to start DROPping them as soon as you can.

I hope you liked this month’s Topic.

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

TTFN,

Roy Boxwell

Posted on

BIF HealthCheck licensed Freeware for DB 10

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

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

BIF 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 incompatibilities in DB2 11

 

More about BIF

BIF-Usage

Presentation

BIF CompatibilityDB2 10 compatibility mode
Changes to the STRING formating of decimal data within the CHAR and VARCHAR built-in function and to the CAST specification with CHAR and VARCHAR result types as well as  UNSUPPORTED TIMESTAMP STRINGs.
White PaperFinding BIFsAnd How to Lead a Problem-Free Life With Them in the Future
Navigating the Challenges of moving to a new DB2 Release
Newsletter2015-01 – BIFCIDS – Where’s the BIF?How will you deal with loop-hole usage in production code?
VideoBIF Usage(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
“Give and Take”
Program” page		 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 
4  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
 User StatementsBIF Usage:

“Give and Take
Program 3”

 Customer CommentsRead the Customer Comments across the Industry

  • Health Care
  • Insurance
  • Banking
  • Car Manufacturing

 

 

Posted on

BIF HealthCheck Licensed Freeware for DB2 11

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

BIF HealthCheck 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

 

More about BIF

BIF-Usage

Presentation

BIF CompatibilityDB2 10 compatibility mode
Changes to the STRING formating of decimal data within the CHAR and VARCHAR built-in function and to the CAST specification with CHAR and VARCHAR result types as well as  UNSUPPORTED TIMESTAMP STRINGs.
White PaperFinding BIFsAnd How to Lead a Problem-Free Life With Them in the Future
Navigating the Challenges of moving to a new DB2 Release
Newsletter2015-01 – BIFCIDS – Where’s the BIF?How will you deal with loop-hole usage in production code?
VideoBIF Usage(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
“Give and Take”
Program” page		 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 
4  BIF HealthCheck (Freeware) – This last one is still available
We TAKE the anonymized results for research
 and will communicate with the local User Groups for discussions
 User StatementsBIF Usage:

“Give and Take
Program 3”

 Customer CommentsRead the Customer Comments across the Industry

  • Health Care
  • Insurance
  • Banking
  • Car Manufacturing