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

	------------------------------------------ DATASTAGE4YOU --------------------------------------
	-- SQL will generate the SCHEMA file for a table ( oracle table). we can convert this for any DB
	select 'record {final_delim=end, record_delim_string=''\n'', delim=''|'', quote=none} (' as col_scm from dual
	union all
	select * from (
	SELECT
	COLUMN_NAME || ':'
	|| CASE WHEN NULLABLE = 'Y' THEN 'nullable ' ELSE '' END
	|| CASE DATA_TYPE
	WHEN 'NUMBER' THEN 'decimal[' || DATA_PRECISION || ',' || DATA_SCALE || ']'
	WHEN 'TIMESTAMP' THEN 'timestamp'
	WHEN 'DATE' THEN 'date'
	WHEN 'VARCHAR2' THEN 'string[max=' || DATA_LENGTH || ']'
	WHEN 'CHAR' THEN 'string[' || DATA_LENGTH || ']'
	ELSE 'Unknown data type' END
	|| CASE WHEN NULLABLE = 'Y' THEN ' {null_field=''''}' END
	|| ';' as COL_SCM
	FROM
	ALL_TAB_COLS
	WHERE
	OWNER = '#SCHEMA_NAME#' AND
	TABLE_NAME = '#TGT_TABLE#'
	order by column_id
	)
	union all
	select ')' from dual
	------------------------------------------ DATASTAGE4YOU --------------------------------------

view raw AutoCrtSch.sql hosted with ❤ by GitHub

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