dbschema error 217 column (colmin) not found in any table

Hi, I'm given the task to unload an Informix datase to port the database to Oracle. The Informix-DB is on a SUSE Linux server. When I try to unload a table schema using DBSCHEMA i get error -217 - column (colmin) not found in any table in the query DBSCHEMA says it is version 7.24.UC5 I found an old message about this problem related to NLS settings. As I'm an absolute Linux and Informix beginner I have no idea what I need to set or check. In DBACCESS the database tells me these NLS settings: en_US.819 Collating Sequence en_US.819 CType The Linux system has none of the DB_LOCAL or CLIENT_LOCALE environment variables set, as mentioned in the old post to this forum. But env shows among others the following variables: DBDATE=DMY4.DBDELIMITER='^' LANG=de_de LANGUAGE_IDENTIFIER=german.germany.8859 I guess the first two are informix related, the last two are just Linux NLS settings. Can anybody please give me a guiding hand? Thanks, Stefan

it says colmin notr found.... assume it's 7.24 (check maybe onstat -V?? oninit -V) try: oncheck -cc <yourdb>or if you do a select * from syscolumns of that db does it include colmin?? if it does not then someone has been mucking around with you system catalog. i suggest contact tech support. Superboer. On 23 feb, 11:49, olympus_m...@gmx.de wrote: > Hi, > > I'm given the task to unload an Informix datase to port the database > to Oracle. The Informix-DB is on a SUSE Linux server. When I try to > unload a table schema using DBSCHEMA i get error > -217 - column (colmin) not found in any table in the query > > DBSCHEMA says it is version 7.24.UC5 > > I found an old message about this problem related to NLS settings. As > I'm an absolute Linux and Informix beginner I have no idea what I need > to set or check. > > In DBACCESS the database tells me these NLS settings: > en_US.819 Collating Sequence > en_US.819 CType > > The Linux system has none of the DB_LOCAL or CLIENT_LOCALE environment > variables set, as mentioned in the old post to this forum. But env > shows among others the following variables: > DBDATE=DMY4.> DBDELIMITER='^' > LANG=de_de > LANGUAGE_IDENTIFIER=german.germany.8859 > > I guess the first two are informix related, the last two are just > Linux NLS settings. > > Can anybody please give me a guiding hand? > Thanks, > Stefan

olympus_mons@gmx.de wrote: > Hi, > > I'm given the task to unload an Informix datase to port the database > to Oracle. The Informix-DB is on a SUSE Linux server. When I try to > unload a table schema using DBSCHEMA i get error > -217 - column (colmin) not found in any table in the query > > DBSCHEMA says it is version 7.24.UC5 > > I found an old message about this problem related to NLS settings. As > I'm an absolute Linux and Informix beginner I have no idea what I need > to set or check. > > In DBACCESS the database tells me these NLS settings: > en_US.819 Collating Sequence > en_US.819 CType > > The Linux system has none of the DB_LOCAL or CLIENT_LOCALE environment > variables set, as mentioned in the old post to this forum. But env > shows among others the following variables: > DBDATE=DMY4.> DBDELIMITER='^' > LANG=de_de ^^^^^^^^^^^^^ This might be it. Your NLS setting in your environment does not match the one used to create the database. That sometimes causes unusual problems. Try unsetting LANG or setting it to 'en_US'. Art S. Kagel > LANGUAGE_IDENTIFIER=german.germany.8859 > > I guess the first two are informix related, the last two are just > Linux NLS settings. > > Can anybody please give me a guiding hand? > Thanks, > Stefan >

Don't think colmin was in syscolumns back then. Check your version of dbexport/dbschema. j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Superboer Sent: Friday, February 23, 2007 7:33 AM To: informix-list@iiug.org Subject: Re: dbschema error 217 column (colmin) not found in any table it says colmin notr found.... assume it's 7.24 (check maybe onstat -V?? oninit -V) try: oncheck -cc <yourdb>or if you do a select * from syscolumns of that db does it include colmin?? if it does not then someone has been mucking around with you system catalog. i suggest contact tech support. Superboer. On 23 feb, 11:49, olympus_m...@gmx.de wrote: > Hi, > > I'm given the task to unload an Informix datase to port the database > to Oracle. The Informix-DB is on a SUSE Linux server. When I try to > unload a table schema using DBSCHEMA i get error > -217 - column (colmin) not found in any table in the query > > DBSCHEMA says it is version 7.24.UC5 > > I found an old message about this problem related to NLS settings. As > I'm an absolute Linux and Informix beginner I have no idea what I need > to set or check. > > In DBACCESS the database tells me these NLS settings: > en_US.819 Collating Sequence > en_US.819 CType > > The Linux system has none of the DB_LOCAL or CLIENT_LOCALE environment > variables set, as mentioned in the old post to this forum. But env > shows among others the following variables: > DBDATE=DMY4.> DBDELIMITER='^' > LANG=de_de > LANGUAGE_IDENTIFIER=german.germany.8859 > > I guess the first two are informix related, the last two are just > Linux NLS settings. > > Can anybody please give me a guiding hand? > Thanks, > Stefan _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

