cdr_serial issue

I made a mistake about cdr_serial I assumed that it would work regardless of whether ER was on or not. This doesn't seem to be the case. I need to spread out my serial IDs like CDR_SERIAL does. Is there a way to get the functionality of CDR_SERIAL without actually replicating? I thought of defining all of the serial columns as replicates and just never starting replication. But is there a more elegant way or better way. I don't want to use sequences because I think we would have too many application changes. Would using a sequence and a trigger be acceptable?

bozon wrote: > I made a mistake about cdr_serial I assumed that it would work > regardless of whether ER was on or not. This doesn't seem to be the > case. I need to spread out my serial IDs like CDR_SERIAL does. > > Is there a way to get the functionality of CDR_SERIAL without actually > replicating? > > I thought of defining all of the serial columns as replicates and just > never starting replication. But is there a more elegant way or better > way. I don't want to use sequences because I think we would have too > many application changes. Would using a sequence and a trigger be > acceptable? > So, you want a unique value for tables across multiple instances. Database1@Instance1:table1 (col1 serial); Gets allocated 1,3,5,7,9 Database1@Instance2:table1 (col1 serial); Gets allocated 2,4,6,8,10 etc. etc.

On Nov 9, 6:54 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > bozon wrote: > > I made a mistake about cdr_serial I assumed that it would work > > regardless of whether ER was on or not. This doesn't seem to be the > > case. I need to spread out my serial IDs like CDR_SERIAL does. > > > Is there a way to get the functionality of CDR_SERIAL without actually > > replicating? > > > I thought of defining all of the serial columns as replicates and just > > never starting replication. But is there a more elegant way or better > > way. I don't want to use sequences because I think we would have too > > many application changes. Would using a sequence and a trigger be > > acceptable? > > So, you want a unique value for tables across multiple instances. > > Database1@Instance1:table1 (col1 serial); > Gets allocated 1,3,5,7,9 > Database1@Instance2:table1 (col1 serial); > Gets allocated 2,4,6,8,10 > > etc. etc. Exactly, in our infinite wisdom we our redesigning are application to be distributed but we don't want to use ER. We are going to handle it in the application. Don't get me started I tried to do it the easier way but I am only 1 DBA with many developers and they were all really excited about googlefying our application, but they don't want to change the code to use composite keys because that would be too much work.

On Nov 9, 8:13 am, bozon <cur...@crowson1.com> wrote: > On Nov 9, 6:54 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > > > > > bozon wrote: > > > I made a mistake about cdr_serial I assumed that it would work > > > regardless of whether ER was on or not. This doesn't seem to be the > > > case. I need to spread out my serial IDs like CDR_SERIAL does. > > > > Is there a way to get the functionality of CDR_SERIAL without actually > > > replicating? > > > > I thought of defining all of the serial columns as replicates and just > > > never starting replication. But is there a more elegant way or better > > > way. I don't want to use sequences because I think we would have too > > > many application changes. Would using a sequence and a trigger be > > > acceptable? > > > So, you want a unique value for tables across multiple instances. > > > Database1@Instance1:table1 (col1 serial); > > Gets allocated 1,3,5,7,9 > > Database1@Instance2:table1 (col1 serial); > > Gets allocated 2,4,6,8,10 > > > etc. etc. > > Exactly, in our infinite wisdom we our redesigning are application to > be distributed but we don't want to use ER. We are going to handle it > in the application. Don't get me started I tried to do it the easier > way but I am only 1 DBA with many developers and they were all really > excited about googlefying our application, but they don't want to > change the code to use composite keys because that would be too much > work. My head hurts and is flat from banging it on the desk.

On Nov 9, 8:40 am, bozon <cur...@crowson1.com> wrote: > On Nov 9, 8:13 am, bozon <cur...@crowson1.com> wrote: > > > > > On Nov 9, 6:54 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > > > > bozon wrote: > > > > I made a mistake about cdr_serial I assumed that it would work > > > > regardless of whether ER was on or not. This doesn't seem to be the > > > > case. I need to spread out my serial IDs like CDR_SERIAL does. > > > > > Is there a way to get the functionality of CDR_SERIAL without actually > > > > replicating? > > > > > I thought of defining all of the serial columns as replicates and just > > > > never starting replication. But is there a more elegant way or better > > > > way. I don't want to use sequences because I think we would have too > > > > many application changes. Would using a sequence and a trigger be > > > > acceptable? > > > > So, you want a unique value for tables across multiple instances. > > > > Database1@Instance1:table1 (col1 serial); > > > Gets allocated 1,3,5,7,9 > > > Database1@Instance2:table1 (col1 serial); > > > Gets allocated 2,4,6,8,10 > > > > etc. etc. > > > Exactly, in our infinite wisdom we our redesigning are application to > > be distributed but we don't want to use ER. We are going to handle it > > in the application. Don't get me started I tried to do it the easier > > way but I am only 1 DBA with many developers and they were all really > > excited about googlefying our application, but they don't want to > > change the code to use composite keys because that would be too much > > work. > > My head hurts and is flat from banging it on the desk. Here is my work around but I don't really want to do this for every fantastic table. drop table test_serial; drop procedure test_serial_insert_tr;drop sequence test_serial_seq; create sequence test_serial_seq increment by 32 start 1; create table "informix".test_serial ( id int8, junk char(20) ); create procedure test_serial_insert_tr(invalue int8) returning int8; if (invalue is null or invalue = 0) then return test_serial_seq.nextval; else return invalue; end if; end procedure; create trigger test_serial_insert insert on test_serial referencingnew as n for each row ( execute procedure test_serial_insert_tr(n.id) into test_serial.id ); insert into test_serial values (0, "test junk 1"); insert into test_serial values (0, "test junk 2"); insert into test_serial values (0, "test junk 3"); insert into test_serial values (0, "test junk 4"); insert into test_serial values (0, "test junk 5"); insert into test_serial values (10000, "test junk 6"); select * from test_serial; To get some response to this will I have to mention how much better Oracle is and how we are like the dinosaurs that don't know that we are extinct yet. That always seems to get a rise. ;-)

