Help needed in transfering or backing up/restoring Informix database via ODBC.

[v***@gmail.com]

I've recently encountered a full file system failure on my Informix
server, it's running Informix IDS 9.20 (Linux); access to the Informix
tools is not available, changing the allow.hosts file isn't an option
(as the file system is damaged); otherwise I'd setup remote access
(i.e. dbaccess/dbexport, etc.) - presently Informix is online, the
dbspace's and databases have yet to be compromised.

So here's the situation, I have ODBC access, I have a backup that is a
month old, so I have a current/correct schema from dbexport; I've
attempted to export all the tables into SQL files via WinSQL (and a
couple of other products); however after creating a new database (new
server); attempts at importing (i.e. INSERT's from the .SQL files) have
either failed due to the size of the .SQL files, or due to WinSQL's
method of export (creates long statements and Informix kick's back a
460 error). A few other products couldn't go over 1940 records.

I've also tried a direct migration from one table to another table (old
server/table to new server/table), that worked fine on simple tables,
but any tables with key constraints failed; I'm not certain if I should
create a new database without the keys, import (i.e. migrate/transfer);
then create the keys as it would occur in the dbexport, i.e create
table, unload, create key, create index.

So that's the situation, the server is on borrowed time; so I either
need to find a decent utility that can export/migrate/transfer/import
an Informix database via ODBC; or figure out a way to handle the key
constraint errors when I'm attempt to migrate/transfer from old-to-new
in WinSQL.

Any suggestions, ideas, are definitely welcomed.

[d***@smooth1.co.uk]

Post by v***@gmail.com
I've recently encountered a full file system failure on my Informix
server, it's running Informix IDS 9.20 (Linux); access to the Informix
tools is not available, changing the allow.hosts file isn't an option
(as the file system is damaged); otherwise I'd setup remote access
(i.e. dbaccess/dbexport, etc.) - presently Informix is online, the
dbspace's and databases have yet to be compromised.
So here's the situation, I have ODBC access, I have a backup that is a
month old, so I have a current/correct schema from dbexport; I've
attempted to export all the tables into SQL files via WinSQL (and a
couple of other products); however after creating a new database (new
server); attempts at importing (i.e. INSERT's from the .SQL files) have
either failed due to the size of the .SQL files, or due to WinSQL's
method of export (creates long statements and Informix kick's back a
460 error). A few other products couldn't go over 1940 records.
I've also tried a direct migration from one table to another table (old
server/table to new server/table), that worked fine on simple tables,
but any tables with key constraints failed; I'm not certain if I should
create a new database without the keys, import (i.e. migrate/transfer);
then create the keys as it would occur in the dbexport, i.e create
table, unload, create key, create index.

Go to www.iiug.org and the Software section. Get Art Kagels utils_ak2
and use dbcopy from there to copy the data.

The new tables should not have contraints or indexes on them whilst the
copy is happening.

Post by v***@gmail.com
So that's the situation, the server is on borrowed time; so I either
need to find a decent utility that can export/migrate/transfer/import
an Informix database via ODBC; or figure out a way to handle the key
constraint errors when I'm attempt to migrate/transfer from old-to-new
in WinSQL.
Any suggestions, ideas, are definitely welcomed.

[v***@gmail.com]

David,
Unfortunately I only have ODBC access, the server is not configured to
trust another INFORMIXSERVER nor can I add trust via allow.hosts (or
.rhosts) due to the level of file corruption on the failing server.
Typically I'd access the server via DBACCESS and DBEXPORT remotely
providing a trust relationship was established with the source location
(workstation/server IP, username, etc. ) - that isn't the case here. If
I attempt to access the server with such tools, I'm greeted with a
"Client host or user (***@myaddress.com) is not trusted by the
server."

So basically unless a utility exists with the capability of dbexport or
dbcopy that can bypass or ignore the client host/use trust
restrictions; I'm limited solely to ODBC and the server is truly on
borrowed time. (If the Informix server were to be shutdown, it would
never start again due to the level of corruption at the filesystem
level)

Thanks!

Post by d***@smooth1.co.uk
Go to www.iiug.org and the Software section. Get Art Kagels utils_ak2
and use dbcopy from there to copy the data.
The new tables should not have contraints or indexes on them whilst the
copy is happening.

[Clive Eisen]

