AS/400 >> Identity Column Question

by tmecheski » Tue, 24 Aug 2004 21:28:18 GMT

I am relatively new to using embedded SQL in RPG so please forgive me
if this seems like an obvious question or problem. Listed below is
some background information as well as my question.

I have an RPGIV program written by someone else on an iSeries machine
running V5R2 that uses embedded SQL for most of it's processing to
declare a cursor, fetch the cursor and perform it's processing logic.
The sole purpose of this program is to update a single table's values
as records are added, changed or deleted during the week in other

Every Saturday there is a job that runs here to reset the identity
values of these same tables in a different library and then early
Monday morning these records are copied over to our production library
to replace what it there. Again, during the week a job runs nightly
to perform updates.

My question is this, the program that I am referring to uses SQL for
most all of it's processing. However, at the point that it needs to
do an INSERT it is instead doing a WRITE. I do not know why this
decision was made, but we haven't had problems with this program until
now so it strikes me as odd that it is failing now. Does anyone know
if a WRITE handles Identity Value Columns differently than an SQL

My assumption would have been that the assignment of an identity
column would be a DB2 function. However, we are getting duplicate
record errors at the point of the WRITE even though the identity
column isn't referenced in this program. So, I am wondering if
changing this write to an insert statement would fix our problem as I
have checked the index that we are using over this table and the
record we are trying to add is truly not there. That leads me to
believe our problem MUST BE related to the identity column as the ID
being used IS in the table.

Any input would be greatly appreciated.



AS/400 >> Identity Column Question

by arrowcomputer » Wed, 25 Aug 2004 02:24:38 GMT

Also, check all logicals built over the file, and make sure there are
no duplicates in the logicals if they are keyed UNIQUE.

AS/400 >> Identity Column Question

by Birgitta.Hauser » Wed, 25 Aug 2004 10:00:04 GMT

Does anyone know

The identity column is directly connected to the database table.
Neither a SQL insert nor a RPG WRITE are setting the identity column.

It would be interesting to know how your identity column is defined. I
assume that the maximum value was reached and cycle was allowed. In
this case the identity column counting starts with the minimum value
and this causes certainly duplicate records.


AS/400 >> Identity Column Question

by Charles Wilt » Wed, 25 Aug 2004 20:53:52 GMT


When you say it's doing a WRITE, you mean it's using native RPG I/O via
the WRITE opcode?

Has the RPG program been recompiled recently?
What was the SQL used to create the table? (You can retrieve this via
iSeries Navigator & (maybe?) DSPFD)

Here's what I'm thinking, RPG I/O to a table with an identity column is
a little surprising to see. If the table was generated with GENERATED
BY DEFAULT option instead of the GENERATED ALWAYS. Then I think the
only way for the RPG to make use of the identity column would be to
WRITE the column with a NULL value. Or write the record via a logical
that didn't have the column. I'm wondering if it the RPG program might
have somehow been compiled without the ALWNUL(*USRCTL) option. I would
have thought it wouldn't compile successfully though.

Lastly, somebody else already mentioned the possibility of it cycling.
Additionally, here's a quote from the SQL manual:

"For an identity column, the database manager inserts a specified value
but does not verify that it is a unique value for the column unless the
identity column has a unique constraint or a unique index that solely
specifies the identity column."

The key phrase above is "that solely specifies". Perhaps you have a
unique composite key defined (ie. the identity column and some other
column) expecting the identity column to always be unique, but without a
specific unique constraint or index on it.

Did that make sense?


In article < XXXX@XXXXX.COM >,

AS/400 >> Identity Column Question

by tmecheski » Wed, 25 Aug 2004 20:59:52 GMT

Thanks for the feedback.

The program that I am referring to is using a logical file or view
that does not have a unique key and there is only 1 other logical file
over the PF that also does not have a unique key. The only unique
requirement for this particular file is the Identity column on the PF.

Once again, at the time of the write, the record that we are trying to
write does not exist in any of these, but somehow it appears that the
database is trying to use an existing identity value in spite of the
fact that the program doesn't refer to this column at all. That is
why I am confused. How could it possibly be using an identity value
that already exists if it supposed to automatically generate a new one
on a write? Initially, I thought that it might be related to the fact
that the file had the REUSEDLT set to (*YES), but that has since
changed and did not make a difference.

