Database and Storage Solution for Small and Medium

SOLUTION REPORT
F RO M T H E A DA P T E C S O L U T I O N S T E S T L A B
Database and Storage Solution for Small
and Medium-Sized Businesses
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2
Configuration details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2
1. DB2 Express 8.1 server information . . . . . . . . . . . . . . . .2
2. Local U320 SCSI disk configuration . . . . . . . . . . . . . . . .3
3. Adaptec Snap Server 4500 information . . . . . . . . . . . . .3
4. Switch information . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
5. Database software product information . . . . . . . . . . . . . .3
PART 1: Adaptec Snap Server 4500 configuration . . . . . . . .3
1. Basic configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
2. Creating a RAID storage pool and an iSCSI target . . . . . .3
3. Creating iSCSI target on Snap Server 4500 . . . . . . . . . . .8
PART 2: Server Installation and Setup . . . . . . . . . . . . . . . .11
1. ASA-7211 iSCSI initiator installation . . . . . . . . . . . . . .11
2. ASA-7211 iSCSI initiator configuration . . . . . . . . . . . . .11
3. DB2 Express 8.1 requirements . . . . . . . . . . . . . . . . . . .11
Operating system requirements . . . . . . . . . . . . . . . . . . .11
Hardware requirements . . . . . . . . . . . . . . . . . . . . . . . . .11
Software requirements . . . . . . . . . . . . . . . . . . . . . . . . . .11
Communication requirements . . . . . . . . . . . . . . . . . . . .12
Disk requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
Memory requirements . . . . . . . . . . . . . . . . . . . . . . . . . .12
4. DB2 Express 8.1 installation . . . . . . . . . . . . . . . . . . . . .12
PART 3: DB2 Tests on the Adaptec Snap Server 4500 . . . .16
1. Creating a new database . . . . . . . . . . . . . . . . . . . . . . .16
A. Using a GUI interface . . . . . . . . . . . . . . . . . . . . . . . .16
B. Using a SQL script . . . . . . . . . . . . . . . . . . . . . . . . . .18
2. Data population/insertion . . . . . . . . . . . . . . . . . . . . . . .18
3. Backup the database to filesystem device . . . . . . . . . .18
A. Using a GUI interface . . . . . . . . . . . . . . . . . . . . . . . .18
B. Using a SQL script . . . . . . . . . . . . . . . . . . . . . . . . . .20
4. Restore the database . . . . . . . . . . . . . . . . . . . . . . . . . .21
A. Using a GUI interface . . . . . . . . . . . . . . . . . . . . . . . .21
B. Using a SQL script . . . . . . . . . . . . . . . . . . . . . . . . . .23
5. LOB (Large Object Binary) I/O test . . . . . . . . . . . . . . . .23
6. TPC-H test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23
A. TPC-H pre requirement . . . . . . . . . . . . . . . . . . . . . . .23
B. TPC-H configuration . . . . . . . . . . . . . . . . . . . . . . . . .23
C. Running TPC-H . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
Appendix B: Sample configuration file(s) . . . . . . . . . . . . . .26
Instructions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
Introduction: The Database Storage
Challenge
Many small and medium-sized businesses are looking
to leverage advanced database technology to power a
range of e-business and on-demand business applications. Unfortunately, while the range of such applications is growing constantly, IT budgets and rack space
are not. To deploy the customer-centric business applications they need to compete effectively, small and
medium-sized organizations need powerful database
solutions they can afford—and flexible, cost-effective
database storage solutions that keep pace with business growth and evolution.
2
A Proven Database Storage Solution
This Solution Report describes a database storage configuration combining the Adaptec Snap Server 4500
Storage Array with IBM’s DB2 Universal Database
Express Edition Version 8.1. The tested solution benefits from the working relationship between Adaptec
and IBM, ensuring continued innovation and proven
performance for database and other crucial business
applications.
Test Configuration
The Solution: Adaptec Snap Server 4500
Storage Array
The Adaptec Snap Server 4500 is a cost-effective
shared iSCSI storage server that offers small businesses
or remote office locations high throughput, best-inclass storage density, simple deployment, and exceptional price/performance.
Used with industry-leading database solutions—such
as IBM’s cost-effective DB2 Express—the Adaptec
Snap Server 4500 Storage Array offers small and medium-sized businesses a range of benefits:
• Exceptional price/performance, putting high-performance database storage capabilities within reach
of small and medium-sized businesses
•
GigE SWITCH
Ease of deployment and management, with easy,
appliance-like installation, configuration and management, and a compact 1U footprint
• High density, with available 1.6TB configuration in
just 1U, plus the ability to add two Snap Disk 10
Expansion Arrays to achieve a total capacity of
3.6TB.
• Scalability—Instant Capacity Expansion (I.C.E.)
feature lets you dynamically increase volumes and
use the expanded capacity immediately, without
disrupting user access to the volume
• Enhanced data protection, with a complete data
protection suite including enhanced snapshot and
synchronous volume mirroring
The combination of the Adaptec Snap Server 4500
Storage Array and IBM’s DB2 Express provides an
attractive, highly cost-effective solution for the moderate transaction database workloads commonly found
in small and medium-sized businesses. Such a configuration ideally meets the needs of these businesses
today, providing the flexibility to grow and evolve to
meet future needs, while protecting their database
storage investment.
ASA-7211
iSCSI ADAPTER
DB2 EXPRESS SERVER 8.1
Configuration Details
1. DB2 Express 8.1 Server information
Model: IBM eServer xSeries 335
Processor: Intel Xeon 3.2 GHz x 2
Memory: 4GB
OS, Version and SP: Windows 2000 Advance Server w/SP4
Number of iSCSI initiator: 1
- Vendor and Model: Adaptec ASA-7211
- IP address and subnet mask of ASA-7211:
xxx.xxx.xxx.xxx
- Firmware Version: 1.20.44
- Driver Name and Version: asa72xx.sys; 1.20.0.39
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
2. Local U320 SCSI disk configuration
Local, direct attach disk configurations were used as a
reference comparison. TPC-H benchmarks were run
on both Snap Server 4500 and Local disk configurations. The local disk configuration comprised:
- Drives: 2 - 146GB U320 SCSI 10K RPM IBM Drives
- SCSI Adaptor: LSI 1020/1030 Ultra-SCSI adaptor
- RAID level: No RAID
3. Adaptec Snap Server 4500 information
Brand and Model: Adaptec Snap Server 4500
Boot Image Version: 3.2.019
Disk Drive Vendor and Model: WDC-WD2500BB-50
Disk Drive Capacity: 250GB
Number of Disk Drives: 4
Number of iSCSI Data ports: 2 (Interface 1 & Interface 2)
Management Ethernet port information:
- DHCP: No
- IP Address: xxx.xxx.xxx.xxx
- Subnet Mask: 255.255.255.0
- Router: xxx.xxx.xxx.xxx
ISCSI target port 1:
- IP Address: xxx.xxx.xxx.xxx
- Subnet Mask: 255.255.255.0
ISCSI target port 2:
- Not used
3
5. Database software product information
Product Tested: IBM DB2 Express v8.1 with Fix pack5
Part 1: Adaptec Snap Server 4500 configuration
1. Basic configuration
Configure the Adaptec Snap Server 4500 using its web
browser-based management interface.
When the Adaptec Snap Server 4500 is on the network, the Initialization wizard opens. Use the Initialization wizard to set all basic parameters such as IP
address, Password, etc.
After completing the basic parameters setup, you can
use the management browser at any time to manage
the Snap Server 4500.
2. Creating a RAID storage pool and an iSCSI target
This procedure requires that name resolution services
(via WINS or an equivalent service) be operational.
1. Find the server name. The default server name is
“Snapnnnnnn” where “nnnnnn” is the server number.
For example, the name of a Snap Server with a server
number of 405442 is Snap405442. The server number
is a unique, numeric string that appears on a label
affixed to the inside of your Snap Server. To view the
label, remove the front bezel.
Management Software: Adaptec Storage Manager
Version: v1.1 build 32
Storage Pool:
- One RAID 0+1 consisting of all 4 hard drives, it has
pool size 467 GB.
Configured iSCSI Target:
- TARGET1 – 200GB of the RAID 0+1 (used by DB2
Express 8.1 Server)
(Note: The drive E: is iSCSI target drive on the server
running DB2).
4. Switch information
Ethernet Switch Vendor: Allied Telesyn
Ethernet speed: Gigabit
Model: AT-9410GB
Firmware version: 1.01
Management Ethernet port info : xxx.xxx.xxx.xxx
- Subnet Mask: 255.255.255.0
- Router: xxx.xxx.xxx.xxx
- DHCP Mode: Disabled
Login into the Snap Server 4500 unit under test and
create a RAID 0, RAID 1 or Raid 5 storage pool with
the available three or four physical drives. Then create
an iSCSI target with 200GB from this pool for the
DB2 information store. Make sure the iSCSI target has
unrestricted access control.
Please refer to the Snap Server 4500 User’s Guide if
you need more information.
2. To access the Snap Server management interface,
open a web browser and enter the following URL:
http://Snapnnnnnn or IP address (Snapnnnnnn server
number can also be used).
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
3. Press Enter. The Web View screen opens.
4. Log into the Administration browser and complete the Initial Setup Wizard.
Click the administration link and, in the login dialog box, enter “admin” as the user name and “admin” as the password, and then click OK. Complete the setup procedure using the Initial Setup wizard, if necessary
4
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
5. Click “Storage” option.
6. Click “RAID Sets” option to create RAID.
7. Click “Create RAID Set.”
5
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
6
8. Select desired RAID Set (Available RAID sets = RAID-0, RAID-1 and RAID-5). Note: RAID 0 was chosen in this
scenario; other available RAID configurations, such as RAID 5, could be just as easily chosen.
Click “Continue.”
9. Select the device(s) you would like to use for RAID Sets. Click “Continue.”
10. Click “Continue” to create the RAID Set.
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
11. After the RAID Set creation, you must create the Volume. Click “Create Volume.”
12. Select the Volume Name, RAID Set and Capacity.
13. Click “Continue” to create volume.
7
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
14. Click “Exit” to ignore the volume sharing. Note: you can go back to create the share anytime. Shares are not
required for iSCSI disks.
3. Creating iSCSI Target on Snap Server 4500
1. Return to the Snap Server main menu screen. Click “Storage” option.
2. Click “iSCSI” option.
8
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
9
3. Click “iSCSI” menu to create iSCSI targets.
4. Click “Create iSCSI Disk.”
5. Enter a name and desired capacity for the iSCSI disk. In this test case, 200GBs was used for the DB2 Database.
Click “Continue” to create an iSCSI disk.
Note: All created iSCSI disks are accessible by ALL initiators unless a CHAP authentication token is entered. If a CHAP token
is entered on the target, the same CHAP token must be entered by the initiator in order for it to connect to the Snap Server
iSCSI target. If you are not concerned with what initiator connects to the Snap Server, CHAP authentication is not required.
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
6. Click “Continue” to create iSCSI Disk.
7. Click “Exit” to complete iSCSI Disk creation.
The iSCSI disk has been created. You can now connect it to the Host initiator for use with DB2.
10
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
11
Part 2: Server Installation and Setup
1. ASA-7211 iSCSI initiator installation
1. Install ASA-7211 adapter according to the instructions from the “ASA-7211 Adapter Installation Guide.”
2. Start Windows 2000 and log in administrative privileges. The Windows “Found New Hardware Wizard” automatically detects the new hardware.
3. The adapter is identified as a Mass Storage Controller. Follow the Windows instruction to install the drivers for
it. After installation, it will be listed under the SCSI and RAID Controllers device group in the Device Manager.
2. ASA-7211 iSCSI initiator configuration
Using the Adaptec iSCSI Configuration Manager
1. To start the Adaptec iSCSI Configuration Manager, open the Control Panel and select Adaptec iSCSI Configuration Manager.
2. When starting the iSCSI Configuration Manager for the first time, the Express Setup wizard starts automatically,
and assists you in configuring the initiator and discovering targets. Before you begin, have the following network
information available:
- IP address, subnet mask and gateway for the ASA-7211
- iSCSI name for ASA-7211 (if different from the default)
- Snap Server Target IP address
3. Start the initiator software on the host and attach to the Snap Server target.
Please refer to the ASA-7211 User’s Guide if you need more information.
3. DB2 Express 8.1 requirements
To install DB2 Express, the following operating system, software and communications requirements must be met:
Operating system requirements:
•
•
•
•
Windows NT Version 4 with Service Pack 6a or higher
Windows 2000 With SP4
Windows XP
Windows Server 2003
Hardware requirements:
• An Intel x86 or compatible processor.
Software requirements:
• The IBM Developer Kit for Java 1.3.1 Service Release 2 is required for DB2 servers, for using the DB2 Control
Center, and for creating and running Java applications, including stored procedures and user-defined functions.
During the installation process, if the correct level of the JDK is not already installed, it will be installed.
• A browser is required to view online help.
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
12
Communication requirements:
You can use TCP/IP, Named Pipes, and NetBIOS. To remotely administer a DB2 Version 8 database, you must connect using TCP/IP.
• For TCP/IP, Named Pipes, and NetBIOS connectivity, no additional software is required.
• If you plan to use LDAP (Lightweight Directory Access Protocol), you require either a Microsoft LDAP client or
an IBM SecureWay LDAP client V3.1.1.
Disk requirements:
• 800MB (full installation) or 200MB (minimum installation).
Memory requirements:
Minimum 256 MB of RAM. Additional memory may be required depending on the following factors:
• Additional memory may be required for non-DB2 software that may be running on your system.
• Additional memory is required to support database clients.
• Specific performance requirements may determine the amount of memory needed.
• Memory requirements will be affected by the size and complexity of your database system.
• Memory requirements will be affected by the extent of database activity and the number of clients accessing
your system.
4. DB2 Express 8.1 installation
1. Click “setup.exe” from DB2 Express folder and select “Install Product.”
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
2. Click “Next.”
3. Click “Next” from DB2 Setup wizard.
13
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
4. Select the installation type.
5. Enter the DB2 user information in the window shown.
14
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
6. Click “install.”
7. Click “Finish.”
15
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
16
Part 3: DB2 Tests on the Adaptec Snap Server 4500
1. Creating a new database
A. Using a GUI interface
1. Open “Control Center” from the Windows “Start” menu а “Programs” а “IBM DB2” а “General Administration
Tools “ to create a new database. Right click on “Databases” and select “Create” drop down menu.
Select “Database Using Wizard” as shown below:
2. Type database name and select target drive where you wish to locate new database. Click “Next.”
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
3. Specify how and where to store the system catalog tables.
4. Click “Finish” on “Summary” screen to create database.
17
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
18
B. Creating new database using a SQL script
To create a new database, run the following SQL command(s):
CREATE DATABASE TPCD ON 'E:' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM;
RESULT: DB2 successfully created the new database on Snap Server 4500 database volume. No error occurred.
2. Data population/insertion
Test the created database by populating it with data. This could be via SQL commands or a customized script. For
the test configuration, we used an in-house C# program to insert BLOB data from a specified directory.
RESULT: DB2 populated the BLOB data(s) on to the database successfully. No error occurred.
3. Backup the database to filesystem device
A. Using a GUI interface
1. The IBM DB2 Express provided the “Backup” and “Restore” options. Open the “Control center” and right click
the database you wish to backup from database list. Select “Backup.” Click “Next” on following screen.
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
19
2. Select “File System” from “Media Type.” Browse destination folder to backup the database. Click “Add” to select
path.
3. Select the path on following screen click “OK.”
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
4. Select “Run now without saving task history” and click “Finish.”
B. Using a SQL script
Use the following SQL command(s) to run database backup:
CONNECT TO TPCD;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
BACKUP DATABASE TPCD TO "X:\Backup\" WITH 1 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO TPCD;
UNQUIESCE DATABASE;
CONNECT RESET
RESULT: DB2 Backup Test was performed successfully with 1GB data. No errors occurred.
20
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
4. Restore the database
A. Using a GUI interface
IBM DB2 Express 8.1 provides the following restore options:
- Restore to an existing database
- Restore to new database
1. Open the “Control center” and select Restore from “selected” from menu.
2. Select “Restore to an existing database” and Click “Next.”
21
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
22
3. Select the database backup image(s) to use.
4. Select the performance option for Restore. Select “Run now without saving task history” from “Restore Wizard”
window as shown.
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
23
B. Restoring the database using a SQL script
Use the following SQL command(s) to run database restore:
RESTORE DATABASE TPCD FROM "X:\Backup" TAKEN AT 20040720165202 TO "E:" INTO TEST NEWLOGPATH "C:\DB2_LOG\"
WITH 1 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
RESULT: DB2 Restore Test was performed successfully with 1GB data. No errors occurred.
5. LOB (Large Object Binary) I/O test
LOBIOTEST- In-house C# test tool used to populate a DB2 table space by inserting files as binary data. Additionally,
the program performs file comparisons with the source directory.
RESULT: Test was performed for 24 hours using 1GB data from a source directory. No file comparison errors
occurred.
6. TPC-H test
The TPC Benchmark™H (TPC-H) is a decision support benchmark consisting of a suite of business-oriented adhoc queries and concurrent data modifications. The queries and the data populating the database are chosen to
have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.
The performance metric reported by TPC-H is called the TPC-H Composite Query-per-Hour Performance Metric
(QphH@Size), and reflects multiple aspects of the capability of the system to process queries, including:
• The selected database size against which the queries are executed.
• The query processing power when queries are submitted by a single stream.
• The query throughput when queries are submitted by multiple concurrent users.
The TPC-H Price/Performance metric is expressed as $/QphH@Size.
A. TPC-H pre-requirement
1. Install Perl for windows “ActivePerl-5.8.3.809-MSWin32-x86.msi” or download from following link:
http://www.activestate.com/Products/Download/Download.plex?id=ActivePerl
2. Install Visual C++ 6.0
3. Unzip the tpcd.zip to C:\TPCD
Note: Set /PAE Switch in “boot.ini”
The TPC-H benchmark results are based on database tuning and system tuning. If the system memory exceeds
4GB (the standard x86 limit), you must set /PAE switch in “boot.ini.”
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000
Advanced Server/PAE" /fastdetect /PAE
B. TPC-H configuration
1. Edit all TPC settings from “C:\tpcd\tools\tpcd-setup” file.
(See additional information in ‘Appendix’ section).
2. To compile the “dbgen” and “qgen” type the following command line:
cd\tpcd\appendix.v2\dbgen
nmake /b /f makefile.TPCH.nt
3. In the Environment Variables from “System Properties” as shown, add the following:
“User Variables for Administrator”
Path: “C:\tpcd\tools\bin\nt”
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
24
C. Running TPC-H
1. Because all DB2 commands must be run under “DB2CLP” command window, type this command:
C:\>db2cmd
2.
From “DB2CLP” command window, run following commands:
1) cd\tpcd\tools
2) db2stop force
3) db2start
4) perl setupdir
5) perl buildtpcd
6) perl buildtpcdbatch
7) perl setuprun
8) perl runpower
9) perl runthroughput
10) db2stop
// Setup the output directory
// Build the TPCD database
// compile & bind the C program
// Prepare a new TPCD run
// Run the power tests
// Run the throughput tests
3. Results are in directory \TPCD_AUDIT_DIR\auditruns\runxx. Results files should appear as follows:
• mpinterx.metrics
• mtinterx.metrics
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
25
Summary
In testing of typical database operations, the combination of the Adaptec Snap Server 4500 Storage Array and IBM
DB2 Express proved an effective and practical solution. Leveraging the attractive performance and economics of
iSCSI technology, the Adaptec Snap Server 4500 and IBM DB2 Express configuration is a highly cost-effective way
for small and medium-sized businesses to deploy database applications for the customer-centric business strategies
of today…and those of tomorrow.
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
Appendix B: Sample configuration file(s)
The following parameters and associated files need to modify in the ‘tpcd.setup’ file to run TPC-H on DB2 environment correctly. (Note: Use the default setting for all others parameters).
TPCD_PLATFORM=nt
TPCD_DBNAME=TPCD
TPCD_WORKLOAD=H
TPCD_AUDIT_DIR=c:\tpcd
#
#
#
#
#
aix, nt, sun ....
name to create database under
TPC version (R for TPCR, H for TPCH)
top level directory of tar file for
all the tpcd scripts
TPCD_SF=10.00
#
#
#
#
#
#
size of the database (1=1GB,...) to
get test size databases use:
0.012 = 12MB
0.1
= 100MB
ALL - do everything (create,load,
index,stats,config) (Default)
#
#
#
#
#
path for database (defaults to home)
path for all ddl files and customized
scripts (load script), config files,etc
name of file with bufferpool
definitions and sizes
TPCD_TBSP_DDL=create_tablespaces
TPCD_DDL=create_tables
TPCD_QUAL_TBSP_DDL= create_tablespaces
TPCD_QUAL_DDL= create_tables
#
#
#
#
#
#
#
ddl file for tablespaces
ddl file for tables
ddl file for tablespaces for qual
ddl file for qualification database
tablespaces and tables should be
identical to regular ddl
except container names
TPCD_INDEXDDL=create_index.H
# ddl file for indexes
TPCD_ADD_RI=NULL
#
#
#
#
file name that contains any RI
constraints to add after index creation
set to NULL (default) if unused
indices to create after the load.
TPCD_RUNSTATS=dss.runstats
#
#
#
#
#
#
#
#
#
#
ddl file for runstats. If you have
created indices before the load (ie
TPCD_EARLYINDEX=yes), have specified to
gather stats on the load command (either
through your own load script or by using
TPCD_LOADSTATS=yes, AND you have
specified a file for TPCD_EXTRAINDEX
then this runstats file should include
the runstats commands specifically for
the extra indices.
TPCD_DBGEN=c:\tpcd\appendix.v2\dbgen
#
#
#
#
#
#
#
#
#
#
path name to data generation code
Parameters used to specify source of
data for load scripts
NULL - use dbgen generated data OR
path name - to the pre-generated
flat files
TPCD_BUILD_STAGE=ALL
TPCD_DBPATH=E:
TPCD_DDLPATH=c:\tpcd\sstg
TPCD_BUFFERPOOL_DEF=create_bufferpools
TPCD_INPUT=NULL
TPCD_QUAL_INPUT=NULL
TPCD_LOAD_CONFIGFILE=NULL
NULL - use dbgen generated data OR
path name - to the pre-generated
flat files
#config file with specific database config
# parms for the load/index/runstats part
# of the build.
# set to NULL if use defaults
26
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
TPCD_LOAD_DBM_CONFIGFILE=NULL
#
#
#
#
config file with specific
database manager config parts for the
load/index/runstats part of the build.
set to NULL if use defaults
TPCD_LOAD_QUALCONFIGFILE=NULL
#
#
#
#
#
#
#
config file with specific database config
parms for the load/index/runstats part
of the build for qualification db.
set to NULL if use defaults
config file with specific
database manager config parts for the
load/index/runstats part of the build.
TPCD_LOAD_DBM_QUALCONFIGFILE=NULL
TPCD_SORTBUF=4096
# sortbuf size for LOAD
# used in load script only
TPCD_LOGPRIMARY=100
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
NULL/value = how many primary log files
to configure. If NULL is specified then
the default is not changed.
NULL/value = how 4KB pages to use for
logfilsiz db cfg parameter. If NULL is
specified then the default is not changed
NULL/value = how many secondary log files
to configure. If NULL is specified then
the default is not changed.
directory where log files stored..
NULL leaves them in the dbpath
directory where qual log files stored
NULL leaves them in the dbpath
yes/no - whether to turn LOG_RETAIN on
i.e. are backups needed to be taken
CONFIG specific parameters
#
#
#
#
#
#
#
#
#
#
#
Script that contains the db2set commands
for the benchmark run. Use NULL if not
specified
name of configuration file in ddl path
that will be used for the benchmark run
name of config file for database manager
cfg parms
name of database cfg file in ddl path
for qualification database
name of config file for database
manager cfg parms
TPCD_LOGFILSIZ=16384
TPCD_LOGSECOND=10
TPCD_LOG_DIR=NULL
TPCD_LOG_QUAL_DIR=NULL
TPCD_LOG=YES
TPCD_DB2SET_SCRIPT=NULL
TPCD_CONFIGFILE=dbcfg_for_run
TPCD_DBM_CONFIG=dbmcfg_for_run
TPCD_QUALCONFIGFILE=dbcfg_for_run
TPCD_DBM_QUALCONFIG=dbmcfg_for_run
TPCD_TMP_DIR=e:\TPC_TMP
# place to put temp working files
TPCD_QUAL_DBNAME=TPCD
TPCD_NUMSTREAM=6
# name of qualification database
# number of streams for the throughput test
TPCD_UFTEMP=UFTEMP1
#
#
#
#
#
#
base name of tablespace(s) where the
staging tables for the update functions
are created
this name will be used as the
basename for the tablespaces...eg
UFTEMP1 UFTEMP2 ....
TPCD_ROOTPRIV=no
#
#
#
#
do you have root privileges to be able
get values of things like schedtune
and vmtune (currently on AIX only)
acid test specific information
=========================================================================
27
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server™ 4500 Storage Array
create_bufferpools
alter bufferpool ibmdefaultbp size 30000;
create bufferpool BP8K size -1 pagesize 8K;
create bufferpool BP16K size 65536 pagesize 16K;
loadcfg.sql
update database configuration for tpcd using
NUM_FREQVALUES 0
NUM_QUANTILES 300
bufferpage 30000
catalogcache_sz 386
chngpgs_thresh 10
bbheap 10000
locklist 6000
logbufsz 4096
logfilsiz 16384
logprimary 10
logsecond 2
maxappls 8
maxlocks 20
mincommit 1
num_iocleaners 4
num_ioservers 4
pckcachesz 320
softmax 4000
sortheap 20000
stat_heap_sz 16000
stmtheap 4096
util_heap_sz 32000
applheapsz 768
app_ctl_heap_sz 1024
dft_degree 2;
get database configuration for tpcd;
--connect reset;
===================================================
= LOB Copy and Compare Utility (DB2 Build)
=
=
=
= (c) 2004 Adley LeBlanc
=
=
=
===================================================
Files
----LOB.exe
largefiles.db2
IBM.Data.DB2.dll
READEME.txt
- LOB Copy and Compare Utility
- Script to create the 'largefiles' table
which the utility uses.
- IBM .NET Data Provider
- this file
28
SOLUTION REPORT
Using DB2 Express v8.1 with Adaptec Snap Server® 4500 Storage Array
29
Instructions
1. The 'LARGEFILES' table needs to be created in the
DB2 database for the LOB application to run. Use the
largefiles.db2 script and the IBM DB2 Command Center to create the table. (step 2)
2. In the largefiles.db2 script the CREATE TABLE
command currently creates the table in the ME
schema. You need to update the script to create the
table in your own schema. For example:
CREATE TABLE MYSCHEMA.LARGEFILES .....
would create the LARGEFILES table in MYSCHEMA.
Make the update and save the largefiles.db2 script. You
can also change the name of the database in the first
line if you want.
4. Run the LOB utility using the following syntax:
LOB [db_alias] [username/schema name] [password] [src
directory]
db_alias - name of the database instance which the
LARGEFILES table is on.
username - the name of the user to use, recommend
using the schema name so no security problems exist.
password - the password of user
src directory - the directory on the local filesystem
which has the files to upload and compare. The max
file size is 50MB, anything bigger will fail.
Example: LOB DB1 user1 password c:\test
3. Import the script into DB2 Command center and
execute it to build the table.
This Report is intended to provide guidance only. This document outlines configuration examples and does notimply that compatibility is limited to the products or components listed; other vendors and products may also be compatible. Consult Adaptec for compatibility details on specific products or components.
This document is for informational purposes only. Nothing contained in this Report is intended to, nor shall it create any warranty of any kind or nature whatsoever. ADAPTEC EXPRESSLY DISCLAIMS ANY AND ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO
ARRANTIES OF MERCHANTABILITY, FITNESS FOR PURPOSE, AND NON-INFRINGEMENT. Under no circumstance shall Adaptec have any liability
arising out of or relating to a lack of compatibility or interoperability, or set-up related issues. The user assumes all risks associated with the use or implementation of any of the solutions described in this report.
Adaptec, Inc.
691 South Milpitas Boulevard
Milpitas, California 95035
Tel: (408) 945-8600
Fax: (408) 262-2533
US and Canada: 1 (800) 442-7274 or (408) 957-7274
World Wide Web: http://www.adaptec.com
Copyright 2005 Adaptec Inc. All rights reserved. Adaptec and the Adaptec logo are trademarks of Adaptec, Inc., which may be registered in some jurisdictions.
All other trademarks used are owned by their respective owners.
Information supplied by Adaptec Inc., is believed to be accurate and reliable at the time of printing, but Adaptec Inc., assumes no responsibility for any errors that may appear in this document.
Adaptec, Inc., reserves the right, without notice, to make changes in product design or specifications. Information is subject to change without notice.
P/N: 906724-011
Printed in USA
2/05 3660_1.2