Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Question Unanswered: DB2 UDB Identity Columns

    Hi Folks,

    Hope someone can help, as this has been bugging a few coleagues and myself for a while

    Right, UDB 7.1 - IDENTITY columns. How on earth do you make the link between the IDENTITY attributes and the Column using those attributes on a table?

    What I am after is the START WITH/INCREMENT values for the IDENTITY column, the column that has 'Y' in the IDENTITY column in syscolumns (i.e. the definition of IDENTITY as displayed in the DB2 tools). I've had a look thru that manuals and can't find any docs on it - I know where the values are stored and if the columns is an IDENTITY column on a table, but not how to link the two values together.

    For 390, this is easy as there is a syssequencesdep table with the column information in it, linked to the syssequences.

    If anyone can point me in the right direction, I'd really appreciate it

    My thanks in advance,
    malarky

    edit: spelling >_<
    Last edited by malarky; 03-18-03 at 07:06.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am fairly certain this is how it works in UDB. The table SYSIBM.SYSDEPENDENCIES defines the table that contains the IDENTITY column. Since any given table can only contain one IDENTITY column, to get the information you are after, you would need to join the SYSSEQUENCES, SYSDEPENDENCIES, and SYSCOLUMNS tables.
    SYSSEQUENCES and SYSDEPENDENCIES join on SEQNAME and DNAME respectively.

    The query would look something like:
    select s.*,c.tbcreator,c.tbname,c.name from sysibm.syssequences as s, sysibm.sysdependencies as d, sysibm.syscolumns as c where (s.seqname = d.dname) and (d.bname = c.tbname) and (d.bschema = c.tbcreator) and (c.identity = 'Y')

    HTH

    Andy

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    Thumbs up

    Gah! /me kicks myself

    That worked a charm, thanks for the reply!

Posting Permissions

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