As it pertains to the identity value being assigned, I am aware that
the database assigns this automatically and that it will re-use the
values if the length is not defined large enough. That is to say,
that, if the table had an identity column defined as SMALLINT, once it
reached 32767 I believe it would start over again by assigning an
identity value of 500 and so on.

However, the table that I am referring to has the following fields and
as you will see the ID_COLUMN is a very large field so it should NEVER
wrap. We also reset these weekly beginning at 1 before we go to use
them during the week, but during the week the job now fails with a
duplicate record error.

MIN VALUE ---- 1
MAX VALUE -- 9223372036854775807


Based on the file above, the index that we are using within the
program in question is keyed by FLD1. The other logical is keyed by
FLD2, but neither of these are unique.

Any other thoughts?

AS/400 >> Identity Column Question

by Brian » Thu, 26 Aug 2004 00:09:31 GMT

did a quick test in the course of researching use of identiy column. I
used an RPGIV program (with record I/O, not SQL) to insert records into a
table that contains an identiy column. The value of the identity field
seems to be totally ignored, and the database handles the assignment of the
identity column. The identity column was defined with generate always. My
test was done on V5R2.

"Charles Wilt" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

AS/400 >> Identity Column Question

by Charles Wilt » Thu, 26 Aug 2004 20:20:58 GMT


Well that seems to pretty much cover everything I can thing of....except
have you loaded any PTFs recently? Perhaps one of them is causing a

In any case, I'd say it's time to call IBM.


In article < XXXX@XXXXX.COM >,

AS/400 >> Identity Column Question

by tmecheski » Thu, 26 Aug 2004 21:02:10 GMT

Yes, this is exactly what I am saying. It wouldn't have been my first
choice either given that the rest of the logic is using SQL, but
that's what I have to work with.

It was recompiled back on 7/27 and has worked between then and the
problem. However, the control entries right in the header
specifications of the source code have:

H AlwNull(*UsrCtl)
H DatFmt(*ISO)
H DftActGrp(*NO)

So, this should take care of that.

As indicated in my previous post, the table was created with the
identity column being GENERATED ALWAYS. We are also trying to write
to a logical over that table that doesn't have that (Identity) columm
and we are still receiving the error.

I'm wondering if it the RPG program might

Agreed. However, I have proven in the table that nothing has been
added or changed since it was repopulated.

There are only 2 logical over this table of which neither has a unique
key. The only unique key is found on the physical file and the unique
key only contains the identity column.

Thanks Charles for the input. This all makes perfect sense and I am
sure now you can see my confusion.

AS/400 >> Identity Column Question

by Jonathan Ball » Thu, 26 Aug 2004 21:12:02 GMT

The last sentence in the preceding paragraph leads me
to think the program in question is a trigger program.
I don't know if that has any importance or not.

Are you sure the duplicate key problem is happening
when the program is writing to its target file, or
could it be happening on one of the other files in the
course of the copy?

If none of the above is pertinent, I suggest you need
to identify whatever it was that changed in the system
at the point the program began failing.

AS/400 >> Identity Column Question

by tmecheski » Thu, 26 Aug 2004 21:21:23 GMT

Hi Brian,

Thanks for the feedback and for performing the test using an RPGIV
program with a write. This is the exact same scenario.

I have since gotten IBM Rochester involved with this as I have now
found that I also can no longer do even so much as an insert into this
table using SQL.

Here is what we think is happening and are now in the process of
proving or disproving. You may recall that I said the identities are
reset in a different library on Saturday and copied to production
using a CPYF (*replace) on Monday.
The problem "could potentially be" due to the fact that we are not
resetting the identity column is the production library. We are only
replacing the records.

Therefore, consider the following test...

1.) You have Library A with a table that has an identity column
defined as generated always and there are 5 records in that table.

2.) You have library B with an identical table that has an identity
column defined as generated always and there are 5 identical records
in that table also.

3.) Clear this file using CLRPFM and rebuild the data in the table
however you choose. In our case, we are using RPGIV.

4.) Then, reset the identity columns in this table starting at 1.

There can be no further activity on either of these tables before the
next step.

4.) Do a CPYF from Library B to Library A.

If you were now to add a record to the table in Library A, what would
YOU expect to see for an identity value on that record if you did a
IDENTITY_VAL_LOCAL() on that table?

I can personally see where people might answer 1, 6 or even 11 but I
don't know the correct answer.

AS/400 >> Identity Column Question

by Jonathan Ball » Thu, 26 Aug 2004 21:55:54 GMT

im Mecheski wrote:

How is this being done?

You have two steps '4.)'; THERE'S your problem. Just
kidding; sorry.

WHAT is being copied from library B to library A, and
how does this involve the target file (with the
identity column) and the RPG IV program that writes to it?

It sounds to me as if the CPYF step IS, in fact, going
to generate duplicate records, if not duplicate keys,
in this target file, if the program in question is a
trigger program. That is (simplifying, and assuming
this is a trigger issue):

- libraries A and B each contain to business application
tables (X & Y), and some kind of audit table (Z);
Z is the target table of the trigger program

- assume X contains 5 rows, Y contains 10 rows, and so
Z contains 15 audit rows

- based on what you wrote above, you copy (*replace)
B/Z to A/Z, and somehow reset the identify column values
in A/Z; this table now contains 15 rows

- now you copy B/X to A/X, and B/Y to A/Y; for each row
written to the target tables in the CPYF steps, the
RPG IV trigger program fires, and (in the absence of
true duplicate key problems) writes/inserts a row to
A/Z; at the conclusion of the CPYF steps, A/Z will
30 rows - 15 based on the copy of B/Z to A/Z, and
15 based on the action of the trigger program

I recognize I have made a lot of assumptions that might
be wrong.

I am not sufficiently familiar with identity columns in
iSeries DB2 to know about that function, or where it's
used, but a quick search revealed an IBM PTF that is
intended to solve a problem of the function returning a
null value. The PTF is SI13616, it's for V5R2, and the
APAR it fixes is SE15362. The abstract states:


or (if the above link breaks).

AS/400 >> Identity Column Question

by Jonathan Ball » Thu, 26 Aug 2004 21:58:38 GMT

onathan Ball wrote:

