Generate DDL if Table does not exist

[sumitgulati]

Hi All,

I have two jobs

1) Job A creates a table X first and then populates data into it.
2) Job B processes data from table X into another table and then drops table X.

There is a sequencer calling Job A and then Job B.

The problem is if Job B fails for some reason I have to manually drop table X before I could rerun Job A. Is there any way to handle this automatically? Is there any option to create the table only if its not there and if the table already exists DataStage ignores the 'create table' statement?

Please suggest.

Thanks and Regards,
-Sumit

[rasi]

Hi Sumit

You can couple of options to look for.

First is you can check in the system table on your database to see if the table exist or not. You can read the system table and have a hash lookup and then if not found then create the table.

Second option is to look for temp table in your database(I am not sure which database you are working with). Most of the databases has the temporary table options. In which you can create a temp table and it will drop when you close the session. As long as you are in one database session and doing job A and job B it will work.

I will suggest you to go for option 1.

thanks
Siva

[vmcburney]

Do you really need a database temp table? This thing will run a whole lot faster if you can write to a temporary hash file or sequential file instead. If you are sticking with the table then get Job A to drop and recreate the table every time using the DDL generate option in the output stage. DataStage is good at recreating database tables on the fly and if you have delete and create table turned on it should handle most situations.

[djdinjan]

Thanks for the suggestion guys. I will have to go through the jobs again to see if this can be achieved using a file instead of a temporary table.

Thanks again,
-Sumit

[sumitgulati]

Thanks for the suggestion guys. I will have to go through the jobs again to see if this can be achieved using a file instead of a temporary table.

Thanks again,
-Sumit