Data Copy Issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 20
- Joined: Wed Nov 01, 2017 12:14 pm
Data Copy Issue
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
Matt Johnson
-
- Participant
- Posts: 20
- Joined: Wed Nov 01, 2017 12:14 pm
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.
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
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
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 20
- Joined: Wed Nov 01, 2017 12:14 pm
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
Matt Johnson
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.
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
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.
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.