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
0 (zero) padding for number datatype field
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA
Hi Srini,
First, you need to convert the numeric values into string type. Then use any combination of the below tranformations.
You can remove the leading zeroes by using the below tranformation for each field.
You can remove the trailing zeroes by using the below tranformation for each field.
alternatively
You can remove the leading and trailing zeroes by using the below tranformation for each field.
That being said, you need to be carefull for the decimal fields, where you might want to retain two digits after the decimal.
In the above case, you need to manually append the required number of zeroes after the decimal point and then convert it back to the appropriate numeric type before writing it to the output.
There are a lot of different ways to tackle your problem, but I just presented a few easy things that came to my mind. Hope this helps you.
First, you need to convert the numeric values into string type. Then use any combination of the below tranformations.
You can remove the leading zeroes by using the below tranformation for each field.
Code: Select all
Trim(InLink.Incol1,"0","L")
Code: Select all
Trim(InLink.Incol1,"0","T")
You can remove the leading and trailing zeroes by using the below tranformation for each field.
Code: Select all
TrimB(Inlink.Incol1)
In the above case, you need to manually append the required number of zeroes after the decimal point and then convert it back to the appropriate numeric type before writing it to the output.
There are a lot of different ways to tackle your problem, but I just presented a few easy things that came to my mind. Hope this helps you.
Thanks,
Naveen
Naveen
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: