DS_METADATA

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sunil_acc
Participant
Posts: 28
Joined: Thu Oct 20, 2005 5:49 am

DS_METADATA

Post by sunil_acc »

HI,
How can i access the DS_METADATA file present in my project folder.
Besides that can anybody help me how can we write the metadata of a imported definition to a sequential file.
Top
sunil_acc
Participant
Posts: 28
Joined: Thu Oct 20, 2005 5:49 am

Re: DS_METADATA

Post by sunil_acc »

sunil_acc wrote:HI,
How can i access the DS_METADATA file present in my project folder.
Besides that can anybody help me how can we write the metadata of a imported definition to a sequential file.

I am able to access hash file now but still i cant locate the metadata of the elements of the definations i imported (say for example emp table in OCI 9 stage). I want to write the column names and there metadata in a seq file.................
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

Ray wrote a routine to export these records to a XML file. The key to this hashed file look like ODBC\DSN\TABLENAME. I am not certain how the OCI keys look but you can import them to any key you want.
Mamu Kim
Top
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

Perhaps you can try with the key @ID

Code: Select all

SELECT * FROM DS_METADATA WHERE @ID LIKE "%OCI%"
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Top
abhishekmuradia
Participant
Posts: 6
Joined: Tue May 23, 2006 10:25 pm
Location: Bangalore

hi

Post by abhishekmuradia »

