IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 9 - Utilities - COPY,REORG Clarification Needed
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Mon Oct 22, 2012 12:30 pm
 Reply with quote
Hi,

Consider the below scenarios.


Scenario 1: I have 1 table with 1 CLOB and 1 XML column.

BASE TABLESPACE - DBDSN800.TSBASE01
CLOB TABLESPACE - DBDSN800.TSLOB001 (Implicitly Created)
XML TABLESPACE - DBDSN800.TSXML001 (Implicitly Created)


Scenario 2: I have 2 tables - TABLE1 PARENT TABLE, TABLE2 CHILD TABLE .

PARENT TABLE - SCHEMA.TABLE1 - TABLESPACE - DBDSN800.TSBASEP1
CHILD TABLE - SCHEMA.TABLE2 - TABLESPACE - DBDSN800.TSBASEC1

Both the tables have RI defined between them.

Now my question is what COPY and REORG utilities should I give to make sure both are recoverable to a consistent point using recover utility for each scenarios?

I tried the following.

SCENARIO 1
DEFINED TEMPLATES AND LISTDEF
STEP1: REORG OF BASE TABLESPACE STATISTICS SHRLEVEL NONE LOG NO UNLDDN

Code:
LISTDEF  REO1                                         
          INCLUDE TABLESPACES DATABASE DBDMFP1P BASE 
   TEMPLATE SREC1                                     
   UNIT SYSDA                                         
   DSN 'HLQ.DM.REORG.&DB..&TS..SREC'             
            DISP(NEW,DELETE,CATLG)                   
   REORG TABLESPACE LIST REO1                         
   STATISTICS TABLE (ALL)                             
   INDEX(ALL KEYCARD FREQVAL NUMCOLS 1 COUNT 10       
   FREQVAL NUMCOLS 2 COUNT 10                         
   FREQVAL NUMCOLS 3 COUNT 10                         
   FREQVAL NUMCOLS 4 COUNT 10                         
   FREQVAL NUMCOLS 5 COUNT 10) UPDATE(ALL)           
   SHRLEVEL NONE LOG NO KEEPDICTIONARY               
   SORTDEVT SYSDA SORTNUM 8                           
   UNLDDN SREC1     


STEP2: REORG OF LOB TABLESPACE SHRLEVEL REFERENCE UNLDDN COPYDDN

Code:
LISTDEF  REO2                                           
          INCLUDE TABLESPACES DATABASE DBDMFP1P LOB       
   TEMPLATE SREC2                                       
   UNIT SYSDA                                           
   DSN 'HLQ.DM.REORG.&DB..&TS..SREC'               
            DISP(NEW,DELETE,CATLG)                     
   TEMPLATE SCOPY2                                     
   DSN 'HLQ.DM.FULL.&DB..&TS.(+1)'                 
            DISP(NEW,CATLG,CATLG)                       
            GDGLIMIT(4)                                 
   UNIT SYSDA                                           
   REORG TABLESPACE LIST REO2                           
   SHRLEVEL REFERENCE                                   
   UNLDDN SREC2                                         
   COPYDDN SCOPY2       


STEP3: REORG OF XML TABLESPACE SHRLEVEL NONE LOG NO UNLDDN

Code:
LISTDEF  REO3                                               
          INCLUDE TABLESPACES DATABASE DBDMFP1P XML         
   TEMPLATE SREC3                                           
   UNIT SYSDA                                               
   DSN 'HLQ.DM.REORG.&DB..&TS..SREC'                     
            DISP(NEW,DELETE,CATLG)                           
   REORG TABLESPACE LIST REO3                               
   STATISTICS TABLE(ALL) INDEX (ALL)                         
   SHRLEVEL NONE LOG NO KEEPDICTIONARY                       
   SORTDEVT SYSDA SORTNUM 8                                 
   UNLDDN SREC3   


STEP4: FULLIMAGE COPY TO DEFINE COMMON POINT OF CONSISTENCY

