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
 

 

Delete all the tables in a daatbase

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Delete all the tables in a daatbase
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: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jul 25, 2013 7:02 pm    Post subject:
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: 1738
Location: Bloomington, IL

PostPosted: Thu Jul 25, 2013 7:56 pm    Post subject:
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

Site Director


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

PostPosted: Thu Jul 25, 2013 9:36 pm    Post subject:
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: 1013
Location: India

PostPosted: Tue Jul 30, 2013 3:32 pm    Post subject:
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    Post subject:
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    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 JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Delete record or Reset RC code using ... mpawan Compuware & Other Tools 4 Tue Jul 26, 2016 3:52 pm
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm


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