Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Question Unanswered: Data Import / Export

    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.

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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 UNIX
    Code:
    dbexport -ss -o filename database   or: 
    dbexport -ss -t tapedevicename -b blocksize(KB) -s tapesize(KB) database
    to produce a dump in the 'database.exp' subdir under filename or on tape. With
    Code:
    dbimport -c -i filename database  or:
    dbimport -c -t tapedevicename -b blocksize(KB) -s tapesize(KB) database
    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 with
    Code:
    ontape -s -L 0
    and restore off-line to IDS with
    Code:
    ontape -r
    For 'ontape' though the TAPEDEV/TAPEBLK/TAPESIZE parameters in the onconfig file(s) must be set to the particular tapedevice. Check with
    Code:
    onstat -c | grep TAPE
    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

  3. #3
    Join Date
    Sep 2006
    Posts
    5

    perfect !

    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.

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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,
    Code:
    dbexport -cc -o C:\belgacom belgacom
    and before that, create directory C:\belgacom.

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

    Grts

  5. #5
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    You didn't give it the database name you are attempting to export.
    Fred Prose

  6. #6
    Join Date
    Sep 2006
    Posts
    5
    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.

  7. #7
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  8. #8
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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
    Last edited by Tyveleyn; 09-11-06 at 16:30.

  9. #9
    Join Date
    Sep 2006
    Posts
    5

    So !

    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 !

  10. #10
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    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
    Fred Prose

  11. #11
    Join Date
    Sep 2006
    Posts
    5
    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 !

  12. #12
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    You could try this entry in your script:
    Code:
    dbaccess databasename <<SQL!
    CLOSE databasename;
    DROP DATABASE databasename;
    SQL!
    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
    Last edited by Tyveleyn; 09-16-06 at 06:50.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •