The EGL get statement retrieves a single file record or database row and provides an option that lets you replace or delete the stored data later in your code. In addition, this statement allows you to retrieve a set of database rows and place each succeeding row into the next SQL record in a dynamic array.
The get statement is sometimes identified as get by key value and is distinct from other statements that begin with the word get.
If the resource is recoverable (as in the case of a VSAM file or DL/I or SQL database), the forUpdate option locks the record so that it cannot be changed by other programs until a commit occurs. For details on commit processing, see Logical unit of work.
In a clause like this one (which is outside of an #sql{ } block), do not include a semicolon before the name of a host variable.
If you do not specify a usingKeys clause, the key-value component of the implicit statement is based on the SQL record part that is either referenced in the get statement or is the basis of the dynamic array referenced in the get statement.
In the case of a dynamic array, the items in the usingKeys clause (or the host variables in the SQL record) must not be in the SQL record that is the basis of the dynamic array.
The usingKeys information is ignored if you specify an explicit SQL statement.
emp.empnum = 1; // sets the key in record emp try get emp forUpdate; onException myErrorHandler(8); // exits the program end emp.empname = emp.empname + " Smith"; try replace emp; onException myErrorHandler(12); end
try get emp singleRow into empname with #sql{ select empname from Employee where empnum = :empnum }; onException myErrorHandler(8); end
try get emp forUpdate into empname with #sql{ select empname from Employee where empnum = :empnum }; onException myErrorHandler(8); // exits the program end emp.empname = emp.empname + " Smith"; try replace emp; onException myErrorHandler(12); end
In the CICS® for MVS/ESA™ environment, the get position is lost when a commit or rollback is issued, or following a converse statement if running in segmented mode.
Details on the get statement depend on the record type. For details on DL/I processing, see DLISegment record. For details on SQL processing, see SQL record.
get myCustomer, myLocation, myOrder;EGL will generate the following pseudo-DL/I code from this statement:
GU STSCCST*D (STQCCNO = :myCust.customerNo) STSCLOC (STQCLNO = :myLocation.locationNo) STPCORD
When you issue a get statement against an indexed record, the key value in the record determines what record is retrieved from the file.
If the file is a VSAM file, the EGL get statement (with the forUpdate option) prevents the record from being changed by other programs. In z/OS® batch programs, the lock remains until a commit occurs, which may not happen until the job step ends. In iSeries™ COBOL programs, the lock remains until a commit occurs, which may not happen until the end of the run unit, as described in Run unit.
When you read an MQ record in a message queue with the get keyword, EGL automatically:
On IMS, a get statement for a serial record assigned to a single-segment message queue results in a get unique (GU) call to the I/O PCB. This GU call results in an automatic commit point. The first get statement for a serial record assigned to a multiple-segment message queue results in a GU call to the I/O PCB. Subsequent get statements result in GN calls until an NRF (no records found at this segment level) condition is reached. The first get statement after the NRF results in another GU call, and the process continues until an EOF is reached. Each GU call results in an automatic commit point.
A get statement reads the record following the last record read in the entry sequence. The first record is read for the first scan of a file. If the record accessed on the previous I/O operation was the last record in the file, the get statement returns EOF.
The file is closed and reopened whenever the program changes from adding to reading or from reading to adding. When the file is closed, the file position is lost; therefore, the first get statement after an add statement will read the first record from the file. Similarly, an add that follows a get or get next statement will add a record to the beginning of the file.
In the zSeries® batch environment, a get statement for a serial record assigned to a GSAM file results in a get next (GN) call to the GSAM database. If a variable-length serial record is in a file associated with GSAM and the record length is longer than the physical file, DL/I returns a blank status code. Data is truncated, but no message is issued because the situation cannot be detected.
The EGL get statement results in an SQL SELECT statement in the generated code. If you specify the singleRow option, the SQL SELECT statement is a stand-alone statement. Alternatively, the SQL SELECT statement is a clause in a cursor, as described in SQL support.
When you specify an SQL record as an I/O object for the get statement but do not specify an explict SQL statement, the implicit SQL SELECT has the following characteristics:
SELECT column01, column02, ... columnNN FROM tableName WHERE keyColumn01 = :keyItem01 FOR UPDATE OF column01, column02, ... columnNN
INTO :recordItem01, :recordItem02, ... :recordItemNN
EGL derives the SQL INTO clause if the SQL record is accompanied by an explicit SQL SELECT statement when you have not specified an INTO clause. The items in the derived INTO clause are those that are associated with the columns listed in the SELECT clause of the SQL statement. (The item-and-column association is in the SQL record part, as noted in SQL item properties.) An EGL INTO clause is required if a column is not associated with an item.
keyColumn01 >= :keyItem01 & keyColumn02 >= :keyItem02 & . . . keyColumnN >= :keyItemN
Related concepts
DL/I database support
Logical unit of work
Record types and properties
References to parts
resultSetID
SQL support
Related tasks
Syntax diagram for EGL statements and commands
Related reference
#dli directive
add
close
CICS-related considerations
delete
EGL statements
Exception handling
execute
get next
get next inParent
get previous
I/O error values
open
prepare
replace
set
SQL item properties
terminalID