DBSQLPR output missing the blank in first column
Article ID: 247478
Updated On:
Products
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.
- You can review the jobs that process the SQL output file, and change them to now start with data in column 1.
- 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.
- 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.
Feedback
