The DB2 Check Backup utility is invoked by executing the db2ckbkp command. The syntax for this command is:

db2ckbkp
[ <-a>
  <-c>
  <-d>
  <-e>
  <-h>
  <-l>
  <-n>
  <-o>
  <-p>
  <-s>
  <-t>
  <-cl [DecompressLib]>
  <-co [DecompressOpts]> ,... ]
[FileName,...]

or

db2ckbkp –H [FileName,...]

or

db2ckbkp –S [FileName,...]

or

db2ckbkp –T [FileName,...]

where:

All other options shown with this command are described in Table F.1.

Thus, if you wanted to display only the media header information for an existing backup image named TEST.0.DB2.NODE0000.CATN0000.20081215130904.001, you could do so by executing a db2ckbkp command that looks something like this:

db2ckbkp –H TEST.0.DB2.NODE0000.CATN0000.20081215130904.001

(The –h option tells the tool to display header information along with other information; the –H options tells the tool to display ONLY header information.)

RESTORE DATABASE payroll
FROM /backups
INTO payroll2

If the PAYROLL database was created using automatic storage, the restore operation will maintain the storage path definitions used. So simply restoring the database will not alter the storage paths used; if you want to change the storage path definitions, you must do so explicitly when restoring the database.

(The RECOVER DATABASE command cannot be used to create an entirely new database.)

• Execution of Data Definition Language (DDL) statements (CREATE, ALTER, DROP)

• Execution of Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE)

• Buffer pool operations

• Table space operations (as well as storage-related operations performed on automatic storage databases)

• Online reorganization

• Offline reorganization

• Changes to metadata (system catalog information) for stored procedures and user-defined functions (UDFs)

HADR does not replicate stored procedure and UDF object and library files. If this type of replication is needed, you must physically create the files on identical paths on both the primary and standby databases. (If the standby database cannot find the referenced object or library file, the stored procedure or UDF invocation will fail on the standby database.)

Additionally, non-logged operations, such as changes to database configuration parameters and to the recovery history file, are not replicated to the standby database.

db2pd –applications –db payroll

You could obtain information about connected applications using the DB2 snapshot monitor, but the query used to do so would look like this:

SELECT * FROM TABLE(SNAP_GET_APPL('payroll',-1)) AS applications

It is important to note that if the DB2 server is not responding and appears to be hung, the ONLY way to obtain a list of applications that are currently connected to the database is by using the db2pd utility.

Table space SYSCATSPACE is a resizable DMS table space, however, it has NOT been resized yet; table space IBMDB2SAMPLEREL is an autoresizing DMS table space; and the state of table space USERSPACE1 is “Normal” – not “Roll-forward pending”.

Once the source code file for a user exit program has been compiled and linked to produce an executable program, two actions must be performed before it can actually be used: the executable form of the user exit program must be copied to a specific location and the logarcmeth1 or logarcmeth2 database configuration parameter for the appropriate database(s) must be assigned the value USEREXIT.

It is important to note that IBM is recommending users move away from user exit programs. An easier method for moving log files from the active log directory to an archive log storage location is to assign the value DISK:, followed by the desired location path to the logarchmeth1 database configuration parameter.

db2ckbkp –h TEST.0.DB2.NODE0000.CATN0000.20081215131853.001

The results produced should look something like this:

=====================
MEDIA HEADER REACHED:
=====================
        Server Database Name           -- TEST
        Server Database Alias          -- TEST
        Client Database Alias          -- TEST
        Timestamp                      -- 20081215131853
        Database Partition Number      -- 0
        Instance                       -- DB2
        Sequence Number                -- 1
        Release ID                     -- B00
        Database Seed                  -- 4773F9E6
        DB Comment's Codepage (Volume) -- 2020
        DB Comment (Volume)            --
        DB Comment's Codepage (System) -- 2020
        DB Comment (System)            --
        Authentication Value           -- 32
        Backup Mode                    -- 0
        Includes Logs                  -- 0
        Compression                    -- 1
        Backup Type                    -- 0
        Backup Gran.                   -- 0
        Status Flags                   -- 1
        System Cats inc                -- 1
        Catalog Partition Number       -- 0
        DB Codeset                     -- IBM-1252
        DB Territory                   --
        LogID                          -- 1197742728
        LogPath                        -- C:\LOGS\NODE0000\
        Backup Buffer Size             -- 16777216
        Number of Sessions             -- 1
        Platform                       -- 5

The proper image file name would be:
     TEST.0.DB2.NODE0000.CATN0000.20081215131853.001

[1] Buffers processed:  ####

Image Verification Complete - successful.

					  

(In this case, because the “Include Logs” attribute is set to 0, the INCLUDE LOGS option of the BACKUP DATABASE command was NOT specified when the backup image was created.)

The LIST HISTORY command will tell you what type of backup image was created (F = Offline, N = Online, I = Incremental offline, O = Incremental online, D = Delta offline, E = Delta online, R = Rebuild), when the backup image was created, where the backup image was stored, and the earliest log and current log files used. However, it will not tell you if the log files were included in the backup image.

