Technique using DBSQLPR to create a data extract of the first 80 bytes of a variable length column.

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

Summary:

This article provides a technique using DBSQLPR to extract the first 80 bytes of a variable length field from a CA Datacom Table that is SQL accessible.

Instructions:

This is a 2 step process:

1. Use CA Datacom DBSQLPR batch utility to extract the data.

2. Use IEBGENER to copy to positions 2-81 to a new fixed 80 data set.  

Details for each step: 

1. Run DBSQLPR with the following options to suppress various headings and override default limitations.
This also redirects the STDOUT output to a new DD STDALT allowing the DCB to default to VB 1028.
 
//DBSQLPR  EXEC PGM=DBSQLPR,REGION=0M
//SYSUDUMP DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SNAPPER  DD  SYSOUT=*
//STDERR   DD  SYSOUT=*
//STDOUT   DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//STDALT   DD  DSN=dbsqlpr.extract.dataset,
//         UNIT=unit,SPACE=(cyl,(n,n)), 
//         DISP=(NEW,CATLG,DELETE)
//OPTIONS  DD  *
DATASEPARATOR=,
PRTFILE=STDALT
PRTWIDTH=1500
NOCOLHDR
NOECHO
NOFORMFEED
NOPAGEHDR
NOPAGES
PAGELEN=99999
ROWLIMIT=9999
/*
//SYSIN        DD DATA,DLM=$$
SELECT columnname FROM tablename;
$$
 
2.  Run IEBGENER to copy the positions 2-81 to a new data set positions 1-80, removing the first character which is the carriage control character.
 
//IEBGENER EXEC  PGM=IEBGENER,REGION=0M
//SYSPRINT DD  SYSOUT=*
//SYSUT1   DD  DSN=dbsqlpr.extract.dataset,DISP=SHR
//SYSUT2   DD  DSN=iebgener.output.dataset,
//         DISP=(NEW,CATLG,DELETE),
//         UNIT=unit,SPACE=(cyl,(n,n)),
//         DCB=(LRECL=80,RECFM=FB,BLKSIZE=0)
//SYSIN    DD   *
 GENERATE MAXFLDS=10
 RECORD FIELD=(80,2,,1)
/*
 

NOTE: The CA Datacom table must be "SQL-able" in order to run SQL requests. That is, the value of the table's SQL-INTENT attribute must be "Y". "SQL-INTENT" for a tabl can be checked via a CXX report of the database.

Additional Information:

For additional information on DBSQLPR and SET-INTENT, refer to the CA Datacom/DB SQL User Guide  

https://support.ca.com/cadocs/0/CA%20Datacom%20V15%200%20Public-ENU/Bookshelf.html?intcmp=searchresultclick&resultnum=13