Identity Column Question
Tim Mecheski
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
files.
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
insert?
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.
Thanks.
-Tim
Terence
no duplicates in the logicals if they are keyed UNIQUE.
Birgitta Hauser
> if a WRITE handles Identity Value Columns differently than an SQL
> insert?
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.
Birgitta
Charles Wilt
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?
HTH,
Charles
In article <eedf536c.04082...@posting.google.com>,
tmec...@jjillgroup.com says...
Tim Mecheski
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.
ID_COLUMN BINARY 18 0
GENERATED --- ALWAYS
ORIGINAL STARTS WITH ---- 1
CURRENT STARTS WITH ---- 1
INCREEENT BY ---- 1
MIN VALUE ---- 1
MAX VALUE -- 9223372036854775807
CYCLE NO
NUMBER OF VALUES TO CACHE 10
FLD1 BINARY 18 0
FLD2 BINARY 4 0
FLD3 CHAR 10
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?
Brian
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" <cw...@meaa.mea.com> wrote in message
news:MPG.1b965582a...@news.easynews.com...
Charles Wilt
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
problem.
In any case, I'd say it's time to call IBM.
Charles
In article <eedf536c.04082...@posting.google.com>,
tmec...@jjillgroup.com says...
Tim Mecheski
> Tim,
>
> When you say it's doing a WRITE, you mean it's using native RPG I/O via
> the WRITE opcode?
A.
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.
>
> Has the RPG program been recompiled recently?
A.
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.
> 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.
A.
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
> 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."
A.
Agreed. However, I have proven in the table that nothing has been
added or changed since it was repopulated.
>
> 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.
A.
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.
>
> Did that make sense?
A.
Thanks Charles for the input. This all makes perfect sense and I am
sure now you can see my confusion.
>
> HTH,
> Charles
>
Jonathan Ball
> 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
> files.
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.
Tim Mecheski
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.
"Brian" <no.spam.for@me> wrote in message news:<412cba0f$0$8081$a186...@newsreader.visi.com>...
Jonathan Ball
> 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.
How is this being done?
>
> 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.
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
contain
30 rows - 15 based on the copy of B/Z to A/Z, and
another
15 based on the action of the trigger program
I recognize I have made a lot of assumptions that might
be wrong.
>
> 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 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:
'OSP-DB-INCORROUT IDENTITY_VAL_LOCAL function returning
null'
See
http://www-912.ibm.com/a_dir/as4ptf.nsf/0/ad0161ad3013be2186256e9f0047835e?OpenDocument
or http://tinyurl.com/55fp6 (if the above link breaks).
Jonathan Ball
TWO business application tables (damn, I hate this
keyboard...)
Jonathan Ball
> 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.
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.
> 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.
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?
>
> 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.
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
table?
3. Does the RPG program attempt to populate the identity
column?
4. What do the contents of the identity column look like
after the rebuild?
>
> 4.) Then, reset the identity columns in this table starting at 1.
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.
>
> 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?
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.
Tim Mecheski
> Tim Mecheski wrote:
> 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.
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.
>
> 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?
It is definitely happening when the program is writing to the target
file in the production library.
>
> 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.
>
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.
Tim Mecheski
>
> 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.
>
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 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.
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
CREATE TABLE LIBNAME.SKU_DISP
SKU_DISP_ID FOR COLUMN SKU_D00001 BIGINT
GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER CACHE 10 ),
SKU_ID BIGINT NOT NULL ,
CHANNEL SMALLINT NOT NULL ,
DISP_CODE VARCHAR(10) ALLOCATE(3)
CCSID 37 DEFAULT NULL ,
TIME_ADD TIMESTAMP NOT NULL,
TIME_CHG TIMESTAMP NOT NULL ,
CONSTRAINT LIBNAME.QSYS_SKU_DISP_00001
PRIMARY KEY( SKU_DISP_ID ) ) ;
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.
>
> What is the point in "resetting" the actual identity
> column values for the table in library B before copying
> the table to library A?
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.
>
> LOTS of questions:
>
> 1. Why are you clearing this table if the records are
> identical to those in the file version in library A?
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.
>
> 2. What is the source of the data for rebuilding this
> table?
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
data.
>
> 3. Does the RPG program attempt to populate the identity
> column?
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.
>
> 4. What do the contents of the identity column look like
> after the rebuild?
Ironically, the contents of the identity column looked just fine after
a rebuild. The identity column was reset to 1 increment by 1 using an
ALTER TABLE and so as such they were sequential.
>
> >
> > 4.) Then, reset the identity columns in this table starting at 1.
>
> 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.
>
The table that is reset is the table in the staging library. I was
obviously unclear.
The table in the staging library is cleared.
The identity values are reset to 1.
The table is rebuilt.
Finally it get's copied to the production library using a CPYF
(*REPLACE)
> 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.
I believe your thought process is very similar to where I am at and
hopefully I have clarified this mess above. The end result is that we
ended up doing an ALTER TABLE with a RESET to 10,000,000 on the
production system and re-ran
the failing job. It ran just fine and while all new records that were
written to this table started with an identity value of 10,000,000
some had a timestamp of Saturday due to the fact that the job has been
failing for quite some time now.
I would imagine at this point if we left the logic exactly as is we
will probably be all set until the day when the file being rebuilt in
the staging library reaches the 10,000,000 record mark. At that
point, copying the file over to production would again cause the job
to try to reuse existing identity values. On the other hand, records
being added by the daily will continue to increment higher than the
10,000,000.
As for those of you who may be wondering why we didn't use a trigger.
Well, that's a good question. We do use triggers in many places to
fire off a message over a Websphere MQ message queue which is then
picked up by a broker designed with logic flows that then call stored
procedures to ensure that disparate systems are updated. In many
other cases, a simple DDM call is used. The moral of the story is,
somehow this one slipped through the cracks. :)
Thanks again for the input and sorry for being so long-winded, but
wanted to be thorough here. By the way, I still haven't heard back
from IBM. This particular issue has apparently been a bug in the past
and they are researching whether or not it might still be. I will let
you all know as I said before.