Friends,
I'm having trouble with inserting data which has CHAR columns using DataStage.
Due to new project requirements, most of the source columns are been changed to CHAR(XX CHAR) [Previously they were CHAR(XX BYTES)]
The source table has 5 columns:
1) Merch_key --> NUMBER(10)
2) Merch_Name --> CHAR(30 CHAR)
3) Merch_Addr_Line1 --> CHAR(30 CHAR)
4) Merch_Addr_Line2 --> CHAR(30 CHAR)
5) Merch_City --> CHAR(30 CHAR)
Destination table also has the same columns
1) Merch_key --> NUMBER(10)
2) Merch_Name --> CHAR(30 CHAR)
3) Merch_Addr_Line1 --> CHAR(30 CHAR)
4) Merch_Addr_Line2 --> CHAR(30 CHAR)
5) Merch_City --> CHAR(30 CHAR)
The DataStage job is supposed to populate the destination table without any transformations.
The problem is with the DataStage job. For some reason, DataStage is appending 'Spaces' (3 times the column length) at the end and as a result treating the column as CHAR(90 CHAR). When I execute the job all the rows are rejected with 'Inserted value too large, row rejected' error.
The job executes successfully when I 'Trim' the columns in the Transformer. I think this is bad as it has an adverse affect on the performance. Also, there are quite a few existing jobs and Trimming all the CHAR columns would be a cumbersome task.
I was able to insert the records manually into the destination table using SQL PLUS. That makes me think that the problem may be with DataStage.
Is there a setting at the server level which prevents adding spaces to the CHAR columns
Any help would be highly appreciated
Thanks
Yamini
DataStage Metadata issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Research "derivation substitution" to reduce the cumbersome aspect.
I am not aware of any setting that changes the string pad character in server jobs - you could try setting APT_STRING_PADCHAR to 0x00 but I doubt that that would have any effect in server jobs.
I am not aware of any setting that changes the string pad character in server jobs - you could try setting APT_STRING_PADCHAR to 0x00 but I doubt that that would have any effect in server jobs.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.