I want to perform bulk loads using Oracle OCI Load stage. Job design is something as below:
[Seq File] > [Transformer] > [Oracle OCI Load]
In the properties of Oracle OCI Load stage, i have specified Automatic mode. And this works fine. But all it can do is insert data. How do I specify other options such as REPLACE/TRUNCATE (to truncate table before inserting new data) etc.
- Vinay
REPLACE option - performing bulk loads Oracle OCI Load Stage
Moderators: chulett, rschirm, roy
I wonder why is that option not supported. Is it possible to use Oracle OCI Load stage and provide it with a custom ctl file, using which it can load in Automatic mode. Since in Manual mode, it will not actually load the data.
I do see a 'Oracle 7 Load' stage which has in its properties the option of INSERT/TRUNCATE/APPEND/REPLACE. But this stage does not have options to set the database name, uid, password. I don't have the docs for this stage. When would one use this stage?
I do see a 'Oracle 7 Load' stage which has in its properties the option of INSERT/TRUNCATE/APPEND/REPLACE. But this stage does not have options to set the database name, uid, password. I don't have the docs for this stage. When would one use this stage?
Why, when loading to an Oracle 7 database, of course. In other words, hardly ever any more. And as to why those other options are not supported, you'd have to ask IBM. 
Automatic mode doesn't use a control file or even create any logs, it communicates directly with the Oracle direct path loader under the covers. So there's no mechanism whereby you can 'provide it with a custom ctl file'.
In manual mode, you have to initiate the load manually (hence the name) after the job completes. And if you can't use the control file it generates there's no burning need to even use the stage. Do what others have done in that case -
1) Create your own control file.
2) Use a standard Sequential File stage to create the file to be bulk loaded.
3) Script a call to sqlldr that uses the files from steps #1 and #2.
Automatic mode doesn't use a control file or even create any logs, it communicates directly with the Oracle direct path loader under the covers. So there's no mechanism whereby you can 'provide it with a custom ctl file'.
In manual mode, you have to initiate the load manually (hence the name) after the job completes. And if you can't use the control file it generates there's no burning need to even use the stage. Do what others have done in that case -
1) Create your own control file.
2) Use a standard Sequential File stage to create the file to be bulk loaded.
3) Script a call to sqlldr that uses the files from steps #1 and #2.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks Craig!
I looked at some other posts in the forum and one approach(your name was there too
) was to create a sequence job such that
J1(to truncate) = [Transformer] > [Relational Database Stage]
J2(load data) = [Sequential Stage] > [Transformer] > [Oracle OCI Load]
Ofcourse the second option is to execute sqlloader script. I cannot find the Command Stage when am in Server job(though I can see the pdf doc for it). I can see a Execute Command activity if i create a Sequence. These are two different things right? Do we need to enable it(Command Stage) to be shown in the designer (DataStage 7.5)?
I looked at some other posts in the forum and one approach(your name was there too
) was to create a sequence job such thatJ1(to truncate) = [Transformer] > [Relational Database Stage]
J2(load data) = [Sequential Stage] > [Transformer] > [Oracle OCI Load]
Ofcourse the second option is to execute sqlloader script. I cannot find the Command Stage when am in Server job(though I can see the pdf doc for it). I can see a Execute Command activity if i create a Sequence. These are two different things right? Do we need to enable it(Command Stage) to be shown in the designer (DataStage 7.5)?
Well, it is supported - but only in a Sequence job. It's called the Execute Command stage, as you've noted, and would be the preferred method of accomplishing this.
If you wanted to keep the sqlldr call in your Server job (which I would not recommend as it's not restartable there) you would need to create an 'After Job' subroutine and in that subroutine use the DSExecute function to call your sqlldr script. Or just choose ExecSH and put your direct command line syntax there... either way.
Suggest you keep it as a discrete step, however.
If you wanted to keep the sqlldr call in your Server job (which I would not recommend as it's not restartable there) you would need to create an 'After Job' subroutine and in that subroutine use the DSExecute function to call your sqlldr script. Or just choose ExecSH and put your direct command line syntax there... either way.
Suggest you keep it as a discrete step, however.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers