Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Reorg DISCARD for dependant tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
CICS fan

New User


Joined: 03 Apr 2008
Posts: 82
Location: United States

PostPosted: Mon May 07, 2012 10:47 pm    Post subject: Reorg DISCARD for dependant tables
Reply with quote

All,

We have two tables A and B where B is the child table with refrential integrity set as RESTRICT.

We are using a REORG DISCARD to archive rows from A using something like this.

Code:
REORG TABLESPACE TS1 SHRLEVEL REFERENCE       
          PUNCHDDN LOADSTMT DISCARDDN SYSDISC 
          UNLOAD CONTINUE                               
          DISCARD                                       
          FROM TABLE A                         
             WHEN (TRANS_DATE <= CURRENT DATE - 3 MONTHS)



This is obviously throing B into check pend. Now, we can change the rule to cascade and I ahve two questions on that.

1. If we set this as CASCADE will the rows on B will also be archived?
2. We can provide a archive dataset for A in 'DISCDS'. But can we provide a similar dataset name for 'B'?

Thanks for any help.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue May 08, 2012 8:45 am    Post subject:
Reply with quote

Hi CICSfan,

Quote:
1. If we set this as CASCADE will the rows on B will also be archived?
No. Rows will be deleted in B, when a delete statement is executed in A. Since you are archiving A table via REORG, B will be put in check-pending.
Quote:
2. We can provide a archive dataset for A in 'DISCDS'. But can we provide a similar dataset name for 'B'?
Ofcourse you can. Run the REORG utility with DISCARDS in B then another REORG with DISCARDS for A.

Thanks,
Sushanth
Back to top
View user's profile Send private message
CICS fan

New User


Joined: 03 Apr 2008
Posts: 82
Location: United States

PostPosted: Wed May 09, 2012 2:21 am    Post subject:
Reply with quote

Thanks Sushanth,

Yes, running the reorgs one after another is an option. My question was whether we can run reorg once and have the paretnt table rows and corresponding child table rows archived at one go. So we would need two DISCARD dataset. I cannot find any such option on IBM website. Any ideas?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 09, 2012 3:20 am    Post subject:
Reply with quote

Hello,

Quote:
I cannot find any such option on IBM website. Any ideas?
Possibly because there is no such option. . .

Suggest you run the 2 reorgs as Sushanth recommended.
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2086
Location: Silicon Valley

PostPosted: Wed May 09, 2012 4:32 am    Post subject: Reply to: Reorg DISCARD for dependant tables
Reply with quote

Quote:
So we would need two DISCARD dataset.


I have never done it! (so proceed with caution).

Is it possible using templates? I was thinking of something like this:
Code:

TEMPLATE  mydiscrd  DSN  'PEDRO.DISCARD.&DB..&TS' 
 
REORG TABLESPACE  blah.blah
   DISCARDDN mydiscrd 
Back to top
View user's profile Send private message
CICS fan

New User


Joined: 03 Apr 2008
Posts: 82
Location: United States

PostPosted: Wed May 09, 2012 9:56 am    Post subject:
Reply with quote

Thank Dick and Pedro.

We eventually decided on running the reorgs in series followed by check utility.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 09, 2012 8:41 pm    Post subject: Reply to: Reorg DISCARD for dependant tables
Reply with quote

Good to hear you have a resolution - thank you for letting us know icon_smile.gif

d
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 Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us