The query optimizer is the key component in the performance of any Enterprise Database Server. IBM has made more than a 25-year investment in enhancements to DB2s cost-based, rule-driven optimizer with the goal of keeping it the best in the industry. A major component of this effort is the implementation of the Starburst extensible optimizer in DB2 UDB. This allows IBM to add new intelligence to the optimizer without having to modify the entire query-compilation process.
As DB2 UDB is enhanced with new features and functionality, the optimizer performance improves. An example is extending the optimizer to understand OLAP SQL extensions and multiple levels of parallel query processing. The latter is particularly important in clusters where each node is an SMP. IBM has also built into the optimizer knowledge about how to work with underlying disk subsystems, such as the arrays of disks inside the ESS. The optimizer takes advantage of its knowledge of the characteristics of the hardware environment; CPU speed, I/O speed, network bandwidth (for federated queries), bufferpool allocations. In addition, it knows the characteristics of the data itself: size of the table, partitioning scheme, uniqueness, existence of indexes, existence of automatic summary tables, when choosing an optimal execution strategy for a given SQL statement. This ability becomes more important as the size of the database increases. In addition, the optimizer can also perform query re-write automatically. This enables DB2 UDB to execute better performing queries, while keeping the results set the same. This capability is especially important for environments where the SQL is being generated by a tooltrue for many decision support applications.

1.2.4 DB2 UDB utilities

DB2 UDB utilities are designed to support both very large databases and also small OLTP databases as well. They incorporate a highly scalable software architecture which allows them to exploit a wide variety of hardware platforms. This book does not attempt to give a complete list of the DB2 utilities, we merely outline here those utilities which have the most interaction with the ESS. For more information on DB2 UDB utilities, please refer to DB2 UDB Administration Guide: Performance V7, SC09-2945.
Backup and recovery utilities
DB2 UDB provides a granular and parallel backup and restore utility. Some of the options available to backup include: Online or offline. Entire database, single tablespace or multiple tablespaces. For incremental backups, delta or cumulative backups can be taken. For partitioned databases, all partitions or a subset of the data partitions can be chosen. The degree of parallelism achieved during the backup and restore process is determined by the number of backup devices available. This parallel capability results in a linear reduction in backup time. For more information about the use of these utilities in an Enterprise Database Server, please refer to Chapter 7, Database backup and recovery on page 99. DB2 UDB interfaces with storage management products, such as the IBM Tivoli Storage manager (TSM) product, which manages backup jobs and log archives from multiple servers. The TSM interface keeps track of backups and their associated logs. These products (integrated through the use of DB2 user exits) allow for the automated archival and retrieval of backups and log files to and from off-line storage.

2.2.13 Logs

Changes to data pages in the buffer pool are logged. Agent processes updating a data record in the database update the associated page in the buffer pool and write a log record into a log buffer. The written log records in the log buffer will be flushed into the log files asynchronously by the logger. On UNIX, you can see a logger process (db2loggr) for each active database using the ps command. Neither the updated data pages in the buffer pool nor the log records in the log buffer are written to disk immediately to optimize performance. They are written to disk by page cleaners and the logger respectively. The logger and the buffer pool manager cooperate and ensure that the updated data page is not written to disk storage before its associated log record is written to the log. This behavior ensures that the database manager can obtain enough information from the log to recover and protect a database from being left in an inconsistent state when the database is crashed resulting from an event, such as a power failure.
2.2.14 Parallel operations
DB2 UDB extensively uses parallelism to optimize performance when accessing a database. DB2 supports several types of parallelism Query I/O Utility

Query parallelism

There are two dimensions of query parallelism: inter-query parallelism and intra-query parallelism. Inter-query parallelism refers to the ability of multiple applications to query a database at the same time. Each query executes independently of the others, but they are all executed at the same time. Intra-query parallelism refers to the simultaneous processing of parts of a single query, using either intra-partition parallelism, inter-partition parallelism, or both. Intra-partition parallelism subdivides what is usually considered a single database operation, such as index creation, database loading, or SQL queries into multiple parts, many or all of which can be run in parallel within a single database partition. Inter-partition parallelism subdivides what is usually considered a single database operation, such as index creation, database loading, or SQL queries into multiple parts, many or all of which can be run in parallel across multiple partitions of a partitioned database on one machine or on multiple machines. Inter-partition parallelism only applies to EEE.

I/O parallelism

When there are multiple containers for a tablespace, the database manager can exploit parallel I/O. Please reference 3.2.1, Know where your data resides on page 32 for the settings required when a container spans multiple physical disks. Parallel I/O refers to the process of writing to, or reading from, two or more I/O devices simultaneously; it can result in significant improvements in throughput. DB2 implements a form of data striping by spreading the data in a tablespace across multiple containers. In storage terminology, the part of a stripe that exists on a single device is a strip. The DB2 term for strip is extent. If your tablespace has three containers, DB2 will write one extent to container 0, the next extent to container 1, the next extent to container 2, then back to container 0. The stripe width - a generic term not often used in DB2 literature - is equal to the number of containers, or three in this case. Extent sizes are normally measured in numbers of DB2 pages. For simpler disk systems, containers for a tablespace would ordinarily be placed on separate physical disks, allowing work to be spread across those disks, and allowing disks to operate in parallel. Since ESS logical disks are striped across an array, the database administrator can allocate DB2 containers on separate logical disks residing on separate ESS arrays. This will take advantage of parallelism both within DB2 and within ESS. For example, four DB2 containers residing on four ESS logical disks on four 7+P arrays would have data spread across 32 physical disks.

3.1.1 Workload characteristics
In order to understand the performance issues associated with a particular database, it is helpful to have an understanding of the different database profiles and their unique workload characteristics. While the characteristics of Online Transaction Processing (OLTP) and Decision Support Systems (DSS) in terms of disk usage can vary widely many of the recommendations in this book are valid for both workload types. When we feel that tuning or configuration parameters should vary due to workload type we will specifically state the differences. Otherwise it can be assumed that our advice applies to both.
Online Transaction Processing (OLTP)
OLTP databases are among the most mission-critical and widely deployed of any of the database types. Literally, millions of transactions encompassing billions of dollars are processed on OLTP systems around the world on a daily basis. The primary defining characteristic of OLTP systems is that the transactions are processed in real-time or online and often require immediate response back to the user. Examples would be: A point of sale terminal in a retail setting An Automated Teller Machine (ATM) used for withdrawing funds from a bank A telephone sales order processing site looking up inventories and taking order details From a workload perspective, OLTP databases typically: Process a large number of concurrent user sessions Process a large number of transactions using simple SQL statements Process a single database row at a time Are expected to complete transactions in seconds, not minutes or hours
OLTP systems process the day-to-day operational data of a business and, therefore, have strict user response and availability requirements. They also have very high throughput requirements and are characterized by large amounts of database inserts and updates. They typically serve hundreds, if not thousands, of concurrent users, which can severely impact system performance.
Decision Support Systems (DSS)
DSS differ from the typical transaction-oriented systems in that they most often consist of data extracted from multiple source systems for the purpose of supporting end-user: Data analysis applications using pre-defined queries Application generated queries Ad-hoc user queries Reporting requirements DSS systems typically deal with substantially larger volumes of data than OLTP systems due to their role in supplying users with large amounts of historical data. Whereas 100 gigabytes would be considered large for an OLTP system, a large DSS system would most likely be 1 terabyte or more. The increased storage requirements of DSS systems can also be attributed to the fact that they often contain multiple, aggregated views of the same data. While OLTP queries tend to be centered around one specific business function, DSS queries are often substantially more complex. The need to process large amounts data results in many CPU intensive database sort and join operations. The complexity and variability of these types of queries must be given special consideration when designing a DSS system for performance.