Hi Sunil
This routine might help you
I haven't tested it yet
* -----------------------------------------------------------------
* KgdCreateBcpMetadata(DsMetadataId)
* Decription: Create Bcp file from records in DS_METADATA.
* Written by: Kim Duke
* Notes:
* -----------------------------------------------------------------
* $INCLUDE DSINCLUDE JOBCONTROL.H
Ans = ''
RoutineName = "KgdCreateBcpMetadata"
Usage = RoutineName:'(DsMetadataId)'
* -----------------------------------------------------------------
* initialize variables
* -----------------------------------------------------------------
True = 1
False = 0
Other = True
Forever = True
Today = oconv(@DATE, "D4-")
ErrMsg = ''
NullRec = ''
CrLf = char(13):char(10)
DsMetadataSep = '\'
DebugMode = False
* -----------------------------------------------------------------
FileName = 'DS_METADATA'
open FileName to FilePtr else
ErrMsg = 'Can not open ':FileName:' file.'
goto ErrRtn
end
DsMetadataPtr = FilePtr
* -----------------------------------------------------------------
GenRec = ''
PKeys = ''
* -----------------------------------------------------------------
read DsMetadataRec from DsMetadataPtr, DsMetadataId then
Occurrence = 1
* -----------------------------------------------------------------
FindColumns:
FindStr "CMetaColumn" In DsMetadataRec, Occurrence Setting AttrNo, MvNo, SvNo Then
* -----------------------------------------------------------------
* number of column definitions in collection
* -----------------------------------------------------------------
NoOfFields = Field(DsMetadataRec<AttrNo>, "/", 2, 1)
If NoOfFields = 0 Then
* Stored procedures use the second columns collection in the record.
* Go get the next one.
Occurrence += 1
GoTo FindColumns
End
TableName = field(DsMetadataId, DsMetadataSep, dcount(DsMetadataId, DsMetadataSep))
GenRec<-1> = '8.0'
GenRec<-1> = NoOfFields
Indent = spaces(3*1)
* NoOfFields = dcount(DsMetadataRec<AttrNo+1>,
for MvNo=1 to NoOfFields
ColumnName = DsMetadataRec<AttrNo+1, MvNo>
if DebugMode then print 'ColumnName=':ColumnName
ColumnDescription = DsMetadataRec<AttrNo+2, MvNo>
DataElement = DsMetadataRec<AttrNo+3, MvNo>
if DebugMode then print 'DataElement=':DataElement
SQLDataTypeCode = DsMetadataRec<AttrNo+4, MvNo>
if DebugMode then print 'SQLDataTypeCode=':SQLDataTypeCode
Precision = DsMetadataRec<AttrNo+5, MvNo>
Scale = DsMetadataRec<AttrNo+6, MvNo>
IsNullable = DsMetadataRec<AttrNo+7, MvNo>
if DebugMode then print 'IsNullable=':IsNullable
KeyPosition = DsMetadataRec<AttrNo+8, MvNo>
DisplayWidth = DsMetadataRec<AttrNo+9, MvNo>
Begin case
case SQLDataTypeCode = -7
SQLType = "BIT"
case SQLDataTypeCode = -6
SQLType = "TINYINT"
case SQLDataTypeCode = -5
SQLType = "BIGINT"
case SQLDataTypeCode = -4
SQLType = "LONGVARBINARY"
case SQLDataTypeCode = -3
SQLType = "VARBINARY"
case SQLDataTypeCode = -2
SQLType = "BINARY"
case SQLDataTypeCode = -1
SQLType = "LONGVARCHAR"
case SQLDataTypeCode = 0
SQLType = "VARCHAR"
case SQLDataTypeCode = 1
SQLType = "CHAR"
case SQLDataTypeCode = 2
SQLType = "NUMERIC"
case SQLDataTypeCode = 3
SQLType = "NUMERIC"
case SQLDataTypeCode = 4
SQLType = "INTEGER"
case SQLDataTypeCode = 5
SQLType = "SMALLINT"
case SQLDataTypeCode = 6
SQLType = "FLOAT"
case SQLDataTypeCode = 7
SQLType = "REAL"
case SQLDataTypeCode = 8
SQLType = "DOUBLE"
case SQLDataTypeCode = 9
SQLType = "DATE"
case SQLDataTypeCode = 10
SQLType = "TIME"
case SQLDataTypeCode = 11
SQLType = "TIMESTAMP"
case SQLDataTypeCode = 12
SQLType = "VARCHAR"
case True
SQLType = "VARCHAR"
end case
If SQLType = "NUMERIC" Then
If Precision = @NULL Then
DataType = SQLType
GenRec<-1> = MvNo:' SQL':SQLType:' "" 22 "" ':MvNo:' ':ColumnName:' ""'
End Else
If Scale = @NULL Then
DataType = SQLType : "(" : Precision : ")"
GenRec<-1> = MvNo:' SQL':SQLType:' "" ':Precision:' "" ':MvNo:' ':ColumnName:' ""'
End Else
DataType = SQLType : "(" : Precision : "," : Scale : ")"
GenRec<-1> = MvNo:' SQL':SQLType:' "" ':Precision:' "" ':MvNo:' ':ColumnName:' ""'
End
End
End Else
if SQLType = "DATE" or SQLType = "TIME" or SQLType = "TIMESTAMP" then
DataType = SQLType
GenRec<-1> = MvNo:' SQL':SQLType:' 1 8 "" ':MvNo:' ':ColumnName:' ""'
end else
DataType = SQLType : "(" : Precision : ")"
GenRec<-1> = MvNo:' SQL':SQLType:' 0 ':Precision:' "" ':MvNo:' ':ColumnName:' SQL_Latin1_General_CP1_CI_AS'
end
End
if DebugMode then print 'DataType=':DataType
next MvNo

if DebugMode then print
NoOfLines = dcount(GenRec, @FM)
for i=1 to NoOfLines
if DebugMode then print GenRec<i>
next i

Ans = GenRec
end else
ErrMsg = 'Could not find CMetaColumn in ':DsMetadataId
goto ErrRtn
end
end else
ErrMsg = 'Could not find ':DsMetadataId:' in DS_METADATA.'
goto ErrRtn
end

goto TheEnd
* -----------------------------------------------------------------
ErrRtn:
if DebugMode then print
if DebugMode then print ErrMsg
if DebugMode then print
Ans = ErrMsg
* -----------------------------------------------------------------
TheEnd:
if DebugMode then print
if DebugMode then print
if DebugMode then print Ans
if DebugMode then print

























Another one

* -----------------------------------------------------------------
* KgdCreateBcpMetadata(DsMetadataId)
* Decription: Create Bcp file from records in DS_METADATA.
* Written by: Kim Duke
* Notes:
* -----------------------------------------------------------------
* $INCLUDE DSINCLUDE JOBCONTROL.H
Ans = ''
RoutineName = "KgdCreateBcpMetadata"
Usage = RoutineName:'(DsMetadataId)'
* -----------------------------------------------------------------
* initialize variables
* -----------------------------------------------------------------
True = 1
False = 0
Other = True
Forever = True
Today = oconv(@DATE, "D4-")
ErrMsg = ''
NullRec = ''
CrLf = char(13):char(10)
DsMetadataSep = '\'
DebugMode = False
* -----------------------------------------------------------------
FileName = 'DS_METADATA'
open FileName to FilePtr else
ErrMsg = 'Can not open ':FileName:' file.'
goto ErrRtn
end
DsMetadataPtr = FilePtr
* -----------------------------------------------------------------
GenRec = ''
PKeys = ''
* -----------------------------------------------------------------
read DsMetadataRec from DsMetadataPtr, DsMetadataId then
Occurrence = 1
* -----------------------------------------------------------------
FindColumns:
FindStr "CMetaColumn" In DsMetadataRec, Occurrence Setting AttrNo, MvNo, SvNo Then
* -----------------------------------------------------------------
* number of column definitions in collection
* -----------------------------------------------------------------
NoOfFields = Field(DsMetadataRec<AttrNo>, "/", 2, 1)
If NoOfFields = 0 Then
* Stored procedures use the second columns collection in the record.
* Go get the next one.
Occurrence += 1
GoTo FindColumns
End
TableName = field(DsMetadataId, DsMetadataSep, dcount(DsMetadataId, DsMetadataSep))
GenRec<-1> = '8.0'
GenRec<-1> = NoOfFields
Indent = spaces(3*1)
* NoOfFields = dcount(DsMetadataRec<AttrNo+1>,
for MvNo=1 to NoOfFields
ColumnName = DsMetadataRec<AttrNo+1, MvNo>
if DebugMode then print 'ColumnName=':ColumnName
ColumnDescription = DsMetadataRec<AttrNo+2, MvNo>
DataElement = DsMetadataRec<AttrNo+3, MvNo>
if DebugMode then print 'DataElement=':DataElement
SQLDataTypeCode = DsMetadataRec<AttrNo+4, MvNo>
if DebugMode then print 'SQLDataTypeCode=':SQLDataTypeCode
Precision = DsMetadataRec<AttrNo+5, MvNo>
Scale = DsMetadataRec<AttrNo+6, MvNo>
IsNullable = DsMetadataRec<AttrNo+7, MvNo>
if DebugMode then print 'IsNullable=':IsNullable
KeyPosition = DsMetadataRec<AttrNo+8, MvNo>
DisplayWidth = DsMetadataRec<AttrNo+9, MvNo>
Begin case
case SQLDataTypeCode = -7
SQLType = "BIT"
case SQLDataTypeCode = -6
SQLType = "TINYINT"
case SQLDataTypeCode = -5
SQLType = "BIGINT"
case SQLDataTypeCode = -4
SQLType = "LONGVARBINARY"
case SQLDataTypeCode = -3
SQLType = "VARBINARY"
case SQLDataTypeCode = -2
SQLType = "BINARY"
case SQLDataTypeCode = -1
SQLType = "LONGVARCHAR"
case SQLDataTypeCode = 0
SQLType = "VARCHAR"
case SQLDataTypeCode = 1
SQLType = "CHAR"
case SQLDataTypeCode = 2
SQLType = "NUMERIC"
case SQLDataTypeCode = 3
SQLType = "NUMERIC"
case SQLDataTypeCode = 4
SQLType = "INTEGER"
case SQLDataTypeCode = 5
SQLType = "SMALLINT"
case SQLDataTypeCode = 6
SQLType = "FLOAT"
case SQLDataTypeCode = 7
SQLType = "REAL"
case SQLDataTypeCode = 8
SQLType = "DOUBLE"
case SQLDataTypeCode = 9
SQLType = "DATE"
case SQLDataTypeCode = 10
SQLType = "TIME"
case SQLDataTypeCode = 11
SQLType = "TIMESTAMP"
case SQLDataTypeCode = 12
SQLType = "VARCHAR"
case True
SQLType = "VARCHAR"
end case
If SQLType = "NUMERIC" Then
If Precision = @NULL Then
DataType = SQLType
GenRec<-1> = MvNo:' SQL':SQLType:' "" 22 "" ':MvNo:' ':ColumnName:' ""'
End Else
If Scale = @NULL Then
DataType = SQLType : "(" : Precision : ")"
GenRec<-1> = MvNo:' SQL':SQLType:' "" ':Precision:' "" ':MvNo:' ':ColumnName:' ""'
End Else
DataType = SQLType : "(" : Precision : "," : Scale : ")"
GenRec<-1> = MvNo:' SQL':SQLType:' "" ':Precision:' "" ':MvNo:' ':ColumnName:' ""'
End
End
End Else
if SQLType = "DATE" or SQLType = "TIME" or SQLType = "TIMESTAMP" then
DataType = SQLType
GenRec<-1> = MvNo:' SQL':SQLType:' 1 8 "" ':MvNo:' ':ColumnName:' ""'
end else
DataType = SQLType : "(" : Precision : ")"
GenRec<-1> = MvNo:' SQL':SQLType:' 0 ':Precision:' "" ':MvNo:' ':ColumnName:' SQL_Latin1_General_CP1_CI_AS'
end
End
if DebugMode then print 'DataType=':DataType
next MvNo

if DebugMode then print
NoOfLines = dcount(GenRec, @FM)
for i=1 to NoOfLines
if DebugMode then print GenRec<i>
next i

Ans = GenRec
end else
ErrMsg = 'Could not find CMetaColumn in ':DsMetadataId
goto ErrRtn
end
end else
ErrMsg = 'Could not find ':DsMetadataId:' in DS_METADATA.'
goto ErrRtn
end

goto TheEnd
* -----------------------------------------------------------------
ErrRtn:
if DebugMode then print
if DebugMode then print ErrMsg
if DebugMode then print
Ans = ErrMsg
* -----------------------------------------------------------------
TheEnd:
if DebugMode then print
if DebugMode then print
if DebugMode then print Ans
if DebugMode then print
Top
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

Others were more familiare with Kim's routines and scripts that Kim do :lol:
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Kim's routines were - I believe - based on my originals to which he referred.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

So again here comes Ray :wink:
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

Ray is correct. I modified Ray's routine to create DDL and get row length from knowing how DS_METADATA works. He hacked this one, not me.
Mamu Kim
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”