Dear collective wisdom,

we use DB2 and EGL-programs on iSeries as the backbone of our web services. Our tables have ID column with unique index constraint. Typically option "generated always with identity" is not used. (Changing table design is not an option here.) Instead, we query current max key and then do the insert by adding 1 to the max key. Something like the following:

The problem is that if two users are concurrently doing the inserts, we may receive duplicate key error (SQL code -803). What would be a solid I am looking for a solid pattern for handling keys when inserting rows. As far as I know, EGL does not allow locking table between getCurrentMaxID() and insert. While we could write insert SQL manually and use select max(ID)+1 in the insert, then we would not know which ID was inserted.

The best idea so far is to try adding record, catch slqCode 803, call getCurrentMaxID() again and try insert again. Any better ideas?

You could put a trigger on the table and let the trigger determine the unique ID. It would be a "before insert" trigger so that it could modify the row data before it is written.

Here is an example. This one uses a data area to keep up with the last ID used but maybe you could do something similar by getting the max ID -- but I guess you'll still need a data area or something to lock and unlock in order to ensure that the updates happen synchronously so that you don't get a duplicate key error.

http://www2.systeminetwork.com/code/index.cfm?fuseaction=ShowCodeInFile&Year=2000&Month=07&Page=057&HostType=AS400&FileType=RPGLE&PCName=UPDTRG.RP4&HostName=UPDTRG

If you could use a data area to hold the last ID, like the example, that would be better still. Anyway, before we had the ability to use generated identity columns the trigger-based approach was commonly used. This way, no matter what writes the record, it gets a correctly assigned unique ID.

--Dan

Hello Dan, thanks for the suggestion. I was hoping that I was missing some more obvious solution, but I presume I wasn't.

I would prefer not to resort to RPG and data areas. A similar thing could be done in EGL using a separate RunningKeys table. Last used running key is stored in the table and table row is locked using forupdate statement. It would be better if we could use max() for the pertinent table instead of a separate table, since it would keep the data in a single location. Alas, that approach fails concurrent updates.

In the current issue we have (patching an old program), checking for slqCode 803 might be the quickest solution, but I'll know better in the future.

Skip the RPG and use the SQL CREATE TRIGGER statement. 

Daron

Do you have an example that updates the data buffer on a before insert trigger without using RPG? That was the requirement here. If that can be done with SQL alone and not RPG I would love to have an example of it for future reference.

Thanks.

Dan

Dan,

Thanks for putting me on the spot - I should have done better than a simple "drive-by" post like that.

So here's what I think should work...

CREATE TABLE MySchema/MyTable (
    Id INTEGER  CONSTRAINT MyTableId UNIQUE,
    CustNo INTEGER,
    CustomerName VARCHAR(50))
  ;

CREATE OR REPLACE TRIGGER MySchema/MyTableUniqueId
  BEFORE INSERT ON MySchema/MyTable
  REFERENCING NEW AS Bef
  FOR EACH ROW
  MODE DB2ROW

BEGIN ATOMIC
  SET Bef.Id = IFNULL((SELECT MAX(Id) + 1
                            FROM MySchema/MyTable), 1);
END;

--  Insert some data w/o Id

INSERT
  INTO MySchema/MyTable (CustNo, CustomerName)
  VALUES(123, 'ACME Co.')
  ;

--  Insert some data with Id (value of Id will be overridden by trigger event)

INSERT
  INTO MySchema/MyTable
  VALUES(22, 234, 'Smith & Co.')
  ;

This does not take of concurrent inserts.  I'm curious if the trigger option

CONCURRENT ACCESS RESOLUTION WAIT FOR OUTCOME

will be of value; although, I'm not really sure what it does.

Disclaimer:  I don't profess to be a DB2 for i guru.  But, I did stay at a Holiday Inn Express last night

I hope this helps.

Daron

Daron,

That's great! I really appreciate the follow-up with the example. I didn't know how to do that.

--Dan

Thank you for the suggestion and detailed description of the trigger. However, I am still unsure how to combine this with EGL. The rationale behind the original code was that we can return the ID field for further processing. If the ID field is updated by a trigger, I presume we do not get the updated value in the EGL record. We could obtain the same result by defining a field as GENERATED ALWAYS WITH IDENTITY.

Hence, the question remains: how it insert rows safely concurrently and know which was the ID in the row added?

Yes, the same result would be produced as using GENERATED ALWAYS WITH IDENTITY.  I understand you're unable to change the table do do such, so I was offering a method using the exiting id column with UNIQUE constraint.

Could you move the i/o logic from EGL into an SQL Stored Procedure?

Using the LOCK TABLE statement should enable you to INSERT the record and then query for MAX(id).  From that, the stored procedure would return the value as a parameter.

Daron

This sounds worth exploring. We have not used stored procedures, but I presume appropriate documentation is amply available. What remains to be tested is whether the key created in the stored procedure is available in EGL. The examples in the EGL manual give a host variable as parameter (but it is unclear whether the host variable can be updated) or return a result set. Getting a result set from an insert operation seems a little odd, but I'll need to explore the alternatives.

I don't believe the key would be intrinsically available in EGL.  It could be passed back as out parameter from the stored procedure to EGL.  Using the SQL definitions above, the stored procedure would look something like this:

CREATE OR REPLACE PROCEDURE MySchema/AddCustomer(
    IN id INT,
    IN custNo INT,
    IN customerName VARCHAR(50),
    OUT idAssigned INT)
  LANGUAGE SQL

BEGIN

  LOCK TABLE MySchema/MyTable IN SHARE MODE;
  INSERT INTO MySchema/MyTable (CustNo, CustomerName)
    VALUES (custNo, customerName);
 
  SET idAssigned = (SELECT MAX(id) FROM MySchema/MyTable);

END;
 

EGL code to CALL the stored procedure (untested):

package com.mycompany.mypackage;

// service

service MyService

    
    function addCustomer()
        id int = 0;  //  doesn't matter as ignored and assigned by db
        custNo int = 1234;
        customerName string = "Acme Co.";
        assignedId int;

        try
            // EGL statements
            
            execute
                    #sql{
                        CALL MySchema/AddCustomer(:id,
                                                  :custNo,
                                                  :customerName,
                                                  :assignedId)
                    };
            SysLib.writeStdout("Record added with id = " + assignedId);
        onException(ex SQLException)
            SysLib.writeStdout(ex.message);
        end
    end

end
 

Depending on the # of columns being added in the db record, you may want to pass the record in a structure as one parameter.  However, this can get involved with INTEGER type data.  I haven't done it in quite some time so the methods may have changed.  The topic should be found on this forum.

Daron

Did you try used sql option "WITH UR" (Uncommited Read) on getCurrentMaxID ?

The problem is that there may be key gap , if one or more transaction are not commited.

Hsieh

Tuukka

I am resurrecting this old topic as we came up with a simple solution. Namely, we can base keys on sequences: the number obtained from a sequence is unique even in concurrent processes and can be used as a key when inserting data. First, create a sequence like below.

CREATE SEQUENCE mylib/porkkana

We can then get obtain key value from the sequence, for example using the following EGL code 

function getSequence()  seq int; execute #sql{    SELECT NEXT VALUE FOR porkkana into :seq FROM SYSIBM.SYSDUMMY1  };end

Concurrent processes seem get unique keys from the sequence just fine. If defined using the default values, the keys may not be consecutive but that is not an essential requirement for us. We have not yet used this approach in production code, but initial tests are positive.