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
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')