Getting junk characters while unloading using platinum

Vibha Upadhyay · New User Joined: 17 Mar 2011 Posts: 8 Location: India

Hi all,

I am using the below query in my JCL which unloads data from a DB2 table using Platinum utility. The query runs fine when i run it using spufi etc but when i use the same query along with platinum utility i get junk character values prefixed in the "Desc_t" column which is a character field of length 80. The requirement is to replace any pipe character which appears inside the description field with '~~' as we are using pipe as the delimiter to differentiate between different fields.

SELECT
TRANS_I, GROUP_I,
REPLACE(DESC_T,'|','~~')
FROM DB2PROD.TRANS_GROUP

The output should have been:
001ACSTRATEGIC ALIGN

but it is like below:
001AC000&STRATEGIC ALIGN

Here - 01 and AC are the valuse in the first 2 columns which are displayed correctly. the problem occurs in the third field where i am using the replace command. Any suggestions to the above problem are appreciated.

Thanks!

dbzTHEdinosauer · Posted: Mon Oct 03, 2011 2:42 pm

what are the datatypes of columns Trans_i and Group_i?

Vibha Upadhyay · New User Joined: 17 Mar 2011 Posts: 8 Location: India

Trans_i is SMALLINT which i am later (after the unload job) formatting using a jcl to character readable field of length 3. The '001' which i have mentioned above is the field after formatting. The hexadecimal equivalent of the same is 00
01

Group_I is character field of 2 bytes.

dbzTHEdinosauer · Posted: Mon Oct 03, 2011 3:02 pm

forget my previous post.

from the manual:

Nic Clouston · Posted: Mon Oct 03, 2011 3:04 pm

I sincerely doubt that they are "junk" characters - probably just the data you want but in a format that you did not expect. So post some samples.

GuyC · Posted: Mon Oct 03, 2011 6:49 pm

cast( REPLACE(DESC_T,'|','~~') as char(100))