IBM Support

[Db2] How to retrieve restart value for ID columns.

How To


Summary

This article guides a user on how to identify restart value for ID columns.

Steps

You can use nextcachefirstvalue in syscat.colidentattributes or db2look to retrieve restart value for an ID column.
Take the following scenario for example,
  1. Create a table having ID column (C1 in this case). 
    db2 "create table DB2INST1.T1 (C1 int not null generated always as identity, C2 char(10))"
  2. Insert some data to increment ID 
    db2 "insert into DB2INST1.T1(C2) values 'a'"
In this example, restart value is 21 because of the cache value (20).
Either of the following methods can be used to retrive the restart value.
  1. List nextcachefirstvalue in syscat.colidentattributes 
    db2 "select tabschema, tabname, colname, nextcachefirstvalue, cache from SYSCAT.COLIDENTATTRIBUTES where tabname='T1'"
TABSCHEMA  TABNAME     COLNAME    NEXTCACHEFIRSTVALUE CACHE
---------- ----------- ---------- ------------------- ---------
DB2INST1   T1          C1                          21.       20

  1 record(s) selected.
  2. Print ALTER TABLE statement to identify the restart value 
    db2look -d <db_name> -a -e -t DB2INST1.T1 | grep "ALTER TABLE"
ALTER TABLE "DB2INST1"."T1" ALTER COLUMN "C1" RESTART WITH 21;

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlYAAU","label":"Database Objects-\u003ETables"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
02 March 2023

UID

ibm16959861

Page Feedback