Purpose
Enables users to specify whether the values of any fields associated with Identity Data are returned in response to a SQL Insert operation. Unlike other session control commands, CLI's SPB files (HSHSPB or clispb.dat) do not include a method to specify use of auto generated key retrieval. It can be invoked only at the BTEQ script level through AUTOKEYRETRIEVE command use.
Syntax
where:
|
Syntax Element |
Specification |
|
OFF |
The initial value for AUTOKEYRETRIVE is OFF. OFF is also the default value when no argument for the command is specified. |
|
ROW |
The identity column is included with the resulting row. |
|
COLUMN |
Only the identity column associated with INSERT statements is returned. |
Usage Notes
|
Using the |
Result |
|
Prior to establishing sessions |
Using the AUTOKEYRETRIEVE command prior to establishing sessions is valid. The current AUTOKEYRETRIEVE value remains in effect for all subsequent requests until explicitly changed. It is in effect across LOGONs done within the same BTEQ invocation. However, if the current database version does not support auto generated key retrieval, the setting is not used. |
Example
This example shows the effects of having executed the AUTOKEYRETRIEVE OFF command.
Given the following table definition for an empty table:
CREATE TABLE MyTable
, NO FALLBACK
, NO BEFORE JOURNAL
, NO AFTER JOURNAL
(
MyCol1 INTEGER GENERATED ALWAYS AS IDENTITY
, MyCol2 INTEGER
)
PRIMARY INDEX ( MyCol1 )
;
If the following SQL INSERT statement is submitted with AUTOKEYRETRIEVE set to OFF,
INSERT INTO MyTable VALUES(,1);
the result generated shows only an activity count and elapsed-time status messages similar to the following example:
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
Example
This example shows the effects of having executed the AUTOKEYRETRIEVE COLUMN command.
Given the following table definition for an empty table:
CREATE TABLE MyTable
, NO FALLBACK
, NO BEFORE JOURNAL
, NO AFTER JOURNAL
(
MyCol1 INTEGER GENERATED ALWAYS AS IDENTITY
, MyCol2 INTEGER
)
PRIMARY INDEX ( MyCol1 )
;
If the following SQL INSERT statement is submitted with AUTOKEYRETRIEVE set to COLUMN,
INSERT INTO MyTable VALUES(,1);
the result generated includes the value generated for the identity column.
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
MyCol1
-----------
2
Example
This example shows the effects of having executed the AUTOKEYRETRIEVE ROW command.
Given the following table definition for an empty table:
CREATE TABLE MyTable
, NO FALLBACK
, NO BEFORE JOURNAL
, NO AFTER JOURNAL
(
MyCol1 INTEGER GENERATED ALWAYS AS IDENTITY
, MyCol2 INTEGER
)
PRIMARY INDEX ( MyCol1 )
;
If the following SQL INSERT statement is submitted with AUTOKEYRETRIEVE set to ROW,
INSERT INTO MyTable VALUES(,1);
the result generated includes values for the entire row generated for the insert.
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
MyCol1 MyCol2
----------- -----------
3 1