Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: Database read access while dbexport is running

    Hi Folks,
    I'm new to this forum and new to informix databases. I've got a - I guess - simple question I wasn't able to find an answer for.

    Can I execute reading queries while dbexport is running? Do I need a special user for this, e.g. a user with read-only rights?

    Background: An backup system sends me a status mail with this content:
    -425 - Database is currently opened by another user.
    -107 - ISAM error: record is locked.
    and I guess that the reason is an open connection from a user with write rights. Is this correct?

    Many thanks in advance.
    Greetings
    Johannes

  2. #2
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Dbexport locks the database in exclusive mode while it is running, so an error is returned if it cannot obtain an exclusive lock.

    Regards
    Miike

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    Thanks for your reply, Mike.
    Unfortunately, I don't know in which cases a database is locked exclusively.
    Do I prevent dbexport from obtaining an exlusive lock if I have an open connection with a read-only user?

  4. #4
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    I'm afraid, you are not able to prevent it. Dbexport command uses lock mechanism to protect the data and keep it consistent, it dosn't check users rights, only active connections. I would be nice if IBM could add a new switch to dbexport command, that allows to choose lock mode. DBA's are rather clerver and they know what they are doing.

    You can use onunload instead of dbexport. Onunload is much faster than dbexport, but it has many more constraints placed on it than dbexport has. There are advantages and disadvantages to both commands, onunload locks the database in shared mode while it is running, but it can only spit out to
    binary file.

    Regards
    Mikef

  5. #5
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi johannes,


    sorry for the delay. depending on what you need to do with dbexport and what Informix server you are using Informix Dynamic Server, IBM Informix or Informix SE.

    If this is for a backup on Dynamic Server / IBM Informix, use ontape or onbar, this is the regular procedure.

    If this is to copy or migrate data to another server, effectively dbexport sets an exclusive lock ( well explained above by Mike).

    There are several options as workaround:
    1) write a sql script that emules dbexport that you will run with dbaccess
    something based on this principle
    Code:
    unload to mydb_export.sql
    SELECT " unload to /dirname/databasename/"||tabname || ".unl 
     SELECT * FROM " || tabname || ";"
    from systables where tabid > 99
    run it with dbaccess, get the mydb_export script, you may have
    to fix some layout issues to run it
    and you'll have what you want

    2) the other options is to download a great tool on the iiug.org site, in the software repository, called sqsl, which is a super power dbaccess, on which you'll probably find what you seek.
    info at SQSL
    download at
    ftp://ftp.iiug.org/pub/informix/pub/sqsl-0.06.tar.gz

    Solution # 1 should be quicker :-)
    Eric

Posting Permissions

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