RE: C-ISAM and ISQL
Larry wanted an ISQL-like ad-hoc query tool for a C-ISAM database being moved from old SCO to current Linux, after IBM told him ISQL no longer talks to C-ISAM. Jonathan Leffler explained ISQL never accessed C-ISAM directly — it always went through Informix SE (or old sqlexec) — so the old box must have had SE installed; the simplest fix is to license SE for Linux and run ISQL against it. Larry confirmed SE was indeed present on the old server. Follow-up advice on converting C-ISAM apps to SQL covered moving code to ESQL/C and Art Kagel's list of semantic differences (scroll cursors to emulate ISAM key navigation, transactions, optimistic locking instead of record locks, avoiding delete/insert updates, using SQL joins).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Does anyone know of any tools, either at iiug or elsewhere, that work similar to ISQL and will talk to a C-ISAM database? Larry
On Thu, Jun 26, 2008 at 11:44 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Does anyone know of any tools, either at iiug or elsewhere, that work similar > to ISQL and will talk to a C-ISAM database? What do you mean? Or, perhaps, what's wrong with Informix Standard Engine? Please elaborate on your requirements if SE is not the answer. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.
We have an old C-ISAM database on an old version of SCO. We have an old version of ISQL that we use to talk to the database. We are setting up a new copy of the database on a current version of Linux with the latest version of C-ISAM. In speaking with IBM, ISQL no longer communicates directly to C-ISAM. We would like an ISQL-like tool that we could use to run ad hoc queries. Short of migrating to SE or trying to use a datablade, are there any tools that we can use for this?> To: ids@iiug.org> From: jleffler.iiug@gmail.com> Subject: Re: C-ISAM and ISQL [12531]> Date: Fri, 27 Jun 2008 02:40:31 -0400> > On Thu, Jun 26, 2008 at 11:44 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > Does anyone know of any tools, either at iiug or elsewhere, that work > similar > > to ISQL and will talk to a C-ISAM database? > > What do you mean? Or, perhaps, what's wrong with Informix Standard Engine? > > Please elaborate on your requirements if SE is not the answer. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ > "Blessed are we who can laugh at ourselves, for we shall never cease > to be amused." > NB: Please do not use this email for correspondence. > I don't necessarily read it every week, even. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
On Sat, Jun 28, 2008 at 4:59 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > We have an old C-ISAM database on an old version of SCO. We have an old > version of ISQL that we use to talk to the database. We are setting up a new > copy of the database on a current version of Linux with the latest version of > C-ISAM. In speaking with IBM, ISQL no longer communicates directly to C-ISAM. > We would like an ISQL-like tool that we could use to run ad hoc queries. > > Short of migrating to SE or trying to use a datablade, are there any tools > that we can use for this? > To: ids@iiug.org > From: jleffler.iiug@gmail.com> > Subject: Re: C-ISAM and ISQL [12531] > Date: Fri, 27 Jun 2008 02:40:31 -0400 > > > On Thu, Jun 26, 2008 at 11:44 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: >> Does anyone know of any tools, either at iiug or elsewhere, that work >> similar to ISQL and will talk to a C-ISAM database? > What do you mean? > Or, perhaps, what's wrong with Informix Standard Engine? > Please elaborate > on your requirements if SE is not the answer. Well, ISQL never did talk directly to a C-ISAM database other than via SE, so you must have had SE on the old system. How old is 'very old'? Unless you are positively ante-diluvian (meaning prior to version 4.00), you will have had SE as well as ISQL on the (SCO) machine, whether you realized it or not. (If you are positively ante-diluvian, then you had sqlexec, which became SE from 4.00 onwards, and was effectively the same as having SE on the system even so.) I'm not aware of any tools other tools that interact directly with C-ISAM. I assume there's a reason why you can't move away from C-ISAM -- for example, why you can't simply import the data into IDS? You should get SE for Linux and use that (and ISQL); it will be far the simplest operation. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.
I appreciate all of the comments on this. It appears that we have had a version of SE on the old server just for use with ISQL. Larry -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Leffler Sent: Wednesday, July 02, 2008 12:04 AM To: ids@iiug.org Subject: Re: C-ISAM and ISQL [12541] On Sat, Jun 28, 2008 at 4:59 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > We have an old C-ISAM database on an old version of SCO. We have an old > version of ISQL that we use to talk to the database. We are setting up a new > copy of the database on a current version of Linux with the latest version of > C-ISAM. In speaking with IBM, ISQL no longer communicates directly to C-ISAM. > We would like an ISQL-like tool that we could use to run ad hoc queries. > > Short of migrating to SE or trying to use a datablade, are there any tools > that we can use for this? > To: ids@iiug.org > From: jleffler.iiug@gmail.com> > Subject: Re: C-ISAM and ISQL [12531] > Date: Fri, 27 Jun 2008 02:40:31 -0400 > > > On Thu, Jun 26, 2008 at 11:44 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: >> Does anyone know of any tools, either at iiug or elsewhere, that work >> similar to ISQL and will talk to a C-ISAM database? > What do you mean? > Or, perhaps, what's wrong with Informix Standard Engine? > Please elaborate > on your requirements if SE is not the answer. Well, ISQL never did talk directly to a C-ISAM database other than via SE, so you must have had SE on the old system. How old is 'very old'? Unless you are positively ante-diluvian (meaning prior to version 4.00), you will have had SE as well as ISQL on the (SCO) machine, whether you realized it or not. (If you are positively ante-diluvian, then you had sqlexec, which became SE from 4.00 onwards, and was effectively the same as having SE on the system even so.) I'm not aware of any tools other tools that interact directly with C-ISAM. I assume there's a reason why you can't move away from C-ISAM -- for example, why you can't simply import the data into IDS? You should get SE for Linux and use that (and ISQL); it will be far the simplest operation. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
One last question for those of you with programming experience: What kind of an effort do you think it would take to revise applications to communicate with an SE database rather than a C-ISAM database. I know the question is vague, but what kinds of calls, connections would need to be modified, etc? Larry -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Larry Sorensen Sent: Tuesday, July 08, 2008 11:07 AM To: ids@iiug.org Subject: RE: C-ISAM and ISQL [12596] I appreciate all of the comments on this. It appears that we have had a version of SE on the old server just for use with ISQL. Larry -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Leffler Sent: Wednesday, July 02, 2008 12:04 AM To: ids@iiug.org Subject: Re: C-ISAM and ISQL [12541] On Sat, Jun 28, 2008 at 4:59 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > We have an old C-ISAM database on an old version of SCO. We have an old > version of ISQL that we use to talk to the database. We are setting up a new > copy of the database on a current version of Linux with the latest version of > C-ISAM. In speaking with IBM, ISQL no longer communicates directly to C-ISAM. > We would like an ISQL-like tool that we could use to run ad hoc queries. > > Short of migrating to SE or trying to use a datablade, are there any tools > that we can use for this? > To: ids@iiug.org > From: jleffler.iiug@gmail.com> > Subject: Re: C-ISAM and ISQL [12531] > Date: Fri, 27 Jun 2008 02:40:31 -0400 > > > On Thu, Jun 26, 2008 at 11:44 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: >> Does anyone know of any tools, either at iiug or elsewhere, that work >> similar to ISQL and will talk to a C-ISAM database? > What do you mean? > Or, perhaps, what's wrong with Informix Standard Engine? > Please elaborate > on your requirements if SE is not the answer. Well, ISQL never did talk directly to a C-ISAM database other than via SE, so you must have had SE on the old system. How old is 'very old'? Unless you are positively ante-diluvian (meaning prior to version 4.00), you will have had SE as well as ISQL on the (SCO) machine, whether you realized it or not. (If you are positively ante-diluvian, then you had sqlexec, which became SE from 4.00 onwards, and was effectively the same as having SE on the system even so.) I'm not aware of any tools other tools that interact directly with C-ISAM. I assume there's a reason why you can't move away from C-ISAM -- for example, why you can't simply import the data into IDS? You should get SE for Linux and use that (and ISQL); it will be far the simplest operation. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
One possible strategy is to move your C programs to ESQL/C. The work involved will be: - Move the files that access to database to .ec - Change all C-ISAM queries to SQL queries. This task depend on the way you do it. Sérgio Larry Sorensen escreveu: > One last question for those of you with programming experience: > > What kind of an effort do you think it would take to revise applications to > communicate with an SE database rather than a C-ISAM database. I know the > question is vague, but what kinds of calls, connections would need to be > modified, etc? > > Larry > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Larry > Sorensen > Sent: Tuesday, July 08, 2008 11:07 AM > To: ids@iiug.org > Subject: RE: C-ISAM and ISQL [12596] > > I appreciate all of the comments on this. It appears that we have had a > version of SE on the old server just for use with ISQL. > > Larry > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Jonathan Leffler > Sent: Wednesday, July 02, 2008 12:04 AM > To: ids@iiug.org > Subject: Re: C-ISAM and ISQL [12541] > > On Sat, Jun 28, 2008 at 4:59 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > >> We have an old C-ISAM database on an old version of SCO. We have an old >> version of ISQL that we use to talk to the database. We are setting up a >> > new > >> copy of the database on a current version of Linux with the latest version >> > > of > >> C-ISAM. In speaking with IBM, ISQL no longer communicates directly to >> > C-ISAM. > >> We would like an ISQL-like tool that we could use to run ad hoc queries. >> >> Short of migrating to SE or trying to use a datablade, are there any tools >> > > >> that we can use for this? >> > > >> To: ids@iiug.org >> From: jleffler.iiug@gmail.com> >> Subject: Re: C-ISAM and ISQL [12531] >> Date: Fri, 27 Jun 2008 02:40:31 -0400 >> >> >> On Thu, Jun 26, 2008 at 11:44 AM, LARRY SORENSEN <lsorensen25@msn.com> >> > wrote: > >>> Does anyone know of any tools, either at iiug or elsewhere, that work >>> similar to ISQL and will talk to a C-ISAM database? >>> >> What do you mean? >> Or, perhaps, what's wrong with Informix Standard Engine? >> Please elaborate >> on your requirements if SE is not the answer. >> > > Well, ISQL never did talk directly to a C-ISAM database other than via > SE, so you must have had SE on the old system. How old is 'very old'? > Unless you are positively ante-diluvian (meaning prior to version > 4.00), you will have had SE as well as ISQL on the (SCO) machine, > whether you realized it or not. (If you are positively ante-diluvian, > then you had sqlexec, which became SE from 4.00 onwards, and was > effectively the same as having SE on the system even so.) > > I'm not aware of any tools other tools that interact directly with > C-ISAM. I assume there's a reason why you can't move away from C-ISAM > -- for example, why you can't simply import the data into IDS? > > You should get SE for Linux and use that (and ISQL); it will be far > the simplest operation. > >
It's a different animal. I'm working with a client that's making the conversion from CISAM to ESQL/C and IDS. The only reason that it's a truly viable idea for them is that they wisely coded all of their applications to a middle layer of query functions, so they are only having to rewrite that library not hundreds of individual requests. There are several differences in the semantics of an SQL query versus a CISAM find. Among them: - CISAM you find a key then you can scroll around at the keys (and so the records) before and after it in the index. To be able to do that in SQL you need two SCROLL cursors. One for all of the rows greater than or equal to the 'find' row and another with a reversed ORDER BY clause for the rows less than the 'find' row. You'll have to save context to know in which cursor the 'current' record is located and where you are within the cursor so you will know whether to FETCH PREV or FETCH NEXT in the same cursor or FETCH FIRST into the other (personally I always just use FETCH ABS <N> and keep track of what <N> is etc. - Equal searches in CISAM are almost always semantically >= searches using a one-direction version of the more general search described above. - There are no transactions in CISAM, get used to using them. - You cannot directly lock a record that a user intends to update as you would in CISAM. You have to SELECT... FOR UPDATE and you DO NOT want to do that before displaying a row for a user to modify. First, it will block other index scans of the index you are using for the query while the row is locked blocking out other users. Second it may trigger a long transaction condition causing automatic rollbacks of transactions. Instead you have to adopt an optimistic locking protocol. - Much CISAM code updates records by deleting the original row and adding the updated row. This is too expensive in a relational environment where there tend to be more secondary indexes to support join conditions. - CISAM apps perform lots of join operations in application memory, fetching data from several tables in independent nested searches, that IDS can often (but not always) perform more efficiently using SQL joins. You should reexamine the application logic in light of the database's capabilities. At least in the generation of the app after the initial port. Art On Tue, Jul 8, 2008 at 2:00 PM, Larry Sorensen <lsorensen25@msn.com> wrote: > One last question for those of you with programming experience: > > What kind of an effort do you think it would take to revise applications to > communicate with an SE database rather than a C-ISAM database. I know the > question is vague, but what kinds of calls, connections would need to be > modified, etc? > > Larry > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Larry > Sorensen > Sent: Tuesday, July 08, 2008 11:07 AM > To: ids@iiug.org > Subject: RE: C-ISAM and ISQL [12596] > > I appreciate all of the comments on this. It appears that we have had a > version of SE on the old server just for use with ISQL. > > Larry > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Jonathan Leffler > Sent: Wednesday, July 02, 2008 12:04 AM > To: ids@iiug.org > Subject: Re: C-ISAM and ISQL [12541] > > On Sat, Jun 28, 2008 at 4:59 PM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > > > We have an old C-ISAM database on an old version of SCO. We have an old > > version of ISQL that we use to talk to the database. We are setting up a > new > > copy of the database on a current version of Linux with the latest > version > > of > > C-ISAM. In speaking with IBM, ISQL no longer communicates directly to > C-ISAM. > > We would like an ISQL-like tool that we could use to run ad hoc queries. > > > > Short of migrating to SE or trying to use a datablade, are there any > tools > > > that we can use for this? > > > To: ids@iiug.org > > From: jleffler.iiug@gmail.com> > > Subject: Re: C-ISAM and ISQL [12531] > > Date: Fri, 27 Jun 2008 02:40:31 -0400 > > > > > > On Thu, Jun 26, 2008 at 11:44 AM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > >> Does anyone know of any tools, either at iiug or elsewhere, that work > >> similar to ISQL and will talk to a C-ISAM database? > > What do you mean? > > Or, perhaps, what's wrong with Informix Standard Engine? > > Please elaborate > > on your requirements if SE is not the answer. > > Well, ISQL never did talk directly to a C-ISAM database other than via > SE, so you must have had SE on the old system. How old is 'very old'? > Unless you are positively ante-diluvian (meaning prior to version > 4.00), you will have had SE as well as ISQL on the (SCO) machine, > whether you realized it or not. (If you are positively ante-diluvian, > then you had sqlexec, which became SE from 4.00 onwards, and was > effectively the same as having SE on the system even so.) > > I'm not aware of any tools other tools that interact directly with > C-ISAM. I assume there's a reason why you can't move away from C-ISAM > -- for example, why you can't simply import the data into IDS? > > You should get SE for Linux and use that (and ISQL); it will be far > the simplest operation. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ > "Blessed are we who can laugh at ourselves, for we shall never cease > to be amused." > NB: Please do not use this email for correspondence. > I don't necessarily read it every week, even. > > > **************************************************************************** > > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.