For CA Datacom users without SQL: How to Extract Only a Subset of Records from a Table?

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

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.