Creating a database sets up all the system catalog tables that are needed by the database and allocates the database recovery log. The database configuration file is created, and the default values are set. The database manager will also bind the database utilities to the database.
The following database privileges are automatically granted to PUBLIC: CREATETAB, BINDADD, CONNECT, and IMPLICIT_SCHEMA. SELECT privilege on the system catalog views is also granted to PUBLIC.
The following command line processor command creates a database called personl, in the default location, with the associated comment "Personnel DB for BSchiefer Co".
create database personl
with "Personnel DB for BSchiefer Co"
The tasks carried out by the database manager when you create a database are discussed in the following sections:
For additional information related to the physical implementation of your database, see Chapter 3, Designing Your Physical Database.
If you wish to create a database in a different, possibly remote, database manager instance, see Using Multiple Instances of the Database Manager. This topic also provides an introduction to the command you need to use if you want to perform any instance-level administration against an instance other than your default instance, including remote instances.
| Note: | Refer to the Command Reference for information about the default database location and about specifying a different location with the CREATE DATABASE command. |
When a database is initially created, database partitions are created for all partitions specified in the db2nodes.cfg file. Other partitions can be added or removed with the ADD NODE and DROP NODE commands.
When a database is initially created, three table spaces are defined:
If you do not specify any table space parameters with the CREATE DATABASE command, the database manager will create these table spaces using system managed storage (SMS) directory containers. These directory containers will be created in the subdirectory created for the database (see Database Physical Directories). The extent size for these table spaces will be set to the default.
If you do not want to use the default definition for these table spaces, you may specify their characteristics on the CREATE DATABASE command. For example, the following command could be used to create your database on OS/2:
CREATE DATABASE PERSONL
CATALOG TABLESPACE
MANAGED BY SYSTEM USING ('d:\pcatalog','e:\pcatalog')
EXTENTSIZE 16 PREFETCHSIZE 32
USER TABLESPACE
MANAGED BY DATABASE USING (FILE'd:\db2data\personl' 5000,
FILE'd:\db2data\personl' 5000)
EXTENTSIZE 32 PREFETCHSIZE 64
TEMPORARY TABLESPACE
MANAGED BY SYSTEM USING ('f:\db2temp\personl')
WITH "Personnel DB for BSchiefer Co"
In this example, the definition for each of the initial table spaces is explicitly provided. You only need to specify the table space definitions for those table spaces for which you do not want to use the default definition.
The coding of the MANAGED BY phrase on the CREATE DATABASE command follows the same format as the MANAGED BY phrase on the CREATE TABLESPACE command. For additional examples, see Creating a Table Space.
Before creating your database, see Designing and Choosing Table Spaces.
A set of system catalog tables is created and maintained for each database. These tables contain information about the definitions of the database objects (for example, tables, views, indexes, and packages), and security information about the type of access users have to these objects. These tables are stored in the SYSCATSPACE table space.
These tables are updated during the operation of a database; for example, when a table is created. You cannot explicitly create or drop these tables, but you can query and view their content. When the database is created, in addition to the system catalog table objects, the following database objects are defined in the system catalog:
After your database has been created, you may wish to limit the access to the system catalog views, as described in Securing the System Catalog Views.
Three directories are used when establishing or setting up a new database.
A local database directory file exists in each path (or drive on other platforms) in which a database has been defined. This directory contains one entry for each database accessible from that location. Each entry contains:
To see the contents of this file for a particular database, issue the following command, where location specifies the location of the database:
LIST DATABASE DIRECTORY ON location
A system database directory file exists for each instance of the database manager, and contains one entry for each database that has been cataloged for this instance. Databases are implicitly cataloged when the CREATE DATABASE command is issued and can also be explicitly cataloged with the CATALOG DATABASE command. For information about cataloging databases, see Cataloging a Database.
For each database created, an entry is added to the directory containing the following information:
To see the contents of this file, issue the LIST DATABASE DIRECTORY command without specifying the location of the database directory file.
In a partitioned database environment, you must ensure that all database partitions always access the same system database directory file, sqldbdir, in the sqldbdir subdirectory of the home directory for the instance. Unpredictable errors can occur if either the system database directory or the system intention file sqldbins in the same sqldbdir subdirectory are symbolic links to another file that is on a shared file system. These files are described in Enabling Data Partitioning.
The database manager creates the node directory when the first database partition is cataloged. To catalog a database partition, use the CATALOG NODE command. To list the contents of the local node directory, use the LIST NODE DIRECTORY command. The node directory is created and maintained on each database client. The directory contains an entry for each remote workstation having one or more databases that the client can access. The DB2 client uses the communication end point information in the node directory whenever a database connection or instance attachment is requested.
The entries in the directory also contain information on the type of communication protocol to be used to communicate from the client to the remote database partition. Cataloging a local database partition creates an alias for an instance that resides on the same machine. A local node should be cataloged when there is more than one instance on the same workstation to be accessed from the user's client.
DCE is an Open Systems Foundation** (OSF**) architecture that provides tools and services to support the creation, use, and maintenance of applications in a distributed heterogeneous computing environment. It is a layer between the operating system, the network, and a distributed application that allows client applications to access remote servers.
With local directories, the physical location of the target database is individually stored on each client workstation in the database directory and node directory. The database administrator can therefore spend a large amount of time updating and changing these directories. The DCE directory services provide a central directory alternative to the local directories. It allows information about a database or a database manager instance to be recorded once in a central location, and any changes or updates to be made at that one location.
DCE is not a prerequisite for running DB2, but if you are operating in a DCE environment, see Appendix F, Using Distributed Computing Environment (DCE) Directory Services for more information.
Lightweight Directory Access Protocol (LDAP) is an industry standard access method to directory services. Each instance of the database server will publish its existence and provide the protocol communication information in the LDAP directory. When a client connects to the database server, the communication information for the server can be retrieved from the LDAP directory. Each client is no longer required to store the server connection information by cataloging a node entry locally on each machine. Instead, when a database is created, the database publishes its existence using the LDAP directory. Client applications search the LDAP directory for the database location and the information required to connect to the database.
LDAP is not a prerequisite for running DB2, but if you are operating in an LDAP environment, see Appendix T, Lightweight Directory Access Protocol (LDAP) Directory Services for more information.
You create a nodegroup with the CREATE NODEGROUP statement. This statement specifies the set of nodes on which the table space containers and table data are to reside. This statement also:
Assume that you want to load some tables on a subset of the database partitions in your database. You would use the following command to create a nodegroup of two nodes (1 and 2) in a database consisting of at least 3 (0 to 2) nodes:
CREATE NODEGROUP mixng12 ON NODES (1,2)
For more information about creating nodegroups, refer to the SQL Reference manual.
The CREATE DATABASE command or sqlecrea() API also create the default system nodegroups, IBMDEFAULTGROUP, IBMCATGROUP, and IBMTEMPGROUP. (See Designing and Choosing Table Spaces for information.)
A database recovery log keeps a record of all changes made to a database, including the addition of new tables or updates to existing ones. This log is made up of a number of log extents, each contained in a separate file called a log file.
The database recovery log can be used to ensure that a failure (for example, a system power outage or application error) does not leave the database in an inconsistent state. In case of a failure, the changes already made but not committed are rolled back, and all committed transactions, which may not have been physically written to disk, are redone. These actions ensure the integrity of the database.
For more information, see Chapter 9, Recovering a Database.
When a database is created, the database manager attempts to bind the utilities in db2ubind.lst to the database. This file is stored in the bnd subdirectory of your sqllib directory.
Binding a utility creates a package, which is an object that includes all the information needed to process specific SQL statements from a single source file.
| Note: | If you wish to use these utilities from a client, you must bind them explicitly. Refer to the Quick Beginnings manual appropriate to your platform for information. |
If for some reason you need to bind or rebind the utilities to a database, issue the following commands using the command line processor:
connect to sample bind @db2ubind.lst
| Note: | You must be in the directory where these files reside to create the packages in the sample database. The bind files are found in the BND subdirectory of the SQLLIB directory. In this example, sample is the name of the database. |
When you create a new database, it is automatically cataloged in the system database directory file. You may also use the CATALOG DATABASE command to explicitly catalog a database in the system database directory file. The CATALOG DATABASE command allows you to catalog a database with a different alias name, or to catalog a database entry that was previously deleted using the UNCATALOG DATABASE command.
The following command line processor command catalogs the personl database as humanres:
catalog database personl as humanres
with "Human Resources Database"
Here, the system database directory entry will have humanres as the database alias, which is different from the database name (personl).
You can also catalog a database on an instance other than the default. In the following example, connections to database B are to INSTANCE_C.
catalog database b as b at node instance_c
| Note: | The CATALOG DATABASE command is also used on client nodes to catalog databases that reside on database server machines. For more information, refer to the Quick Beginnings manual appropriate to your platform. |
For information on the Distributed Computing Environment (DCE) cell directory, see DCE Directory Services and Appendix F, Using Distributed Computing Environment (DCE) Directory Services.
| Note: | To improve performance, you may cache directory files, including the database directory, in memory. (Refer to "Directory Cache Support" in the Administration Guide, Performance for information about enabling directory caching.) When directory caching is enabled, a change made to a directory (for example, using a CATALOG DATABASE or UNCATALOG DATABASE command) by another application may not become effective until your application is restarted. To refresh the directory cache used by a command line processor session, issue a db2 terminate command. |
In addition to the application level cache, a database manager level cache is also used for internal, database manager look-up. To refresh this "shared" cache, issue the db2stop and db2start commands.
For more information about directory caching, refer to "Directory Cache Support" in the Administration Guide, Performance.
Creating a table space within a database assigns containers to the table space and records its definitions and attributes in the database system catalog. You can then create tables within this table space.
See Designing and Choosing Table Spaces for design information on table spaces.
The syntax of the CREATE TABLESPACE statement is discussed in detail in the SQL Reference manual. For information on SMS and DMS table spaces, see Designing and Choosing Table Spaces.
The following SQL statement creates an SMS table space on OS/2 or Windows NT using three directories on three separate drives:
CREATE TABLESPACE RESOURCE
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
The following SQL statement creates a DMS table space on OS/2 using two file containers each with 5,000 pages:
CREATE TABLESPACE RESOURCE
MANAGED BY DATABASE
USING (FILE'd:\db2data\acc_tbsp' 5000,
FILE'e:\db2data\acc_tbsp' 5000)
In the above two examples, explicit names have been provided for the containers. You may also specify relative container names, in which case, the container will be created in the subdirectory created for the database (see Database Physical Directories).
In addition, if part of the path name specified does not exist, the database manager will create it. If a subdirectory is created by the database manager, it may also be deleted by the database manager when the table space is dropped.
The assumption in the above examples is that the table spaces are not associated with a specific nodegroup. The default nodegroup IBMDEFAULTGROUP is used when the following parameter is not specified in the statement:
IN nodegroup
The following SQL statement creates a DMS table space on a UNIX-based system using three logical volumes of 10 000 pages each, and specifies their I/O characteristics:
CREATE TABLESPACE RESOURCE
MANAGED BY DATABASE
USING (DEVICE '/dev/rdblv6' 10000,
DEVICE '/dev/rdblv7' 10000,
DEVICE '/dev/rdblv8' 10000)
OVERHEAD 24.1
TRANSFERRATE 0.9
The UNIX devices mentioned in this SQL statement must already exist and be able to be written to by the instance owner and the SYSADM group.
The following example creates a DMS table space on a nodegroup called ODDNODEGROUP in a UNIX partitioned database. ODDNODEGROUP must be previously created with a CREATE NODEGROUP statement. In this case, the ODDNODEGROUP nodegroup is assumed to be made up of database partitions numbered 1, 3, and 5. On all database partitions, use the device /dev/hdisk0 for 10 000 4 KB pages. In addition, declare a device for each database partition of 40 000 4 KB pages.
CREATE TABLESPACE PLANS
MANAGED BY DATABASE
USING (DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n1hd01' 40000) ON NODE 1
(DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n3hd03' 40000) ON NODE 3
(DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n5hd05' 40000) ON NODE 5
UNIX devices are classified into two categories: character serial devices and block-structured devices. For all file-system devices, it is normal to have a corresponding character serial device (or raw device) for each block device (or cooked device). The block-structured devices are typically designated by names similar to "hd0" or "fd0". The character serial devices are typically designated by names similar to "rhd0", "rfd0", or "rmt0". These character serial devices have faster access than block devices. The character serial device names should be used on the CREATE TABLESPACE command and not block device names.
The overhead and transfer rate help to determine the best access path to use when the SQL statement is compiled. For information on the OVERHEAD and TRANSFERRATE parameters, refer to "Tuning Application Performance" in the Administration Guide, Performance.
DB2 can greatly improve the performance of sequential I/O using the sequential prefetch facility, which uses parallel I/O. Refer to "Understanding Sequential Prefetching" in the Administration Guide, Performance for details on this facility.
You also have the ability to create a table space that uses a page size larger than the default 4 KB size. The following SQL statement creates an SMS table space on a UNIX-based system with an 8 KB page size.
CREATE TABLESPACE SMS8K
PAGESIZE 8192
MANAGED BY SYSTEM
USING ('FSMS_8K_1')
BUFFERPOOL BUFFPOOL8K
Notice that the associated buffer pool must also have the same 8 KB page size.
The created table space cannot be used until the buffer pool it references is activated.
The ALTER TABLESPACE SQL statement can be used to add a container to a DMS table space and modify the PREFETCHSIZE, OVERHEAD, and TRANSFERRATE settings for a table space. The transaction issuing the table space statement should be committed as soon as possible, to prevent system catalog contention.
| Note: | The PREFETCHSIZE should be a multiple of the EXTENTSIZE. For example if the EXTENTSIZE is 10, the PREFETCHSIZE should be 20 or 30. For more information, refer to "Understanding Sequential Prefetching" in the Administration Guide, Performance. |
By placing a table space in a multiple database partition nodegroup, all of the tables within the table space are divided or partitioned across each database partition in the nodegroup. The table space is created into a nodegroup. Once in a nodegroup, the table space must remain there; It cannot be changed to another nodegroup. The CREATE TABLESPACE statement is used to associate a table space with a nodegroup.
DB2 Universal Database supports direct disk access (raw I/O). This allows you to attach a direct disk access (raw) device to any DB2 Universal Database system. (The only exception is the Linux platform.) The following list demonstrates the physical and logical methods for identifying this type of device:
where N represents one of the physical drives in the system. For example, N could be replaced by 0, 1, 2, or any other positive integer.
where N: represents a logical drive letter in the system. For example, N: could be replaced by E: or any other drive letter on the system.
For example:
| Note: | You must have Windows NT Version 4.0 with Service Pack 3 installed to be able to write logs to a device. |
While organizing your data into tables, it may also be beneficial to group tables (and other related objects) together. This is done by defining a schema through the use of the CREATE SCHEMA statement. 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 schema.
The syntax of the CREATE SCHEMA statement is described in detail in the SQL Reference manual. The new schema name cannot already exist in the system catalogs and it cannot begin with "SYS".
If a user has SYSADM or DBADM authority, then the user can create a schema with any valid name. When a database is created, IMPLICIT_SCHEMA authority is granted to PUBLIC (that is, to all users).
The definer of any objects created as part of the CREATE SCHEMA statement is the schema owner. This owner can GRANT and REVOKE schema privileges to other users.
The following is an example of a CREATE SCHEMA statement that creates a schema for an individual user with the authorization ID "joe":
CREATE SCHEMA joeschma AUTHORIZATION joe
This statement must be issued by a user with DBADM authority.
Schemas may also be implicitly created when a user has IMPLICIT_SCHEMA authority. With this authority, users implicitly create a schema whenever they create an object with a schema name that does not already exist.
If users do not have IMPLICIT_SCHEMA authority, the only schema they can create is one that has the same name as their own authorization ID.
You may wish to establish a default schema for use by unqualified object references in dynamic SQL statements issued from within a specific DB2 connection. This is done by setting the special register CURRENT SCHEMA to the schema you wish to use as the default. Any user can set this special register: No authorization is required.
The syntax of the SET SCHEMA statement is described in detail in the SQL Reference manual.
The following is an example of how to set the CURRENT SCHEMA special register:
SET CURRENT SCHEMA = 'SCHEMA01'
This statement can be used from within an application program or issued interactively. Once set, the value of the CURRENT SCHEMA special register is used as the qualifier (schema) for unqualified object references in dynamic SQL statements, with the exception of the CREATE SCHEMA statement where an unqualified reference to a database object exists.
The initial value of the CURRENT SCHEMA special register is equal to the authorization ID of the current session user.
After you determine how to organize your data into tables, the next step is to create those tables, by using the CREATE TABLE statement. The table descriptions are stored in the system catalog of the database to which you are connected.
The syntax of the CREATE TABLE statement is described in detail in the SQL Reference. For information about naming tables, columns, and other database objects, see Appendix D, Naming Rules.
The CREATE TABLE statement gives the table a name, which is a qualified or unqualified identifier, and a definition for each of its columns. You can store each table in a separate table space, so that a table space will contain only one table. If a table will be dropped and created often, it is more efficient to store it in a separate table space and then drop the table space instead of the table. You can also store many tables within a single table space. In a partitioned database environment, the table space chosen also defines the nodegroup and the database partitions on which table data is stored.
The table does not contain any data at first. To add rows of data to it, use one of the following:
Details concerning the movement of data into and out of tables is presented in Data Movement Utilities Guide and Reference.
It is possible to add data into the table without logging the change. This is done using the NOT LOGGED INITIALLY parameter on the CREATE TABLE statement. Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE operation in the same unit of work in which the table is created are not logged. Logging begins in subsequent units of work.
A table consists of one or more column definitions. A maximum of 500 columns can be defined for a table. Columns represent the attributes of an entity. The values in any column are all the same type of information. Refer to the SQL Reference for more information.
| Note: | The maximum of 500 columns is true when using a 4 KB page size. The maximum is 1012 columns when using an 8 KB, 16 KB, or 32 KB page size. |
A column definition includes a column name, data type, and any necessary null attribute, or default value (optionally chosen by the user).
The column name describes the information contained in the column and should be something that will be easily recognizable. It must be unique within the table; however, the same name can be used in other tables. See Object Names for information about naming rules.
The data type of a column indicates the length of the values in it and the kind of data that is valid for it. The database manager uses character string, numeric, date, time and large object data types. Graphic string data types are only available for database environments using multi-byte character sets. In addition, columns can be defined with user-defined distinct types, which are discussed in Creating a User-Defined Type (UDT).
The default attribute specification indicates what value is to be used if no value is provided. The default value can be specified, or a system-defined default value used. Default values may be specified for columns with, and without, the null attribute specification.
The null attribute specification indicates whether or not a column can contain null values.
The following is an example of a CREATE TABLE statement that creates the EMPLOYEE table in the RESOURCE table space. This table is defined in the sample database:
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL WITH DEFAULT,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10M) NOT NULL)
IN RESOURCE
When creating a table, you can choose to have the columns of the table based on the attributes of a structured type. Such a table is called a "typed table".
A typed table can be defined to inherit some of its columns from another typed table. Such a table is called a "subtable", and the table from which it inherits is called its "supertable". The combination of a typed table and all its subtables is called a "table hierarchy". The topmost table in the table hierarchy (the one with no supertable) is called the "root table" of the hierarchy.
The following sections build on the previous example to cover other options you should consider:
You can also create a table that is defined based on the result of a query. This type of table is called a summary table. For more information, see Creating a Summary Table.
Before creating a table that contains large object columns, you need to make the following decisions:
If you do not want to log these changes, you must turn logging off by specifying the NOT LOGGED clause when you create the table. For example:
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL WITH DEFAULT,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10M) NOT NULL NOT LOGGED)
IN RESOURCE
If the LOB column is larger than 1 GB, logging must be turned off. (As a rule of thumb, you may not want to log LOB columns larger than 10 MB.) As with other options specified on a column definition, the only way to change the logging option is to re-create the table.
Even if you choose not to log changes, LOB columns are shadowed to allow changes to be rolled back, whether the roll back is the result of a system generated error, or an application request. Shadowing is a recovery technique where current storage page contents are never overwritten. That is, old, unmodified pages are kept as "shadow" copies. These copies are discarded when they are no longer needed to support a transaction rollback.
| Note: | When recovering a database using the RESTORE and ROLLFORWARD commands, LOB data that was "NOT LOGGED"and was written since the last backup will be replaced by binary zeros. |
You can make the LOB column as small as possible using the COMPACT clause on the CREATE TABLE statement. For example:
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL WITH DEFAULT,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10M) NOT NULL NOT LOGGED COMPACT)
IN RESOURCE
There is a performance cost when appending to a table with a compact LOB column, particularly if the size of LOB values are increased (because of storage adjustments that must be made).
On platforms such as OS/2 where sparse file allocation is not supported and where LOBs are placed in SMS table spaces, consider using the COMPACT clause. Sparse file allocation has to do with how physical disk space is used by an operating system. An operating system that supports sparse file allocation does not use as much physical disk space to store LOBs as compared to an operating system not supporting sparse file allocation. The COMPACT option allows for even greater physical disk space "savings" regardless of the support of sparse file allocation. Because you can get some physical disk space savings when using COMPACT, you should consider using COMPACT if your operating system does not support sparse file allocation.
| Note: | DB2 system catalogs use LOB columns and may take up more space than in previous versions. |
There are large object (LOB) columns in the catalog tables. LOB data is not kept in the buffer pool with other data but is read from disk each time it is needed. Reading from disk slows down the performance of DB2 where the LOB columns of the catalogs are involved. Since a file system usually has its own place for storing (or caching) data, using a SMS table space, or a DMS table space built on file containers, make avoidance of I/O possible when the LOB has previously been referenced.
This section discusses how to define constraints:
For more information on constraints, see Planning for Constraint Enforcement and refer to the SQL Reference.
Unique constraints ensure that every value in the specified key is unique. A table can have any number of unique constraints, with at most one unique constraint defined as a primary key.
You define a unique constraint with the UNIQUE clause in the CREATE TABLE or ALTER TABLE statements. The unique key can consist of more than one column. More than one unique constraint is allowed on a table. However, a unique constraint may not be defined on a subtable.
Once established, the unique constraint is enforced automatically by the database manager when an INSERT or UPDATE statement modifies the data in the table. The unique constraint is enforced through a unique index.
When a unique constraint is defined in an ALTER TABLE statement and an index exists on the same set of columns of that unique key, that index becomes the unique index and is used by the constraint.
You can take any one unique constraint and use it as the primary key. The primary key can be used as the parent key in a referential constraint (along with other unique constraints). There can be only one primary key per table. You define a primary key with the PRIMARY KEY clause in the CREATE TABLE or ALTER TABLE statement. The primary key can consist of more than one column.
A primary index forces the value of the primary key to be unique. When a table is created with a primary key, the database manager creates a primary index on that key.
Some performance tips for indexes used as unique constraints include:
Defining Referential Constraints:
Referential integrity is imposed by adding referential constraints to table and column definitions. Referential constraints are established with the "FOREIGN KEY Clause", and the "REFERENCES Clause" in the CREATE TABLE or ALTER TABLE statements. A referential constraint cannot be associated with a typed table.
The identification of foreign keys enforces constraints on the values within the rows of a table or between the rows of two tables. The database manager checks the constraints specified in a table definition and maintains the relationships accordingly. The goal is to maintain integrity whenever one database object references another.
For example, primary and foreign keys each have a department number column. For the EMPLOYEE table, the column name is WORKDEPT, and for the DEPARTMENT table, the name is DEPTNO. The relationship between these two tables is defined by the following constraints:
The SQL statement defining the parent table, DEPARTMENT, is:
CREATE TABLE DEPARTMENT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(29) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16),
PRIMARY KEY (DEPTNO))
IN RESOURCE
The SQL statement defining the dependent table, EMPLOYEE, is:
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10m) NOT NULL,
FOREIGN KEY DEPT (WORKDEPT)
REFERENCES DEPARTMENT ON DELETE NO ACTION)
IN RESOURCE
By specifying the DEPTNO column as the primary key of the DEPARTMENT table and WORKDEPT as the foreign key of the EMPLOYEE table, you are defining a referential constraint on the WORKDEPT values. This constraint enforces referential integrity between the values of the two tables. In this case, any employees that are added to the EMPLOYEE table must have a department number that can be found in the DEPARTMENT table.
The delete rule for the referential constraint in the employee table is NO ACTION, which means that a department cannot be deleted from the DEPARTMENT table if there are any employees in that department.
Although the previous examples use the CREATE TABLE statement to add a referential constraint, the ALTER TABLE statement can also be used. See Modifying a Table in Both Structure and Content.
Another example: The same table definitions are used as those in the previous example. Also, the DEPARTMENT table is created before the EMPLOYEE table. Each department has a manager, and that manager is listed in the EMPLOYEE table. MGRNO of the DEPARTMENT table is actually a foreign key of the EMPLOYEE table. Because of this referential cycle, this constraint poses a slight problem. You could add a foreign key later (see Adding Primary and Foreign Keys). You could also use the CREATE SCHEMA statement to create both the EMPLOYEE and DEPARTMENT tables at the same time (see the example in the SQL Reference).
A foreign key references a primary key or a unique key in the same or another table. A foreign key assignment indicates that referential integrity is to be maintained according to the specified referential constraints. You define a foreign key with the FOREIGN KEY clause in the CREATE TABLE or ALTER TABLE statement.
The number of columns in the foreign key must be equal to the number of columns in the corresponding primary or unique constraint (called a parent key) of the parent table. In addition, corresponding parts of the key column definitions must have the same data types and lengths. The foreign key can be assigned a constraint name. If you do not assign a name, one is automatically assigned. For ease of use, it is recommended that you assign a constraint name and do not use the system-generated name.
The value of a composite foreign key matches the value of a parent key if the value of each column of the foreign key is equal to the value of the corresponding column of the parent key. A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.
The following rules apply to foreign key definitions:
The REFERENCES clause identifies the parent table in a relationship, and defines the necessary constraints. You can include it in a column definition or as a separate clause accompanying the FOREIGN KEY clause, in either the CREATE TABLE or ALTER TABLE statements.
If you specify the REFERENCES clause as a column constraint, an implicit column list is composed of the column name or names that are listed. Remember that multiple columns can have separate REFERENCES clauses, and that a single column can have more than one.
Included in the REFERENCES clause is the delete rule. In our example, the ON DELETE NO ACTION rule is used, which states that no department can be deleted if there are employees assigned to it. Other delete rules include ON DELETE CASCADE, ON DELETE SET NULL, and ON DELETE RESTRICT. See DELETE Rules.
Implications for Utility Operations:
The LOAD utility will turn off constraint checking for self-referencing and dependent tables, placing these tables into check pending state. After the LOAD utility has completed, you will need to turn on the constraint checking for all tables for which it was turned off. For example, if the DEPARTMENT and EMPLOYEE tables are the only tables that have been placed in check pending state, you can execute the following command:
SET INTEGRITY FOR DEPARTMENT, EMPLOYEE IMMEDIATE CHECKED
The IMPORT utility is affected by referential constraints in the following ways:
To use these functions, first drop all foreign keys in which the table is a parent. When the import is complete, re-create the foreign keys with the ALTER TABLE statement.
Defining a Table Check Constraint:
A table check constraint specifies a search condition that is enforced for each row of the table on which the table check constraint is defined. You create a table check constraint on a table by associating a check-constraint definition with the table when the table is created or altered. This constraint is automatically activated when an INSERT or UPDATE statement modifies the data in the table. A table check constraint has no effect on a DELETE or SELECT statement. A check constraint can be associated with a typed table.
A constraint name cannot be the same as any other constraint specified within the same CREATE TABLE statement. If you do not specify a constraint name, the system generates an 18-character unique identifier for the constraint.
A table check constraint is used to enforce data integrity rules not covered by key uniqueness or a referential integrity constraint. In some cases, a table check constraint can be used to implement domain checking. The following constraint issued on the CREATE TABLE statement ensures that the start date for every activity is not after the end date for the same activity:
CREATE TABLE EMP_ACT
(EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DECIMAL(5,2),
EMSTDATE DATE,
EMENDATE DATE,
CONSTRAINT ACTDATES CHECK(EMSTDATE <= EMENDATE) )
IN RESOURCE
Although the previous example uses the CREATE TABLE statement to add a table check constraint, the ALTER TABLE statement can also be used. See Modifying a Table in Both Structure and Content.
You can create a typed table using a variant of the CREATE TABLE statement. You can also create a hierarchy of typed tables that is based on a hierarchy of structured types. The following example illustrates creation of a table hierarchy based on the type hierarchy described in Creating a User-Defined Structured Type:
CREATE TABLE Department OF Department_t
(REF IS Oid USER GENERATED);
CREATE TABLE Person OF Person_t
(REF IS Oid USER GENERATED);
CREATE TABLE Employee OF Employee_t UNDER Person
INHERIT SELECT PRIVILEGES
(Dept WITH OPTIONS SCOPE Department);
CREATE TABLE Student OF Student_t UNDER Person
INHERIT SELECT PRIVILEGES;
CREATE TABLE Manager OF Manager_t UNDER Employee
INHERIT SELECT PRIVILEGES;
CREATE TABLE Architect OF Architect_t UNDER Employee
INHERIT SELECT PRIVILEGES;
The first typed table created above is Department. This table is defined to be OF type Department_t, so it will hold instances of that type. This means that it will have a column corresponding to each attribute of the structured type Department_t. Because typed tables contain objects that can be referenced by other objects, every typed table must have an "object identifier" (OID) column as its first column. In this example, the type of the OID column will be REF(Department_t), and its column name (Oid) is given in the REF IS...USER GENERATED clause. The USER GENERATED part of this clause indicates that the initial value for the OID column of each newly inserted row will be provided by the user when inserting a row; once inserted, the OID column cannot be updated.
The next typed table above, Person, is of type Person_t. The type Person_t is the root of a type hierarchy, so we need to create a corresponding "table hierarchy" if we want to store instances of type Person_t and its subtypes. Thus, after creating the table Person, we create two "subtables" of the Person table, Employee and Student, and also two subtables of the Employee table, Manager and Architect. Just as a subtype inherits the attributes of its supertype, a subtable inherits the columns of its supertable -- including the OID column. (Note: A subtable must reside in the same schema as its supertable.) Rows in the Employee subtable, for example, will therefore have a total of six columns: Oid, Name, Age, SerialNum, Salary, and Dept.
The INHERIT SELECT PRIVILEGES clause specifies that the subtable being defined, such as Employee, should (at least initially) be readable by the same users and groups as the "supertable", such as Person, UNDER which it is created. Any user or group holding a SELECT privilege on the supertable will be granted SELECT privilege on the newly created subtable, with the subtable definer being the grantor of this privilege.
| Note: | Privileges may be granted and revoked independently at every level of a table hierarchy. Thus, the inherited SELECT privileges on a subtable may be revoked after the subtable has been created if the definer of the subtable does not wish for them to remain granted. While doing so does not prevent a user with SELECT privilege on the supertable from seeing those columns of the subtable's rows, it does prevent them from seeing the additional columns that appear only at the level of the subtable because a user can only operate directly on a subtable if they hold the necessary privilege on that subtable. |
The WITH OPTIONS SCOPE clause in the CREATE statement for the Employee table declares that the Dept column of this table has a "scope" of Department. This means that the reference values in this column of the Employee table are intended to refer to objects in the Department table. The scope information is needed if the user wants to be able to dereference these references in SQL statements using the new SQL dereference operator (->).
This example has shown how a table hierarchy can be defined, based on a corresponding hierarchy of structured types, in order to create a database in which objects of particular types and subtypes can be stored and managed. Every table hierarchy has a "root table", which has an OID column plus a column for each attribute of its declared type. In addition, it can have a number of "subtables", each of which is created UNDER the root table or some other appropriate "supertable" within the table hierarchy. This example has also shown how scopes are specified for reference attributes.
A SELECT, UPDATE, or DELETE statement that operates on a supertable automatically operates on all its subtables as well. For example, an UPDATE statement on the Employee table might affect rows in Employee, Manager, and Architect tables, but an UPDATE statement on the Manager table can only affect Manager rows.
See SQL Reference for more information on the CREATE TABLE statement (or the CREATE VIEW statement) and how to establish subtype/supertype relationships between typed tables. (For an introduction to CREATE VIEW you could see Creating a Typed View.)
After creating the structured types and then creating the corresponding tables and subtables, you will have a database like the following:
Once the hierarchy is established, you will need to populate the tables with data. This may be done as shown in the following example:
INSERT INTO Department (Oid, Name, Headcount)
VALUES(Department_t('1'), 'Toy', 15);
INSERT INTO Department (Oid, Name, Headcount)
VALUES(Department_t('2'), 'Shoe', 10);
INSERT INTO Person (Oid, Name, Age)
VALUES(Person_t('a'), 'Andrew', 20);
INSERT INTO Person (Oid, Name, Age)
VALUES(Person_t('b'), 'Bob', 30);
INSERT INTO Person (Oid, Name, Age)
VALUES(Person_t('c'), 'Cathy', 25);
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000, Department_t('1'));
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
VALUES(Employee_t('e'), 'Eva', 31, 83, 45000, Department_t('2'));
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
VALUES(Employee_t('f'), 'Franky', 28, 214, 39000, Department_t('2'));
INSERT INTO Student (Oid, Name, Age, SerialNum, Marks)
VALUES(Student_t('g'), 'Gordon', 19, 10245, 90);
INSERT INTO Student (Oid, Name, Age, SerialNum, Marks)
VALUES(Student_t('h'), 'Helen', 20, 10357, 70);
INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus)
VALUES(Manager_t('i'), 'Iris', 35, 251, 55000, Department_t('1'), 12000);
INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus)
VALUES(Manager_t('j'), 'Christina', 10, 317, 85000, Department_t('1'), 25000);
INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus)
VALUES(Manager_t('k'), 'Ken', 55, 482, 105000, Department_t('2'), 48000);
INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption)
VALUES(Architect_t('l'), 'Leo', 35, 661, 92000, Department_t('2'), 20000);
INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption)
VALUES(Architect_t('m'), 'Brian', 7, 882, 112000,
(SELECT Oid FROM Department WHERE name = 'Toy'), 30000);
Notice from the example that first value in each inserted row is the OID for the data being inserted into the tables. Also, when inserting data into a subtable, note that data must be provided for its inherited columns. Finally, notice that any reference-valued expression of the appropriate type can be used to initialize a reference attribute. In most cases above, the Dept reference of the employees is input as an appropriately type-casted constant; however, in the case of Brian, the reference is obtained using a subquery.
Following the above INSERT statements, we can now query the typed tables. For example, here is the result we would obtain if we now ask DB2 to "SELECT Name, Age FROM Person", which prints the names and ages of all persons (in Person or its subtables) in our database:
NAME AGE -------------------- ----------- Andrew 20 Bob 30 Dennis 26 Eva 31 Franky 28 Gordon 19 Helen 20 Iris 35 Christina 10 Ken 55 Leo 35 Brian 7 12 record(s) selected.
Similarly, here is the result of the query "SELECT Name, Salary, Dept->Name FROM Employee", which prints the names, salaries, and department names of all the employees in the database:
NAME SALARY NAME -------------------- ----------- -------------------- Dennis 30000 Toy Eva 45000 Shoe Franky 39000 Shoe Iris 55000 Toy Christina 85000 Toy Ken 105000 Shoe Leo 92000 Shoe Brian 112000 Toy 8 record(s) selected.
| Note: | In the second SELECT statement above, the dereference operator (->) is used. The dereference operator returns the named column value from the target table of a scoped reference. In the expression "Dept --> Name", "Dept" is a reference column whose scope (target table) is "Department", and "Name" is the name of a column in that target table. |
A hierarchy table is a table that is associated with the implementation of a typed table hierarchy. It is created at the same time as the root table of the hierarchy. When creating a root table, an optional HIERARCHY clause can be used to specify the name of the hierarchy table that is associated with the root table. If you do not specify a name, the name of the hierarchy table is the same as the name of the root table, followed by a system-generated unique suffix. A hierarchy table cannot be directly referenced in an SQL statement.
The hierarchy table contains one column for each unique column in the hierarchy. Give the hierarchy show in Figure 20, the hierarchy table would contain these columns: Oid, Name, Age, SerialNum, Salary, Marks, Bonus, and StockOption. (The actual order of the columns depends on the order that the tables were created.) In addition, there is an extra column for the type ID so that the DB2 database manager can tell the type of a given row.
Suppose the type IDs are as follows: Person uses 10,
Employee uses 25, Manager uses 35, Architect
uses 45, and Student uses 100. Then given the values
inserted into the hierarchy as shown following Figure 20, the populated hierarchy table would appear as
follows:
| (type) | Oid | Name | Age | SerialNum | Salary | Marks | Bonus | StockOption |
|---|---|---|---|---|---|---|---|---|
| 10 | a | Andrew | 20 | -- | -- | -- | -- | -- |
| 10 | b | Bob | 30 | -- | -- | -- | -- | -- |
| 10 | c | Cathy | 25 | -- | -- | -- | -- | -- |
| 25 | d | Dennis | 26 | 105 | 30000 | -- | -- | -- |
| 25 | e | Eva | 31 | 83 | 45000 | -- | -- | -- |
| 25 | f | Franky | 28 | 214 | 39000 | -- | -- | -- |
| 100 | g | Gordon | 19 | 10245 | -- | 90 | -- | -- |
| 100 | h | Helen | 20 | 10357 | -- | 70 | -- | -- |
| 35 | i | Iris | 35 | 251 | 55000 | -- | 12000 | -- |
| 35 | j | Christina | 10 | 371 | 85000 | -- | 25000 | -- |
| 35 | k | Ken | 55 | 482 | 105000 | -- | 48000 | -- |
| 45 | l | Leo | 35 | 661 | 92000 | -- | -- | 20000 |
| 45 | m | Brian | 7 | 882 | 112000 | -- | -- | 30000 |
Notice that when a column does not apply to a given row, then the value is NULL (as shown by the "--").
The SQL optimizer uses the hierarchy table to generate access plans for processing queries written against the individual tables in the hierarchy.
Data, index, and long column data can be stored in the same table space as the table or in a different table space only for DMS. The following example shows how the EMP_PHOTO table could be created to store the different parts of the table in different table spaces:
CREATE TABLE EMP_PHOTO
(EMPNO CHAR(6) NOT NULL,
PHOTO_FORMAT VARCHAR(10) NOT NULL,
PICTURE BLOB(100K) )
IN RESOURCE
INDEX IN RESOURCE_INDEXES
LONG IN RESOURCE_PHOTO
This example will cause the EMP_PHOTO data to be stored as follows:
See Table Space Design Considerations for additional considerations on the use of multiple DMS table spaces for a single table.
Refer to the SQL Reference for more information.
Before creating a table that will be physically divided or partitioned, you need to consider the following:
One additional option exists when creating a table in a partitioned database environment: the partitioning key. A partitioning key is a key that is part of the definition of a table. It determines the partition on which each row of data is stored.
It is important to select an appropriate partitioning key because it cannot be changed later. Furthermore, any unique indexes (and therefore unique or primary keys) must be defined as a superset of the partitioning key. That is, if a partitioning key is defined, unique keys and primary keys must include all of the same columns as the partitioning key (they may have more columns).
If you do not specify the partitioning key explicitly, the following defaults are used. Ensure that the default partitioning key is appropriate.
Following is an example:
CREATE TABLE MIXREC (MIX_CNTL INTEGER NOT NULL,
MIX_DESC CHAR(20) NOT NULL,
MIX_CHR CHAR(9) NOT NULL,
MIX_INT INTEGER NOT NULL,
MIX_INTS SMALLINT NOT NULL,
MIX_DEC DECIMAL NOT NULL,
MIX_FLT FLOAT NOT NULL,
MIX_DATE DATE NOT NULL,
MIX_TIME TIME NOT NULL,
MIX_TMSTMP TIMESTAMP NOT NULL)
IN MIXTS12
PARTITIONING KEY (MIX_INT) USING HASHING
In the preceding example, the table space is MIXTS12 and the partitioning key is MIX_INT. If the partitioning key is not specified explicitly, it is MIX_CNTL. (If no primary key is specified and no partitioning key is defined, the partitioning key is the first non-long column in the list.)
A row of a table, and all information about that row, always resides on the same database partition.
The size limit for one partition of a table is 64 GB, or the available disk space, whichever is smaller. (This assumes a 4 KB page size for the table space.) The size of the table can be as large as 64 GB (or the available disk space) times the number of database partitions. If the page size for the table space was 8 KB, the size of the table can be as large as 128 GB (or the available disk space) times the number of database partitions. If the page size for the table space was 16 KB, the size of the table can be as large as 256 GB (or the available disk space) times the number of database partitions. If the page size for the table space was 32 KB, the size of the table can be as large as 512 GB (or the available disk space) times the number of database partitions.
A trigger defines a set of actions that are executed in conjunction with, or triggered by, an INSERT, UPDATE, or DELETE clause on a specified base table. Some uses of triggers are to:
You cannot use triggers with nicknames.
You can use triggers to support general forms of integrity or business rules. For example, a trigger can check a customer's credit limit before an order is accepted or update a summary data table.
The benefits of using a trigger are:
The following SQL statement creates a trigger that increases the number of employees each time a new person is hired, by adding 1 to the number of employees (NBEMP) column in the COMPANY_STATS table each time a row is added to the EMPLOYEE table.
CREATE TRIGGER NEW_HIRED
AFTER INSERT ON EMPLOYEE
FOR EACH ROW MODE DB2SQL
UPDATE COMPANY_STATS SET NBEMP = NBEMP+1;
A trigger body can include one or more of the following SQL statements: INSERT, searched UPDATE, searched DELETE, full-selects, SET transition-variable, and SIGNAL SQLSTATE. The trigger can be activated before or after the INSERT, UPDATE, or DELETE statement to which it refers. Refer to the SQL Reference for complete syntax information on the CREATE TRIGGER statement. Refer to the Application Development Guide for information about creating and using triggers.
All dependencies of a trigger on some other object are recorded in the SYSCAT.TRIGDEP catalog. A trigger can depend on many objects. These objects and the dependent trigger are presented in detail in the SQL Reference discussion on the DROP statement.
If one of these objects is dropped, the trigger becomes inoperative but its definition is retained in the catalog. To revalidate this trigger, you must retrieve its definition from the catalog and submit a new CREATE TRIGGER statement.
If a trigger is dropped, its description is deleted from the SYSCAT.TRIGGERS catalog view and all of its dependencies are deleted from the SYSCAT.TRIGDEP catalog view. All packages having UPDATE, INSERT, or DELETE dependencies on the trigger are invalidated.
If the dependent object is a view and it is made inoperative, the trigger is also marked inoperative. Any packages dependent on triggers that have been marked inoperative are invalidated. (For more information, see Statement Dependencies When Changing Objects.)
User-defined functions (UDFs) extend and add to the support provided by built-in functions of SQL, and can be used wherever a built-in function can be used. You can create UDFs as either:
There are three types of UDFs:
For example, if there is a distinct type SHOESIZE defined with base type INTEGER, a UDF AVG(SHOESIZE) which is sourced on the built-in function AVG(INTEGER) could be defined, and it would be a column function.
For example, table functions can take a file and convert it to a table, tabularize sample data from the World Wide Web, or access a Lotus Notes database and return information such as the date, sender, and text of mail messages. This information can be joined with other tables in the database.
A table function can only be an external function. It cannot be a sourced function.
Information about existing UDFs is recorded in the SYSCAT.FUNCTIONS and SYSCAT.FUNCPARMS catalog views. The system catalog does not contain the executable code for the UDF. (Therefore, when creating your backup and recovery plans you should consider how you will manage your UDF executables.)
Statistics about the performance of UDFs are important when compiling SQL statements. For information about how to update UDF statistics in the system catalog, refer to "Updating Statistics for User-Defined Functions" in Administration Guide, Performance.
For details on using the CREATE FUNCTION statement to write a UDF to suit your specific application, refer to the Application Development Guide. Refer to the SQL Reference for details on UDF syntax.
In a federated database, create a function mapping when you need to map a local function or a local function template (described in Creating a Function Template) with a function at one or more data sources. Default function mappings are provided for many data source functions.
Function mappings are useful when:
Function mappings defined with CREATE FUNCTION MAPPING statements are stored in the federated database.
Functions (or function templates) must have the same number of input parameters as the data source function. Additionally, the data types of the input parameters on the federated side should be compatible with the data types of the input parameters on the data source side.
Use the CREATE FUNCTION MAPPING statement to create a function mapping. For example, to create a function mapping between an Oracle AVGNEW function and a DB2 equivalent at server ORACLE1:
CREATE FUNCTION MAPPING ORAVGNEW FOR SYSIBM.AVG(INT) SERVER ORACLE1 OPTIONS (REMOTE_NAME 'AVGNEW')
You must hold one of the SYSADM or DBADM authorities at the federated database to use this statement. Function mapping attributes are stored in SYSCAT.FUNCMAPPINGS.
The federated server will not bind input host variables or retrieve results of LOB, LONG VARCHAR/VARGRAPHIC, DATALINK, distinct and structured types. No function mapping can be created when an input parameter or the returned value includes one of these types.
For additional details on using and creating function mappings, refer to the Application Development Guide. Refer to the SQL Reference for details on CREATE FUNCTION MAPPING syntax.
In a federated system, function templates provide "anchors" for function mappings. They are used to enable the mapping of a data source function when a corresponding DB2 function does not exist at the federated server. A function mapping requires the presence of a function template or an existing similar function at DB2.
The template is just a function shell: name, input parameters, and the return value. There is no local executable for the function.
Because there is no local executable for the function, it is possible that a call to the function template will fail even though the function is available at the data source. For example, consider the query:
SELECT myfunc(C1) FROM nick1 WHERE C2 < 'A'
If DB2 and the data source containing the object referenced by nick1 do not have the same collating sequence, the query will fail because the comparison must be done at DB2 while the function is at the data source. If the collating sequences were the same, the comparison operation could be done at the data source that has the underlying function referenced by myfunc.
Functions (or function templates) must have the same number of input parameters as the data source function. The data types of the input parameters on the federated side should be compatible with the data types of the input parameters on the data source side. These requirements apply to returned values as well.
You create function templates using the CREATE FUNCTION statement with the AS TEMPLATE keyword. After the template is created, you map the template to the data source using the CREATE FUNCTION MAPPING statement.
For example, to create a function template and a function mapping for function MYS1FUNC on server S1:
CREATE FUNCTION MYFUNC(INT) RETURNS INT AS TEMPLATE CREATE FUNCTION MAPPING S1_MYFUNC FOR MYFUNC(INT) SERVER S1 OPTIONS (REMOTE_NAME 'MYS1FUNC')
For details on using and creating function templates, refer to the Application Development Guide. Refer to the SQL Reference for details on CREATE FUNCTION syntax.
A user-defined type (UDT) is a named data type that is created in the database by the user. A UDT can be a distinct type which shares a common representation with a built-in data type or a structured type which has a sequence of named attributes that each have a type. A structured type can be a subtype of another structured type (called a supertype), defining a type hierarchy.
UDTs support strong typing, which means that even though they share the same representation as other types, values of a given UDT are considered to be compatible only with values of the same UDT or UDTs in the same type hierarchy.
The SYSCAT.DATATYPES catalog view allows you to see the UDTs that have been defined for your database. This catalog view also shows you the data types defined by the database manager when the database was created. For a complete list of all data types, refer to the SQL Reference.
A UDT cannot be used as an argument for most of the system-provided, or built-in, functions. User-defined functions must be provided to enable these and other operations.
You can drop a UDT only if:
When a UDT is dropped, any functions that are dependent on it are also dropped.
A user-defined distinct type is a data type derived from an existing type, such as an integer, decimal, or character type. You can create a distinct type by using the CREATE DISTINCT TYPE statement.
The following SQL statement creates the distinct type t_educ as a smallint:
CREATE DISTINCT TYPE T_EDUC AS SMALLINT WITH COMPARISONS
Instances of the same distinct type can be compared to each other, if the WITH COMPARISONS clause is specified on the CREATE DISTINCT TYPE statement (as in the example). The WITH COMPARISONS clause cannot be specified if the source data type is a large object, a DATALINK, LONG VARCHAR, or LONG VARGRAPHIC type.
Instances of distinct types cannot be used as arguments of functions or operands of operations that were defined on the source type. Similarly, the source type cannot be used in arguments or operands that were defined to use a distinct type.
After you have created a distinct type, you can use it to define columns in a CREATE TABLE statement:
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10M) NOT NULL,
EDLEVEL T_EDUC)
IN RESOURCE
Creating the distinct type also generates support to cast between the distinct type and the source type. Hence, a value of type T_EDUC can be cast to a SMALLINT value and SMALLINT value can be cast to a T_EDUC value.
Refer to the SQL Reference for complete syntax information on the CREATE DISTINCT TYPE statement. Refer to the Application Development Guide for information about creating and using a distinct type.
You can transform UDTs into base data types, and base data types into UDTs, using transformations. Creation of a transform function is through a CREATE TRANSFORM statement.
Support for transforms is also found through the CREATE METHOD statement and extensions to the CREATE FUNCTION statement. Refer to the SQL Reference for details on this support.
A structured type is a user-defined type that contains one or more attributes, each of which has a name and a data type of its own. A structured type can serve as the type of a table, in which each column of the table derives its name and data type from one of the attributes of the structured type. A structured type may be created as a subtype of another structured type, called its "supertype". In this case, the subtype inherits all the attributes of the supertype, and may optionally add additional attributes of its own.
For example, consider the following user-defined structured types:
CREATE TYPE Department_t AS (Name VARCHAR(20), Headcount INT)
MODE DB2SQL;
CREATE TYPE Person_t AS (Name VARCHAR(20), Age INT)
MODE DB2SQL;
CREATE TYPE Employee_t UNDER Person_t
AS (SerialNum INT, Salary INT, Dept REF(Department_t))
MODE DB2SQL;
CREATE TYPE Student_t UNDER Person_t AS (SerialNum INT, Marks INT)
MODE DB2SQL;
CREATE TYPE Manager_t UNDER Employee_t AS (Bonus INT)
MODE DB2SQL;
CREATE TYPE Architect_t UNDER Employee_t AS (StockOption INT)
MODE DB2SQL;
The AS clause provides the attribute definitions associated with the type.
The MODE DB2SQL clause is used to specify the mode of the type. DB2SQL is the only value for mode currently supported.
The UNDER clause specifies that the structured type is being defined as a subtype of the specified supertype.
The first structured type above (Department_t) is a type with two attributes: Name and Headcount. The second structured type (Person_t) is another type with two attributes: Name and Age. The type Person_t has two subtypes, Employee_t and Student_t, that each inherit the attributes of Person_t and also have several additional attributes that are specific to their particular types. Note that the Dept attribute of Employee_t is a reference, of type REF(Department_t), that can refer to an object of type Department_t. Finally, Manager_t and Architect_t are both subtypes of Employee_t; they inherit all the attributes of Employee_t and extend them further as appropriate for their types. Thus, an instance of type Manager_t will have a total of six attributes: Name, Age, SerialNum, Salary, Dept, and Bonus.
This example showing user-defined structured types contains definitions for two "type hierarchies". One is the Department_t type hierarchy, which consists only of the type Department_t (and therefore isn't much of a hierarchy). The other is the Person_t type hierarchy, which consists of the type Person_t, two subtypes of Person_t, namely Employee_t and Student_t, and two subtypes of Employee_t, namely Manager_t and Architect_t. The Department_t type and Person_t type are "root types" since they are not subtypes of any other type (that is, neither one has an UNDER clause in its type definition).
Refer to SQL Reference for more information on the CREATE TYPE (Structured) statement.
In a federated system, a type mapping lets you map specific data types in data source tables and views to DB2 distinct data types. A type mapping can apply to one data source or a range (type, version) of data sources.
Default data type mappings are provided for built-in data source types and built-in DB2 types. New data type mappings (that you create) will be listed in the SYSCAT.TYPEMAPPINGS view.
You create type mappings with the CREATE TYPE MAPPING statement. You must hold one of the SYSADM or DBADM authorities at the federated database to use this statement.
An example of a type mapping statement is:
CREATE TYPE MAPPING MY_ORACLE_DEC FROM SYSIBM.DECIMAL(10,2) TO SERVER ORACLE1 TYPE NUMBER([10..38],2)
You cannot create a type mapping for a LOB, LONG VARCHAR/VARGRAPHIC, DATALINK, structured or distinct type.
For details on using and creating type mappings, refer to the Application Development Guide. Refer to the SQL Reference for details on CREATE TYPE MAPPING syntax.
Views are derived from one or more base tables, nicknames, or views, and can be used interchangeably with base tables when retrieving data. When changes are made to the data shown in a view, the data is changed in the table itself.
A view can be created to limit access to sensitive data, while allowing more general access to other data. For example, the EMPLOYEE table may have salary information in it, which should not be made available to everyone. The employee's phone number, however, should be generally accessible. In this case, a view could be created from the LASTNAME and PHONENO columns only. Access to the view could be granted to PUBLIC, while access to the entire EMPLOYEE table could be restricted to those who have the authorization to see salary information. For information about read-only views, refer to the SQL Reference manual.
With a view, you can make a subset of table data available to an application program and validate data that is to be inserted or updated. A view can have column names that are different from the names of corresponding columns in the original tables.
The use of views provides flexibility in the way your programs and end-user queries can look at the table data.
The following SQL statement creates a view on the EMPLOYEE table that lists all employees in Department A00 with their employee and telephone numbers:
CREATE VIEW EMP_VIEW (DA00NAME, DA00NUM, PHONENO)
AS SELECT LASTNAME, EMPNO, PHONENO FROM EMPLOYEE
WHERE WORKDEPT = 'A00'
WITH CHECK OPTION
The first line of this statement names the view and defines its columns. The name EMP_VIEW must be unique within its schema in SYSCAT.TABLES. The view name appears as a table name although it contains no data. The view will have three columns called DA00NAME, DA00NUM, and PHONENO, which correspond to the columns LASTNAME, EMPNO, and PHONENO from the EMPLOYEE table. The column names listed apply one-to-one to the select list of the SELECT statement. If column names are not specified, the view uses the same names as the columns of the result table of the SELECT statement.
The second line is a SELECT statement that describes which values are to be selected from the database. It may include the clauses ALL, DISTINCT, FROM, WHERE, GROUP BY, and HAVING. The name or names of the data objects from which to select columns for the view must follow the FROM clause.
The WITH CHECK OPTION clause indicates that any updated or inserted row to the view must be checked against the view definition, and rejected if it does not conform. This enhances data integrity but requires additional processing. If this clause is omitted, inserts and updates are not checked against the view definition.
The following SQL statement creates the same view on the EMPLOYEE table using the SELECT AS clause:
CREATE VIEW EMP_VIEW
SELECT LASTNAME AS DA00NAME,
EMPNO AS DA00NUM,
PHONENO
FROM EMPLOYEE
WHERE WORKDEPT = 'A00'
WITH CHECK OPTION
You can create a view that uses a UDF in its definition. However, to update this view so that it contains the latest functions, you must drop it and then re-create it. If a view is dependent on a UDF, that function cannot be dropped.
The following SQL statement creates a view with a function in its definition:
CREATE VIEW EMPLOYEE_PENSION (NAME, PENSION)
AS SELECT NAME, PENSION(HIREDATE,BIRTHDATE,SALARY,BONUS)
FROM EMPLOYEE
The UDF function PENSION calculates the current pension an employee is eligible to receive, based on a formula involving their HIREDATE, BIRTHDATE, SALARY, and BONUS.
In addition to using views as described above, a view can also be used to:
When you create a view that references nicknames using standard CREATE VIEW syntax, you will see a warning alerting you to the fact that the authentication ID of view users will be used to access the underlying object or objects at data sources instead of the view creator authentication ID. Use the FEDERATED keyword to suppress this warning.
An alternative to creating a view is to use a nested or common table expression to reduce catalog lookup and improve performance. Refer to the SQL Reference for more information about common table expressions.
You can create a typed view using the CREATE VIEW statement. For example, to create a view of the typed Department table that we created earlier, we can define a structured type that has the desired attributes and then create a typed view using that type:
CREATE TYPE VDepartment_t AS (Name VARCHAR(20))
MODE DB2SQL;
CREATE VIEW VDepartment OF VDepartment_t MODE DB2SQL
(REF IS VOid USER GENERATED)
AS SELECT VDepartment_t(Varchar(Oid)), Name FROM Department;
The OF clause in the CREATE VIEW statement tells the system that the columns of the view are to be based on the attributes of the indicated structured type (in this case VDepartment_t).
The MODE DB2SQL clause specifies the mode of the typed view. This is the only valid mode currently supported.
The REF IS... clause is identical to that of the typed CREATE TABLE statement. It provides a name for the view's OID column (VOid in this case), which is the first column of the view. Typed views, like typed tables, require an OID column to be specified (in the case of a root view) or inherited (in the case of a subview, as will be shown shortly).
The USER GENERATED clause specifies that the initial value for the OID column must be provided by the user when inserting a row. Once inserted, the OID column cannot be updated.
The "body" of the view, which follows the keyword AS, is a SELECT statement that determines the content of the view. The column-types returned by this SELECT statement must be compatible with the column-types of the typed view, including the initial object ID column.
To illustrate the creation of a typed view hierarchy, the following example defines a view hierarchy that omits some sensitive data and eliminates some type distinctions from the Person table hierarchy created earlier under Creating a Typed Table:
CREATE TYPE VPerson_t AS (Name VARCHAR(20))
MODE DB2SQL;
CREATE TYPE VEmployee_t UNDER VPerson_t
AS (Salary INT, Dept REF(VDepartment_t))
MODE DB2SQL;
CREATE VIEW VPerson OF VPerson_t MODE DB2SQL
(REF IS VOid USER GENERATED)
AS SELECT VPerson_t (Varchar(Oid)), Name FROM ONLY(Person);
CREATE VIEW VEmployee OF VEmployee_t MODE DB2SQL
UNDER VPerson INHERIT SELECT PRIVILEGES
(Dept WITH OPTIONS SCOPE VDepartment)
AS SELECT VEmployee_t(Varchar(Oid)), Name, Salary,
VDepartment_t(Varchar(Dept))
FROM Employee;
The two CREATE TYPE statements create the structured types that are needed to create the object view hierarchy for this example.
The first typed CREATE VIEW statement above creates the root view of the hierarchy, VPerson, and is very similar to the VDepartment view definition. The difference is the use of ONLY(Person) to ensure that only the rows in the Person table hierarchy that are in the Person table (and not in any subtable) are included in the VPerson view. This ensures that the Oid values in VPerson are unique compared with the Oid values in VEmployee. The second CREATE VIEW statement creates a subview VEmployee under the view VPerson. As was the case for the UNDER clause in the CREATE TABLE...UNDER statement, the UNDER clause when creating a view establishes the superview/subview relationship. (Note: The subview must be created in the same schema as its superview.) As was the case for typed tables, columns are inherited by subviews. Rows in the VEmployee view will inherit the columns VOid and Name from VPerson and have the additional columns Salary and Dept associated with the type VEmployee_t.
The INHERIT SELECT PRIVILEGES clause has the same meaning here as in the typed CREATE TABLE statement.
Similarly, the WITH OPTIONS clause in a typed view definition plays the same role as it does in a typed table definition -- it allows column options such as SCOPE to be specified. As well, a new column option, READ ONLY (not used in our example), is provided for columns of typed views. This clause is used to force a superview column to be marked as read-only so that a later subview definition can legitimately specify an expression for the same column that is implicitly read-only.
If a view has a reference column (like VEmployee's Dept column), a scope must be associated with the column if it is to be usable in SQL dereference operations. If no scope is specified for the reference column of the view and the underlying table or view column was scoped, then the underlying column's scope is passed on to the view's reference column. It can be explicitly given a scope by using WITH OPTIONS, as in our example where the Dept column of the VEmployee view gets the VDepartment view as its scope. The column would remain unscoped if the underlying table or view column did not have a scope and none was explicitly assigned in the view definition (or later by using the ALTER VIEW statement).
There are several important rules associated with restrictions on the queries for typed views found in the SQL Reference that you should read carefully before attempting to create and use a typed view.
A summary table is a table whose definition is based on the result of a query. As such, the summary table typically contains pre-computed results based on the data existing in the table or tables that its definition is based on. If the SQL compiler determines that a query will run more efficiently against a summary table than the base table, the query executes against the summary table, and you obtain the result faster than you otherwise would.
The creation of a summary table with the replication option can be used to replicate tables across all nodes in a partitioned database environment. These are known as "replicated summary tables". See Replicated Summary Tables for more information.
| Note: | Summary tables are not used with static SQL or nicknames. |
In general a summary table, or a replicated summary table, is used for optimization of a query if the isolation level of the summary table, or the replicated summary table, is higher than or equal to the isolation level of the query. For example, if a query is running under the cursor stability (CS) isolation level, only summary tables, and replicated summary tables, that are defined under CS or higher isolation levels are used for optimization.
To create a summary table, you use the CREATE SUMMARY TABLE statement with the AS fullselect clause and the IMMEDIATE or REFRESH DEFERRED options. When you create the summary table, you have the option of specifying whether the summary table is refreshed automatically when the base table is changed, or whether it is refreshed by using the REFRESH TABLE statement. To have the summary table refreshed automatically when changes are made to the base table or tables, specify the REFRESH IMMEDIATE keyword. An immediate refresh is useful when:
The summary table, in this situation, can provide pre-computed results. If you want the refresh of the summary table to be deferred, specify the REFRESH DEFERRED keyword. Summary tables specified with REFRESH DEFERRED will not reflect changes to the underlying base tables. You should use summary tables where this is not a requirement. For example, if you run DSS queries, you would use the summary table to contain legacy data.
A summary table defined with REFRESH DEFERRED may be used in place of a query when it:
The SQL special register CURRENT REFRESH AGE SQL is set to ANY or has a value of 99999999999999. The collection of nines is the maximum value allowed in this special register which is a timestamp duration value with a data type of DECIMAL(20,6).
| Note: | Summary tables defined with REFRESH DEFERRED are not used to optimize static SQL. |
You use the CURRENT REFRESH AGE special register to specify the amount of time that the summary table with deferred refresh can be used for a dynamic query before it must be refreshed. To set the value of the CURRENT REFRESH AGE special register, you can use the SET CURRENT REFRESH AGE statement. For more information about the CURRENT REFRESH AGE special register and the SET CURRENT REFRESH AGE statement, refer to the SQL Reference.
Summary tables defined with REFRESH IMMEDIATE are applicable to both static and dynamic queries and do not need to use the CURRENT REFRESH AGE special register.
| Note: | Setting the CURRENT REFRESH AGE special register to a value other than zero should be done with caution. By allowing a summary table that may not represent the values of the underlying base table to be used to optimize the processing of the query, the result of the query may not accurately represent the data in the underlying table. This may be reasonable when you know the underlying data has not changed, or you are willing to accept the degree of error in the results based on your knowledge of the data. |
With activity affecting the source data, a summary table over time will no longer contain accurate data. You will need to use the REFRESH TABLE statement. Refer to the SQL Reference for more information.
If you want to create a new base table that is based on any valid fullselect, specify the DEFINITION ONLY keyword when you create the table. When the create table operation completes, the new table is not treated as a summary table, but rather as a base table. For example, you can create the exception tables used in LOAD and SET INTEGRITY as follows:
CREATE TABLE XT AS
(SELECT T.*, CURRENT TIMESTAMP AS TIMESTAMP,CLOB(",32K)
AS MSG FROM T) DEFINITION ONLY
Here are some of the key restrictions regarding summary tables:
Refer to the SQL Reference for a complete statement of summary table restrictions.
An alias is an indirect method of referencing a table, nickname, or view, so that an SQL statement can be independent of the qualified name of that table or view. Only the alias definition must be changed if the table or view name changes. An alias can be created on another alias. An alias can be used in a view or trigger definition and in any SQL statement, except for table check-constraint definitions, in which an existing table or view name can be referenced.
The alias is replaced at statement compilation time by the table or view name. If the alias or alias chain cannot be resolved to a table or view name, an error results. For example, if WORKERS is an alias for EMPLOYEE, then at compilation time:
SELECT * FROM WORKERS
becomes in effect
SELECT * FROM EMPLOYEE
An alias name can be used wherever an existing table name can be used, and can refer to another alias if no circular or repetitive references are made along the chain of aliases.
The following SQL statement creates an alias WORKERS for the EMPLOYEE table:
CREATE ALIAS WORKERS FOR EMPLOYEE
The alias name cannot be the same as an existing table, view, or alias, and can only refer to a table within the same database. The name of a table or view used in a CREATE TABLE or CREATE VIEW statement cannot be the same as an alias name in the same schema.
You do not require special authority to create an alias, unless the alias is in a schema other than the one owned by your current authorization ID, in which case DBADM authority is required.
An alias can be defined for a table, view, or alias that does not exist at the time of definition. However, it must exist when an SQL statement containing the alias is compiled.
When an alias, or the object to which an alias refers, is dropped, all packages dependent on the alias are marked invalid and all views and triggers dependent on the alias are marked inoperative.
| Note: | DB2 for MVS/ESA employs two distinct concepts of aliases: ALIAS and SYNONYM. These two concepts differ from DB2 Universal Database as follows: |
In a federated database, the CREATE WRAPPER statement registers a wrapper. The statement defines the mechanism by which a federated server can interact with a certain category of data source.
Specific libraries must be used for specific data source types, versions, communication protocols, and operating systems. For example, AS/400 and DB2 for OS/390 data sources are accessed using the "libdrda.dll" library for federated databases operating on Windows NT operating systems using APPC communications.
Wrappers can be created in the Control Center or from the command line processor. In both cases, creating a wrapper registers it to the federated database.
The following SQL statement registers the wrapper ORACLE8 on a Windows NT operating system:
CREATE WRAPPER ORACLE8 LIBRARY 'libnet8.dll'
You must have SYSADM or DBADM authority at the federated database to use this statement.
For details on using the CREATE WRAPPER statement, refer to the Application Development Guide. Refer to the SQL Reference for details on syntax.
In a federated database, create servers to define data sources to DB2 and describe their characteristics: name, wrapper, type, version, location, and options. This information is used to map nicknames to specific data management systems and to provide information to the DB2 optimizer. Server information is located in the SYSCAT.SERVERS and SYSCAT.SERVEROPTIONS catalog views.
| Note: | In this section, servers represent data sources, not DRDA servers or DB2 DBMSs. |
You can create servers from the Control Center or the command line processor.
The following sample SQL statement creates the Oracle server ORA8:
CREATE SERVER ORA8 TYPE ORACLE VERSION 8 WRAPPER ORACLE8 OPTIONS (NODE 'ONODE')
The following sample SQL statement creates the DB2 server DB2TEST:
CREATE SERVER DB2TEST TYPE DB2 VERSION 6.1 WRAPPER DB2UDB OPTIONS (NODE 'DB2TEST', DBNAME 'TEST1')
The definition of NODE, in SERVER SQL statements, varies depending on the data source. If the data source is a DB2 DBMS, the value refers to an instance of DB2 that has one or more databases. In the previous example, note that the DBNAME option specifies the database name. If the data source is a DB2 for OS/390 DBMS, the value refers to a specific node for a subsystem. If the data source is an Oracle DBMS, the value refers to the actual database (the DBNAME option is not needed).
You must have SYSADM or DBADM authority at the federated database to use this statement.
For additional details on using the CREATE SERVER statement, refer to the SQL Reference.
You can create user mappings to manage differences in authentication processing between DB2 and data source servers. User mappings are discussed in detail in User Mappings.
When a server is dropped, all objects dependent on that server are dropped (user mappings, nicknames, function mappings, type mappings, plans, etc.).
Provide server options when creating a server. These options provide necessary details about the server (such as the node name). Server options can also set specific performance and security values.
You can set variables called server options to values that affect how a federated server accesses data sources. This section:
Purposes of Server Options: In general, you use server options to:
There are three SQL statements in which you can assign values to server options: CREATE SERVER, ALTER SERVER, and SET SERVER OPTION.
Use the CREATE SERVER statement to set an option to a value that persists indefinitely over time for multiple connections to a data source. With this statement, you can set an option to a value other than the default or, if an option has no default value, you can set it to an initial value.
Use the ALTER SERVER statement if, after setting a server option to a value with the CREATE SERVER statement, you want to set it to a different value that persists over multiple connections.
Use the SET SERVER OPTION statement to change server option values temporarily for the duration of a single connection to a database. SET SERVER OPTION statements must be issued first within the first unit of work following the connection to the data source.
For example, to temporarily enable the use of plan hints for the Oracle server ORASEB1, issue the statement:
SET SERVER OPTION plan_hints TO 'Y' FOR SERVER ORASEB1
Server Options and Their Settings: See the "Server Options Affecting Federated Database Queries" section at the end of the "Environmental Considerations" chapter which describes the server options and the values that you can set them to. Unless otherwise stated, all server option values must be enclosed in single quotes.
Using Pass-through Sessions with Servers: Pass-through sessions let applications communicate directly with a server using the server's native client access method and native SQL dialect.
Pass-through sessions are useful when:
When referencing objects in a pass-through session, use the true name of the object (not the nickname).
Use the SET PASSTHRU statement to start a pass-through session and access a server directly. This statement must be issued dynamically. An example of this statement is:
SET PASSTHRU BACKEND
which opens a pass-through session to the data source BACKEND.
For more information on SET PASSTHRU and SQL processing in pass-through sessions, see the SQL Reference.
In a federated database, nicknames are identifiers for data source tables, aliases, and views. Distributed requests typically reference nicknames, not data source tables or views.
Nicknames are part of the means by which DB2 provides location transparency. Nicknames rely on server definitions for data source location information to find and efficiently access data sources. An ALTER SERVER statement can, for example, transparently update server performance data and version information for all users and applications without requiring new nicknames or changes to application code.
Nicknames can be created in the Control Center or from the command line processor. You can define more than one nickname for the same data source table or view.
Nicknames cannot be used in static SQL statements.
Before creating a nickname, run the equivalent of the RUNSTATS command at the data source and update statistics for data source objects. Statistical information is gathered from data sources when a nickname is created and stored in the federated database catalog. This catalog data includes table and column definitions, and, if available, index definitions and statistics.
The following SQL statement creates the nickname CUSTOMER:
CREATE NICKNAME CUSTOMER for OS390A.SHAWNB.CUSTLIST
You must hold one of the SYSADM or DBADM authorities, or, you must have either the database privilege IMPLICIT_SCHEMA or the schema privilege CREATEIN (for the current schema) at the federated database to use this statement.
For additional details on using the CREATE NICKNAME statement, refer to the SQL Reference.
References to data source objects typically use the defined nickname. The one exception is a reference within a pass-through session (see Using Pass-through Sessions with Servers for more information). For example, if you define the nickname DEPT for the data source table DB2MVS1.PERSON.DEPT, the statement SELECT * FROM DEPT is allowed; the statement SELECT * FROM DB2MVS1.PERSON.DEPT is not allowed.
Most utility commands (LOAD, IMPORT, EXPORT, REORGCHK, REORGANIZE TABLE) do not support nicknames
COMMENT ON is supported; it updates the system catalog at the federated database.
INSERT, UPDATE, and DELETE operations are not supported against nicknames.
After you have created several nicknames, you might want to use the following information to identify to which data source a given nickname corresponds or identify all nicknames at a given data source.
Identifying a Nickname and Its Data Source:
This example assumes that you know the nickname (PAYROLL) and who created it (ACCTG), but need additional information about the data source. Use the following SQL statement to first obtain information about what PAYROLL is known as at its data source (SERVER).
select option, setting
from syscat.taboptions
where tabname = 'PAYROLL'
and tabschema = 'ACCTG'
and option in ('SERVER','REMOTE_SCHEMA','REMOTE_TABLE');
The answer set from this statement is DB2_MVS, FINANCE, DEPTJ35_PAYROLL. You now know that PAYROLL is the nickname for the table called DEPTJ35_PAYROLL owned by FINANCE at the server named DB2_MVS. You can use this information in a subsequent SELECT statement:
select option,setting
from syscat.serveroptions
where servername = 'DB2_MVS'
and option in ('NODE','DBNAME');
The answer set from this statement is REGIONW and DB2MVSDB3. You now know that the table DEPTJ35_PAYROLL is in a database named DB2MVSDB3, on a node called REGIONW.
With this information, you can use the LIST NODE DIRECTORY command to obtain information about the REGIONW node, such as the communications protocol and security type used. If the node had been for a data source other than the DB2 Family, you would need to check that data source's configuration files to find similar information. For example, if the node had been an Oracle data source, you would get similar information from the Oracle tnsnames.ora file.
For details on system catalog views, refer to the SQL Reference.
Identifying All Nicknames Known to DB2:
The following SQL statement provides a list of all nicknames known to the federated database, including the schema name and remote server for each nickname.
select tabname,tabschema, setting as remote_server
from syscat.taboptions
where option = 'SERVER';
An index is a list of the locations of rows, sorted by the contents of one or more specified columns. Indexes are typically used to speed up access to a table. However, they can also serve a logical data design purpose. For example, a unique index does not allow entry of duplicate values in the columns, thereby guaranteeing that no two rows of a table are the same. Indexes can also be created to specify ascending or descending order of the values in a column.
An index specification is a metadata construct. It tells the optimizer that an index exists for a data source object (table or view) referenced by a nickname. An index specification does not contain lists of row locations-it is just a description of an index. The optimizer uses the index specification to improve access to the object referenced by the nickname. When a nickname is first created, an index specification is generated if an index exists for the underlying table at the data source in a format DB2 can recognize.
| Note: | If needed, create index specifications on table nicknames or view nicknames where the view is over one table. |
Manually create an index or an index specification when:
Index specifications can be created when no index exists on the base table (DB2 will not check for the remote index when you issue the CREATE INDEX statement). An index specification does not enforce uniqueness of rows even when the UNIQUE keyword is specified.
The DB2 Index Advisor is a wizard that assists you in choosing an optimal set of indexes. You can access this wizard through the Control Center. The comparable utility is called db2advis.
An index is defined by columns in the base table. It can be defined by the creator of a table, or by a user who knows that certain columns require direct access. A primary index key is automatically created on the primary key, unless a user-defined index already exists.
Any number of indexes can be defined on a particular base table, and they can have a beneficial effect on the performance of queries. However, the more indexes there are, the more the database manager must modify during update, delete, and insert operations. Creating a large number of indexes for a table that receives many updates can slow down processing of requests. Therefore, use indexes only where a clear advantage for frequent access exists.
An index key is a column or collection of columns on which an index is defined, and determines the usefulness of an index. Although the order of the columns making up an index key does not make a difference to index key creation, it may make a difference to the optimizer when it is deciding whether or not to use an index.
If the table being indexed is empty, an index is still created, but no index entries are made until the table is loaded or rows are inserted. If the table is not empty, the database manager makes the index entries while processing the CREATE INDEX statement.
For a clustering index, new rows are inserted physically close to existing rows with similar key values. This yields a performance benefit during queries because it results in a more linear access pattern to data pages and more effective pre-fetching.
If you want a primary key index to be a clustering index, a primary key should not be specified at CREATE TABLE. Once a primary key is created, the associated index cannot be modified. Instead, perform a CREATE TABLE without a primary key clause. Then issue a CREATE INDEX statement, specifying clustering attributes. Finally, use the ALTER TABLE statement to add a primary key that corresponds to the index just created. This index will be used as the primary key index.
Generally, clustering is more effectively maintained if the clustering index is unique.
Column data which is not part of the unique index key but which is to be stored/maintained in the index is called an include column. Include columns can be specified for unique indexes only. When creating an index with include columns, only the unique key columns are sorted and considered for uniqueness. Use of include columns improves the performance of data retrieval when index access is involved.
The database manager uses a B+ tree structure for storing indexes where the bottom level consists of leaf nodes. The leaf nodes or pages are where the actual index key values are stored. When creating an index, you can enable those index leaf pages to be merged or reorganized online. online index reorganization is used to prevent the situation where, after much delete and update activity, many leaf pages of an index have only a few index keys left on them. In such a situation, and without online reorganization, space could only be reclaimed by an off-line reorganization of the data and index. When deciding whether to create an index with the ability to reorganize index pages online, you should consider the following:
| Note: | Pages freed after an online reorganization merge are available for re-use only for other indexes in the same table. With a full reorganization, those pages that are freed are available to other objects (when working with Database Managed Storage) or to disk space (when working with System Managed Storage). In addition, an online reorganization will not free up any non-leaf pages of the index, whereas a full reorganization will make the index as small as possible by making the index as small as possible, reducing the non-leaf and leaf pages as well as the number of levels of the index. |
See Using the CREATE INDEX Statement for more information on how to implement an index that will reorganize online.
Indexes for tables in a partitioned database are built using the same CREATE INDEX statement. They are partitioned based on the partitioning key of the table. An index on a table consists of the local indexes in that table on each node in the nodegroup. Note that unique indexes defined in a multiple partition environment must be a superset of the partitioning key.
Performance Tip: Create your indexes before using the LOAD utility if you are going to carry out the following series of tasks:
You should consider ordering the execution of tasks in the following way:
For more information on LOAD performance improvements, see System Catalog Tables.
Indexes are maintained after they are created. Subsequently, when application programs use a key value to randomly access and process rows in a table, the index based on that key value can be used to access rows directly. This is important, because the physical storage of rows in a base table is not ordered. When a row is inserted, unless there is a clustering index defined, the row is placed in the most convenient storage location that can accommodate it. When searching for rows of a table that meet a particular selection condition and the table has no indexes, the entire table is scanned. An index optimizes data retrieval without performing a lengthy sequential search.
The data for your indexes can be stored in the same table space as your table data, or in a separate table space containing index data. The table space used to store the index data is determined when the table is created (see Creating a Table in Multiple Table Spaces).
The following two sections, Using an Index and Using the CREATE INDEX Statement, provide more information on creating an index.
An index is never directly used by an application program. The decision on whether to use an index and which of the potentially available indexes to use is the responsibility of the optimizer.
The best index on a table is one that:
For a detailed discussion of how an index can be beneficial, refer to "Index Scan Concepts" in the Administration Guide, Performance.
You can create an index that will allow duplicates (a non-unique index) to enable efficient retrieval by columns other than the primary key, and allow duplicate values to exist in the indexed column or columns.
The following SQL statement creates a non-unique index called LNAME from the LASTNAME column on the EMPLOYEE table, sorted in ascending order:
CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)
The following SQL statement creates a unique index on the phone number column:
CREATE UNIQUE INDEX PH ON EMPLOYEE (PHONENO DESC)
A unique index ensures that no duplicate values exist in the indexed column or columns. The constraint is enforced at the end of the SQL statement that updates rows or inserts new rows. This type of index cannot be created if the set of one or more columns already has duplicate values.
The keyword ASC puts the index entries in ascending order by column, while DESC puts them in descending order by column. The default is ascending order.
When working with a structured type, it might be necessary to create user-defined index types. This requires a means of defining index maintenance, index search, and index exploitation functions. Refer to the SQL Reference for information on the requirements for creating an index type.
The following SQL statement creates a clustering index called INDEX1 on LASTNAME column of the EMPLOYEE table:
CREATE INDEX INDEX1 ON EMPLOYEE (LASTNAME) CLUSTER
To be effective, use clustering indexes with the PCTFREE parameter associated with the ALTER TABLE statement so that new data can be inserted on the correct pages which maintains the clustering order. Typically, the greater the INSERT activity on the table, the larger the PCTFREE value (on the table) that will be needed in order to maintain clustering. Since this index determines the order by which the data is laid out on physical pages, only one clustering index can be defined for any particular table.
If, on the other hand, the index key values of these new rows are, for example, always new high key values, then the clustering attribute of the table will try to place them at the end of the table. Having free space in other pages will do little to preserve clustering. In this case, placing the table in append mode may be a better choice than a clustering index and altering the table to have a large PCTFREE value. You can place the table in append mode by issuing: ALTER TABLE APPEND ON. See Changing Table Attributes for additional overview information on ALTER TABLE. Refer to the SQL Reference for additional detailed information on ALTER TABLE.
The above discussion also applies to new "overflow" rows that result from UPDATEs which increase the size of a row.
The MINPCTUSED clause of the CREATE INDEX statement specifies the threshold for the minimum amount of used space on an index leaf page. If this clause is used, online index reorganization is enabled for this index. Once enabled, the following considerations are used to determine if an online reorganization takes place: After a key is deleted from a leaf page of this index and a percentage of used space on the page is less than the specified threshold value, the neighboring index leaf pages are checked to determine if the keys on the two leaf pages can be merged into a single index leaf page.
For example, the following SQL statement creates an index with online index reorganization enabled:
CREATE INDEX LASTN ON EMPLOYEE (LASTNAME) MINPCTUSED=20
When a key is deleted from this index, if the remaining keys on the index page take up twenty percent or less space on the index page, then an attempt is made to delete an index page by merging the keys of this index page with those of a neighboring index page. If the combined keys can all fit on a single page, this merge is performed and one of the index pages is deleted.
The PCTFREE clause of the CREATE INDEX statement specifies the percentage of each index page to leave as free space when the index is built. Leaving more free space on the index pages will result in fewer page splits. This will reduce the need to reorganize the table in order to regain sequential index pages which increases prefetching. And prefetching is one important component that may improve performance. Again, if there are always high key values, then you will want to consider lowering the value of the PCTFREE clause of the CREATE INDEX statement. In this way there will be limited wasted space reserved on each index page.
In multiple partition databases, unique indexes must be defined as supersets of the partitioning key.
If you have a replicated summary table, its base table (or tables) must have a unique index, and the index key columns must be used in the query that defines the replicated summary table. For more information, see Replicated Summary Tables.
For intra-partition parallelism, index create performance is improved by using multiple processors for the scanning and sorting of data that is performed during index creation. The use of multiple processors is enabled by setting intra_parallel to YES(1) or ANY(-1). The number of processors used during index create is determined by the system and is not affected by the configuration parameters dft_degree or max_querydegree, by the application runtime degree, or by the SQL statement compilation degree. If the database configuration parameter index sort is NO, then index create will not use multiple processors.