First, thanks to all for trying to help me. I checked a few things: (1) there is no oncheck, oninit or onstat (at least in none of the bin's in path, nor in informix' bin directory) (2) dbschema says it's version 7.24.UC5 (3) there is no colmin colum. I did a select * from syscolumns where colname = 'colmin' (4) that said, can it really be a problem of the LANG setting? when I login as root the commandline says "bash 2.03#" how can I set LANG to "en_us". As I said I'm not a Unix guy. I tried "set LANG=" but this does not reset LANG. (5) This is on a SuSE Linux Version 6.2 which was installed at customers site way back in 1999. The company that once developed the database and the application no longer exists. The customer has a sysadmin that knows nothing about Linux or Informix, they know how to start and stop the server, that's all. This is why they want to port the whole app to oracle. This thing is a ticking time bomb, but nobody knows when it will blast... I did some unix stuff back in the 80's when I was at universitiy but this stuff seems to be swapped from my brain... Stefan

dbaccess -V or $INFORMIXDIR/lib/sqlexec -V may tell the exact version,sounds like it's a standard engine. as far as i can remember there should be a column called colmin anyway, it's used for the optimizer?????!!!!!!! even V 5 had colmin if i recall correctly.... so it sound like someone has mucked around in the catalog???? OR dependant on what the version really is, you may need to find the proper dbschema??? or you can try and reconstruct the schema;s of the tables. using the catalog. -->> for this grab the guide to sql, syntax, reference , tutorial and look for the system catalog. even if the version manual is of a later date, ytou should be able to work out what you need. Superboer. ahum assume that unload to somefile.unl select * from sometable works .... On 23 feb, 14:42, olympus_m...@gmx.de wrote: > First, thanks to all for trying to help me. > > I checked a few things: > > (1) there is no oncheck, oninit or onstat (at least in none of the > bin's in path, nor in informix' bin directory) > > (2) dbschema says it's version 7.24.UC5 > > (3) there is no colmin colum. I did a > select * from syscolumns where colname = 'colmin'> > (4) that said, can it really be a problem of the LANG setting? > when I login as root the commandline says "bash 2.03#" > how can I set LANG to "en_us". As I said I'm not a Unix guy. I tried > "set LANG=" but this does not reset LANG. > > (5) This is on a SuSE Linux Version 6.2 which was installed at > customers site way back in 1999. The company that once developed the > database and the application no longer exists. The customer has a > sysadmin that knows nothing about Linux or Informix, they know how to > start and stop the server, that's all. This is why they want to port > the whole app to oracle. This thing is a ticking time bomb, but nobody > knows when it will blast... > > I did some unix stuff back in the 80's when I was at universitiy but > this stuff seems to be swapped from my brain... > > Stefan

> > how can I set LANG to "en_us". As I said I'm not a Unix guy. I tried > > "set LANG=" but this does not reset LANG. dependant on what shell i guess bash: export LANG=<something> -- This is why they want to port the whole app to oracle hum you will be better of using informix..... Superboer. On 23 feb, 16:14, "Superboer" <superbo...@t-online.de> wrote: > dbaccess -V or $INFORMIXDIR/lib/sqlexec -V may tell the exact version,> sounds like it's a standard engine. > > as far as i can remember there should be a column called colmin > anyway, it's used for the optimizer?????!!!!!!! > even V 5 had colmin if i recall correctly.... > > so it sound like someone has mucked around in the catalog???? > > OR dependant on what the version really is, you may need to find the > proper dbschema??? > > or you can try and reconstruct the schema;s of the tables. > using the catalog. > -->> for this grab the guide to sql, syntax, reference , tutorial and > look for the system catalog. > even if the version manual is of a later date, ytou should be able to > work out what you need. > > Superboer. > > ahum assume that unload to somefile.unl select * from sometable > works .... > > On 23 feb, 14:42, olympus_m...@gmx.de wrote: > > > First, thanks to all for trying to help me. > > > I checked a few things: > > > (1) there is no oncheck, oninit or onstat (at least in none of the > > bin's in path, nor in informix' bin directory) > > > (2) dbschema says it's version 7.24.UC5 > > > (3) there is no colmin colum. I did a > > select * from syscolumns where colname = 'colmin'> > > (4) that said, can it really be a problem of the LANG setting? > > when I login as root the commandline says "bash 2.03#" > > how can I set LANG to "en_us". As I said I'm not a Unix guy. I tried > > "set LANG=" but this does not reset LANG. > > > (5) This is on a SuSE Linux Version 6.2 which was installed at > > customers site way back in 1999. The company that once developed the > > database and the application no longer exists. The customer has a > > sysadmin that knows nothing about Linux or Informix, they know how to > > start and stop the server, that's all. This is why they want to port > > the whole app to oracle. This thing is a ticking time bomb, but nobody > > knows when it will blast... > > > I did some unix stuff back in the 80's when I was at universitiy but > > this stuff seems to be swapped from my brain... > > > Stefan

