Bharath's Tech Blog

Conversion of DB2 LRSN to Timestamp and vice-versa

Redirected Recovery (of Recovery Expert for z/OS) migrates data from Source to Target Databases using the Image Copies of the Source Database’s Tablespaces. As it can do only PIT Recovery, we have to mention the Timestamp (in Recovery JCLs build process) that’s close the actual max Image Copy completion time for all Tablespaces in the Database.

Getting the max Image Copy completion time is tricky, which I have realized recently. Below is the scenario.

1.  I used the below SQL query to get the max ICTIME for the Image Copies of Partitioned TS.

  

  SELECT MAX(ICTIME)

    FROM SYSIBM.SYSCOPY

    WHERE

         DBNAME = ’XXXYYY’ AND

         ICDATE = 161202   AND

         DSNUM  > 0

     WITH UR;

      2. I used a Timestamp which is 5 minutes more than the MAX(ICTIME) in my Recovery JCLs build    process. But the process failed for a Tablespace. After PMR with IBM, below has been confirmed.

a.      Though the Timestamp I used is greater than the ICTIME of the Tablespace, it’s not greater than the START_RBA (Hexadecimal format of START_RBA) of the Tablespace.

b.      ICTIME of the Tablespace is 215511, and the Timestamp I used is 2016-12-02-22.26.00. LRSN for the Timestamp I used is D1BC9BC5FA6 which is lesser than the LRSN of the Image Copy D1BC9BC9594.

For the above mentioned reason, Tool is unable to generate Recovery JCL for the Tablespace.

Having knowledge about conversion of LRSN to Timestamp and vice-versa is needed in scenarios like above mentioned. After help from IDUG DB2 community, I’m able to understand the whole process of conversion.

è LRSN to Timestamp

1)      RBA has to be converted to LRSN. Below SQL query can be used for that.

SELECT HEX(START_RBA) FROM SYSIBM.SYSCOPY

WHERE

   DBNAME = 'LPAHCA'   AND

   TSNAME = 'TSATAETT' AND

   ICDATE = 161231     AND

   ICTYPE = 'F'

WITH UR;

2)      Below SQL query can be used to convert the Hex value of START_RBA to Timestamp.

SELECT TIMESTAMP(X'hex value of START_RBA') FROM SYSIBM.SYSDUMMY1;

  è LRSN to Timestamp and vice-versa can be achieved using the below REXX code.

/* REXX */

/* CONVERT TIMESTAMP TO DB2 LRSN */

 TS = '2016-12-31-07.04.37.747296'

 NUMERIC DIGITS 31

 PARSE VAR TS YR '-' MT '-' DY '-' HR '.' MI '.' SE '.' MS

 DYS = DATE('B', YR||MT||DY,'S')  /* DAYS SINCE 0001-01-01 */

 DYS = DYS - 693595                      /* MINUS DAYS FROM 0001-01-01 TO 1900-01-01 */

 TMS = (DYS * 86400000000),

            +(HR * 3600000000),

            +(MI * 60000000),

            +(SE * 1000000),

            + MS                                    /* TOTAL MICROSECONDS FOR THE TIMESTAMP */

 LRSN=SUBSTR(D2X(TMS),1,12)

 SAY 'MICROSECONDS: ' TMS

 SAY 'TIMESTAMP:    ' TS

 SAY 'LRSN:         ' LRSN

/* CONVERT DB2 LRSN TO TIMESTAMP */

 SAY STCK(LRSN)

 EXIT

 STCK: PROCEDURE

 ACC=ARG(1)

 ACC=X2C(ACC)

 TDATE = COPIES('0' , 26)

 ADDRESS LINKPGM "BLSUXTOD ACC TDATE"

 RETURN TDATE