Re: iSeries SQL and identity columns

Apparently the ToolBox implements that query for isAutoIncrement() function. Just a guess that the noted query being run is a defect in the given scenarios. See the APAR SE44759 "TOOLBOXJAVA-JT400-ACCESS-JDBC-INCORROUT ISAUTOINCREMENT() METHOD UNEXPECTED BEHAVIOR" with IBM i 7.1 PTF "R710 SI41966 1000"; 1000 indicates that PTF is not available on a cumulative, although by name, that might just be the latest supersede rather than the originally corrective fix. Regarding my suggestion of "defect", specifically noted in the PTF is that "Unless the 'extended metadata' connection property is set to 'true', Toolbox should not attempt to query the DB for the isAutoIncrement value but it is."
http://www-01.ibm.com/support/docview.wss?uid=nas27b7d3d0c72cf1a128625778a003c760d

Refer to the APAR SE44306 for IBM i 6.1 with v6r1 PTF SI40986 or latest supersede SI41957.

Note: The matching SQL built with the expression
ps = con_.prepareStatement("SELECT identity_generation " +
"FROM QSYS2" + getCatalogSeparator() + "SYSCOLUMNS"
...
is found here:
http://www.koders.com/java/fid81A0CF93B369CADA274E280970FBB3736873E62C.aspx?s=CurrencyTypeDescriptor

Regards, Chuck

On 2/1/11 12:01 PM, Alan Campin wrote:

I see the same statement coming from our JDBC drivers. Why the
driver would care is beyond me.

On Tue, Feb 1, 2011 at 12:33 PM, CRPence wrote:

On 2/1/11 10:16 AM, terry_bullard@xxxxxxxxxxxx wrote:

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 = ?

<<SNIP>> 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???

<<SNIP>>

The database would have no need to perform such a query, so the
statement is very _unlikely_ to have been "system-generated" by\at
the server. Every TABLE has a "record format" that defines whether
the file has an IDENTITY column, thus there is no reason for the
database to query if\which column has that attribute, since that
information is available directly from the TABLE itself. Some
client application might have some "need" to perform such a query,
but I could not guess why.