Programming Languages on Power

Power Programming Languages

IBM Power, including the AIX, IBM i, and Linux operating systems, support a wide range of programming languages, catering to both traditional enterprise applications and modern development needs.


#Power

 View Only
Back to discussions
Expand all | Collapse all

Clean up identity to add unique key and debug

  • 1.  Clean up identity to add unique key and debug

    Posted Tue August 09, 2022 08:58 AM
    We have a table that was defined with the natural key as a primary key even though the table was defined with an identity column. No constraint was added to keep the identity column unique. Now, the identity column has duplicates and I've been asked to clean it up before adding a unique constraint. I'm sure someone has run into this before. Has anyone created a stored procedure to fix? If so, can you share your code or point to a web example?

    Also, how are stored procedures debugged? Any videos available on the web?

    ------------------------------
    Patrick Conner
    ------------------------------

    #SQL


  • 2.  RE: Clean up identity to add unique key and debug

    Posted Tue August 09, 2022 11:10 AM
    First order of business would be fixing the code to prevent those duplicate IDs from being inserted. This can be done by using the "overriding user value" syntax combined with the identity_val_local() function. 
    Why do this?
    Well, overriding user value simply ignores the value we pass to the insert statement allowing the database to use the next available sequence it has ready for the identity column. If the system chooses the ID value, how do we know what ID was just inserted? identity_val_local() will tell us this in the event that it's needed.
    How can we update the sequence number that the system will use to insert into the table? Reset identity syntax. I don't have the exact syntax at the moment as it's not used often, but you can set the sequence to start at whatever number you want. I would set it to a number higher than the number of records in the table. This should prevent further duplicate records from being created.

    Once the programs are fixed and the identity is reset, the next order of business would be the cleanup.
    This can be done programmatically. A testable approach would be to recreate the master table in a test schema without any data.
    Once the table is setup, create a program that scrolls through every record in the production master table. For every record, insert into your test master table.
    After this is done, you should have a copy of the master table with unique IDs. A production record can be linked to a test record using the natural key. Since that link can be made, each production ID can be replaced with it's linked ID from the test table.
    This is all under the assumption that the ID is not used as a foreign key value.

    Were almost done now. The last piece would be to make the tables key the ID column and then create an index over the natural key. This should complete the transformation.

    With all these steps, the table should be safeguarded against this happening again.

    ------------------------------
    Mike Zaringhalam
    ------------------------------

    Original Message


  • 3.  RE: Clean up identity to add unique key and debug
    Best Answer

    Posted Tue August 09, 2022 02:22 PM
    You can do the following:
    1. Retrieve the CREATE TABLE Statement (with the ACS Wizard)
    2. Modify the GENERATED ALWAYS for the Identity Column to GENERATE BY DEFAULT
    3. After you will be able to change the (duplicate) Identity values.
    4. I'd suggest to CREATE a SEQUENCE which starts with a value higher than the maximum Id.
    5. After you can run an UPDATE Statement replacing the duplicate Ids out of the SEQUENCE
    6. Change the GENERATED BY DEFAULT clause back to GENERATED ALWAYS, rerun your CREATE OR REPLACE TABLE Statement and Restart the Identity counter

    The following example works for me:
    Create OR Replace Table Hscommon05.Testid (
         Id Integer Generated Always As Identity (Start With 1,  Increment By 1, 
                                                      No Order, No Cycle, 
                                                      No Minvalue, No Maxvalue, Cache 20)
                          Not Null Not Hidden, 
         Text Varchar(10) Default '' Not Null Not Hidden);
         

Insert into HSCOMMON05.TestId (Text) Values('XXX'), ('YYY'), ('ZZZ'), ('WWW');

Alter Table Hscommon05/Testid Alter Column Id Restart With 3;

Insert into HSCOMMON05.TestId (Text) Values('AAA'), ('BBB');

Create or Replace Sequence Hscommon05.Seqtestid 
       As Integer Start With 10 ;

Create OR Replace Table Hscommon05.Testid (
         Id Integer Generated By Default As Identity (Start With 1,  Increment By 1, 
                                                      No Order, No Cycle, 
                                                      No Minvalue, No Maxvalue, Cache 20)
                          Not Null Not Hidden, 
         Text Varchar(10) Default '' Not Null Not Hidden);
      
   
Update HSCOMMON05.TestId b
   set Id = Next Value For HSCOMMON05.SeqTestId
   Where rrn(b) in (Select Max(rrn(a))
                       from HSCOMMON05.TestId a
                       Group by Id 
                       Having Count(*) > 1);  


Create OR Replace Table Hscommon05.Testid (
         Id Integer Generated Always As Identity (Start With 20,  Increment By 1, 
                                                      No Order, No Cycle, 
                                                      No Minvalue, No Maxvalue, Cache 20)
                          Not Null Not Hidden, 
         Text Varchar(10) Default '' Not Null Not Hidden);  
         


Insert into HSCOMMON05.TestId (Text) Values('EEE'), ('FFF');                      ​


    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------

    Original Message


  • 4.  RE: Clean up identity to add unique key and debug

    Posted Tue August 30, 2022 10:26 AM
    Thanks for the feedback. I was called away from the task where I can use your feedback. I will let you know how things go once I'm back on this.

    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


  • 5.  RE: Clean up identity to add unique key and debug

    Posted Thu September 29, 2022 05:41 PM

    I have to distribute the code to multiple sites. Although, I've written plenty of queries, I'm not a SQL PL'er. Can I get your thoughts on the following code? I tested the code and I get the expect results, but please comment on style and functionality.

    begin
  declare startVal bigInt;
  declare dupId bigInt;

  update_loop: LOOP

    set dupId = (
    select "ID" from lib1.F1 a
    where rrn(a) in (select Max(rrn(b))
                     from lib1.F1 b
                     Group by "ID"
                     Having Count(*) > 1)
    order by "ID"
    fetch first 1 row only
    );
    
    if (dupId is null) then 
      leave update_loop;
    end if;

    set startVal = ( select max( "ID" ) + 1 from lib1.F1 );

    alter table lib1.F1 alter column "ID" set generated by default;
    
    create schema tempJP;
    execute immediate 'create sequence tempJP.tempJPSeq as bigInt start with ' || startVal;

    update lib1.F1 a
    set "ID" = next value for tempJP.tempJPSeq
    where rrn(a) in (select Max(rrn(b)) from lib1.F1 b Group by "ID" Having Count(*) > 1);

    drop schema tempJP;

    alter table lib1.F1 alter column "ID" set generated always;

  end LOOP;

end;


    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


  • 6.  RE: Clean up identity to add unique key and debug

    Posted Wed August 10, 2022 05:04 AM
    Edited by Satid Singkorapoom Wed August 10, 2022 05:08 AM
    Dear Partick

    >>>>  Also, how are stored procedures debugged? Any videos available on the web? <<<<

    This recent article should be a good start for you :  https://www.itjungle.com/2020/06/08/guru-graphical-debugging-through-acs/   

    The articles that I attach herewith may provide additional useful information on the debugger.

    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------

    Attachment(s)

    pdf
    SQL-Enhanced SQL Graphical Debugger.pdf   174 KB 1 version
    pdf
    SQL-DB2i Graphical Debugger.pdf   551 KB 1 version
    Original Message


  • 7.  RE: Clean up identity to add unique key and debug

    Posted Tue August 30, 2022 10:26 AM
    Thanks for the feedback. I was called away from the task where I can use your feedback. I will let you know how things go once I'm back on this.

    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


  • 8.  RE: Clean up identity to add unique key and debug

    Posted Mon October 03, 2022 09:05 AM
    I get "Attempting connection to 'Server1' error... I tried looking at the job documented at the top of the window, but no messages are in the job. Is this an authority issue? Where should I look for error messages?


    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


  • 9.  RE: Clean up identity to add unique key and debug

    Posted Tue October 04, 2022 05:53 AM
    Edited by Satid Singkorapoom Tue October 04, 2022 05:59 AM
    Dear Patrick

    I Google with "ibm i graphical debugger strdbg" and find this IBM i Technote that may help with your problem : Authorities Requried for Debugging Using the Graphical (GUI) Debugger


    This document provides the authorities required for debugging using the graphical or GUI debugger.

    Authorities Required for Debugging

    The user profile that you use to sign on to an iSeries system in the workbench iSeries logon dialog must have the following authorities:

    o *USE authority to the Start Debug (STRDBG) command.
    o *USE authority to the End Debug (ENDDBG) command.
    o *USE authority to the Start Service Job (STRSRVJOB) command.
    o *USE authority to the End Service Job (ENDSRVJOB) command.
    o Either *CHANGE authority to the program or Java class being debugged, or *USE authority to the program being debugged and *SERVICE special authority.

    If the job that you are debugging is running under a different user profile than the user profile you use to sign on to the iSeries system from the debugger, the user profile that you use to sign on to the iSeries system from the debugger must have the following authorities: 

    o *USE authority to the user profile that the job you are debugging is running under.
    o *JOBCTL special authority if you do not explicitly use fully qualified program names (library/program). In other words, if you use *CURLIB or *LIBL or you do not specify a library name, you need to have *JOBCTL special authority.

    The group profile QPGMR gives you the correct authority to the STRDBG, ENDDBG, STRSRVJOB, and ENDSRVJOB commands and *JOBCTL special authority.


    I also find a 13-minute video on IBM i GUI Debugger on Youtube. Not sure if it will be useful for you or not.

    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------

    Original Message


  • 10.  RE: Clean up identity to add unique key and debug

    Posted Tue October 04, 2022 08:53 AM
    Thanks for the feedback. I can use all those commands when connecting through 5250. I connect with the same user profile via ACS. I'm going to post this problem in a different group, the IBM i group, since this subject is not SQL based.

    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


  • 11.  RE: Clean up identity to add unique key and debug

    Posted Mon October 03, 2022 08:37 AM
    Edited by Patrick Conner Mon October 03, 2022 12:14 PM


Related Content