Tuning Application Performance Application Considerations" describes some techniques for improving database performance when designing your applications Environmental Considerations"describes some techniques for improving database performance when setting up your database environment 'System Catalog Statistics"describes how statistics about your data can be collected and used to ensure optimal performance. Understanding the SQL Compiler"describes what happens to an SQL statement when it is compiled using the SQL compiler "SQL Explain Facility"describes the Explain facility, which allows you to examine the choices the SQL compiler has made to access your data Tuning and Configuring Your System Operational Performance"describes an overview of how the database manager uses memory and other considerations that affect run-time performance Using the Governor"describes an introduction to the use of a governor to control some aspects of database management Scaling Your Configuration"describes some considerations and task ith increasing the size of yo syst Redistributing Data Across Database Partitions" discusses the tasks required in a partitioned database environment to redistribute data across partitions. Benchmark Testing"presents an overview of benchmark testing and how to perform benchmark testing "Configuring dB2" discusses the database manager and database configuration files and the values for the e database ma nager,database, and DAS configuration parameters Appendixes DB2 Registry and Environment Variables"describes profile registry values and environment variables Explain Tables and Definitions" describes the tables used by the DB2 Explain facility and how to create those tables "SQL Explain Tools"describes how to use the DB2 explain tools: db2expln and dynexpln db2exfmt- Explain Table Format Tool"describes how to use the DB2 explain tool to format the explain table data
Tuning Application Performance v ″Application Considerations″ describes some techniques for improving database performance when designing your applications. v ″Environmental Considerations″ describes some techniques for improving database performance when setting up your database environment. v ″System Catalog Statistics″ describes how statistics about your data can be collected and used to ensure optimal performance. v ″Understanding the SQL Compiler″ describes what happens to an SQL statement when it is compiled using the SQL compiler. v ″SQL Explain Facility″ describes the Explain facility, which allows you to examine the choices the SQL compiler has made to access your data. Tuning and Configuring Your System v ″Operational Performance″ describes an overview of how the database manager uses memory and other considerations that affect run-time performance. v ″Using the Governor″ describes an introduction to the use of a governor to control some aspects of database management. v ″Scaling Your Configuration″ describes some considerations and tasks associated with increasing the size of your database systems. v ″Redistributing Data Across Database Partitions″ discusses the tasks required in a partitioned database environment to redistribute data across partitions. v ″Benchmark Testing″ presents an overview of benchmark testing and how to perform benchmark testing. v ″Configuring DB2″ discusses the database manager and database configuration files and the values for the database manager, database, and DAS configuration parameters. Appendixes v ″DB2 Registry and Environment Variables″ describes profile registry values and environment variables. v ″Explain Tables and Definitions″ describes the tables used by the DB2 Explain facility and how to create those tables. v ″SQL Explain Tools″ describes how to use the DB2 explain tools: db2expln and dynexpln. v ″db2exfmt — Explain Table Format Tool″ describes how to use the DB2 explain tool to format the explain table data. About this book xi
art 1 Database concepts ght IBM Corp 1993-2002
Part 1. Database concepts © Copyright IBM Corp. 1993 - 2002 1
Chapter 1. Basic relational database concepts Database objects Instances An instance(sometimes called a database manager)is DB2@ code that manages data. It controls what can be done to the data, and manages system resources assigned to it. Each instance is a complete environment. It contains all the database partitions defined for a given parallel database system. An instance has its own databases(which other instances cannot access), and all its database partitions share the same system directories. It also has separate security from other instances on the same machine(system) Databases: A relational database presents data as a collection of tables. A table consists defined number of columns and any number of rows. Each database includes a set of system catalog tables that describe the logical and physical structure of the data, a configuration file containing the parameter values allocated for the database, and a recovery log with ongoing transactions and archivable transactions Database partition groups a database partition group is a set of one or more database partitions. When you want to create tables for the database, you first create the database partition group where the table spaces will be stored, then you create the table space where the tables will be stored In earlier versions of DB2, database partition groups were known as nodegroups Tables: A relational database presents data as a collection of tables. a table consists of data logically arranged in columns and rows. All database and table data is assigned to table spaces. The data in the table is logically related, and elationships can be defined between tables. Data can be viewed and manipulated based on mathematical principles and operations called relations Table data is accessed through Structured Query Language(SQL),a standardized language for defining and manipulating data in a relational c Copyright IBM Corp. 1993-2002 3
Chapter 1. Basic relational database concepts Database objects Instances: An instance (sometimes called a database manager) is DB2® code that manages data. It controls what can be done to the data, and manages system resources assigned to it. Each instance is a complete environment. It contains all the database partitions defined for a given parallel database system. An instance has its own databases (which other instances cannot access), and all its database partitions share the same system directories. It also has separate security from other instances on the same machine (system). Databases: A relational database presents data as a collection of tables. A table consists of a defined number of columns and any number of rows. Each database includes a set of system catalog tables that describe the logical and physical structure of the data, a configuration file containing the parameter values allocated for the database, and a recovery log with ongoing transactions and archivable transactions. Database partition groups: A database partition group is a set of one or more database partitions. When you want to create tables for the database, you first create the database partition group where the table spaces will be stored, then you create the table space where the tables will be stored. In earlier versions of DB2, database partition groups were known as nodegroups. Tables: A relational database presents data as a collection of tables. A table consists of data logically arranged in columns and rows. All database and table data is assigned to table spaces. The data in the table is logically related, and relationships can be defined between tables. Data can be viewed and manipulated based on mathematical principles and operations called relations. Table data is accessed through Structured Query Language (SQL), a standardized language for defining and manipulating data in a relational © Copyright IBM Corp. 1993 - 2002 3
database. A query is used in applications or by users to retrieve data from a database. The query uses SQL to create a statement in the form of SELECT <data name> FRoM <table name> Views A view is an efficient way of representing data without needing to maintain it. A view is not an actual table and requires no permanent storage. A"virtual table" is created and used A view can include all or some of the columns or rows contained in the tables on which it is based. For example, you can join a department table and an employee table in a view, so that you can list all employees in a particular department Figure 1 shows the relationship between tables and views Database Table a Column Table b 47 ABC 17 QRS 87 DJS KMP View a View AB CREATE VIEW A CREATE VIEW AB AS SELECT FROM TABLE A. TABLE B WHERE WHERE Figure 1. Relationship Between Tables and Views 4 Administration Guide: Planning
database. A query is used in applications or by users to retrieve data from a database. The query uses SQL to create a statement in the form of SELECT <data_name> FROM <table_name> Views: A view is an efficient way of representing data without needing to maintain it. A view is not an actual table and requires no permanent storage. A ″virtual table″ is created and used. A view can include all or some of the columns or rows contained in the tables on which it is based. For example, you can join a department table and an employee table in a view, so that you can list all employees in a particular department. Figure 1 shows the relationship between tables and views. Database Table A Table B View A View AB CREATE VIEW_A AS SELECT... FROM TABLE_A WHERE... CREATE VIEW_AB AS SELECT... FROM TABLE_A, TABLE_B WHERE... 47 Row Column ABC 17 QRS 85 FCP 81 MLI 93 CJP 87 DJS 19 KMP { { Figure 1. Relationship Between Tables and Views 4 Administration Guide: Planning
Indexes: An index is a set of keys, each pointing to rows in a table. For example, table A in Figure 2 has an index based on the employee numbers in the table. This key value provides a pointer to the rows in the table: employee number points to employee KMP. An index allows more efficient access to rows in a table by creating a direct path to the data through pointers The SQL optimizer automatically chooses the most efficient way to access data n tables. The optimizer takes indexes into consideration when determining the fastest access path to data. Unique indexes can be created to ensure uniqueness of the index key. An index key is a column or an ordered collection of columns on which an index is defined. Using a unique index will ensure that the value of each index key in the indexed column or columns is unique Figure 2 shows the relationship between an index and a table Database Index Table A Colum Dw 47ABC 81 MLI 19 KMP Figure 2. Relationship Between an index and a Table Figure 3 on page 6 illustrates the relationships among some database objects. It so shows that tables, indexes, and long data are stored in table space Chapter 1. Basic relational database concepts 5
Indexes: An index is a set of keys, each pointing to rows in a table. For example, table A in Figure 2 has an index based on the employee numbers in the table. This key value provides a pointer to the rows in the table: employee number 19 points to employee KMP. An index allows more efficient access to rows in a table by creating a direct path to the data through pointers. The SQL optimizer automatically chooses the most efficient way to access data in tables. The optimizer takes indexes into consideration when determining the fastest access path to data. Unique indexes can be created to ensure uniqueness of the index key. An index key is a column or an ordered collection of columns on which an index is defined. Using a unique index will ensure that the value of each index key in the indexed column or columns is unique. Figure 2 shows the relationship between an index and a table. Figure 3 on page 6 illustrates the relationships among some database objects. It also shows that tables, indexes, and long data are stored in table spaces. Database Index Table A A 17 47 Row Column ABC 19 17 QRS 47 85 FCP 81 81 MLI 85 93 CJP 87 87 DJS 93 19 KMP { { Figure 2. Relationship Between an Index and a Table Chapter 1. Basic relational database concepts 5