Test the db2_all command to update the database configuration file for all partitions

with one command. Figure 2.24 shows an example of this.

And that’s it! In this case study you have reviewed some basic statements and com-

mands applicable to the DPF environment. You reviewed the db2stop and

db2start commands, determined and switched the active partition, and created a

database, a partition group, a buffer pool, a table space, a table with a distribution key,

and an index. You also used the db2_all command to update a database configura-

tion file parameter.

2.8 IBM BALANCED WAREHOUSE

A data warehouse system is composed of CPUs (or processors), memory, and disk. A balanced mix is the key to optimal performance. Unbalanced, ill-planned configurations will waste a lot of money if the system has

• Too many or too few servers • Too much or too little storage

• Too much or too little I/O capacity and bandwidth • Too much or too little memory

To help alleviate these issues, and ensure that DB2 customers build highly performing data warehouses, IBM has focused on a prescriptive and quality approach through the use of a proven balanced methodology for data warehousing. This is called the IBM Balanced Warehouse (BW) (formerly known as Balanced Configuration Unit or BCU), and is depicted in Figure 2.25.

[db2inst1@aries sqllib]$ db2 get db cfg for mydb1 | grep LOGFILSIZ Log file size (4KB) (LOGFILSIZ) = 1000

[db2inst1@aries sqllib]$ db2_all "db2 update db cfg for mydb1 using LOGFILSIZ 500" DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. aries.myacme.com: db2 update db cfg for mydb1 using LOGFILSIZ 500 completed ok DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. aries.myacme.com: db2 update db cfg for mydb1 using LOGFILSIZ 500 completed ok DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. saturn.myacme.com: db2 update db cfg for mydb1 using LOGFILSIZ 500 completed ok DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. saturn.myacme.com: db2 update db cfg for mydb1 using LOGFILSIZ 500 completed ok [db2inst1@aries sqllib]$ db2 get db cfg for mydb1 | grep LOGFILSIZ

Log file size (4KB) (LOGFILSIZ) = 500 [db2inst1@aries sqllib]$

The BW applies specifically to DB2 data warehouses. It is composed of a methodology for deployment of the database software, in addition to the IBM hardware and other software com- ponents that are required to build a data warehouse. These components are integrated and tested as a preconfigured building block for data warehousing systems. The prescriptive approach used by the BW eliminates the complexity of database warehouse design and implementation by making use of standardized, end-to-end stack tested designs, and best practices that increase the overall quality and manageability of the data warehouse.

This modular approach ensures that there is a balanced amount of disks, I/O bandwidth, process- ing power and memory to optimize the cost-effectiveness and throughput of the database. While you might start off with one BW, if you have a large database, you might also start off with sev- eral of these BW building blocks in a single system image. As the database grows, you can sim- ply add additional building blocks (BWs) to handle the increased data or users as shown in Figure 2.26.

Figure 2.25 IBM Balanced Warehouse

BW 1 BW 2 BW n

The main advantages of the BW are

• best-of-breed IBM components selected for optimum price/performance

• repeatable, scalable, consistent performance that can grow as business needs grow • a prescriptive, fully validated and tested, successful best practices design that reduces

the time and removes the risk of building a business intelligence solution

2.9 SUMMARY

This chapter provided an overview of the DB2 core concepts using a “big picture” approach. It introduced SQL statements and their classification as Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL) statements. XQuery with XPath and the FLWOR expression were also introduced.

DB2 commands were classified into two groups—system commands and CLP commands—and several examples were provided, such as the command to start an instance, db2start.

An interface is needed to issue SQL statements, XQuery statements, and commands to the DB2 engine. This interface is provided by using the DB2 tools available with the product. Two text-based interfaces were mentioned, the Command Line Processor (CLP) and the Command Window. The Control Center was noted as being one of the most important graphical adminis- tration tools, while the Data Server Administration Control was presented as the next generation of graphical administration tooling.

This chapter also introduced the concepts of instances, databases, table spaces, buffer pools, logs, tables, indexes, and other database objects in a single partition system. The different levels of configuration for the DB2 environment were presented, including environment variables, DB2 registry variables, and configuration parameters at the instance (dbm cfg) and database (db cfg) levels. DB2 has federation support for queries referencing tables residing in other databases in the DB2 family. The chapter also covered the Database Partitioning Feature (DPF) and the concepts of database partition, catalog partition, coordinator node, and distribution map on a multipartition system. The IBM Balanced Warehouse (BW) was also introduced.

Two case studies reviewed the single-partition and multipartition environments respectively, which should help you understand the topics discussed in the chapter.

2.10 REVIEW QUESTIONS