Parallel job hanging on oracle SQLloader ?
Moderators: chulett, rschirm, roy
Parallel job hanging on oracle SQLloader ?
Morning,
we're running a very simple parallel job, writing to an oracle table using the Load-method. We've got 2 nodes defined in the APT-config. For both nodes we receive the 'Export complete' message along with a rowcount. At times (by the look of it rather random) we're receiving just 1 'Load completed' message and the other node seems to be 'stuck' without any (error-)message. Just waiting in vain to report back the other Load completed message...
SQL-loader situation:
- the 'par' and 'ctl'-files are still in the scratch folder (unexpected)
- no bad-file is produced (as expected)
- log-file is complete and contains no errors (as expected)
Nothing strange in the db2diag.log either. So...
Any other place to look for the possible reason?
Or even better any solutions for this unpredictable behavior?
thanks again,
William
we're running a very simple parallel job, writing to an oracle table using the Load-method. We've got 2 nodes defined in the APT-config. For both nodes we receive the 'Export complete' message along with a rowcount. At times (by the look of it rather random) we're receiving just 1 'Load completed' message and the other node seems to be 'stuck' without any (error-)message. Just waiting in vain to report back the other Load completed message...
SQL-loader situation:
- the 'par' and 'ctl'-files are still in the scratch folder (unexpected)
- no bad-file is produced (as expected)
- log-file is complete and contains no errors (as expected)
Nothing strange in the db2diag.log either. So...
Any other place to look for the possible reason?
Or even better any solutions for this unpredictable behavior?
thanks again,
William
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Turn the operator combination off. And try looking in to oracle trace whether its oracle creating problems. Also, whats the APT_ORACLE_LOAD_OPTIONS environment variable setting.
Do you have any index rebuild or maintenance specified if using Oracle enterprise stage.
Do you have any index rebuild or maintenance specified if using Oracle enterprise stage.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Re: Parallel job hanging on oracle SQLloader ?
Hi,
- APT_ORACLE_LOAD_OPTIONS isn't set explicitly, hence we're running with:
OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES)
- Oracle-trace doesn't show any problems.
- No indexes (or rebuilds) on the table yet. So index-maintenance options not set
Turned the operator combination off and currently rerunning.
But since the problem occures ' once in a while' not sure if we ' Catch' it this time...
thanks for your time and effort,
William
- APT_ORACLE_LOAD_OPTIONS isn't set explicitly, hence we're running with:
OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES)
- Oracle-trace doesn't show any problems.
- No indexes (or rebuilds) on the table yet. So index-maintenance options not set
Turned the operator combination off and currently rerunning.
But since the problem occures ' once in a while' not sure if we ' Catch' it this time...
thanks for your time and effort,
William
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 18
- Joined: Wed May 12, 2010 11:57 pm
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
in case of any deadlock, the trace file should have the details and since OP mentioned there is none, i believe its ok.
BTW, skip maintenance is not the default load option, for DIRECT=TRUE datatstage expects the index to be rebuilt or maintained unless SKIP MAINTENANCE is explicity specified. IMO.
BTW, skip maintenance is not the default load option, for DIRECT=TRUE datatstage expects the index to be rebuilt or maintained unless SKIP MAINTENANCE is explicity specified. IMO.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Jobs hanging again. Datastage-processes still up, but not running (no changes in CPU or memory allocation). No DB-processes and no usefull information in the logs after having ran with APT_DISABLE_COMBINATION.
Just the job waiting there for the 'Load completed message' from the second node... Any other clues?
Just the job waiting there for the 'Load completed message' from the second node... Any other clues?