Re: iSeries SQL and identity columns

On Tue, Feb 1, 2011 at 12:16 PM, <terry_bullard@xxxxxxxxxxxx> wrote:

Hello All,

Have been having some performance issues with web pages hitting our
iSeries. Set up SQL Exit program to be able to capture/log odbc sql
statements. Looking in my log file - the overwhelming log entries have
the following sql statement.

SELECT identity_generation FROM QSYS2/SYSCOLUMNS WHERE identity_generation
is not null AND column_name = ? AND table_name = ? AND table_schema = ?

I have never seen a statement use the identity_generation field. From
what I can tell from IBM's website - this is a col you can add when
creating a sql table and is used to support a type of "auto increment" key
value?

Yes, identity column.

So - a couple of questions? Is there a way (perhaps with Ops Navigator)
to find a list of all tables that have this identity_generation column so
I can backtrack to see what web pages are being called?

Launch run-sql-scripts tool from iNav and run these queries:

Tables with an identity column:

SELECT distinct table_schema, table_name
FROM QSYS2.SYSCOLUMNS
WHERE is_identity = 'YES'
order by table_schema, table_name ;

Identity columns:

SELECT table_schema, table_name, column_name
FROM QSYS2.SYSCOLUMNS
WHERE is_identity = 'YES'
order by table_schema, table_name ;

Also - is this a system generated statement? I ask because the developers

who have written most of our web pages are telling me they don't recognize
this either???

I highly doubt it. The query is part of the web app.