Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: generated identity

    DB2 LUW ESE 10.1 fp3 on P/server
    I have a table with a column :
    "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
    looking at db2look I can see the value (with cache)
    ALTER TABLE "PROJ89 "."LETTER" ALTER COLUMN "ID" RESTART WITH 2000;
    looking at : NEXTCACHEFIRSTVALUE from syscat.COLIDENTATTRIBUTES
    I can see this value is 1
    whenever an insert is done on this table the value in syscat gets the correct value 2020...
    when I execute the alter table..restart with 2021 this value gets reset to 1
    the db2look continues to reflect the correct value
    where does db2look get this value from ?
    we have a script that checks after load or any other utility that this next value is still correct and adjust if needed. with the syscat value being 1 (upto an insert we can not get the next value without doing a select with max(id)
    any ideas ?
    thanks for all reply/help
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try SYSCAT.SEQUENCES WHERE SEQID = SYSCAT.COLIDENTATTRIBUTES.SEQID
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for the update - I already tested with sysibm.syssequences
    and now with syscat.sequences
    according db2look
    ALTER TABLE "PROJ89 "."ACTOR" ALTER COLUMN "ACTOR_ID" RESTART WITH 2144;
    (0)[db2inst1@dlx00003 work]$ db2 "select char(c.TABNAME,20),s.NEXTCACHEFIRSTVALUE from SYSCAT.SEQUENCES s,SYSCAT.COLIDENTATTRIBUTES c where s.SEQID = c.SEQID and c.tabschema='PROJ89'"

    1 NEXTCACHEFIRSTVALUE
    -------------------- ---------------------------------
    ACTOR 1.
    and this value also gets reset to 1 with alter table..restart with ...
    and corrected with insert..
    also looked in db2look.bnd but no reference to these tables.. probably in SYSPROC.DB2LK_GENERATE_DDL
    it would be nice if we could look at the source code of db2look and see where they get this value...
    Last edited by przytula_guy; 05-22-14 at 10:30.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •