Extracting Column Definitions

[EJRoufs]

Is there a quick and easy way to export my list of Column Definitions (from a Sequential File stage) to a text file?

[ogmios]

Not inherent in DataStage. Most shops I know have created their own perl scripts to do stuff like that (based on export files).

Ogmios

[kduke]

If all you need is the column names then write to a sequential file in a job and quit after 1 row. Check make the first row the column names.

[EJRoufs]

If all you need is the column names then write to a sequential file in a job and quit after 1 row. Check make the first row the column names.

Hey, GREAT idea! Thanks! :> We actually need it all, but heck, that's a start. :>

[ray.wurlod]

Some time back I published (here, I think) a routine for dumping a table definition from the Repository. See if you can find it. Otherwise I'm sure I can dig it out.
However, you'd have to have the table definition in the Repository; the routine does not read it from a stage (actually link) definition. You can always use the Save button.

[kduke]

Jeez, Ray that is nasty. Very complex. The link is viewtopic.php?t=85791 .

Ray's code looks CMetaColumn in the records in DS_METADATA. The next field in the record is column names.

Code: Select all

         lColumnNames = Raise(rDSMETADATA<FMC+1>) 
         lColumnDescriptions = Raise(rDSMETADATA<FMC+2>) 
         lDataElements = Raise(rDSMETADATA<FMC+3>) 
         lSQLDataTypeCodes = Raise(rDSMETADATA<FMC+4>) 
         lPrecision = Raise(rDSMETADATA<FMC+5>) 
         lScale = Raise(rDSMETADATA<FMC+6>) 
         lIsNullable = Raise(rDSMETADATA<FMC+7>) 
         lKeyPosition = Raise(rDSMETADATA<FMC+8>) 
         lDisplayWidth = Raise(rDSMETADATA<FMC+9>) 
         * The final three are for UniVerse and UniData files/tables 
         lAssociation = Raise(rDSMETADATA<FMC+10>) 
         lFieldPos = Raise(rDSMETADATA<FMC+11>) 
         lFieldType = Raise(rDSMETADATA<FMC+12>) 

These fields are multivalued. The Raise() command changes them from multivalues to fields or what are called attributes. He parses these into XML or DSX format. Pretty cool.

Someone could easily change this into a create table DDL. This code is a job. It goes into the Job Control part of a job. I think Ray assumed that this web site has users as knowledgable about DataStage as he is. The job has lots of parameters defined in the calls to DSGetParamInfo().

This requires you to save your column names and be able to find them in DS_METADATA. You could easily do something similar in the doc_tool.mdb or MetaStage. I am not sure any of these are simple solutions. Good luck.

You could do something similar in DS_JOBOBJECTS by looking for COutputColumn or CCustomProperty.

[EJRoufs]

Got some info that may come in handy for some of you....... i can't take credit for it, though. One of my co-workers came up with this......

He had the file go to a DB2 Stage, and then set it to "Generate DDL". That gave him pretty much what he needed....... all column names, with the appropriate data types and lengths. He was able to cut and paste that into a text file. :>