Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2011
    Posts
    16

    Unanswered: Help need with backup script (Shell or SQL)

    OS - Ubuntu 10.04.02
    DB2 ver - 9.7.02
    Fix Pack - 2


    Hello All -

    I am fairly new to db2 and need assistance with creating a script to backup a db then restore it to another instance nightly so both db's match. In trying to figure out the best way to do this I want to create a script that will backup then restore so I started working ont he backup process. Within IBM Data Studio, I can execute a backup successfully. However if I take the command and either put it in a shell or sql script and run it on the target machine I get errors.

    Command given from IBM Data Studio:

    CONNECT RESET;
    QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    DEACTIVATE DATABASE VCF;
    BACKUP DATABASE VCF TO "/home/db2inst1/db2bak" COMPRESS EXCLUDE LOGS WITHOUT PROMPTING ;
    UNQUIESCE DATABASE;
    CONNECT TO VCF;



    When the above command is ran from IBM Studio, it runs perfectly, however when I take that command and export it as a .sql script and run it from the target machine I get the following error:

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL1024N A database connection does not exist. SQLSTATE=08003

    Thinking that there isn't a connection string in the script to connect to the database, I issued a db2 connect to vcf then ran the .sql script once again.
    Now I got the following error:

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following
    "export.sql". Expected tokens may include: "JOIN <joined_table>".
    SQLSTATE=42601



    So having no luck that route, I decide to try a shell script.

    db2 connect reset;
    db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    db2 DEACTIVATE DATABASE VCF;
    db2 BACKUP DATABASE VCF TO "/home/sysadmin/db2backup"COMPRESS EXCLUDE LOGS WITHOUT PROMPTING;
    db2 UNQUIESCE DATABASE;
    db2 CONNECT TO VCF;



    Error when running shell script:

    SQL1024N A database connection does not exist. SQLSTATE=08003
    : not found1:

    Database Connection Information

    Database server = DB2/LINUX 9.7.2
    SQL authorization ID = DB2INST1
    Local database alias = VCF

    DB20000I The QUIESCE DATABASE command completed successfully.
    : not found3:
    SQL1493N The application is already connected to an active database.
    : not found4:
    SQL1350N The application is not in the correct state to process this request
    Reason code="1".
    : not found5:
    DB20000I The UNQUIESCE DATABASE command completed successfully.
    : not found6:
    SQL0752N Connecting to a database is not permitted within a logical unit of
    work when the CONNECT type 1 setting is in use. SQLSTATE=0A001
    : not found7:



    I checked my connection state:

    Database Connection State

    Connection state = Connectable and Connected
    Connection mode = SHARE
    Local database alias = VCF
    Database name = VCF
    Hostname =
    Service name =


    So I know what I'm trying to accomplish is probably very simple but because of my lack of knowledge with db2 I'm running into problems. Any help is greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    As indicated in the manual, QUIESCE requires a database connection, so you should issue CONNECT RESET after QUIESCE, not before.

    To run an DB2 script, make sure the DB2 profile is executed, then issue db2 -tf yourscript.sql (assuming you use semicolons as delimiters).

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    1. use -v command option so you can see messages after commands;
    2. usually those 2 used together:
    db2 connect reset;
    db2 terminate;
    3. backup without log files is useless so you should use 'include logs' backup command option;
    4. you need to connect to DB again in order to unquiesce it;
    5. db2 compress was very slow in previous versions of db2, so instead it might be better zip the backup file after (might be different in v9.7 though), your db becomes available sooner, and then you can start your zip after backup is done.

    So your script should be something like this:

    db2 -v QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    db2 -v connect reset;
    db2 -v terminate;
    db2 -v backup db ... include logs ...
    db2 -v connect to ...
    db2 -v UNQUIESCE DATABASE;
    Last edited by MarkhamDBA; 06-17-11 at 12:13.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Mar 2011
    Posts
    16
    Thanks for the quick responses. I'm going to give the suggestions a try and will post back the results. Thanks again.

  5. #5
    Join Date
    Mar 2011
    Posts
    16
    OK...so first off please forgive my ignorace on this but I tried as you suggeted as a shell script. What I have been doing while testing is logon to ubuntu then su to db2inst1 and running the command sh db2bak.sh Listed below is the shell script and the subsequent error Bold that came after executing it.

    Shell

    db2 -v QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS; SQL1024N A database connection does not exist
    db2 -v connect reset; SQL1024N A database connection does not exist
    db2 -v terminate; The TERMINATE command completed successfully
    db2 -v backup database Test to "/home/sysadmin/db2backup" compress include logs without prompting; SQL1035N The database is currently in use
    db2 -v UNQUIESCE DATABASE; SQL1024N A database connection does not exist
    db2 -v CONNECT TO Test;

    I tried to run each one of the commands individually from a CLP and got the same error. I'm going to try as a sql script but I have a feeling that it will yield the same error. Any assistance is greatly appreciated.

  6. #6
    Join Date
    Oct 2004
    Posts
    268
    You should take Nick's advice. "QUIESCE requires a database connection"

    Here are the missing pieces.


    db2 -v connect to Test;
    db2 -v QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    db2 -v connect reset;
    db2 -v terminate;
    db2 -v backup database Test to "/home/sysadmin/db2backup" compress include logs without prompting;
    db2 -v connect to Test;
    db2 -v UNQUIESCE DATABASE;
    db2 -v CONNECT RESET;

    If you are connecting to database with userid and password then you should do

    db2 -v connect to Test user <USERNAME> using <PASSWORD>;


    Hope this helps.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I sometimes add "sleep xx" prior to backup.... to give FORCE enough time to terminate all connections.

  8. #8
    Join Date
    Mar 2011
    Posts
    16
    Thanks for the continued support on this. Going to give it a try and will post back the results.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are backing up offline (the ONLINE keyword is not present in the BACKUP command), then you do not need the INCLUDE LOGS clause. You only need that with ONLINE backups.

    Andy

  10. #10
    Join Date
    Mar 2011
    Posts
    16
    The backups will be done offline (after hours) so the logs won't be neccessary. Ulimately what I need to do is do a backup of the db then import into another db2 instance located in the cloud. So after the backup situation is taken care of I'll need to find out how to import into another db2 instance and automate all of that to be done on a weekly basis. I'm anticipating once the backpup is complete, running rsync to push to the other system and do an import. I'm hoping that it's not too difficult to facilitate this process.

  11. #11
    Join Date
    Mar 2011
    Posts
    16
    Following the advice given.....I added to connect string to the script. Every thing completes successfully up until the backup command:


    db2 -v backup database test to "/home/sysadmin/db2backup" compress exclude logs without prompting;


    Which yields the error: SQL1035N The database is currently in use SQLSTATE=08003

    So it's now connecting at the beginning and successfully quiesce'ing the db, executes the connect reset command and also the terminate command however when it tries to backup, the error that gets returned seems like something else is locking the db. Don't know if this accounts for anything but this db is being used with a RoR app using Apache and Phusion Passenger.
    Last edited by Shftz; 06-22-11 at 16:58. Reason: Left out part of the shell command

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Did you ACTIVATE the database by any chance?

    Andy

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you allow your application(s) to connect using an ID with QUIESCE CONNECT authority (which you should not), they will be able to connect after the QUIESCE, thus keeping the database active.

  14. #14
    Join Date
    Mar 2011
    Posts
    16
    Quote Originally Posted by ARWinner View Post
    Did you ACTIVATE the database by any chance?

    Andy
    I did not.....at what stage in the script is this command envoked?

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Some locations issue an "ACTIVATE DATABASE" command to keep a database active even if all connections are terminated. If it is activated, you need to issue DEACTIVATE DATABASE to do an offline backup. This greatly reduces the performance when the first user connects to the database. It can be issued at anytime, usually when the instance is started. If your site does not use this, then either the force did not finish completely (it is asynchronous) or someone as n_i states someone with quiesce connect rights connected after the force was issued.

    Andy

Posting Permissions

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