DBSQLPR output missing the blank in first column
search cancel

DBSQLPR output missing the blank in first column

book

Article ID: 247478

calendar_today

Updated On:

Products

Datacom Datacom/AD Datacom/DB

Issue/Introduction

We have a customer that is telling us their output is off by one character when they run their SQL (DBSQLPR) job using Datacom/AD version 15.1. They did not have this issue under version 14.0 jobs. We were able to fix by having them update the SAS to look in column 1 instead of column 2, but we want to understand if this is a known issue with version 15.1 DBSQLPR?  

Environment

Release : 15.1

Component : Datacom/DB

Component : Datacom/AD

Cause

Version 15.1 solution SO09190 corrected an old problem that inadvertently introduced the blank in column 1.

Resolution

The "missing blank" is not an error, but is, in fact, a correction of an error that was introduced in a previous version of our software. Solution SO09190 for SQL 15.1 was published in October of 2019 and corrected several things in DBSQLPR processing. Here is one of the points in that solution:

3. A leading blank was added in a previous release, which has caused problems for some customers, as well as adding a blank to the output data, which is not allowed under the comma-separated values (CSV) standard.

There are several ways to deal with this situation where the blank column was expected but now does not appear. Which approach you take may vary, and your decision could be affected by what is downstream using the file. For some third-party applications, you don't have the luxury to tell it to start in Column 1 instead of Column 2; for others you do.

  1. You can review the jobs that process the SQL output file, and change them to now start with data in column 1.
  2. You can modify your SQL queries to add a blank concatenated to your first column. For example, if you have SELECT S_JOBNAME... you can change it to SELECT ' ' || S_JOBNAME.... Note that this will not work if the first column selected is not a character field.
  3. You can add an intervening step after the DBSQLPR step that copies the SQL output to a new file, adding a space to the head of every record. Here is an example IEBGENER that will shift columns 1-79 to the right one column, and insert a blank in column 1 for an 80-byte file:
//GENER    EXEC  PGM=IEBGENER       
//SYSIN    DD   *                  
GENERATE MAXFLDS=2,MAXLITS=1       
RECORD FIELD=(1,' ',,1),           
      FIELD=(79,1,,2)             
/*                                
//SYSPRINT DD  SYSOUT=*            
//SYSUT1   DD  DISP=SHR,DSN=<your input file> 
//SYSUT2   DD  DISP=OLD,DSN=<your output file>

Additional Information

As always, please contact Broadcom support for Datacom if you have further questions.