TWO business application tables (damn, I hate this

AS/400 >> Identity Column Question

by Jonathan Ball » Fri, 27 Aug 2004 02:13:20 GMT

im Mecheski wrote:

I still find this curious. If the table of interest in
library B also has the column specified as an identity
column, I'm not clear as to how you can "reset" the
values. I tried doing both a SQL UPDATE to an identity
column, and modifying it programmatically. The UPDATE
failed explicitly; the programmatic update of the
column didn't give me any error, but it also didn't
work: when I examined the rows after the program ran,
the values were unchanged.

I think, rather, that the problem is that you ARE
resetting the identity column in the production
library, and that there also is a unique constraint on
the column; possibly the column is the primary key of
the table. However it is you achieve the resetting of
the values in the column in library B, when you copy
the file to library A, you now have an identity column
value of 1, AND you have reset the identity attributes
of it to begin with 1 for the next insert. Because of
the (probable) unique constraint on the column, which
will *not* show up as a separate index on the table,
you're going to get a duplicate key error when the
program attempts to write a new row.

What is the point in "resetting" the actual identity
column values for the table in library B before copying
the table to library A?

LOTS of questions:

1. Why are you clearing this table if the records are
identical to those in the file version in library A?

2. What is the source of the data for rebuilding this

3. Does the RPG program attempt to populate the identity

4. What do the contents of the identity column look like
after the rebuild?

Huh? Why are you going to reset the identity column
(singular - a table may only have one such column) of
this table AFTER rebuilding it? I suppose I should
ask, WHICH table: in B, or in A? "This" table, above,
is ambiguous.

If you altered the table in A to reset the identity
column to begin with 1, and then inserted a row, I
would expect to see the function return a value of 2.
However, I still strongly suspect you have some kind of
unique constraint on the identity column, either UNIQUE
or PRIMARY KEY, and you won't be able to add a record
to the table in A, because the WRITE will fail on a
duplicate key.

AS/400 >> Identity Column Question

by tmecheski » Fri, 27 Aug 2004 03:06:55 GMT

I can see where it may appear that way based on the way that I worded
it. Sorry about that. However, this is not the case. These are two
completely different libraries each with the same file. The file is
rebuilt fresh in one library and then copied to the other. Again, I
don't know why the decision was made to do things this way in the
past, I am simply supporting it now.

It is definitely happening when the program is writing to the target
file in the production library.

Thats a really good question that neither I nor 3 or 4 other
developers here can seem to answer just yet. It is my belief that we
hitting a section of code that hasn't been executed since this program
was written a year ago and quite simply no one has ever known.

Ultimlately, we are coming down to a solution that appears that it
will work. We may simply need to do an ALTER TABLE to reset the
identitiy values on the target file as well (just like we do in source
table). That way database should never get confused as to where it
needs to start inserting records again. I will update everyone once I
hear back from IBM on this.

AS/400 >> Identity Column Question

by tmecheski » Fri, 27 Aug 2004 07:31:52 GMT

onathan Ball < XXXX@XXXXX.COM > wrote in message news:<4LpXc.1049$ XXXX@XXXXX.COM >...

When I stated the word "reset", I should have clarified by stating
that we need to do an ALTER TABLE to reset the identity columns
beginning at a value greater than anything currently used. We
actually did do this today and reran the job and everything worked
fine, but it still does not explain why clearing the file or replacing
the records should make a difference.

I believe your analysis is close to being correct although I am not
very clear on the wording exactly. We do have a primary key on the
physical file and that is the identity value as I have stated before.
In fact, listed below is the definition of the file:

-- Generate SQL -- Version: V5R2M0 020719 --
Generated on: 08/26/04 18:53:14 -- Relational Database: DBNAME
-- Standards Option: DB2 UDB AS/400


Note that the SKU_DISP_ID in this case is the identity value. I will
explain a little more about our existing process and the reasons why
we do them below, but once again note that I was not the original
person responsible for making this decision so I would certainly
entertain suggestions openly.

Given that the maximum identity value is 18 bytes long, I would say
that this is a VERY good question. I can't forsee the day when we
would EVER reach that. However, I have been told that the decision
was originally made to rebuild these semi-static files on a weekend in
a staging library to avoid heavy performance hits due to the large
size of these files. The files are also used quite extensively in
production so the decision was made to work with a static copy.

That is why the files in the staging library are cleared, reset and
rebuilt. I may have stated the order wrong before, but I think you
get the picture. Then, these rebuilt files are copied back to
production at an off peak time on Monday.

Again, another good question. As mentioned above, due to the size of
the files being rebuilt, this entire process is mostly completed in a
staging library. Quite honestly, this is really a poorly designed
process that needs to be relooked at. While the organization can
continue to use WebSphere MQ in the mix if they so desire, the
ultimate goal here is to maintain an up-to-date table as changes
occur. However, if you ask me the process has been complicated way
beyond where it should have been.

The source of the data for rebuilding this table is the production
database believe it or not. The process utilizes DDM files to access
other files on a different (the production) system to acquire it's

NO. Not at all. In fact, it doesn't even reference the identity
column in the source code at all. What I ultimately found is that it
didn't matter if I tried using a WRITE I/O operation or an INSERT. I
simply could not write to this file.

Ironically, the contents of the identity column looked just fine after
a rebuild. Th

Similar Threads

1. recreate table with identity column

I have an sql table with an identity column:

 CREATE TABLE mqahistp
   HistId               integer
                        generated always as identity,
   MsgqName             char(10),
   ... ) ;

When I add new columns to the table and recreate it I start to get
duplicate key errors on INSERTs to the table. The application that uses
the table is still being written so to get around the error I just
"delete from mqahistp" to clear the table.

I am using TURNOVER when the tables are recreated, so I am guessing
TURNOVER uses CPYF or something to recover the data of the replace
file.  Which then throws off the IDENTITY sequence value.

I have not looked into this very much. What is the recommended way in
SQL to recover data in a replaced file while retaining the values in
the identity column?


2. DB2 - OS390: Identity Column Problem

