sequences

Hello! Is there an analogue for Oracle sequence in Informix ( non transactional )? Thanks. Artem Goncharuk

Artem Goncharuk wrote in message <946vlp$2fc3$1@nl.novosoft.ru>... >Hello! > >Is there an analogue for Oracle sequence in Informix ( non transactional )? > >Thanks. >Artem Goncharuk Serial numbers. Normally you define a serial column for the tables where you want to have allocated numbers. Inserting 0 into that column causes the engine to replace it with the next number available. If you want a general purpose stream of numbers, we use a little 4GL routine which exploits a small table and a careful little algorithm to allocate numbers without locking problems. If you attempt to design such a routine, be careful of the finer points... I could give you the code, but I won't right now because: if you are converting from an Oracle mentality to an Informix mentality, I strongly recommend you run with the plain old serial numbers and get really familiar and comfortable with them, because they will do what you need in 99% of all cases. If you slip into an emulation of an Oracle concept then you won't be using the Informix properly. In other words, I'm trying to help you follow this principle: "you have to know the rules before you can break them" Informix serial numbers are great, fine, do an excellent job and are very commonly used.

Hello! "Andrew Hamm" <ahamm@sanderson.net.au> wrote in message news:3a6778ba$1@news.iprimus.com.au... > If you want a general purpose stream of numbers, we use a little 4GL routine > which exploits a small table and a careful little algorithm to allocate > numbers without locking problems. If you attempt to design such a routine, > be careful of the finer points... I could give you the code, but I won't > right now because: Can you give me the routine code, please. The problem is that my application works with several databases in unified way. So it is not a porting from Oracle to Informix generally... The Other solution that can help me is the folowing : if there is any mechanizm of creating unique IDs in Informix ( for example, like in DB2 ) I cound use it. Generally, I need to know primary key in the table before insertion the row in it. The other requirement is that the sbroutine should be executed in some transaction context, so locks and update conflicts should be avoided... Thanks, Artem Goncharuk

