Identity Column Question

  • Permalink
  • submit to reddit
  • Email
  • Follow


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.

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
0
Reply tmecheski (9) 8/24/2004 1:28:18 PM

See related articles to this posting


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


> 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
0
Reply arrowcomputer (237) 8/24/2004 6:24:38 PM

Does anyone know
> 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
0
Reply Birgitta.Hauser (119) 8/25/2004 2:00:04 AM

Tim,

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.0408240528.3d96fa14@posting.google.com>, 
tmecheski@jjillgroup.com says...
> 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.
> 
> 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
> 
0
Reply cwilt1 (190) 8/25/2004 12:53:52 PM

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.

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?
0
Reply tmecheski (9) 8/25/2004 12:59:52 PM

I 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" <cwilt@meaa.mea.com> wrote in message
news:MPG.1b965582add166d7989707@news.easynews.com...
> Tim,
>
> 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.0408240528.3d96fa14@posting.google.com>,
> tmecheski@jjillgroup.com says...
> > 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.
> >
> > 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
> >


0
Reply Brian 8/25/2004 4:09:31 PM

Tim,

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.0408250459.211a0ae4@posting.google.com>, 
tmecheski@jjillgroup.com says...
> 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.
> 
> 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?
> 
0
Reply cwilt1 (190) 8/26/2004 12:20:58 PM

Charles Wilt <cwilt@meaa.mea.com> wrote in message news:<MPG.1b965582add166d7989707@news.easynews.com>...

> 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
>
0
Reply tmecheski (9) 8/26/2004 1:02:10 PM

Tim Mecheski wrote:

> 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.

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

0
Reply jonball (677) 8/26/2004 1:12:02 PM

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.

  

"Brian" <no.spam.for@me> wrote in message news:<412cba0f$0$8081$a1866201@newsreader.visi.com>...
> I 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.
> 
>
0
Reply tmecheski (9) 8/26/2004 1:21:23 PM

Tim Mecheski wrote:
> 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).

> 
> 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$a1866201@newsreader.visi.com>...
> 
>>I 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.
>>
>>

0
Reply jonball (677) 8/26/2004 1:55:54 PM

Jonathan Ball wrote:

> Tim Mecheski wrote:
> 
>> 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

TWO business application tables (damn, I hate this 
keyboard...)


>   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).
> 
>>
>> 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$a1866201@newsreader.visi.com>...
>>
>>> I 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.
>>>
>>>
> 

0
Reply jonball (677) 8/26/2004 1:58:38 PM

Tim Mecheski wrote:
> 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.

> 
> 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$a1866201@newsreader.visi.com>...
> 
>>I 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.
>>
>>

0
Reply jonball (677) 8/26/2004 6:13:20 PM

Jonathan Ball <jonball@whitehouse.not> wrote in message news:<CklXc.866$W_5.767@newsread1.news.pas.earthlink.net>...
> 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.
0
Reply tmecheski (9) 8/26/2004 7:06:55 PM
comp.sys.ibm.as400.misc 9044 articles. 12 followers. Post

13 Replies
427 Views

Similar Articles

[PageSpeed] 54


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

Identity Column Question #2
Jonathan Ball <jonball@whitehouse.not> wrote in message news:<4LpXc.1049$W_5.925@newsread1.news.pas.earthlink.net>... > > 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 exami...

question on inserting a record on sql server with identity column as key
Hi, All: Please help. I use sql server as back end and Access 2003 as front end (everything is DAO). A table on SQL server has an identity column as the key. We have trouble on adding records to this table using the following SQL. strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D, E FROM myTableonAccessLocal" db.execute strSQL The schema of the table "myTableOnSQLServer" and the schema of the table "myTableonAccessLocal" are all the same except that the "myTableOnSQLServer" has an identity column (ID). The key of the "m...

