2

What I'm trying to do: find a way to backup a DB2 database via a JDBC command/query. Here's the command that I've tested with the DB2 command-line program:

backup database dbName to C:\backup\db2

It works just fine on the command line, but when attempting to run it in either Data Studio or through a standalone JDBC program, it fails. Here's the JDBC exception message:

SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: backup database dbName to C:/;BEGIN-OF-STATEMENT;<values>

Data Studio complains about "JOIN" being expected instead of "dbName", and a few other things. I have yet to find any helpful documentation about DB2, which is why I came here.

Has anyone run into this before and figured out how to resolve the issue? Or is there something I've missed?

David Young
  • 441
  • 1
  • 4
  • 13
  • Have you een the official DB2 documentaiton? http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0001933.htm – David Dec 28 '12 at 18:17
  • Yes I have, which is how I knew the syntax that works with the DB2 command-line tool. But unless I'm missing something on that page, there's no info that helps me with either Data Studio or JDBC. – David Young Dec 28 '12 at 18:25
  • You probably have to do this in 2 steps. 1) Backup the DB2 database to the same system that the database resides on. 2) FTP the backup to your computer. I don't believe you can backup a DB2 database over JDBC. – Gilbert Le Blanc Dec 28 '12 at 18:43
  • I'm okay with the backup being on the same box as the DB2 instance. Sorry, should've mentioned that up front. I just need to RUN the backup via JDBC. – David Young Dec 28 '12 at 18:46

1 Answers1

1

Try using the admin_cmd procedure:

CALL SYSPROC.ADMIN_CMD('backup db mydatabase to c:\backup\db2\')

This will work also through the JDBC drivers.

user918176
  • 1,770
  • 13
  • 34
  • `CALL SYSPROC.ADMIN_CMD('quiesce db immediate force connections'); CALL SYSPROC.ADMIN_CMD('backup db dbName to c:\backup\db2\'); CALL SYSPROC.ADMIN_CMD('unquiesce db');` gives me "Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect.. SQLCODE=-2413, SQLSTATE= , DRIVER=3.63.108" I've restarted DB2, so there shouldn't be a pending condition, and apparently offline backups are the default. I'm about ready to throw this thing off the roof - haha. – David Young Dec 28 '12 at 19:36
  • First of all, quiesce doesn't stop admin users from using the database. In all likelyhood you are connecting to the database using the default account - which doesn't get blocked by quiesce. If you want to do that you must create a new user, use that for all connections, and then quiesce will work correctly. – user918176 Dec 28 '12 at 21:32
  • Second, even development databases are usually better to have in recoverable mode, unless you batch load/unload large masses of data when developing. (See http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.ha.doc%2Fdoc%2Fc0005962.html ) This is because the database will more surely recover from crashes etc. Simplified, the transaction logs will be saved (setting is LOGARCHMETH1) and the database product will be able to roll back and forth in time all your changes. – user918176 Dec 28 '12 at 21:35
  • Third, the default is offline but you can add the parameter ONLINE to your commands (if the logarchmeth1 is set and you have made the first offline backup) and you will never need the quiesce for backing up. If you want "single recoverable backup file", take a look at the option "include logs". :) – user918176 Dec 28 '12 at 21:37
  • You, sir (or madam) are my hero. Thanks a bunch for taking the time to give me a thorough response that ended up helping me solve this :) – David Young Dec 31 '12 at 18:24
  • Crap. Any chance you know how to do a restore with JDBC? I tried doing it the exact same way, but changing the backup command to restore, and it's saying that "restore" is an "unexpected token". I understand what the error message means, but I don't understand WHY I'm getting it or how to get around it. Any thoughts? – David Young Jan 02 '13 at 17:06
  • If you changed to recoverable databases, use RECOVER DATABASE as in http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0011703.html . Otherwise you really have to yank all the users out of the database and do something like recover db dbName from c:\backup\db2\ taken at thetimestampfromthefilename... Samples at the bottom of page http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0011703.html – user918176 Jan 02 '13 at 17:40
  • I'm getting the same "unexpected token" message with recover. Should I still be wrapping these in CALL SYSPROC.ADMIN_CMD? – David Young Jan 02 '13 at 17:58
  • Why, of course. They are not really SQL, but vendor specific special commands. – user918176 Jan 02 '13 at 18:03
  • Damn. That's what I've been doing. I was hoping I was missing something so this would be easier to solve. I've been able to run a restore and rollforward from within the db2 prompt, but the same commands (even wrapped in ADMIN_CMD) yield the unrecognized token error when run from within DataStudio. – David Young Jan 02 '13 at 18:57
  • Well, I've figured out part of it - ADMIN_CMD doesn't support "restore" which would be why I'm getting the "unrecognized token" message. Now I just have to find another way to do it (if there IS one). – David Young Jan 03 '13 at 17:23
  • Interesting find! In that case you might use the API: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.api.doc%2Fdoc%2Fr0011702.html but for that you need to develop stored procedure by using Java/C/C++. It seems someone at IBM thought you shouldn't be able to do what you are doing... :/ – user918176 Jan 04 '13 at 18:00