Page 1 of 1
Using Table Definition of mainframe in Seq files
Posted: Tue Oct 02, 2007 1:12 pm
by landaghaar
I am reading from a mainframe file and write it to a sequencial file. it is a one to one mapping without any transformation.
I have the copy book for mainframe file, when i use the same table definition for the target file i run into problems for number fields.
the question is, is there a way to make a copy of the table definiation and make it sequencial file compatible? currently the layout shows number fields as BINARY.
Or should I retype all the column names, their type and size?
Posted: Tue Oct 02, 2007 2:22 pm
by ray.wurlod
what stage type are you using to read the mainframe file? If it's the CFF stage, you can specify to convert the COMP fields into appropriate data types (e.g. integer or decimal). When these are written (exported) to a sequential file, they are converted by the export operator to text.
Posted: Tue Oct 02, 2007 2:38 pm
by landaghaar
I am reading from a mainframe file using a CFF stage. then i write the fields to a flat file.
then i read the flat file for other purposes. The problem is when i open that flat file with the same table definition as the mainframe, i have hard time reading integers, i have to read them as string then convert, but again when i write, it doesnt write as integer.
If i dont use the table definition, and create all the columns in the target, then it works. but it is a very boring job and it is very dumb of DS if it cant change the definitions.
Posted: Tue Oct 02, 2007 2:40 pm
by landaghaar
Here is the export of the definition, see they are marked as binary, i dont want that, i want to copy the definition and make another one with standard types.
BEGIN HEADER
CharacterSet "ENGLISH"
ExportingTool "Ascential DataStage Export"
ToolVersion "4"
ServerName "S3W04206"
ToolInstanceID "DEV_DW00EDW_EE"
MDISVersion "1.0"
Date "2007-10-02"
Time "14.16.42"
ServerVersion "7.5x2"
END HEADER
BEGIN DSTABLEDEFS
BEGIN DSRECORD
Identifier "ETI_Migration\\LDKE6H-PROOF-BATCH\\BTCH"
DateModified "2007-10-02"
TimeModified "14.14.19"
OLEType "CMetaTable"
Readonly "0"
Version "8"
QuoteChar "000"
Multivalued "0"
SPErrorCodes ";"
Columns "CMetaColumn"
BEGIN DSSUBRECORD
Name "AST_ID"
SqlType "1"
Precision "8"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "CHARACTER"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "TR_NO"
SqlType "4"
Precision "8"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "BINARY"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "binary, big_endian, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "BTCH_CREAT_DT"
SqlType "1"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "CHARACTER"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "BTCH_ID"
SqlType "4"
Precision "4"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "BINARY"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "binary, big_endian, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "FI_NO"
SqlType "4"
Precision "8"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "BINARY"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "binary, big_endian, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "BTCH_CREAT_TM"
SqlType "1"
Precision "8"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "CHARACTER"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "BTCH_TYP"
SqlType "4"
Precision "4"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "BINARY"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "binary, big_endian, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "STS"
SqlType "4"
Precision "4"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "BINARY"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "binary, big_endian, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "TOT_AMT"
SqlType "3"
Precision "18"
Scale "2"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "DECIMAL"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "packed, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "PRFS_NO"
SqlType "4"
Precision "4"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "BINARY"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "binary, big_endian, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "TR_EFF_DT"
SqlType "1"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "CHARACTER"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "EMP_ID"
SqlType "3"
Precision "15"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "DECIMAL"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
APTFieldProp "packed, default=0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "SNAP_DT"
SqlType "1"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
LevelNo "0"
NativeType "CHARACTER"
Occurs "0"
SignOption "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
END DSSUBRECORD
SEQ-Delimiter ","
SEQ-QuoteChar "\""
SEQ-ColHeaders "0"
SEQ-FixedWidth "0"
SEQ-ColSpace "0"
SEQ-OmitNewLine "0"
AllowColumnMapping "0"
Locator "TableType=\"COBOL FD\"|Computer=\"Mainframe\"|SoftwareProduct=\"FileSystem\"|DataStore=\"ETI_Migration\\LDKE6H-PROOF-BATCH\"|DataSchema=\"V:\\ETI Migration\\LDKE6H-PROOF-BATCH\\LDKE6H-PROOF-BATCHpopulate1.cpy\"|DataCollection=\"BTCH\""
ImportLocation "V:\\ETI Migration\\LDKE6H-PROOF-BATCH\\LDKE6H-PROOF-BATCHpopulate1.cpy"
PadChar ""
APTRecordProp "record_format={type=implicit}, delim=none, ebcdic, binary, fix_zero"
END DSRECORD
END DSTABLEDEFS
Posted: Tue Oct 02, 2007 3:32 pm
by ArndW
Have you tried replace the "BINARY" with "CHARACTER" in your .dsx file and then importing the table definitions?
Posted: Wed Oct 03, 2007 9:29 am
by landaghaar
Yes, but same results. It does not change the mainframe type to standard reqular type.