Performance issue

sreddy · Post by **sreddy** » Thu Aug 06, 2009 7:19 am

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....

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Aug 06, 2009 7:40 am

Why is your lead not accepting?

Did you measure the time if you start with a blank db2 table against it being fully loaded ?

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 06, 2009 8:30 am

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.

ArndW · Post by **ArndW** » Thu Aug 06, 2009 8:46 am

Remember that DB2 has no "truncate" function, there is a "LOAD FROM /DEV/NULL OF DEL REPLACE INTO YourTable" equivalent, though.

chulett · Post by **chulett** » Thu Aug 06, 2009 8:53 am

Yoda says: "Odd, I have always found that."

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 06, 2009 9:44 am

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

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 06, 2009 9:53 am

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

miwinter · Post by **miwinter** » Thu Aug 06, 2009 9:53 am

Just to add that that's only applicable for z/os, and not LUW

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 06, 2009 9:56 am

well that was the source of confusion as I am not working on DB2

sreddy · Post by **sreddy** » Fri Aug 07, 2009 8:18 am

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.

keshav0307 · Post by **keshav0307** » Sun Aug 09, 2009 3:33 am

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.

sreddy · Post by **sreddy** » Mon Aug 10, 2009 11:20 am

If i used Truncate we are loosing monthly updated purchase order data.
that is why he is not interested for Truncate and load.

sreddy · Post by **sreddy** » Mon Aug 10, 2009 11:20 am

If i used Truncate we are loosing monthly updated purchase order data.
that is why he is not interested for Truncate and load.