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
Generate DDL if Table does not exist
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
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
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
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India