AUTOKEYRETRIEVE

Basic Teradata Query Reference

brand
Teradata Tools and Utilities
prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

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



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.

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