Database Management for Db2 for z/OS: How can the timestamp that is used on many Database Management Solutions for Db2 for z/OS product tables be displayed with SQL?
Article ID: 25183
Updated On:
Products
Issue/Introduction
Several of the 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?
Resolution
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 Timestamp 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.
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) TIMESTMP,
TIMESTAMP, INTERNAL_TYPE, SEQUENCE_FIELD, VERSION, "DATA"
FROM PTI.PTPLA_STRAT_0100
;
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 TIMESTMP TIMESTAMP
---------+---------+---------+---------+---------+---------+---------+--
MPOxRMHD vQkSPqHL 2026-07-09-13.52.34.921560 S3TfvEgl
lqDMEUBZ KbejJzRo 2018-02-24-19.27.26.969230 L0yJMQZD
Ln9l3TsB uOGXJT2X 2035-08-10-14.06.46.460296 3 MXo8b5
vZkoquqM VIFmgMbd 2010-02-22-10.59.31.057822 ElZI4IXR
bdQau0dd uQ eb5v 1985-10-27-02.58.30.931551 r1DTLe1H
5bESjajT CZ5Q af5 2029-08-30-08.04.49.012820 Yo2YeE i
2qWNXeQu OyB1MFM3 2036-01-03-23.54.36.312153 38YqOvn5
5Aabv2oX mAUO83GO 1976-11-15-02.53.02.831384 iU4mm1cA
tsD4hQLU qm9awXLZ 2010-09-17-17.54.03.502428 Fqhdm5Az
S6bvAqcU M5kdHoF2 2012-05-06-06.14.25.727833 IfepsNnP
Additional Information
Feedback
