Identity Column Question

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
tmecheski (9)
8/24/2004 1:28:18 PM
comp.sys.ibm.as400.misc 9110 articles. 13 followers. Post Follow

13 Replies
766 Views

Similar Articles

[PageSpeed] 25
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
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
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
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
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
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
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
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
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
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
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
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
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
tmecheski (9)
8/26/2004 7:06:55 PM
Reply:
Similar Artilces:

Question about how calculate numbers in batch
I have around 2000 variables. I want all of the 2000 variables divided by the last variable(total). I can do some tedious work like: var1=var1/total; var2=var2/total; ......... However, that will take too much time. Is there an easier way to deal with this? By the way, my varialbe names are not like var1, var2. So I can't do this by loop, since it's not a numbered range list. But it does have the common name prefix. Thank you very much! On Sep 8, 11:13=A0am, greenwillow <yangliuy...@gmail.com> wrote: > I have around 2000 variables. I want all of the 20...

edit box question
Hello all. I would like to know how to print only 3 decimal digits on a edit box in gui by using set(handles.edit4,'String',gf(2)); "gf" is class double. Thank you in advance. leequid wrote: > Hello all. > > I would like to know how to print only 3 decimal digits on a edit box in gui by using > set(handles.edit4,'String',gf(2)); > "gf" is class double. > > Thank you in advance. uicontrol('style', 'edit', 'string', sprintf('%.3f', 4.45617)) ...

how does ident function?
Hi all, When I was using 'ident' gui for identification. I found the variables and estimated models were not available in workspace until I dragged the icon in 'ident' to the 'to workspace' area. Can anyone tell me where are those data stored before they show up in workspace? Thanks, Yang ...

Date Format Question
Hi, I need to enter date into a control without a month day - just mm/yyyy. I tried to use format mm/yyyy. It displays date OK, but when I edit, it shows dd/mm/yyyy. What's the corret way o doing that? Thank in advance, AK Internally, the date/time field is stored as a floating point number, where the integer represents the date and the fractional part is the time of day. It is therefore impossible to store a date without the day. Setting its Format only affects how it is displayed, not how it is stored. If you really want to store month and year, use 2 number fields: TheMon...

Lotus Notes/Domino Frequently Asked Questions (FAQ) Site #4
This is an automated monthly reminder message for new users. The Lotus Notes/Domino Frequently Asked Questions (FAQ) Site has answers to the most commonly asked questions in this newsgroup. http://www.keysolutions.com/NotesFAQ Please check it before posting a question. It is updated at least once per month and includes pointers to other resources. You should also check the online Lotus KnowledgeBase at http://www.ibm.com/software/lotus/support ...

Toolbar Customization questions
Win98SE, PSP 8.0 Couple of questions from a long-time, part-time, non-artist, user of PSP: 1) Is there some way I can sort the Commands in the Toolbar's Customize window? It's not bad enough not knowing which Category to use for Commands, but the haphazard sorting of commands is frustrating. 2) How can I insert a Separator (vertical grey bar) between icon groups on a Toolbar? Thanks, Don -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- On...

Sequence/Identity
Hi All! It is possible in Oracle 8i to define a column(called ID) in a table as been value-autogenerated or Identity(as in SqlServer)? Is there any alternative to the sequences for generating the new IDs for my tables ? Ernest "Ernest Morariu" asked .. > It is possible in Oracle 8i to define a column(called ID) in a table as been > value-autogenerated or Identity(as in SqlServer)? > Is there any alternative to the sequences for generating the new IDs for my > tables ? Use a sequence in combination with before-insert trigger for your table ...

Compaq Contura Aero Frequently Asked Questions #3
Archive-name: pc-hardware-faq/laptops/compaq-aero Posting-Frequency: 57 days Last-modified: 2002/04/29 Version: 3.31 URL: http://www.zenspider.edu/~pwilk/aero/aero-faq.txt ------------------------------ Subject: Introduction Compaq Contura Aero Frequently Asked Questions Version 3 Editors: Philip Wilk (Version 3) Current Ekkehard Rohwedder (Version 2.0) Renee Roberts (Version 1.0) To send submissions, corrections or deletions for this FAQ, please email pwilk-faq@zenspider.com or post to the aero mailing list. This document is to be freely distributed. ...

ARB_multisample question
I want Full Scene Antialiasing in my sample program. I know that I must use ARB_multisample extension. Is this portable such as OpenGL? I saw that I must use WGL_ARB_pixel_format extension which is only for MS Windows. If I want my program portable in all Operating Systems, I must not use FSAA??? Thanks ...

scheme n00b: how to implement 4-column table?
Hi -- I want to implement a table of records where each record has these fields: town_name (string) latitude (float) longitude (float) adjacent_towns (list of strings) The table will have a fixed number of records and only needs to be read from, not updated. I'll need to do lookups on the town name field and retrieve any of the other fields. If I were using C, I'd use an array of structures. What's the equivalent data type in scheme? I imagine I could use a list of lists, where each sublist is one record. I read about property lists in a lisp book; do those exist in scheme?...

Opensolaris patch question...
Hi, What Opensolaris version should I run at home? Solaris Express, Community Edition or Developer Edition? Since it will be my firewall/nat, I need to be able to apply latest security patches, is this possible with Opensolaris? I haven't seen any patch tools or strategies mentioned when it comes to Opensolaris.. Regards, Daniel On Sat, 24 Feb 2007, kertby@gmail.com wrote: > What Opensolaris version should I run at home? Solaris Express, > Community Edition or Developer Edition? It's up to you, but I'd say the DE might be best (most stable). Just don't install all...

Fortran C++ interop. question
Hi, everyone. I have a simple question about this subject. I'm writing my first programs using interoperability and I'm facing the following situation: In the fortran program which calls the C++ functions I have the interface block interface real(c_double) function cfunc(x) bind(c) use iso_c_binding real(c_double) :: x end function cfunc subroutine cfunc2(x,y) bind(c) use iso_c_binding real(c_double) :: x, y end subroutine end interface (function cfunc2 has void type). The thing is that x isn't e...

[Please read!] Questions? First read The Ultimate MSX FAQ! #2
Hello MSX freaks! This posting is to remind you that before you ask any questions, you should read The Ultimate MSX FAQ. This FAQ is available on the web only at the following URL: http://www.faq.msxnet.org/ By the way, this is an automated posting, it is sent the 1st and 15th of every month. -- Grtjs, Manuel PS: MSX 4 EVER! (Questions? See: http://www.faq.msxnet.org/) PPS: Visit my homepage at http://bilderbeek.cjb.net/ ...

Eclipse IDE Question
Hi All, I am new to Eclipse and it is mystifying me why the Build Project and Build All items on the Project menu are grayed out! I am able to run and debug but can't figure out to compile a JAR file! Anyone? Thanks! On 6-7-2008 8:03, hayes.randy@gmail.com wrote: > Hi All, > > I am new to Eclipse and it is mystifying me why the Build Project and > Build All items on the Project menu are grayed out! I am able to run > and debug but can't figure out to compile a JAR file! > > Anyone? > > Thanks! In the Project menu, have you also seen the check mark i...

Re: M.P.E. Trivia; Date Question
On Wednesday, October 29, 2003, at 04:21 PM, Michael Anderson wrote: > Oct. 29, 1971 (a.k.a.: 19711029) Does this date mean anything to any of > the M.P.E. old-timers? I was in the lab at that time but didn't pay any attention to that date or the event it relates to. Jonathan Bale or Bob Green may be able to help. Also, I have been contacted 2 or 3 times over the last twenty years or so by some HP historians(?) who interviewed me for what little I could remember about my 12+ years with HP (between Aug 1969 and Oct 1981). Maybe somebody at HP Corporate could help you. ...

Overriding __init__() questions
What's the difference between doing something calling A.__init__(self) like in the following... [cdalten@localhost ~]$ python Python 2.4.3 (#1, Oct 1 2006, 18:00:19) [GCC 4.1.1 20060928 (Red Hat 4.1.1-28)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> class A: .... def __init__(self): .... pass .... >>> class B(A): .... def __init__(self, x): .... A.__init__(self) .... self.x = x .... print x .... >>> x=B(5) 5 >>> y=A(5) Traceback (most recen...

