About UDB API stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
About UDB API stage
Hi,
I'm populating one db2 table using API UDB stage. There is a column of 4 byte but only first three bytes have the value and the last byte is space. In the next job I'm doing a lookup using the same column in the same db2 table.
If I load data in the first job using enterprise UDB stage. I find the matches in the next job. But in case of API stage I'm not finding the match. When I view data, data looks fine in both cases.
Is there any issue with API UDB stage? Why I'm not finding any matches if I load data using API UDB stage.
-Nripendra
I'm populating one db2 table using API UDB stage. There is a column of 4 byte but only first three bytes have the value and the last byte is space. In the next job I'm doing a lookup using the same column in the same db2 table.
If I load data in the first job using enterprise UDB stage. I find the matches in the next job. But in case of API stage I'm not finding the match. When I view data, data looks fine in both cases.
Is there any issue with API UDB stage? Why I'm not finding any matches if I load data using API UDB stage.
-Nripendra
Have you looked at the data to see if the trailing spaces has been removed? You could do a quick DS job to read it and see if the LEN is 3 or 4 (if it's a VarChar) or a check of "In.ColumnName[4,1]=' '"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
The column is char(4) at db2. I found that while loading data through enterprise db2 stage the data is getting padded with nulls because the default pad char is null. but when i load data through API stage then it is padding all the data with spaces.
So while performing lookup I'm using RTRIM fn and now lookup is working properly.
Now If I want to pad spaces in enterprise stage then I need to spcify hex value at pad char option. hex value for spaces is 20. I specified it but it gave me following msg:
"The value of the -padchar option must be a null,
a single-char string, or a hexdecimal number; got: `0020'."
What value should i give at pad char option?
-Nripendra
So while performing lookup I'm using RTRIM fn and now lookup is working properly.
Now If I want to pad spaces in enterprise stage then I need to spcify hex value at pad char option. hex value for spaces is 20. I specified it but it gave me following msg:
"The value of the -padchar option must be a null,
a single-char string, or a hexdecimal number; got: `0020'."
What value should i give at pad char option?
-Nripendra
Try "0x20" or just a space in that field.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
I put 0x20 and job ran fine. But when I checked db2 table after the completion of job, it didn't put spaces. Still it is putting null. pad char is not working.
When I put spaces, it gave me the same error msg saying that the value of the -padchar option must be a null, a single-char string, or a hexdecimal number.
-Nripendra
When I put spaces, it gave me the same error msg saying that the value of the -padchar option must be a null, a single-char string, or a hexdecimal number.
-Nripendra
Where are you specifying this- I would put it in the column defaults, you get to them by right-mouse-click on the column name and then "edit row".
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
In the Pad Char box put just ONE space
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
I kept one space there (in enterprise stage), job ran fine but still problem is same. All the records going through API UDB stage are having spaces at their end but records going through Enterprise UDB stage don't have spaces at their end. Despite of putting padchar as space explicitly it is still putting nulls at the end of each column. Is their anything else which i need to define?
-Nripendra
-Nripendra
Can you set your $APT_STRING_PADCHAR to " " for this job?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
I tried this option as well. But still enterprise udb stage is not padding spaces. So I'm thinking to pad spaces in datasets itself and then load data in db2 tables from this dataset. Please tell me how to pad spaces in datasets. Do I need to add the $APT_STRING_PADCHAR as parameter or any other option is there because padchar property is not there in datasets.
-Nripendra
-Nripendra
DataStage will only pad if it needs to. If your CHAR column is defined as length 4 and your database is a CHAR(4) column then the settings for padding won't apply. You will need to do this at the source, or at the point in your job where you are going from a char(3) to a char(4) column type. Where is that? Perhaps in the source stage or in a transform or modify?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)