Figure 3-1 illustrates this technique for a single table space consisting of eight containers.

ESS1 C lu s te r 1

C lu s te r 2

C lu s te r 1

C o n ta in e r 1

C o n ta in e r 2

C o n ta in e r 3

C o n ta in e r 4

C o n ta in e r 5

C o n ta in e r 6

C o n ta in e r 7

C o n ta in e r 8

Figure 3-1 Allocating DB2 containers using a spread your data approach
In addition, the testing described in Appendix A substantiates the following points: You may intermix data, indexes, and tempspaces on RAID arrays. Your I/O activity will be more evenly spread and avoid the skew which you would see if the components were
isolated. This has the added advantage of making available more disk arms to each of these objects. For EEE systems, establish a policy that allows partitions and containers within partitions to be spread evenly across ESS resources. You can choose either a horizontal mapping, in which every partition has containers on every available ESS array, or a vertical mapping, in which DB2 partitions are isolated to specific arrays, with containers spread evenly across those arrays. For EEE systems, selection of horizontal or vertical mapping approaches may be influenced as you consider the number of DB2 partitions, the number of arrays, and how future growth will influence those factors. The vertical mapping approach works well as long as the number of ESS RAID arrays is an even multiple of the number partitions. Otherwise, the horizontal approach is probably best. Please see Appendix A, Data placement tests on page 107 for a full description of horizontal and vertical mapping.
Use DB2 to stripe across containers
Look again at Figure 3-1. In this case, we are striping across arrays, across disk adapters, across clusters, and across enterprise storage system boxes. This can all be done using the striping capabilities of DB2s container and shared nothing concept. This eliminates the need to employ AIX logical volume striping.
3.2.3 Selecting DB2 logical sizes
The three settings in a DB2 system that primarily effect the movement of data to and from the disk subsystem work together. These include page size, extent size, and prefetch size.

Page size

Prefetch size

The tablespace prefetch size determines the degree to which separate containers can operate in parallel. Although larger prefetch values might enhance throughput of individual queries, mixed applications would generally operate best with moderate-sized prefetch and extent parameters.You will want to engage as many arrays as possible in your prefetch, to maximize throughput. It is worthwhile to note that prefetch size is tunable. By this we mean that prefetch size can be altered after the tablespace has been defined and data loaded. This is not true for Extent and Page size which are set at Table space creation time and can not be altered without re-defining the table space and re-loading the data. Tip: The prefetch size should be set such that as many arrays as desired can be working on behalf of the prefetch request. In non-ESS storage, the general recommendation is to calculate prefetch size to be equal to a multiple of the extent size times the number of containers in your tablespace. For ESS you may work with a multiple of the extent size times the number of arrays underlying your tablespace.
3.2.4 Selecting ESS logical disk sizes
ESS gives you great flexibility when it comes to allocating disk space. This is particularly helpful when you need to attach multiple hosts or implement security. However, this flexibility can present a challenge as you plan for future requirements.
Criteria for selecting ESS logical disk sizes
ESS supports a high degree of parallelism and concurrency on a single logical disk. As a result, performance tests have indicated that a single logical disk consuming an entire array achieves the same performance as many smaller logical disks on that array. However, consider how logical disk size affects systems management. Smaller logical disks allows for more granularity when managing storage, although it increases the number of logical disks seen by the operating system. Select an ESS logical disk size that allows for granularity and growth without proliferating the number of logical disks. If you are using FlashCopy you need to allow capacity in each LSS for the target volumes. You should also take into account your container size and how the containers will map to AIX logical volumes and ESS logical disks. In the simplest situation the container, AIX logical volume and ESS logical disk will be the same size. Tip: Try to strike a reasonable balance between flexibility and manageability for your needs. Our general recommendation is that you create no fewer than two logical disks in an array, and minimum logical disk size should be around 16G. Unless you have an extremely compelling reason, use a single logical disk size throughout the ESS. Eight logical disks per RAID array can be a reasonable balance. Smaller logical disks have the following attributes: Advantages: They allow an administrator to assign storage to different applications and hosts They allow greater flexibility in performance reporting. For example, ESS Expert reports statistics for logical disks. They allow greater flexibility in use of Copy Services. Disadvantages: Small logical disk sizes can contribute to proliferation of logical disks, particularly in SAN environments and large configurations. Larger logical disks have the following attributes: Advantages Simplifies understanding of how data maps to arrays. Reduces the number of disks that appear to the operating system and storage administrators Disadvantages Less granular storage administration

# of Rows Row Size (M) 50 5000
Total Raw data size (Gb) 18.0 0.3 250.0
Figure 4-1 Calculating raw data requirements
Once the Total raw data is known you can continue with educated sizing calculations to complete your capacity estimates, or you can apply some general rules of thumb.

Rules of thumb

Rules of thumb can be employed to move you from the base data (also referred to as raw data) to the entire amount of space required. In the past, this rule of thumb has been 3 to 1, for example, for every 1 GB of raw data, you would need 3 GB in storage. While this rule of thumb is useful, you may want a more precise method of calculating your capacity requirements. (Especially as the cost of storage may be the largest portion of your budget for this system). Using Example 4-1, the rule of thumb would indicate a minimum of 805 GB of storage as a starting point.

Calculations

To calculate your capacity requirements more precisely, you will need to know more about your planned database, including how many indexes are defined for each table, the length of the indexes as well as similar information for summary tables. This information combined with the expected number of rows of the tables will give you a good start on calculating your capacity requirements.
The following example shows another spreadsheet calculation for base storage. The result in this case is slightly higher than what the Rules of Thumb method would indicate but should be more accurate.
Table Name C us tom er It e m S a le s. To ta l

Row Size 50

# of Rows (M) 5000
Total Raw data size (GB) 18.0 0.3 250.0 268.3 # of Rows (M) Total Table Size (GB) 0.8 0.2 75.0 75.0 151.0
Table Name C us tom er C us tom er S a le s S a le s. To ta l
Index Name Nam e z ip c o d e S a le s _ IX S a le s _ n a m e _ IX

Row Size 15 15

5000 5000
Summary Table Name S a le s b y p ro d u c t S a le s b y re p. To ta l
Base table S a le s S a le s

Row Size 50 50

# of Rows (M) 1000 100
Total Table Size (GB) 50.0 5.0 55.0

5.2.1 Virtual paths - Subsystem Device Driver (SDD)
The concept of a virtual path comes into play when more than one connection between the ESS and the AIX server are provided. Virtual paths (vpaths), allow the system to take advantage of the multiple host adapters, allowing greater overall I/O bandwidth. 56
From the AIX point of view, the resource that needs to be mapped (an hdisk from iostat), now translates first into a vpath and then through that vpath to the ESS volume. Figure 5-4 on page 57, shows the graphical view of the ESS. We have manually identified the hdisks and vpath that the resources map to.
hdisk247 hdisk261 hdisk267 hdisk283

