NetVault Plug-in for DB2 12.0

Configuring default settings Configuring a default login account Enabling Archive Logging for the database Enabling the TRACKMOD parameter Additional notes

Backing up data

Available backup modes Available backup types Defining a backup strategy Performing backups

Selecting data for a backup Setting backup options Finalizing and submitting the job

Restoring data

Restore and recovery overview Restoring from Full Backups

Selecting data for a restore Setting restore options

Setting restore type and other restore options Configuring Destination Details Viewing backup information

Finalizing and submitting the job

Restoring from Incremental Backups Restoring from Delta Backups Advanced restore procedures

Renaming or relocating a database during a restore

Prerequisites (Windows only) Restore procedure

Restoring a database to an alternate server Working with dropped DB2 tables

Setting the Dropped Table Recovery option Recovery method 1: soft recovery Recovery method 2: hard recovery

Troubleshooting

Setting the Dropped Table Recovery option

To recover a dropped table, the Dropped Table Recovery option must be set to “Y” for the tablespace that contained the dropped table. For tablespaces created using the DB2 command prompt, this option is enabled by default. For tablespaces created using the DB2 Control Center graphical user interface (GUI), the Create Tablespace wizard lets you enable this option.

To verify the status of this option and to enable it if it has not been enabled, perform the following steps.

Start a terminal session on the DB2 Server, and navigate to the “…\SQLLIB\BIN” directory, where “…” indicates the installation path of DB2.

To access the DB2 command prompt, type:

DB2

To connect to the local instance of the target database, type:

connect to <localDatabaseName>

To determine the status of Dropped Table Recovery option for the tablespaces in the database, type:

select tbspace, drop_recovery from syscat.tablespaces

If the output displays “N” in the Drop_Recovery column for the target tablespace, type:

alter tablespace <tablespaceName> dropped table recovery on

Recovery method 1: soft recovery

With a soft recovery, the data in other tables in the tablespace is rolled forward to the most current transaction log. This process prevents any current data that exists in other tables from being overwritten when the backup is restored.

To perform a soft recovery of a dropped database table, perform the following steps.

To connect to the local instance of the database, repeat Step 1 through Step 3 described in Setting the Dropped Table Recovery option.

To list the dropped tables, type:

list history dropped table all for db <databaseName>

Note the Backup ID, name of the dropped table, tablespace, and the DDL statement for the table.

DB2=>list history dropped table all for db database_1

List History File for sample

Number of matching file entries = 1

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

D T 20120202114619 000000000000bb000002000d

"ADMINISTRATOR"."EMPLOYEES" resides in 1 tablespace(s):

00001 USERSPACE1

Comment: DROP TABLE

Start Time: 20120202114619

End Time: 20120202114619

00001

DDL: CREATE TABLE "ADMINISTRATOR"."EMPLOYEES" ("NAME" CHAR(20) NOT NULL,

"PROFESSION" VARCHAR(30)) IN "USERSPACE1";

DB2=>

Start the NetVault Backup WebUI on the NetVault Backup Server.

In the Navigation pane, click Create Restore Job, select Plug‑in for DB2 from the Plugin Type list, select a saveset that contains the backup of the target tablespace, and click Next.

Also select the Archived Logs if they were removed from the database directory on the DB2 Server.

For more information, see Selecting data for a restore.

Click Edit Plugin Options, and configure the following parameters on the Options tab:

•

Restore Mode: Select Online.

•

Restore only table space-level backup images: Select this option.

•

Rollforward Archived Logs: Clear this check box.

For more information, see Setting restore type and other restore options.

Complete and submit the job.

For more information, see Finalizing and submitting the job.

To verify that the Roll Forward state for the target tablespace is set to “Pending,” type:

list tablespaces

Verify that the Detailed Explanation column displays “Roll forward pending” status.

DB2=>list tablespaces

Tablespaces for Current Database

Tablespace ID = 0

Name = SYSCATSPACE

Type = System managed space

Contents = Any data

State = 0x0000

Detailed explanation:

Normal

Tablespace ID = 1

Name = USERSPACE1

Type = System managed space

Contents = Any data

State = 0x0080

Detailed explanation:

Roll forward pending

DB2=>

On the DB2 Server, create a directory — recovery directory — to store the recovered table, and the note the complete path.

To perform the roll-forward recovery for the database, connect to the database, and type:

rollforward database <databaseName> to end of logs and complete tablespace

<tablespaceName> recover dropped table <backupID> to

"<completePathToRecoveryDirectory>"

To recreate the table, type:

create table <DDLstatementNotedEarlier>

To import the table data from the recovery directory, type:

import from "<recoveryDirectoryPath>\NODE0000\data" of del messages

<anyFileOutsideTargetDatabase> insert into <tableName>

