Running CA Datacom/DB SQL we are getting a space in column 1 of the output, is there a way to remove this while using DBSQLPR?

Document ID : KB000019033
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Running CA Datacom/DB SQL we are getting a space in column 1 of the output before the first data field, and we would like to use the file as input to other programs. Is there a command or options setting to remove this in the DBSQLPR execution?

Solution:

All of the output files from DBSQLPR (output, options, official messages, etc.) have a blank as the first character of the format string for the Print command. This is not changeable via options or commands, so the only thing you can do to eliminate that first-column space is to add a step following the DBSQLPR execution to copy the file to another file and bypass the blank column, and copy the data starting in column 2.

You can use any file manipulation tool or SORT to do that. Here is a z/OS sample of how to copy it using IEBGENER:

//GENER    EXEC  PGM=IEBGENER,REGION=0M
//SYSIN    DD   *
GENERATE MAXFLDS=1
RECORD FIELD=(reclen,2,,1)      <-- change reclen to your record length of the output file 
/*
//SYSPRINT DD  SYSOUT=*
//SYSUT1   DD  DISP=SHR,DSN=Your file from DBSQLPR,BUFNO=40
//SYSUT2   DD  DSN=Your new output file,DISP=(NEW,CATLG),
//         UNIT=3390,SPACE=(CYL,(99,99)),BUFNO=40,
//         LRECL=reclen,RECFM=FB,BLKSIZE=0   <-- change reclen to your record length 
//

Using the BUFNO parameter makes this process faster. On a test system, copying nearly 4 million records of 450 bytes added about 7-10 seconds to the total job run time.