Page 1 of 1
truncate table in unsuccessful load
Posted: Wed Oct 16, 2013 11:01 am
by prasson_ibm
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.
Posted: Wed Oct 16, 2013 3:13 pm
by ray.wurlod
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.
Posted: Wed Oct 16, 2013 11:32 pm
by chulett
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.
Posted: Thu Oct 17, 2013 1:40 am
by ray.wurlod
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".
Posted: Thu Oct 17, 2013 4:26 am
by prasson_ibm
Thanks Craig.
Posted: Thu Oct 17, 2013 6:40 am
by chulett
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.