vpath124

Volume 104-13902
Figure 5-4 AIX resources mapped to ESS graphical layout
The commands outlined in the next section are necessary to be able to track the AIX perspective through to the DB2 UDB tablespace definitions. However, these commands alone will not provide enough information to track the disk activity (as reported by iostat) back to the ESS. In order to do that, we need the virtual path configuration.
Virtual path configuration lsvpcfg
Similar to many of the AIX commands, the lsvpcfg command can be executed without parameters. In this case, all of the vpaths which are defined will be returned. The output of the lsvpcfg command will tell us how many virtual paths are defined for a particular resource. This is important when reviewing I/O activity. In order to calculate the throughput for a particular disk group (array), all AIX physical volume (hdisk) activity for the same vpath should be added together. In the case where multiple ESS logical disks have been defined over a single array, multiple vpaths statistics may need to be added together to obtain the throughput for the entire array. In the example below, vpath0, vpath1, vpath3, vpath6, vpath7 and vpath9 have one single path defined, while vpath2, vpath4 and vpath5 have eight paths defined. The information which precedes the equals sign is the volume number of the ESS logical disk associated with this vpath. To match this with the ESS Specialist displays, a hyphen needs to be inserted after the third digit. (For example 31213902 shown in Example 5-1 becomes 312-13902.)
Example 5-1 lsvpcfg command vpath0 (Avail pv ssa4vg3) 31213902 = hdisk140 (Avail ) vpath1 (Avail pv ssa4vg3) 31313902 = hdisk141 (Avail ) vpath2 (Avail pv ssa4vg3) 31413902 = hdisk142 (Avail ) hdisk182 (Avail ) hdisk183 (Avail ) hdisk184 (Avail ) hdisk185 (Avail ) hdisk186 (Avail ) hdisk187 (Avail ) hdisk188 (Avail ) vpath3 (Avail pv ssa4vg3) 31513902 = hdisk143 (Avail ) vpath4 (Avail pv ssa4vg3) 31613902 = hdisk144 (Avail ) hdisk189 (Avail ) hdisk190 (Avail ) hdisk191 (Avail ) hdisk192 (Avail ) hdisk193 (Avail ) hdisk194 (Avail ) hdisk195 (Avail ) vpath5 (Avail pv ssa4vg3) 31713902 = hdisk145 (Avail ) hdisk196 (Avail ) hdisk197 (Avail ) hdisk198 (Avail ) hdisk199 (Avail ) hdisk200 (Avail ) hdisk201 (Avail ) hdisk202 (Avail ) vpath6 (Def pv ) 51213902 = hdisk176 (Avail pv ssa6vg3) vpath7 (Def pv ) 51313902 = hdisk177 (Avail pv ssa6vg3) vpath8 (Def pv ) 51413902 = hdisk178 (Avail pv ssa6vg3) hdisk203 (Avail pv ssa6vg3) hdisk204 (Avail pv ssa6vg3) hdisk205 (Avail pv ssa6vg3) hdisk206 (Avail pv ssa6vg3) hdisk207 (Avail pv ssa6vg3) hdisk208 (Avail pv ssa6vg3) hdisk209 (Avail pv ssa6vg3) vpath9 (Def pv ) 51513902 = hdisk179 (Avail pv ssa6vg3)

Most Active Logical Volumes -----------------------------------------------------------------------util #rblk #wblk KB/s volume description -----------------------------------------------------------------------0.0 847.7 /dev/lv.rb.D1.DA8c1 raw 0.0 847.7 /dev/lv.rb.D1.DA5b1 raw 0.0 846.8 /dev/lv.rb.D1.DA6d1 raw 0.0 847.7 /dev/lv.rb.D1.DA3b1 raw 0.0 847.7 /dev/lv.rb.D1.DA4e1 raw 0.0 846.8 /dev/lv.rb.D1.DA6a1 raw 0.0 846.8 /dev/lv.rb.D1.DA8a1 raw 0.0 846.8 /dev/lv.rb.D1.DA5d1 raw 0.0 847.7 /dev/lv.rb.D1.DA5a1 raw Most Active Physical Volumes -----------------------------------------------------------------------util #rblk #wblk KB/s volume description -----------------------------------------------------------------------0.8 1872.6 /dev/hdisk179 IBM FC 2105F20 0.16 1790.3 /dev/hdisk176 IBM FC 2105F20 0.40 1913.4 /dev/hdisk161 IBM FC 2105F20 0.32 1888.9 /dev/hdisk165 IBM FC 2105F20 0.40 1895.1 /dev/hdisk160 IBM FC 2105F20 0.8 1878.7 /dev/hdisk174 IBM FC 2105F20 0.32 1843.9 /dev/hdisk182 IBM FC 2105F20 0.24 1538.0 /dev/hdisk424 IBM FC 2105F20 0.8 1871.7 /dev/hdisk157 IBM FC 2105F20 -----------------------------------------------------------------------Detailed Logical Volume Stats (512 byte blocks) -----------------------------------------------------------------------VOLUME: /dev/lv.rb.D1.DA8c1 description: raw reads: 1810(0 errs) read sizes (blks): avg 32.0 min 32 max 32 sdev 0.0 read times (msec):avg 43.261 min 32.991 max 524.179 sdev 39.365 read sequences: 7 read seq. lengths:avg 8274.3 min 64 max 28864 sdev 11853.9 seeks: 7 (0.4%) seek dist (blks):init 260288, avg 85.3 min 64 max 128 sdev 30.2 time to next req(msec): avg 18.864 min 0.001 max 536.519 sdev 28.021 throughput:847.7 KB/sec utilization:0.96 -----------------------------------------------------------------------Detailed Physical Volume Stats (512 byte blocks) -----------------------------------------------------------------------VOLUME: /dev/hdisk179 description: IBM FC 2105F20 reads: 2000(0 errs) read sizes (blks): avg 64.0 min 32 max 64 sdev 1.0 read times (msec):avg 17.047 min 0.053 max 516.848 sdev 28.503 read sequences: 1902 read seq. lengths:avg 67.3 min 32 max 192 sdev 14.3 writes: 1 (0 errs) write sizes (blks): avg 8.0 min 8 max 8 sdev 0.0 write times (msec):avg 4.685 min 4.685 max 4.685 sdev 0.000 write sequences: 1 write seq. lengths:avg 8.0 min 8 max 8 sdev 0.0 seeks: 1903(95.1%) seek dist (blks):init 8115008, avg 18311324.2 min 64 max 41941632 sdev 10162236.0 seek dist (%tot blks):init 8.82141, avg 19.90530 min 0.00007 max 45.59260 sdev 11.04685 time to next req(msec): avg 17.070 min 0.066 max 505.553 sdev 23.220 throughput:1872.6 KB/sec utilization:0.5

