Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Getting junk characters while unloading using platinum

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Vibha Upadhyay

New User


Joined: 17 Mar 2011
Posts: 8
Location: India

PostPosted: Mon Oct 03, 2011 2:12 pm    Post subject: Getting junk characters while unloading using platinum
Reply with quote

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!
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Oct 03, 2011 2:42 pm    Post subject:
Reply with quote

what are the datatypes of columns Trans_i and Group_i?
Back to top
View user's profile Send private message
Vibha Upadhyay

New User


Joined: 17 Mar 2011
Posts: 8
Location: India

PostPosted: Mon Oct 03, 2011 2:59 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Oct 03, 2011 3:02 pm    Post subject:
Reply with quote

forget my previous post.

from the manual:
Quote:
The result of the function depends on the data type of the arguments:
VARCHAR if the arguments are character strings


and if your reformatting step is only reformatting the INT column,
why not cast that in the SQL (DIGITS)?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1868
Location: UK

PostPosted: Mon Oct 03, 2011 3:04 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 03, 2011 6:49 pm    Post subject:
Reply with quote

cast( REPLACE(DESC_T,'|','~~') as char(100))
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Binary to display characters Roy Ware SYNCSORT 3 Tue Sep 19, 2017 8:46 pm
No new posts replacing junk with hyphen Nitin32 DFSORT/ICETOOL 10 Fri Jun 02, 2017 7:47 pm
No new posts Remove junk values in a file and rite... KP1125 DFSORT/ICETOOL 2 Wed Jan 25, 2017 9:58 pm
No new posts Remove Special Characters from Mainfr... Rodger Zhang All Other Mainframe Topics 6 Wed Jul 06, 2016 1:12 am
No new posts Trasnmitting a file with french chara... spoorni IBM Tools 1 Wed Feb 24, 2016 8:13 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us