Clear a big table then insert rows

selina_king · Post by **selina_king** » Wed Dec 21, 2011 1:57 am

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.

pandeesh · Post by **pandeesh** » Wed Dec 21, 2011 2:17 am

Don't hurry up!!
Clearing means , do you want to truncate the table and insert?
What stage are you using?

Thanks

selina_king · Post by **selina_king** » Wed Dec 21, 2011 2:23 am

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.

pandeesh · Post by **pandeesh** » Wed Dec 21, 2011 2:24 am

Yes.I understand that.
What you are giving exactly in Before sql.

Thanks

pandeesh · Post by **pandeesh** » Wed Dec 21, 2011 2:27 am

What about this method?

Code: Select all

ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

selina_king · Post by **selina_king** » Wed Dec 21, 2011 2:36 am

I want to use "Import from /dev/null of del replace into tablename", it is does not work.

pandeesh · Post by **pandeesh** » Wed Dec 21, 2011 2:39 am

The code what i have posted is also doing the same.
Make sure whethere that's case sensitive.

Try like:

Code: Select all

IMPORT FROM /dev/null/ OF DEL REPLACE INTO tablename

Dont give any quotes in side the command.

selina_king · Post by **selina_king** » Wed Dec 21, 2011 3:06 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
'.

pandeesh · Post by **pandeesh** » Wed Dec 21, 2011 3:10 am

So,does the below work?

Code: Select all

ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

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.

manoj_23sakthi · Post by **manoj_23sakthi** » Wed Dec 21, 2011 3:45 am

Hi,
Could you please try this in script and call this script in sequencer job
Script must contain this inputs :
Use looping concept
-----------------------
select count(*) from Table >D1
D1 >1
then
(
Delete from (select * from table fetch first 100000 rows only )

commit;

)

pandeesh · Post by **pandeesh** » Wed Dec 21, 2011 3:48 am

manoj_23sakthi wrote:Hi,

select count(*) from Table >D1
D1 >1
then
(
Delete from (select * from table fetch first 100000 rows only )

commit;

)

Hi ,

However , delete processes row by row right.
Then how it makes better..
Can you please justify?

pandeesh · Post by **pandeesh** » Wed Dec 21, 2011 3:50 am

manoj_23sakthi wrote:Could you please try this in script and call this script in sequencer job

By the by, that's Job sequence not sequencer job.
if you call like that, Ray and Craig will become angry.

chulett · Post by **chulett** » Wed Dec 21, 2011 7:52 am

Just Ray.

I've seen how the documentation calls it Job Sequence or Sequencer Job interchangeably so it doesn't really bother me as long as you include 'job' in there... there is a Sequence stage after all.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 21, 2011 3:15 pm

chulett wrote:there is a Sequence stage after all

Oh no there isn't!

There's a Sequencer which is an activity in a sequence job.
Sequence jobs don't have stages. They have activities.

chulett · Post by **chulett** » Wed Dec 21, 2011 3:31 pm

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:

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.

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.

It's not worth calling out and whacking people over. IMHO. However, talking about a hash file is still a capital offense.