Hi,

My question may/could be really trivial, but I'm in big hurry and need accurate expert advice quickly, so pardon me if I may appear too candid (some googling didn't gave me any clear answer).

I have been assigned the task to perfom an Informix databse data dump, for backup purpose and replication. The problem is that there is absolutely NO ONE (including me) experimented with Informix who knows how to do this.

Since this task is of medium priority, and since I'm currently overbooked with other of highest priority, I try to avoid by all means an expensive try/catch error experimentation. I can't afford the time to play with tools I don't know, which could costs me several day.

So I beg an experienced administrator for help !

Thanks in advance for your wisdom, and pardon my bad english,

sne.

Do you have a tapedevice mounted to the system or accessible in the network? I think the 'ontape' utility is the easiest way of performing a complete datadump, including logical logs, of data managed by a server. In order to restore the data from this tape you need a similarly configured target server. So restore to the same IDS will work fine, for replication to another IDS it must be a copy of the source server.

To get a (textfile) dump of a complete database, including schema, for migration to another server/OS probably the 'dbexport' and 'dbimport' utilities are the simplest.

So if I wanted to replicate a database to another machine where IDS was already installed I would use on UNIXto produce a dump in the 'database.exp' subdir under filename or on tape. With the database can be created on the second (UNIX) machine. Both the servers can be on-line for this.

Once the replication database(s) is (are) defined you can make an on-line IDS dump withand restore off-line to IDS withFor 'ontape' though the TAPEDEV/TAPEBLK/TAPESIZE parameters in the onconfig file(s) must be set to the particular tapedevice. Check with If it's not done already you must assign values to these parameters in the onconfig file (echo $ONCONFIG) and stop and initialise IDS again for the new onconfig file to become effective.

Another, and possible better, way to dump on-line data with more options but also more configuration necessary is to use ON-Archive, but I'm not familiar with that.

Regards

So first of all, thanks for your answer. That looks good.

I tried the dbexport command but didn't succeeded to have it work.
Here is my command line trace :

E:\belgacom\Informix\bin>dbexport -ss -o c:\ belgacom@belgacom
-387 - No connect permission.
-111 - ISAM error: no record found.

What did I missed ?

I tried connecting via dbaccess, exited and typed the dbexport command but that doesn't changed anything.

Thanks in advance for your help,

sne.

Are you logged in as a DBA? I usually do things like this as 'informix'. And I don't know if the database@server syntax is correct in this case; try, on the Belgacom host, and before that, create directory C:\belgacom.

BTW: Keep in mind that dbexport is NOT the right utility for regular backups...

Grts

You didn't give it the database name you are attempting to export.

Ok.

So, I think i didn't gave enough informations, so let's clarify some points.

Environment
------------
I have an informix BDD, named 'mydbtest', on a windows 2000 server.
I'm logged as 'administrator' on this server.

What I try
----------
I launch an informix console that set the env vars for mydbtest.

I use the command
E:\mydb\Informix\bin>dbexport -ss -o c:\mydb mydb

and I get a

-387 - No connect permission.
-111 - ISAM error: no record found.

