Auto Generate Table Schema in DataStage

Few days ago, I got a request to explain how to generate the SCHEMA file of a table ? So here we goes.....

a. Design :

We can generate the SCHEMA file with this job. As we can see there are only two stages. One is DB connector stage ( it can be any DB) and a seq file stage which will hold the SCHEMA definition.

b. Job Properties 

We will define these 2 job parameter for our Job. 

SCHEMA_NAME :- this will hold the table schema for which we want to generate the schema file.   

TGT_TABLE :- This is the table name 

c. DB Connector Metadata

We will enable the RCP for the Job

as well as for DB connecor and do not define any column in column space.

d. SQL

We will use below SQL in Sql space of DB connector.

SQL for creating Schema

e. Sequential File Stage Properties 

Define the Sequential File Stage properties as usual. Defined the Target File.

Define the File FORMAT as below  --

Keep the Output Column definition as NULL ( do not define any column in output )

Now compile and run the job. It will ask for SCHEMA and TABLE NAME for which you want to create SCHEMA file.

Please let me know if face any problem with this.



Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you
https://twitter.com/datastage4you
For WHATSAPP group , drop a msg to 91-88-00-906098