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
suja.somu
Participant
Posts: 79 Joined: Thu Feb 07, 2013 10:51 pm
Post
by suja.somu » Thu Mar 21, 2013 12:21 pm
For every day run, I have to truncate the target table before loading.
The job runs on a 2- node configuration.
I am using a OPEN Command in the SQL server Enterprise stage.
DELETE FROM tablenameA;
This is not working . Looks like I cannot use a commit in open command. whats the feasible approach to do?
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Thu Mar 21, 2013 3:16 pm
TRUNCATE TablenameA perhaps? This should be non-transactional.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
suja.somu
Participant
Posts: 79 Joined: Thu Feb 07, 2013 10:51 pm
Post
by suja.somu » Thu Mar 28, 2013 9:16 am
Firstly, truncate table commands can be executed only by the owner. So the DBA's are reluctant to grant the owner privilege for the Datastage user.
Secondly, there is a reference for Foreign key to this table. So Truncate table command cannot be used in this case.
What is the best alternative for this process?
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Thu Mar 28, 2013 10:16 am
Typically, that would be a stored procedure written by the DBAs that you would have execute grants on. It would have the grants needed to do the truncate and could handle any RI constraints as well.
-craig
"You can never have too many knives" -- Logan Nine Fingers
suja.somu
Participant
Posts: 79 Joined: Thu Feb 07, 2013 10:51 pm
Post
by suja.somu » Mon Apr 01, 2013 3:15 pm
I used an ODBC connector stage instead of SQL server enterprise stage , to acheive this delete table request in run before SQL comand and then
suja.somu
Participant
Posts: 79 Joined: Thu Feb 07, 2013 10:51 pm
Post
by suja.somu » Mon Apr 01, 2013 3:16 pm
--- Continuation to the previous post
I did " delete table " in before sql and then insert into table.