To verify that the table data has been restored correctly, type:

select * from <tableName>

Repeat Step 7 through Step 12 for each table that you want to restore.

Recovery method 2: hard recovery

In a hard recovery, data in the tables of the target tablespace is rolled forward to the point at which the backup was taken. Changes to the tables within the target tablespace that occurred between the time the backup was taken and the time this recovery is performed, are lost. However, this procedure requires the least number of steps and time to perform.

To perform a hard recovery of a dropped database table, perform the following steps.

To connect to the local instance of the database, repeat Step 1 through Step 3 described in Setting the Dropped Table Recovery option.

To list the dropped tables, type:

list history dropped table all for db <databaseName>

Note the names of tablespaces that contain any dropped tables.

To take the database offline, which is required for a hard recovery, type:

disconnect all

Start the NetVault Backup WebUI on the NetVault Backup Server.

In the Navigation pane, click Create Restore Job, select Plug‑in for DB2 from the Plugin Type list, select a saveset that contains the backup of the target tablespace, and click Next.

Also select the Archived Logs if they were removed from the database directory on the DB2 Server.

For more information, see Selecting data for a restore.

To avoid overwriting the existing data, use the procedure described in Renaming or relocating a database during a restore to restore the data to an alternate location.

Click Edit Plugin Options, and configure the following parameters on the Options tab:

•

Restore all table spaces in the database: Select this option.

•

Rollforward Archived Logs: Select this check box.

•

Hard Recovery: Select this option.

For more information, see Setting restore type and other restore options.

Complete and submit the job.

For more information, see Finalizing and submitting the job.

Connect to the database

To list the available tablespaces, type the following command:

list tablespaces

To verify that the table data has been restored correctly, type:

select * from <tableName>

Troubleshooting

This topic describes some common errors and their solutions. In cases where an error occurs and is not described in this table, obtain the DB2 error number from the NetVault Backup Logs, and then see the relevant DB2 documentation for help.

Table 1. Troubleshooting

Symptom

Error

Explanation or solution

Backup fails to run

The database is in use. Offline backup is not allowed.

No other application or user should connect to the database.

Backup fails to run

Online backup is not allowed.

The “userexit” or “logretain” database configuration parameter is not enabled.

Backup error

Unable to Get Contact Addresses for “host_name” when running a remote backup.

The Plug‑in for DB2 Client cannot obtain a connection with the NetVault Backup Server. Add the IP address and the host information of the NetVault Backup Server to the “…\etc\hosts” file.

Backup Completed with warnings

Check the NetVault Backup Logs for the job to see if they display one or both of the following messages:

•

Failed to add backup record

•

Failed to write index of backup to the database

These messages indicate that the selected data was backed up, but the job’s index information was not properly added to NetVault Database. Without this index information, the data cannot be properly restored.

Method 1:

Open the Manage Devices page, select the backup media, and click Scan. NetVault Backup stores index information for backup jobs in the NetVault Database and the backup media. When you scan the backup media, the index information is added to the NetVault Database. To verify that the information was added, open the Manage Job Definitions page, and locate the specific job. If you can run the job now, the scan process has corrected the problem.

Method 2:

If the scan has failed, run the backup job again.

Cannot browse a database in the selection tree

SQL0332N Character conversion from the source code page “<codePageNumber>” to the target code page “<codePageNumber>” is not supported. SQLSTATE=57017

When you install NetVault Backup, the system is set in locale US, codeset=ISO8859-1, LANG=C. NetVault Backup uses a constant code page 819.

A DB2 database is created using code-set IBM-eucJP Database, code page 954.

Result: The system cannot translate between 819 and 954 as they are not compatible. This issue results in the error message “SQL0332N Character conversion from the source code page “819” to the target code page “UNKNOWN” is not supported. SQLSTATE=57017”

While installing a non-English version of NetVault Backup, verify that your system is running the correct to prevent any problems related to localization.

Restore job fails

DB2 Server error message 'SQL2043N Unable to start a child process or thread

Unable to start the child processes or threads required during the processing of a database task. There may not be enough available memory or system resources to create the process or thread. Close applications that are not being used or increase your system resources. Update your DB2 version to the latest IBM DB2 fix pack available for such version. Ensure that the system limit for number of processes or threads has not been reached by increasing the limit or by reducing the number of processes or threads already running. Ensure that there is enough virtual memory for new process and threads. Resubmit the restore job.

Relocating Tablespaces completes with warnings for automatic storage tablespaces

The SET TABLESPACE CONTAINERS command is not allowed on an automatic storage tablespace.

Containers associated with automatic storage tablespaces are under the control of the database. In DB2 version 9, the CREATE DATABASE command creates automatic storage enabled databases by default. However the user is allowed to create tablespaces with or without automatic storage enabled.