Existing columns vs. Null columns question
I have a get function that looks like this: /** * Magic function that gets properties * @param mixed $var The property that should be retrieved * @return mixed The value of the property. Returns null if property doesn't exist */ function __get($var) { $special_properties = array('name'); if (!isset($this->data[$var]) && !in_array($var,$special_properties)) { echo "Warning: $var is not a property for ".get_class($this)."!\n"; return null; } else{ switch ($var){ case 'name': return $this->data['firs...

Clustered index on the identity column or a datetime column
Hello all, We have a table with about 2 million rows that is used to store log events. The table has an identity column and also a datetime column to record the event time. It is expected to at least double in size over its lifetime. Because the datetime records the current time, the value of that column goes up as the value in the identity column goes up. When doing searches on the table it is normal to have a date range as part of the search. This would lead me to think that the clustered index of the main table should be on the datetime column, letting SQL Server know that a date range is...

To make an existing column become an identity column
Hi (SQL Server 2000) I have an existing table (t) with a column that is NOT an identity column (t.ID), but it has manually inserted "row numbers". I want to make this column become an identity column. This column is a key field to other tables, so I want to keep the row numbers that are allready inserted. From the Query Analyzer, how do I do this? Thanks in advance! Regards, Gunnar V�yenli EDB-konsulent as NORWAY Try this. Copy the table by using Export Data. Then using the copied table set your column to identity and see if any of the numbers change. Dave "Gunnar V�...

questions about columns
I need to make the text version of my resume slim so it can fit into online forms and look nice. I tried M-x set-fill-prefix which seems to work but M-x set-fill-column gives me the error: set-fill-column requires an explicit argument but doesn't give me a chance to set it. How do I? Also, is there a way from within emacs to do something like the "pr" command (formats text into columns of a given width and page length)? In other words, can emacs massage text into a newspaper-like format? I'm using version: XEmacs 21.4 (patch 19) "Constant Variable" [Lucid] (i486...

identity column
Hi, I would like a piece of advice. I have 3 foreign keys in a table used as primary keys for this table. Is it useful in that case to have just one identity column that would be used as the unique primary key, thus no need to have 3 primary keys ? Advantages vs Drabacks ? Regards Sam (samuel.berthelot@voila.fr) writes: > I would like a piece of advice. > I have 3 foreign keys in a table used as primary keys for this table. > Is it useful in that case to have just one identity column that would > be used as the unique primary key, thus no need to have 3 primary keys > ? > ...

Identity...I need to get the last (or highest number in Identity column)...
Ok, I just need to know how to get the last record inserted by the highest IDENTITY number. Even if the computer was rebooted and it was two weeks ago. (Does not have to do with the session). Any help is appreciated. Thanks, Trint SELECT IDENT_CURRENT('table_name') ; -- David Portas SQL Server MVP -- "trint" <trinity.smith@gmail.com> wrote in message news:1127164340.720014.70160@g14g2000cwa.googlegroups.com... > Ok, > I just need to know how to get the last record inserted by the highest > IDENTITY number. Even if the computer was rebooted and it was ...

Identity columns
I have been using the following query to identify the IDENTITY columns in a given table. (The query is inside an application.) select column_name from information_schema.columns where table_schema = 'user_a' and table_name = 'tab_a' and columnproperty(object_id(table_name), column_name, 'IsIdentity') = 1 This works. When "user_a" performs the query, everything is OK. Now, another user wanted to use the same application. So, "user_b" clicks on a button, and the exact same query as above is run. (No substitutions are made; user_b is trying to se...

Shifting data from columns and removing question mark characters from columns
I moved data from a MS-Word Table into a MS-Access table using VBA code. Almost all of the word documents had five rows for first table in the document, but few Word documents had six rows for their first table which I did not know earlier. I created the Access table with five columns to hold data from each corresponding row of the first table of the Word document. Now, the documents which had six rows in their first table have their data flowing into sixth column of the Access table which was for storing data of table 2 from the Word document. I had something like Word Document A whic...

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 Tony Do wrote: > 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 > > You can execute the statement VALUES(IDENTITY_VAL_LOCAL()) Good luck, -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== ...

Identity question
Fairly often, when I click a different identity in the dropdown box, then do a selective download, the POP server for the 'old' identity is used. I can't see why this happens unpredictably. Thanks. -- Sheldon Isaac lose all .control when replying Shel wrote: > Fairly often, when I click a different identity in the dropdown box, then > do a selective download, the POP server for the 'old' identity is used. This might be an un-expected side-effect of "When checking for mail, performs checks for other identities" on the new identity....

questions about columns
I need to make the text version of my resume slim so it can fit into online forms and look nice. I tried M-x set-fill-prefix which seems to work but M-x set-fill-column gives me the error: set-fill-column requires an explicit argument but doesn't give me a chance to set it. How do I? Also, is there a way from within emacs to do something like the "pr" command (formats text into columns of a given width and page length)? In other words, can emacs massage text into a newspaper-like format? I'm using version: XEmacs 21.4 (patch 19) "Constant Variable" [L...

Question on Idenity Columns
Is there a way to query any system tables, like sysobjects, for User tables with Identity columns? We have a SQL2000 server that we want to replicate. One of my tasks is to identify all tables with Identity columns that are missing the 'not for replication' clause. Thanks, JoeyD JoeyD (joeydba@yahoo.com) writes: > Is there a way to query any system tables, like sysobjects, for User > tables with Identity columns? We have a SQL2000 server that we want to > replicate. One of my tasks is to identify all tables with Identity > columns that are missing the 'not for repl...

Unique() columns question
I have what I fear is an embarrassingly simple question regarding removing duplicate columns from a matrix. I've been playing around with the unique() function to no avail and am still relying on a very slow nested for-loop scheme. I tried adapting some other solutions posted here regarding removing duplicate rows, but those didn't work for me. I have a matrix that has 1800 columns and 3000 rows. There are sets of columns that are identical (same values in the same order). I want to keep all unique columns and only one column from duplicate each set. if m = [ 0 1 1 1 1 1 0 ...

row,column question
If I have a 4x4 matrix and I postmultiply by {{1,0,0,bx}} I actually get something like col1 + bx*col4 Is there any way of telling Mathematica to show the result in a symbolic form using (for example, rows and columns) in this case. NB. I've been playing with the AaddOn NCAlgebra, but not managed it with that either. thanks JB In article <eqs94u$ihh$1@smc.vnet.net>, John Biddiscombe <biddisco@cscs.ch> wrote: > If I have a 4x4 matrix and I postmultiply by {{1,0,0,bx}} I actually get > something like col1 + bx*col4 > > Is there any way ...

Question about ident service
Hey, I got a quick question regarding java code and ident service. If I just do a quick Socket connection to an ftp server, like Socket s = new Socket("127.0.0.1", 1234); the ftp-server doesnt receive an ident response, but if I from a prompt do a telnet 127.0.0.1 1234 the ftp-server does get an ident response. Can anyone point me to any info which can explain to me why that is and how I can get the ident response to work when connection from java also ? Thanks in advance Steen In article <15aade9b-e97e-4be1-aa66-dd6091be0ba1@v37g2000vbv.googlegroups.com&g...

Question on column matrices
For a given row matrice of n columns (each column an integer between 1 and 20), is there a command to join the columns side by side to make one number? Please see the example below. Example: [ 3 5 6] =>> apply the command =>> 356 [ 3 10 34] =>> apply the command =>> 31034 > For a given row matrice of n columns (each column an integer > between > 1 and 20), is there a command to join the columns side by side to > make one number? Please see the example below. > > Example: [ 3 5 6] =>> apply the command =>> 356 > [ 3 10 34] =>> a...

Column totaling question
I am missing something here. I have a tab that is a filtered locator. I have a temp field that totals the each column in the browse. As I begin typing, the browse gets smaller as the filtering begins. The Total field does not update itself. How do I get that chore done?? Kent Baldwin using C5.5PEH ABC WENATVMPBQND@spammotel.com Hi Set your total to a special condition and make the condition the same as the filter condition Regards Ben "Kent Baldwin" <WENATVMPBQND@spammotel.com> wrote in message news:h4ldiv0h6ouja5suq9ee5udppbr6hi2jqu@4ax.com... > I am missing som...

questions for column vector
well, i have to create 255 column vectors of 255x1 size which should be like these, 0;0;0;0;...;0 (255 times of zeros) 1;1;1;1;...;1 (255 times of ones) ... 255;255;255;...;255 (255 times of 255) what i think leaves unexploited the computational skills of matlab. So, what do you think can i improve the following ? alex0 = zeros(255,1) alex1 = ones(255,1) alex2 = 2*alex1 ... alex255 = 255*alex1 thanks a lot Dim Howbout: vec = (0:255)'; result = repmat(vec,1,255); grandfather <dimpallas@gmail.com> wrote in message <31025107.1202831320079.JavaMail.jakarta@nitrogen.mathfor...

Column length question.
Dear Experts, As I know it is possible to use in stored procedures a column type definition for a variable declaration like the following: myVariable tableName.columnName%TYPE; Is it possible (and how) to use a column size definition? Something like: IF (length(myVariable) > tableName.columnName%SIZE) THEN .... do something .... END IF; Regards, Serguei. On Nov 21, 4:14 pm, Serguei.Goumeni...@cibc.ca wrote: > Dear Experts, > As I know it is possible to use in stored procedures a column type > definition for a variable declaration like the following: > ...

Question About Column Charts
I have a column chart with two series: 1) Number Of Applications 2) Number of Completed Applications. These are in a column format, so it looks like one bar, but colors outline the two numbers. Now I want to have a percentage (Completed Apps/Number Of Apps) over the related column. I tried added a third series, but it just doesnt look right. Any other ideas? TIA Brian BerkshireGuy wrote: > I have a column chart with two series: > > 1) Number Of Applications > 2) Number of Completed Applications. > > These are in a column format, so it looks like one bar, but colors...

Matrix where all columns are identical
I want to make a matrix with all of the columns identical to each other. Is there a quick way to do this? On Sep 4, 4:25=A0pm, "Jonas " <jonas...@gmail.com> wrote: > I want to make a matrix with all of the columns identical to each other. = Is there a quick way to do this? %construct constant column m =3D 5; x =3D ceil(rand(m,1)*5); % generated randomly for example % produce the matrix. n =3D 5; % desired columns of the matrix y =3D repmat(x, 1,n); % y is m*n "Jonas " <jonasmyo@gmail.com> wrote in message <h7r800$hcs$1@fred.mathworks.com>... > I w...

Text Column question
Hello, If I create a table with two columns of type "Text" will that cause any issues? If my memory serves me correctly I thought I read a while back that is it standard practice to only have one "Text" column per table. Any input would be appreciated. Thanks, Rob Panosh On 1 Sep 2006 06:05:01 -0700, rob wrote: >Hello, > >If I create a table with two columns of type "Text" will that cause any >issues? If my memory serves me correctly I thought I read a while back >that is it standard practice to only have one "Text" column per tab...