Creating and manipulating various DB2 objects
This section discusses the purpose and use of the following DB2 objects:
- Data placing objects
- Buffer pools
- Table spaces
- Application objects
- Identity columns
The database buffer pool area is a piece of memory used to cache a table's index and data pages as they are being read from disk to be scanned or modified. The buffer pool area helps improve database system performance by allowing data to be accessed from memory instead of from disk. Because memory access is much faster than disk access, the less often that DB2 needs to read from or write to a disk, the better the system will perform.
When a database is created, one default buffer pool is created for the database.
This buffer pool, IBMDEFAULTBP, will have a page size equal to the database
page size as specified at the database creation time. If no default page size
was specified at database creation, the default buffer pool will have a page
size of 4 KB. The default buffer pool size depends on the operating system. For
Windows, the default buffer pool is 250 pages or 1 MB; for UNIX, it's
1,000 pages or 4 MB. The default buffer pool cannot be dropped,
but its size can be changed using the
create bufferpool command has options to specify the following:
Buffer pool namespecifies the name of the buffer pool. The name cannot be used for any other buffer pools and cannot begin with the characters SYS or IBM.
immediatespecifies that the buffer pool will be created immediately if there is enough memory available on the system. If there is not enough reserved space in the database shared memory to allocate the new buffer pool, a warning is returned, and buffer pool creation will be DEFERRED, as described below.
deferredspecifies that the buffer pool will be created the next time that the database is stopped and restarted.
all dbpartitionnumsspecifies that the buffer pool will be created on all partitions in the database. This is the default if no database partition group is specified.
database partition groupspecifies the database partition groups on which the buffer pool will be created. The buffer pool will be created on all database partitions that are part of the specified database partition groups.
sizespecifies the size of the buffer pool and is defined in number of pages. In a partitioned database, this will be the default size for all database partitions where the buffer pool exists.
numblockpagesspecifies the number of pages to be created in the block-based area of the buffer pool. The actual value of numblockpages may differ from what was specified because the size must be a multiple of the block size. The block-based area of the buffer pool cannot be more than 98 percent of the total buffer pool size. Specifying a value of 0 will disable block I/O for the buffer pool.
blocksizespecifies the number of pages within a given block in the block-based area of the buffer pool. The block size must be between 2 and 256 pages; the default value is 32 pages.
pagesizespecifies the page size used for the buffer pool. The default page size is 4 KB or 4,096 bytes. The page size can be specified in bytes or kilobytes.
Once a page size and name for a buffer pool have been defined, they cannot be altered.
The following statement creates a buffer pool named BP1 with a size of 100 MB
(25,000 4-KB pages). Because the page size is not specified, the buffer pool
uses the default page size of 4 KB. Since the IMMEDIATE option is the default,
the buffer pool is allocated immediately and available for use as long as there
is enough memory available to fulfill the request:
create bufferpool BP1 size 25000.
The following statement creates a buffer pool named BP2 with a size of 200 MB
(25,000 8-KB pages). The buffer pool uses an 8-KB page size. Since the
immediate option is the default, the buffer pool is
allocated immediately and available for use as long as there is enough memory
available to fulfill the request:
bufferpool BP2 size 25000 pagesize 8 K.
The following statement creates a buffer pool named BP3 with a size of 4 GB
(1 million 4-KB pages). Because the page size is not specified, the buffer pool
uses the default page size of 4 KB; 128 pages of the buffer pool will be used in
the block-based area and blocks of the buffer pool will have 64 pages each.
deferred option is specified, the buffer
pool is not allocated until the database is stopped and restarted:
create bufferpool BP3 deferred size 1000000
NUMBLOCKPAGES 128 BLOCKSIZE 64.
A table space is a storage structure containing tables, indices, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system. Table spaces are stored in database partition groups.
A schema is a high-level qualifier for database objects created within a database. It is a collection of database objects such as tables, views, indices, or triggers. It provides a logical classification of database objects.
While you are organizing your data into tables, it may also be beneficial to
group tables and other related objects. This is done by defining a
schema using the
create schema command. Information
about the schema is kept in the system catalog tables of the database to which
you are connected. As other objects are created, they can be placed within this
A set of system schemas is created with every database and placed into the SYSCATSPACE table space:
- SYSIBM — The base system catalogs; direct access is not recommended.
- SYSCAT — SELECT authority granted to PUBLIC on this schema; catalog read-only views; recommended way to obtain catalog information.
- SYSSTAT — Updatable catalog views; influences the optimizer.
- SYSFUN — User-defined functions.
Use a schema to fully qualify a table or other object name:
You can have multiple tables with the same name, but different schema names. Thus, the table user1.staff is not the same as user2.staff. As a result, you can use schemas to create logical databases within a DB2 database.
To create a schema, use the
create schema command.
When you can create a schema, you can specify the owner of the schema using
authorization. If you do not, the
authorization ID that executed the
statement will be the owner of the schema. Privileges on the schema can also be
granted to users or groups at the same time.
Once a schema exists, the owner of the schema can grant CREATE_IN privilege on the schema to other users or groups.
The schema name for an object can be explicitly specified as follows:
create table DWAINE.table1 (c1 int, c2 int).
If the user DWAINE connects to the database SAMPLE and issues the
create table t2 (c1
int), the schema DWAINE is created (as long as IMPLICT_SCHEMA has not been revoked from
the user DWAINE), as well as the table in the database.
The ID used to connect to the database is known as the authorization ID.
When using DML commands (for example,
delete) on database
- The object schema can be explicitly specified on the object name, such as schema1.table1.
- The object schema can be specified using the
set current schemaor
set current sqlidcommands.
- If no object schema is explicitly specified, the schema will be set to the current authorization ID.
For example, if user DWAINE connects to the database SAMPLE and issues
select * from t2, DWAINE.T2
is selected from the table, if the table exists. Otherwise, an error is
To create a table in a database, you must first be connected to the database. You must also have SYSADM authority in the instance, or DBADM authority or createtab privilege in the database.
When creating a table, you can specify the following:
- Table name
- Column definitions
- Primary/foreign keys
- Table space for the data, index, and long objects
The listing below shows an example:
Listing 1. Example of table creation
CREATE TABLE EMPLOYEE_SALARY ( DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, EMPNO CHAR(6) NOT NULL, SALARY DECIMAL(9,2) NOT NULL WITH DEFAULT) INDEX IN indtbsp IN datatbsp
If a table is created without the
in clause, the table
data (and its indices and LOB data) is placed in the following order:
- In the IBMDEFAULTGROUP table space, if it exists, and if the page size is sufficient.
- In a user-created table space, which is of the smallest page size sufficient for the table.
- In USERSPACE1, if it exists and has a sufficient page size.
LONG IN clauses specify the table spaces in which the
regular table data, index, and long objects are to be stored. Note that this
only applies to DMS table spaces.
You can obtain table information with the commands in the table below.
Table 1. Obtaining table information
||List tables for the current user|
||List all tables defined in the database|
||List tables for the specified schema|
||Show the structure of the specified table|
For example, the command
department produces the following output.
Figure 3. Table information output
An index can:
- Be ascending or descending (the default, if not specified, is ascending).
- Be unique or non-unique (the default, if not specified, is non-unique).
- Be compound.
- Be used to enforce clustering.
- Be bi-directional; this is controlled by allow or disallow reverse scans.
- Include additional columns; this is only applicable for unique indices.
Following are a number of create unique statements that illustrate these options:
create unique index itemno on albums (itemno) desc create index clx1 on stock (shipdate) cluster allow reverse scans create unique index incidx on stock (itemno) include (itemname) create index item on stock (itemno) disallow reverse scans collect detailed statistics
An identity column is a numeric column in a table that causes DB2 to automatically generate a unique numeric value for each row inserted into the table. A table can have a maximum of one identity column. The values for the column can be generated by DB2 always or by default:
- If values are always generated, the DB2 database always generates them, and applications are not allowed to provide an explicit value.
- If values are generated by default, the values can be explicitly provided by an application; DB2 generates a value only if the application does not provide it. Thus, DB2 cannot guarantee the uniqueness of the values. This option is intended for data propagation, or loading and unloading of a table.
Let's look at an example. Given the table created using the following command.
create table inventory (partno INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 1), description CHAR(20) )
HINT: If you plan to load or import data into your table, use
GENERATED BY DEFAULT IDENTITY, which will make use
of the supplied values if they are provided; if the data is missing or
explicitly NULL, a value is generated.
Table 2. Identity column statement and values
|insert into inventory VALUES (DEFAULT,'door')||inserts 100,door|
|insert into inventory (description) VALUES ('hinge')||inserts 101,hinge|
|insert into inventory VALUES (200,'windor')||error|
|insert into inventory (description) VALUES ('frame')||inserts 102,frame|
Then the statement
SELECT * FROM inventory
100 door 101 hinge 102 frame.
Views are derived from one or more base tables, nicknames, or views, and can be used interchangeably with base tables when retrieving data. All views can be used just like tables for data retrieval. Whether a view can be used in an insert, update, or delete operation depends on its definition. Views are classified by the operations they allow:
A view can be created to limit access to sensitive data, while allowing more general access to other data. The data for a view is not stored separately from the table. In other words, a view uses no space in the database, other than its definition in the system catalogs. The creator of a view needs to have at least SELECT privilege on the base tables referenced in the view definition.
The information about all existing views is stored in:
create view statements show how views
create view DEPTSALARY AS SELECT DEPTNO, DEPTNAME, SUM(SALARY) AS TOTALS FROM PAYROLL GROUP BY DEPTNO,DEPTNAME create view EMPSALARY AS SELECT EMPNO, EMPNAME, SALARY FROM PAYROLL, PERSONNEL WHERE EMPNO=EMPNUMB
with check option specifies the constraint that
every row inserted or updated through a view must conform to the
definition of the view. A row that does not conform to the definition of the
view is a row that does not satisfy the search conditions of the view.
For example, consider this command:
create view emp_view2 (empno, empname, deptno) AS (SELECT id, name, dept FROM employee WHERE dept = 10)with check option
When this view is used to insert or update with new values, the
with check option restricts the input values for the
There are a number of types of constraints in DB2:
- Referential integrity constraints
- Unique constraints
- Check constraints
- Informational constraints
You cannot directly modify a constraint; you must instead drop it and create a new constraint with the characteristics you want. Each constraint is discussed below.
Referential integrity constraints are defined when a table is created or
subsequently using the
statement. The clauses that establish referential integrity are:
- The primary key clause
- The unique constraint clause
- The foreign key clause
- The references clause
create table artists (artno INT, ... primary key (artno) foreign key dept (workdept) references department on delete no action)
Let's take a look at the various referential integrity rules:
- There is an implicit rule to back out of an insert if a parent is not found.
- Restrict: A parent row is not deleted if dependent rows are found.
- Cascade: Deleting a row in a parent table automatically deletes any related rows in a dependent table.
- No Action (the default): Enforces presence of parent row for every child after all other referential constraints are applied.
- Set Null: Foreign key fields set to null; other columns left unchanged.
- Restrict: An update for a parent key will be rejected if a row in a dependent table matches the original values of key.
- No Action (the default): An update will be rejected for parent key if there is no matching row in the dependent table.
A unique constraint can be used as the primary key for a foreign key constraint, just like an explicitly declared primary key. This allows referential integrity constraints to be placed on different columns within the same table. A unique constraint forces the values in the column to be unique; the column cannot contain null values.
A check constraint is used to enforce data integrity at the table level. It forces values in the table to conform to the constraint. All subsequent inserts and updates must conform to the defined constraints on the table or the statement will fail. If existing rows in the table do not meet the constraint, the constraint cannot be defined. Constraint checking can be turned off to speed up the addition of a large amount of data, but the table will be placed in CHECK PENDING state.
Informational constraints are rules that can be used by the optimizer, but are not enforced during runtime. Because other constraints may result in the overhead for insert, update, or delete operations, informational constraints may be a better alternative if the application already verifies the data. Informational constraints can be:
- ENFORCED — The constraint is enforced by the database manager during normal operations such as insert, update, or delete.
- NOT ENFORCED: When used, DB2 may return wrong results when any data in the table violates the constraint.
- ENABLE QUERY OPTIMIZATION: The constraint can be used for query optimization under appropriate circumstances.
- DISABLE QUERY OPTIMIZATION: The constraint cannot be used for query optimization.
A trigger defines a set of actions that are activated, or triggered, by an action on a specified base table. The actions triggered may cause other changes to the database or raise an exception. A trigger can be fired before or after inserts, updates, or deletes. Triggers are used for:
- Validation, similar to constraints but more flexible.
- Conditioning, allowing new data to be modified or conditioned to a predefined value.
- Integrity, similar to referential integrity but more flexible.