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

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

book

Article ID: 33295

calendar_today

Updated On:

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