3. What is logged for insert on identity column

                    START WITH +1
                    INCREMENT BY +1
                    MINVALUE +1
                    MAXVALUE +99999999
                    NO CYCLE
                    CACHE 20
                    NO ORDER ) )
                 DATA CAPTURE CHANGES
                 IN "USERSPACE1" ;

db2 insert into myschema.t1 values (default).

Is the real value inserted in the table logged and is it possible to
retrieve it with the log analyis API? 

Bernard Dhooghe

4. Retrieving Identity Column Value with ADO Recordset s

5. Identity column

I have a table with identity column. How do I get the identity value after
the insert from ASP application. I am using windows 2000 and DB2 V7.2 Fix
pack 10

6. [JDBC] PreparedStatement and identity column

7. Identity column errors when filling in gaps


I have an identity column on a table that is filling in gaps when I
complete inserts.

For example if i had rows with these ID's


Then I completed 2 inserts, rows would be inserted with ID's of 3 and
6. My issue is that if i tried to do two inserts one after the other I
was getting the following error:

------------------------------ Commands Entered
VALUES ('Test Event', 'Test Event Description', '2007-05-26',
'4:10:00', 2, 0, 0, 1, 21, 0, 14, 18, 1, 'N', 25)@
('Test Event', 'Test Event Description', '2007-05-26', '4:10:00', 2,
0, 0, 1, 21, 0, 14, 18, 1, 'N', 25)
DB21034E  The command was processed as an SQL statement because it was
not a
valid Command Line Processor command.  During SQL processing it
SQL0803N  One or more values in the INSERT statement, UPDATE
statement, or
foreign key update caused by a DELETE statement are not valid because
primary key, unique constraint or unique index identified by "1"
table "DB2ADMIN.EVENT" from having duplicate rows for those columns.

SQL0803N  One or more values in the INSERT statement, UPDATE
statement, or foreign key update caused by a DELETE statement are not
valid because the primary key, unique constraint or unique index
identified by "1" constrains table
"DB2ADMIN.EVENT                                                      "
from having duplicate rows for those columns.


The INSERT or UPDATE object table "<table-name>" is constrained
by one or more UNIQUE indexes to have unique values in certain
columns or groups of columns.  Alternatively, a DELETE statement
on a parent table caused the update of a foreign key in a
dependent table "<table-name>" that is constrained by one or more
UNIQUE indexes.  Unique indexes might support primary keys or
unique constraints defined on a table. The statement cannot be
processed because completing the requested INSERT, UPDATE or
DELETE statement would result in duplicate column values.

 Alternatively, if a view is the object of the INSERT or UPDATE
statement, it is the table "<table-name>" on which the view
is defined that is constrained.

 If "<index-id>" is an integer value, the index name can be
obtained from SYSCAT.INDEXES by issuing the following query:

    WHERE IID = <index-id>
    AND TABSCHEMA = 'schema'
    AND TABNAME = 'table'

 where schema represents the schema portion of "<table-name>"
and table represents the table name portion of "<table-name>".

 The statement cannot be processed.  The table remains

User Response:

Examine the definition for the index identified by

 For an UPDATE statement, ensure that the specified operation is
not itself inconsistent with the uniqueness constraint.  If this
does not show the error, examine the object table content to
determine the cause of the problem.

 For an INSERT statement, examine the object table content to
determine which of the values in the specified value list
violates the uniqueness constraint.  Alternatively, if the INSERT
statement contains a subquery, the object table contents
addressed by that subquery must be matched against the object
table contents to determine the cause of the problem.

 For a DELETE statement, examine the identified dependent table
for unique constraints on foreign keys that are defined with the
rule ON DELETE SET NULL.  This table has a foreign key column
included in the identified unique index that cannot be set to
null since there is already a null in the column for that

 Federated system users: isolate the problem to the data source
failing the request (refer to the problem determination guide to
determine which data source is failing to process the SQL
statement) and examine the index definitions and data for the
conditions listed previously.

 sqlcode :  -803

 sqlstate :  23505

If I wait for a second then Insert again it seems to work o.k, also
now that the gaps are all filled the inserts are working fine, any
ideas why this was happening and how I can avoid this error in the
future? There is only a PK on the identity column, no unique
constraint's at all.

8. db2move, problem importing data into table with GENERATED ALWAYS identity column