Artem Goncharuk wrote in message <949d61$20s9$1@nl.novosoft.ru>... > >Can you give me the routine code, please. The problem is that my >application works with several databases in unified way. So it is not >a porting from Oracle to Informix generally... Sigh* Database independence is killing off any power programming techniques on specific engines;-) I'm passing on the original 4GL code and also a new stored procedure implementation of a routine which returns a stream of integer numbers. You may choose to use serial8 and integer8 datatypes instead of serial and integer, but I'm not sure that 4GL supports these large integer types yet - a consequence of not having tested it and not caring to! Both routines rely on using a table defined like this: create table sequencer ( seq serial ); revoke index, alter on sequencer from "public"; It is ESSENTIAL that no indexes are added to the table, and also that no operations are performed on the table through other means. The danger with outside activity is that rubbish rows may be left behind. There is only one circumstance that you might wish to temporarily leave a row in this table. I'll talk about that at the end. The 4GL version of the code can work in any isolation mode, but the SPL version requires dirty read mode. If you are working in any other isolation level, then you must explicitly change the isolation level prior to calling the SPL and then restore your favourite isolation level after it returns. Or something like that... The restriction on the SPL code is due to the fact that (AFAIK) you cannot get the rowid of a freshly inserted row in SPL. If that's actually possible, the routine can be modified so as to be independent of isolation level. Ways to do that will become obvious as you read on. <4GL CODE> function next_seq() define seq_no, p_rowid integer whenever error ...... # whatever is your favourite response insert into sequencer values (0) let seq_no = sqlca.sqlerrd[2] let p_rowid = sqlca.sqlerrd[6] delete from sequencer where rowid = p_rowid return seq_no end function </4GL CODE> <SPL CODE> create procedure next_seq() returning integer; define seq_no, p_rowid integer; insert into sequencer values (0); let seq_no = dbinfo('sqlca.sqlerrd1'); select rowid into p_rowid from sequencer where seq = seq_no; delete from sequencer where rowid = p_rowid; return seq_no; end procedure; </SPL CODE> Both routines should be executed inside a transaction to prevent problems should the process crap out between the insert and the deletion of the row. A rare possibility, sure, but shit does and will happen. The use of transactions means that the inserted row will be automatically removed by the implicit rollback in the event of a crash. The routines work like this: Insert of 0 info a serial column causes allocation of the next serial number. Both routines get that new serial number from the sqlca.sqlerrd structure. Note that 4GL treats that as an array starting at 1, and SPL treats it as an array starting at 0 (in the style of the C language). In fact, SPL doesn't really treat it as an array at all, but the name 'sqlerrd1' is obviously an alias for the appropriate field. Both routines also collect the rowid of the freshly inserted row. Unfortunately the SPL implementation cannot get it from sqlca.sqlerrd so we must search for it. This is where the SPL may suffer from locks, and hence the requirement to run it in dirty read isolation level. Any where clause that scans through a set of rows MAY trip up over a pre-existing lock, which will certainly be there if another process has called this procedure but hasn't yet committed. The 4GL version is immune to lock problems because it doesn't scan. It's extremely polite and looks only at the row it's allocated for itself. It's quite uptight really, like a Pastor at a bikini contest. Hmmm - I'm starting to think that it MAY be possible to remove the rule against adding an index, and use an index for the deletion of the fresh row, since modern 7.XX engines and above have smarter index locking. I leave it as an exercise to the interested programmer to try that if they want to use the SPL version in a stronger isolation level. You MUST test it in a multi-user way. To test, I suggest you call the routine on two or more sessions, with explicit, long pauses (perhaps wait for you the user to press return) so that you can see if there is going to be any locking contention in the index. If you use the 4GL version of the routine, you don't need to piss around testing alternative cleanup techniques because the routine works fine as it stands. Once we know the rowid, we can delete the fresh row. It's no use to us... Deleting via rowid is free from locking problems because the engine can go directly to the row and do the damage. This is the beauty of the use of rowid's in this situation. I'm using a separate fetch of the rowid and delete via rowid because if we use a delete ... where seq = seq_no, then the delete will sweep a promotable lock (RTFM or previous recent posts) thru any existing or freshly deleted rows from other processes and cause locking problems. To any who will complain about the use of rowid: 1) This is an Informix-specific solution. It is not to be ported. If you are using engine-based sequences in your application and you wish to use a different engine, then you must find a solution in the target engine. Possible solutions somewhere else cannot have an influence on the correctness of this routine for Informix engines. 2) The table will not be fragmented. Don't even think about it. 3) If you are a purist and want to object to rowid "just because they are evil" then get over it in this instance. This is a specific solution to a specific problem on a specific engine. Possibly the suggested index deletion may work as an alternative to the use of rowids, but I'm not going to mess around with it here, because the routine has been working for several years, and even more importantly, it's survived several changes to the way the engine works. In fact, this final form is the result of surviving some serious changes made around the time of 5.03 engines. Fear of future implementation changes in the engine is keeping me very happy with the success of this routine as it is written. A reason to leave a row in the table over the short term: If you are exporting the database, then there is a famous problem where the last serial number is not passed on in the SQL (unless the -ss flag of dbexport causes that?) Since this table is designed to have no rows ever (and that's a good test you can check to see that no outside process is messing around with this table) then you will find the sequences restart from 1 on the newly imported database. This is usually frowned upon... SO: prior to exporting, you should insert a 0 into the table, thereby causing the last highest number to appear in the unload files, and thus the sequence number will be properly set in the new imported database. Don't forget to delete that row fro

Andrew Hamm wrote in message <3a6b8232@news.iprimus.com.au>... > >Both routines rely on using a table defined like this: > >create table sequencer >( > seq serial >);> Dammit I forgot to add: lock mode (row); The table must have row level locking to further reduce contention and possibly improve performance too (a very weak theory backs up that performance argument).