Miscellaneous Extract Topics (Option 3.7.1)

This section provides information about the following topics:

Extract JCL
Db2 Privileges
File-AID Extract File
File-AID Extract Request File

Extract JCL

The extract JCL shown in the following figure performs the batch portion of the extract process and generates the appropriate reports. The extract JCL can be coded and executed separately in a batch job. In addition, File-AID can automatically generate the JCL through the online extract screens.

Extract JCL

//EXDRV    EXEC PGM=XFREXEC,REGION=nn,
//         PARM=(FD,2,B)
//F2RPRINT DD DUMMY
//STEPLIB   DD DSN=File-AID-load-libraries,DISP=SHR
//          DD DSN=DB2-load-library,DISP=SHR
//RDXMLIB   DD DSN=File-AID-message-library,DISP=SHR
//AUDIT     DD *
USERID.audit prefix
/*
//RDXEC     DD DSN=extract-request-file,
//             DISP=(OLD,DELETE,CATLG)
//EXTRACT   DD DSN=extract-to-file-name,DISP=SHR
//EXTPARMS DD DSN=extract-parms-file,DISP=SHR
/*
//SORTWK01 DD UNIT=SYSDA,
//             SPACE=(CYL,(5,5),,CONTIG),
//             DISP=(NEW,DELETE,DELETE)
//SORTWK02 DD UNIT=SYSDA,
//             SPACE=(CYL,(5,5),,CONTIG),
//             DISP=(NEW,DELETE,DELETE)
//SORTWK03 DD UNIT=SYSDA,
//             SPACE=(CYL,(5,5),,CONTIG),
//             DISP=(NEW,DELETE,DELETE)
//DFSPARM   DD UNIT=SYSDA,
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=8000,DSORG=PS),
//             SPACE=(TRK,(1,1),RLSE),
//             DISP=(NEW,DELETE,DELETE)
/*
//X37MSG    DD SYSOUT=(*)
//PROGRESS DD SYSOUT=(*)
//REPORT    DD SYSOUT=(*)
//RDXSQLCD DD SYSOUT=(*)
//SYSTERM   DD SYSOUT=(*)
//SYSPRINT DD SYSOUT=(*)
//TPTPRINT DD SYSOUT=(*)
//SYSOUT    DD SYSOUT=(*)
//SORTLIST DD SYSOUT=(*)
//IDCAMLIST DD SYSOUT=(*)
//DPARMS    DD SYSOUT=(*)
CYL
1
1
//*

XFREXEC is the File-AID extract program name.

The parameter information in the PARM field in the EXEC statement must have the following format:

EXEC Statement PARM Field—Extract Step

Parameter	Default Value	Length	Description
Product	FD	2	File-AID for Db2
Option	2	1	File-AID option number.
Operating Mode	B	1	B Batch

The following DD statements are required for extract:

DD Statements—Extract

DD Statement	Description
STEPLIB	File-AID load libraries (SXVJLOAD and CXVJLOAD) and Db2 load library
RDXMLIB	Library containing File-AID messages.
RDXEC	Library containing the File-AID extract criteria (only required if extract criteria is being applied).
EXTRACT	File to which extracted data is written.
DELIMEXT	File to which extracted data is written when Delimited extract option was specified (see Delimited Format Options). DELIMEXT DD is used instead of EXTRACT DD.
AUDIT	Prefix for the File-AID/Data Solutions audit trail dataset name.
SYSOUT	Output class for SORT utility.
RDXSQLCD	Output class for detailed SQL error information from Db2.
SYSTERM	Output class for SAS/C runtime errors.
SYSPRINT	Standard output stream.
SYSIN	Control card used by File-AID to sort data during the extract process.
SORTWK01 SORTWK02 SORTWK03	Work files required by the SORT utility.
X37MSG REPORT PROGRESS	Output class for error messages and reports.
DFSPARM	Parameters for temporary sort files.
F2RPRINT	Dummy DD.
EXTPARMS	File containing the extract parameters.
IDCAMLST	Parameters for temporary extract files for XML and MVS objects.
TPTPRINT	Output for diagnostic trace.
DPARMS	Space allocation parameters for temporary extract files.

Return Codes

An execution return code (RC) appears in the output listing. For return codes other than 0, an error code and message indicating the specific problem also appear in the listing. In general, the return codes can be summarized as follows:

Return Code	Description
0	Job ran to completion.
4	Job ran to completion, warnings issued.
8	Job completed with error.
16	Severe error, job aborted.

Db2 Privileges

During extract, the SELECT privilege is required for all tables included in the extract. The plan being used to execute File-AID requires the SELECT privilege for the Db2 system catalog tables.

You must have one of the following authorization privileges to extract data from Db2 Image Copy:

RECOVERDB privilege for the database
DBADM, or DBCTRL authority for the database
SYSCTRL or SYSADM authority

File-AID Extract File

During the extract process, File-AID writes all extracted data to the extract file. This is the file that File-AID uses in the load process to load all or only selected data.

In order to increase data security the File-AID extract file does not include DDL statements in readable form. The Db2 source environment information is captured in a masked format.

The extract file has the following characteristics:

File organization

Partitioned (DSORG=PO) or sequential (DSORG=PS)

Record format

Variable-blocked (RECFM=VB)

Logical record length

Maximum LRECL determined by File-AID (default = 27994)

Important

File-AID will dynamically reallocate a sequential extract file to adjust to the needed space. Dynamic reallocation is only available for sequential files (DSORG=PS).

The first four bytes at the beginning of each record indicate the record’s type and the corresponding header record.

After a successful extract is performed, the extract file contains the following types of records:

product record (0);
header record (H);
Db2 create records (C);

data records (D);
Db2 XML data records (x);
trailer records (T).

File-AID Extract Request File

The Extract Request file is a work file that contains records that reflect the values you specified in the Extract DB2 - Driving Object panel. After successful extract execution it will be deleted.

Work files

During an extract, File-AID creates some work files that it deletes after extract execution.

The work files have the following format::

userid.FADB2.*.Dnnnnnn.Tnnnnnn

Where * is either EXTCRIT or PARMS.

Periodically, you may want to manually delete any left-over work files from incomplete extracts that are no longer needed.