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:
- Start db2admin by running the following command: db2admin start
- Start DB2 by running the following command: db2start
- Open a DB2 command line by running the following command: db2cmd
- 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:
- Start db2admin by running the following command: db2admin start
- Start DB2 by running the following command: db2start
- Open a DB2 command line by running the following command: db2cmd
- 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.