Is it possible to generate the schema file using ds job

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
pdv
Premium Member
Premium Member
Posts: 23
Joined: Mon Oct 30, 2006 11:58 pm
Location: Chennai

Is it possible to generate the schema file using ds job

Post by pdv »

Hi All,

Is it possible to generate the schema file using data stage 8.5.
My requirement is as follows.

we want create the delimiter files using dataset or Oracle stage, which we want to re-use it in different project. In order to avoid the rework, is there any option to create the schema files from dataset or Oracle stage, so that i can use the RCP concept to load the data.

Thanks in Advance.
BI-RMA
Premium Member
Premium Member
Posts: 463
Joined: Sun Nov 01, 2009 3:55 pm
Location: Hamburg

Post by BI-RMA »

Sorry ... but I really did not understand what Your requirement is. Please rephrase...
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
pdv
Premium Member
Premium Member
Posts: 23
Joined: Mon Oct 30, 2006 11:58 pm
Location: Chennai

Post by pdv »

Thanks for your reply.

My question,do we have option to create the schema file in 8.5 using meta data.

EX :

Data set metadata

No , decimal 11
Name , varchar2(10)
Address, varchar2(50)


we want to create the schema file for above metadata through data stage job as below.

record
{record_delim='\n', record_length=fixed, delim=none}
(
No:nullable decimal[11,0] {width=11, null_field=' '};
Name:nullable string[10] {width=10, null_field=' '};
Address :nullable string[50] {width=50, null_field=' '};
)
Vino
BI-RMA
Premium Member
Premium Member
Posts: 463
Joined: Sun Nov 01, 2009 3:55 pm
Location: Hamburg

Post by BI-RMA »

You need a table containing the metadata of your sequential files (column names, datatypes, length, scale, nullability etc.).

Then you can run a query to produce a file in the format you need. You will need some UNION statements to produce the header lines and the finishing closing brackets. You may need to experiment a bit to find out which datatypes need which options and how You have to modify Your statement to be able to read your files using the resulting schema-files. So there will be a number of case-statements as well.

Datasets do not have an option to attach a schema-file because the schema-definition is contained in the descriptor-file of the dataset.

You do not need schema files to run queries against databases either. Just enable Runtime Column Propagation on the Stage and run a valid SQL-Statement like "select * from #db_schema#.#db_table# or an SQL-Statement from a file. DataStage will use metadata from the system-tables of the database to propagate the result downstream.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Right click in the columns grid of the table definition you have already in DataStage and choose Save As to save as a schema file.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
pdv
Premium Member
Premium Member
Posts: 23
Joined: Mon Oct 30, 2006 11:58 pm
Location: Chennai

Post by pdv »

Thank you so much for your support. we have developed the job to create scheme file.
Vino
mctny
Charter Member
Charter Member
Posts: 166
Joined: Thu Feb 02, 2006 6:55 am

Post by mctny »

ray.wurlod wrote:Right click in the columns grid of the table definition you have already in DataStage and choose Save As to save as a schema file. ...
I can't find this feature on my table definitions, I am using 9.1 Parallel job. Am I doing something wrong? I want to create schema files from many database tables
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I mean the table definition in the Repository, not in a job.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
mouthou
Participant
Posts: 208
Joined: Sun Jul 04, 2004 11:57 pm

Post by mouthou »

Hi Ray,

I am also in a need to create a schema file systematically as it is difficult to know all the orchestrate keywords to create one manually.

As per this thread, I created a table in Teradata DB and imported in the table definition repository. But when I checked the option to create schema file, I couldn't find any. Please let me know if that feature is revoked in 11.3 or present somewhere else.


Thanks
bkerebel
Participant
Posts: 15
Joined: Wed Jan 05, 2005 3:18 am

Post by bkerebel »

Hi,
to create automatically a schema file in DS 11.5 (I think it was the same before)
1) save the table definition
2) open the saved table definition
3) go to Layout
4) activate the Parallel view
5) copy / cut the schema file auto generated to a schema file in your server

Have a nice day
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

How is a manual cut and paste and a file edit an "Automatic" activity?


My 2 cents:

To automate the activity as the OP (back in 2012) originally asked, I would modify the job to output a 1 row dataset. I would then write an after job routine to use the orchadmin command to dump the schema of that dataset. You could wrap the orchadmin command with some fancy awk/sed and echo statements to properly make a schema file, then orchadmin rm that dataset afterwards.



Something like this:

echo "record" > #seqfilepath##ds_filename#.schema; echo "{record_delim='\n', final_delim=end,delim=',', quote=double}" >> #seqfilepath##ds_filename#.schema; $DSHOME/../PXEngine/bin/orchadmin.exe describe -s #dataset_path##ds_filename# 2>/dev/null | sed 1,11d >> #seqfilepath##ds_filename#.schema; echo #seqfilepath##ds_filename#.schema; $DSHOME/../PXEngine/bin/orchadmin.exe rm #seqfilepath##ds_filename#;


where:

#seqfilepath# is the path where you want to drop the schema file.
#ds_filename# is the dataset filename.

Remember to ensure that your orchadmin.exe has the correct LD_LIBRARY_PATH or equivalent for your OS set.

Not sure if the above command is 100% syntactically correct, but you can figure it out.


The OP asked for an AUTOMATED mechanism that accounted for RCP being used in his/her job. That means that the person wanted an random table to be passed to a job at runtime.

Automation is not clicky clicky on a gui.
Post Reply