Data Copy Issue

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Data Copy Issue

Post by mjohnson62982 »

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.
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

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.
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
rschirm
Premium Member
Premium Member
Posts: 27
Joined: Fri Dec 13, 2002 2:53 pm

Post by rschirm »

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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Was getting there. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
boxtoby
Premium Member
Premium Member
Posts: 138
Joined: Mon Mar 13, 2006 5:11 pm
Location: UK

Post by boxtoby »

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.
Bob Oxtoby
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

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.
Respectfully,
Matt Johnson
boxtoby
Premium Member
Premium Member
Posts: 138
Joined: Mon Mar 13, 2006 5:11 pm
Location: UK

Post by boxtoby »

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.
Bob Oxtoby
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Gotcha. Can you tell I have no experience with DB2? :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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