Use DB2 V9 for non-incremental redirection restoration (3)

Use Automatic Storage Management

Next, let's take an example to see how to use automatic storage management. The specific steps are as follows:

1. Create a database DB2TEST1 that uses automatic storage to modify its database configuration parameters, enable it to use archive logs, and then perform a full backup for the database.

2. Create the regular tablespace TABLESPACE1 using the automatic storage function and the large tablespace TABLESPACE2 without the automatic storage function.

3. perform a full backup for the test database DB2TEST1 so that it can be restored to this status in the future.

4. Create the example table "DB2INST1". "EMPLOYEE ".

5. perform a full backup operation on the Test Database DB2TEST1 so that the database can be restored to this status in the future.

6. Use full backup to restore the test database db2test1.

7. non-incremental targeted restoration test database DB2TEST1.

Create Test Database

CREATE a test DATABASE DB2TEST1 that uses the automatic storage function, open the DB2 command window, and issue the create database statement to CREATE a DATABASE, as shown in Listing 1:

-- List 1. Create an automatic storage DB2 database

Create database DB2TEST1 on/db2/databases/db2test1/auto_storage

Dbpath on/db2/databases/db2test1

In this way, an automatic storage database DB2TEST1 is created, and its database path is/db2/databases/db2test1. The automatic storage path is/db2/databases/db2test1/auto_storage, three tablespaces are created by default: SYSCATSPACE, TEMPSPACE1, and USERSPACE1, all of which are managed using automatic storage. After creating a database and connecting to the database DB2TEST1, we need to change the log management mode of the database to archive, as shown in Listing 2.

-- List 2. Change Database Parameters

Update database configuration using LOGARCHMETH1 LOGRETAIN IMMEDIATE

We recommend that you use LOGARCHMETH1. This parameter specifies the media type of the main target of archived logs. Do not continue to use the LOGRETAIN or USEREXIT parameter. The default value of LOGARCHMETH1 is OFF, which can be LOGRETAIN, USEREXIT, DISK, TSM, or VENDOR. Their meanings are as follows:

L OFF

Specify not to use the log archiving method. If both logarchmeth1 and logarchmeth2 are set to OFF, the database is considered to be using cyclic log records and cannot be restored by roll-forward. This is the default value.

L LOGRETAIN

This value can only be used for logarchmeth1 and is equivalent to setting the logretain configuration parameter to RECOVERY. If this value is specified, the logretain configuration parameters are automatically updated.
L USEREXIT

This value is only valid for logarchmeth1 and is equivalent to setting the userexit configuration parameter to ON. If this value is specified, the userexit configuration parameter is automatically updated.

L DISK

This value must be followed by the colon (:), followed by the existing standard path name, where the log file will be archived. For example, if you set logarchmeth1 to DISK:/u/dbuser/archived_logs, put the archived log file to the directory named/u/dbuser/archived_logs.

Note: If you are archiving a tape, you can use the db2tapemgr utility to store and retrieve log files.

L TSM

If no additional configuration parameters are specified, this value indicates that the default Management class should be used to archive log files on the local TSM server. If this value is followed by the colon (:) and TSM management classes, use the specified management class to archive log files.

L VENDOR

Specifies that the supplier library will be used to archive log files. This value must be followed by the colon (:) and library name. The APIS provided in the database must use the APIs for backup and recovery of vendor products.

Note: If you set logarchmeth1 or logarchmeth2 to a value other than OFF, you must configure the database for rollback and recovery.

If you update the userexit or logretain configuration parameters, logarchmeth1 is automatically updated, and vice versa. However, if you want to use userexit or logretain, you must set logarchmeth2 to OFF.

After the change is successful, run the following command:

FORCE APPLICATIONS ALL

After the command is successful, use list applications to check whether all applications are disconnected. If not, execute force applications all repeatedly to ensure that the database is fully standby after the command is disconnected, as shown in listing 3.

-- List 3. Back up the database

Backup database DB2TEST1 TO/db2/databases/backup/

After the full backup is successful, connect to the database. Now you can create the sample tablespace. In this way, a test database DB2TEST1 is created, which uses archived logs and does not allow Incremental backup.

Create example tablespace

You can create a tablespace by executing commands on the server or locally, or by using a graphical interface in the local Windows environment. To use a graphical interface, we first cataloguing a remote database server, see Figure 4.

-- List 4. cataloguing a remote database

Catalog admin tcpip node 192.168.0.1 REMOTE 192.168.0.1

Catalog tcpip node db2inst1 REMOTE 192.168.0.1 SERVER 50000

Catalog database DB2TEST1 as mydb at node db2inst1

The Database Server whose IP address is 192.168.0.1 and port number is 50000 is catalogued locally as node db2inst1, and the database DB2TEST1 is catalogued locally as MYDB.

Next we will CREATE a TABLESPACE TABLESPACE1 using the automatic storage function, connect to the database DB2TEST1, and then issue the create tablespace Command, as shown in listing 5.
-- List 5. Create a regular tablespace TABLESPACE1 and use automatic storage

Create regular tablespace TABLESPACE1 PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL IBMDEFAULTBP

Note that we follow the managed by operation with automatic storage, which indicates that the newly created tablespace will use automatic storage. To view the tablespace, run the list tablespaces command and enter the following command in the DB2 command line, as shown in Listing 6.

-- List 6. view the tablespace

LIST TABLESPACES SHOW DETAIL

You can see that TABLESPACE1 has been created successfully, and its space management type is database management space, as shown in listing 7.

-- List 7. View table space results

Tablespace id = 3

Name = TABLESPACE1

Type = database management space

Content = all persistent data. Regular tablespace.

Status = 0x0000

Explanation:

Normal

Total pages = 8192

Available pages = 8160

Used pages = 96

Available pages = 8064

High water mark (PAGE) = 96

Page size (in bytes) = 4096

Extended data block size (PAGE) = 32

Prefetch size (PAGE) = 32

Container COUNT = 1

Minimum recovery time =

You can also operate the MYDB database by connecting the remote instance to the database in the local (Windows) control center, right-click the "tablespace" folder, and choose create, open the create tablespace wizard, as shown in 1.

Figure 1: Create a regular tablespace TABLESPACE1 and use automatic storage

Enter the table space name TABLESPACE1 in the name, and select "Let DB2 manage storage (Automatic Storage) (L)". This will create a table space that uses automatic storage. You must note that, when you select "Let DB2 manage storage (Automatic Storage) (L)", the wizard selects "I want to manually manage storage (I )", the space management and container functions are missing because the Automatic Storage of tablespace containers and space management are automatically completed.

Next, we will create a large tablespace TABLESPACE2 that uses non-automatic storage and uses a 32 K page size. Therefore, we need to create a buffer pool with a 32 K page size before creation, as shown in listing 8.

-- List 8. Create a buffer pool

Create bufferpool BUFFERPOOL_32K immediate size 250 automatic pagesize 32 K

Note: The AUTOMATIC Memory self-tuning mechanism is introduced in DB2 V9. By default, the system starts. We add an option "AUTOMATIC" in the buffer pool BUFFERPOOL_32K to enable AUTOMATIC memory Self-Tuning for the buffer pool.

After the command is complete, create a large tablespace TABLESPACE2 with a 32 K page size that is not automatically stored, as shown in listing 9.

-- List 9. Create a large tablespace

Create large tablespace TABLESPACE2 PAGESIZE 32 k managed by database using (FILE/db2/databases/tablespaces/tablespace_32k 640) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BUFFERPOOL_32K

Note: In this case, the DATABASE is followed by managed by, indicating that space management is a DATABASE management (DMS), and containers also need to be defined BY themselves. IT168