Obviously, transactions that were in process at the moment the write suspend was issued will not complete. They will be backed out when the database copy is started. Nevertheless, this process can be useful for creating snapshots of the database. One example would be when you want to offload intensive query/reporting activities that did not rely on up-to-the-second information. A DB2 backup of this copied database can be restored to the production system, however ALL log records generated after the FlashCopy completed are no longer usable. This will essentially provide a version level backup and it is not recommended that it be restored to the production system.
7.4.8 Using the FlashCopy as a backup
Once the FlashCopy has been taken, the copy itself can be used to restore the production system in the case of a failure where no DB2 backups are available. This can be done by following these steps: 1. Stop the DB2 production database, preferable by shutting down the entire instance, for example, db2stop 2. Mount the FlashCopy over top of the production DB2 database tablespace containers and database directory. DO NOT mount the DB2 log directory FlashCopy. 3. Issue the following db2inidb database_name AS MIRROR This will bring the FlashCopy (or split-mirror) up as in the AS STANDBY mode, at which point the surviving log files can be replayed by issuing a db2 rollforward database database_name to end of logs and complete Although we did not test the As standby and As mirror scenarios, we do know that a hot backup functionality with no data loss has been implemented within Tivoli Storage Manager, and we recommend that product to you (see redbook Backing Up DB2 Using Tivoli Storage Manager, SG24-6247).

Appendix A.

Data placement tests
This appendix includes a description of the tests that were done on-site in Toronto as a part of this redbook project. These tests were executed to determine the comparative effect of various data placement options as well as to compare the effects of various database tuning parameters on performance. While we are outlining here only the tests done directly due to the redbook internship, it is important to note that testing of ESS with DB2 UDB had already been conducted as part of a joint effort between IBM Software Group and IBM Storage Division. Benchmark work has already been completed and documented in the following whitepapers (see DB2 UDB for AIX Performance of Enterprise Storage Server and DB2 UDB EEE Scalability: RS/6000 S80 & Enterprise Storage Servers), which can be found at: http://www-4.ibm.com/software/data/pubs/papers/index.html#udbaix: We recognize that while we have attempted to answer some of the questions raised by this technology, there are and will continue to be additional questions to answer. For example, although we originally intended to run both SMS and DMS tests, we were unable (given our timeframe) to complete both SMS and DMS tests.

Eight-path setup

Once a baseline of testing was done using the multi-path setup, we decided to enable eight paths for all of the arrays which we would be using. This would ensure that (at least from an ESS / AIX perspective) we should be able to drive the system evenly across all of the resources we had available. An important item to remember is that when the virtual pathing changes, the hdisks / vpaths are also changed. In this case, most of the hdisks in the eight-path setup referred to different underlying ESS logical disks than they had previously.

Three-path setup

During the course of the redbook internship, the system was moved to a new location. This opportunity was taken to upgrade from SCSI connections to Fibre. Three Fibre connections were made available, and all ESS logical disks were enabled with three paths. While Fibre connections are faster than the SCSI connections were, they are not twice as fast. Since we moved from eight paths down to three, we expect to see a slight reduction in throughput capabilities of the ESS for those workloads that are pushing the limits of system throughput. Again, all of the logical names were re-assigned. So it is important to obtain the base configuration data whenever virtual pathing changes. See Chapter 5, Mapping ESS resources to AIX and DB2 UDB on page 53 and Chapter 6, Diagnostics and performance monitoring on page 69.
Large versus small logical disks
The following discussion presents a relevant question, and then offers our test design and results.

Question

On very large data warehouse applications where the number of ESS arrays (ranks) for a single application can be large, the proliferation of hdisks (one for each virtual path and ESS logical disk), can become problematic. Is it true that logically dividing up the array using ESS will provide equivalent performance to logically dividing up the array using AIX logical volume manager? If so, customers with large data warehousing needs can use larger ESS logical disk sizes when setting up their systems without paying a performance penalty for doing so. And a secondary question, is there a performance difference between logical disks on the same array? That is, if there is a loved application, do we need to create its logical disks in a specific array location?

Test design

Tablescans

Elapsed Time seconds 450

Single Path Tablescan

Four-path Tablescan
Figure A-3 Large versus small logical disks elapsed tablescan times
Vertical versus horizontal placement 36 arrays
What is the impact of 'vertical' vs. 'horizontal' layout of containers across ESS logical disks for DB2 UDB EEE? For our purposes, vertical layout means that each data partition had exclusive use of a subset of the total number of arrays (that is, arrays were not shared). A horizontal layout would mean that each data partition used all arrays (that is, arrays were shared). If the performance is similar regardless of the association of disk to data partition, then we can be assured that there will be flexibility for growth options (for example, addition of containers) without a performance penalty due to data placement.
This test design shows the Shark, AIX and DB2 setups (vertical and horizontal) and measurements. Shark setup Each array off of disk adapters 3 through 8 has four ESS logical disks defined. We used one of these ESS logical disks from each array for our testing (36 logical disks). This setup did not vary for these tests. AIX setup
Each ESS logical disk was divided into multiple logical volumes, resulting in 6 raw devices per array for use in data storage, and 6 filesystems per array to use for Temporary Storage. DB2 setup Each test was run with data and index defined in the same DMS tablespace using an extent size of 16 pages and a prefetch size of 96 pages using a 16 KB page size.

Vertical

Seven EEE data partitions were defined. Node 0 was the catalog node, nodes 1 through 6 were data and index partitions. The data partitions each had exclusive use of six ESS logical disks (not shared). 36 containers were defined for each data partition. Figure A-4 illustrates this layout.

Vertical Layout

B BBBBB BBBBBB BBBB BB AAAAAA CCC CC C BBBBB B D DD DDD BBBBB B DDD DD D DD DD DD DD DD DD C CC CC C EEEEEE DD DD DD FFFFFF DDD DD D FFFFFF FFFFFF FFFFFF EEEEEE FFFFFF FFFFFF
AAAA AA AAAAAA AAAA AA A AAAAA

AAAAAA

BBB BBB
D P1 D P2 D P3 D P4 D P5 D P6

= = = = = =

A B C D E F
C CCC CC CCC CC C CC CC CC CCC CC C EEEEE E EEEEE E EE EEEE EEEEE E
Figure A-4 Vertical layout diagram

Horizontal

Seven EEE data partitions were defined. Node 0 was the catalog node, nodes 1 through 6 were data and index partitions. The data partitions each used one container from every ESS logical disk (shared). Again, 36 containers were defined for each data partition. Figure A-5 illustrates this.

INTERNATIONAL TECHNICAL SUPPORT ORGANIZATION
BUILDING TECHNICAL INFORMATION BASED ON PRACTICAL EXPERIENCE
IBM Redbooks are developed by the IBM International Technical Support Organization. Experts from IBM, Customers and Partners from around the world create timely technical information based on realistic scenarios. Specific recommendations are provided to help you implement IT solutions more effectively in your environment.
For more information: ibm.com/redbooks

ISBN 0738422584

rpm -qa compat-libstdc++*
To install these packages on Red Hat Linux, use the Red Hat Linux installation CDs and the rpm command, or the up2date utility. For example, to install the compat-libstdc++ package from the Red Hat Linux CD, mount the Red Hat Linux CD #4, and enter this command as root:
rpm -ivh /mnt/cdrom/RedHat/RPMS/compat-libstdc++-296-2.96-132.7.2.i386.rpm
To install using the up2date package, run this command as root:

up2date compat-libstdc++

To install these packages on SUSE Linux, you can also use rpm or use the handy YaST (Yet another Setup Tool) utility. For example, to install nfs-utils on SLES 9 using YaST: 1. Log on as root 2. From the YaST Control Center, select Software -> Software Management, then use the search "nfs-utils" to find the appropriate packages.
5. Installation considerations and planning
After verifying that your system meets the basic requirements, you should plan your installation by considering installation methods and the creation of users and groups: Installation choices: db2setupinstallation db2_installinstallation DB2 response file installation User and group setup: User and group requirements Creating users
5.1. Installation methods
After downloading and extracting the tar.gz file, the file tree looks like this:
exp/ -| disk1/ -| db2_install db2setup installFixPack db2/ -| doc/ -|

(* (* (* (* (*

command line installation script *) GUI installation launcher *) not applicable for Express-C *) installation and licensing files *) install instructions and release notes *)
You can use three methods to install DB2: db2setup utility db2_install utility a DB2 response file Each method has its own advantages and disadvantages. The choice of method depends on your level of expertise and type of environment, but in general, if a graphical interface is available, using db2setup is recommended for ease of installation.
5.1.1. db2setup installation
The db2setup command starts the DB2 Launchpad, a graphical tool that configures and organizes the DB2 installation. It lays down the DB2 filesets, creates and configures DB2 instances, users and groups, communications, the administration database used by the DB2 Tools, and sets up notification processes. The DB2 Launchpad also allows you to create a response file for replicating a particular setup to other systems. Using db2setup is the best method for less experienced users, because most of the configuration is automatically performed during the installation. As mentioned previously, you require an X Window System capable of rendering a graphical user interface to use db2setup successfully.

5.1.2. db2_install installation
The db2_install command script installs all DB2 packages on your Linux system. This method is reliable and commonly used by expert users for installing DB2 on larger, more complex systems. However, tasks such as setting up users and groups, creating instances, tools catalog database creation, and notification setup need to be performed manually after the installation is completed. One limitation to db2_install is that it only installs user assistance (like help, messages, and tool interfaces) in English, whereas db2setup supports the installation of additional languages. Also, a db2_install installation can take longer (because of the higher number of manual configuration tasks); it requires a higher level of knowledge and skill, and it cannot be used to create response files.
5.1.3. Response file installation
A response file can be created using the DB2 Setup Wizard or by editing an existing DB2 response (.rsp) file. It allows you to install DB2 across multiple machines with consistent installation and configuration settings. A response file installation is fast, because it bypasses the graphical wizard configuration. Another advantage of using a response file instead of the db2_install command is that it creates a Database Administration Server (DAS) on each machine, while with db2_install the DAS must be created manually after installation. There is a sample response file (db2exp.rsp) included on the installation image that can be modified and used to install DB2, configure users, create instances, set up notification, create the tools catalog, and configure a large number of database management parameters. This is the quickest installation method if you already have all the information you need. Unlike db2setup, the response file installation is not interactive, and it takes longer to prepare the response file initially.
5.2. User and group setup
DB2 requires user IDs and groups for database authentication. This section discusses these IDs and groups and how to create them.
5.2.1. User and group requirements
DB2 requires a minimum of three users and groups: an instance owner, a fenced user, and the Database Administration Server (DAS) user. You may use the default names provided by the setup utilities, or specify your own user and group names. For example, consider the following default user IDs and group names produced by the DB2 Setup Launchpad:
Table 3. Default user IDs and group names Required user Instance owner User name db2inst1 Group name db2grp1 Description Administers a DB2 instance Responsible for executing "fenced" user defined functions, such as JDFs and stored procedures. 9

Fenced user

db2fenc1

db2fgrp1

IBM DB2 Express-C for Linux HOWTO DAS user dasusr1 dasadm1 Administers the DB2 Administration Server

5.2.2. Creating users

You must have root authority to create users and groups. There are three ways to create a DB2 user ID: DB2 Launchpad: the DB2 Launchpad installation creates all of the required users and groups for you during installation. The default users and groups that get created are displayed in the table above. This method also gives you the option to specify your own user and group names. Response file: Users can also be created during a response file installation if you specify user and group information in the response file. For example, consider the following entries in a response file that create the three required users and groups for DB2:
* DAS user DAS_USERNAME = dasusr1 DAS_GROUP_NAME = dasadm1 DAS_HOME_DIRECTORY = /home/dasusr1 DAS_PASSWORD = ****** * Instance-owning user inst1.NAME = db2inst1 inst1.GROUP_NAME = db2grp1 inst1.HOME_DIRECTORY = /db2home/db2inst1 inst1.PASSWORD = ****** inst1.AUTOSTART = YES inst1.AUTHENTICATION = SERVER inst1.SVCENAME = db2c_db2inst1 inst1.PORT_NUMBER = 50001 inst1.FCM_PORT_NUMBER = 60000 inst1.MAX_LOGICAL_NODES = 4 * Fenced user nst1.FENCED_USERNAME = db2fenc1 inst1.FENCED_GROUP_NAME = db2fgrp1 inst1.FENCED_HOME_DIRECTORY = /db2home/db2fenc1 inst1.FENCED_PASSWORD = ******
Manually using command line. To use this method, follow these steps: 1. Log on to your machine as root. 2. Create groups for the instance owner, the fenced user and the DAS user by using these commands:
groupadd db2grp1 groupadd db2fgrp1 groupadd dasadm1
Linux will create the groups using the first available group ID (GID) numbers available. You can specify GID numbers if you prefer. 3. Create a user that belongs to each group and specify the home directory. For example, you can choose to place all home directories in /db2home by entering these commands:
useradd -g db2grp1 -m -d /db2home/db2inst1 db2inst1 -p password1 useradd -g db2fgrp1 -m -d /db2home/db2fenc1 db2fenc1 -p password2 useradd -g dasadm1 -m -d /db2home/dasusr1 dasusr1 -p password3
5.2.3. Adding existing users as DB2 users
To enable existing system users to act as DB2 instance administrators, you must make a few modifications to the existing user accounts: Add your user to the db2grp1 group. The SYSADM_GROUP database manager configuration parameter controls who has that level of access for all databases in that instance. You can determine what group is set as the SYSADM group by entering:
db2 get dbm cfg | grep SYSADM_GROUP
By default, DB2 sets this as the db2grp1 group. To add the user to the SYSADM group, modify the /etc/groups file (as root), adding the user account ID to the line for the DB2 system admin group. For example:

db2grp1:x:102:jackc,ellenp,frankj
Update the user's profile by adding the following lines to the.bashrc file in the home directory
# The following three lines have been added by the DB2 SYSADM. if [ -f /home/frankj/sqllib/db2profile ]; then. /home/frankj/sqllib/db2profile fi
After installation, if you wish to grant or revoke access to databases (or other objects), use the "User and Groups" section of the Control Center or the GRANT and REVOKE SQL statements. If you want to see an example, by default the SAMPLE database grants a number of privileges to "PUBLIC".
6. Installing DB2 Express-C
This chapter covers a typical setup scenario for installing DB2 Express-C on a 32-bit Linux system, using the db2setup installation method.
6.1. db2setup installation

6.1.1. Prerequisites

Ensure that your system meets the software, memory, and disk requirements outlined in Section 5. You require root authority to perform the installation. The DB2 Setup Launchpad is a graphical installer. You must have X Window System software capable of rendering a graphical user interface for the DB2 Setup Launchpad to run on your machine.

6.1.2. Procedure

To install DB2 Express-C 9: 1. Log on to the system as a user with root authority. 2. If you are installing from a CD or DVD, enter the command to mount the DB2 installation media. For example,

mount /media/cdrom

If you are using a downloaded tar.gz file, extract the contents of this archive to a location on your harddrive. For example, presuming the tar.gz file was downloaded to the /tmp directory:
cd /tmp tar zxf db2exc_912_LNX_x86.tar.gz
3. Change to the install directory. For the CD/DVD install:
cd /media/cdrom/exp/disk1
For the downloaded and extracted tar.gz file:

cd /tmp/exp/disk1

4. Run the db2setup command.

./db2setup

5. When the IBM DB2 Setup Launchpad opens, select "Install a Product". 6. Select the "Install New" button in the main window. 7. When the Welcome to the DB2 Setup wizard window appears, click "Next". 8. Read the license agreement and select the "Accept" button if you accept the terms (the "Decline" button is selected by default). Click "Next". 9. On the Select the installation typewindow, select the type of installation you want to perform. You have three installation options: Typical: 460-560 MB. This option installs and configures most of the DB2 components, using a typical configuration with default values. This option includes all of the core features, along with ODBC support, and commonly used tools such as the Control Center. You can select the "View Features" button to see what components get installed with this option. Compact: 380-460 MB. This option installs and configures only the basic DB2 components, with minimal configuration performed on your computer. With this option you get the base server and client support, along with Java and SQL procedure support. Again, you can click the "View Features" button for more information. 6. Installing DB2 Express-C 12

IBM DB2 Express-C for Linux HOWTO Custom: 320-820 MB. This option allows you to install only those components that you select; and to create and customize a basic DB2 instance. Make your choice and click "Next". (The remainder of these instructions are based on the Custom installation option). 10. On the Select the installation action window, choose the "Install DB2 Express on this computer" radio button. You can also choose to save your installations settings in a response file by selecting "Save your settings in a response file". You should choose this option if you plan to install DB2, with these same settings, on other computers in your environment. 11. Choose the DB2 elements you want on the Select the features to install window. You can preview each component by selecting it and reading the corresponding description in the Description section. You can also choose where to install the DB2 program files. By default for a first time installation, this location is /opt/ibm/db2/V9.1/ Once you have decided what features you want to install and the destination, click "Next". 12. Select what languages you want installed for the messages and user interface on your computer in the Languages window. English is always installed, regardless of other language choices. Click "Next". 13. In the Specify the location of the DB2 Information Center window, select how you want to access the DB2 Information Center. The DB2 Information Center contains documentation for DB2 and other related DB2 products. The "On the IBM Web site" button is selected by default. By choosing this option, DB2 is configured to access documentation on the Web using your browser. If you have a copy of the DB2 Information Center installed elsewhere on an intranet server, you can choose the "On the following intranet server" button. Specify the values in the Host name and Port number text fields (the default value for the port number is 51000). Click the "Help" button for more information, otherwise click "Next". 14. Fill out the information for the user that will administer the DB2 Administration Server in the Set user information for the DB2 Administration Server window. Select either the "New user" button, or the "Existing user" button. The Password and Confirm password text boxes are outlined in red indicating required fields that must be filled in for the installation to continue. If you choose to create a new user, a user account will be created (including a home directory) on your Linux system. Click "Next". 15. If you want to create a new basic DB2 instance to store your database, then select the "Create a DB2 instance" button on the Set up a DB2 instance window. Otherwise, select the "Defer this task until after installation is complete" button. If you select this second option, you will need to create a new instance after the installation by using the db2icrt command. Click "Help" for more information, otherwise click "Next". 16. If you create a new instance, fill out the information for the DB2 instance owner on the Set user information for the DB2 instance owner window. Select either the "New user" button, or the "Existing user" button. The Password and Confirm password text boxes are outlined in red indicating required fields that must be filled in for the installation to continue. Again, if you choose to create a new user, a user account will be created (including a home directory) on your Linux system. Click "Next". 17. Fill out the information for the DB2 fenced user on the Set user information for the fenced user window. Select either the "New user" button, or the "Existing user" button. The Password and Confirm password text boxes are outlined in red indicating required fields that must be filled in for the installation to continue. For more information on fenced users, and how fenced users relate to fenced and non-fenced user-defined functions (UDFs) and stored procedures, click "Help". Otherwise, click "Next" to continue. 18. Configure the DB2 instance for TCP/IP communications on the Configure instance IP communication and startup window. You may either accept the default values found in the Service name and Port number fields, or select your own. If you want to configure the instance properties at a later time, select the "Do not configure at this time" button. This service name and port are added 6. Installing DB2 Express-C 13

IBM DB2 Express-C for Linux HOWTO to the services file for the system, typically found in /etc/services. You can also choose whether to start up this instance at system startup. This is recommended if you are planning on running your system as a DB2 server. Click "Next". 19. On the Prepare the DB2 tools catalog window, select the "Prepare the DB2 tools catalog" button if you want to use the Task Center or Task Scheduler. This option creates a local database that stores task metadata. If you do not have this task repository, the scheduler will not function. If you want to create a DB2 tools catalog after you finish the installation, select the "Do not prepare the DB2 tools catalog" button. You can create the DB2 tools catalog later manually by using the CREATE TOOLS CATALOG command. Click "Next". If you decide to create a DB2 tools catalog, you can specify which instance, database, and schema in which to store the DB2 tools catalog. The DB2 tools catalog will be placed in the home directory of the instance owner. Click "Next". 20. Specify a notification mail server and contact list on the Set up notifications window. This allows DB2 to notify administrators if a database requires attention. Either create a new contact list that is stored locally, or use an existing global contact list that resides on a remote DB2 server. Use an available SMTP server that will accept an unauthenticated SMTP message for e-mail notifications. You may also choose to skip this step; DB2 will still record all administration notifications in the log files. Click "Next". 21. If you specified a new local list for contacts in the previous step, then in the Specify a contact for health monitor notification, you can add a name and email address where DB2 Health Center messages will be sent for any health alerts from DB2 databases. 22. On the Start copying files window, review the installation settings, and then click "Finish" to complete the installation. The Current settings panel provides a summary of your installation and configuration settings. While the files are being copied to your computer, you will see two status bars on the Installing DB2 Express window, one indicating the progress of each individual installation task, and one for the overall installation progress. When the installation is finished, the Setup Complete window indicates what, if any, post-installation steps need to be taken, along with a tab (labelled "Log File") that shows the log file report for the installation. Click "Finish" to close this window and the Setup Launchpad.

11. Installing DB2 Express-C on SUSE Linux Enterprise Server (SLES) 9
Before proceeding, please ensure that you have read Section 5 and Section 6. These installation notes are based on a single-partition installation using the 2.6.5-7.97-default kernel.

11.1. Installation notes

