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

Products

• Datacom
• DATACOM - AD

Issue/Introduction

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

 

Environment

Release: DATABB00200-14-Datacom/AD
Component: