RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?

ALIREZA BAGHBAN

RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?

Hi all,

I tried to recover a tablespace to a specific rba before unwanted delete statement, But I did not succeed.

can any one help me? This is the process that I have done:

1- After i created one segmented tablespace, i inserted a few rows in it.

2- i maked a full copy from the tablespace:

COPY TABLESPACE xxxxx.xxxxxx DSNUM ALL
FULL YES
OUTPUT: DB2 IMAGE COPY SUCCESSFUL FOR TABLESPACE xxxx.xxxxxx

3- I also added other records and updated some rows.

4- Its time to happen unwanted delete with clause. This delete statement removes most records.

Now is the time for recover deleted rows:

5- i fetch and keep relevant information about dbid and psid:

SELECT T.DBNAME,T.NAME,HEX(T.DBID),HEX(T.PSID) FROM SYSIBM.SYSTABLESPACE T
WHERE T.NAME='xxxxxx'
WITH UR;--DBID=0101 PSID=0034

6- i archive the log with command : -archive log

7- after the offload is finished i started running REPORT RECOVERY UTILITY on the tablespace and i found related archive logs:

REPORT RECOVERY TABLESPACE xxxxx.xxxxxx
DSNUM ALL
OUTPUT:
REPORT RECOVERY TABLESPACE xxxxx.xxxxxx ARCHLOG1 BSDS VOLUMES:
XXXXXX.ARCHLOG1.XXXXXX.XXXXXX.A0036684
XXXXXX.ARCHLOG1.XXXXXX.XXXXXX.A0036685

8- I then run DSN1LOGP utility to get the RBAs and log ranges of the object in nearest time before the unwanted deletion:

//DSN1LOGP JOB ACCT#,BG,
// NOTIFY=Xxxxxx,CLASS=A,
// MSGLEVEL=(1,1)
//STEP1 EXEC PGM=DSN1LOGP
//STEPLIB DD DSN=xxxxxx.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=A
//SYSABEND DD SYSOUT=A
//ARCHIVE DD DSN=xxxxxx. XXXXXX.ARCHLOG1. XXXXXXXX. XXXXXXXX.A0036684,DISP=SHR
//                 DD DSN=xxxxxx. XXXXXX.ARCHLOG1. XXXXXXXX. XXXXXXXX.A0036685,DISP=SHR
//SYSIN DD *
DATAONLY(YES)
DBID(0101) OBID(0034)


OUTPUT:
DSN1212I DSN1LGRD FIRST LOG RBA ENCOUNTERED 49B500CB6000

...
49B58A9EA605 TYPE( UNDO REDO ) URID(49B58A9EA575)
LRSN(D3A9EEB69102) DBID(0101) OBID(0034) PAGE(00000002)
SUBTYPE(INSERT IN A DATA PAGE) CLR(NO) PROCNAME(DSNISGRT)

49B58A957BDD TYPE( REDO ) URID(49B58A957B4D)
LRSN(D3A9EEB64065) DBID(0101) OBID(0034) PAGE(00000001)
SUBTYPE(UPDATE SPACE MAP) CLR(NO) PROCNAME(DSNISGSU)

49B60DE5E245 TYPE( UNDO REDO ) URID(49B60DE5E13A) ​17:12:39 17.364
LRSN(D3A9F3384E80) DBID(0101) OBID(0034) PAGE(00000002)
SUBTYPE(DELETE IN A DATA PAGE) CLR(NO) PROCNAME(DSNIDILS)

9- In the output, I found the last LRSN before the deleted records and I used it in RECOVER UTILITY:

//RECOVER EXEC DSNUPROC,SYSTEM=XXXX,
// UID=''
//DSNUPROC.SYSUT1 DD DSN=XXXXXXXX.SYSUT1,
// DSNTYPE=LARGE,DCB=(DSORG=PS,LRECL=80,RECFM=FB),
// DISP=(,DELETE,DELETE),
// SPACE=(CYL,(5000,2000),RLSE),
// UNIT=3390,VOL=SER=(XXXXXX,XXXXXX,XXXXXX)
//DSNUPROC.SYSIN DD *
RECOVER TABLESPACE XXXXX.XXXXXXXX DSNUM ALL
TOLOGPOINT X'D3A9F3384E83'


OUTPUT:
LAST FULL COPY IS PARTICIPATING IN RECOVERY OF TABLESPACE
RECOVERY COMPLETE
LOG APPLY PHASE COMPLETE

............................................................

Everything seems to me right, but The table was not properly recovered and existing records were also deleted!!!!???

Thank you for discovering my mistake.

Edited By:
ALIREZA BAGHBAN[Organization Members] @ Dec 30, 2017 - 12:38 PM (America/Eastern)

Michael Hannan

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to ALIREZA BAGHBAN)

Alireza,
 

I have not really played with Point in Time Recover since Db2 allowed us to recover to any point, not just a Sync Point, and automatically handled uncommitted Units of Work. However I assume it works! I don't recall what version of Db2 that feature arrived but fairly long ago (before DB2 10)

You don't really supply enough detail to convince me that you have recovered to the desired point in time, and you also do not say what was wrong with your recovered table exactly. I would have liked to see exactly what Date/Time all SQLs and other events were executed and find timestamps in the Log to Match. Something to indicate you have recovered to the correct place.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

ALIREZA BAGHBAN

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to Michael Hannan)

I found my mistake. Thanks.

Edited By:
ALIREZA BAGHBAN[Organization Members] @ Dec 31, 2017 - 01:11 PM (America/Eastern)
ALIREZA BAGHBAN[Organization Members] @ Dec 31, 2017 - 01:12 PM (America/Eastern)

Michael Hannan

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to Michael Hannan)

In Reply to Michael Hannan:

