How can the timestamp that is used on many CA Database Management Solutions for DB2 for z/OS product tables be displayed with SQL?

Document ID : KB000025183
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

Several of the CA Database Management Solutions for DB2 for z/OS database tables have a CHAR(8) column containing a TIMESTAMP.  How can this timestamp be displayed with SQL so that the age of the record can be ascertained?

 

Answer:

When a TIMESTAMP field is in a CHAR(8) format it is a Time stamp based on Greenwich Mean Time in STCK format. On z/OS, current date is retrieved by an SVC instruction called STCK (Store Clock). It returns in system time format, for example, '9F58E233D3242FE3' EQUATES TO OCT 30, 1988, 04:53:35.770.

There is an assembler module for converting this on this member:
STCKCONV macro can be found in SYS1.AMACLIB.

The Times tamp is a double-word (8-byte) area containing a 64-bit time of day clock value. It is a derived integer which starts at 1/1/1900 at 0:00 AM GMT and is a number based on milliseconds.

There is a detailed discussion on it to be found on: http://www.redbooks.ibm.com/pubs/pdfs/redbooks/sg242070.pdf

The DB2 TIMESTAMP SQL function is able to process it.

Example Table:

TABLE NAME       CREATOR COLUMN NAME      COLTYPE
PTPLA_STRAT_0100   PTI
                         STRATEGY_CREATOR CHAR
                         STRATEGY_NAME    CHAR
                         TIMESTAMP        CHAR
                         INTERNAL_TYPE    SMALLINT
                         SEQUENCE_FIELD   SMALLINT
                         VERSION          SMALLINT
                         DATA             VARCHAR

If it is browsed with RCU this is displayed:

STRATEGY_CREATOR STRATEGY_NAME TIMESTAMP INTERNAL_TYPE
XXXXXXX          P638AAUI      ARQ..(V.  1,005
XXXXXXX          P638AAUX      ARQ..(V.  1,005
XXXXXXX          P638ACNI      ARQ..7..  1,005
XXXXXXX          P638ACNX      ARQ..7..  1,005
XXXXXXX          P638ACRI      ARQ6 .CW  1,005
XXXXXXX          P638ACRX      ARQ6 .CW  1,005
XXXXXXX          P638APLI      A.....?.  1,005
XXXXXXX          P638APLX      A.....?.  1,005

This column cannot be processed with the DATE function.

The TIMESTAMP function is used to display it correctly.

SELECT STRATEGY_CREATOR, STRATEGY_NAME, TIMESTAMP(TIMESTAMP),
INTERNAL_TYPE, SEQUENCE_FIELD, VERSION, "DATA"
FROM PTI.PTPLA_STRAT_0100
WHERE (INTERNAL_TYPE = 1005);

Here is a sample of the output. The format of the timestamp here is 'YYYY-MM-DD-HH24.MM.SS.msmsms

STRATEGY_CREATOR STRATEGY_NAME #1                         INTERNAL_TYPE
XXXXXXX          P638AAUI      2008-01-25-15.00.26.093790 1005
XXXXXXX          P638AAUX      2008-01-25-15.00.26.093790 1005
XXXXXXX          P638ACNI      2008-01-25-15.02.56.841589 1005
XXXXXXX          P638ACNX      2008-01-25-15.02.56.841589 1005
XXXXXXX          P638ACRI      2008-01-25-15.04.33.298412 1005
XXXXXXX          P638ACRX      2008-01-25-15.04.33.298412 1005
XXXXXXX          P638APLI      2008-01-25-18.31.24.870884 1005
XXXXXXX          P638APLX      2008-01-25-18.31.24.870884 1005