DSXchange

Hi,

We have created one datastage application which loads data from sequential files to database tables and then from database tables further generates the summarised data into another database tables using stored procedures. We are using DB2 database version 9.x.

We have successfully ran the datastage jobs more than 300 times so far. On an average all datastage jobs in our project finished in 3 hours time.

Thereafter we decided to take back up of our DB2 database. After taking back up of all the DB2 databases, the DBA changed the logging method from circular to archival so that online back up can be taken even if the database is in use.

Now when we started loading data into DB2 database tables(after back up and after changing the log method from circular to archival), the same datastage jobs are running very very slowly(although the input data volume is same as it was earlier).The jobs are taking 10 times more time to run as compared to normal run time.

Can anyone please suggest why it is hapening like this. Is this happening because of archival logging in DB2.? Kindly reply.

Thanks

You can take a look at the following link

http://www.ibm.com/developerworks/data/ ... kline.html

It explains how exactly the circular and archival logging works.
If you are saying the data volumes are same, datastage jobs are same it must be the database related issue.

I think it is good idea to discuss with your DBA what the factors they considered while changing the logging process.

Hope this helps.

arvind_ds wrote:Is this happening because of archival logging in DB2?

That seems like a pretty obvious 'yes' to me. As noted, a chat with your DBA is in order, see what your options are for dealing with your newly slow loads.

Switching back to circular before the next run will quickly determine whether the new logging method is the culprit. Would this be difficult for the DBA to do?

Part of the converstion with your DBA should be a suggestion that these changes be made in a dev environment first to determine the effect. It's not uncommon for DBA to fail to take into account what their changes will have on our ETL activities. It is just common for organizations to put processes/restrictions on how developers move new code into production but fail to apply the same processes to the DBAs.

Rob