JDBC and Serials (again) - 2 questions

Okay chaps, I know this has been asked like a billion times on the newsgroups, but I still can't believe that there's no way around this (especially with Informix's new JDBC 2 driver). It's the old "how do I get the value of the serial just allocated" problem, using Java (JDBC) connecting to an Informix (Dynamic Server 7.3) database. I know about SELECT DBINFO but quite frankly that's a bit of a cop-out. My situation is that I've got a network of 35 machines constantly accessing this table, and constantly locking and unlocking that table (and performing 5 SQL operations - begin trans, lock, insert, select, commit - rather than one) to ensure they get the correct number back has got to be a huge waste of time. Aren't there any extensions in the latest version of the driver that allow you to grab the allocated serial? [And if not, why not? It's implemented in every other bloody language I've used] --- As an aside, I've noticed a "feature" in Informix's implementation of serials. Basically, any serials allocated inside a rolled-back transaction *remain* allocated outside that transaction: (window one) SET ISOLATION TO DIRTY READ;SELECT MAX(serialfield) FROM serialtable; # returns 500 (say) (window two) BEGIN WORK; INSERT INTO serialtable (serialfield, text) VALUES (0, "some text"); (window one) SELECT MAX(serialfield) FROM serialtable; # returns 501 (window two) ROLLBACK WORK; (window one) SELECT MAX(serialfield) FROM serialtable; # returns 500, as expected (window two) BEGIN WORK; INSERT INTO serialtable (serialfield, text) VALUES (0, "some text");COMMIT WORK; (window one) SELECT MAX(serialfield) FROM serialtable; # returns 502!!! Surely the serial allocated should be 501, because the previous use of 501 was aborted...? Ozzy

Here you go ... (at least there is a serial field, you could always select max(id) from table ... import java.sql.*; // For Informix we need this import com.informix.jdbc.*; /** * All of the vendor specific stuff goes in here. Things like supporting * an automatically incrementing row id column. * * Edit this file and recompile ... yuk! */ public class JdbcVendor { /** * Some call it SERIAL * (eg Informix), some call it IDENTITY * others just don't have it. Its a column which automatically * creates a serial id for a row. * Postgres doesn't have it but come to thing of it there is another * sort of Id for a row - the details escape me at the moment but that * could be an option in the Postgres rather than the getNextId() method * */ public static boolean SUPPORT_ID=true; public static long getId(PreparedStatement pStmt) throws SQLException { return (long)((IfxStatement)pStmt).getSerial(); // Informix } } Paul 'Ozymandias' Harman <ozzy@kasterborus.demon.co.uk> wrote in message news:H%473.215$TM6.1200@news.colt.net... > Okay chaps, I know this has been asked like a billion times on the > newsgroups, but I still can't believe that there's no way around this > (especially with Informix's new JDBC 2 driver). > > It's the old "how do I get the value of the serial just allocated" > problem, using Java (JDBC) connecting to an Informix (Dynamic Server > 7.3) database. > > I know about SELECT DBINFO but quite frankly that's a bit of a > cop-out. My situation is that I've got a network of 35 machines > constantly accessing this table, and constantly locking and unlocking > that table (and performing 5 SQL operations - begin trans, lock, > insert, select, commit - rather than one) to ensure they get the > correct number back has got to be a huge waste of time. > > Aren't there any extensions in the latest version of the driver that > allow you to grab the allocated serial? [And if not, why not? It's > implemented in every other bloody language I've used] > > --- > > As an aside, I've noticed a "feature" in Informix's implementation of > serials. Basically, any serials allocated inside a rolled-back > transaction *remain* allocated outside that transaction: > > (window one) > SET ISOLATION TO DIRTY READ;> SELECT MAX(serialfield) FROM serialtable; # returns 500 (say) > > (window two) > BEGIN WORK; > INSERT INTO serialtable (serialfield, text) VALUES (0, "some text");> > (window one) > SELECT MAX(serialfield) FROM serialtable; # returns 501 > > (window two) > ROLLBACK WORK; > > (window one) > SELECT MAX(serialfield) FROM serialtable; # returns 500, as > expected > > (window two) > BEGIN WORK; > INSERT INTO serialtable (serialfield, text) VALUES (0, "some text");> COMMIT WORK; > > (window one) > SELECT MAX(serialfield) FROM serialtable; # returns 502!!! > > Surely the serial allocated should be 501, because the previous use of > 501 was aborted...? > > Ozzy > >

No. The engine does not SELECT MAX(serial_column) to get the next serial number, it simply reads the next_serial value from the table's TABLESPACE TABLESPACE header with a lock on the table and updates it immediately, releasing the lock on the table header. This update is outside of any transaction and is not rollback-able. The reason for this is that otherwise all transactions on a serial table would have to become single threaded as the table header would have to remain locked until the entire transaction was completed. And since the purpose of serial numbers in Informix, unlike sequences in Oracle, is to produce a unique, monotonically increasing, blind key for a table and not to produce sequential numbers for any other purpose, this behavior, or quirk if you prefer, is acceptable and correct behavior. If instead you really want a sequential number you can do this yourself by creating a sequence number table with a sequence key where you lock the sequence table row containing the matching sequence key and update and fetch (or fetch and update) the next sequence, maintaining the lock until your transaction is complete. This is the behavior of an Oracle sequence and is implemented in precisely this way it is just institutionalized as a pseudo procedure. So write a stored procedure and voila. Art S. Kagel "Surfer!" wrote: > > In article <375FA3CD.745FF77C@mindspring.com>, Tim Schaefer > <tschaefe@mindspring.com> writes > >Neil Truby wrote: > >> > >> >2. the admin manual suggests setting group and user 'informix' for the > >> >raw disk device. Is this mandatory or are this security reasons. How > >> >should I set this for Solaris systems - the /dev/rdsk entries are > >> >linked themselves in some stages to the real device. Can anyone give > >> >me an example? Otherwise I would link to /dev/rdsk/c0txdysz and set > >> >the 'informix' ownership to the link. > >> > >> It's more than a suggestion; IDS will be unable to access the disk unless > >> it's owner and group are informix. The permissions should be 660, although > >> more liberal permissions will also work. > >> > >> As mentioned in TFM, you should create a link to the raw device rather than > >> use the device name itself - this helps recovery if the disk fails. So, as > >> an example: > >> > >> 1. ln -s /dev/rdsk/c0t1d0s0 /opt/informix/dbspaces/mydbspace > >> 2. chown informix:informix /opt/informix/dbspaces/mydbspace > >> 3. chmod 660 /opt/informix/dbspaces/mydbspace > >> > >> 4. onspaces -c mydbspace -p /opt/informix/dbspaces/mydbspace -o 0 -s 2097150 > >> > >> (Don't forget to slice up your disk so as not to use cylinder 0 for a raw > >> device - the disk vtoc is stored here and Solaris will allow Informix to > >> overwrite it. Also, I've composed the aboveoffline so I can't swear for its > >> 100% accuracy). > >> > > > >Yes, Niel, but you specified offset zero in your step 4. I think if you were > >going to follow this, it would look like this? > > > > onspaces -c mydbspace -p /opt/informix/dbspaces/mydbspace -o 1024 -s 2097150> > If the actual slice is correctly set up (e.g. doesn't use cylinder 0) > then I don't think that you need an offset in the first dbspace set up > on that slice. Of course subsequent dbspaces do - unless you want to > overwrite the initial one! > > However, if this is just an initial test system and you have the space I > think it's much easier to use 'cooked' file systems to start with. > > <snip> > > -- > Surfer!

Sorry folks my news service went berserk earlier and while the Subject is correct for my answer, the accompanying text is another post altogether from someone else. "Art S. Kagel" wrote: > > No. The engine does not SELECT MAX(serial_column) to get the next > serial number, it simply reads the next_serial value from the table's [SNIP] > "Surfer!" wrote: > > > > In article <375FA3CD.745FF77C@mindspring.com>, Tim Schaefer > > <tschaefe@mindspring.com> writes > > >Neil Truby wrote: > > >> > > >> >2. the admin manual suggests setting group and user 'informix' for the > > >> >raw disk device. Is this mandatory or are this security reasons. How [SNIP]