|
View previous topic :: View next topic
|
| Author |
Message |
Richy12
New User
Joined: 29 May 2013
Posts: 22
Location: India
|
|
|
|
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 |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| I have not tried it myself for load may be try LIBDEF TEMPLATES might help |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| What do you find time-consuming: the generation of JCL and control cards, or the running of the delete jobs? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Richy12
New User
Joined: 29 May 2013
Posts: 22
Location: India
|
|
|
|
Thanks Sushanth... will try it  |
|
| Back to top |
|
|
|
|
