Search code examples
db2identitygenerated

Getting Generated Always Columns list from DB2

Is there any way I get all column names and assoicated table names which has identity column set as generated always?

For I dentity columns I can simply use syscat.columns but how to fitler identity columns which has generated always vallue?

select identity, substr(tabname,1,30), substr(colname, 1, 30) from syscat.columns where tabschema='MYSCHEMA'"

From the above select list I wanted to filter only columns which uses generated values...

Solution

  • The online documentation for SYSCAT.COLUMNS. The two columns you're interested in are IDENTITY and GENERATED.

    Your query will probably be something like:

    SELECT TABNAME,COLNAME FROM SYSCAT.COLUMNS WHERE
IDENTITY='Y' AND GENERATED = 'A' AND TABSCHEMA='MYSCHEMA'