IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 16 replies
  • Latest Post - ‏2014-08-08T20:52:08Z by CRPence@vnet.ibm.com
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic Identity colum and CPYF command

‏2006-08-01T19:01:31Z |
Hi

Is there anywhere I can figure the last number that was used for the
identity col or the next number that will be used when a new record is
inserted.

when I copied some data to the table with identity col using CPYF
command in CL, It did not update the identity counter/value that has to
be genereated.

Do I need to always alter table and reset the value in a scenario like that

TIA
Updated on 2009-05-26T18:21:29Z at 2009-05-26T18:21:29Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Identity colum and CPYF command

    ‏2006-08-02T22:33:23Z  
    me wrote:
    > Hi
    >
    > Is there anywhere I can figure the last number that was used for the
    > identity col or the next number that will be used when a new record is
    > inserted.

    I don't believe so. At least I've never found a way.

    >
    > when I copied some data to the table with identity col using CPYF
    > command in CL, It did not update the identity counter/value that has to
    > be genereated.

    That right, CPYF inserts are like using OVERRIDING SYSTEM VALUE clause
    on SQL inserts.

    >
    > Do I need to always alter table and reset the value in a scenario like that

    Yes. Here's pseudo-code for the reset utility we use:

    1. Lock the table
    2. Query, selecting the max-value of the identity column
    3. Alter the table, using the identity column reset clause to set the
    next value the max-value plus one
    4. Unlock the table

    Beyond that, you can get fancy and query the system catalog to discover
    which column is the identity column and handle other variables like
    max-value, min-value, starting-value and increment (positive and negative).

    >
    > TIA
    >
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Identity colum and CPYF command

    ‏2006-08-03T14:09:56Z  
    me wrote:
    > Hi
    >
    > Is there anywhere I can figure the last number that was used for the
    > identity col or the next number that will be used when a new record is
    > inserted.

    I don't believe so. At least I've never found a way.

    >
    > when I copied some data to the table with identity col using CPYF
    > command in CL, It did not update the identity counter/value that has to
    > be genereated.

    That right, CPYF inserts are like using OVERRIDING SYSTEM VALUE clause
    on SQL inserts.

    >
    > Do I need to always alter table and reset the value in a scenario like that

    Yes. Here's pseudo-code for the reset utility we use:

    1. Lock the table
    2. Query, selecting the max-value of the identity column
    3. Alter the table, using the identity column reset clause to set the
    next value the max-value plus one
    4. Unlock the table

    Beyond that, you can get fancy and query the system catalog to discover
    which column is the identity column and handle other variables like
    max-value, min-value, starting-value and increment (positive and negative).

    >
    > TIA
    >
    Brian

    Thanks for the input

    Do you do the process you mentioned in the command prompt, or is it
    automatically done using some change management software whenever you
    recreate the table
    Brian wrote:
    > me wrote:
    >
    >> Hi
    >>
    >> Is there anywhere I can figure the last number that was used for the
    >> identity col or the next number that will be used when a new record is
    >> inserted.
    >
    >
    > I don't believe so. At least I've never found a way.
    >
    >>
    >> when I copied some data to the table with identity col using CPYF
    >> command in CL, It did not update the identity counter/value that has
    >> to be genereated.
    >
    >
    > That right, CPYF inserts are like using OVERRIDING SYSTEM VALUE clause
    > on SQL inserts.
    >
    >>
    >> Do I need to always alter table and reset the value in a scenario like
    >> that
    >
    >
    > Yes. Here's pseudo-code for the reset utility we use:
    >
    > 1. Lock the table
    > 2. Query, selecting the max-value of the identity column
    > 3. Alter the table, using the identity column reset clause to set the
    > next value the max-value plus one
    > 4. Unlock the table
    >
    > Beyond that, you can get fancy and query the system catalog to discover
    > which column is the identity column and handle other variables like
    > max-value, min-value, starting-value and increment (positive and negative).
    >
    >>
    >> TIA
    >>
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Identity colum and CPYF command

    ‏2006-08-03T23:27:45Z  
    Brian

    Thanks for the input

    Do you do the process you mentioned in the command prompt, or is it
    automatically done using some change management software whenever you
    recreate the table
    Brian wrote:
    > me wrote:
    >
    >> Hi
    >>
    >> Is there anywhere I can figure the last number that was used for the
    >> identity col or the next number that will be used when a new record is
    >> inserted.
    >
    >
    > I don't believe so. At least I've never found a way.
    >
    >>
    >> when I copied some data to the table with identity col using CPYF
    >> command in CL, It did not update the identity counter/value that has
    >> to be genereated.
    >
    >
    > That right, CPYF inserts are like using OVERRIDING SYSTEM VALUE clause
    > on SQL inserts.
    >
    >>
    >> Do I need to always alter table and reset the value in a scenario like
    >> that
    >
    >
    > Yes. Here's pseudo-code for the reset utility we use:
    >
    > 1. Lock the table
    > 2. Query, selecting the max-value of the identity column
    > 3. Alter the table, using the identity column reset clause to set the
    > next value the max-value plus one
    > 4. Unlock the table
    >
    > Beyond that, you can get fancy and query the system catalog to discover
    > which column is the identity column and handle other variables like
    > max-value, min-value, starting-value and increment (positive and negative).
    >
    >>
    >> TIA
    >>
    me wrote:
    > Brian
    >
    > Thanks for the input
    >
    > Do you do the process you mentioned in the command prompt, or is it
    > automatically done using some change management software whenever you
    > recreate the table
    >

    In our development environment the programmer has to remember to run the
    reset utility when needed. For upgrades at customer sites the reset is
    part of the installation process.
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Identity colum and CPYF command

    ‏2006-08-04T03:57:50Z  
    Does the IDENTITY_VAL_LOCAL function help at all?

    me wrote:
    > Hi
    >
    > Is there anywhere I can figure the last number that was used for the
    > identity col or the next number that will be used when a new record is
    > inserted.
    >
    > when I copied some data to the table with identity col using CPYF
    > command in CL, It did not update the identity counter/value that has to
    > be genereated.
    >
    > Do I need to always alter table and reset the value in a scenario like that
    >
    > TIA
    >
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Identity colum and CPYF command

    ‏2006-08-04T13:21:30Z  
    Does the IDENTITY_VAL_LOCAL function help at all?

    me wrote:
    > Hi
    >
    > Is there anywhere I can figure the last number that was used for the
    > identity col or the next number that will be used when a new record is
    > inserted.
    >
    > when I copied some data to the table with identity col using CPYF
    > command in CL, It did not update the identity counter/value that has to
    > be genereated.
    >
    > Do I need to always alter table and reset the value in a scenario like that
    >
    > TIA
    >
    Kent

    Yes it will help , if I need to use to get the number that is
    generated.,I was looking for a way to see it from a command prompt if
    possible(but the issue is a lil more/differne now)
    The issue I ran it to happens when the tables with identity cols are
    moved thru our change management system.
    if the table is modified and moved thru our change management system ,
    it backups up the old data , create a new object and copy the data from
    the backup to the new one using a CPYF command.

    The CPYF command will not update the identity col value after copying
    the records.( I saw an APAR# SE13418 talking about this issue.)

    So when new records are inserted we will run into duplicate key exception.

    The option I have is to use an ALTER command instead of using the CREATE
    TABLE if I have to modify the table. The only concern we have is then
    the changes(in a course of time) for one table will not be in one source
    member .

    Kent Milligan wrote:
    > Does the IDENTITY_VAL_LOCAL function help at all?
    >
    > me wrote:
    >
    >> Hi
    >>
    >> Is there anywhere I can figure the last number that was used for the
    >> identity col or the next number that will be used when a new record is
    >> inserted.
    >>
    >> when I copied some data to the table with identity col using CPYF
    >> command in CL, It did not update the identity counter/value that has
    >> to be genereated.
    >>
    >> Do I need to always alter table and reset the value in a scenario like
    >> that
    >>
    >> TIA
    >>
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Identity colum and CPYF command

    ‏2009-05-26T18:21:29Z  
    Does the IDENTITY_VAL_LOCAL function help at all?

    me wrote:
    > Hi
    >
    > Is there anywhere I can figure the last number that was used for the
    > identity col or the next number that will be used when a new record is
    > inserted.
    >
    > when I copied some data to the table with identity col using CPYF
    > command in CL, It did not update the identity counter/value that has to
    > be genereated.
    >
    > Do I need to always alter table and reset the value in a scenario like that
    >
    > TIA
    >
    Hi Kent,
    I established primary key constraint on a physical file. When I did a CPYF to a new library, the records are copied in the primary key order.? At the same time when I did a CRTDUPOBJ, the records were kept in the arrival sequence.
    Why there is a difference?
    Please clarify.

    Thanks
  • Damery
    Damery
    44 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-05T12:51:35Z  

    Here is my issue,

    I have QA and DEV environments with SQL tables that use identity fields and primary foreign keys which are the identity fields.

    I want to copy a subset from QA to DEV but I need to either keep the identity fields because of the foreign key relationships or I need to create new identities but then how do I associate the children's foreign keys?

    I am not copying the whole file just a subset of data by a date range. What can I do if the DEV environment has already used the identity used in QA?(duplicates)

    *Update: I also sample from Production but when I restore the Table the next Identity counter is 1 which blows up on the next insert. Can we alter the table to the max value?

    Updated on 2014-08-05T15:21:34Z at 2014-08-05T15:21:34Z by Damery
  • imagolfer
    imagolfer
    5 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-05T16:07:38Z  

    This SQL statement will list the tables with an identity column:

    select table_schema,table_name,column_name,is_identity, identity_generation
    from qsys2.syscolumns
    where is_identity = 'YES'
    order by table_schema,table_name,column_name
    ;

    This SQL statement will list the next identity value for tables in a schema:

    select TABLE_SCHEMA,TABLE_NAME,NUMBER_ROWS,NUMBER_DELETED_ROWS,NEXT_IDENTITY_VALUE
    from qsys2.syspartitionstat
    where table_schema = '[schema name]' and NEXT_IDENTITY_VALUE > 0;

    I've reset the next identity value using this SQL statement:

    alter table [schema name].[table name]
    alter column [identity column name] restart with 1;

    An alternative is

    alter table [schema name].[table name]
    alter column [identity column name] restart with (select max([identity column name]) from
    [schema name].[table name]);

    I hope this helps.

  • imagolfer
    imagolfer
    5 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-05T16:46:44Z  
    • imagolfer
    • ‏2014-08-05T16:07:38Z

    This SQL statement will list the tables with an identity column:

    select table_schema,table_name,column_name,is_identity, identity_generation
    from qsys2.syscolumns
    where is_identity = 'YES'
    order by table_schema,table_name,column_name
    ;

    This SQL statement will list the next identity value for tables in a schema:

    select TABLE_SCHEMA,TABLE_NAME,NUMBER_ROWS,NUMBER_DELETED_ROWS,NEXT_IDENTITY_VALUE
    from qsys2.syspartitionstat
    where table_schema = '[schema name]' and NEXT_IDENTITY_VALUE > 0;

    I've reset the next identity value using this SQL statement:

    alter table [schema name].[table name]
    alter column [identity column name] restart with 1;

    An alternative is

    alter table [schema name].[table name]
    alter column [identity column name] restart with (select max([identity column name]) from
    [schema name].[table name]);

    I hope this helps.

    Correction to last SQL example.  It should be:

    alter table [schema name].[table name]
    alter column [identity column name] restart with (select (max([identity column name]) + 1) from [schema name].[table name]);

     

  • Damery
    Damery
    44 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-05T19:06:39Z  
    • imagolfer
    • ‏2014-08-05T16:46:44Z

    Correction to last SQL example.  It should be:

    alter table [schema name].[table name]
    alter column [identity column name] restart with (select (max([identity column name]) + 1) from [schema name].[table name]);

     

    I tried the ALTER TABLE with the sub select statement and it bombed. The syntax says it can only use "WITH numeric-constant". I could maybe prepare and execute.

    But you seem to be pointing me down the same hole I am looking at.

    PROD can start with identity = 1

    ETL process can sample PROD and insert into DEV overriding system values (to keep the identity #'s the same)

    DEV will need to ALTER and RESTART at 1000000000 or a number high enough not to cause problem for a few years.

    then our TEST QA will ALTER and RESTART at 2000000000 etc.

    then I can insert data from PROD to DEV to TEST without much worry of duplicate identity columns.

    *The big worry is when PARENT CHILD relationships are created between PROD and DEV/TEST etc. then you might have a PROD Parent with a DEV child. If you clean up and remove the PROD data from DEV then you could abandon or get a foreign key constraint error from the child. The cleanest would be to delete all data in DEV/TEST and then sample PROD and then create new DEV TEST data each stretch. always RESTARTING the DEV and TEST identity numbers.

  • imagolfer
    imagolfer
    5 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-05T22:27:42Z  
    • Damery
    • ‏2014-08-05T19:06:39Z

    I tried the ALTER TABLE with the sub select statement and it bombed. The syntax says it can only use "WITH numeric-constant". I could maybe prepare and execute.

    But you seem to be pointing me down the same hole I am looking at.

    PROD can start with identity = 1

    ETL process can sample PROD and insert into DEV overriding system values (to keep the identity #'s the same)

    DEV will need to ALTER and RESTART at 1000000000 or a number high enough not to cause problem for a few years.

    then our TEST QA will ALTER and RESTART at 2000000000 etc.

    then I can insert data from PROD to DEV to TEST without much worry of duplicate identity columns.

    *The big worry is when PARENT CHILD relationships are created between PROD and DEV/TEST etc. then you might have a PROD Parent with a DEV child. If you clean up and remove the PROD data from DEV then you could abandon or get a foreign key constraint error from the child. The cleanest would be to delete all data in DEV/TEST and then sample PROD and then create new DEV TEST data each stretch. always RESTARTING the DEV and TEST identity numbers.

    Sorry about the sub-select. I guess I "mis-remembered".  We had to update identity values on our backup system some months back because it wasn't keeping the values in sync.

    Last week we replaced data in some files on our development system with data from our production system.  The files all had identity fields with foreign key references within the file group (mostly).  Our process for each file was:

    1. Disable all foreign key constraints on the file. (CHGPFCST)
    2. Delete records in the file.
    3. Alter the identity field attribute from GENERATE ALWAYS to GENERATE BY DEFAULT (which is now our new standard)
    4. Alter the next identity value to 1.
    5. We used SQL to insert records from the production file into the development file.  No field list was specified on the insert or the select.  Step 3 allows the insert to retain the identity field value in the production file records.
    6. Re-enable foreign key constraints. (CHGPFCST) The CHECK default is to validate the constraint data so we'd know if there was a problem.
    7. Alter the identity field attribute back to GENERATE ALWAYS (This is our choice based on the CMS we're using.)

    This worked for us.  The order of the files is important, i.e. parents before children, etc.  All of this was done inside an SQL script.

  • Damery
    Damery
    44 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-06T13:52:00Z  
    • imagolfer
    • ‏2014-08-05T22:27:42Z

    Sorry about the sub-select. I guess I "mis-remembered".  We had to update identity values on our backup system some months back because it wasn't keeping the values in sync.

    Last week we replaced data in some files on our development system with data from our production system.  The files all had identity fields with foreign key references within the file group (mostly).  Our process for each file was:

    1. Disable all foreign key constraints on the file. (CHGPFCST)
    2. Delete records in the file.
    3. Alter the identity field attribute from GENERATE ALWAYS to GENERATE BY DEFAULT (which is now our new standard)
    4. Alter the next identity value to 1.
    5. We used SQL to insert records from the production file into the development file.  No field list was specified on the insert or the select.  Step 3 allows the insert to retain the identity field value in the production file records.
    6. Re-enable foreign key constraints. (CHGPFCST) The CHECK default is to validate the constraint data so we'd know if there was a problem.
    7. Alter the identity field attribute back to GENERATE ALWAYS (This is our choice based on the CMS we're using.)

    This worked for us.  The order of the files is important, i.e. parents before children, etc.  All of this was done inside an SQL script.

    Does disabling the foreign key constraint allow you to copy children before parents? Then just get checked when you re-enable constraints at step 6?

    *Note you can get around the Generate Always issue by using OVERRIDING SYSTEM VALUES when you insert the copied data.

    Updated on 2014-08-06T13:53:19Z at 2014-08-06T13:53:19Z by Damery
  • imagolfer
    imagolfer
    5 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-06T19:23:42Z  
    • Damery
    • ‏2014-08-06T13:52:00Z

    Does disabling the foreign key constraint allow you to copy children before parents? Then just get checked when you re-enable constraints at step 6?

    *Note you can get around the Generate Always issue by using OVERRIDING SYSTEM VALUES when you insert the copied data.

    I haven't tried it.  Seems like it should work ok if all constraints for the file (as a child and as a parent) are disabled.  Update the thread if it doesn't.

  • krmilligan
    krmilligan
    450 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-06T21:17:07Z  
    • imagolfer
    • ‏2014-08-06T19:23:42Z

    I haven't tried it.  Seems like it should work ok if all constraints for the file (as a child and as a parent) are disabled.  Update the thread if it doesn't.

    Disabling the constraint would allow orphaned child values to exist without a matching parent key value.  When the constraint is enabled, DB2 does have to verify that all of the existing data meets RI rules - this could take a while on a large table.

    After a copy operation involving a table with Identity column, IBM did recently provide a system stored procedure, RESTART_IDENTITY (https://ibm.biz/BdFrQ5) that automates the process of propagating the next identity value from source to the target table.

  • Damery
    Damery
    44 Posts

    Re: Identity colum and CPYF command

    ‏2014-08-07T19:07:49Z  

    Disabling the constraint would allow orphaned child values to exist without a matching parent key value.  When the constraint is enabled, DB2 does have to verify that all of the existing data meets RI rules - this could take a while on a large table.

    After a copy operation involving a table with Identity column, IBM did recently provide a system stored procedure, RESTART_IDENTITY (https://ibm.biz/BdFrQ5) that automates the process of propagating the next identity value from source to the target table.

    too bad it doesn't also allow you to set the identity to the maximum value, for when you copy from source into a target file and the target file already has identity numbers higher than the source. 

    I am thinking like PRODUCTION to DEV to QA/TEST environments.

    so if production is using 1 to 999,999,999 then dev can use 1,000,000,000 to 1,999,999,999 without collision. incase you want to keep your DEV data but want a fresh update from production.

  • CRPence@vnet.ibm.com
    37 Posts

    The FROMRCD() parameter determines arrival vs keyed AccPth order

    ‏2014-08-08T20:52:08Z  
    Hi Kent,
    I established primary key constraint on a physical file. When I did a CPYF to a new library, the records are copied in the primary key order.? At the same time when I did a CRTDUPOBJ, the records were kept in the arrival sequence.
    Why there is a difference?
    Please clarify.

    Thanks

    Response offered specifically to this old and apparently unanswered question above, about the order of copied data.

    The default for Copy File (CPYF) is to request From Record (FROMRCD) as the special-value *START.  That special value indicates that for a non-keyed file, the member data is copied using the Arrival Sequence Access Path, but if the file is keyed then the member data is copied using the Keyed Access Path.  To ensure the Arrival Sequence is used to copy the data from the FROMMBR() and FROMFILE(), specify that the copy request should start at the Relative Record Number of one; i.e. specify explicitly the FROMRCD(1) on the CPYF request.  Note also that a copy using the Keyed AccPth will compress deleted records implicitly whereby if I recall correctly, although the Compress-out Deleted Records (COMPRESS) parameter defaults to *YES, if the fast-copy interface is used, then the effect is the same [in implementation and thus] result as the Create Duplicate Object (CRTDUPOBJ) request with DATA(*YES) [and of course if FROMMBR(*ALL) TOMBR(*FROMMBR) on CPYF] whereby the RRNs [both active and deleted records] are maintained; the fast-copy feature is defeated by specifying an Error Level (ERRLVL) of something other than zero and/or if the requester of the copy can not obtain an immediate-wait-timeout *EXCLRD [Exclusive allow-read] lock.  I believe possibly other means exist to prevent fast-copy; discussed elsewhere, and I seem to recall even mentioned in some docs [as one would hope to be the case, but the nomenclature fastcopy may not be easily searched\located].