Superboer, thanks for your feedback. Here is some more info: dbaccess -V says: DB-Access Version 7.24.UC5 dbschema -V says: Informix-SQL Version 7.24.UC5 isql -V says Informix-SQL 7.20.UD7 there is no sqlexec in /opt/informix/bin and yes it is a SE standard engine server unload to works, I tried that with one table. I exported to a CSV-file using ';' as delimiter.I need to unload about 30 tables. It would be great if I can also get the schema files using dbschema. using dbaccess I can log into the database and get the columns table by table via the menu but this will be tedious... And yes I can work through the meta tables to get the info about colums, indexes etc. Another idea is to do a reverse engineering using ERwin. I already downloaded and installed the client SDK from IBM's site. But up to know I was not able to figure out what I need to connect. I also have the feeling, that on the server side there is no listener. Which brings up the next question: how can I configure a listener? I already know (by fiddling arround on the server): - the host name - the informix server name - the database name - user-ID and passwort - the protocoll (seipcpip or sesoctcp) I do not know: - the service name (i.e. the alias name for the port on which the infromix server is listening) - on the server side, there is no entry in the services file, at least none that I would match to informix. ONTH on the server itsself, the port alias must not be configured at all... >From the client (a WinXP notebook) I tried to configure a DSN but failed, because I don't know the port number for the listener. How can I find out if there is a listener on the linux server for Informix? And if there is none, how can I set it up... The existing legacy app ist written in 4GL and is started on the windows clients using wtk(?). I know it's Friday afternoon, but maybe someone ist still busy... TIA, Stefan

ok, I'm able to change the env variables now. setting LANG to "en_US" or "en_US.819" has no effect on dbschema. But setting DB_LOCALE to "en_US" at least brings an error message that it differs from the database' setting So I tried "en_US.819" as this exactly matches the database setting. But then again I get the error 217 column (colmin) missing. I really guess that somebody messed up the system. I found check_version command: "check_version csdk" tells me that the current installed version is an older version than the previous version. But I do not dare to change anything to this confuguration, as I don't want to be the one who gets hanged, when the informix database stops working... arrrg!

Ok, I was wrong. checking the 7.22 sqlref manual (chap 2). colmin was there back then. If this is an se version, then all of the (few) utilities are prefixed with 'se' - so oncheck is really secheck. You might check for those. I don't recall, but sqlexec may be called sqlturbo. I believe you would need the sqlexecd daemon running (ps -ef | grep sqlexecd to see if it is) for network connectivity. From the Suse box itself, you should not need that. Your options for export are dbexport, and unload (which you seem to have run across). dbexport will export all tables along with the schema - it will probably run into the same colmin not found problem. Be aware that when you unload data using a delimiter of "," any occurences of a comma in the actual data will be preceeded by an escape character. So (table.col="abc,def") will unload as "abc\\,def". I like Art's suggestion that this is an environmental issue. Are you performing your work as the informix user? If not, check what environment that user has (log in as informix and 'env'). Environment variables you should have: DBPATH, INFORMIXDIR, INFORMIXSERVER, INFORMIXSQLHOSTS (this is by default $INFORMIX/etc/sqlhosts), INFORMIXTERM, PATH, SQLEXEC, TERM. Of course check the other variables mentioned in other notes. If you look in $INFORMIXDIR/etc/sqlhosts you should see the service name (last field) listed for the engine (first field). That service should be listed in /etc/services and specify the port number. If you want to continue with the working around dbschema issues, there is an article on system catalogues at developer works that may help: http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle /0305parker/0305parker.html. j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of olympus_mons@gmx.de Sent: Friday, February 23, 2007 8:42 AM To: informix-list@iiug.org Subject: Re: dbschema error 217 column (colmin) not found in any table First, thanks to all for trying to help me. I checked a few things: (1) there is no oncheck, oninit or onstat (at least in none of the bin's in path, nor in informix' bin directory) (2) dbschema says it's version 7.24.UC5 (3) there is no colmin colum. I did a select * from syscolumns where colname = 'colmin' (4) that said, can it really be a problem of the LANG setting? when I login as root the commandline says "bash 2.03#" how can I set LANG to "en_us". As I said I'm not a Unix guy. I tried "set LANG=" but this does not reset LANG. (5) This is on a SuSE Linux Version 6.2 which was installed at customers site way back in 1999. The company that once developed the database and the application no longer exists. The customer has a sysadmin that knows nothing about Linux or Informix, they know how to start and stop the server, that's all. This is why they want to port the whole app to oracle. This thing is a ticking time bomb, but nobody knows when it will blast... I did some unix stuff back in the 80's when I was at universitiy but this stuff seems to be swapped from my brain... Stefan _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

