The following is a detailed description of each control statement used in submitting a DQL Language or SQL query. Statements appear in alphabetical order.
OPTION AUTHID=
(Optional - Valid in SQL Mode only.) Allows you to specify the authorization ID that is to be used in the query. The format of the OPTION AUTHID= statement is as follows:
►►─ OPTION ─ AUTHID=nnnnnnnn ─────────────────────────────────────────────────►◄
Where nnnnnnnn is a 1- to 18-character authorization ID.
A valid 1- to 18-character ID that specifies the authorization ID used in your query
If no OPTION AUTHID= statement is used, your private authorization ID
EXECUTE
(Required in both SQL Mode and DQL Mode.) Tells CA Dataquery to execute the query. EXEC can also be used. If this keyword is omitted, the query does not execute and no printed output is produced.
►►─ EXECUTE ─ name ─ (variable-list) ─ (stage-1) ─ (thru ─ stage-2) ──────────► ►─ (total-option) ───────────────────────────────────────────────────────────►◄ ►►─ EXECUTE ─ name ─ (total-option) ──────────────────────────────────────────►◄
(Required in both SQL Mode and DQL Mode.) Specifies a name of a query or dialog to execute.
A 1- to 15-character query name, or an asterisk (*) to specify execution of the active query. To use the active query, enter it in the JCL stream immediately preceding the EXECUTE statement, using the FIND statement of the DQL query or any SQL query statement. The active query cannot be a dialog.
(No default)
(Optional - Valid in both SQL Mode and DQL Mode.) The variable-list is a list naming the variables which can be substituted for a dialog executed by this batch job. This is optional and if not given for a dialog, the query is executed with the default values.
If it is necessary to continue a statement when naming values for dialog variables, place any non-blank character in column 72 of the SYSIN or SYSIPT statement. The statement to be continued can end at any comma in the variable list and the next variable can begin in any position on the next statement. To enter a very long variable value, continue the variable through column 71 with the continuation character in column 72, and begin the continuation in column 1 of the next statement. Only a variable-list can continue from one statement to another. All of the other parameters on the statement must be totally contained on one SYSIN or SYSIPT statement variable. Identify each variable by its dialog number. Separate each variable value with a comma.
Enter the variable number indicated in the dialog, an equal sign, and the value.
The default values assigned to the dialog variable
The following is a sample valid variable-list entry:
1='EASTERN',15='986',3='CAI'
(Optional - Valid in DQL Mode only.) Specifies at which stage query execution should begin. This is optional and is only used when specifying that execution stop at a specific stage before producing output. An example of an instance when you might choose to stop execution before producing output is: selecting data for EXPORT without printing it first. In DQL Mode, batch execution only permits stage-1 to be FIND. Enter stage-1 when specifying that execution stop at a specific stage. (See stage-2, next.) Names the first stage of the query to execute, which in batch CA Dataquery must be FIND.
FIND
FIND
(Required when stage-1 is specified - Valid in DQL Mode only.) Indicates the last stage of the query to be executed. (THRU is an optional word.)
SET, SORT, PRINT, or DISPLAY
Defaults to the last CA Dataquery keyword in the query.
(Optional - Valid in both SQL Mode and DQL Mode.) Indicates the type of totaling to be done, if any. In DQL Mode, indicates the type of totaling to be done, if any. In SQL Mode, TOTALS option will have meaning only for a query developed online for which a report format has been generated.
NO-TOTALS or TOTALS-ONLY
Defaults to the value specified in the query.
EXECUTE Examples
The following is an example of an EXECUTE statement for a dialog with totals:
EXEC MYDIALOG 1="JUNE',3='TEXAS',4=500.00 TOTALS-ONLY
The following is an example of an EXECUTE statement that is not a dialog but uses all options:
EXEC MYQUERY FIND THRU SORT TOTALS-ONLY
EXPORT
(Optional - Valid in both SQL Mode and DQL Mode.) Tells CA Dataquery to export the data. The format for the EXPORT statement is:
►►─ EXPORT ─ set-name ─ data-type ─ output-type ─ data-format ────────────────►◄
Identifies the set being exported.
A valid 1- to 15-character set name enclosed in apostrophes.
(No default)
Indicates whether detail or total records are exported.
DETAIL or TOTALS (Use 2 EXPORT statements to have both details and totals reported.) Only DETAIL is valid for use with a FIXED format. See data-format
DETAIL
(Optional - Valid in z/VSE DQL Mode only.) Indicates the device type.
TAPE or DISK
The value specified in the System Option Table TYPE= option.
(Optional) Indicates the format for data on the output data set. Available formats are CSV and FIXED. CSV means variable-length records with values separated by a character specified in the CA Dataquery System Option Table. FIXED means fixed-length records. Only one EXPORT of fixed-length records is permitted per execution in Batch Sign/On Mode.
CSV or FIXED
CSV
For more information on the EXPORT function, see Using the Batch Export Function.
FIND or COUNT
(Optional - Valid in DQL Mode only.) One of these keywords must be the first word of a query input on an input statement in batch CA Dataquery, since an active found set does not exist for batch CA Dataquery. The COUNT keyword provides no print of the query.
Use only FIND or only COUNT. When using CA Dataquery through the Sign/On mode, all of the input to batch CA Dataquery is made through the use of SYSIN or SYSIPT control statements for z/OS and z/VSE respectively. Provide individual control statements for each CA Dataquery keyword, with only one keyword per statement. However, you can have an entire query on one statement if you follow these rules:
Options can be reset between queries if you are submitting several queries at one time.
Following is a sample valid query:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+
FIND ALL COMPANY +
SORT COMPANY-NUMBER +
PRINT COMPANY-NUMBER BILLED PAID
The format for the FIND keyword is as follows:
►►─ FIND ─ data ──────────────────────────────────────────────────────────────►◄
Is the rest of the user's query text.
Valid Entries: Enter a valid query text.
Default Value: (No default)
(Optional - Valid in DQL Mode only.) The COUNT keyword provides the number of rows found. The format for the COUNT keyword is as follows:
►►─ COUNT ─ data ─────────────────────────────────────────────────────────────►◄
Is the rest of the user's query text.
Enter a valid query text.
(No default)
OPTION DDBASE=
(Optional - Valid in both SQL Mode and DQL Mode.) Names the database ID of the tables specified in the batch query if different from the one defined in the CA Dataquery System Option Table. The format of the OPTION DDBASE= statement is as follows:
►►─ OPTION ─ DDBASE=nnnnn ────────────────────────────────────────────────────►◄
Is the numeric database ID.
The valid CA Datacom Datadictionary database ID that contains the table specified in the user's query.
The database ID from the CA Dataquery System Option Table.
OPTION DIAGNOSTICS=
(Optional - Valid in DQL Mode only.) Produces Compound Boolean Selection diagnostics report or the request table. The format of the OPTION DIAGNOSTICS statement is as follows:
►►─ OPTION ─ DIAGNOSTICS= ─┬─ CBS ───┬────────────────────────────────────────►◄ ├─ OFF ◄ ─┤ └─ RQT ───┘
This option specifies printing of a Compound Boolean Selection diagnostics report, the request table, or no diagnostics.
CBS, OFF, or RQT
OFF
Note: If RQT is used, this statement should follow either the FIND or EXECUTE statement. Otherwise, it should precede the FIND or EXECUTE statement.
OPTION PLANOPTS, DISPLAY
(Optional - Valid in SQL Mode only.) Specifies printing messages requested with OPTION PLANOPTS,MSG= statement. This statement should follow the EXECUTE statement.
OPTION PLANOPTS,DISPLAY
OPTION PLANOPTS,MSG=
(Optional - Valid in SQL Mode only.) Specifies the kinds of SQL Plan Optimization messages to be produced. This statement should precede the query and the EXECUTE statement.
OPTION PLANOPTS,MSG=xy
x specifies messages about preparation time and y specifies messages about execution time.
N - No messages
S - Summary messages
D - Detail messages
NN
OPTION PLANOPTS,OPT=
(Optional - Valid in SQL Mode only.) Specifies the type of join order to be used.
OPTION PLANOPTS,OPT= M
P
This option specifies that CA Dataquery use the order specified in the query or that CA Dataquery use the order chosen by SQL.
M - Order from user
P - Order from SQL
Order decided by SQL at statement preparation time.
OPTION PAGESTOGETHER=
(Optional - Valid in both DQL Mode and SQL Mode.) Used only when windowing is requested with OPTION WINDOWING statement. Specifies if the report pages are printed with all windows of a page together (1A, 1B, 1C...2A, 2B, and so forth) or all pages of a window together (1A, 2A, 3A,...1B, 2B, 3B, and so forth). The format of the OPTION PAGESTOGETHER statement is:
►►─ OPTION ─ PAGESTOGETHER= ─┬─ YES ─┬────────────────────────────────────────►◄ └─ NO ──┘
The option specifies whether to print windows of a page together (YES) or to print pages of a window together (NO).
NO or YES
Your profile value for hardcopy print options.
OPTION WINDOWING=
(Optional - Valid in both DQL Mode and SQL Mode.) Specifies the WRAP and NOWRAP selections for reports. The format of the OPTION WINDOWING statement is as follows:
►►─ OPTION ─ WINDOWING= ─┬─ YES ─┬────────────────────────────────────────────►◄ └─ NO ──┘
This option specifies whether to have CA Dataquery print the report with the lines wrapped (NO) or to have CA Dataquery to print the report without wrapping (YES).
NO or YES
Your profile value for hardcopy print options.
OPTION QUERYLANG=
(Optional - Valid in both DQL Mode and SQL Mode.) Specifies the query language (either DQL or SQL) to be used in the query. Causes the named query language to be used, rather than the last language used online. The format of the OPTION QUERYLANG= statement is as follows:
►►─ OPTION ─ QUERYLANG= ─┬─ DQL ─┬────────────────────────────────────────────►◄ └─ SQL ─┘
This option specifies the query language ID.
DQL or SQL
The CA Dataquery query language ID specified on your profile.
OPTION STATISTICS=
(Optional - Valid in both DQL Mode and SQL Mode.) Tells CA Dataquery to provide statistical information on the execution of the query or dialog. The format of the OPTION STATISTICS statement is as follows:
►►─ OPTION ─ STATISTICS= ─┬─ ALL ─┬───────────────────────────────────────────►◄ ├─ ON ──┤ └─ OFF ─┘
Specify ON to have CA Dataquery print the statistics, OFF for CA Dataquery to print no statistics page, or ALL for CA Dataquery to print extended statistics (DQL only.)
ON, OFF, or ALL
Your profile value for hardcopy print options.
OPTION QUERYTEXT=
(Optional - Valid in both DQL Mode and SQL Mode.) Specifies whether the text of the query that produced the report is printed when the report is printed. The format of the OPTION QUERYTEXT statement is as follows:
►►─ OPTION ─ QUERYTEXT= ─┬─ YES ─┬────────────────────────────────────────────►◄ └─ NO ──┘
Specifies YES to have CA Dataquery print the text of the query and NO to have CA Dataquery to print no query.
NO or YES
Your profile value for hardcopy print options.
Indicates that the report is produced on the system printer. This is the default.
SELECT and Other SQL Statements
(Optional - Valid in SQL Mode only.) These keywords must be the first word of a query input on an input statement in batch CA Dataquery if the query is to select columns from a table. Use only one of these statements: SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, ALTER, GRANT, REVOKE, and any other SQL keyword that can be used in online CA Dataquery. Use these keywords as they would be used online in a SQL Mode query. See the CA Dataquery Reference Guide for details about use.
You can have an entire query on one statement if you follow these rules:
The format for the SQL keyword is:
►►─ sqlkeyword ─ data ────────────────────────────────────────────────────────►◄
Is any valid SQL keyword (see the CA Dataquery Reference Guide).
Is the rest of your query text.
(Required) Identifies the CA Dataquery user to CA Dataquery. Only one SIGN/ON statement is allowed and it must be the first statement in the job stream. The PASSWORD keyword is required if a password is assigned, optional if there is no password.
Is the CA Dataquery user identification assigned to you.
A 1- to 32-character CA Dataquery user ID
(No default)
Is the your password, if one is assigned.
A 1- to 9-character password
(No default)
STORE
(Optional - Valid in both DQL Mode and SQL Mode.) With unique table name operand, creates a personal table from active found set of data in DQL Mode or SQL Mode. The format of STORE follows:
►►─ STORE ─ new-table-name ───────────────────────────────────────────────────►◄
A 1- to 32-character Personal Database Facility table name for the new personal table which is to contain the output from this query.
|
Copyright © 2014 CA.
All rights reserved.
|
|