truncate table in unsuccessful load

[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.

[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.

[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.

[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".

[prasson_ibm]

Thanks Craig.

[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.