Target Table gets locked after 27000 records get inserted
Moderators: chulett, rschirm, roy
Target Table gets locked after 27000 records get inserted
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
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
Aim high
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.
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.
Re: Target Table gets locked after 27000 records get inserte
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
41,000 total - 27,000 good = 14,000 badchulett wrote:And Ken, since two warnings are logged for each pk constraint error, that's more like 54,000 warnings to the log.
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.
![Cool 8)](./images/smilies/icon_cool.gif)
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle