Well... haven't had a chance to use this stage, but do have a couple of thoughts. Automatic mode generates a Direct Path load script and executes it, with no property available to turn 'direct' off it would seem. So it seems like you might try the 'Manual' option so that it only generates the files but doesn't start the load. This would give you a chance to write some sort of followup job or after-job script that modifies the control file and then initiates a Conventional Path load. Seems to me you could use something like 'sed' to flip the direct 'true' to 'false' or just remove the line so that it defaults back to conventional and then launch the load yourself. HTH.
-craig
Disabling direct path load with ORAOCIBL stage
Moderators: chulett, rschirm, roy
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I would avoid this stage altogether as it is too slow. There are three options for bulk loads in version 6.0:
- The OraBulk stage which creates a ctl and dat file that can then be loaded via a Unix script.
- The Sequential file stage can create a dat file. The OraBulk can create the ctl file or you can create one manually.
- The OraOCIBL stage creates ctl and dat files and optionally runs the load.
I've done some timings on a three column csv file with simple transforms:
Sequential file ran at 75109 rows a second.
OraOCIBL (manual) ran at 26747 rows a second.
OraBulk ran at 6590 rows a second.
As you can see the OCI is 3 times slower and OraBulk is 10 times slower than the Sequential stage at creating the DAT file.
OraOCIBL gives you the ability to run a bulk load automatically however the options are poor. You can't choose a load mode and I was unable to find a bulk load log file. Errors are either not reported at all or returned as information messages instead of warnings or errors. As Blizzard discovered the bulk load wrecked the index making the table unusable and failed to report any type of error. You are better off writing a custom Unix script which traps bulk load errors and returns them to DataStage. Have a search through the forum archive for discussions on retrieving bulk load error messages.
regards
Vincent
- The OraBulk stage which creates a ctl and dat file that can then be loaded via a Unix script.
- The Sequential file stage can create a dat file. The OraBulk can create the ctl file or you can create one manually.
- The OraOCIBL stage creates ctl and dat files and optionally runs the load.
I've done some timings on a three column csv file with simple transforms:
Sequential file ran at 75109 rows a second.
OraOCIBL (manual) ran at 26747 rows a second.
OraBulk ran at 6590 rows a second.
As you can see the OCI is 3 times slower and OraBulk is 10 times slower than the Sequential stage at creating the DAT file.
OraOCIBL gives you the ability to run a bulk load automatically however the options are poor. You can't choose a load mode and I was unable to find a bulk load log file. Errors are either not reported at all or returned as information messages instead of warnings or errors. As Blizzard discovered the bulk load wrecked the index making the table unusable and failed to report any type of error. You are better off writing a custom Unix script which traps bulk load errors and returns them to DataStage. Have a search through the forum archive for discussions on retrieving bulk load error messages.
regards
Vincent