Extracting Data (FEX)

Data collected by a query and stored using the EXTRACT command can be accessed by user application programs. By calling the Extract Interface (FEX), these user programs can access the extract data table created by the EXTRACT command. FEX handles both batch and online extracts.

Extracted data is stored in the DQF. Unlike Saved sets and Found sets, an extract extracts and stores data not pointers. Thus an extract can require a significant amount of space. If you plan to use the EXTRACT command, you should review the size of your DQF table. If you need to enlarge it, see DQF: Found Table for details.

Operation

By calling the Extract Interface (FEX), these user programs can access the extract data table created by the EXTRACT command. The Extract Interface handles both batch and online applications. In batch, it can be used by CA Datacom/DB Reporting Facility to create reports or sequential output tables. See CA Datacom/DB Reporting Facility documentation for further information on these reporting capabilities.

Procedure

Use a standard CALL statement to communicate with FEX. For each call, provide a place for the FEX to return a code set to indicate the result. Each call with a return code of zero puts the next logical row of the EXTRACT member in the data area you provide. The sequence is specified by the SORT statement of the original query. All six parameters are required and must be specified.

The format of the call is as follows:

COBOL: CALL 'DQFEXPR' USING parm1 parm2 ... parm6
FORTRAN: CALL DQFEXPR (parm1,parm2, ... parm6)
Assembler: CALL DQFEXPR,(parm1,parm2, ... parm6)
PL/I: CALL DQFEXPR,(parm1,parm2, ...parm6)

Parameters for FEX

The six parameters you must provide to the FEX are as follows:

WORK AREA

An 1100-byte area reserved for use by the FEX. Your program should initialize this area to blanks or binary zeros prior to the first request for an EXTRACT member, or when changing from one member to another before EOF is encountered on the first member. The program must not modify the work area between requests for the same member.

Set up the work area in working storage or in the linkage section of the program on a fullword boundary (a 01-level field for COBOL). If the program is online and reentrant, define the work area in a task-related storage area described in the program's linkage section.

MEMBER NAME

Names the eight-character field containing the member name of the EXTRACT member being accessed.

OPERATOR ID

Names the 32-character signon ID of the user who created the EXTRACT member being accessed.

PASSWORD

Refers to the nine-character password specified in the EXTRACT command. If a password was not specified, provide a blank field.

ROW LENGTH

Refers to a two-byte binary field containing the length of the logical rows in the EXTRACT member. See the section in this chapter on logical row format.

DATA AREA

Names the data area into which the FEX is to return EXTRACT member rows. You are responsible for correctly defining data values within this area. To avoid unpredictable results, ensure that this area is large enough to accept the logical rows passed from the FEX.

Return Codes

When the call to the FEX is completed, a code is returned to the calling program to indicate the results of the request. The program must interrogate the code each time to determine whether to continue. The return code is a two-byte character field located in the first two positions of the work area provided as the first parameter of the call.

Following are FEX return codes:

0: Good return - the data requested was successfully returned to the program.
4: Logical end of file - no more data exists for the requested EXTRACT member.
8: The requested EXTRACT member does not exist or the user ID (or password) is invalid.
12: An error was returned from CA Datacom/DB during a request to read the DQF (Found Table). The second two positions (bytes 3-4) of the work area contain the CA Datacom/DB return code.
16: Length error - the actual length of a logical row for the EXTRACT member specified exceeds the row length specified in the input parameter list.

Logical Row Format

EXTRACT member data rows are formatted as follows:

The data fields are concatenated, end to end, so that they are sequenced just as they were in the PRINT or DISPLAY statement of the original query.
Each data field in the EXTRACT member row appears as it originally appeared on the database. The format of each data field should always conform to the CA Datacom Datadictionary definition for the CA Datacom/DB files from which the data field was extracted.
Each exported field which is nullable on the database is preceded by a 1-byte null indicator. In the EXTRACT row, this null indicator is blank if the field is not null and N if the field is null. The field data immediately follows the null indicator. If the null indicator is N, you should ignore any data in the field.
Data fields computed from a SET statement appear in the EXTRACT member row as 8-byte signed, packed fields, unless the DQOPTLST parameter XTRSETL=16 is specified. If it is, they are 16-byte signed, packed fields.
Note: If a SET result field would normally have an error code printed during the report due to invalid data or some other problem or the result contains more than 15 decimal digits and XTRSETL=16 was not specified in DQOPTLST, the FEX defaults the field to zero, a SET result field which is a result of a calculation on a nullable field is also nullable and is preceded by a 1-byte null indicator in the row output by FEX.

Implementing the FEX Online

To implement an online version of the FEX, write and link-edit the program just as you would any other CA Datacom/DB online program, with these additions:

Ensure that the DQF table is specified as one of the tables to be accessed in the CA Datacom/DB User Requirements Table associated with the online application under which this program is to execute. The following macro statements must be added:

 URT     TITLE 'DQURT - DQFEXPR'
         DBURINF                                                X
               OPEN=DB                                          X
               URTABLE=ASM,                                     X
               BACKSPT=YES *other user options may be coded*
         DBURSTR                                                X
               ABEND=YES
         DBURTBL                                                X
               TBLNAM=DQF,                                      X
               UPDATE=NO,                                       X
               SYNONYM=NO,                                      X
               DBID=nnnnn
         DBUREND

DBID=nnnnn is the database ID of the DQF (Found Table) usually 00003.

The calling program must be linked with DBCSVPR and DQFEXPR as shown in the following link-edit control statements:
```
     INCLUDE user program
     INCLUDE DBCSVPR
     INCLUDE DQFEXPR
     ENTRY user program
```

Implementing the FEX in Batch

To implement a batch version, write and link-edit the program as you would any other batch CA Datacom/DB program, with these additions:

Specify the DQF in the CA Datacom/DB User Requirements Table as one of the tables to be accessed. Add the following macro statements:

 URT     TITLE 'DQURT - DQFEXPR'
         DBURINF                                                X
               OPEN=DB                                          X
               URTABLE=ASM,                                     X
               BACKSPT=YES *other user options may be coded*
         DBURSTR                                                X
               ABEND=YES
         DBURTBL                                                X
               TBLNAM=DQF,                                      X
               UPDATE=NO,                                       X
               SYNONYM=NO,                                      X
               DBID=nnnnn
         DBUREND

DBID=nnnnn is the database ID of the DQF (Found Table) usually 00003.

The program must be linked with the User Requirements Table and DQFEXPR as shown in the following link-edit control statements:
```
     INCLUDE user program
     INCLUDE user requirements table
     INCLUDE DQFEXPR
     ENTRY BEGIN
```
Under z/OS and z/VSE, the execution JCL for the program must include DD statements for the DQF and for the CA Datacom/DB Directory (CXX) and Index Area (IXX) unless the dynamic allocation feature of CA Datacom/DB is installed.

Note: For additional information about installing or using CA Datacom/DB programs, see the CA Datacom/DB Programming" Guide and the CA Datacom/DB Database and System Administration Guide.