Post by v***@gmail.com
David,
Unfortunately I only have ODBC access, the server is not configured to
trust another INFORMIXSERVER nor can I add trust via allow.hosts (or
.rhosts) due to the level of file corruption on the failing server.
Typically I'd access the server via DBACCESS and DBEXPORT remotely
providing a trust relationship was established with the source location
(workstation/server IP, username, etc. ) - that isn't the case here. If
I attempt to access the server with such tools, I'm greeted with a
server."

can you block the server in a checkpoint and dd the chunks over scp? You
don't need a trusted relationship, just informix or root access over ssh.

Then install the same version of IDS on the same sort of hardware and bingo!

[v***@gmail.com]

Hi Clive,
SSH nor DD is available, the majority of all tools and their subsequent
libraries have been corrupted; the database is online simply due to the
fact the chunks are located on a different drive.

Now if I could use the raw chunks from the good drive, i.e. replace the
faulty drive, install OS, re-install Informix and then access the
chunks that were created previously; that might be an option; however
I'm unable to backup anything in the /opt/informix directory; so
essential configuration files are not accessible. Last I checked (years
ago), using those previous "good" chunks isn't an option; or is it?

At present I have a replacement server, same OS, same IDS version,
virtually the same hardware online, I'm exporting all the data to SQL
files (inserts); and then I'm slowly attempting to import that data
into the new server; fortunately I had a working schema, so the
database and subsequent table structure is identical - the downside is
the .SQL files were formatted with long statements (i.e. -460 errors);
and I'm having issues with the ODBC tools crashing due to the .SQL file
sizes.

In attempt to resolve the long statement errors, I'm editing each
file, running the INSERT's and then comparing the data. This process is
consuming a monumental amount of time and the outcome is uncertain as
the tools are unpredictable.

Thanks!

Post by Clive Eisen
can you block the server in a checkpoint and dd the chunks over scp? You
don't need a trusted relationship, just informix or root access over ssh.
Then install the same version of IDS on the same sort of hardware and bingo!

[Clive Eisen]

Post by v***@gmail.com
Hi Clive,
SSH nor DD is available, the majority of all tools and their subsequent
libraries have been corrupted; the database is online simply due to the
fact the chunks are located on a different drive.

I hope somebody is paying you well :-)

Can you list what you know does work on this machine - or is it just the
running IDS?

Do you know the path to all the chunks?

Post by v***@gmail.com
Now if I could use the raw chunks from the good drive, i.e. replace the
faulty drive, install OS, re-install Informix and then access the
chunks that were created previously; that might be an option; however
I'm unable to backup anything in the /opt/informix directory; so
essential configuration files are not accessible. Last I checked (years
ago), using those previous "good" chunks isn't an option; or is it?

In order to get a running system from just the chunks AFAIK you need
three things - the same version of IDS, the path to the root chunk and
all the chunks in the same place as before. And you can get all of this
from your month old backup.

Post by v***@gmail.com
At present I have a replacement server, same OS, same IDS version,
virtually the same hardware online, I'm exporting all the data to SQL
files (inserts); and then I'm slowly attempting to import that data
into the new server; fortunately I had a working schema, so the
database and subsequent table structure is identical - the downside is
the .SQL files were formatted with long statements (i.e. -460 errors);
and I'm having issues with the ODBC tools crashing due to the .SQL file
sizes.

Good plan, but as you say fraught with difficulty.

I'd work on this in order to get copies of the data in case all else fails.

Then move the drive out and COPY IT to another drive using dd and
someone looking over your shoulder to make sure you get the dds round
the right way ( I might even put the read only jumper on the drive if it
has one ) and then try in start IDS on the replacement server with the copy.

Post by v***@gmail.com
In attempt to resolve the long statement errors, I'm editing each
file, running the INSERT's and then comparing the data. This process is
consuming a monumental amount of time and the outcome is uncertain as
the tools are unpredictable.

YUK

Post by v***@gmail.com
Thanks!

You have my deepest sympathy in this
--
Clive

[Simmons, Keith]

VP

usually if you run dbaccess and the Connect to a non-trusted host
you get a username/password dialogue. If you enter a valid c
combination for he target host you should be able to get access
(unless your assword/sshadow files are corrupt).

Keith

-----Original Message-----
From: ***@gmail.com [mailto:***@gmail.com]
Sent: Wednesday, June 07, 2006 11:32
To: informix-***@iiug.org
Subject: Re: Help needed in transfering or backing up/restoring Informix
database via ODBC.


