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

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

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

How do you fix it?

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

Same procedure as every year

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

How long?

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

Day of reckoning

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

Alarm!

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

Oh no it isn’t!

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

Clever old JAVA

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

Date Check

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

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

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

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

It is a GUI world

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

DST Db2 timestamp problems - Char - Daylight Saving Time

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

Spot the difference!

Isn’t that

great/terrifying (delete what is not applicable)

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

This scares me a little…actually quite a lot!

Docu – What Docu?

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

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

If you read it you can find this one sentence:

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

Which, of course, explains everything!

The quick fix…

There is no quick fix!

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

Or

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

Faster and Faster : the best fix ?

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

SET LOG SUSPEND

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

SET LOG RESUME

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

 

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

 

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

TTFN,

Roy Boxwell

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 (the queries presented first of all as a screenshot):

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 = ''                                   
-----+-------+---------+---------+---------+---------+---------+
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'

 

 

-----+---------+---------+---------+---------+---------+---------+------
CLAUSE     PARENT_PNO            LHS_TABNO  RHS_TABNO  MARKER  LITERALS
-----+---------+---------+---------+---------+---------+---------+------
WHERE               0                    0          0  N       0,1,'W'

 

-----+--------+--------+---------+---------+-----------------
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'

 

-----+---------+---------+---------+---------+----------+----
 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               
;

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)

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)

• The Data Server was in version 11 New-Function Mode (NFM)
• APPLICATION COMPATIBILITY was set to V10R1
• 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

BIF HealthCheck licensed Freeware for DB2 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 
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

GIVE and TAKE Program

1,2,3


Give and Take 2020

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


Previous Give & Take

We have “GIVEn” various free-of-charge Use Cases from our SQL WorkloadExpert for Db2 z/OS like:

1 Index Maintenance Costs

2 EXPLAIN Suppression

3 BIF Usage

Limited free-of-Charge Db2 Application

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

<a href="http://www.seg.de/produkte/db2-zos-produkte/sql-workloadexpert-for-db2-zos/" target="


Index Maintenance Costs, EXPLAIN Suppression, BIF

Limited free-of-Charge Db2 Application

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

SQL WorkloadExpert for Db2 z/OS (WLX) contains several “Use Cases”. We provided three of them, free of charge, for one month to different sites. In return, we received their results. We’d like to share this with you now.

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 
BIX HealthCheck – This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions

Inspiring experiences

Customer Statements

3BIF USAGE
News
Read the Customer Comments across the Industry 

 

  • Health Care
  • Insurance
  • Banking
  • Car Manufacturing

First results from Db2 z/OS sites

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

 

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

 [Results from Db2 z/OS sites]

Program 3 – BIF Usage –  has now started

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

BIF Usage video