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
 
delete a table with huge records it gives -904

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

New User


Joined: 30 Apr 2007
Posts: 27
Location: Hyderabad

PostPosted: Thu May 29, 2008 12:10 pm    Post subject: delete a table with huge records it gives -904
Reply with quote

hi
when i use to delete a table with huge records it gives -904
since it has huge records; even though i empty it using where condition but it not efficient ....


so i want to know about any verb for delete table with huge record( i want to empty it only )


even i heard about TRANCATE
can any body haelp me on this (i meams its use and syntax)
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10408
Location: italy

PostPosted: Thu May 29, 2008 12:28 pm    Post subject: Reply to: delete a table with huge records it gives -904
Reply with quote

did You look at the -904 meaning...

emptying a huge table with huge columns by using a "delete * from table"
will be a silly waste of resources,

db2 will log all the activity for a ( useless ) restart

why not simply drop and create the table ???

if You insist on emptying it You must do it using a program and committing every few deletes

I' ll let You find the reasons by searching the forum for an explanation
( I checked and You can easily find it )
Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Thu May 29, 2008 12:32 pm    Post subject:
Reply with quote

Hi jitu,

I think U have to give commit. For ex Give commit statement when count cross 1000 records. I thick It may solve your problem. I think I am right.
Back to top
View user's profile Send private message
am_ne

New User


Joined: 24 Mar 2007
Posts: 25
Location: Bangalore

PostPosted: Thu May 29, 2008 12:55 pm    Post subject: Reply to: delete a table with huge records it gives -904
Reply with quote

Instead of deleting huge records you can opt for REORG utility with purge criteria. This will avoid unnecessary logging overhead.

If you are using "delete * " then it is better to use LOAD REPLACE with dummy file rather than using delete statements.

To be on the safer side take image copy before performing all the operations so that you can recover the database if anything goes wrong.

Thanks
Amit
Back to top
View user's profile Send private message
kishorekyn

New User


Joined: 20 Feb 2008
Posts: 5
Location: Chennai

PostPosted: Thu May 29, 2008 1:59 pm    Post subject: DELETE * FROM TABLE ??
Reply with quote

Hi Enrico will this query not throw any negative sqlcode??
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 Merging records, but not all the time prino DFSORT/ICETOOL 3 Sat Feb 17, 2018 4:20 pm
No new posts Need all records with high key kalidasvs SYNCSORT 13 Fri Feb 09, 2018 10:38 pm
No new posts load and delete table through JCL wit... ram_vizag JCL & VSAM 9 Thu Feb 01, 2018 12:47 am
No new posts Unloading DB2 table in CSV format alo... grvtomar COBOL Programming 7 Sun Jan 28, 2018 2:05 am
No new posts Why is SORT rearranging records that ... cschablow DFSORT/ICETOOL 4 Wed Jan 24, 2018 10:14 pm

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