Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 9 - Utilities - COPY,REORG Clarification Needed

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 9 - Utilities - COPY,REORG Clarification Needed
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: 1013
Location: India

PostPosted: Tue Oct 23, 2012 11:20 am    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Tue Oct 23, 2012 11:54 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts HALDB data refresh/copy from producti... vineetanand2007 IMS DB/DC 0 Mon Jan 02, 2017 11:16 am
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Single COPY CICS TS datasets and when... Kyle Carroll CICS 2 Tue Oct 11, 2016 9:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us