DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Mon Jan 29, 2018 3:31 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42753
Location: Denver, CO
Points: 220316

Post Posted: Mon Jan 29, 2018 4:11 pm Reply with quote    Back to top    

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

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Mon Jan 29, 2018 4:28 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42753
Location: Denver, CO
Points: 220316

Post Posted: Mon Jan 29, 2018 4:51 pm Reply with quote    Back to top    

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

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
rschirm

Premium Poster



Group memberships:
Premium Members, Inner Circle

Joined: 13 Dec 2002
Posts: 27

Points: 203

Post Posted: Mon Jan 29, 2018 4:54 pm Reply with quote    Back to top    

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?
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42753
Location: Denver, CO
Points: 220316

Post Posted: Mon Jan 29, 2018 5:00 pm Reply with quote    Back to top    

Was getting there. Wink

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
boxtoby



Group memberships:
Premium Members

Joined: 13 Mar 2006
Posts: 132
Location: UK
Points: 1375

Post Posted: Tue Jan 30, 2018 5:22 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42753
Location: Denver, CO
Points: 220316

Post Posted: Tue Jan 30, 2018 8:19 am Reply with quote    Back to top    

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

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Tue Jan 30, 2018 8:27 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
boxtoby



Group memberships:
Premium Members

Joined: 13 Mar 2006
Posts: 132
Location: UK
Points: 1375

Post Posted: Wed Jan 31, 2018 3:26 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42753
Location: Denver, CO
Points: 220316

Post Posted: Wed Jan 31, 2018 8:32 am Reply with quote    Back to top    

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

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 335

Points: 3391

Post Posted: Wed Jan 31, 2018 9:22 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours