Writing table metadata details
Moderators: chulett, rschirm, roy
Writing table metadata details
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.
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.
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:
The column names are in field 12. To create a dictionary for field 12:
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.
You do this in a UV stage and output it to table of your choice.
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
Code: Select all
INSERT INTO DICT DS_METADATA
(
FIELD,
CODE,
EXP,
NAME,
FORMAT,
SM,
ASSOC
) VALUES (
'ColNames',
'I',
'@RECORD<12>',
'ColNames',
'64L',
'M',
'MvCols'
)
;
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
;
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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 "
"
Equate LF To Char(13) ; Equate XMLLF To "
"
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
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:
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
I tested writing a job with the following stages:
Code: Select all
[Sequential File]->[XML INPUT]->[Transformer]->[Sequential 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
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
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.
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:
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.
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.
Code: Select all
Command = "SELECT DS_METADATA WITH DSRID LIKE " : Quote("0X'" : pTableDefinition : "'0X") : " TO 10"
Call DSExecute("UV", Command, Output, Code)
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
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
Mamu Kim