Code:
LISTDEF  CPY1                                             
         INCLUDE TABLESPACES DATABASE DBDMFP1P RI ALL       
   TEMPLATE TMP1                                           
   UNIT SYSDA                                             
   DSN 'HLQ.DM.FULL.&DB..&TS.(+1)'                     
            DISP(NEW,CATLG,CATLG)                         
            GDGLIMIT(4)                                   
   COPY LIST CPY1 COPYDDN (TMP1) FULL YES PARALLEL(4)     
   SHRLEVEL REFERENCE 



SCENARIO2:
STEP1: REORG TABLESPACE (SAME as STEP1 of SCENARIO1)
STEP2: FULLIMAGE COPY TO DEFINE COMMON POINT OF CONSISTENCY (SAME as STEP4 FOR SCENARIO1)


Is the steps followed for SCENARIO1/SCENARIO2 , sufficient to develop a point of consistency? Or should I do REORG SHRLEVEL REFERENCE with inline copy and then a common QUIESCE point? Because when I tried running REORG
SHRLEVEL REFERENCE with inline copy, then I saw in SYSIBM.SYSCOPY, I was not able to see a common RECOVERY point as START_RBA column was not same for all the tablespaces (BASE,LOB,XML).

Also can you confirm if I am doing REORG with SHRLEVEL NONE and UNLDDN, then the REORG is restartable without any data loss?

Please suggest?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India
PostPosted: Tue Oct 23, 2012 11:20 am
 Reply with quote
hmmm...

Separating out Base & LOBs is a good idea. But, LOBs are not reorg'd very frequently, unless there is a really really a big peformance problem.

SCENARIO 1 : STEP3 : SHRLEVEL NONE LOG NO on LOB tablespace <-- Bad Idea

Is there any reason for doing a REORG with SHRLEVEL NONE, since the world is moving towards 24x7 applications(CHANGE, if that's not possible REFERENCE).

Code:
INCLUDE TABLESPACES DATABASE DBDMFP1P RI ALL
What about application RI's ?

Scenario2 is better than Scenario1 for regular house-keeping.

Wouldn't this be easier,
1. COPY everything except DB2 Catalog Tables
2. Quiesce everything Application wise except DB2 Catalog Tables
3. REORG BASE tables application wise with 1 inline copy
4. COPY everything except DB2 Catalog Tables
5. Quiesce everything Application wise except DB2 Catalog Tables

For House-Keeping is always better to consult your shop's Senior DBA & Storage, since they will be having more idea of how an application behaves.

Regards,
sushanth
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Tue Oct 23, 2012 11:41 am
 Reply with quote
Thanks sushanth...

But in the below steps...
Code:

1. COPY everything except DB2 Catalog Tables
2. Quiesce everything Application wise except DB2 Catalog Tables
3. REORG BASE tables application wise with 1 inline copy
4. COPY everything except DB2 Catalog Tables
5. Quiesce everything Application wise except DB2 Catalog Tables


We are doing copy two times... one with Reorg and then a separate copy.

STEP 3,4
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India
PostPosted: Tue Oct 23, 2012 11:54 am
 Reply with quote
Bharat,

Copy in (1) is necessary, because if anything happens during reorg and when you get to a situation that you need to recover, copy(1) is there.

Its recommended to take an inline copy with REORG and sometimes have to take due to LOG NO.

Copy after REORG is just for safety purposes(incase if a imagecopy taken during reorg is unusable due to bad volume something like that).

Thanks,
Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts Reorg abended with REASON=X'00E40347' DB2 2
No new posts VB to VB copy - Full length reached SYNCSORT 8
No new posts Need COBOL COPY Help in MVS Environment COBOL Programming 4
No new posts Issue after ISPF copy to Linklist Lib... TSO/ISPF 1
No new posts Mainframe Programmer with CICS Skill... Mainframe Jobs 0
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us