some questions about Informix

Hi, First of all, I have very little Informix experience but I have been working on oracle for several years. Now our company is considering support our product (currently running on oracle) on Informix also. Before we make that decision, I would like to "confirm" or "know" if Informix has certain features that we need for our product. I looked at Informix website and some other places and read some articles, but I could not find all the info I want. So I think I would post my questions here and hope to get some "quick" answers. 1. Informix has sequence number generation feature? In oracle, one could create an "oracle sequence" and used it for a primary key column in a table. Do I have to create a column in "serial" type in Informix to accomplish this? 2. The last time I tried Informix Online(about 6 months ago), I seem to running into an issue that my Foreign key constraint name is limited to 18 characters. Is this stillI the case? What is the limit of table name, column nam and constraint name in the current version of Informix? 3. I know Informix support stored procedures and triggers. How about packages (similar to oracle package)? 4. In oracle, the admin tools include Oracle Enterprise Manager (OEM), Server Manager and SqlPlus. The last time I tried, Informix's admin tool was web based. Is this still the case? Are there any "easy to use" yet "powerful" admin tool(s) for Informix? 5. Is DBload an utility similar to oracle's SQL Loader? 6. Are there any tools similar to oracle exp/imp utility? (dbexp/dbimp?) 7. Last but not least, how people usually do backup of Informix db, writing your own backup scripts or using some Informix backup tools? I know I have lots of questions here but we are on the very short time limit to make a decision so any response is appreciated. TIA. Guang Sent via Deja.com http://www.deja.com/ Before you buy.

> 1. Informix has sequence number generation feature? In oracle, one could > create an "oracle sequence" and used it for a primary key column in a > table. Do I have to create a column in "serial" type in Informix to > accomplish this? There are special type 'serial' (ala autoincrement) in Informix for using as type for primary key column. create table same_table ( id serial not null primary key, ... ) But if you want 'sequience' - it is easy: for example --------------------------------------------------------- create table seq ( name char(8) not null primary key, val int default 0 not null );--------------------------------------------------------- create function sequence( name like seq.name, inc like seq.val default 1 ) returning int;define l_val int; foreach curs for select s.val + inc into l_val from seq s where s.name = name update seq set val = l_val where current of curs; end foreach; return l_val; end function --------------------------------------------------------- insert into seq (name) values ('same_name'); execute function sequence('same_name'); -- next val execute function sequence('same_name', 0); -- cur val ---------------------------------------------------------Woala! > 2. The last time I tried Informix Online(about 6 months ago), I seem to > running into an issue that my Foreign key constraint name is limited to > 18 characters. Is this stillI the case? What is the limit of table name, > column nam and constraint name in the current version of Informix? In IDS-9.x upper limit of length of all names is 128. > 3. I know Informix support stored procedures and triggers. How about > packages (similar to oracle package)? Regrettably INformix SPL does not have this faculty (packages). > 4. In oracle, the admin tools include Oracle Enterprise Manager (OEM), > Server Manager and SqlPlus. The last time I tried, Informix's admin tool > was web based. Is this still the case? Are there any "easy to use" yet > "powerful" admin tool(s) for Informix? There is a lot of managers maked by third firms specially for Informix. ( for example Server Studio for Informix http://www.agsltd.com ) >5. Is DBload an utility similar to oracle's SQL Loader? onload, onunload, dbload, dbunload > 6. Are there any tools similar to oracle exp/imp utility? (dbexp/dbimp?) dbexport, dbimport > 7. Last but not least, how people usually do backup of Informix db, > writing your own backup scripts or using some Informix backup tools? Informix have a vary powerful backup tools. ( ontype, onbar, onarchive ) Good luck. Sergey. <gmei@my-deja.com> ''''' ' ''''''''':8t83hj$fh9$1@nnrp1.deja.com... > Hi, > > First of all, I have very little Informix experience but I have been > working on oracle for several years. Now our company is considering > support our product (currently running on oracle) on Informix also. > Before we make that decision, I would like to "confirm" or "know" if > Informix has certain features that we need for our product. I looked at > Informix website and some other places and read some articles, but I > could not find all the info I want. So I think I would post my questions > here and hope to get some "quick" answers. > > 1. Informix has sequence number generation feature? In oracle, one could > create an "oracle sequence" and used it for a primary key column in a > table. Do I have to create a column in "serial" type in Informix to > accomplish this? > > 2. The last time I tried Informix Online(about 6 months ago), I seem to > running into an issue that my Foreign key constraint name is limited to > 18 characters. Is this stillI the case? What is the limit of table name, > column nam and constraint name in the current version of Informix? > > 3. I know Informix support stored procedures and triggers. How about > packages (similar to oracle package)? > > 4. In oracle, the admin tools include Oracle Enterprise Manager (OEM), > Server Manager and SqlPlus. The last time I tried, Informix's admin tool > was web based. Is this still the case? Are there any "easy to use" yet > "powerful" admin tool(s) for Informix? > > 5. Is DBload an utility similar to oracle's SQL Loader? > > 6. Are there any tools similar to oracle exp/imp utility? (dbexp/dbimp?) > > 7. Last but not least, how people usually do backup of Informix db, > writing your own backup scripts or using some Informix backup tools? > > I know I have lots of questions here but we are on the very short time > limit to make a decision so any response is appreciated. > > TIA. > > Guang > > > Sent via Deja.com http://www.deja.com/ > Before you buy.

