About UDB API stage

Nripendra Chand · Post by **Nripendra Chand** » Thu Feb 16, 2006 1:39 am

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

ArndW · Post by **ArndW** » Thu Feb 16, 2006 2:00 am

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]=' '"

ray.wurlod · Post by **ray.wurlod** » Thu Feb 16, 2006 3:34 am

You do not mention whether the column is Char(4) or VarChar(4) in the DB2 table.

Nripendra Chand · Post by **Nripendra Chand** » Mon Feb 20, 2006 1:40 am

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

ArndW · Post by **ArndW** » Mon Feb 20, 2006 2:04 am

Try "0x20" or just a space in that field.

Nripendra Chand · Post by **Nripendra Chand** » Mon Feb 20, 2006 2:36 am

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

ArndW · Post by **ArndW** » Mon Feb 20, 2006 2:45 am

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".

Nripendra Chand · Post by **Nripendra Chand** » Mon Feb 20, 2006 3:05 am

I'm specifying it at pad char option of stage properties in "enterprise udb" stage. In column defaults I'm not getting this as column option.

-Nripendra

ArndW · Post by **ArndW** » Mon Feb 20, 2006 3:35 am

In the Pad Char box put just ONE space

Nripendra Chand · Post by **Nripendra Chand** » Mon Feb 20, 2006 4:05 am

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

ArndW · Post by **ArndW** » Mon Feb 20, 2006 4:22 am

Can you set your $APT_STRING_PADCHAR to " " for this job?

Nripendra Chand · Post by **Nripendra Chand** » Mon Feb 20, 2006 5:49 am

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

ArndW · Post by **ArndW** » Mon Feb 20, 2006 5:59 am

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?

Nripendra Chand · Post by **Nripendra Chand** » Mon Feb 20, 2006 6:08 am

I'm using a transformer already, so i'll pad spaces there only.

-Nripendra

dsusr · Post by **dsusr** » Tue Feb 21, 2006 2:19 am

Nripendra Chand wrote:I'm using a transformer already, so i'll pad spaces there only.

-Nripendra

Hi Nripendra,

Check for the value of $APT_STRING_PADCHAR in environment variables. By default it must be 0x00 so just change it to 0x20 and then just try to tun the job.

Regards,
dsusr