Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
sheema
Premium Member
Posts: 204 Joined: Sat Jul 22, 2006 10:20 am
Post
by sheema » Wed Jul 30, 2008 12:05 pm
I have a need where data in a oracle staging table has to be deleted before the job runs. I do not want to use the open /close command in Oracle Enterprise stage.
Can we create a before/after job sub routine to delete data in oracle table.
Can some one help me in this regard.
Thanks
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Wed Jul 30, 2008 12:21 pm
Why not just script a sqlplus session? Then ExecSH before job will get the job done.
-craig
"You can never have too many knives" -- Logan Nine Fingers
sheema
Premium Member
Posts: 204 Joined: Sat Jul 22, 2006 10:20 am
Post
by sheema » Wed Jul 30, 2008 12:36 pm
Craig,
I have never done it,Can you let me know how do it.
Thanks
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Wed Jul 30, 2008 1:03 pm
Best to sit with your DBA or someone there that's done that kind of thing and have them walk you through it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
mystuff
Premium Member
Posts: 200 Joined: Wed Apr 11, 2007 2:06 pm
Post
by mystuff » Wed Jul 30, 2008 1:05 pm
Create a job control using the following- You can call this in before sub-routine
Code: Select all
UnixCmd = ScriptName : Agr1 :" ": Agr2 :" ": Agr3
Call DSU.ExecSH(UnixCmd, ErrCode)
In the Unix Script :ScriptName call sqlplus
Code: Select all
sqlplus -s $UserId/$Password@$OracleDb <<-EOF
DELETE FROM <your staging table>;
EOF
If you are thinking just to delete that particular staging table so that you can load in it , select option truncate (or) delete rows before inserting values in the plugin
sheema
Premium Member
Posts: 204 Joined: Sat Jul 22, 2006 10:20 am
Post
by sheema » Wed Jul 30, 2008 1:09 pm
Thank you.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Wed Jul 30, 2008 1:13 pm
Pretty bare bones, that. Your DataStage job will have no clue if that sql succeeds or fails.
-craig
"You can never have too many knives" -- Logan Nine Fingers
mystuff
Premium Member
Posts: 200 Joined: Wed Apr 11, 2007 2:06 pm
Post
by mystuff » Wed Jul 30, 2008 1:41 pm
we might have to remove the
-s option and forward the output to a file, then may be grep and indicate error based on it
sqlplus $UserId/$Password@$OracleDb <<-EOF > $File
DELETE FROM <your staging table>;
EOF