For CA Datacom users without SQL: How to Extract Only a Subset of Records from a Table?
Article ID: 19519
Updated On:
Products
Issue/Introduction
Description:
If you have ever needed to extract a given record or subset of records out of a CA Datacom Database table but SQL was not implemented in your MUF, read further! This article lists several methods to perform this type of data extract.
Solution:
One way to accomplish this is to execute DBUTLTY using the EXTRACT function. However, this would capture all of the data. The output would then need to be reduced using an editor or another program. Most can use their mainframe sort utility to accomplish this function.
A second method is to run a DBUTLTY EXTRACT SEQ=NATIVE utilizing the FIRSTKEY= or LASTKEY= or both parameters to select only those records with a Native Key that matches the value or range desired. However, since this selection facility requires the EXTRACT to be run with SEQ=NATIVE, no other task can have any table in the same area open for update at the time this is executed.
A third way is to create an extract file from an existing DBUTLTY BACKUP tape. Using a backup file as input to a DBUTLTY EXTBKUP function creates an extract image from the backup file. Once again, the file would then need to be reduced in size by some other means.
A fourth way is to utilize the CA Dataquery DQBATCH utility program using the EXPORT function.
A fifth way is to utilize the CA Datacom Reporting Facility utility, DRREPORT. Utilizing the WRITE ONLY feature of this utility, records can be read from the desired table whether or not the table is currently open for update, selecting any field, and written out to a sequential file. A sample of how this can be accomplished in a z/OS environment is demonstrated in this sample CA Datacom Report Facility utility (DRREPORT) which reads the sample PMF table in DBID 001, selects employee number 00100 and writes the entire record out to a sequential DROUT file.
//*------------------------------------------------------------------**
//*
//* *** CA-DATACOM DEMO OF EXTRACTING A RECORD FROM ***
//* *** TABLE PMF IN DBID 001 ***
//*
//*------------------------------------------------------------------**
//DRWRITEO EXEC PGM=DRREPORT,REGION=4096K
//STEPLIB DD DSN=CAI.CHLQ.CUSLIB,DISP=SHR
// DD DSN=CAI.THLQ.CAILIB,DISP=SHR
// DD DSN=CAI.CA90S.WU42.CAILIB,DISP=SHR
//DRWORK DD UNIT=SYSDA,SPACE=(TRK,60)
//DROUT DD DSN=MY.DROUT,
// DISP=(NEW,CATLG,DELETE),UNIT=DISK,VOL=SER=vvvvvv,
// SPACE=(TRK,(2,1),RLSE)
//DDSNAP DD SYSOUT=*
//SNAPER DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
OPTION LIST ON MAP WRITE ONLY
USER 'DB REPORTING FACILITY SAMPLE USING WRITE ONLY'
SKIP1
NOTE ***********************************************
NOTE * *
NOTE * S A M P L E E X E C U T I O N *
NOTE * ...(DATACOM/DB FILE ACCESS)... *
NOTE * *
NOTE * THIS SAMPLE PROGRAM READS THE PMF TABLE IN *
NOTE * DBID 001, SELECTS EMPLOYEE NUMBER 100 AND *
NOTE * WRITES THAT ONE ENTIRE 80 BYTE RECORD TO *
NOTE * FILE DROUT. THE PMF TABLE CONTAINS A KEY *
NOTE * NANE OF EMPNO AND AN ELEMENT CALLED EMDTA *
NOTE * THAT SPANS THE ENTIRE RECORD. *
NOTE * *
NOTE ***********************************************
SKIP1
MASTER: INPUT DATACOM RECORD=380 NAME=PMF DBID=001
DEFINE DB-COMMAND 1-5 X
DEFINE DB-KEY-NAME 6-10 X
DEFINE DB-ELEMENT-LIST 191-201 X
NOTE * USER RECORD LAYOUT FOLLOWS:
DEFINE EMPLOYEE-NUMBER 301-305 X
DEFINE NAME 306-329 X
DEFINE FILLER 330-380 X
SKIP1
MOVE 'GETIT' TO MASTER.DB-COMMAND
MOVE 'EMPNO' TO MASTER.DB-KEY-NAME
MOVE 'EMDTA ' TO MASTER.DB-ELEMENT-LIST
GET MASTER
GO TO EOJ WHEN MASTER EQ END-OF-FILE
SKIP1
SELECT 'A' WHERE EMPLOYEE-NUMBER = '00100'
SKIP1
FORMAT EMPLOYEE-NUMBER NAME FILLER
END
/*
For more information on the DRREPORT facility, see the CA Datacom/DB Reporting Facility Guide.
This is rewrite of TEC355189.
Environment
Component: DB
Feedback
