Different ways to extract Datacom Tables without SQL

Products

• Datacom
• Datacom/DB
• Datacom/AD

Issue/Introduction

This article lists several methods to extract a given record or subset of records out of a Datacom database table if SQL was not implemented in the MUF or if SQL takes too long to process.

Resolution

1. One way to accomplish this is to execute DBUTLTY using the EXTRACT function. However, this would capture all 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. The SORT INCLUDE control statement can be used to select data from the EXTRACT output file. The SORT OUTREC control statement can be used to convert decimal numbers to zoned decimals so the file can be used in non-mainframe systems.
   Example SORT SYSIN:

   SORT FIELDS=COPY
   INCLUDE COND=(01,01,CH,NE,C'T') only copy records with 'T' in first column.
   OUTREC FIELDS=(0001:0001,0006,           
                   0007:0007,0005,PD,TO=ZD,LENGTH=0009)   convert field at position 7 from packed decimal to zoned numeric

2. 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.
   
   
3. A third method 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.
   
   
4. A fourth method is to utilize the Dataquery DQBATCH utility program using the EXPORT function.
   
   
5. A fifth method is to utilize the Datacom Reporting Facility utility, DRREPORT. Utilizing the WRITE ONLY feature of this utility, records can be read from the desired table whether the table is currently open for update or not, 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 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.

Additional Information

See Datacom documentation section EXTRACT (Extract Data Table), BACKUP (Create Backups) and EXTBKUP (Extract from Backup File).

Also see article Shortening the run time for Datacom DBUTLTY BACKUP or EXTRACT processing.