DB2 Database Objects __ Database

Database objects
SQL can be divided into three main categories:
1. DDL, data definition language for creating, modifying, and deleting database objects
2. DML, data manipulation language for selecting, inserting, updating, and deleting database records
3, DCL, Data Control language, to provide data object access control

Data type
1. Built-in data type
2. For custom data types
A) custom-differentiated types, which allow you to create new data types of your own semantics based on existing built-in data types.
b) Custom Structure type
c) Custom reference types

There are three types of tables:
1. Permanent table, also called base table, created using CREATE table
2. Temporal Declaration table, a temporary table is a feature introduced from DB2 V7 that enables app developers to create temporary tables that are used only for the duration of the program
3. Temporary derivative tables

A tablespace is a logical layer between a database and a table stored in that database, and DB2 supports 2 of the table space
1. System Management Table Space SMS
2, database management space DMS, this is a file system

Index
1. Ensure consistency of data values
2. Improve SQL query performance
Additional values can be added to the index that can be used by the optimizer to obtain non-key values. Getting a result set from an index rather than from a data page can also improve query performance
Index maintenance overhead can negatively affect INSERT, UPDATE, DELETE statement performance

DB2 uses the method of pre-write log, the modification is written to the log first, then the Physical database table implementation of these changes, in V8, the log file can reach 256G, in some events, the transaction will exceed the limit

Database Storage Management

The ability to efficiently store and quickly access large amounts of data is the responsibility of any DBA. The physical storage of data can affect query performance.
CPU and memory
Buffer pooling is a very important part of DB2 performance optimization, how to configure physical memory and virtual memory, and how to configure DB2 memory usage and buffer pool is very important.
Buffer pool--allocating memory to DB2
Tablespace-The logical layer between the physical table that holds the data and the database, like a book-Physical table, bookshelf-tablespace, bookcase-database
Containers--allocating storage space for table spaces
Excessive disk read and write results in IO performance, you may consider establishing multiple buffer pools. UNIX, create a database on the default one buffer pool IBMDEFAULTBP size is 1000 page 4KB size, and on other platforms is 250 page 4KB size