The installation on SLES 9 completed successfully, with no problems getting the GUI installer working, and no errors during the install. This smooth installation isn't surprising, given that SLES 9 is an officially supported platform for DB2.
11.2. Post-installation notes
This will ensure that the DB2 instance is initialized and ready to receive incoming connections after a reboot (intentional or otherwise). Also, to allow DB2 access through the firewall (which is enabled by default), it was necessary to add the port values to the firewall using the YaST utility. You can use the db2 get dbm cfg | grep SVCENAME command to retrieve the DB2 instance service name. By default, this is set as a TCP connection at port 50001. Note that you will need to add another new TCP rule to allow firewall access if you create any additional instances later. If you want to remotely administer the DB2 installation on this system, the UDP and TCP connections at port 523 should also be allowed through the firewall. To open these ports using YaST, open the YaST tool, and select "Security and Users, then select the "Firewall" icon. In the setup panel, click "Next" and then on the "Expert button. In the ports dialog, add 523 50001. Click "Next" until the setup wizard completes and then "Continue" so that YaST will restart the firewall with the new configuration. Remote connections will now pass through to the DB2 instance.
12. Installing DB2 Express-C on SUSE Linux Enterprise Server (SLES) 10
Before proceeding, please ensure that you have read Section 5 and Section 6. These installation notes are based on a single-partition installation using the 2.6.16.21-0.8-default kernel. Novell AppArmor security was installed and enabled during the installation and testing.

12.1. Installation notes

The installation on SLES 10 completed successfully, with no problems getting the GUI installer working, and no errors during the install. This smooth installation isn't surprising, given that SLES 10 is an officially supported platform for DB2.
12.2. Post-installation notes
13. Installing DB2 Express-C on SUSE Linux Enterprise Desktop 10
Before proceeding, please ensure that you have read Section 5 and Section 6. These installation notes are based on a single-partition installation using the 2.6.16.21-0.25 kernel.

13.1. Installation notes

The installation on SLED 10 completed successfully, with no problems getting the GUI installer working, and no errors during the install.
13.2. Post-installation notes
This will ensure that the DB2 instance is initialized and ready to receive incoming connections after a reboot (intentional or otherwise). Also, to allow DB2 access through the firewall (which is enabled by default), it was necessary to add the port values to the firewall using the YaST2 utility. You can use the db2 get dbm cfg | grep SVCENAME command to retrieve the DB2 instance service name. By default, this is set as a TCP connection at port 50001. Note that you will need to add another new TCP rule to allow firewall access if you create any additional instances later. If you want to remotely administer the DB2 installation on this system, the UDP and TCP connections at port 523 should also be allowed through the firewall. To open these ports using YaST, open the YaST tool, and select "Security and Users, then select the "Firewall" icon. In the Allowed Services setup panel, click on the "Advanced" button. In the TCP ports dialog, add 523 50001, and in the UDP ports dialog, add 523. Click "OK" and then "Next" until the setup wizard completes and restarts the firewall. Remote connections will now pass through to the DB2 instance.
14. Installing DB2 Express-C on Mandriva Linux 2007
Before proceeding, please ensure that you have read Section 5 and Section 6. Note that IBM does not officially support this distribution for DB2 9. Any problems you encounter using this distribution must be replicated on a supported distribution before IBM Support can help you resolve the issue. These installation notes are based on a single-partition installation using the 2.6.17-5mdvlegacy kernel.

14.1. Installation notes

Note that root logins are not allowed in Mandriva, so you will need to log on as another user, then run the su -l command from a terminal session to gain root access before you launch the./db2setup command. The only installation error encountered was found when launching the./db2setup command. The screen displayed this error:

3. Enable DB2 service ports for remote connections. To enable outside users to get to your DB2 databases, you'll need to set up the DB2 service port, as it is not configured by the Synaptic installation process. As the regular user, run sudo vi /etc/services and add this line to the end of the file:
Now remote administrators can connect to the DAS server to administer databases on your Ubuntu system. 5. Correct instance restart problem One issue of note with the Ubuntu 7.04 installation is that the DB2 instances do not properly restart after a system reboot, and setting DB2AUTOSTART=YES does not resolve the issue. One workaround to this problem was suggested in part by IgorM on the DB2 Express-C forum; and that is to create and execute your own DB2 instance start up script. 16. Installing DB2 Express-C on Ubuntu 7.04 'Feisty Fawn' 29
IBM DB2 Express-C for Linux HOWTO To begin with, login and create a new script file using the command:

sudo vi /etc/init.d/db2

and then insert this text:
#!/bin/sh # # Script to start DB2 instances on bootup. # set -e. /lib/lsb/init-functions case "$1" in start) /opt/ibm/db2exc/V9.1/instance/db2istrt ;; stop|restart|reload) ;; esac exit 0
and write the file to disk with the :wq vi command. Note that the path to the executable is /opt/ibm/db2exc/V9.1/, the default installation path from the DB2.deb file. If your installation put the DB2 files elsewhere, be sure to adjust this script as required to find the /instance/ directory. Once the script has been saved, give the file the proper ownership and permissions:
chown root.root db2 chmod 755 db2
Now, to initialize this script so that it starts at boot time, install the Boot-Up Manager with this command:

sudo apt-get bum

After Synaptic downloads and installs the application file, you can run the program by clicking System -> Administration -> BootUp-Manager. Then click on the "Activate" checkbox beside the db2 entry. Click Apply and say "No" to the dialog that asks if you want to start the service now. Click Quit. The script will now run at your next boot and start up all DB2 instances. The only other way to resolve the problem is to manually start the instances after a reboot with db2start (as the instance owner) and db2admin start (as the DAS owner). Once DB2 Express-C has passed the validation process for Ubuntu 7.04, this instance start issue will disappear.
17. Installing DB2 Express-C on SimplyMEPIS 6.0
SimplyMEPIS 6.0 is based on the Ubuntu package management system and repositories, and is currently amongst the top 5 Linux distributions (according to distrowatch.com). In 6.0, SimplyMEPIS only supports the 32-bit architecture, but the new release, 6.5, supports the 64-bit architecture. Note that IBM does not officially support this distribution for DB2 9. Any problems you encounter using this distribution must be replicated on a supported distribution before IBM Support can help you resolve the issue. These installation notes are based on a single-partition 32-bit installation using the 2.6.15-26.386 kernel.

18.1.1. Post-installation notes
You can change which directory the Information Center uses as a temporary working directory. To do this, modify the DB2_ECLIPSEIC_DATA parameter in the db2ic.conf file found in the /opt/ibm/db2ic/V9/cfg directory. The temporary directory can be anywhere where the "bin" user can write to. Also in this file, you can change the port used to serve up the Information Center, in case you change your mind later on about the one you set up during the install. Just change the DB2_ECLIPSEIC_PORT parameter to the new number (though it must be a port under the maximum of 65535).
IBM DB2 Express-C for Linux HOWTO After making any changes, do a
/etc/init.d/db2icdv9 restart
to have your changes picked up by the Information Center (see below for more details on the db2icd daemon).
18.2. The Information Center daemon
The Information Center daemon is set up during installation and is used to launch the background process that runs the Information Center. The daemon script, named db2icdv9 is installed to the /etc/init.d/ directory, and start up symbolic links are added to the /etc/init.d/rc.X/ run-level folders. By default, the daemon is turned on for run-levels 3 and 5. You can modify these run-levels with any system init utility (chkconfig, ntsysv, or any of the X-based variants). There are 5 commands you can pass to the daemon: 1. start Starts the Information Center, using the configuration parameters set in the /opt/ibm/db2ic/V9/cfg/db2ic.conf file. 2. stop Halts a running Information Center. 3. restart A combination command that stops, then starts the daemon again. 4. reload Same as restart. 5. status Provides information on whether the Information Center is running, and if it is running, what the process IDs (PID) are.
18.3. Accessing an installed Information Center
There are three possible methods to access the DB2 Information Center: Via the publib.boulder.ibm.com Web site (this is the default option enabled during a DB2 installation) Via an intranet machine that is hosting the DB2 Information Center within your own organization Via an Information Center that is installed and set up on your local machine You can configure which access method will be used, either during the DB2 installation (only by specifying a "Custom" installation), or afterwards in the tools configuration panel (in the GUI tools), or from the command line. To make the change using the GUI tools (Control Center), open the Tools menu, select Tools Settings, and then the Documentation tab. On this panel, you can specify a host URL and port where an Information Center can be found. When putting a hostname in, do not add the "http://" prefix, or any subdirectories - just type in the hostname, and the internal code will do the rest. To change the access points from the command line, execute these commands:

db2set DB2_DOCHOST=host_URL db2set DB2_DOCPORT=host_port
where host_URL is the location hostname of the Information Center. This could be: mydocserver.myorg.net or localhost (for a local installation), and host_port is the port number where the Information Center was installed. By default, these DB2 environment settings are blank, which means that DB2 defaults to the 18. Installing the DB2 Information Center 33
IBM DB2 Express-C for Linux HOWTO http://publib.boulder.ibm.com/infocenter/db2help/ Web site. To invoke the DB2 Information Center from the GUI tools, click on the "?" icon in the toolbar, any of the "Help" hyperlinks or buttons, or by selecting any of the menu items in the "Help" menu. To invoke the DB2 Information Center from the command line, issue any of these commands: db2icdocs db2help db2ic db2cc -ic
19. Removing DB2 Express-C
Uninstalling DB2 Express-C is a bit more detailed than the installation process. Unfortunately, at this time, there is no GUI-based uninstaller utility like there is for installation. Follow the steps below to completely remove the DB2 Express-C software from your system. Since the database files are kept in the home directory of the instance user, you can keep the database files by skipping the removal of the instance owner (the last step). 1. Remove the Database Administration Server. Switch to the DAS owner (dasusr1 by default), either through the su - dasusr1 command or by logging in to the system directly as the DAS user, and issue the command db2admin stop. Then switch to the root user, and issue the /opt/ibm/db2/V9.1/instance/dasdrop command. 2. Remove the DB2 instance(s). Switch to the instance owner (db2inst1 by default), either through the su - db2inst1 command or by logging in to the system directly as the instance user, and issue the command db2stop. Then, as root, remove the instance by issuing the /opt/ibm/db2/V9.1/instance/db2idrop db2_instance command, where db2_instance refers to the actual instance name you want to drop (for example, /opt/ibm/db2/V9.1/instance/db2idrop db2inst1). If you want to verify which instances are present on your system, run the /opt/ibm/db2/V9.1/instance/db2ilist command. 3. Remove the software installation. As root, run the /opt/ibm/db2/V9.1/install/db2_deinstall -a command. This will remove the installation of DB2 from your system. 4. If no longer need the DB2 database files (which are kept in the home directory of the instance owner), you can remove the users that were created during the installation of DB2 Express-C by entering these commands as root:
userdel -r db2inst1 userdel -r dasusr1 userdel -r db2fenc1

(If you created different user names during installation, substitute as necessary.)

20. Using DB2

This section gives you the basic information you need to start working with DB2 Express-C on Linux. It includes instructions on issuing DB2 commands and SQL statements from the command line, as well as the commands you need to start the DB2 Control Center and the DB2 Information Center. For more information about general DB2 usage, please investigate the DB2 manuals and Information Center.

20.1. DB2 Control Center

To start the DB2 Control Center: 1. Ensure you are logged on to your Linux workstation using either the DB2 Administration Server user ID (dasusr1 by default) or the DB2 instance user ID (db2inst1 by default). If you use the su command to change to one of those users, ensure you include the -l parameter to initialize the proper user environment. 2. Start the X server, if it is not already started. 3. Start a terminal session, or type Alt + F2 to bring up the Linux "Run Command" dialog. 4. Type db2cc to start the DB2 Control Center. The left side of the DB2 Control Center provides an object based view of the database objects that you have catalogued, including DB2 instances and databases on other DB2 servers. One way to add, edit, or drop database objects is to right-click on the object to bring up a context menu.
20.2. DB2 Information Center
As mentioned above, the new DB2 Information Center is a revamped documentation display system that now encapsulates the task, concept and reference information required to properly run DB2. It can be installed locally, on an intranet server, or accessed directly from the ibm.com hosting service. To start the DB2 Information Center: 1. Ensure you are logged on to your Linux workstation using either the DB2 Administration Server user ID (dasusr1 by default) or the DB2 instance user ID (db2inst1 by default). If you use the su command to change to one of those users, ensure you include the -l parameter to initialize the proper user environment. 2. Ensure that the X server is started, as the Information Center requires a Web browser. 3. Issue one of the launch commands: db2icdocs db2help db2ic db2cc -ic Alternatively, you can launch the Information Center from within the Control Center (or any of the other GUI tools), by clicking an item under the "Help" menu, or by clicking on the "?" (question mark) icon. If you did not install the DB2 Information Center, or have not configured your tools to use a different server, 20. Using DB2 36
IBM DB2 Express-C for Linux HOWTO then these commands will launch a detected browser, and take you directly to the IBM hosted DB2 Information Center at http://publib.boulder.ibm.com/infocenter/db2help/.

23.9. TRANSLATION

Translation is considered a kind of modification, so you may distribute translations of the Document under the terms of section 4. Replacing Invariant Sections with translations requires special permission from their copyright holders, but you may include translations of some or all Invariant Sections in addition to the original versions of these Invariant Sections. You may include a translation of this License, and all the license notices in the Document, and any Warranty Disclaimers, provided that you also include the original English version of this License and the original versions of those notices and disclaimers. In case of a disagreement between the translation and the original version of this License or a notice or disclaimer, the original version will prevail. If a section in the Document is Entitled "Acknowledgements", "Dedications", or "History", the requirement (section 4) to Preserve its Title (section 1) will typically require changing the actual title.

23.10. TERMINATION

You may not copy, modify, sublicense, or distribute the Document except as expressly provided for under this License. Any other attempt to copy, modify, sublicense or distribute the Document is void, and will automatically terminate your rights under this License. However, parties who have received copies, or rights, from you under this License will not have their licenses terminated so long as such parties remain in full compliance.
23.11. FUTURE REVISIONS OF THIS LICENSE
The Free Software Foundation may publish new, revised versions of the GNU Free Documentation License from time to time. Such new versions will be similar in spirit to the present version, but may differ in detail to address new problems or concerns. See http://www.gnu.org/copyleft/. Each version of the License is given a distinguishing version number. If the Document specifies that a particular numbered version of this License "or any laterversion" applies to it, you have the option of following the terms and conditions either of that specified version or of any later version that has been published (not as a draft) by the Free Software Foundation. If the Document does not specify a version number of this License, you may choose any version ever published (not as a draft) by the Free Software Foundation.