Clear a big table then insert rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 21
- Joined: Fri Jul 29, 2011 8:02 am
Clear a big table then insert rows
HELLO, everyone.
I want to clear the table then insert rows. When I choose the option it will be delete the data then it will fill up transactions logs in the database.
So I want to clear the data in the before SQL tab. I try to use "Import from /dev/null of del replace into tablename". It is does not work. The logs are
"DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: Error executing statement 'import from /dev/null of del replace into test3'. See following DB2 message for details."
"DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: DB2 warning/info message '[IBM][CLI Driver][DB2/NT] SQL0007N The character "" following "import from " is not valid. SQLSTATE=42601"
I do not know what is the matter. Is anyone have ever saw the trouble please give me idea.
By the way . The target database version is V9.1, so I can not use "truncate table tablename immediate".
Thank you very much. It is urgency.
Sorry. I forget it. I use two DB2 APT stage. One is source table another is target table. I want to extract data from source table and clear the target table then insert the data. I want to truncate table but the target database version is V9.1. "truncate" does not work on it.
I want to clear the table then insert rows. When I choose the option it will be delete the data then it will fill up transactions logs in the database.
So I want to clear the data in the before SQL tab. I try to use "Import from /dev/null of del replace into tablename". It is does not work. The logs are
"DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: Error executing statement 'import from /dev/null of del replace into test3'. See following DB2 message for details."
"DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: DB2 warning/info message '[IBM][CLI Driver][DB2/NT] SQL0007N The character "" following "import from " is not valid. SQLSTATE=42601"
I do not know what is the matter. Is anyone have ever saw the trouble please give me idea.
By the way . The target database version is V9.1, so I can not use "truncate table tablename immediate".
Thank you very much. It is urgency.
Sorry. I forget it. I use two DB2 APT stage. One is source table another is target table. I want to extract data from source table and clear the target table then insert the data. I want to truncate table but the target database version is V9.1. "truncate" does not work on it.
Last edited by selina_king on Wed Dec 21, 2011 2:22 am, edited 1 time in total.
-
- Participant
- Posts: 21
- Joined: Fri Jul 29, 2011 8:02 am
What about this method?
Code: Select all
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
pandeeswaran
-
- Participant
- Posts: 21
- Joined: Fri Jul 29, 2011 8:02 am
The code what i have posted is also doing the same.
Make sure whethere that's case sensitive.
Try like:
Dont give any quotes in side the command.
Make sure whethere that's case sensitive.
Try like:
Code: Select all
IMPORT FROM /dev/null/ OF DEL REPLACE INTO tablename
pandeeswaran
-
- Participant
- Posts: 21
- Joined: Fri Jul 29, 2011 8:02 am
Thank you very much. I already try it. But it is still not work.
warning:
DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: Error executing statement 'IMPORT FROM /dev/null/ OF DEL REPLACE INTO TEST3'. See following DB2 message for details.
DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: DB2 warning/info message '[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "IMPORT" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601
'.
warning:
DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: Error executing statement 'IMPORT FROM /dev/null/ OF DEL REPLACE INTO TEST3'. See following DB2 message for details.
DB2_UDB_API_9,0: Warning: test_db2_notin_test.DB2_UDB_API_9: SQLExecDirect: DB2 warning/info message '[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "IMPORT" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601
'.
So,does the below work?
And one more suggestion is , a unix script which contains the import from script and call that script in Before job subroutine.
That may work.
Code: Select all
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
That may work.
pandeeswaran
-
- Participant
- Posts: 47
- Joined: Tue Feb 23, 2010 12:16 am
- Location: CHENNAI
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Oh no there isn't!chulett wrote:there is a Sequence stage after all
There's a Sequencer which is an activity in a sequence job.
Sequence jobs don't have stages. They have activities.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Just an EMT Ray, an Early Morning Typo.
And I just have to add - you are getting way too anal in your old age. Yes, they are stages. And yes, those stages represent activities. The documentation likes to flip-flop around on how it refers to them:
And I just have to add - you are getting way too anal in your old age. Yes, they are stages. And yes, those stages represent activities. The documentation likes to flip-flop around on how it refers to them:
Designing a job sequence is similar to designing a job. You create the job sequence in the Designer, add activities (as opposed to stages) from the tool palette, and join these together with triggers (as opposed to links) to define control flow.
It's not worth calling out and whacking people over. IMHO. However, talking about a hash file is still a capital offense.You create a job sequence by:
. Placing the stages representing the activities in your sequence on the canvas.
. Linking the stages together.
. Specifying properties details for each activity, defining what it does.
. Specifying trigger information for each activity specifying what action is taken on success or failure of the activity.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers