Re: Next number processing

Rune,

no good idea, this locks the complete table (depending on isolation level) und 
doesn't prevent conflicts. 
You could use a self written UDF to get rid of the key generation, or you 
could use a before insert trigger to generate the number, or since v5r2 there 
is a sort of auto increment type.

Dieter Bender

On Dienstag, 9. März 2004 15:07, rune@xxxxxxx wrote:
> Hi Patrick.
>
> Why not use the MAX function of SQL to read the highest event ID directly
> from the event-file.
>
> something like this:
>
> INSERT INTO SCHEMA.EVENTS (ID, ....) VALUES(SELECT MAX(ID)+1 FROM
> SCHEMA.EVENTS, ....)
>
> HTH
>
> Rune
>
>
>
>
> patrick.odowd@xxxxxxxxxxxxxxxxxxxxxx
> Sent by: java400-l-bounces@xxxxxxxxxxxx
> 09.03.2004 12:33
> Please respond to Java Programming on and around the iSeries / AS400
>
>         To:     java400-l@xxxxxxxxxxxx
>         cc:
>         Subject:        Next number processing
>
>
> Before acting on this e-mail or opening any attachment, you are advised to
> read the disclaimer at the end of this mail.
>
> Hi,
> I have an Event file that has a field called ID as a primary key. ID is an
> integer, incremented by 1 for each record added.
>
> I am wondering what the options are for retrieving the next sequence
> number for
> the event records.
>
> At the moment I have a separate file that stores the last event ID used,
> when a
> new event record needs to be added I call a static method on a database
> helper
> class that returns the next number, code below.
>
> There will only be 1 thread incrementing this event. The volume of events
> is
> low, a few per day. It is a batch process.
>
> What I have works but I am wondering if there a better way to do this?
>
> Thanks in advance,
> Pat
>
>   public static synchronized int getNextNumber(Connection dbConn,
>                                                String collection, String
> key)
> throws
>       JS_Exception {
>     Statement nextNumStm;
>     ResultSet nextNumSet;
>     String nextNumQry;
>     int nextNum = -1;
>
>     try {
>       nextNumStm = dbConn.createStatement();
>       nextNumStm.executeUpdate("UPDATE " + collection + ".MQ_PARM " +
>                                "SET INTVALUE = INTVALUE + 1 WHERE ID =" +
>                                '\'' + key + '\'');
>       nextNumQry = "SELECT * FROM " + collection + ".MQ_PARM WHERE ID =" +
>                    '\'' + key + '\'';
>       nextNumSet = nextNumStm.executeQuery(nextNumQry);
>
>       if (nextNumSet.next()) {
>         nextNum = nextNumSet.getInt("INTVALUE");
>       }
>     }
>     catch (SQLException e) {
>       JS_Utils.processException(e);
>       throw new JS_Exception();
>     }
>
>     return nextNum;
>   }
>
>
> **********************************************************************
> This e-mail is intended solely for the addressee and is strictly
> confidential. If you are not the addressee, please do not read, print,
> re-transmit, store or act in reliance on it or any attachments. Instead
> please e-mail it back to the sender and delete the message from your
> computer.
>
> E-mail transmission cannot be guaranteed to be secure or error free and
> The Co-operative Bank accepts no liability for changes made to this e-mail
> (and any attachments) after it was sent or for viruses arising as a result
> of this e-mail transmission.
>
> Any unauthorised reproduction, dissemination, copying, disclosure,
> modification, distribution and/or publication of this e-mail message is
> strictly prohibited.
>
> The Co-operative Bank reserves the right to intercept any e - mails or
> other communication for permitted purposes in accordance with the current
> legislation which you send to, or receive from, any of the employees or
> agents of the Bank via Bank telecommunication systems. By so corresponding
> you also give your consent to the Bank monitoring and recording of any
> correspondence using these systems.
>
> The Co-operative Bank p.l.c. is registered in England and Wales, number
> 990937. The registered office is at PO Box 101, 1, Balloon Street,
> Manchester, M60 4EP.
> **********************************************************************
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>
>
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.

-- 
mfG

Dieter Bender


DV-Beratung Dieter Bender
Wetzlarerstr. 25
35435 Wettenberg
Tel. +49 641 9805855
Fax +49 641 9805856
www.bender-dv.de
eMail dieter.bender@xxxxxxxxxxxx