IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Delete all the tables in a daatbase


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Richy12

New User


Joined: 29 May 2013
Posts: 22
Location: India

PostPosted: Thu Jul 25, 2013 6:26 pm
Reply with quote

My requirement is to purge or empty all the tables in a database.

I used DSNUTILB utility.

LOAD DATA
REPLACE
INTO TABLE TABLE_NAME

Used the load file as DUMMY(SYSREC).

There are around 500 tables in my DB. I cannot use TRUNCATE as I use DB2 v8. Delete is very time consuming. My question is how can I do it for multiple tables? Is there an easy way to do it?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1566
Location: Andromeda Galaxy

PostPosted: Thu Jul 25, 2013 7:02 pm
Reply with quote

I have not tried it myself for load may be try LIBDEF TEMPLATES might help
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Thu Jul 25, 2013 7:56 pm
Reply with quote

What do you find time-consuming: the generation of JCL and control cards, or the running of the delete jobs?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Jul 25, 2013 9:36 pm
Reply with quote

Hello,

A row-by-row delete might take "forever" (depending on volume).

Why not back up everything and then drop everything and then re-CREATE Everything? Make sure to have "canned" all of the permission GRANTs to apply after the database(s) have been recreated.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Tue Jul 30, 2013 3:32 pm
Reply with quote

Hi Richy,

Try this

Run the below query to get the load statement generated
Code:
SELECT                                           
'LOAD DATA REPLACE LOG NO NOCOPYPEND'||           
' INDDN INPUT'||                                 
' INTO TABLE '||                                 
STRIP(SUBSTR(CREATOR,1,8))||'.'||SUBSTR(NAME,1,40)
FROM SYSIBM.SYSTABLES                             
WHERE DBNAME ='LLOYSBBT'                         

copy and paste them in your load job like this and run,

Code:
//SYSIN DD *                                                           
 TEMPLATE INPUT                                                         
      DSN(NULLFILE)                                                     
      DISP(OLD,KEEP,KEEP)                                               
                                                                       
 LOAD DATA REPLACE LOG NO NOCOPYPEND INDDN INPUT INTO TABLE LLOYSBB.TEST1
 LOAD DATA REPLACE LOG NO NOCOPYPEND INDDN INPUT INTO TABLE LLOYSBB.TEST2


Thanks,
sushanth
Back to top
View user's profile Send private message
Richy12

New User


Joined: 29 May 2013
Posts: 22
Location: India

PostPosted: Tue Jul 30, 2013 3:34 pm
Reply with quote

Thanks Sushanth... will try it icon_smile.gif
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

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts insert into session tables with UR DB2 0
No new posts Sync logic between VSAM files and DB2... COBOL Programming 9
No new posts Need to delete inactive records from ... DB2 5
No new posts Unable to delete dataset - VSAM datas... JCL & VSAM 20
No new posts How to delete a VSAM dataset from ins... JCL & VSAM 6

Back to Top