Using the Batch Export Function
The CA Dataquery batch export function allows the user to export the columns and keys named in the PRINT or DISPLAY statement of the query to an output file in the order specified in the query. The tables and keys named in the PRINT or DISPLAY statements of the query determine the columns in the output record. This exported data is then available for use in user-written programs. The user must be authorized for SUBMIT ALLOWED and EXPORT ALLOWED on the User Table Maintenance panel.
Dataquerybatch export function allows the user to export the columns and keys named in the PRINT or DISPLAY statement of the query to an output file in the order specified in the query. The tables and keys named in the PRINT or DISPLAY statements of the query determine the columns in the output record. This exported data is then available for use in user-written programs. The user must be authorized for SUBMIT ALLOWED and EXPORT ALLOWED on the User Table Maintenance panel.
JCL Member Creation
Dataqueryplaces the requested exported data in a sequential output file, a data set must be allocated for the exported data. When you create your JCL member for exporting data, specify the name of the data set to which the export data is to be written.
The JCL member can specify any device type supported by IBM QSAM (z/OS). In a z/OS environment, you can specify either a tape or disk file in your JCL member.
In a z/VSE environment, the System Option Table option specifies whether the default is tape or disk. This default can be overridden for nondeferred jobs at submit time when the system prompts the user for the device. The user's response must match the device specified in the JCL member. Deferred jobs always take the System Option Table value.
Dataqueryhas specific requirements for the DDname in your export JCL member name.
In z/OS, for variable length a comma separates value output, the DDname must be DQOUT. You must also specify tape or disk in a subsequent JCL statement.
In z/VSE, the DLBL name must be DQOUTD. The dftname used for tape is DQOUTT, but an unlabeled tape is created.
For fixed length output in z/OS, the DDNAME must be DQFIXD.
In z/VSE, the DLBL name must be DQFIXD. The dtfname used for tape is DQFIXT, but an unlabeled tape is created.
The user requests data to be exported by specifying either: variable-length or fixed-length data records on the BATCH EXECUTION panel, or by using the EXPORT command in SIGN/ON mode in batch
The user can also request two types of variable-length output: either DETAIL or TOTALS or both (both is supported only on the BATCH EXECUTION panel). If both types are requested, the output consists of two sets of output records, one for DETAIL and one for TOTALS. DETAIL type formats the values of the columns in the PRINT statement of the query in the data record. TOTAL formats subtotals each time a value in a sort field changes when PRINT LINE TOTALING is requested. DETAIL is the default and can be omitted. TAPE and DISK are used only in z/VSE and are ignored for z/OS. If TAPE and DISK are omitted for z/VSE, the defaults specified in the System Option Table are used. No grand totals are exported.
Every column named in the print or display statement of a DQL query or named in the select clause of an SQL query is written to the output row, using the data type and length as on the database table from which it was retrieved.
Exception:In SQL mode, data type SQL-date will be exported as a 10-byte character, SQL time as an 8-byte character, and SQL-timestamp as a 24-byte character.
To print control breaks for SQL queries, use a REPORT DEFINITION (TOTALS and CONTROL BREAKS).
In sign/on mode, you can execute more than one query per execution of DQBATCH.
Dataqueryallows multiple EXPORT control cards.
A sample format for multiple EXPORT control statements follows:
EXPORT 'SETNAME' TOTALS TAPE EXPORT 'SETNAME' DETAIL TAPE EXPORT 'SETNAME' TOTALS DISK EXPORT 'SETNAME' DETAIL DISK
For fixed output, only one EXPORT per DQBATCH execution is permitted. You may not have multiple export cards. In fixed output, 'NAME' and DETAIL may be omitted.
A sample format for an EXPORT control card follows:
EXPORT 'NAME' DETAIL DISK FIXED EXPORT 'NAME' DETAIL TAPE FIXED
If you execute more than one EXPORT per any execution of DQBATCH,
Dataquerywrites all of the output sets to the same output sequential data set. In SIGN/ON mode you can specify the device type on the EXPORT command input card, but all exports in any execution must be to the same output data set. You can write only one output data set per one execution of DQBATCH.
The job control cards used to execute DQBATCH must contain the necessary data definition statements to define the output data set for the EXPORT command.
Data Set Definition
For z/VSE, the data definition statements must define a data set of the type requested on the EXPORT command or the BATCH EXECUTION panel. In z/VSE, the type of the output data set in deferred batch is determined from the EXPDEV= parameter in the
DataquerySystem Option Table.
For OS/MSP the type of device is determined only by the data definition statements in the JCL.
For Fixed-Length Output
The output is sequential. The record size is determined by columns being output and records are blocked with a maximum block size of 4092. The fields are output in type and length as retrieved from the database. No record descriptor word is written and no header or trailer records are written and data records do not have DATA in the first 4 bytes.
For Variable-Length Output
The output data set is a sequential file. The records are variable length, blocked format with a block size of 4096. The maximum length of any data record is 4088.
The data records are written in character format. Leading zeros in numeric fields and trailing blanks in character fields are suppressed. All blank character fields or all zero numeric fields are indicated by a comma immediately following the comma for the preceding field. Numbers containing decimal places appear with a decimal point followed by a zero for each decimal place.
The following table shows a sample record.
Record Descriptor Word
The first 4 bytes of any record contain the record descriptor word. The first field is a keyword indicating the record type which consists of:
- Header record type containing a description of the exported data
- Data record type containing the data values, such as character format with leading zeros suppressed in numeric fields and trailing blanks truncated in character fields
- Trailer record type containing the total record count including the trailer and header
Every output data set contains these three record types.
The data set can be downloaded to a PC, since this is in PC file format known as CSV.
The following topics are discussed on this page:
Sample EXPORT JCL
Sample z/OS JCL
The following is a sample z/OS EXPORT job:
//jobnameSee// EXEC PGM=DQBATCH //STEPLIBSee//SYSUDUMP DD SYSOUT=* //SYSPRINT DD SYSOUT=* Print Output //SNAPER DD SYSOUT=* //SYSOUT DD SYSOUT=* //DQOUT DD DSN= ....Specify according to your site standards.//SYSIN DD * Command input SIGN/ON dquser FIND ALL CAI-DETAIL-REC X PRINT ITMID-ORDID=KEY ORD-QTY SHIP-QTY UNIT-PRICE DISC-PCT ACT-DT EXECUTE * EXPORT 'DOCUMENT1' DETAIL /* //
Sample z/VSE JCL
The following is a sample z/VSE EXPORT job:
* $$ JOB ...See* $$ LST ... // JOB name // EXEC PROC=procnameWhether you use PROCs or LIBDEFs, see// DLBL DQOUTD,'DATACOM.DQ.EXPORT',1 // EXTENT ,volser,1,0,strk,ntrks // EXEC DQBATCH SIGN/ON dquser FIND ALL CAI-DETAIL-REC X PRINT ITM-ORDID-KEY ORD-QTY SHIP-QTY UNIT-PRICE DISC-PCT ACT-DT EXECUTE * EXPORT 'DOCUMENT1' DETAIL /* /& * $$ EOJ
Sample Export File
After printing the report,
Dataqueryexports the data as indicated in the query to the named data set. Each set of data is labeled by its set name specified in the export control card or on the BATCH EXECUTION panel. It is important that you give each set a unique name so that it can be easily located in the data set.
A sample variable length EXPORT file contents display follows:
HEADER,DOCUMENT1,DETAIL,042887,144204,RECORD,CAI-DETAIL-REC,DTL,000, KEY,ITMID-ORDID-KEY,02,C,0010,00,N,N,005,00,N,FIELD,ORD-QTY,N,007, 00,Y,FIELD,SHIP-QTY,N,007,00,Y,FIELD,UNIT-PRICE,N,007,02,Y, FIELD,DISC-PCT,N,003,01,Y,FIELD,ACT-DT,C,0006,00,N DATA,"C10000 01008",2,2,29.50,.0,"991106" DATA,"C10001 01008",4,4,21.00,.0,"991106" DATA,"C10002 01008",6,6,14.00,.0,"991106" DATA,"C10005 01008",2,2,66.75,.0,"991106" DATA,"A60005 01009",1,1,219.99,.0,"991106" DATA,"A60008 01009",2,2,179.99,.0,"991106" DATA,"A70000 01009",4,4,99.99,.0,"991106" DATA,"H10000 01010",2,2,59.99,.0,"991106" DATA,"H10002 01010",10,10,4.99,.0,"991106" DATA,"H20001 01010",3,3,21.99,.0,"991106" DATA,"H20002 01010",5,5,39.99,.0,"991106" DATA,"H20004 01010",2,2,21.99,.0,"991106" DATA,"H20006 01010",5,5,12.99,.0,"991106" DATA,"H30000 01010",4,4,124.99,.0,"991106" DATA,"H30002 01010",6,6,69.99,.0,"991106" DATA,"H30003 01010",3,3,59.99,.0,"991106" DATA,"H40000 01010",15,15,15.99,.0,"991106" DATA,"H70001 01010",100,100,38.99,.0,"991106" DATA,"H80002 01010",20,20,73.99,.0,"991106" DATA,"H80004 01010",10,10,23.99,.0,"991106" TRAILER,0022
Header Format for Variable-Length Output
The following is the format of the header statement from the sample z/OS EXPORT report:
HEADER FIELD FIELD DESCRIPTION Header Describes the data records SET-NAME Name of the output set SET-TYPE DETAIL or TOTALS FIND DATE Date that data was found FIND TIME Time that data was found Descriptors Name of the records, keys or fields Record Record descriptor follows RECORD-NAME Name of the record DB-NAME 3-character DB file name DB ID Database ID for the file Key Key descriptor follows KEY-NAME Name of a key whose value was exported COUNT Number of the fields in the key *DATATYPE Type of data exported: C = Character field, not nullable N = Numeric field, not nullable K = Nullable character M = Nullable numeric *LENGTH Length of field if TYPE=C or no. of digits if TYPE=N *DECIMALS Number of decimal places if TYPE=N or no. of zeros if TYPE=C *SIGN Y if signed, TYPE=N default is N (Fields marked with * repeat if KEY COUNT is greater than 1) Field Field descriptor follows FIELD-NAME Name of the output field DATATYPE Type of data exported: C=character, N=numeric LENGTH Length of field if TYPE=C or no. of digits if TYPE=N DECIMALS Number of decimal places if TYPE=N or no. of zeros if TYPE=C SIGN Y if signed, TYPE=N default is N FIELDS IN DATA RECORDS: Data Indicates data values follow DATA Comma separated values, as described by the Header FIELDS IN TRAILER RECORDS: Trailer Indicates this is a trailer record COUNT A count of total number of records