The steps used to perform a redirected restore operation are as follows:

• SYNC (Synchronous) This mode provides the greatest protection against transaction loss, and using it results in the longest transaction response time among the three modes. In this mode, log writes are considered successful only when log records have been written to log files on the primary database and when the primary database has received acknowledgement from the standby database that the log records have also been written to log files on the standby database. The log data is guaranteed to be stored at both sites.

• NEARSYNC (Near Synchronous) Although this mode has a shorter transaction response time than synchronous mode, it also provides slightly less protection against transaction loss. In this mode, log writes are considered successful only when log records have been written to the log files on the primary database and when the primary database has received acknowledgement from the standby system that the log records have also been written to main memory on the standby system. Loss of data occurs only if both sites fail simultaneously and if the target site has not transferred all of the log data that it has received to nonvolatile storage.

• ASYNC (Asynchronous) This mode has the highest chance of transaction loss if the primary system fails. It also has the shortest transaction response time among the three modes. In this mode, log writes are considered successful only when log records have been written to the log files on the primary database and have been delivered to the TCP layer of the primary system’s host machine. Because the primary system does not wait for acknowledgement from the standby system, transactions might be considered committed when they are still on their way to the standby system.

• Current size

• Maximum size (hard limit)

• Largest size (high water mark)

• Type (identifier indicating function for which memory will be used)

• Agent who allocated pool (only if the pool is private)

(This information is also available from the snapshot monitor.)

The DB2 memory tracker is invoked by executing the db2mtrk command. The syntax for this command is:

db2mtrk
<-i>
<-d>
<-p
<-m | -w>
<-r [Interval] <Count>>
<-v>
<-h>

where:

All other options shown with this command are described in Table F.2.

Thus, if you wanted to see how memory is utilized by the active databases on a system, and you wanted to capture and view this information ten times, updating it every five minutes, you could do so by executing a db2mtrk command that looks something like this:

db2mtrk –d –r 300 10

(The –d option tells db2mtrk to collect and display information for all databases; the –r 300 10 option tells db2mtrk to collect the information every 300 seconds – which is 5 minutes – a total of 10 times.)

The num_log_span configuration parameter is used to set a limit on the number of log files any single running transaction can span. When a transaction is started, a record is written to the current active log file and as DB2 processes the transaction (as well as other transactions) log files are filled and closed. If the number of log files filled exceeds the limit established before the transaction is committed or rolled back, then by default the transaction is rolled back and the application the transaction was running under will be terminated. This behavior can also be overridden by setting the DB2_FORCE_APP_ON_MAX_ LOG registry variable to FALSE.

BACKUP [DATABASE | DB] [DatabaseAlias]
<USER [UserName] <USING [Password]>>
<TABLESPACE ([TS_Name],...)
<ONLINE>
<INCREMENTAL <DELTA>>
<TO [Location] |
    USE TSM <OPTIONS [TSMOptions]>
    <OPEN [NumSessions] SESSIONS>>
<WITH [NumBuffers] BUFFERS>
<BUFFER [BufferSize]>
<PARALLELISM [ParallelNum]>
<UTIL_IMPACT_PRIORITY [Priority]>
<EXCLUDE LOGS | INCLUDE LOGS>
<WITHOUT PROMPTING>

where:

Thus, if you wanted to backup a database named ACCOUNTING to a TSM server, using four concurrent TSM client sessions and eight buffers, you could do so by executing a BACKUP DATABASE command that looks something like this:

BACKUP DATABASE accounting
USER db2admin USING ibmdb2
USE TSM OPEN 4 SESSIONS WITH 8 BUFFERS

db2_all "db2 BACKUP DATABASE sample ONLINE INCREMENTAL"

In this example, the BACKUP DATABASE command was executed on every partition. However, there may be times when you only want to perform a backup or recovery operation on one or more specific database partitions. Because of this, the db2_all command has two prefix sequences that can be used to limit the execution of specified operations to individual database partition servers. These prefix sequences are:

• <<-nnn<

  The operation is to be performed on all database partition servers identified in the db2nodes.cfg file except the database partition server whose node number is nnn.

• <<+nnn<

  The operation is only to be performed on the database partition server in the db2nodes.cfg file whose database partition number is nnn.

When using the <<-nnn< and <<+nnn< prefix sequences, nnn can be any 1-, 2-, or 3-digit database partition number; however, the number specified must have a matching nodenum value in the db2nodes.cfg file.

Thus, if you wanted to perform an online table space-level backup of the table space USERSPACE1 (which is the default table space used to hold user tables) for a database named SALES that resides on partition number 5, you could do so by executing a db2_all command that looks something like this:

db2_all "<<+5< db2 BACKUP DATABASE sample
   TABLESPACE (userspace1) ONLINE TO /backup_dir".

If the INCREMENTAL AUTO or the INCREMENTAL AUTOMATIC option is specified, an automatic cumulative restore operation will be initiated and no user intervention will be required to apply incremental and delta restores.

RECOVER [DATABASE | DB] [DatabaseAlias]
<TO [PointInTime] <USING [UTC | LOCAL] TIME>>
<ON ALL DBPARTITIONNUMS>
<USER [UserName] <USING [Password]>>
<USING HISTORY FILE ([HistoryFile])>
<OVERFLOW LOG PATH ([LogDirectory] ,...)>
<RESTART>

or

RECOVER [DATABASE | DB] [DatabaseAlias]
<TO END OF LOGS
    <ON ALL DBPARTITIONNUMS |
     ON DBPARTITIONNUM<S> ([PartitionNum],...)>>
<USER [UserName] <USING [Password]>>
<USING HISTORY FILE ([HistoryFile])>
<OVERFLOW LOG PATH ([LogDirectory] ,...)>
<RESTART>

where:

If the RESTART option is specified, any previous recover operations are ignored; the RESTART option forces the Recover utility to execute a fresh restore operation and then roll the database forward to the point in time specified.

It the Recover utility successfully restores a database, but for some reason fails while attempting to roll it forward, the Recover utility will attempt to continue the previous recover operation without redoing the restore phase. If you want to force the Recover utility to redo the restore phase, you need to execute the RECOVER DATABASE command with the RESTART option specified. There is no way to explicitly restart a recovery operation from a point of failure.

The value assigned to the hadr_timeout database parameter must be the same for both the primary and the standby database.

IBM recommends that you use identical host computers for the HADR primary and standby databases. (If possible, they should be from the same vendor and have the same architecture.) Furthermore, the operating system on the primary and standby database servers should be the same version, including patch level. You can violate this rule for a short time during a rolling upgrade, but use extreme caution when doing so. A TCP/IP interface must also be available between the HADR host machines, and a high-speed, high-capacity network should be used to connect the two.

The DB2 software installed on both the primary and the standby database server must have the same bit size (32 or 64) and the version of DB2 used for the primary and standby databases must be identical; for example, both must be either version 8 or version 9. During rolling upgrades, the modification level (for example, the fix pack level) of the database system for the standby database can be later than that of the primary database for a short while. However, you should not keep this configuration for an extended period of time. The primary and standby databases will not connect to each other if the modification level of the database system for the primary database is later than that of the standby database. Therefore, fix packs must always be applied to the standby database system first.

The amount of storage space allocated for transaction log files should also be the same on both the primary and the standby database server; the use of raw devices for transaction logging is not supported. (Archival logging is only performed by the current primary database.) Table space properties such as table space name, table space type (DMS, SMS, or Automatic Storage), table space page size, table space size, container path, container size, and container type (raw device, file, or directory) must be identical on the primary and standby databases. When you issue a table space statement such as CREATE TABLESPACE, ALTER TABLESPACE, or DROP TABLESPACE on the primary database, it is replayed on the standby database. Therefore, you must ensure that the table space containers involved such statements exist on both systems before you issue the table space statement on the primary database. (If you create a table space on the primary database and log replay fails on the standby database because the containers are not available, the primary database does not receive an error message stating that the log replay failed.) Automatic storage databases are fully supported, including replication of ALTER DATABASE statements. Similar to table space containers, the storage paths specified must exist on both the primary and the standby server.

The automatic client reroute feature can be used with HADR to make client applications connect to the new primary database immediately after a takeover operation. In fact, if you set up HADR using the Set Up High Availability Disaster Recovery (HADR) Databases Wizard, automatic client reroute is enabled by default. If you set up HADR manually, you can enable the automatic client reroute feature by executing the UPDATE ALTERNATE SERVER FOR DATABASE command; automatic client reroute does not use the values stored in the hadr_remote_host and hadr_remote_svc database configuration parameters.

For example, suppose you have cataloged a database named SALES on a client workstation as being located at host named SVR1. Database SALES is the primary database in an HADR environment and its corresponding standby database, also named SALES, resides on a host named SVR2 and listens on port number 456. To enable automatic client reroute, you simply specify an alternate server for the SALES database stored on host SVR1 by executing the following command:

UPDATE ALTERNATE SERVER FOR DATABASE sales
USING HOSTNAME svr2 PORT 456

Once this command is executed, the client must connect to host SVR1 to obtain the alternate server information. Then, if a communication error occurs between the client and the SALES database at host SVR1, the client will first attempt to reconnect to the SALES database on host SVR1. If this fails, the client will then attempt to establish a connection with the standby SALES database located on host SVR2.

db2mtrk –d

Assuming a database named SAMPLE is active at the time the db2mtrk command is issued, the results produced should look something like this:

Tracking Memory on: 2008/05/21 at 14:00:38

Memory for database: SAMPLE
   utilh     pckcacheh catcacheh bph (1)   bph (S32K) bph (S16K) bph (S8K)
   64.0K     128.0K    64.0K     1.2M      704.0K     448.0K     320.0K

   bph (S4K) shsorth   lockh     dbh       other
   256.0K    0         320.0K    4.3M      128.0K