On 23 Feb, 15:41, olympus_m...@gmx.de wrote: > Superboer, > > thanks for your feedback. Here is some more info: > > dbaccess -V says: > DB-Access Version 7.24.UC5> > dbschema -V says: > Informix-SQL Version 7.24.UC5> > isql -V says > Informix-SQL 7.20.UD7 > > there is no sqlexec in /opt/informix/bin > and yes it is a SE standard engine server > If it is standard engine then there should be an sqlexec binary somewhere under $INFORMIXDIR. It may be in lib rather than bin. > unload to works, I tried that with one table. I exported to a CSV-file > using ';' as delimiter.> I need to unload about 30 tables. It would be great if I can also get > the schema files using dbschema. using dbaccess I can log into the > database and get the columns table by table via the menu but this will > be tedious... > > And yes I can work through the meta tables to get the info about > colums, indexes etc. > > Another idea is to do a reverse engineering using ERwin. I already > downloaded and installed the client SDK from IBM's site. But up to > know I was not able to figure out what I need to connect. I also have > the feeling, that on the server side there is no listener. Which > brings up the next question: how can I configure a listener? > You need to look for a binary called sqlexecd somewhere under $INFORMIXDIR (probably in lib again). Run sqlexecd -h to see the options. Don't forget to use the -l <logfile> option as that is useful for debugging! From http://publib.boulder.ibm.com/epubs/pdf/8698a.pdf the listener comes in a seperate product Informix-Net. > I already know (by fiddling arround on the server): > - the host name > - the informix server name > - the database name > - user-ID and passwort > - the protocoll (seipcpip or sesoctcp) > > I do not know: > - the service name (i.e. the alias name for the port on which the > infromix server is listening) > - on the server side, there is no entry in the services file, at least > none that I would match to informix. ONTH on the server itsself, the > port alias must not be configured at all... > > >From the client (a WinXP notebook) I tried to configure a DSN but > > failed, because I don't know the port number for the listener. > How can I find out if there is a listener on the linux server for > Informix? And if there is none, how can I set it up... > > The existing legacy app ist written in 4GL and is started on the > windows clients using wtk(?). > > I know it's Friday afternoon, but maybe someone ist still busy... > > TIA, > Stefan

Wow - Thanks all for your help! I really appreciate that. I don't know if I will find the time next week to do some further test (i.e. connectivity from my notebook) as I have some urgent work to get done until Friday. I can confirm that there is a sqlexec and sqlexed (the daemon?) in lib. in sqlhosts file there are two lines that have a each a different database name, a protocol (one seipcpip the other sesoctcp), same host name and both sqlexec. From the output of the ps command I can see that there is no sqlexec or sqlexecd running. Art, if you are still "listening", from what I've posted about changing DB_LOCALE can you give me some more help to find out if this whole problem is really a NLS/environment problem. Thanks, STefan

> I need to unload about 30 tables. It would be great if I can also get > the schema files using dbschema. using dbaccess I can log into the > database and get the columns table by table via the menu but this will > be tedious... You may have a look at esql or java/jdbc specially the stuff about metadata. ( IfmxResultSetMetaData ) Based on this you may cook up a program which generates the create table sql. Superboer. try createing a test database. then check if a column colmin is present. would really sprise me if there is no colmin.