(I've created the directory c:\mydb before, as hinted)

I hence tried :

I call 'dbaccess'
I 'connect' myself.
I select 'mydb'.
I enter login/password.
I choose the db mydb@mydb
I am connected.
I exit dbacces.

Back on the concole,
E:\mydb\Informix\bin>dbexport -ss -o c:\mydb mydb
this time I get a

-425 - Database is currently opened by another user.
-107 - ISAM error: record is locked.

So... what did I missed ? Is there a 'connect' command I must type before dbexport ?

Again, thanks for your help,

sne.

Can you tell me what the %username% is after running the ol_belgacom.cmd (or something like that) file? It should not be 'administrator' unless that account has dba privleges granted. As I said, I would use account 'informix' for at time of installation in NT this account is created as a member of the 'administrators' group and automatically gets dba privileges granted. If this account still exists with these properties you don't need any other account to perform dbexport.

I'm not familiar with the term BDD, my Windows experience with Informix is limited to IDS (Informix Dynamic Server) 7.3 on WinNT4. On Win2000 and with IDS 2000 things are done differently I believe, I've no knowledge af that.

Regards

Hi, I just looked at your last post again and discovered I gave an answer that didn't match your problem. Apparently the way you tried to perform dbexport was the right one because from your error output I make up that you got connected but the database needed to be in quiescent (single user) mode for the operation.
Something I appeared to be forgotten because initially I stated that the server could be in on-line mode for dbexport. In the Linux environment I work with it happens every night on a replication server but then it's in single user mode of course. And since at home I'm the only one using the database I only work in single user mode... Sorry for the misleading statement in this.
So now you're forced to do some overtime for this dbexport? If it's just a backup you want you still should consider 'ontape'; writing definitely can be done while the database is in concurrent state.

Grts

Hi folks.

So, many thanks for your advices. I made several signifiant progress and dbexport works now.

But I now have some other problems...

1, REIMPORTING THE DATA
----------------------------
I launch (logged in NT as 'informix' user)
E:\mydb\Informix\bin>dbexport -ss -o c:\mydb mydb
and everything goes obviously fine.

I now have a directory
c:\mydb\mydb.exp
with lots of files (obviously a file per table, and another file with no extension, namely 'mydb', that contains some sql code).

Now, when I try
dbimport -c -i c:\mydb\mydb.exp mydbcopy
I get a 'could not find the sql file'

I tried
dbimport -c -i c:\mydb\mydb.exp\mydb mydbcopy

added '.sql' to the file with no extension, then
dbimport -c -i c:\mydb\mydb.exp\mydb.sql mydbcopy

but none worked.

What am I missing ???

Please note that I want to import the 'mydb' dump in the 'mydbcopy' database.

2, CONNECTING TO THE DATABASE WITH A JAVA CLIENT
-------------------------
I'm using DbVisualizer to browse my database (and occasionnaly, netbeans 5.5 database browser, with the same result).

I have several databases, db1, db2 and db3 on my Informix Dynamic Server.
I can connect without any problem to db1 and db3.
When I try to connect to db2, I got this error message (formated by DbVisualizer)


-------------------------------------------------------
An error occured while executing the database request for:
Informix Dynamic Server
9.40.XX
Informix JDBC Driver for Informix Dynamic Server
XXXXXXXXX

The command that caused the error was:
getTables

Error Details:
Type: java.sql.SQLException
Error Code: -229
SQL State: IX000

Error Message:
Could not open or create a temporary file.
-----------------------------------------

I checked the faq and documentations for this kind of error (-229, could not open..), but what I found always points toward disk space or acces rights.

Since I've got lots of gigs of free space on all my disks, it's probably a problem of access rights...
But db2, so as db1 and db3, is accessed by a J2EE application without any problem, and I can launch a 'dbaccess' on it without any problem.

A little bit weird... why does java client trigger this error on db2 only ?

BTW, it's mentionned in the faqs that Dynamic Server doesn't use the DBTEMP var to choose his 'temp' directory anymore, but it's not specified WHAT directory it then use as a temporary directory.

Again, any help would be more than appreciated !

As a side note, don't worry about the term BDD, it's just a localized accronym for 'database'. My mistake !

Regarding your import.

First of all, you can't re-import mydb and call it mydbcopy without tricking dbimport. Given your file structure in the example you will need to:
1. rename c:\mydb\mydb.exp to c:\mydb\mydbcopy.exp
2. rename c:\mydb\mydb.exp\mydb.sql to c:\mydb\mydbcopy.exp\mydbcopy.sql

Second, you need to be pointing to the directory that the export directory (xxx.exp) is in. So your import statement would look like:

dbimport -c -i c:\mydb mydbcopy

Yep ! That's it ! Many thanks, it finally 'works'.

I've almost finalized a little script now.
His goal is to replicate a DB (db1) datas into ànother DB (db2).

I'm able to export data, rename folders and file (to trick the import, as hinted by fprose), and launch dbimport.
BUT I have an error when importing, talking about "duplicate unique key".

So I suppose I must drop the database db2 before importing datas ?

So the question is : how can I drop it with a command line ??? (btw I tried 'dbaccess' tool - unusable in my sfcript of course - and I never succeeded in dropping it).

Again... thanks !

You could try this entry in your script: Where the 'SQL!' keyword is just a label that marks the beginning of the statementlist and must be repeated on a newline at the end. But I'm afraid it only works in UNIX environments though...

Don't know if your if your familiar with the location of the Informix guides, but here you could easily check out many specific things, e.g. usage of dbimport, dbaccess etc. (even some things in French, if that's what your native language is...)
http://www-306.ibm.com/software/data...ary/ids_9.html

Hans