Frequently Used Commands > DB2
1. Connecting to a Database
If you constantly work with the database sql, this is perhaps the most used command.
Command: connect to {DBName} user {userId} using {password}
Example: connect to WCS user wcsadmin using password
2. Backup Database:
Command: backup database {DBName} to {PathToFolder}
Example: backup database WCS to C:\DB2Backups
On some servers you will be required to authenticate to the database server before you can back up the database, here is a version of the same command that can use the username and password in the same comamand.
Command: backup database {DBName} user {userId} using {password} to {PathToFolder}
Example: backup database wcs user wcsadmin using password to C:\DB2Backup
The above commands must be used when there are no connections to the database. There is a version of this database command that can be used for backing up database that is currently in use. I did not have too much luck restoring such backups.
This command may be used to restore a db2 database from a backup location.
Command: restore database {DBName} from {PathToBackupFolder} taken at {YYYYMMDDhhmmss} without rolling forward
Note: YYYYMMDDhhmmss is the datetime format as specified.
Example: restore database WCS from C:\DB2Backups taken at 20070215164459 without rolling forward
Restore database command has several options and my favorite one is to use this command for restoring one database into another. This is needed when I want to create one database from another one. To do this first create a new database and then restore the existing database from the back up using the following version(INTO DB Option) of the restore command.
Command: restore database {ExistingDBName} from {PathToBackupFolder} taken at {YYYYMMDDhhmmss} into {NewDBName} without rolling forward
Example: restore database WCS from C:\DB2Backups taken at 20070215164459 into LOCALWCS without rolling forward
There are several tricks to working with database backups and that must be discussed in a separate post.
4. Run SQL
If you have certain sql statements in a file, you can run such files from a command window as follows:
Command: db2 -tvf {PathToThefile.ext}
Example: db2 -tvf C:\DB2SQL\Current.sql
5. Export a Table
Well, I agree this may not be the most frequently used command - but here it is.
Command: EXPORT TO {”PathToExportFile”} OF DEL MODIFIED BY CHARDEL”" TIMESTAMPFORMAT=”{TimeStampFormat}” COLDEL, DECPT. MESSAGES {”PathToMessageFile”} SELECT * FROM USERID.TABLENAME
Example: EXPORT TO “C:\DBFiles\catgroup.csv” OF DEL MODIFIED BY CHARDEL”" TIMESTAMPFORMAT=”YYYY-MM-DD HH:MM:SS TT” COLDEL, DECPT. MESSAGES “C:\temp\expCatGroup.msg” SELECT * FROM WCSADMIN.CATGROUP