David,
Unfortunately I only have ODBC access, the server is not configured to
trust another INFORMIXSERVER nor can I add trust via allow.hosts (or
.rhosts) due to the level of file corruption on the failing server.
Typically I'd access the server via DBACCESS and DBEXPORT remotely
providing a trust relationship was established with the source location
(workstation/server IP, username, etc. ) - that isn't the case here. If
I attempt to access the server with such tools, I'm greeted with a
"Client host or user (***@myaddress.com) is not trusted by the
server."

So basically unless a utility exists with the capability of dbexport or
dbcopy that can bypass or ignore the client host/use trust
restrictions; I'm limited solely to ODBC and the server is truly on
borrowed time. (If the Informix server were to be shutdown, it would
never start again due to the level of corruption at the filesystem
level)

Thanks!

Post by d***@smooth1.co.uk
Go to www.iiug.org and the Software section. Get Art Kagels utils_ak2
and use dbcopy from there to copy the data.
The new tables should not have contraints or indexes on them whilst the
copy is happening.

_______________________________________________
Informix-list mailing list
Informix-***@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

***********************************************************************************************
This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission.

This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems.
***********************************************************************************************

[v***@gmail.com]

Hi Keith,
I've tried a couple different configuration options between servers
in attempt to access the failing server via DBACCESS and/or DBEXPORT,
all return with a -956 error; same user names, passwords and similar
configurations were attempted; a typical resolve would be a simple
addition to the .rhosts file in the /home/root (and possibly
/home/informix) directories; due to the filesystem damage that isn't
an option; FTP, SU, VI or similar type tools isn't executable
(library corruption). Not to mention the server is off-site, so command
line access has been limited telephone communication; the filesystem is
that badly damaged and I until a backup/export of the data is taken and
confirmed; I'm avoiding any attempts to correct and/or address the
filesystem damage as the server may never come online again. :(

Ultimately the filesystem hard drive is a total loss, Informix is
online as it's all inclusive in memory and the chunks are on a
separate drive.

Well, I'm back to editing long statement .SQL files (truncating them
for Informix usage); thank you (to everyone) that has replied; any
additional thoughts, ideas are certainly welcomed!

Thanks!

Post by Simmons, Keith
VP
usually if you run dbaccess and the Connect to a non-trusted host
you get a username/password dialogue. If you enter a valid c
combination for he target host you should be able to get access
(unless your assword/sshadow files are corrupt).
Keith

[v***@gmail.com]

I've since established DBACESS via TCP by specifying name and password;
however I'm unable to use any of the tools needed; i.e. dbexport,
myschema, myexport - due to the trust error encountered (-956).

Is there a tool similar to dbexport or myexport that allows specifying
hostname, username and password.

Thanks!

[scottishpoet]

UNLOAD TO "filename"
SELECT * FROM <tablename>

or to generate the unload statements you could run

SELECT "UNLOAD TO " || TRIM(tabname) ".unl SELECT * FROM " || tabname
FROM systable
WHERE tabid >= 100

save the output in a file and run the file (you may need to remove the
column heading "expression")

Post by v***@gmail.com
I've since established DBACESS via TCP by specifying name and password;
however I'm unable to use any of the tools needed; i.e. dbexport,
myschema, myexport - due to the trust error encountered (-956).
Is there a tool similar to dbexport or myexport that allows specifying
hostname, username and password.
Thanks!

[scottishpoet]

Post by scottishpoet
UNLOAD TO "filename"
SELECT * FROM <tablename>
or to generate the unload statements you could run
SELECT "UNLOAD TO " || TRIM(tabname) ".unl SELECT * FROM " || tabname
FROM systable
WHERE tabid >= 100
save the output in a file and run the file (you may need to remove the
column heading "expression")

Ooops

systables not systable

[v***@gmail.com]

scottishpoet,
Are you using sqlcmd to perform the following:

SELECT "UNLOAD TO " || TRIM(tabname) ".unl SELECT * FROM " || tabname
FROM systables
WHERE tabid >= 100

Thanks!

Post by scottishpoet
UNLOAD TO "filename"
SELECT * FROM <tablename>
or to generate the unload statements you could run
SELECT "UNLOAD TO " || TRIM(tabname) ".unl SELECT * FROM " || tabname