gmei@my-deja.com wrote: > Hi, > > First of all, I have very little Informix experience but I have been > working on oracle for several years. Now our company is considering > support our product (currently running on oracle) on Informix also. > Before we make that decision, I would like to "confirm" or "know" if > Informix has certain features that we need for our product. I looked at > Informix website and some other places and read some articles, but I > could not find all the info I want. So I think I would post my questions > here and hope to get some "quick" answers. > ... > > 4. In oracle, the admin tools include Oracle Enterprise Manager (OEM), > Server Manager and SqlPlus. The last time I tried, Informix's admin tool > was web based. Is this still the case? Are there any "easy to use" yet > "powerful" admin tool(s) for Informix? There is some powerfull text tools that you can use on the command line to administer the database. It allows you to check about every aspect of the systables rigth from those tools (onstat, oncheck,..). You also have a text base tool to access de database that give a DBA about all that is needed to query de database (dbaccess). And there is a tool to change control the database (up, down, ...) that is also text based (onmonitor). Your other questions have been addressed in other response. Hope it help you! Gilbert.

"Sergey E. Volkov" wrote: > There is a lot of managers maked by third firms specially for Informix. > ( for example Server Studio for Informix http://www.agsltd.com ) Can You name another examples?

I was pleased, but I not interest by them. I like the old and nice dbaccess. Good luck. Sergey. <Leonids.Voroncovs@dati.lv> ''''' ' ''''''''':39F951DA.D74F5EAD@dati.lv... > "Sergey E. Volkov" wrote: > > > There is a lot of managers maked by third firms specially for Informix. > > ( for example Server Studio for Informix http://www.agsltd.com ) > > Can You name another examples? > >

Hi, > > 1. Informix has sequence number generation feature? In oracle, one could > > create an "oracle sequence" and used it for a primary key column in a > > table. Do I have to create a column in "serial" type in Informix to > > accomplish this? > > There are special type 'serial' (ala autoincrement) in Informix for using as > type for primary key column. > ... > But if you want 'sequience' - it is easy: > > for example > --------------------------------------------------------- > create table seq ( > name char(8) not null primary key, > val int default 0 not null > );> --------------------------------------------------------- > create function sequence( name like seq.name, inc like seq.val default 1 ) > returning int;> define l_val int; > > foreach curs > for select s.val + inc into l_val from seq s where s.name = name > update seq set val = l_val where current of curs;> end foreach; > > return l_val; > > end function > --------------------------------------------------------- > insert into seq (name) values ('same_name');> > execute function sequence('same_name'); -- next val > execute function sequence('same_name', 0); -- cur val > ---------------------------------------------------------> Woala! I have this same problem (my company start support our product (currently running on oracle) on Informix also), the solv is easy (I write function like above some about three month ago), but bed for me. Why? : User1 begin work; execute function sequence('same_name');User2 begin work; execute function sequence('same_name');;-( I think about something like this: create table seq_name ( val serial );(for every Oracle sequencer different table) create function sequence(name varchar) returning int;define val int; if name = 'seq_name' then insert into seq_name(val) values(0); select id into val from seq_name; delete from seq_name where id=val; return val; end if; end function; but I want better method, so if somebody know one, please write. I wait for any suggestion to. Piotrek Piotr.Wawrzyniak@put.poznan.pl

> User1 > begin work; > execute function sequence('same_name');> User2 > begin work; > execute function sequence('same_name'); If you use CURSOR STABILITY isolation level, then it is no problems with multiuser access for 'sequence'. If you use for example COMMITTED READ isolation it is also no problems. create function sequence( name like seq.name, inc like seq.val default 1 ) returning int;define l_val int; set isolation to cursor stability; foreach curs for select s.val + inc into l_val from seq s where s.name = name -- this place a shared lock on selected record update seq set val = l_val where current of curs; end foreach; set isolation to read committed; return l_val; end function Good luck. Sergey. Piotr Wawrzyniak <Piotr.Wawrzyniak@put.poznan.pl> ''''' ' ''''''''':8tc450$tn$1@sunsite.icm.edu.pl... > Hi, > > > > 1. Informix has sequence number generation feature? In oracle, one could > > > create an "oracle sequence" and used it for a primary key column in a > > > table. Do I have to create a column in "serial" type in Informix to > > > accomplish this? > > > > There are special type 'serial' (ala autoincrement) in Informix for using > as > > type for primary key column. > > > ... > > But if you want 'sequience' - it is easy: > > > > for example > > --------------------------------------------------------- > > create table seq ( > > name char(8) not null primary key, > > val int default 0 not null > > );> > --------------------------------------------------------- > > create function sequence( name like seq.name, inc like seq.val default 1 ) > > returning int;> > define l_val int; > > > > foreach curs > > for select s.val + inc into l_val from seq s where s.name = name > > update seq set val = l_val where current of curs;> > end foreach; > > > > return l_val; > > > > end function > > --------------------------------------------------------- > > insert into seq (name) values ('same_name');> > > > execute function sequence('same_name'); -- next val > > execute function sequence('same_name', 0); -- cur val > > ---------------------------------------------------------> > Woala! > > I have this same problem (my company start support our product (currently > running on oracle) on Informix also), the solv is easy (I write function > like above some about three month ago), but bed for me. Why? : > > User1 > begin work; > execute function sequence('same_name');> User2 > begin work; > execute function sequence('same_name');> ;-( > > I think about something like this: > > create table seq_name ( > val serial > );> (for every Oracle sequencer different table) > > create function sequence(name varchar) > returning int;> define val int; > > if name = 'seq_name' then > insert into seq_name(val) values(0);> select id into val from seq_name; > delete from seq_name where id=val;> return val; > end if; > > end function; > > but I want better method, so if somebody know one, please write. I wait for > any suggestion to. > > Piotrek > Piotr.Wawrzyniak@put.poznan.pl > >

> > User1 > > begin work; > > execute function sequence('same_name');> > User2 > > begin work; > > execute function sequence('same_name');> > If you use CURSOR STABILITY isolation level, then it is no problems with > multiuser access for 'sequence'. I test and there still is a problem (because function sequence want to make an update of locked row). > If you use for example COMMITTED READ isolation it is also no problems. Yes a use COMMITTED READ > create function sequence( name like seq.name, inc like seq.val default 1 ) > returning int;> define l_val int; > set isolation to cursor stability;> foreach curs > for select s.val + inc into l_val from seq s where s.name = name -- this > place a shared lock on selected record > update seq set val = l_val where current of curs;> end foreach; > set isolation to read committed;> return l_val; > > end function I don't understand. From manual: CURSOR STABILITY - Acquires a shared lock on the selected row. Another process can also acquire a shared lock on the same row, but no process can acquire an exclusive lock to modify data in the row. So how two different transactions can modify this same row ? (I'm not interesting in DIRTY READ). The "normal" situation in DB is that transaction A view changes made by transaction B when the B make a commit. The sequencer is not a "normal" DB object, because the changes are view by another transaction immadietly. So my question is: How to create something, what be visible for all transaction without making a commit ? Piotrek p.s. My idea is to use a serial value inserted into table. create table seq_name ( val serial ); --for every Oracle sequencer differenttable create procedure sequence(name varchar) returning int; define val int; if name = 'seq_name' then insert into seq_name(val) values(0); select id into val from seq_name; delete from seq_name where id=val; return val; end if; end procedure;

Hi Piotr. I think this is a really problem. Unless you commit your transaction, other sessions can't to get next sequence number. I think there is only one way to do this: create raw table seq ( id serial ); { With raw type you take advantage of light appends and avoid the overhead of logging. } create function sequence() returning int insert into seq (id) values (0); return dbinfo('sqlca.sqlerrd2'); end function; Good luck. Sergey. Piotr Wawrzyniak <Piotr.Wawrzyniak@put.poznan.pl> ''''' ' ''''''''':8tjk20$eef$1@sunsite.icm.edu.pl... > > > > User1 > > > begin work; > > > execute function sequence('same_name');> > > User2 > > > begin work; > > > execute function sequence('same_name');> > > > If you use CURSOR STABILITY isolation level, then it is no problems with > > multiuser access for 'sequence'. > > I test and there still is a problem (because function sequence want to make > an update of locked row). > > > If you use for example COMMITTED READ isolation it is also no problems. > > Yes a use COMMITTED READ > > > create function sequence( name like seq.name, inc like seq.val default 1 ) > > returning int;> > define l_val int; > > set isolation to cursor stability;> > foreach curs > > for select s.val + inc into l_val from seq s where s.name = name -- > this > > place a shared lock on selected record > > update seq set val = l_val where current of curs;> > end foreach; > > set isolation to read committed;> > return l_val; > > > > end function > > I don't understand. > From manual: CURSOR STABILITY - Acquires a shared lock on the selected row. > Another process can also acquire a shared lock on the same row, but no > process can acquire an exclusive lock to modify data in the row. > > So how two different transactions can modify this same row ? (I'm not > interesting in DIRTY READ). > > The "normal" situation in DB is that transaction A view changes made by > transaction B when the B make a commit. The sequencer is not a "normal" DB > object, because the changes are view by another transaction immadietly. > So my question is: > How to create something, what be visible for all transaction without making > a commit ? > > Piotrek > > p.s. > My idea is to use a serial value inserted into table. > > create table seq_name ( val serial ); --for every Oracle sequencerdifferent > table > > create procedure sequence(name varchar) > returning int;> define val int; > if name = 'seq_name' then > insert into seq_name(val) values(0);> select id into val from seq_name; > delete from seq_name where id=val;> return val; > end if; > end procedure; > > > >

Just execute "SET LOCK MODE TO WAIT 5;" in each session at startup that will eliminate problems caused by transient locks by making each session wait for five seconds for the lock to clear instead of returning an error immediately. Also do not acquire the sequence until immediately prior to inserting the master record and comitting the overall transaction to minimize the lifespan of the lock. Art S. Kagel Piotr Wawrzyniak wrote: > > Hi, > > > > 1. Informix has sequence number generation feature? In oracle, one could > > > create an "oracle sequence" and used it for a primary key column in a > > > table. Do I have to create a column in "serial" type in Informix to > > > accomplish this? > > > > There are special type 'serial' (ala autoincrement) in Informix for using > as > > type for primary key column. > > > ... > > But if you want 'sequience' - it is easy: > > > > for example > > --------------------------------------------------------- > > create table seq ( > > name char(8) not null primary key, > > val int default 0 not null > > );> > --------------------------------------------------------- > > create function sequence( name like seq.name, inc like seq.val default 1 ) > > returning int;> > define l_val int; > > > > foreach curs > > for select s.val + inc into l_val from seq s where s.name = name > > update seq set val = l_val where current of curs;> > end foreach; > > > > return l_val; > > > > end function > > --------------------------------------------------------- > > insert into seq (name) values ('same_name');> > > > execute function sequence('same_name'); -- next val > > execute function sequence('same_name', 0); -- cur val > > ---------------------------------------------------------> > Woala! > > I have this same problem (my company start support our product (currently > running on oracle) on Informix also), the solv is easy (I write function > like above some about three month ago), but bed for me. Why? : > > User1 > begin work; > execute function sequence('same_name');> User2 > begin work; > execute function sequence('same_name');> ;-( > > I think about something like this: > > create table seq_name ( > val serial > );> (for every Oracle sequencer different table) > > create function sequence(name varchar) > returning int;> define val int; > > if name = 'seq_name' then > insert into seq_name(val) values(0);> select id into val from seq_name; > delete from seq_name where id=val;> return val; > end if; > > end function; > > but I want better method, so if somebody know one, please write. I wait for > any suggestion to. > > Piotrek > Piotr.Wawrzyniak@put.poznan.pl

Hi. > Just execute "SET LOCK MODE TO WAIT 5;" in each session at startup that will > eliminate problems caused by transient locks by making each session wait for > five seconds for the lock to clear instead of returning an error immediately. If I do like this I eliminate death lock but I don't resolve the problem. > Also do not acquire the sequence until immediately prior to > inserting the master record and comitting the overall transaction to > minimize the lifespan of the lock. > Art S. Kagel I prefer when informix work how I want, not when I work how Informix want. (I want function GetNextId, of course this fuctnion can't be called before commit) I thik that solv given by Sergey E. Volkov is much more better. Piotr Wawrzyniak Piotr.Wawrzyniak@put.poznan.pl

The 9.3 Product will support "oracle" sequences. This feature has been added to 9.3 for those who are familiar with oracle's sequences. I'm not sure of a time table for 9.3 and its release. dtracy Piotr Wawrzyniak wrote: > Hi. > > > Just execute "SET LOCK MODE TO WAIT 5;" in each session at startup that > will > > eliminate problems caused by transient locks by making each session wait > for > > five seconds for the lock to clear instead of returning an error > immediately. > > If I do like this I eliminate death lock but I don't resolve the problem. > > > Also do not acquire the sequence until immediately prior to > > inserting the master record and comitting the overall transaction to > > minimize the lifespan of the lock. > > Art S. Kagel > > I prefer when informix work how I want, not when I work how Informix want. > (I want function GetNextId, of course this fuctnion can't be called before > commit) > > I thik that solv given by Sergey E. Volkov is much more better. > > Piotr Wawrzyniak > Piotr.Wawrzyniak@put.poznan.pl