Code: Select all
* -----------------------------------------------------------------
* KgdEstimateDiskSpace(DsMetadataId, OracleStyle, RowCount)
* Decription: Estimate disk space from DS_METADATA.
* Written by: Kim Duke
* Notes:
* -----------------------------------------------------------------
* $INCLUDE DSINCLUDE JOBCONTROL.H
Ans = ''
RoutineName = "KgdEstimateDiskSpace"
Usage = RoutineName:'(DsMetadataId, OracleStyle, RowCount)'
* -----------------------------------------------------------------
* initialize variables
* -----------------------------------------------------------------
True = 1
False = 0
Other = True
Forever = True
Today = oconv(@DATE, "D4-")
ErrMsg = ''
NullRec = ''
CrLf = char(13):char(10)
DsMetadataSep = '\'
DebugMode = True
* -----------------------------------------------------------------
FileName = 'DS_METADATA'
open FileName to FilePtr else
ErrMsg = 'Can not open ':FileName:' file.'
goto ErrRtn
end
DsMetadataPtr = FilePtr
* -----------------------------------------------------------------
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))
DiskSpace = 0
* 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>
Factor = 1
Begin case
case SQLDataTypeCode = -7
SQLType = "BIT"
OracleType = "VARCHAR2"
case SQLDataTypeCode = -6
SQLType = "TINYINT"
OracleType = "NUMBER"
case SQLDataTypeCode = -5
SQLType = "BIGINT"
OracleType = "NUMBER"
case SQLDataTypeCode = -4
SQLType = "LONGVARBINARY"
OracleType = "VARCHAR2"
case SQLDataTypeCode = -3
SQLType = "VARBINARY"
OracleType = "VARCHAR2"
case SQLDataTypeCode = -2
SQLType = "BINARY"
OracleType = "VARCHAR2"
case SQLDataTypeCode = -1
SQLType = "LONGVARCHAR"
OracleType = "VARCHAR2"
case SQLDataTypeCode = 0
SQLType = "VARCHAR"
OracleType = "VARCHAR2"
Factor = .8
case SQLDataTypeCode = 1
SQLType = "CHAR"
OracleType = "CHAR"
case SQLDataTypeCode = 2
SQLType = "NUMERIC"
OracleType = "NUMBER"
case SQLDataTypeCode = 3
SQLType = "NUMERIC"
OracleType = "NUMBER"
case SQLDataTypeCode = 4
SQLType = "INTEGER"
OracleType = "NUMBER"
case SQLDataTypeCode = 5
SQLType = "SMALLINT"
OracleType = "NUMBER"
case SQLDataTypeCode = 6
SQLType = "FLOAT"
OracleType = "NUMBER"
case SQLDataTypeCode = 7
SQLType = "REAL"
OracleType = "NUMBER"
case SQLDataTypeCode = 8
SQLType = "DOUBLE"
OracleType = "NUMBER"
case SQLDataTypeCode = 9
SQLType = "DATE"
OracleType = "DATE"
case SQLDataTypeCode = 10
SQLType = "TIME"
OracleType = "DATE"
case SQLDataTypeCode = 11
SQLType = "TIMESTAMP"
OracleType = "DATE"
case SQLDataTypeCode = 12
SQLType = "VARCHAR"
OracleType = "VARCHAR2"
Factor = .8
case True
SQLType = "VARCHAR"
OracleType = "VARCHAR2"
end case
if OracleStyle then
If OracleType = "NUMBER" Then
Factor = .25
If Precision = @NULL Then
DataType = OracleType
End Else
If Scale = @NULL Then
DataType = OracleType : "(" : Precision : ")"
End Else
DataType = OracleType : "(" : Precision : "," : Scale : ")"
End
End
End Else
if OracleType = "DATE" then
DataType = OracleType
Factor = .5
end else
DataType = OracleType : "(" : Precision : ")"
end
End
end else
If SQLType = "NUMERIC" Then
Factor = 1
If Precision = @NULL Then
DataType = SQLType
End Else
If Scale = @NULL Then
DataType = SQLType : "(" : Precision : ")"
End Else
DataType = SQLType : "(" : Precision : "," : Scale : ")"
End
End
End Else
if SQLType = "DATE" or SQLType = "TIME" or SQLType = "TIMESTAMP" then
DataType = SQLType
Factor = 1
end else
DataType = SQLType : "(" : Precision : ")"
end
End
end
if DebugMode then print 'DataType=':DataType
if OracleStyle = 2 then
Factor = 1
end
DiskSpace += Factor * Precision * RowCount
next MvNo
Ans = DiskSpace "20R,"
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
Forgive my ignorance, can you give an example of how to use this script. The language looks a lot like perl code, I'm just wondering how I would invoke this script to estimate disk space usage. I'm running DataStage 7.1r1 on AIX 5.2.