bozon wrote: > On Nov 9, 8:40 am, bozon <cur...@crowson1.com> wrote: >> On Nov 9, 8:13 am, bozon <cur...@crowson1.com> wrote: >> >> >> >>> On Nov 9, 6:54 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: >>>> bozon wrote: >>>>> I made a mistake about cdr_serial I assumed that it would work >>>>> regardless of whether ER was on or not. This doesn't seem to be the >>>>> case. I need to spread out my serial IDs like CDR_SERIAL does. >>>>> Is there a way to get the functionality of CDR_SERIAL without actually >>>>> replicating? >>>>> I thought of defining all of the serial columns as replicates and just >>>>> never starting replication. But is there a more elegant way or better >>>>> way. I don't want to use sequences because I think we would have too >>>>> many application changes. Would using a sequence and a trigger be >>>>> acceptable? >>>> So, you want a unique value for tables across multiple instances. >>>> Database1@Instance1:table1 (col1 serial); >>>> Gets allocated 1,3,5,7,9 >>>> Database1@Instance2:table1 (col1 serial); >>>> Gets allocated 2,4,6,8,10 >>>> etc. etc. >>> Exactly, in our infinite wisdom we our redesigning are application to >>> be distributed but we don't want to use ER. We are going to handle it >>> in the application. Don't get me started I tried to do it the easier >>> way but I am only 1 DBA with many developers and they were all really >>> excited about googlefying our application, but they don't want to >>> change the code to use composite keys because that would be too much >>> work. >> My head hurts and is flat from banging it on the desk. > > Here is my work around but I don't really want to do this for every > fantastic table. > > drop table test_serial; > drop procedure test_serial_insert_tr;> drop sequence test_serial_seq; > > create sequence test_serial_seq increment by 32 start 1;> > create table "informix".test_serial > ( > id int8, > junk char(20) > ); > > create procedure test_serial_insert_tr(invalue int8) returning int8;> if (invalue is null or invalue = 0) then > return test_serial_seq.nextval; > else > return invalue; > end if; > end procedure; > > > create trigger test_serial_insert insert on test_serial referencing> new as n > for each row ( > execute procedure test_serial_insert_tr(n.id) into test_serial.id > )> ; > > insert into test_serial values (0, "test junk 1"); > insert into test_serial values (0, "test junk 2"); > insert into test_serial values (0, "test junk 3"); > insert into test_serial values (0, "test junk 4"); > insert into test_serial values (0, "test junk 5"); > insert into test_serial values (10000, "test junk 6");> > select * from test_serial;> > To get some response to this will I have to mention how much better > Oracle is and how we are like the dinosaurs that don't know that we > are extinct yet. That always seems to get a rise. ;-) following that reasoning, you are already doing it the oracle way, so it can't get any better than that.... I take it you don't want to change your application at all, or at least try and avoid changes as much as possible. can't you change your tables to have an extra column name eg 'site' whose default is a unique number for each site, and change your primary and foreign keys to include site as well as your serial? what's the proportion of selects that you may have to amend following this change? or you could use to your advantage the property of serial by which if you insert into a serial a value bigger than the current serial, it will pick upthat as the next serial value, so you could partition sites to have each a certain range of serials. The practice is error prone (what if you forget to 'prime a certain table?) and dangerous, in that a site could break its ceiling and run into some other site range. but then cdr_serial wouldn't be any better. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

bozon wrote: > On Nov 9, 8:40 am, bozon <cur...@crowson1.com> wrote: > >>On Nov 9, 8:13 am, bozon <cur...@crowson1.com> wrote: >> >> >> >> >>>On Nov 9, 6:54 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: >> >>>>bozon wrote: >>>> >>>>>I made a mistake about cdr_serial I assumed that it would work >>>>>regardless of whether ER was on or not. This doesn't seem to be the >>>>>case. I need to spread out my serial IDs like CDR_SERIAL does. >> >>>>>Is there a way to get the functionality of CDR_SERIAL without actually >>>>>replicating? Well, this is a hack, but it does work ... create a replicate with one participant : create database jj_one_repl in dbspace2 with log; database jj_one_repl; create table tab1 (col1 serial primary key, col2 char(25)) with crcols lock mode row;EOF cdr define repl -i -A -R -C timestamp -S trans jj_one_repl \\ "jj_one_repl@nan_1000f_1_cdr:informix.tab1" "select * from tab1" cdr start repl jj_one_repl and ensure you have CDR_SERIAL set to something sensible (2,0 for site 1 and 2,1 for site 2 for example if just 2 sites) Then you get the CDR_SERIAL behaviour (and ER doing a bit of work to realise it has nothing to do).

On Nov 9, 12:22 pm, Marco Greco <ma...@4glworks.com> wrote: > bozon wrote: > > On Nov 9, 8:40 am, bozon <cur...@crowson1.com> wrote: > >> On Nov 9, 8:13 am, bozon <cur...@crowson1.com> wrote: > > >>> On Nov 9, 6:54 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > >>>> bozon wrote: > >>>>> I made a mistake about cdr_serial I assumed that it would work > >>>>> regardless of whether ER was on or not. This doesn't seem to be the > >>>>> case. I need to spread out my serial IDs like CDR_SERIAL does. > >>>>> Is there a way to get the functionality of CDR_SERIAL without actually > >>>>> replicating? > >>>>> I thought of defining all of the serial columns as replicates and just > >>>>> never starting replication. But is there a more elegant way or better > >>>>> way. I don't want to use sequences because I think we would have too > >>>>> many application changes. Would using a sequence and a trigger be > >>>>> acceptable? > >>>> So, you want a unique value for tables across multiple instances. > >>>> Database1@Instance1:table1 (col1 serial); > >>>> Gets allocated 1,3,5,7,9 > >>>> Database1@Instance2:table1 (col1 serial); > >>>> Gets allocated 2,4,6,8,10 > >>>> etc. etc. > >>> Exactly, in our infinite wisdom we our redesigning are application to > >>> be distributed but we don't want to use ER. We are going to handle it > >>> in the application. Don't get me started I tried to do it the easier > >>> way but I am only 1 DBA with many developers and they were all really > >>> excited about googlefying our application, but they don't want to > >>> change the code to use composite keys because that would be too much > >>> work. > >> My head hurts and is flat from banging it on the desk. > > > Here is my work around but I don't really want to do this for every > > fantastic table. > > > drop table test_serial; > > drop procedure test_serial_insert_tr;> > drop sequence test_serial_seq; > > > create sequence test_serial_seq increment by 32 start 1;> > > create table "informix".test_serial > > ( > > id int8, > > junk char(20) > > ); > > > create procedure test_serial_insert_tr(invalue int8) returning int8;> > if (invalue is null or invalue = 0) then > > return test_serial_seq.nextval; > > else > > return invalue; > > end if; > > end procedure; > > > create trigger test_serial_insert insert on test_serial referencing> > new as n > > for each row ( > > execute procedure test_serial_insert_tr(n.id) into test_serial.id > > )> > ; > > > insert into test_serial values (0, "test junk 1"); > > insert into test_serial values (0, "test junk 2"); > > insert into test_serial values (0, "test junk 3"); > > insert into test_serial values (0, "test junk 4"); > > insert into test_serial values (0, "test junk 5"); > > insert into test_serial values (10000, "test junk 6");> > > select * from test_serial;> > > To get some response to this will I have to mention how much better > > Oracle is and how we are like the dinosaurs that don't know that we > > are extinct yet. That always seems to get a rise. ;-) > > following that reasoning, you are already doing it the oracle way, so it can't > get any better than that.... > > I take it you don't want to change your application at all, or at least try > and avoid changes as much as possible. > > can't you change your tables to have an extra column name eg 'site' whose > default is a unique number for each site, and change your primary and foreign > keys to include site as well as your serial? > what's the proportion of selects that you may have to amend following this change? > > or you could use to your advantage the property of serial by which if you > insert into a serial a value bigger than the current serial, it will pick up> that as the next serial value, so you could partition sites to have each a > certain range of serials. The practice is error prone (what if you forget to > 'prime a certain table?) and dangerous, in that a site could break its ceiling > and run into some other site range. but then cdr_serial wouldn't be any better. > > -- > Ciao, > Marco > ______________________________________________________________________________ > Marco Greco /UK /IBM Standard disclaimers apply! > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > 4glworks http://www.4glworks.com > Informix on Linux http://www.4glworks.com/ifmxlinux.htm They have issues using a compound key where they would need to change much of the code. And of course I want the developers to have to change as little code as possible. That way they break less. If I use serial8 or int8 I don't think I would have to worry about sites breaking out of there range if I gave each site about 48 bits That would still allow me to have 2^16 sites. The only dang problem is that they want to be able to move companies around on the different servers. So the range wouldn't work because as soon as you move a company you would mess up its range.