I have not really played with Point in Time Recover since Db2 allowed us to recover to any point, not just a Sync Point, and automatically handled uncommitted Units of Work. However I assume it works! I don't recall what version of Db2 that feature arrived but fairly long ago (before DB2 10)

I see now it was DB2 9 for z/OS where it was no longer necessary to Recover to a known point of consistency. From the Utility Guide:

"With TORBA or TOLOGPOINT, the RBA or LRSN does not have to be a consistent point in time. The RECOVER utility automatically handles any uncommitted units of work and the data is left in a consistent state."

 Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Sridharan Muthuraman

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to ALIREZA BAGHBAN)

Hi Alireza, Can you give me a hint of the mistake you identified. Just curious to know.

ALIREZA BAGHBAN

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to Sridharan Muthuraman)

Hi,

RBA was wrong.
This time my steps were as follows to find best RBA :
Using DSN1LOGP I took the appropriate URID Then I used the URID to use in DSN1LOGP with SUMMARY YES AND URID SPECIFICATION.
And I used STARTRBA  of unwanted statement for TO RBA  option in recover utility.

The result is accurate. But DSN1LOGP generates a lot line in sysout and As a result spool is filled.

Steen Rasmussen

RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to Michael Hannan)
Great points Michael, recovery is becoming a topic where there’s less and less expertise since we almost never recover anymore. I remember way back in the 90’s when recovery was a weekly exercise either due to REORG failing (before IBM had online reorg) or a DASD crashed.

Steen Rasmussen

From: Michael Hannan [mailto:[login to unmask email]
Sent: Sunday, December 31, 2017 8:27 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Michael Hannan:

I have not really played with Point in Time Recover since Db2 allowed us to recover to any point, not just a Sync Point, and automatically handled uncommitted Units of Work. However I assume it works! I don't recall what version of Db2 that feature arrived but fairly long ago (before DB2 10)

I see now it was DB2 9 for z/OS where it was no longer necessary to Recover to a known point of consistency. From the Utility Guide:

"With TORBA or TOLOGPOINT, the RBA or LRSN does not have to be a consistent point in time. The RECOVER utility automatically handles any uncommitted units of work and the data is left in a consistent state."

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Lizette Koehler

RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to ALIREZA BAGHBAN)
I have found that you can point a DD statement from SYSOUT=* to a dataset. You will fill up dasd but not spool which is much safer.



So say the DD statement that created a large volume of spool was SYSPRINT



Change



//SYSPRINT DD SYSOUT=* (or however it is coded) to



//SYSPRINT DD DISP=(,CATLG,DELETE),UNIT=SYSDA,

// dsn=some.date.set.name.goes.here,

// SPACE=(CYL,(xx,yy),RLSE)



Work with your storage admins on the name and space allocations. Then you would not fill up spool.





Lizette





From: ALIREZA BAGHBAN [mailto:[login to unmask email]
Sent: Tuesday, January 02, 2018 3:48 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?



Hi,

RBA was wrong.
This time my steps were as follows to find best RBA :
Using DSN1LOGP I took the appropriate URID Then I used the URID to use in DSN1LOGP with SUMMARY YES AND URID SPECIFICATION.
And I used STARTRBA of unwanted statement for TO RBA option in recover utility.

The result is accurate. But DSN1LOGP generates a lot line in sysout and As a result spool is filled.



-----End Original Message-----

ALIREZA BAGHBAN

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to Lizette Koehler)

hi lizette,

I did exactly the same, Thank you for your help.

Can anyone help me with this new problem?

When you are using DSN1COPY To Restore a Dropped Object From Full Image Copy, After you complete necessary steps, you have essentially recovered the table space to the point in time of the last full image copy , which I succeeded in. If you want to use log records to perform forward recovery (LOGAPPLY) on the table space and you can not use IBM DB2 Recovery Expert for z/OS or IBM DB2 Log Analysis Tool for z/OS What can you do?

DSN1COPY translates DBID , PSID and OBID between image copy and new object. The PSID of new tablespace is different so DSN1LOGP can not help you with psid filter to report URIDs to find best RBA. REPORT UTILITY can not report you archive log datasets and recovery informations.

What can you do without any tools?

Jørn Thyssen

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to ALIREZA BAGHBAN)

Hi,

The short answer is: you can’t do that.

You would have to write your own log analysis program that reads the log and applies the log records to the image copy or table space. Alternatively generate REDO SQL from the log records. It is much easier if you have DATA CAPTURE CHANGES on the table, but very few customers have that enabled on every table. 

Either way this is a very non-trivial undertaking and you could easily spend months and years developing such a tool.  Then add in the work that might be needed for new releases of Db2 (just ask any vendor how many man hours were needed for Db2 11)

I strongly recommend getting a vendor tool, such as the ones you mention. For some reason recovery of dropped objects is quite common and without a tool you have no way to recover to “current” (very few developers or DBA take a full image copy before they accidentally drop an object)

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

ALIREZA BAGHBAN

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to Jørn Thyssen)

Hi thyssen,

Your answer is correct and complete, But I'm disappointed.

Unfortunately, ibm has banned our country(IRAN) and we are not able to buy ibm products easily. That is why we still use the old version of db2 (10.1 on z/Os).

however thank you for your guidenance.

If someone knows free tool (Log analysis) , Please introduce.

Jørn Thyssen

RE: RECOVER TO LOGPOINT Db2 10 on z/Os - can any one help me?
(in response to ALIREZA BAGHBAN)

Hi Alireza,

Sorry, I did not realize you were from Iran. This probably rules out all the major Db2 tools vendors as they very likely all have to adhere to US export regulations.

I have never heard of any free log analysis tools. As mentioned log analysis is quite complex, so it is unlikely someone will give it away for free.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal.