Page 1 of 1
Performance issue
Posted: Thu Aug 06, 2009 7:19 am
by sreddy
Hi
I am reading source data and write in to target table using DB2 API stage.
While writing data in to target table , deleting existing records from target table. It is a daily process. When i ask my lead why do not we truncate and load the data at traget table. They are not accepting.
The existing job is taking around 2hrs in production.
Over 2 milion records.
How can increase the performance. Please help me out....
Posted: Thu Aug 06, 2009 7:40 am
by Sainath.Srinivasan
Why is your lead not accepting?
Did you measure the time if you start with a blank db2 table against it being fully loaded ?
Posted: Thu Aug 06, 2009 8:30 am
by priyadarshikunal
If really there is any problem with truncate, they should give an explanation.
delete is always slow and dead slow when there are referential integrity constraints and other factors like rollback space and all.
Hence you might like to ask your lead again if you can use truncate option.
Posted: Thu Aug 06, 2009 8:46 am
by ArndW
Remember that DB2 has no "truncate" function, there is a "LOAD FROM /DEV/NULL OF DEL REPLACE INTO YourTable" equivalent, though.
Posted: Thu Aug 06, 2009 8:53 am
by chulett
Yoda says: "Odd, I have always found that."
Posted: Thu Aug 06, 2009 9:44 am
by priyadarshikunal
Please have a look at this page, (DB2 9.1 for z/OS Topic date: April 2009)
http://publib.boulder.ibm.com/infocente ... tewnew.htm
Posted: Thu Aug 06, 2009 9:53 am
by priyadarshikunal
also there is a stored procedure in DB2 which is used like:
call truncate(tablename) which inturn calls the statement
'IMPORT FROM /dev/null OF DEL REPLACE INTO tablename'
Similar to what you have mentioned.
or
alter table schema.table_name activate not logged initially with empty table
Posted: Thu Aug 06, 2009 9:53 am
by miwinter
Just to add that that's only applicable for z/os, and not LUW
Posted: Thu Aug 06, 2009 9:56 am
by priyadarshikunal
well that was the source of confusion as I am not working on DB2
Posted: Fri Aug 07, 2009 8:18 am
by sreddy
Thank you every one.
I am thinking to redesign job using change capture.
Insert / Update. with this method no need to delete or truncate the records.
please drop your suggestions.
Posted: Sun Aug 09, 2009 3:33 am
by keshav0307
if you are deleting all records from table, then truncate and load in best option.
may be the requirement is not clear, why your lead is not accepting if the performance will improve.
Posted: Mon Aug 10, 2009 11:20 am
by sreddy
If i used Truncate we are loosing monthly updated purchase order data.
that is why he is not interested for Truncate and load.
Posted: Mon Aug 10, 2009 11:20 am
by sreddy
If i used Truncate we are loosing monthly updated purchase order data.
that is why he is not interested for Truncate and load.