Packed column from DB2/400
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Packed column from DB2/400
Hi All,
One of the data sources in my project is DB2 tables in AS/400 system.
The data structure of the table is as follows:
Col A,Col B,Col C,Col D
1234,ab2f,pazz,"&Col E=ZZ&Col F= &Col G=5478&Col H=100.00
2345,cd3f,ddzz,"&Col F=IN&Col G=9801&Col H=250.25&Col J=20060630
The Problems in reading Col D are
1. The record starts with the Quote Character but doesn't end with it.
2. The '&' seperated columns are not following a uniform pattern i.e., it varies from record to record (some start with Col E and some with Col X etc).
3. This field contains around 300 '&' seperated columns in all possible combinations and we have 1 million records per monthly extract.
Is there anyway to read this data using the available stages in Datastage?
It would be of great help if this issue is resolved.
Thanks in advance...
One of the data sources in my project is DB2 tables in AS/400 system.
The data structure of the table is as follows:
Col A,Col B,Col C,Col D
1234,ab2f,pazz,"&Col E=ZZ&Col F= &Col G=5478&Col H=100.00
2345,cd3f,ddzz,"&Col F=IN&Col G=9801&Col H=250.25&Col J=20060630
The Problems in reading Col D are
1. The record starts with the Quote Character but doesn't end with it.
2. The '&' seperated columns are not following a uniform pattern i.e., it varies from record to record (some start with Col E and some with Col X etc).
3. This field contains around 300 '&' seperated columns in all possible combinations and we have 1 million records per monthly extract.
Is there anyway to read this data using the available stages in Datastage?
It would be of great help if this issue is resolved.
Thanks in advance...
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Hi Priyadarshi,
I seperated the Col D into a sequential file and tried to read from that file.
Since the record starts with different columns the mapping with the target variable is not possible.
Also trying to fix a metadata to this long string fails because the columns doesn't follow any fixed pattern.
I seperated the Col D into a sequential file and tried to read from that file.
Since the record starts with different columns the mapping with the target variable is not possible.
Also trying to fix a metadata to this long string fails because the columns doesn't follow any fixed pattern.
Read the entire string as a single varchar(1000) in sequntial file mode
Map your fields in a transformer:
ColE : if index(input.col,'COL E=',1) > 1 then field(input.col[index(input.col,'COL E=',1)+7,99],'&',1) else ''
Map your fields in a transformer:
ColE : if index(input.col,'COL E=',1) > 1 then field(input.col[index(input.col,'COL E=',1)+7,99],'&',1) else ''
3NF: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. So help me Codd.
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Hi Craig,
I tried to sample with one stage variable which is Col_E.
The derivation for Col_E is "Col_E = Field("DSLink5.Col_D","&","2","2")".
The job ran fine but the output was filled only with zeros except for the first record which had the value 1. The actual value of Col_E in the sring is "zz".
Please correct me on what needs to be modified to get the desired output.
Thanks,
Tony
I tried to sample with one stage variable which is Col_E.
The derivation for Col_E is "Col_E = Field("DSLink5.Col_D","&","2","2")".
The job ran fine but the output was filled only with zeros except for the first record which had the value 1. The actual value of Col_E in the sring is "zz".
Please correct me on what needs to be modified to get the desired output.
Thanks,
Tony
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata