0 (zero) padding for number datatype field
Posted: Thu Sep 08, 2005 7:01 am
Hi All,
Our Environment:
HP Unix 11i (11.23)
Oracle 10g R1 (10.1.0.4)
DataStage 7.5.1A EE
Usually, we read sequential file (ascii, field delimited) as input, do some transform and prepare sequential file (ascii, field delimited) as output, which inturn send to sql loader as input to load into target database.
Noticed that the output file, has padded (leading or trailing or both) with 0 (zero) for the number datatype fields.
The following record is one such example from the output (loader) file :
|2005-06-01 13:03:12|13:03:12|13| |||61433290454|61433499995|
|00000000.00|
|00000000.00|H3GA|B|On-net|MMS|0|||7055|B07F70C2-1DD1-71B2-AC07-8313276B
|0000|UNKNOWN|1|UNKNOWN|UNKNOWN|UNKNOWN|2005-09-07 18:10:55|
|0000000000000000000001135135.0000000000||MMH3|mmh|604800|0|UNKNOWN|UNKN
|OWN|082704134251c208c648|1|UNKNOWN|1|010805153223f82cda1c|3_1754428_|0|
|http://10.176.59.240:10021/mmsc?AE-0w0RfpLt_aM0Dv|
|0000000000000000000000288237.0000000000|
|0000000000000000000000444033.0000000000|
|0000000000000000000000887008.0000000000|
|0000000000000000000000420053.0000000000|
|0000000000000000000000575789.0000000000|30011221|518425|0|61433499995|6
|1433290454|H3GMMSC_MMSC_IN|1|en|AE-0w0RfpLt_aM0Dv|1|1|1|1|1|1|0|?|csd|0
||unconfirmed|0|en|0|0|0|normal|personal|?|1|0|37|0|
|0000000000000000000020050618.0000000000|0|1011|
|0000000000000000000000000000.0000000000|
|0000000000000000000000005465.0000000000|
|0000000000000000000000005465.0000000000
Value in output file / Actual Value
======================================
0000000000000000000001135135.0000000000 / 1135135
00000000.00 / 0
0000000000000000000000288237.0000000000 / 288237
Due to this, the loader file size is increased considerably.
Please share with us your thoughts, if you have experienced such scenario, to fix this to produce output file without leading or trailing zeros.
Fixing this, potentially could improve our DataStage job performance as the number of writes to the disk reduces considerably.
Thanks for your reading, awaiting for your response.
Srini
Our Environment:
HP Unix 11i (11.23)
Oracle 10g R1 (10.1.0.4)
DataStage 7.5.1A EE
Usually, we read sequential file (ascii, field delimited) as input, do some transform and prepare sequential file (ascii, field delimited) as output, which inturn send to sql loader as input to load into target database.
Noticed that the output file, has padded (leading or trailing or both) with 0 (zero) for the number datatype fields.
The following record is one such example from the output (loader) file :
|2005-06-01 13:03:12|13:03:12|13| |||61433290454|61433499995|
|00000000.00|
|00000000.00|H3GA|B|On-net|MMS|0|||7055|B07F70C2-1DD1-71B2-AC07-8313276B
|0000|UNKNOWN|1|UNKNOWN|UNKNOWN|UNKNOWN|2005-09-07 18:10:55|
|0000000000000000000001135135.0000000000||MMH3|mmh|604800|0|UNKNOWN|UNKN
|OWN|082704134251c208c648|1|UNKNOWN|1|010805153223f82cda1c|3_1754428_|0|
|http://10.176.59.240:10021/mmsc?AE-0w0RfpLt_aM0Dv|
|0000000000000000000000288237.0000000000|
|0000000000000000000000444033.0000000000|
|0000000000000000000000887008.0000000000|
|0000000000000000000000420053.0000000000|
|0000000000000000000000575789.0000000000|30011221|518425|0|61433499995|6
|1433290454|H3GMMSC_MMSC_IN|1|en|AE-0w0RfpLt_aM0Dv|1|1|1|1|1|1|0|?|csd|0
||unconfirmed|0|en|0|0|0|normal|personal|?|1|0|37|0|
|0000000000000000000020050618.0000000000|0|1011|
|0000000000000000000000000000.0000000000|
|0000000000000000000000005465.0000000000|
|0000000000000000000000005465.0000000000
Value in output file / Actual Value
======================================
0000000000000000000001135135.0000000000 / 1135135
00000000.00 / 0
0000000000000000000000288237.0000000000 / 288237
Due to this, the loader file size is increased considerably.
Please share with us your thoughts, if you have experienced such scenario, to fix this to produce output file without leading or trailing zeros.
Fixing this, potentially could improve our DataStage job performance as the number of writes to the disk reduces considerably.
Thanks for your reading, awaiting for your response.
Srini