Hi,
I am trying to develop a design where table should be truncated if my main job fails in loading.
I am thinking to design a job to truncate a table.
Rowgen------> oracle connector stage
Now I am stuck here, since there is no option to truncate table (instead we have delete option) in connector stage.
Can anyone help me, how can I perform trucate in oracle connector stage.
truncate table in unsuccessful load
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
truncate table in unsuccessful load
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Probably not. How do you feel about encapsulating a TRUNCATE TABLE statement in a Stored Procedure, which you could invoke from a Stored Procedure stage?
Of course, you could issue a TRUNCATE TABLE statement through sqlplus invoked from an Execute Command activity in a sequence.
Of course, you could issue a TRUNCATE TABLE statement through sqlplus invoked from an Execute Command activity in a sequence.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
From the Oracle Connector documentation:
Code: Select all
3. To truncate a table at runtime, perform these steps:
a. Set Table action to Truncate.
b. Use one of these methods to specify the TRUNCATE TABLE statement:
* Set Generate truncate table statement at runtime to Yes,
and enter the name of the table to truncate in the Table name property.
* Set Generate truncate table statement at runtime to No,
and enter the TRUNCATE TABLE statement in the Truncate table statement property.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's truncation before loading. I tried to answer the question about truncation after loading - unfortunately perhaps one can not make the After SQL in a Connector conditional. Therefore, unless you can determine a means in SQL of determining that the load failed, you will not have a solution "in the Oracle Connector stage".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Thanks Craig.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Yes, truncate before loading but you can see from the proposed design that it would be run as a 'stand alone' job after the load failed to do one thing - truncate the table. So I figured before there would be ok and then the SQL could be something you know would have no impact, like perhaps a delete. And wanted to show that it was in fact supported by the Connector.
You're right in that if you needed to do it after a failed load in the same job then perhaps something After Job that checked the INTERIMSTATUS for failure and then called a stored procedure to do the truncate.
You're right in that if you needed to do it after a failed load in the same job then perhaps something After Job that checked the INTERIMSTATUS for failure and then called a stored procedure to do the truncate.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers