Target Table gets locked after 27000 records get inserted

[nkln@you]

I ran a job.

The job has once source ORACLE 9i stage, one transformer and one target ORACLE 9i stage.


41,000 records were selected from source ORACLE 9i stage . but after 27,000 records were inserted into target, i can see the job remaining standstill i.e there is no increase in no. of records written to target. Only rows/ sec is getting reduced ( e.g from 900 rpws/sec to 6 rows/sec) .
After that target table gets locked. In director when i open log for that job, i can only see processing symbol. The update action used is "clear table and insert rows".

Help me in solving this problem

[ArndW]

Hello nlkn,

there are a couple of things you need to do in order to narrow down your problem. First off, you don't know if the READ or the WRITE is causing the the error - try changing the output to a sequential file and see if the program does the same.

Do you have any errors in the log files? What is your commit frequency (i.e. are your ORACLE buffers overrunning). How do you know that 41K records were selected - does this mean the monitor showed 41K rows coming from Ora and going to the transformer?

I would recommend changing your output link first and seeing if the error still occurs.

[wnogalski]

Try to check if there are any locks (and if there are check what's the cause) using f.e. Enterprise Manager or ask Your DBA for help

[kcbland]

Any chance you're sourcing the same table you are inserting into?

[yaminids]

Hello there,

I faced the same problem. In my case, the main problem was the tablespace. I was trying to insert about 4million rows and the DBA did not allocate the necessary table space. I would suggest you to check the table space allocated.

-Yamini

[Sainath.Srinivasan]

In short, ask your DBA to look at the process running on Oracle to confirm the SQL statement's validity and running.

[Aravind]

Any constraints in using Oracle Bulk loader

[ArndW]

Aravind,

DataStage imposes no additional constraints in using the Oracle Bulk loader.

[Sainath.Srinivasan]

You can use SQL Loader but must have your own management of commit points, bad, discard and log files. Also to disable and enable constraints as necessary.

[nkln@you]

Hi,

thanks for your suggestions. I got themistake. there was a primary key constraint violation for every row inserted in the target, so after 27,000 records got inserted, processing stopeed.


This is the reason i think. is this the mistake?

[kcbland]

You didn't notice 20,000 yellow error messages in the job log???

We threw a lot of guesses your way.

Add a Reject link to your job in the transformer right before the DB stage going to a reject file. Next time you have this problem, you'll see those rows that fail to load go to this file.

[chulett]

And set the job to Abort after X number of Warnings... with X being a number significantly smaller than 27,000.

And Ken, since two warnings are logged for each pk constraint error, that's more like 54,000 warnings to the log.

[Sainath.Srinivasan]

....and set small commit size and wait forever for even the 27000 rows to appear.

[kcbland]

And Ken, since two warnings are logged for each pk constraint error, that's more like 54,000 warnings to the log.

41,000 total - 27,000 good = 14,000 bad

2 warnings messages per issue X 14,000 bad = 28,000 total warning messages

My guess was closer, without going over. I win the showcase showdown.

[chulett]

Show him what's he's won, Johnny! [applause]