Writing table metadata details

ririr · Post by **ririr** » Thu Aug 10, 2006 2:27 pm

I am trying to write the Column Name, Group, Derivation, Key, SQL Type, Length, Scale, Nullable, Display, Data Element, Description detials to a file or to a table for all my table definitions in ODBC etc.. folders in Manager for one of my project.

Just copying doesn't help. I tried Usage Analysis, Report Assistant etc. tools, none of them are of any help. I need the information of the table definition displayed in same format like in the table definition properties.

Any help is appreciated.

kduke · Post by **kduke** » Thu Aug 10, 2006 6:44 pm

This is stored in DS_METADATA. It is hard to import the metadata for these repository tables. You have to trick DataStage into doing it. Also the columns you need are not included in the product so you have to create these columns. You need to know quit a bit about Universe in order to do this. These fields are multivalued. That is another long discussion.

To get the data structure for DS_METADATA you need to find Ray's routine where he exported it to XML or one of my routines like KgdCreateDdlMetadata or KgdEstimateDiskSpace. Next you need to create dictionary items for these columns. To see what columns are already defined:

Code: Select all

LIST DICT DS_METADATA

The column names are in field 12. To create a dictionary for field 12:

Code: Select all

INSERT INTO DICT DS_METADATA 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ColNames', 
   'I', 
   '@RECORD<12>', 
   'ColNames', 
   '64L', 
   'M', 
   'MvCols'
)
;

Here are the column names I would create.
ColNames
ColDesc
ColSqlType
ColPrecision
ColScale
ColFieldPos
ColKeyPos
ColNullable

Once you are done then you can run this SQL.

Code: Select all

select
   ColNames,
   ColDesc,
   ColSqlType,
   ColPrecision,
   ColScale,
   ColFieldPos,
   ColKeyPos,
   ColNullable
from 
   unnest DS_METADATA on MvCols
;

You do this in a UV stage and output it to table of your choice.

kduke · Post by **kduke** » Thu Aug 10, 2006 6:45 pm

You want me to post the rest then let me know.

ririr · Post by **ririr** » Thu Aug 10, 2006 10:50 pm

I will appreciate if you post the rest of it.
Thanks

ray.wurlod · Post by **ray.wurlod** » Fri Aug 11, 2006 2:21 am

There follows a job control routine that can be pasted into a new server job in order to read one or more table definitions and produce the table definition in a number of output formats.

This is job control code. It has been tested with all versions of DataStage from 4.0 through 7.5.2.

There are ten job parameters. The purpose of each can be discerned from the DSGetParamInfo() functions.

Code: Select all

$COPYRIGHT "Copyright (c) 2002, Ray Wurlod.  All rights reserved.  May be reproduced with this copyright notice intact." 

* 
* History 
* ======= 
*   Date    Who               Version  Details of Modification 
* --------  ----------------  -------  ----------------------------------- 
* 20/11/02  Ray Wurlod         20.0.2  Remove previous trailer when OpenMode is "Append" and file exists. 
* 11/11/02  Ray Wurlod &       20.0.1  Added output formats, allow Table Definition name to be Category. 
*           Stewart Hanna 
* 10/09/02  Ray Wurlod         20.0.0  Initial Coding. 
* 

* 
* Notice 
* ====== 
* The algorithm used in this code depends on knowledge that is proprietary to Ascential Software, Inc. 
* No guarantee is either expressly given or implied that the code will continue to function beyond the 
* release of DataStage current when it was written (release 5.2). 
* 

* 
* Acknowledgement 
* =============== 
* Information about the format of INTEGRITY metadata files (IMF) was provided by Stewart Hanna of 
* Ascential Software, Inc. 
* 

* 
* Design 
* ====== 
*  1.  Determine that table definition name supplied is unique in Repository, else generate warning message and stop. 
*  2.  Open the output file for writing, else generate warning and stop. 
*  3.  Read the table definition record from DS_METADATA. 
*  4.  Generate required output, write to file, and log success message. 
* 

* Add in header file in which SQL data types are enumerated. 
$INCLUDE DSINCLUDE DSD_BCI.H 
* Add in header file in which DataStage common constants are declared. 
$INCLUDE DSINCLUDE DSR_COMCONST.H 
* Add in header file with constants for SelectInfo function. 
$INCLUDE UNIVERSE.INCLUDE INFO_KEYS.H 
* Set indent size for XML output 
$DEFINE XMLINDENTSIZE 3 

* Pre-evaluate functions at compile time 
      Equate CR To Char(10) ; Equate XMLCR To "&#xA;" 
      Equate LF To Char(13) ; Equate XMLLF To "&#xD;" 
      Equate XMLQuote To """ 

* Determine machine name of server, O/S-specific command names 
      If System(91) 
      Then 
         Shell = "DOS" 
         DeleteFileCommand = "DEL " 
         CopyFileCommand = "COPY " 
      End 
      Else 
         Shell = "SH" 
         DeleteFileCommand = "rm " 
         CopyFileCommand = "cp " 
      End 
      Call DSExecute(Shell, "hostname", lServerName, Code) 
      lServerName = (If Code = 0 Then lServerName<1> Else "unknown") 


* Read parameter values from this job into local variables. 
      pTableDefinition = DSGetParamInfo(DSJ.ME, "TableDefinition", DSJ.PARAMVALUE) 
      If IsNull(pTableDefinition) Or Trim(pTableDefinition) = "" 
      Then 
         Message = "No table definition or category name." 
         Call DSLogWarn(Message, "DumpColumnDefinitions") 
         GoTo MainExit 
      End 
      pOutputFormat = DSGetParamInfo(DSJ.ME, "OutputFormat", DSJ.PARAMVALUE) 
      pProjectName = DSGetParamInfo(DSJ.ME, "ProjectName", DSJ.PARAMVALUE) 
      pProjectDescription = DSGetParamInfo(DSJ.ME, "ProjectDescription", DSJ.PARAMVALUE) 
      pOutputFile = DSGetParamInfo(DSJ.ME, "OutputFile", DSJ.PARAMVALUE) 
      If System(91) Then Convert "/" To "\" In pOutputFile 
      pDelimiter = DSGetParamInfo(DSJ.ME, "Delimiter", DSJ.PARAMVALUE) 
      pOpenMode = DSGetParamInfo(DSJ.ME, "OpenMode", DSJ.PARAMVALUE) 
      pHeaderLine = DSGetParamInfo(DSJ.ME, "HeaderLine", DSJ.PARAMVALUE) 
      pIncludeNullable = DSGetParamInfo(DSJ.ME, "IncludeNullable", DSJ.PARAMVALUE) 
      pIncludeDescription = DSGetParamInfo(DSJ.ME, "IncludeDescription", DSJ.PARAMVALUE) 

* In steps 1 and 2, a non-empty value in this variable will cause an abnormal exit. 
      Message = "" 


*  1.  Determine that table definition name supplied is unique in Repository, else generate warning message and stop. 
*      Another possibility is that the TableDefinition parameter contains the name of a Category, in which case verify 
*      that this name exists in the DS_METADATA table. 

      Open "DS_METADATA" to fDSMETADATA 
      On Error 
         Message = "Unable to open Table Definitions table DS_METADATA in Repository." 
         Message<-1> = "  System error code = " : Status() : "." 
      End 
      Then 

         * Select List number 10 will be used for list of table definition names 
         ClearSelect 10 

         Command = "SELECT DS_METADATA WITH DSRID LIKE " : Quote("0X'" : pTableDefinition : "'0X") : " TO 10" 
         Call DSExecute("UV", Command, Output, Code) 

         If Selectinfo(10, IK$SLACTIVE) <= 0 
         Then 
            Message = "Unable to find " : Quote(pTableDefinition) : " in DataStage Repository." 
         End 
         Else 

            nTableCount = SelectInfo(10, IK$SLCOUNT) 
            If nTableCount > 1 
            Then 
               * Name entered was Category name. 
               * Select List number 10 contains more than one entry. 
               !!!! Code removed in version 20.0.1 
               ! Message = "More than one Table Definition found based on name " : Quote(pTableDefinition) 
               ! ReadList lTableDefinitions From 10 Else TableDefinitions = "" 
               ! Message<-1> = lTableDefinitions 
               !!!! End of removed code 
            End 
            Else 
               * Name entered was Table Definition name. 
               * Select List number 10 contains 1 entry. 
               !!!! Code removed in version 20.0.1 
               ! ReadList lTableDefinition From 10 Else lTableDefinition = "" 
               ! kDSMETADATA = lTableDefinition 
               !!!! End of removed code 
            End                          ; * end of test for more than one Table Definition 

         End                             ; * end of test for empty Select List 

      End 
      Else 
         Message = "Unable to open Table Definitions table DS_METADATA in Repository." 
         Message<-1> = "  System error code = " : Status() : "." 
      End 

      If Len(Message) 
      Then 
         Call DSLogWarn(Message, "Dump Column Definitions") 
         Return                          ; * abnormal exit 
      End 


*  2.  Open the output file for writing, else generate warning and stop. 

      !!! Code added in version 20.0.2 
      If pOpenMode = "Append" 
      Then 
         Begin Case 
            Case pOutputFormat = "Delimited" 
               NULL 
            Case pOutputFormat = "INTEGRITY" 
               GoSub IntegrityEOF        ; * remove "END #DATATABLE" and following lines 
            Case pOutputFormat = "XML" 
               GoSub XMLEOF              ; * remove "</TableDefinition>" and following lines 
               Indent = 2 * XMLINDENTSIZE          ; * initialize indent for next record 
         End Case 
      End 
      !!! End of code added in version 20.0.2 

      OpenSeq pOutputFile To fOutputFile 
      On Error 
         Message = "Unable to open " : Quote(pOutputFile) : " for writing." 
         Message<-1> = "System error code = " : Status() : "." 
      End 
      Locked 
         Message = "Unable to open " : Quote(pOutputFile) : " for writing." 
         Message<-1> = "File locked for update by process number = " : Status() : "." 
      End 
      Then 
         If pOpenMode = "Overwrite" 
         Then 
            WeofSeq fOutputFile          ; * truncate file for overwriting 
            lHeaderRequired = @TRUE 
         End 
         Else 
            * Position to end of file, removing previous trailer if required 
            Seek fOutputFile, 0, 2 Else NULL       ; * position to end of file for appending 
            lHeaderRequired = @FALSE 
         End 
      End 
      Else 
         If Status() = 0 
         Then 
            lHeaderRequired = @TRUE      ; * file opened OK (but doesn't exist yet) 
         End 
         Else 
            Message = "Unable to open " : Quote(pOutputFile) : " for writing." 
            Message<-1> = "System error code = " : Status() : "." 
         End 
      End                                ; * end of OpenSeq statement 

      If Len(Message) 
      Then 
         Call DSLogWarn(Message, "Dump Column Definitions") 
         Return                          ; * abnormal exit 
      End 



*  3.  Read the table definition record(s) from DS_METADATA.  Create header if necessary.  Dump table defintion(s). 
*      Create trailer if necessary. 

      TableDefinitionCount = 0 
      If lHeaderRequired 
      Then 
         Begin Case 
               * Generate table header information into output file 
            Case pOutputFormat = "Delimited" 
               GoSub DelimitedHeader 
            Case pOutputFormat = "INTEGRITY" 
               GoSub IntegrityHeader 
            Case pOutputFormat = "XML" 
               GoSub XMLHeader 
         End Case 
      End 

      Loop 
      While ReadNext kDSMETADATA From 10 

         * Make sure the record is not a Category record, names of which begin with "\\\". 
         If kDSMETADATA[1,3] <> "\\\" 
         Then 

            ReadU rDSMETADATA From fDSMETADATA, kDSMETADATA 
            On Error 
               Message = "Unable to read Table Definition from DS_METADATA in Repository." 
               Message<-1> = "  System error code = " : Status() : "." 
            End 
            Locked 
               Message = "Table Definition record " : Quote(kDSMETADATA) : " locked by other user." 
               Message<-1> = "  DataStage ID of that user = " : Status() : "." 
            End 
            Then 
               pTableDefinition = Field(kDSMETADATA, "\", Count(kDSMETADATA,"\") + 1, 1) 
               lOccurrence = 1 
               GoSub ProcessTableDefinition        ; * record read successfully 
               TableDefinitionCount += 1 
            End 
            Else 
               Message = "Table Definition record " : Quote(kDSMETADATA) : " not found in DS_METADATA table in Repository." 
               Message<-1> = "  System error code = " : Status() : "." 
            End 

            If Len(Message) 
            Then 
               Message<-1> = "  Processing of table definition " : Quote(kDSMETADATA) : " skipped." 
               Call DSLogWarn(Message, "Dump Column Definitions") 
            End 

            Release fDSMETADATA, kDSMETADATA       ; * release exclusive lock on record 

         End                             ; * end of IF test for Category record in DS_METADATA 

      Repeat 

      * Generate table trailer information into output file. 
      Begin Case 
         Case pOutputFormat = "Delimited" 
            NULL                         ; * no trailer required 
         Case pOutputFormat = "INTEGRITY" 
            GoSub IntegrityTrailer 
         Case pOutputFormat = "XML" 
            GoSub XMLTrailer 
      End Case 

      CloseSeq fOutputFile 
      Close fDSMETADATA 

      If TableDefinitionCount = 1 
         Then Message = "One table definition dumped." 
         Else Message = TableDefinitionCount : " table definitions dumped." 
      Call DSLogInfo(Message, "Dump Column Definitions") 

MainExit: 
      RETURN 



************************************************************ 
ProcessTableDefinition: 
************************************************************ 

*  4.  Generate required output, write to file, and log success message. 

      Message = "" 

      lTableName = Field(kDSMETADATA, "\", Count(kDSMETADATA, "\") + 1, 1) 
      lTableShortDesc = Raise(rDSMETADATA<DSR.DICT.DESC>) 

      FindStr "CMetaColumn" In rDSMETADATA, lOccurrence Setting FMC,VMC,SMC 
      Then 

         nColumns = Field(rDSMETADATA<FMC>, "/", 2, 1)       ; * number of column definitions in collection 
         If nColumns = 0 
         Then 
            * Stored procedures use the second columns collection in the record. 
            * Go get the next one. 
            lOccurrence += 1 
            GoTo ProcessTableDefinition 
         End 

         * Replace double-quotes, carriage return and line feed with appropriate XML tokens 
         If pOutputFormat = "XML" 
         Then 
            rDSMETADATA = Ereplace(rDSMETADATA, '"', XMLQuote, -1, 0) 
            rDSMETADATA = Ereplace(rDSMETADATA, CR, XMLCR, -1, 0) 
            rDSMETADATA = Ereplace(rDSMETADATA, LF, XMLLF, -1, 0) 
         End 

         * Convert multi-valued fields to field-mark-delimited dynamic arrays, so as to take advantage 
         * of hint structure in subsequent processing. 
         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>) 


         * Generate column definitions into output file. 
         WriteCount = 0 
         WriteFailCount = 0 
         Begin Case 
            Case pOutputFormat = "Delimited" 
               GoSub DumpDelimited 
            Case pOutputFormat = "INTEGRITY" 
               GoSub DumpIntegrity 
            Case pOutputFormat = "XML" 
               GoSub DumpXML 
         End Case 
         MsgText = "Found " : nColumns : " column definition" : (If nColumns = 1 Then "" Else "s") 
         MsgText := " for table definition " : Quote(pTableDefinition) : "." 
         MsgText<-1> = Lines 
         MsgText<-1> = "  Wrote " : WriteCount : " column definitions to " : Quote(pOutputFile) : "." 
!!       MsgText<-1> = "  Failed to write " : WriteFailCount : "." 
         Call DSLogInfo(MsgText, "Dump Column Definitions") 

      End 
      Else 
         Message = "Could not find CMetaColumn collection in DS_METADATA record " : Quote(kDSMETADATA) : "." 
      End                                ; * end of FindStr statement 

      If Len(Message) 
      Then 
         Call DSLogWarn(Message, "Dump Column Definitions") 
      End 

      Return                             ; * end of ProcessTableDefinition internal subroutine 


************************************************************ 
DumpDelimited: 
************************************************************ 


      Lines = "" 

      lTableQualifier = (If nTableCount = 1 Then "" Else pTableDefinition : ".") 

      For k = 1 To nColumns 
         Line = DQuote(lTableQualifier : lColumnNames<k>) : cDelimiter 
         SQLDataType = DQuote(Field(SQL.TYPE.NAMES, ",", lSQLDataTypeCodes<k>, 1)) 
         Line := SQLDataType : cDelimiter 
         Line := lPrecision<k> : cDelimiter 
         If SQLDataType = "Decimal" Or SQLDataType = "Numeric" 
         Then 
            Line := lScale<k> 
         End 
         Line := cDelimiter 
         If pIncludeNullable = "Yes" 
         Then 
            Line := (If lIsNullable<k> Then "Y" Else "N") : cDelimiter 
         End 
         Line := (If lKeyPosition<k> Then "Y" Else "N") 
         If pIncludeDescription = "Yes" 
         Then 
            Line := cDelimiter : DQuote(lColumnDescriptions<k>) 
         End 
         WriteSeq Line To fOutputFile 
         Then 
            WriteCount += 1 
            Lines<-1> = "    " : Line 
         End 
         Else 
            WriteFailCount += 1 
         End 
      Next k 

      Return                             ; * end of DumpDelimited internal subroutine 



************************************************************ 
DumpIntegrity: 
************************************************************ 


      ColStart = 1 
      pMaxRecordLength = 0 

      For k = 1 To nColumns 
         If lDisplayWidth<k> = 0 
         Then 
            pMaxRecordLength += lPrecision<k> + lScale<k> 
         End 
         Else 
            pMaxRecordLength += lDisplayWidth<k> 
         End 
      Next k 

      If (IsNull(lTableShortDesc[1,40]) OR Trim(lTableShortDesc) = "") 
      Then 
         lTableShortDesc = "No Table Desc" 
      End 

      WriteSeq " " To fOutputFile Else NULL 
      WriteSeq "    DATAFILE BEGIN" To fOutputFile Else NULL 
      WriteSeq "      NAME                       ":pTableDefinition[1,8] To fOutputFile Else NULL 
      WriteSeq "      DESCRIPTION                ":lTableShortDesc[1,40] To fOutputFile Else NULL 
      WriteSeq "      MAXRECORDLENGTH            ":pMaxRecordLength To fOutputFile Else NULL 
      WriteSeq "      FORMAT                     FIXEDTERM" To fOutputFile Else NULL 
      WriteSeq "      CODEPAGE                   NATIVE" To fOutputFile Else NULL 
      WriteSeq " " To fOutputFile Else NULL 

      For k = 1 To nColumns 

         If (IsNull(lColumnDescriptions<k>) OR Trim(lColumnDescriptions<k>) = "") 
         Then 
            lColumnDescriptions<k> = "No Col Desc" 
         End 

         If lDisplayWidth<k> = 0 
         Then 
            lDisplayWidth<k> = lPrecision<k> + lScale<k> 
         End 

         If (lSQLDataTypeCodes<k> = "2" OR lSQLDataTypeCodes<k> = "3" OR lSQLDataTypeCodes<k> = "4" OR lSQLDataTypeCodes<k> = "5" OR lSQLDataTypeCodes<k> = "6" OR lSQLDataTypeCodes<k> = "7" OR lSQLDataTypeCodes<k> = "8" OR lSQLDataTypeCodes<k> = "17" OR lSQLDataTypeCodes<k> = "19") 
         Then 
            pSQLIntType = "I"            ; * I for Integer (DataStage - Numeric, Decimal,Integer,SmallInt,Float,Real,Double,BigInt,TinyInt) 
         End 
         Else 
            pSQLIntType = "S"            ; * S for String  (DataStage - Char,Date,Time,Timestamp,VarChar,LongVarChar,Binary,VarBinary,LongVarBinary,Bit" 

         End 

         WriteSeq "      FIELD BEGIN" To fOutputFile Else NULL 
         WriteSeq "        NAME                     ":lColumnNames<k>[1,7] To fOutputFile Else NULL 
         WriteSeq "        DESCRIPTION              ":Field((lColumnDescriptions<k>[1,40]),LF,"1") To fOutputFile Else NULL 
         WriteSeq "        STARTPOSITION            ":ColStart To fOutputFile Else NULL 
         WriteSeq "        LENGTH                   ":lDisplayWidth<k> To fOutputFile Else NULL 
         WriteSeq "        DATATYPE                 ALPHANUM" To fOutputFile Else NULL 
         WriteSeq "        USETYPE                  ":pSQLIntType To fOutputFile Else NULL 
         WriteSeq "        MISSINGVALUE             S" To fOutputFile Else NULL 
         WriteSeq "      END" To fOutputFile Then WriteCount += 1 Else WriteFailCount += 1 
         WriteSeq " " To fOutputFile Else NULL 

         ColStart += lDisplayWidth<k> 

      Next k 

      WriteSeq "    END" To fOutputFile Else NULL 

      Return                             ; * end of DumpIntegrity internal subroutine 



************************************************************ 
DumpXML: 
************************************************************ 

      * Date/time modified is recorded in the DS_AUDIT file.  We get it from there 
      * using the Trans() function (lazy programming). 
      AuditInfo = Raise(Trans("DS_AUDIT", "4\" : kDSMETADATA, -1, "X")) 
      DTC = AuditInfo<1> 
      DTM = AuditInfo<5,1> 
      If DTM = "" 
      Then 
         DateModified = DTC[1,10] 
         TimeModified = DTC[12,8] 
      End 
      Else 
         DateModified = DTM[1,10] 
         TimeModified = DTM[12,8] 
      End 
      Convert ":" To "." In TimeModified 

      CurrentIndent = Space(Indent) 
      Line = CurrentIndent : "<Record Identifier=" : DQuote(kDSMETADATA) 
      Line := " DateModified=" : DQuote(DateModified) 
      Line := " TimeModified=" : DQuote(TimeModified) 
      Line := ' Type="MetaTable"' 
      Line := " Readonly=" : DQuote(rDSMETADATA<1> <> "NRO") : ">" 
      WriteSeq Line To fOutputFile Else NULL 

      Indent += XMLINDENTSIZE 
      CurrentIndent = Space(Indent) 

      Line = CurrentIndent : '<Property Name="ShortDesc">' : rDSMETADATA<4> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      If IsNull(rDSMETADATA<5>) Or Trim(rDSMETADATA<5>) = "" 
      Then 
         rDSMETADATA<5> = "(none)" 
      End 
      Line = CurrentIndent : '<Property Name="Description" PreFormatted="1">' : rDSMETADATA<5> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="Version">' : rDSMETADATA<6> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="QuoteChar">' : rDSMETADATA<8> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="Multivalued">' : rDSMETADATA<9> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="SPErrorCodes">' : rDSMETADATA<10> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Collection Name="Columns" Type="MetaColumn">' 
      WriteSeq Line To fOutputFile Else NULL 

      Indent += XMLINDENTSIZE 
      CurrentIndent = Space(Indent) 

      For k = 1 To nColumns 

         Line = CurrentIndent : '<SubRecord>' 
         WriteSeq Line To fOutputFile Else NULL 

         Indent += XMLINDENTSIZE 
         CurrentIndent = Space(Indent) 

         Line = CurrentIndent : '<Property Name="Name">' : lColumnNames<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         If IsNull(lColumnDescriptions<k>) Or Trim(lColumnDescriptions<k>) = "" 
         Then 
            lColumnDescriptions<k> = "(none)" 
         End 
         Line = CurrentIndent : '<Property Name="Description" Preformatted="1">' : lColumnDescriptions<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="SQLType">' : lSQLDataTypeCodes<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="Precision">' : lPrecision<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="Scale">' : lScale<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="Nullable">' : lIsNullable<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="KeyPosition">' : lKeyPosition<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="DisplaySize">' : lDisplayWidth<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="Association">' : lAssociation<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="FieldPos">' : lFieldPos<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="FieldType">' : lFieldType<k> : '</Property>' 
         WriteSeq Line To fOutputFile Else NULL 
         Line = CurrentIndent : '<Property Name="PadChar"/> 
         WriteSeq Line To fOutputFile Else NULL 

         Indent -= XMLINDENTSIZE 
         CurrentIndent = Space(Indent) 

         Line = CurrentIndent : '</SubRecord>' 
         WriteSeq Line To fOutputFile Then WriteCount += 1 Else WriteFailCount += 1 

      Next k 

      Indent -= XMLINDENTSIZE 
      CurrentIndent = Space(Indent) 

      Line = CurrentIndent : '</Collection>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="SEQ-Delimiter">' : rDSMETADATA<39> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="SEQ-QuoteChar">' : rDSMETADATA<40> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="SEQ-ColHeaders">' : rDSMETADATA<41> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="SEQ-FixedWidth">' : rDSMETADATA<42> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="SEQ-ColSpace">' : rDSMETADATA<43> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="SEQ=OmitNewLine">' : rDSMETADATA<44> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="AllowColumnMapping">' : rDSMETADATA<45> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="Locator">' : rDSMETADATA<54> : '</Property>' 
      WriteSeq Line To fOutputFile Else NULL 

      Line = CurrentIndent : '<Property Name="PadChar"/> 
      WriteSeq Line To fOutputFile Else NULL 

      Indent -= XMLINDENTSIZE 
      CurrentIndent = Space(Indent) 

      Line = CurrentIndent : '</Record>' 
      WriteSeq Line To fOutputFile Else NULL 

      Return                             ; * end of DumpXML internal subroutine 



************************************************************ 
DelimitedHeader: 
************************************************************ 

      Begin Case 
         Case pDelimiter = "Tab" 
            cDelimiter = Char(9) 
         Case pDelimiter[1,5] = "Comma" 
            cDelimiter = "," 
         Case pDelimiter[1,4] = "Pipe" 
            cDelimiter = "|" 
         Case pDelimiter[1,5] = "Colon" 
            cDelimiter = ":" 
         Case pDelimiter[1,4] = "Semi" 
            cDelimiter = ";" 
         Case pDelimiter[1,8] = "Asterisk" 
            cDelimiter = "*" 
      End Case 

      If pHeaderLine = "Yes" And pOpenMode = "Overwrite" 
      Then 
         HeaderLine = "ColumnName" : cDelimiter : "SQLDataType" : cDelimiter : "Precision" : cDelimiter : "Scale" : cDelimiter : "IsNullable" : cDelimiter : "KeyColumn" : cDelimiter : "Description" 
         WriteSeq HeaderLine To fOutputFile Else NULL 
      End 

      Return                             ; * end of DelimitedHeader internal subroutine 



************************************************************ 
IntegrityHeader: 
************************************************************ 

      * Provide default INTEGRITY project name if necessary. 
      If IsNull(pProjectName) Or Trim(pProjectName) = "" 
      Then 
         pProjectName = DSGetProjectInfo(DSJ.PROJECTNAME) 
      End 

      * Generate max record length for INTEGRITY format. 
      pMaxRecordLength = 0 
      For k = 1 To nColumns 
         If lDisplayWidth<k> = 0 
         Then 
            pMaxRecordLength += lPrecision<k> + lScale<k> 
         End 
         Else 
            pMaxRecordLength += lDisplayWidth<k> 
         End 
      Next k 

      * Generate dummy table description for INTEGRITY or XML format. 
      If (IsNull(lTableShortDesc) OR Trim(lTableShortDesc) = "") 
      Then 
         lTableShortDesc = "No Table Desc" 
      End 

      * Generate non-repeating portion of output file 
      WriteSeq "PROJECT BEGIN" To fOutputFile Else Call DSLogWarn("Problem writing to file", "DEBUG") 
      WriteSeq "  NAME                           ":pProjectName[1,8] To fOutputFile Else NULL 
      WriteSeq "  DESCRIPTION                    ":pProjectDescription[1,40] To fOutputFile Else NULL 
      WriteSeq " " To fOutputFile Else NULL 
      WriteSeq " " To fOutputFile Else NULL 
      WriteSeq " " To fOutputFile Else NULL 
      WriteSeq "  DATA_TABLE BEGIN" To fOutputFile Else NULL 


      Return                             ; * end of IntegrityHeader internal subroutine 



************************************************************ 
IntegrityTrailer: 
************************************************************ 

      WriteSeq " " To fOutputFile Else NULL 
      WriteSeq "  END   #DATA_TABLE" To fOutputFile Else NULL 

      WriteSeq " " To fOutputFile Else NULL 
      WriteSeq "END   #PROJECT" To fOutputFile Else NULL 

      Return                             ; * end of IntegrityTrailer internal subroutine 


************************************************************ 
IntegrityEOF:!!! Routine added in version 20.0.2 
************************************************************ 
* Remove "END #DATATABLE" and subsequent lines from existing file 

      Call !GET.PATHNAME(pOutputFile, TempDir, TempFile, Code) 

      OpenPath TempDir To fTemp 
      On Error 
         Message = 'Unable to open "' : TempDir : '".' 
         Call DSLogWarn(Message, "DumpColumnDefinitions") 
      End 
      Then 
         ReadU TempRecord From fTemp, TempFile 
         Locked 
            Message = Quote(pOutputFile) : " locked for update by another DataStage process." 
            Message<-1> = "   DataStage process number = " : Status() : "." 
            Call DSLogWarn(Message, "DumpColumnDefinitions") 
         End 
         Then 
            NewRecord = "" 
            Loop 
               Remove Element From TempRecord Setting MoreElements 
            While MoreElements And Not(Element Matches "0X'END'0X'#DATA_TABLE'0X") 
               NewRecord<-1> = Element 
            Repeat 
            Write NewRecord To fTemp, TempFile 
            Then 
               NULL 
            End 
         End 
         Else 
            Release fTemp, TempFile      ; * doesn't exist, so release update lock 
         End 
         Close fTemp 
      End 
      Else 
         Message = 'Unable to open "' : TempDir : '".' 
         Call DSLogWarn(Message, "DumpColumnDefinitions") 
      End 
      Return                             ; * end of IntegrityEOF internal subroutine 



************************************************************ 
XMLHeader: 
************************************************************ 

      Indent = 0 

      WriteSeq '<?xml version="1.0" encoding="UTF-8"?>' To fOutputFile Else Null 
      WriteSeq '<DSExport>' To fOutputFile Else Null 

      Indent += XMLINDENTSIZE 
      CurrentIndent = Space(Indent) 

      Line = CurrentIndent : '<Header CharacterSet="ENGLISH" ExportingTool="Ascential DataStage" ToolVersion = "3"' 
      Line := ' ServerName="' : lServerName : '" ToolInstanceID="DumpColumnDefinitions" Date="' : Oconv(@DATE,"D-YMD[4,2,2]") 
      Line := '" Time = "' : Oconv(@TIME, "MTS:") : '"/>' 
      WriteSeq Line To fOutputFile Else Null 

      WriteSeq CurrentIndent : '<TableDefinitions>' To fOutputFile Else Null 

      Indent += XMLINDENTSIZE 
      CurrentIndent = Space(Indent) 

      Return                             ; * end of XMLHeader internal subroutine 


************************************************************ 
XMLTrailer: 
************************************************************ 

      Indent -= XMLINDENTSIZE 
      CurrentIndent = Space(Indent) 

      WriteSeq CurrentIndent : '</TableDefinitions>' To fOutputFile Else Null 

      Indent -= XMLINDENTSIZE 

      WriteSeq '</DSExport>' To fOutputFile Else Null 

      Return                             ; * end of XMLTrailer internal subroutine 



************************************************************ 
XMLEOF:!!! Routine added in version 20.0.2 
************************************************************ 
* Remove </TableDefinitions> and subsequent lines from existing file 

      Call !GET.PATHNAME(pOutputFile, TempDir, TempFile, Code) 

      OpenPath TempDir To fTemp 
      On Error 
         Message = 'Unable to open "' : TempDir : '".' 
         Call DSLogWarn(Message, "DumpColumnDefinitions") 
      End 
      Then 
         ReadU TempRecord From fTemp, TempFile 
         Locked 
            NULL 
         End 
         Then 
            NewRecord = "" 
            Loop 
               Remove Element From TempRecord Setting MoreElements 
            While MoreElements And Not(Element Matches "0X'</TableDefinitions>'0X") 
               NewRecord<-1> = Element 
            Repeat 
            Write NewRecord To fTemp, TempFile 
            Then 
               NULL 
            End 
         End 
         Else 
            Release fTemp, TempFile      ; * doesn't exist, so release update lock 
         End 
         Close fTemp 
      End 
      Else 
         Message = 'Unable to open "' : TempDir : '".' 
         Call DSLogWarn(Message, "DumpColumnDefinitions") 
      End 
      Return

ameyvaidya · Post by **ameyvaidya** » Fri Aug 11, 2006 3:09 am

How about exporting the Table definitions to XML file through DS manager and writing a Program(Java maybe) or a Job in DataStage to parse the XML file and get the information.

I tested writing a job with the following stages:

Code: Select all

[Sequential File]->[XML INPUT]->[Transformer]->[Sequential File]

Except for a vertical pivot which i havent implemented, i get the needed info in a comma separated file.

The tricky parts are:
when exporting, select Externalized strings in the XML tab in the export dialog.
getting the output column defs for the XML input stage. I selected the Auto-Check in the XML Metadata importer tool.

IHTH

kduke · Post by **kduke** » Fri Aug 11, 2006 6:47 am

Even if you are really good with Universe then Ray's code is very advanced. This can dump DS_METADATA to a xml file or a comma deleimted. This is not the same code originally posted. To break it down a little bit.

Code: Select all

         Command = "SELECT DS_METADATA WITH DSRID LIKE " : Quote("0X'" : pTableDefinition : "'0X") : " TO 10" 
         Call DSExecute("UV", Command, Output, Code)

Starts selecting the keys for DS_METADATA. This is not a SQL Select. This is Universe's own version. To process this list of keys then:

Code: Select all

      Loop 
      While ReadNext kDSMETADATA From 10 

         * Make sure the record is not a Category record, names of which begin with "\\\". 
         If kDSMETADATA[1,3] <> "\\\" 
         Then 

            ReadU rDSMETADATA From fDSMETADATA, kDSMETADATA

The ReadNext loads variable kDSMETADATA with one key value from the first to the last until this list is exhausted and the Loop fails. Next he skips anything that starts with "\\\". Next he reads and locks the record. A ReadU locks the record. It requires a Release or a Write to unlock the record. The record is loaded into rDSMETADATA. At this point Ray could process each field until found what he is looking for which is "CMetaColumn". So he could say if rDSMETADATA<1> = "CMetaColumn" then do something else check rDSMETADATA<2>. FindStr will do this for you. It will tell you the field FMC, the multivalue VMC and the subvalue SMC of where it fould it. Ray knows that the next field is the list of column names. It is multivalued. Notice also Ray assumes that table name is the last part of the key. When you import tables then the key looks like "ODBC\MyDsn\MyTable". He counts "\" and adds 1 to get the third field.

Code: Select all

      lTableName = Field(kDSMETADATA, "\", Count(kDSMETADATA, "\") + 1, 1) 
      lTableShortDesc = Raise(rDSMETADATA<DSR.DICT.DESC>) 

      FindStr "CMetaColumn" In rDSMETADATA, lOccurrence Setting FMC,VMC,SMC 
      Then

This is some very nice code. I hope I simplified it because me and Vincent like it simple. There is a lot going on in this code. What he did is very powerful. I am sure Ray has similar code for DS_JOBOBJECTS which he does not share.

ririr · Post by **ririr** » Fri Aug 11, 2006 8:03 am

Thanks, Kim.

I appreciate you taking time for not only helping with the code but providing a great detailed explanation.

This will help me for sure.

kduke · Post by **kduke** » Fri Aug 11, 2006 3:49 pm

You are welcome.

I have used Universe for many years and I learn something new from Ray's code all the time. I figured others might get lost if they try to figure it out.