DSXchange

A short text to describe your forum
http://dsxchange.com./

DS_METADATA

http://dsxchange.com./viewtopic.php?f=24&t=100989

Page 1 of 1

DS_METADATA

Posted: Tue May 30, 2006 6:05 am
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.

Re: DS_METADATA

Posted: Tue May 30, 2006 6:12 am
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.................

Posted: Tue May 30, 2006 6:18 am
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.

Posted: Tue May 30, 2006 6:25 am
by kumar_s
Perhaps you can try with the key @ID

Code: Select all

SELECT * FROM DS_METADATA WHERE @ID LIKE "%OCI%"

hi

Posted: Tue May 30, 2006 6:31 am
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

Posted: Tue May 30, 2006 6:38 am
by kumar_s
Others were more familiare with Kim's routines and scripts that Kim do :lol:

Posted: Tue May 30, 2006 7:00 am
by ray.wurlod
Kim's routines were - I believe - based on my originals to which he referred.

Posted: Tue May 30, 2006 10:46 pm
by kumar_s
So again here comes Ray :wink:

Posted: Tue May 30, 2006 11:17 pm
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.
All times are UTC-06:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited