DB2 Replace Query retrieve junk character



IBM's flagship relational database management system

DB2 Replace Query retrieve junk character

Postby trilochan_p » Mon Apr 14, 2014 11:20 pm

Hi,

I have used REPLACE syntax to replace comma with space and using batch job to retrieve data from the table but in the file i could see junk character replaced before the data and if i won't use the replace syntax then i am not getting that junk character. My requirement is to replace comma with space and shouldn't append any junk character. I am not able to figure out why these jonk characters are appending. Details are mentioned below, please help me.

Query used in SYSIN CARD;

SELECT REPLACE(SUBSTR(CL.CLNT_NM,1,40),',','') CLNT_NM,CHAR(',')


Job Card:
//FD4106CA EXEC PGM=IKJEFT01,DYNAMNBR=50                         
//STEPLIB   DD DSN=DB2O.SDSNLOAD,DISP=SHR                       
//SYSTSIN  DD  DSN=CFS4.Y6700.SYSIN(FD4106CA),DISP=SHR           
//SYSPUNCH DD DUMMY,DISP=(NEW,CATLG,DELETE),                     
//             UNIT=DISK,SPACE=(TRK,(5,5))                       
//SYSREC00 DD DSN=CEA1.BFD33H.SR02249.REPORT(+1),               
//             DISP=(NEW,CATLG,DELETE),                         
//             UNIT=DISK,SPACE=(TRK,(100,50),RLSE),             
//             DCB=(RECFM=FB,LRECL=360,BLKSIZE=0)               
//SYSTSPRT DD SYSOUT=*                                           
//SYSPRINT DD SYSOUT=*                                           
//SYSUDUMP DD SYSOUT=*                                           
//SYSOUT   DD SYSOUT=*                                           
//SYSIN    DD DSN=CEA1.BFD33H.SR02249.SYSIN(FD4EX1AC),DISP=SHR   


Out put File: (In the below file, you can see hex value data 0028 is the junk data when i run the query with REPLACE syntax)
    CEA1.BFD33H.SR02249.REPORT.G0023V00
 ===>                                 
----+----1----+----2----+----3----+----
***************************** Top of Da
  Ahold USA Wrap Up                   
02C89984EEC4E9894E944444444444444444444
081863404210691704700000000000000000000
---------------------------------------
  Ahold USA Wrap Up                   
02C89984EEC4E9894E944444444444444444444
081863404210691704700000000000000000000
---------------------------------------
  Amerisource Bergen Corporation       
02C9898A9A9884C898894C999998A8994444444
081459926493502597550369769139650000000
trilochan_p
 
Posts: 15
Joined: Tue Dec 06, 2011 2:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Replace Query retrieve junk character

Postby dick scherrer » Tue Apr 15, 2014 12:05 am

Hello,

What is in the SYSTSIN member?

Please post the same input records (in hex).

What is the recfm(s) of the file(s).
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DB2 Replace Query retrieve junk character

Postby trilochan_p » Tue Apr 15, 2014 12:58 am

Hi,

Here are my SYSTSIN member details and

DSN SYSTEM(DB2O)                   
RUN PROGRAM(DSNTIAUL) -           
PLAN(DSNTIAUL) -                   
LIB('TTAP.TS2.DB2.APFLIB')  -     
PARMS('SQL')                       
END                               


And Select Query in SYSIN dataset:

//SYSIN    DD DSN=CEA1.BFD33H.SR02249.SYSIN(FD4EX1AC),DISP=SHR
trilochan_p
 
Posts: 15
Joined: Tue Dec 06, 2011 2:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Replace Query retrieve junk character

Postby NicC » Tue Apr 15, 2014 2:53 am

I don't see any "junk" characters so where are they?

Why is this posted in JCL when it is a DB2 query? Moving to DB2.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: DB2 Replace Query retrieve junk character

Postby trilochan_p » Tue Apr 15, 2014 5:22 pm

Hi Nic,

The requirement is to produce .csv format report to user and this has to be automated, so DB2 Query was tested in JCL.
trilochan_p
 
Posts: 15
Joined: Tue Dec 06, 2011 2:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Replace Query retrieve junk character

Postby NicC » Tue Apr 15, 2014 6:05 pm

so DB2 Query was tested in JCL

But your problem is with your DB2 query results and nothing whatsoever to do with JCL. Your JCL only asks the operating system to run a program - it does nothing with the data. The JCL section of the form is for JCL queries i.e what can be found in the JCL manual and utilities. Most things on the mainframe require JCL so that they can execute - online services as well as batch.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: DB2 Replace Query retrieve junk character

Postby dick scherrer » Tue Apr 15, 2014 7:22 pm

Hello,

What is in the SYSTSIN member?

Please post the same input records (in hex).

What is the recfm(s) of the file(s).


Well, you answered one of the questions . . . :?

If you answer the other questions asked earlier, maybe someone can help . . .

I suspect what you call junk characters are the record length . . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DB2 Replace Query retrieve junk character

Postby trilochan_p » Tue Apr 15, 2014 8:50 pm

Hi,

The record length for the output file has been mentioned in my JCL (SYSREC00 DD). The problem i could see here is as, If i use REPLACE syntax then 2 junk caracter is adding before data and if i don't use the REPLACE command then data is fine. What is the solution for this.
trilochan_p
 
Posts: 15
Joined: Tue Dec 06, 2011 2:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Replace Query retrieve junk character

Postby trilochan_p » Tue Apr 15, 2014 9:03 pm

Hi All,

I have used CHAR syntax before REPLACE and it removed the Junk characher and data looks fine to me.
Syntax: CHAR(REPLACE(SUBSTR(CL.CLNT_NM,1,40),'-',''),40) CLNT_NM
trilochan_p
 
Posts: 15
Joined: Tue Dec 06, 2011 2:37 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post