Re: iSeries SQL and identity columns

Thanks Chuck - We are on V5R4 so I'll look to see if this holds true for
our given release as well.

Thanks again,



Terry Bullard – Applications Programmer Analyst

Phone (910) 630-5644 / (GTN) 571-5644
Email: Terry_Bullard@xxxxxxxxxxxx

"Contains Confidential and/or Proprietary Information.
May Not Be Copied or Disseminated Without Express Consent of The Goodyear
Tire & Rubber Company."



From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Date: 02/01/2011 10:06 PM
Subject: Re: iSeries SQL and identity columns
Sent by: rpg400-l-bounces@xxxxxxxxxxxx



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.