Using DBSQLPR to create a data extract of the first 80 bytes of a variable length column

Products

• Datacom
• DATACOM - AD
• Datacom/AD
• Datacom/DB

Issue/Introduction

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

Environment

Release: All supported releases.

Resolution

This is a two step process:

1. Use 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 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 table can be checked via a CXX report of the database.

Additional Information

Using SQL