Purpose
This control enables users to specify whether the database returns values of any fields associated with Identity Data in response to subsequent SQL Insert operations.
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
| Command | Shorthand |
|---|---|
| AUTOKEYRETRIVE | AKR |
| Option | Shorthand | Option | Shorthand |
|---|---|---|---|
| COLUMN | C | ROW | R |
where the following is true:
- 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 AUTOKEYRETRIEVE Command | 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. |
The AUTOKEYRETRIEVE command is valid in an SQL macro.
Example 1 – AUTOKEYRETRIEVE
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 2 – AUTOKEYRETRIEVE
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 3 – AUTOKEYRETRIEVE
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