tabular* right-aligned column with width
Hello, I'm trying to create a tabel. it is centered, 18 cm wide, and two colums the first column must be right-aligned with a width of 9 cm the second column must be left-aligned with a width of 9 cm The problem is: I can't make a right-aligned column, or I just don't know how to do this: this is what I do now: \begin{center} \begin{tabular*}{18cm}{@{\extracolsep{\fill}}rp{9cm}} plaats: & DFF vxfdsf \\ telefoon: & 1239283 \\ \end{tabular*} \end{center} Unfortunalty it doesn't right-align the first column at 9cm, but it creates a big gap between t...

Read
Hello, does anybody know, if there is a limit for the number of columns that "READ" can read. I tried the following with N = 800 and it jumps to 200: READ (IO1, FMT = *, END = 100, ERR = 200) (VECTOR (INDEX), INDEX = 1, N) If there is a limit, is it possible to increase it? I use FTN90 if this is of importance. Thank you, Matthias "Matthias Kehlenbeck" <matthias.kehlenbeck@stud.uni-hannover.de> wrote in message news:c8oaci$osc$02$1@news.t-online.com... > Hello, > > does anybody know, if there is a limit for the number of columns that > "READ&...

RE: Interpretation of onstat -g ses, hostname column #3
Colin, You surprise me. I thought that us old wizened practitioners could still remember our hex conversions. Immediately I saw the numbers in hex I was converting them into decimal. And Mike, I thought you would have known better as well. It's all part of the development plot to make it look a lot more complicated than it is. BTW All, If you follow the max number of extents calculation in the performance guide you no longer have to do the hex conversion. Oncheck gives the address in decial separated by a colon, but nobody has told the tech writers of the change. An...

Design Question: Inheritance or Accessors
Hi everybody, I know this is not usually the place for design questions, but I was wondering what some of you more experienced people would do. Say I have an application that has a C++ & STL core, with a user interface that could be command-line (on topic, I guess) or even .NET/QT/Whatever UI (obviously off-topic, here). One of the classes in the core stores some data, in a vector. I want to display that information to the user. Is it preferable to provide an accessor for the private data, e.g.: std::vector<T> GetData() const { return storage_; } With which the UI can obta...

wxEmbedded/Handheld question
Hi, I am searching around to find any possible emulator software that also works on M$ Windows. I have found some links for microwindows or Nano X window. But these seems not to run on MS Windows. Possibly links to src links with documentation how to build it under windows. Thanks Lothar lothar.behrens <at> lollisoft.de <lothar.behrens <at> lollisoft.de> writes: > I am searching around to find any possible emulator software that also > works > on M$ Windows. I'm not sure which part of of wxEmbedded you are interested in, but wxPalmOS...

Identical functions not identical ?!
Dear Group, I define two plots of the same function, but with different range for the independent variable. Combining them without options, the two plots match. But if I want to enlarge a certain range of the independent variable (using PlotRange) deviations become visible. Why is that and how can I suppress that ? Thanks a bunch in advance, Frank Frank Richter ******************************************************** Technical University of Berlin Department of Nonmetallic-Inorganic Materials Vitreous Materials Sekretariat ES 4 Englische Strasse 20 10587 Berlin ...

iDVD vs DVDStudioPro Fast Question
I need to make a presentation out of a series of still images and MP3 files. The MP3 files are the audio for the presentation (voice over) in 3 separate languages. The client wants a menu with "Play in English" "Play in Spanish" and "Play in Chinese". The user selects one item, and the presentation plays; at the end of each "chapter" he wants the DVD to return to the main menu. Is there a way to do this in iDVD? I don't necessarily need "multiple language tracks" support >if< I can program iDVD to return to the main menu at ...

top unix question
I am trying to do a top and then extract the data from this line: CPU states: % idle, % user, % kernel, % iowait, % swap when you save this info to a file its never there. Does anyone know how to capture this. thanks vaede.n@gmail.com wrote: > I am trying to do a top and then extract the data from this line: > > CPU states: % idle, % user, % kernel, % iowait, % > swap > > when you save this info to a file its never there. Does anyone know > how to capture this. > thanks > top | tee <filename> On Oct 3...

question about using CCS3.1
hi all, i use a pointer to specify a buffer on my pc and load data to this buffer using *buf_ptr but when i try to re-run this program the buffer still holds the old value which was build from last time are there any ways that i can clean the used memory block or i just need to re-open the CCS everytime i need a re-run and another question: can i see the line numbers of the program when i edit my code? just like the feature in DevC or gvim(set nu) thx sincerely, David ...