Topic
16 replies Latest Post - ‏2014-08-08T20:52:08Z by CRPence@vnet.ibm.com
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

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

    Re: Identity colum and CPYF command

    ‏2006-08-02T22:33:23Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Identity colum and CPYF command

      ‏2006-08-03T14:09:56Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Identity colum and CPYF command

        ‏2006-08-03T23:27:45Z  in response to SystemAdmin
        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
    ACCEPTED ANSWER

    Re: Identity colum and CPYF command

    ‏2006-08-04T03:57:50Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Identity colum and CPYF command

      ‏2006-08-04T13:21:30Z  in response to SystemAdmin
      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
      ACCEPTED ANSWER

      Re: Identity colum and CPYF command

      ‏2009-05-26T18:21:29Z  in response to SystemAdmin
      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
      • CRPence@vnet.ibm.com
        25 Posts
        ACCEPTED ANSWER

        The FROMRCD() parameter determines arrival vs keyed AccPth order

        ‏2014-08-08T20:52:08Z  in response to SystemAdmin

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

  • Damery
    Damery
    40 Posts
    ACCEPTED ANSWER

    Re: Identity colum and CPYF command

    ‏2014-08-05T12:51:35Z  in response to SystemAdmin

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

    Re: Identity colum and CPYF command

    ‏2014-08-05T16:07:38Z  in response to SystemAdmin

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

      Re: Identity colum and CPYF command

      ‏2014-08-05T16:46:44Z  in response to imagolfer

      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
        40 Posts
        ACCEPTED ANSWER

        Re: Identity colum and CPYF command

        ‏2014-08-05T19:06:39Z  in response to imagolfer

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

          Re: Identity colum and CPYF command

          ‏2014-08-05T22:27:42Z  in response to Damery

          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
            40 Posts
            ACCEPTED ANSWER

            Re: Identity colum and CPYF command

            ‏2014-08-06T13:52:00Z  in response to imagolfer

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

              Re: Identity colum and CPYF command

              ‏2014-08-06T19:23:42Z  in response to Damery

              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
                441 Posts
                ACCEPTED ANSWER

                Re: Identity colum and CPYF command

                ‏2014-08-06T21:17:07Z  in response to imagolfer

                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
                  40 Posts
                  ACCEPTED ANSWER

                  Re: Identity colum and CPYF command

                  ‏2014-08-07T19:07:49Z  in response to krmilligan

                  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.