|
|
|
Issue a SELECT statement to extract data from a relational table that meets criteria you specify. You can use the SELECT statement by itself or in another statement (then called a subquery).
Note: You can use host variables for columnlist or tablename.
Use this syntax in an AOF rule or an OPS/REXX program:
ADDRESS SQL "SELECT {columnlist|*} keywords"
/* The following keyword must follow the optional INTO hostvarlist keywords, */
/* if used. You may specify more than one tablename; if you do, be sure to */
/* separate each one with a comma: */
{FROM tablename}
/* The following, if used, must follow the required columnlist or * keyword: */
[INTO hostvarlist]
/* The following, if used, must follow the required FROM tablename keywords: */
[WHERE searchcriteria]
[ORDER BY colname ASC|DESC]
[SYSTEM(ALL|EXT|sysnames)]
[SYSWAIT(seconds)]
[OUTPUT|NOOUTPUT]
[COLLIST]
[SYSPLEX]
Use this syntax to invoke the statement from a TSO terminal, a TSO/E REXX program, or a TSO CLIST:
OPSQL SELECT {columnlist|*}
/* This keyword must follow the optional INTO hostvarlist keywords (if used). */
/* You may specify more than one tablename; if you do, be sure to separate */
/* each one with a comma: */
{FROM tablename}
/* These keywords, if used, must follow the required columnlist or * keyword: */
[INTO hostvarlist]
/* These keywords, if used, must follow the required FROM tablename keywords: */
[WHERE searchcriteria]
[ORDER BY colname ASC|DESC]
[COLLIST]
[LINESIZE(20‑250)]
You may specify these operands for the SELECT statement:
Defines the column names that contain the values you want. Each column name should be separated by a comma. If you specify an asterisk (*), the CA OPS/MVS product selects all columns in the table.
(Optional) Defines the host variables (TSO, REXX, or global variables) in which to store the selected column values. The CA OPS/MVS product returns data from selected columns from left to right into the corresponding host variables.
You would not use this option in a cursor declaration, as this function would be accomplished through the FETCH statement.
Defines the name of the table from which you are selecting data. If you specify multiple tables for a join operation, separate each distinct table name with a comma. If a blank rather than a comma follows a table name, the table name that follows is considered to be an alias or correlation value of the preceding table. For more information about join operations, see the chapter “Using the Relational Data Framework” in the User Guide.
(Optional) Defines the data CA OPS/MVS looks for as it searches for the data you want to fetch from the table. The CA OPS/MVS product compares the contents of each row in the table to the searchcriteria you specify. For a description of search criteria, the chapter “Using the Relational Data Framework” in the User Guide. Then, CA OPS/MVS processes those rows whose contents meet the search criteria. For example, if you have a table that stores information about the systems at your site, specify WHERE NAME='CICS' on a SELECT statement if you want to fetch data associated with the CICS system.
(Optional). Defines the name of the column SQL uses as a guideline for sorting table data that this SELECT statement retrieves. If you also specify the ASC keyword, SQL sorts the retrieved values in ascending order, starting with the value in the colname column that comes first in the alphabet or is numerically lowest. If you specify the DESC keyword, SQL sorts the retrieved values in descending order, starting with the value in the colname column that comes last in the alphabet or is numerically highest.
(Optional) Performs cross-system SQL operations. Specify one of these values:
Routes the SQL command to all active MSF-defined systems, including the local system.
Routes the SQL command to all remote, active MSF-defined systems.
Routes the SQL command to the specified systems. You may specify from one to eight system names as the value of sysnames.
For more information, see the chapter “Using the Relational Data Framework” in the User Guide.
(Optional) Defines the number of seconds the SQL processor waits for output from a remote system. You may specify a value between 1 and 300 seconds.
Do not specify a value for SYSWAIT if you specify the SYSTEM(ALL), SYSTEM(EXT), or NOOUTPUT keywords.
(Optional) Indicates whether the command returns output to the external data queue. Specify OUTPUT to have output returned; otherwise, specify NOOUTPUT.
NOOUTPUT is implied when:
(Optional) This option can be used if SELECT * is specified. It returns column information in three additional variables:
Displays a list of column names, in column definition sequence, separated by a single space.
Displays a list of single-character type identifiers, separated by a single space. The positioning of this wordlist corresponds exactly to that of the COLUMN#LIST list, so after a name index is determined, it can be used to position to the type identifier. The identifier codes are described below.
Displays a list of column names which are defined as primary keys. This list may not correspond to either of the above, and should be referenced separately.
The definitions correspond to the datatype specification at column creation.
1 = CHAR
2 = VARCHAR
3 = HEX
4 = INTEGER
5 = SMALLINT
6 = DECIMAL
7 = DATE
8 = TIME
9 = TIMESTAMP
(Optional) Sets the maximum size of the terminal output lines returned by OPSQL when terminal output is implied by the environment in which the command is executed.
This operand is useful for setting a line size to match a sysout print line when the command is run in a batch TSO TMP with SYSTSPRT assigned to SYSOUT
Valid for: OPSQL POI command
Default for TSO: 79 characters
Default for Netview: 68 characters
(Optional) Reduces the scope of the SYSTEM(ALL|EXT) operand to MSF connected systems that belong to the same z/OS sysplex as the command issuer. The keyword has no effect on a list of explicit system names.
| Copyright © 2012 CA. All rights reserved. | Tell Technical Publications how we can improve this information |