DSXchange

I am currently having an issue copying the data from one DB2 table to another. The table is too large and causes the job to crash. What I would like to do now is copy data by a month at a time. Is there a way to set up a dynamic parameter that will allow me to have a the job to copy the information in the table for one month and then start over and do the next month. I don't know if I could set up a parameter to pull month/year information from the table itself and start at the beginning and loop until it has all of the information up until the most current month and year? Any advice or tips would be greatly appreciated.

Why don't we start with the "too large" part. What exactly is causing the job to crash?

After the job gets to 5,200,000 rows the job freezes and will have to be shutdown in DS Director, or just sit there and eventually crash after a couple of hours.

Okay... when it does crash, do you have the error messages it logs?

There is no reason for DataStage to get to this point and crash. can you provide screen shots of the properties within your DB2 target stage?

Have you had your DBA's look at the DB2 side to check for anything?

Was getting there.

Hi there,

This might be a slightly hieretical post but if all you are doing is copying data from one DB2 table to another on the same DB2 server with a where clause, have you considered using cursors?

You can call a script from a DataSatge job, which starts a DB2 command line session which uses SQL commands to use the cursors. If you are not famliar with cursors your dba will be able to give you "chapter and verse" on cursors.

Cursors will always out perform a DataStage job for this kind of task because they are running solely on the DB2 server. They also don't clog up the DB2 transaction logs which can happen with a DataStage job if the database isn't set up as well as it could be.

A DataStage job will haul the data from the DB2 server across the network on to the DataStage server and back again with the attendant processing overheads.

You may need devise a way of passing your parameter down to the cursor, but I'm sure that can be done.

Hope that helps.

Bob.

So, "cursors"... that's DB2 Speak for a stored procedure, yes? Something written in their procedural language that leverages cursors, I assume. And yes, while the DataStage job certainly shouldn't crash performing that job, there are certainly better ways to do something like that with native tools strictly inside the database... even perhaps just some "insert into select from" SQL.

I don't know if it was actually Datastage that was crashing. My co-worker who manages the server said the server was crashing due to memory limits. He was the one that suggested I copy the data over one month at a time. I am pretty new with Datastage and was trying to see if there was a way I could set the job to copy one month and then loop back to do the next month.

Hi Craig,

No, stored procedures are not required. My script looks like this:

# DB2 command line statements to copy table

/opt/IBM/db2/V10.5/bin/db2 connect to database User usrname Using password

/opt/IBM/db2/V10.5/bin/db2 "Declare cursor_tmp Cursor For Select * From $Source_Schema.$Table_Name"

/opt/IBM/db2/V10.5/bin/db2 "Truncate Table $Target_Schema.$Table_Name immediate"

/opt/IBM/db2/V10.5/bin/db2 "Load From cursor_tmp Of Cursor Insert Into $Target_Schema.$Table_Name nonrecoverable"

Quite straight forward in my case.

Bob.

Gotcha. Can you tell I have no experience with DB2?

We also have had db2 memory problems for large tables, due to run-stats / indexing / rollback / etc problems.

A novel approach worked for us: we had our DBA copy the big tables and let our jobs handle the smaller volume updates etc.

If you do copy it in parts, consider hitting run-stats after each chunk or each few chunks, etc.