Troubleshooting
Problem
Customer has a Cognos Controller system, where the databases are hosted on DB2. How can they perform database backups and restores?
Symptom
.
Cause
IMPORTANT:
The information supplied in this Technote is intended to be a basic guide for 'simple' implementations of Controller with DB2, based on Windows.
- It is not intended to be a comprehensive guide covering all scenarios and environments.
Customers are encouraged to employ an experienced DB2 DBA (database administrator) who should be able to perform backup/restore tasks with ease.
- IBM does not accept responsibility for any problems caused by customers using this Technote.
Environment
Cognos Controller software connecting to databases hosted on DB2 server.
Resolving The Problem
======================================================
How to backup DB2 databases:
There are several possible methods. Below are two examples:
Method #1 - Automatic
Create an automatic backup/optimisation routine (scheduled backups)
- For full details, see separate IBM Technote #1981728.
Method #2 - Manual
Manually backup a database (one-time backup)
- For full details, see separate IBM Technote #1668716
======================================================
======================================================
How to restore DB2 databases:
There are several possible methods and scenarios. Below are three examples:
Example #1 - Overwriting an existing database with a previous backup of the same database
Imagine a scenario where:
- Production database called CCR01
- Customer had made a mistake in CCR01, and wanted to restore their production database to the state that it was in an earlier backup file (for example a backup taken the night before)
Steps:
1. Double-check with the super-user, to make sure that you are 100% sure which database is going to be replaced from an old backup file (for example CCR01)
2. Make no Controller users are using that database (CCR01)
3. Logon to the customer's DB2 server
- Make sure you logon using a Windows user which is a DB2 administrator (for example 'db2admin')
4. Launch the appropriate DB2 tool for running DB2 scripts.
- In this example, I shall launch "DB2 Command Window - Administrator":
- Then type in: db2
5. Browse to the folder where your backup file resides (for example C:\UTILS)
- Check that the a file (for example "CCR01.0.DB2.DBPART000.20170905111234.001") exists, and make a note of its filename.
6. Overwrite database CCR01 (with a copy of that backup file) using these commands:
CONNECT TO CCR01
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
CONNECT RESET
DEACTIVATE DATABASE CCR01
RESTORE DATABASE CCR01 FROM "C:\UTILS" TAKEN AT 20170905111234 INTO CCR01 WITHOUT ROLLING FORWARD WITHOUT PROMPTING
CONNECT TO CCR01
UNQUIESCE DATABASE
CONNECT RESET
NOTE:
- Change "CCR01" to a different source/target database (if necessary)
- Change the date '20170905111234' to whatever the datestamp is on the filename (see step 5 above).
8. Perform a simple connection test, by running the following command:
connect to ccr01
[Make sure that there are no errors].
Example #2 - Overwriting an existing database (on the same DB2 server) with a previous backup of the different database
Imagine a scenario where:
- 'Production' database called CCR01
- 'Test' database called CCR02
- Customer would like to overwrite their current 'test' database (CCR02) with a copy of their production system (CCR01)
Steps:
1. Double-check with the super-user, to make sure that you are 100% sure which database is going to be the source (for example CCR01) and which is the target (CCR02)
2. Obtain some agreed downtime (no users using any database)
3. Logon to the DB2 server
- Make sure you logon using a Windows user which is a DB2 administrator (for example 'db2admin')
4. Launch the appropriate DB2 tool for running DB2 scripts.
- For example "DB2 Command Window - Administrator":
- Then type in: db2
5. Backup database CCR01 to a file, using these commands:
CONNECT TO CCR01
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
CONNECT RESET
DEACTIVATE DATABASE CCR01
BACKUP DATABASE CCR01 TO "C:\UTILS" EXCLUDE LOGS WITHOUT PROMPTING
CONNECT TO CCR01
UNQUIESCE DATABASE
CONNECT RESET
6. Browse to the folder (for example C:\UTILS)
- Check that the a file (for example "CCR01.0.DB2.DBPART000.20170905111234.001") exists, and make a note of its filename.
7. Overwrite database CCR02 (with a copy of that backup file , using this command:
RESTORE DATABASE CCR01 FROM "C:\UTILS" TAKEN AT 20170905111234 INTO CCR02 NEWLOGPATH "E:\DB2LiveLogs\CCR02" WITHOUT ROLLING FORWARD WITHOUT PROMPTING
NOTE:
- Change "CCR01" to a different source database (if necessary)
- Change "CCR02" to a different target database (if necessary)
- Change the date '20170905111234' to whatever the datestamp is on the filename (see step 6 above)
- Change the log file location (for example 'E:\DB2LiveLogs\CCR02') to whatever path you store your log files (on your DB2 server)
8. Perform a simple connection test, by running the following command:
connect to ccr02
[Make sure that there are no errors].
Example #3 - Restoring a database backup to a different DB2 server
Imagine a scenario where:
- The customer had two completely separate environments ('production' and 'test')
- Each environment had its own separate Controller DB2 database server
- Customer wanted to restore a copy of the 'production' database onto the 'test' DB2 database server.
In this scenario, the steps are explained inside separate IBM Technote #1638865
======================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
APPENDICES
Below are some errors/problems that you might find during database restores:
(1) SQL1035N The operation failed because the specified database cannot be connected to in the mode requested. SQLSTATE=57019
You may get this when you perform the 'restore' command. This is because the database is 'in use' by Controller. Even if the users are not using the database, the Controller application server's batch service periodically (approximately every 30 seconds) will contact the database. This typically causes this problem.
The easiest solution is:
- Ask users for complete downtime (no user in ANY database hosted on that DB2 server)
- Run these commands:
db2start
- Retry.
(2) SQL5099N The value "E:\DB2LiveLogs\CCR02\" indicated by the database configuration parameter "NEWLOGPATH" is not valid, reason code "4". SQLSTATE=08004
You may get this when you perform the 'restore' command. This is because the current database's log folder is 'in use'.
The easiest solution is:
- Ask users for complete downtime (no user in ANY database hosted on that DB2 server)
- Run these commands:
db2start
- Quickly (within the next 30 seconds or so) browse to the relevant folder, for example: E:\DB2LiveLogs\CCR02\
- Rename the subfolder 'NODE0000' to: NODE0000.old
- Retry.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Related Information
Historical Number
1019301
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21345399