IBM Mainframe Forum

Posted: **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 all

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

Job Card:

Select all

//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)

Select all

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

Posted: **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).

Posted: **Tue Apr 15, 2014 12:58 am**

Hi,

Here are my SYSTSIN member details and

Select all

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

And Select Query in SYSIN dataset:

Select all

//SYSIN DD DSN=CEA1.BFD33H.SR02249.SYSIN(FD4EX1AC),DISP=SHR

Posted: **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.

Posted: **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.

Posted: **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.

Posted: **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 . . .

Posted: **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.

Posted: **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

IBM Mainframe Forum

DB2 Replace Query retrieve junk character

DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character

Re: DB2 Replace Query retrieve junk character