Creating the database and database tables for DB2

A database and a set of database tables need to be created in the local or remote database in order to use the large log support feature provided by Log and Trace Analyzer.

Creating the database and tables

To create the database and database tables for DB2(R), follow these steps:

  1. Start db2admin by running the following command: db2admin start
  2. Start DB2 by running the following command: db2start
  3. Open a DB2 command line by running the following command: db2cmd
  4. Run the script by running the following command: db2 -tvf <script> where <script> is the following file:
    X:\eclipse\plugins\com.ibm.etools.ac.resources.database_x_x_x\scripts\CreateDatabaseAndTablesDB2-8.1.sql

    where X: is the drive or directory where Eclipse is installed, and x_x_x is the version number on the plug-in directory.

Once the database is successfully created, you need to set preferences in Log and Trace Analyzer to enable the large log support feature.

Customizing the database creation script

For DB2, it is recommended that you customize the database creation script as required. The highlighted attributes shown below can be modified:

CREATE BUFFERPOOL HYADES_BUFFERPOOL SIZE 10000 PAGESIZE 32768 NOT EXTENDED STORAGE;

CREATE REGULAR TABLESPACE HYADES_TABLESPACE IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY SYSTEM 
  USING ('container-string')
  EXTENTSIZE 16
  PREFETCHSIZE 16
  BUFFERPOOL HYADES_BUFERPOOL
  OVERHEAD 10.500000
  TRANSFERRATE 0.140000;
where
SIZE number-of-pages
The size of the buffer pool specified as the number of pages.
container-string
The container-string can be an absolute or relative directory name. The directory name, if not absolute, is relative to the database directory. For example, on Windows(R), the container string would be C:\DB2\Hyades
EXTENTSIZE number-of-pages
Specifies the number of PAGESIZE pages that will be written to a container before skipping to the next container.
PREFETCHSIZE number-of-pages
Specifies the number of PAGESIZE pages that will be read from the table space when data prefetching is being performed. Prefetching reads in data needed by a query prior to it being referenced by the query, so that the query need not wait for I/O to be performed.
OVERHEAD number-of-milliseconds
Any numeric literal (integer, decimal, or floating point) that specifies the I/O controller overhead and disk seek and latency time, in milliseconds. The number should be an average for all containers that belong to the table space, if not the same for all containers. This value is used to determine the cost of I/O during query optimization.
TRANSFERRATE number-of-milliseconds
Any numeric literal (integer, decimal, or floating point) that specifies the time to read one page into memory, in milliseconds. The number should be an average for all containers that belong to the table space, if not the same for all containers. This value is used to determine the cost of I/O during query optimization.

Dropping the database tables

To drop the database tables for DB2, follow these steps:

  1. Start db2admin by running the following command: db2admin start
  2. Start DB2 by running the following command: db2start
  3. Open a DB2 command line by running the following command: db2cmd
  4. Run the script by running the following command: db2 -tvf <script> where <script> is the following file:
    X:\eclipse\plugins\com.ibm.etools.ac.resources.database_x_x_x\scripts\DropTablesDB2-8.1.sql

    where X: is the drive or directory where Eclipse is installed, and x_x_x is the version number on the plug-in directory.

Related tasks
Setting large log support preferences for DB2

Related references
Guidelines for using large log support

(C) Copyright IBM Corporation 2000, 2006. All Rights Reserved.