Creating Database (Data) Objects


Database objects, also known as data objects, are used to control how all user data (and some system data) is stored and organized within a DB2 database. Data objects include:

  • Schemas

  • Tables

  • Views

  • Indexes

  • Aliases

  • Sequences

  • Triggers

  • User-defined data types

  • User-defined functions

  • Stored procedures

  • Packages

Schemas

Schemas are objects that are used to logically classify and group other objects in the database. Because schemas are objects themselves, they have privileges associated with them that allow the schema owner to control which users can create, alter, and drop objects within them.

Most objects in a database are named using a two-part naming convention. The first (leftmost) part of the name is called the schema name or qualifier, and the second (rightmost) part is called the object name. Syntactically, these two parts are concatenated and delimited with a period (for example, HR.EMPLOYEES). When any object that can be qualified by a schema name (such as a table, view, index, user-defined data type, user-defined function, nickname, package, or trigger) is first created, it is assigned to a particular schema based on the qualifier in its name. Figure 4-7 illustrates how a table named STAFF would be assigned to the PAYROLL schema during the table creation process.

image from book
Figure 4-7: Assigning a table object to a schema.

Some schema names are reserved and cannot be used. An example includes the names assigned to the four schemas that are automatically created when a database is created-SYSIBM, SYSCAT, SYSSTAT, and SYSFUN.

Tip 

If no schema/qualifier name is specified when an object is created, that object is assigned to the default schema, which is usually the user ID of the individual who created the object.

Schemas are implicitly created whenever a data object that has been assigned a qualifier that is different from existing schema names is created-provided the user creating the object holds IMPLICIT_SCHEMA privilege. (Unless otherwise specified, when a new database is created, the group PUBLIC is given IMPLICIT_SCHEMA privilege. This allows any user to create objects in any schema not already in existence.) Schemas can be explicitly created by executing the CREATE SCHEMA SQL statement. The basic syntax for this statement is:

 CREATE SCHEMA [SchemaName] <SQLStatement, ...> 

or

 CREATE SCHEMA AUTHORIZATION [AuthorizationName] <SQLStatement, ...> 

or

 CREATE SCHEMA [SchemaName] AUTHORIZATION [AuthorizationName] <SQLStatement, ...> 

where:

SchemaName

Identifies the name that is to be assigned to the schema to be created.

AuthorizationName

Identifies the user who is to be given ownership of the schema once it is created.

SQLStatement

Specifies one or more SQL statements that are to be executed together with the CREATE SCHEMA statement. (Only the following SQL statements are valid: CREATE TABLE, CREATE VIEW, CREATE INDEX, COMMENT ON, and GRANT).

If a schema name is specified, but no authorization name is provided, the authorization ID of the user who issued the CREATE SCHEMA statement is given ownership of the newly created schema; if an authorization name is specified but no schema name is provided, the new schema is assigned the same name as the authorization name used.

So, if you wanted to explicitly create a schema named PAYROLL and give ownership of the schema to the user DB2ADMIN, you could do so by executing a CREATE SCHEMA SQL statement that looks something like this:

 CREATE SCHEMA payroll AUTHORIZATION db2admin 

On the other hand, if you wanted to explicitly create a schema named INVENTORY, along with a table named PARTS inside the schema named INVENTORY, you could do so by executing a CREATE SCHEMA SQL statement that looks something like this:

 CREATE SCHEMA inventory CREATE TABLE parts (partno        INTEGER NOT NULL,                     description   VARCHAR(50),                     quantity      SMALLINT) 

Schemas can also be created using the Create Schema dialog, which can be activated by selecting the appropriate action from the Schemas menu found in the Control Center. Figure 4-8 shows the Control Center menu items that must be selected to activate the Create Schema dialog; Figure 4-9 shows how the Create Schema dialog looks when it is first activated.

image from book
Figure 4-8: Invoking the Create Schema dialog from the Control Center.

image from book
Figure 4-9: The Create Schema dialog.

Given that schemas can be implicitly created by creating an object and assigning it a new schema name, you may be wondering why anyone would want to explicitly create a schema using the CREATE SCHEMA statement (or the Create Schema dialog). The primary reason for explicitly creating a schema has to do with access control. An explicitly created schema has an owner, identified either by the authorization ID of the user who executed the CREATE SCHEMA statement or by the authorization ID provided to identify the owner when the schema was created. A schema owner has the authority to create, alter, and drop any object stored in the schema; to drop the schema itself; and to grant these privileges to other users. On the other hand, implicitly created schemas are considered to be owned by the user "SYSIBM." Any user can create an object in an implicitly created schema, and each object in the schema is controlled by the user who created it. Furthermore, only users with System Administrator (SYSADM) or Database Administrator (DBADM) authority are allowed to drop implicitly created schemas. Thus, in order for a user other than a system administrator or database administrator to have complete control over a schema, as well as all data objects stored in it, the schema must be created explicitly.

Tables

A table is a logical database object that acts as the main repository in a database. Tables present data as a collection of unordered rows with a fixed number of columns; each column contains values of the same data type or one of its subtypes, and each row contains a set of values for each column available. Usually, the columns in a table are logically related, and additional relationships can be defined between two or more tables. The storage representation of a row is called a record, the storage representation of a column is called a field, and each intersection of a row and column is called a value. Figure 4-10 shows the structure of a simple database table.

image from book
Figure 4-10: A simple database table.

With DB2 9, five types of tables are available:

  • Base tables: User-defined tables designed to hold persistent user data.

  • Result tables: DB2 Database Manager-defined tables populated with rows retrieved from one or more base tables in response to a query.

  • Materialized query tables: User-defined tables whose definition is based on the result of a query, and whose data is in the form of precomputed results that are taken from one or more tables upon which the materialized query table definition is based. Materialized query tables (MQTs) are used during query optimization to improve the performance of a subset of queries.

  • Declared temporary tables: User-defined tables used to hold nonpersistent data temporarily, on behalf of a single application. Declared temporary tables are explicitly created by an application when they are needed and implicitly destroyed when the application that created them terminates its last database connection.

  • Typed tables: User-defined tables whose column definitions are based on the attributes of a user-defined structured data type.

Because tables are the basic data objects used to store information, many are often created for a single database. Tables are created by executing the CREATE TABLE SQL statement. In its simplest form, the syntax for this statement is:

 CREATE TABLE [TableName] ([ColumnName] [DataType], ...) 

where:

TableName

Identifies the name that is to be assigned to the table to be created. (The table name specified must be unique within the schema the table is to be created in.)

ColumnName

Identifies the unique name (within the table definition) that is to be assigned to the column that is to be created.

DataType

Identifies the data type (built-in or user-defined) to be assigned to the column to be created; the data type specified determines the kind of data values that can be stored in the column. (Table 4-2 contains a list of valid built-in data type definitions.)

Table 4-2: Built-In Data Type Definitions That Can Be Used with the CREATE TABLE Statement
Open table as spreadsheet

Definition(s)

Data Type

SMALLINT

Numeric

INTEGER

INT

Numeric

BIGINT

Numeric

DECIMAL(Precision, Scale)

DEC(Precision, Scale)

NUMERIC(Precision, Scale)

NUM(Precision, Scale)

where Precision is any number between 1 and 31; Scale is any number between 0 and Precision

Numeric

REAL

FLOAT(Precision)

where Precision is any number between 1 and 24

Numeric

DOUBLE

DOUBLE PRECISION

FLOAT(Precision)

where Precision is any number between 25 and 53

Numeric

CHARACTER(Length) <FOR BIT DATA>

CHAR(Length) <FOR BIT DATA>

where Length is any number between 1 and 254. See note at bottom of table for the FOR BIT DATA clause.

Character/Character string

CHARACTER VARYING(MaxLength) <FOR BIT DATA>

CHAR VARYING(MaxLength) <FOR BIT DATA>

VARCHAR(MaxLength) <FOR BIT DATA>

where MaxLength is any number between 1 and 32,672. See note at bottom of table for the FOR BIT DATA clause.

Character string

LONG VARCHAR <FOR BIT DATA>

Character string

GRAPHIC(Length)

where Length is any number between 1 and 127

Double-byte character string

VARGRAPHIC(MaxLength)

where MaxLength is any number between 1 and 16,336

Double-byte character string

LONG VARGRAPHIC

Double-byte character string

DATE

Date

TIME

Time

TIMESTAMP

Date and time

BINARY LARGE OBJECT(Size <K | M | G>)

BLOB(Size <K | M | G>)

where Length is any number between 1 and 2,147,483,647; if K (for kilobyte) is specified, Length is any number between 1 and 2,097,152; if M (for megabyte) is specified, Length is any number between 1 and 2,048; if G (for gigabyte) is specified, Length is any number between 1 and 2.

Binary

CHARACTER LARGE OBJECT(Size <K | M | G>)

CHAR LARGE OBJECT(Size <K | M | G>)

CLOB(Size <K | M | G>)

where Length is any number between 1 and 2,147,483,647; if K (for kilobyte) is specified, Length is any number between 1 and 2,097,152; if M (for megabyte) is specified, Length is any number between 1 and 2,048; if G (for gigabyte) is specified, Length is any number between 1 and 2.

Character string

DBCLOB(Size <K | M | G>)

where Length is any number between 1 and 1,073,741,823; if K (for kilobyte) is specified, Length is any number between 1 and 1,048,576; if M (for megabyte) is specified, Length is any number between 1 and 1,024; if G (for gigabyte) is specified, Length is must be 1.

Double-byte character string

CHARACTER VARYING(MaxLength) <FOR BIT DATA>

CHAR VARYING(MaxLength) <FOR BIT DATA>

VARCHAR(MaxLength) <FOR BIT DATA>

where MaxLength is any number between 1 and 32,672. See note at bottom of table for the FOR BIT DATA clause.

Character string

XML

XML Document

Note: If the FOR BIT DATA option is used with any character string data type definition, the contents of the column the data type is assigned to are treated as binary data.

Thus, if you wanted to create a table named EMPLOYEES that had three columns in it, two of which are used to store numeric values and one that is used to store character string values, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE  TABLE employees      (empid  INTEGER,       name   CHAR(50),       dept   INTEGER) 

It is important to note that this is an example of a relatively simple table. Table definitions can be quite complex; therefore, the CREATE TABLE statement has several different permutations. Because the definition for a table object can be quite complex, and because the syntax for the CREATE TABLE SQL statement can be complex as well, the CREATE TABLE SQL statement is covered in much more detail in Chapter 6, "Working with DB2 Tables, Views, and Indexes." (A detailed description of the data types available is presented in Chapter 6 as well.)

Like schemas, tables can be created using a GUI tool that is accessible from the Control Center. In this case, the tool is the Create Table Wizard, and it can be activated by selecting the appropriate action from the Tables menu found in the Control Center. Figure 4-11 shows the Control Center menu items that must be selected to activate the Create Table Wizard; Figure 4-12 shows how the first page of the Create Table Wizard looks when it is first activated.

image from book
Figure 4-11: Invoking the Create Table Wizard from the Control Center.

image from book
Figure 4-12: The first page of the Create Table Wizard.

A Word About Declared Temporary Tables

Of the five different types of tables available, base tables are the most common type of table used, followed by declared temporary tables. Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them-and only for the life of the application. When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the description of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference focuses on naming conventions: base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name. Finally, while base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement.

Tip 

Before an application can create and use a declared temporary table, a user temporary table space must be defined for the database the application will be working with.

Views

Views are used to provide a different way of looking at the data stored in one or more base tables. Essentially, a view is a named specification of a result table that is populated whenever the view is referenced in an SQL statement. (Each time a view is referenced, a query is executed and the results are returned in a table-like format.) Like base tables, views can be thought of as having columns and rows. And in most cases, data can be retrieved from a view the same way it can be retrieved from a table. However, whether or not a view can be used in insert, update, and delete operations depends upon how it was defined-views can be defined as being insertable, updatable, deletable, and read-only.

Although views look similar to base tables, they do not contain real data. Instead, views refer to data stored in other base tables. Only the view definition itself is actually stored in the database. (In fact, when changes are made to the data presented in a view, the changes are actually made to the data stored in the base table(s) the view references.) Figure 4-13 shows the structure of a simple view, along with its relationship to two base tables.

image from book
Figure 4-13: A simple view that references two base tables.

Because views allow different users to see different presentations of the same data, they are often used to control access to data. For example, suppose you had a table that contained information about all employees who worked for a particular company. Department managers could be given access to this table using a view that allows them to see only information about the employees who work in their department. Members of the payroll department, on the other hand, could be given access to the table using a view that allows them to see only the information needed to generate employee paychecks. Both sets of users are given access to the same table; however, because each user works with a different view, it appears that they are working with their own tables.

Tip 

Because there is no way to grant SELECT privileges on specific columns within a table, the only way to prevent users from accessing every column in a table is by creating a result, summary, or declared temporary table that holds only the data a particular user needs, or by creating a view that contains only the table columns a user is allowed to access. Of the two, a view is easier to implement and manage.

Views can be created by executing the CREATE VIEW SQL statement. The basic syntax for this statement is:

 CREATE VIEW [ViewName] <( [ColumnName], ... )> AS [SELECTStatement] <WITH <LOCAL | CASCADED> CHECK OPTION> 

where:

ViewName

Identifies the name that is to be assigned to the view to be created.

ColumnName

Identifies the names of one or more columns that are to be included in the view to be created. If a list of column names is specified, the number of column names provided must match the number of columns that will be returned by the SELECT statement used to create the view. (If a list of column names is not provided, the columns of the view will inherit the names that are assigned to the columns returned by the SELECT statement used to create the view.)

SELECTStatement

Identifies a SELECT SQL statement that, when executed, will produce data that will populate the view.

Thus, if you wanted to create a view that references all data stored in a table named DEPARTMENT and assign it the name DEPT_VIEW, you could do so by executing a CREATE VIEW SQL statement that looks something like this:

 CREATE VIEW dept_view AS SELECT * FROM department 

On the other hand, if you wanted to create a view that references specific data values stored in the table named DEPARTMENT and assign it the name ADV_DEPT_VIEW, you could do so by executing a CREATE VIEW SQL statement that looks something like this:

 CREATE VIEW adv_dept_view AS SELECT (dept_no, dept_name, dept_size) FROM department      WHERE dept_size > 25 

The view created by this statement would contain only department number, department name, and department size information for each department that has more than 25 people in it.

Views can also be created using the Create View dialog, which can be activated by selecting the appropriate action from the Views menu found in the Control Center. Figure 4-14 shows the Control Center menu items that must be selected to activate the Create View dialog; Figure 4-15 shows how the Create View dialog looks when it is first activated.

image from book
Figure 4-14: Invoking the Create View dialog from the Control Center.

image from book
Figure 4-15: The Create View dialog.

If the WITH LOCAL CHECK OPTION clause of with the CREATE VIEW SQL statement is specified (or if the Local Check option is selected on the Create View dialog), insert and update operations performed against the view that is created are validated to ensure that all rows being inserted into or updated in the base table the view refers to conform to the view's definition (otherwise, the insert/update operation will fail). So what exactly does this mean? Suppose a view was created using the following CREATE VIEW statement:

 CREATE VIEW priority_orders AS SELECT * FROM orders WHERE response_time < 4 WITH LOCAL CHECK OPTION 

Now, suppose a user tries to insert a record into this view that has a RESPONSE_TIME value of 6. The insert operation will fail because the record violates the view's definition. Had the view not been created with the WITH LOCAL CHECK OPTION clause, the insert operation would have been successful, even though the new record would not be visible to the view that was used to add it. Figure 4-16 illustrates how the WITH LOCAL CHECK OPTION clause works.

image from book
Figure 4-16: How the WITH LOCAL CHECK OPTION clause is used to ensure that insert and update operations conform to a view's definition.

Tip 

Views created with the WITH LOCAL CHECK OPTION clause specified are referred to as symmetric views, because every record that can be inserted into them can also be retrieved from them.

If the WITH CASCADED CHECK OPTION clause of with the CREATE VIEW SQL statement is specified (or if the Cascaded Check option is selected on the Create View dialog), the view created will inherit the search conditions of the parent view upon which the view is based and treat those conditions as one or more constraints that are used to validate insert and update operations that are performed against the view. Additionally, every view created that is a child of the view that was created with the WITH CASCADED CHECK OPTION clause specified will inherit those constraints; the search conditions of both parent and child views are ANDed together to form the constraints. To understand better what this means, let's look at an example. Suppose a view was created using the following CREATE VIEW statement:

 _________________________________________________ CREATE VIEW priority_orders AS SELECT * FROM orders WHERE response_time < 4 

Now, suppose a second view was created using this CREATE VIEW statement:

 CREATE VIEW special_orders AS SELECT * FROM priority_orders WITH CASCADED CHECK OPTION 

If a user tries to insert a record into the SPECIAL_ORDERS view that has a RESPONSE_TIME value of 6, the insert operation will fail because the record violates the search condition of the PRIORITY_ORDERS view's definition (which is a constraint for the SPECIAL_ORDERS view). Figure 4-17 illustrates how the WITH CASCADED CHECK OPTION clause works.

image from book
Figure 4-17: How the WITH CASCADED CHECK OPTION clause is used to ensure insert and update operations conform to a parent view's definition.

Indexes

An index is an object that contains an ordered set of pointers that refer to rows in a base table. Each index is based upon one or more columns in the base table it refers to (known as keys), yet it is stored as separate entities. Figure 4-18 shows the structure of a simple index, along with its relationship to a base table.

image from book
Figure 4-18: A simple index.

Indexes are important because:

  • They provide a fast, efficient method for locating specific rows of data in very large tables. (In some cases, all the information needed to resolve a query may be found in the index itself, in which case the actual table data does not have to be accessed.)

  • They provide a logical ordering of the rows of a table. (When indexes are used, the values of one or more columns can be sorted in ascending or descending order; this property is very beneficial when processing queries that contain ORDER BY and GROUP BY clauses.)

  • They can be used to enforce the uniqueness of records stored in a table.

  • They can force a table to use clustering storage, which causes the rows of a table to be physically arranged according to the ordering of their index column values. (Although all indexes provide a logical ordering of data, only a clustering index provides a physical ordering of data.)

Tip 

A clustering index usually increases performance by decreasing the amount of I/O required to access data: When a logical set of rows are physically stored close together, a read operation on the set of rows will require less I/O, because adjacent rows are more likely to be found within the same extent (remember, data pages are written in batches called extents) instead of being widely distributed across multiple extents.

Whereas some indexes are created implicitly to provide support for a table's definition (for example, to provide support for a primary key), indexes are typically created explicitly, using tools available with DB2. One way to explicitly create an index is by executing the CREATE INDEX SQL statement. The basic syntax for this statement is:

 CREATE <UNIQUE> INDEX IndexName] ON [TableName] ( [PriColumnName] <ASC | DESC>, ... ) <INCLUDE ( [SecColumnName], ... )> <CLUSTER> <DISALLOW REVERSE SCANS | ALLOW REVERSE SCANS> 

where:

IndexName

Identifies the name that is to be assigned to the index to be created.

TableName

Identifies the name assigned to the base table with which the index to be created is to be associated.

PriColumnName

Identifies one or more primary columns that are to be part of the index's key. (The combined values of each primary column specified will be used to enforce data uniqueness in the associated base table.)

SecColumnName

Identifies one or more secondary columns whose values are to be stored with the values of the primary columns specified, but are not to be used to enforce data uniqueness.

If the UNIQUE clause is specified when the CREATE INDEX statement is executed, rows in the table associated with the index to be created must not have two or more occurrences of the same values in the set of columns that make up the index key. If the base table the index is to be created for contains data, this uniqueness is checked when the DB2 Database Manager attempts to create the index specified; once the index has been created, this uniqueness is enforced each time an insert or update operation is performed against the table. In both cases, if the uniqueness of the index key is compromised, the index creation, insert, or update operation will fail and an error will be generated. It is important to keep in mind that when the UNIQUE clause is used, it is possible to have an index key that contains one (and only one) NULL value.

So, if you had a base table named EMPLOYEES that has the following characteristics:

Open table as spreadsheet

Column Name

Data Type

EMPNO

INTEGER

FNAME

CHAR(20)

LNAME

CHAR(30)

TITLE

CHAR(10)

DEPARTMENT

CHAR(20)

SALARY

DECIMAL(6,2)

and you wanted to create a index such that the index key consists of the column named EMPNO and all employee numbers entered will be guaranteed to be unique, you could do so by executing a CREATE INDEX statement that looks something like this:

 CREATE UNIQUE INDEX empno_indx ON employees(empno) 

Indexes can also be created using the Create Index wizard, which can be activated by selecting the appropriate action from the Indexes menu found in the Control Center. Figure 4-19 shows the Control Center menu items that must be selected to activate the Create Indexes dialog; Figure 4-20 shows how the Create Index wizard might look when it is first activated.

image from book
Figure 4-19: Invoking the Create Index dialog from the Control Center.

image from book
Figure 4-20: The Create Index wizard.

If an index is created for an empty table, that index will not have any entries stored in it until the table the index is associated with is populated. On the other hand, if an index is created for a table that already contains data, index entries will be generated for the existing data and added to the index upon its creation. Any number of indexes can be created for a table, using a wide variety of combinations of columns. However, each index comes at a price in both storage requirements and performance. Since each index replicates its key values, and this replication requires additional storage space, and each modification to a table results in a similar modification to all indexes defined on the table, performance can decrease when insert, update, and delete operations are performed. In fact, if a large number of indexes are created for a table that is modified frequently, overall performance will decrease, rather than increase, for all operations except data retrieval. Tables that are used for data mining, business intelligence, business warehousing, and other applications that execute many (and often complex) queries while rarely modifying data are prime targets for multiple indexes. On the other hand, tables that are used in OLTP (On-Line Transactional Processing) environments or other environments where data throughput is high should use indexes sparingly.

Aliases

An alias is simply an alternate name for a table or view. (Aliases can also be created for nicknames that refer to tables or views found on federated systems.) Once created, an alias can be referenced the same way the table or view the alias refers to can be referenced. However, an alias cannot be used in every context that a table or view name can. For example, an alias cannot be used in the check condition of a check constraint, nor can it be used to reference a user-defined temporary table.

Like tables and views, an alias can be created, dropped, and have comments associated with it. Unlike tables (but similar to views), aliases can refer to other aliases-a process known as chaining. Aliases are publicly referenced names, so no special authority or privilege is required to use them. However, access to the table or view that an alias refers to still requires appropriate authorization.

Aliases can be created by executing the CREATE ALIAS SQL statement. The basic syntax for this statement is:

 CREATE [ALIAS | SYNONYM] [AliasName] FOR [TableName | ViewName | Nickname | ExistingAlias] 

where:

AliasName

Identifies the name that is to be assigned to the alias to be created.

TableName

Identifies the table, by name, that the alias to be created is to reference.

ViewName

Identifies the view, by name, that the alias to be created is to reference.

Nickname

Identifies the nickname that the alias to be created is to reference.

ExistingAlias

Identifies an existing alias, by name, that the alias to be created is to reference.

Thus, if you wanted to create an alias that references a table named EMPLOYEES and you wanted to assign it the name EMPINFO, you could do so by executing a CREATE ALIAS SQL statement that looks something like this:

 CREATE ALIAS empinfo FOR employees 

Aliases can also be created using the Create Alias dialog, which can be activated by selecting the appropriate action from the Alias menu found in the Control Center. Figure 4-21 shows the Control Center menu items that must be selected to activate the Create Alias dialog; Figure 4-22 shows how the Create Alias dialog might look when it is first activated.

image from book
Figure 4-21: Invoking the Create Alias dialog from the Control Center.

image from book
Figure 4-22: The Create Alias dialog.

So why would you want use an alias instead of the actual table/view name? By using aliases, SQL statements can be constructed such that they are independent of the qualified names that identify the base tables or views they reference.

Whenever an alias is used in an SQL statement, the behavior is the same as when the target (source table or view) of the alias is used instead. Therefore, any application that uses an alias to access data can easily be made to work with many different targets. That's because, when the target of an alias is changed, no changes to applications that use the alias are necessary.

Sequences

A sequence is an object that is used to generate data values automatically. Unlike an identity column, which is used to generate data values for a specific column in a table, a sequence is not tied to any specific column or any specific table. Instead, a sequence behaves like a unique counter that resides outside the database, with the exception that it does not introduce the concurrency and performance problems that can exist when external counters are used.

Sequences have the following characteristics:

  • Values generated can be any exact numeric data type that has a scale of zero (SMALLINT, BIGINT, INTEGER, or DECIMAL).

  • Consecutive values can differ by any specified increment value. The default increment value is 1.

  • Counter values are recoverable. Counter values are reconstructed from logs when recovery is required.

  • Values generated can be cached to improve performance.

In addition, sequences can generate values in one of three ways:

  • By incrementing or decrementing by a specified amount, without bounds

  • By incrementing or decrementing by a specified amount to a user-defined limit and stopping

  • By incrementing or decrementing by a specified amount to a user-defined limit, and then cycling back to the beginning and starting again

To facilitate the use of sequences in SQL operations, two expressions are available: PREVIOUS VALUE and NEXT VALUE. The PREVIOUS VALUE expression returns the most recently generated value for the specified sequence, while the NEXT VALUE expression returns the next sequence value.

Tip 

One important difference between a sequence and an identity column is that an identity column cannot be referenced by an INSERT SQL statement; a sequence, on the other hand, can.

Sequences can be created by executing the CREATE SEQUENCE SQL statement. The basic syntax for this statement is:

 CREATE SEQUENCE [SequenceName] <AS INTEGER | AS [DataType]> <START WITH [StartingNumber]> <INCREMENT BY [1 | IncrementValue]> <NO MINVALUE | MINVALUE [MinValue]> <NO MAXVALUE | MAXVALUE [MaxValue]> <NO CYCLE | CYCLE> <CACHE 20 | CACHE [CacheValue] | NO CACHE> <NO ORDER | ORDER> 

where:

SequenceName

Identifies the name that is to be assigned to the sequence to be created.

DataType

Identifies the data type to be used for the sequence's value. The data type specified can be any built-in numeric type with a scale of zero (SMALLINT, INTEGER, BIGINT, or DECIMAL), or a user-defined data type whose source is a built-in numeric type with a scale of zero.

StartingValue

Identifies the first value to be provided by the sequence being created.

IncrementValue

Identifies the interval that is to be used to calculate the next consecutive value for the sequence being created.

MinValue

Specifies the minimum value at which a descending sequence either cycles or stops generating values, or an ascending sequence cycles to after reaching the maximum value.

MaxValue

Specifies the maximum value at which an ascending sequence either cycles or stops generating values, or a descending sequence cycles to after reaching the minimum value. (It is possible for MaxValue to be equal to MinValue; if that is the case, the sequence will return a constant value.)

CacheSize

Identifies the number of values of the identity sequence that are to be pre-generated and kept in memory. (Pregenerating and storing values in the cache reduces synchronous I/O to the log when values are generated for the sequence. However, in the event of a system failure, all cached sequence values that have not been used in committed statements are lost; that is, they can never be used.)

Thus, if you wanted to create a sequence that generates numbers, starting with the number 100 and incrementing each subsequent number produced by 10, you could do so by executing a CREATE SEQUENCE SQL statement that looks something like this:

 CREATE SEQUENCE emp_id START WITH 100 INCREMENT BY 10 

Once this sequence is created, the first value returned by it will be 100. Subsequent values will be 110, 120, 130, 140, and so forth. Values are generated each time the sequence is queried (for example, by executing the statement VALUES NEXT VALUE FOR emp_id INTO :empid) regardless of which user/application performs the query.

On the other hand, if you wanted to create a sequence that generates numbers, starting with the number 5, incrementing each subsequent number produced by 5, and caching 5 numbers at a time, you could do so by executing a CREATE SEQUENCE SQL statement that looks something like this:

 CREATE SEQUENCE dept_id START WITH 5 INCREMENT BY 5 CACHE 5 

In this case, the numbers returned by the sequence can vary, depending upon how the sequence is used. For example, if three different transactions were to access the sequence, because five values are generated at a time, the first transaction would get the numbers 5, 10, 15, 20, and 25; the second transaction would get the numbers 30, 35, 40, 45, and 50; while the third transaction would get the numbers 55, 60, 65, 70, and 75. If any one of these transactions terminates without using the full set of numbers available, those numbers are lost. (Because sequences are persistent across transaction boundaries, their behavior is not affected by commit or rollback operations.)

Triggers

A trigger is used to define a set of actions that are to be executed whenever an insert, update, or delete operation is performed against a table or updatable view. Triggers are often used to enforce data integrity rules and business rules. (A data integrity rule might be that whenever the record for an employee is deleted from the table that holds employee information, the corresponding record will be deleted from the table that holds payroll information; a business rule might be that an employee's salary cannot be increased by more than 10 percent.) Triggers can also be used to update other tables, automatically generate or transform values for inserted or updated rows, and invoke functions to perform such tasks as issuing alerts.

By using triggers, the logic needed to enforce business rules can be placed directly in the database, and applications that work with the database can concentrate solely on data storage, management, and retrieval. And by storing the logic needed to enforce data integrity and business rules directly in the database, it can be modified as those rules change without requiring applications to be recoded and recompiled.

Before a trigger can be created, the following components must be identified:

  • Subject table/view: The table or view with which the trigger is to interact.

  • Trigger event: An SQL operation that causes the trigger to be activated whenever the event is performed against the subject table/view. This operation can be an insert operation, an update operation, or a delete operation.

  • Trigger activation time: Indicates whether the trigger should be activated before, after, or instead of the trigger event. A BEFORE trigger will be activated before the trigger event occurs; therefore, it will be able to see new data values before they are inserted into the subject table. An AFTER trigger will be activated after the trigger event occurs; therefore, it can see only data values that have already been inserted into the subject table. An INSTEAD OF trigger will replace the trigger event made against the subject view. (A BEFORE trigger might be used to trap and process unwanted values, while an AFTER trigger could be used to copy data values entered to other tables or views.)

  • Set of affected rows: The rows of the subject table/view that are being inserted, updated, or deleted.

  • Trigger granularity: Specifies whether the actions the trigger will perform are to be performed once for the entire insert, update, or delete operation or once for every row affected by the insert, update, or delete operation.

  • Triggered action: An optional search condition and a set of SQL statements that are to be executed whenever the trigger is activated. (If a search condition is specified, the SQL statements will only be executed if the search condition evaluates to TRUE.) If the trigger is a BEFORE trigger, the triggered action can include statements that retrieve data, set transition variables, or signal SQL states. If the trigger is an AFTER trigger, the triggered action can include statements that retrieve data, insert records, update records, delete records, or signal SQL states.

Triggered actions can refer to the values in the set of affected rows using what are known as transition variables. Transition variables use the names of the columns in the subject table, qualified by a specified name that indicates whether the reference is to the original value (before the insert, update, or delete operation is performed) or the new value (after the insert, update, or delete operation is performed). Another means of referring to values in the set of affected rows is through the use of transition tables. Transition tables also use the names of the columns in the subject table, but they allow the complete set of affected rows to be treated as a table. Transition tables can only be used in after triggers.

Once the appropriate trigger components have been identified, a trigger can be created by executing the CREATE TRIGGER SQL statement. The basic syntax for this statement is:

 CREATE TRIGGER [TriggerName] [<NO CASCADE> BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE <OF [ColumnName], ... >] ON [TableName | ViewName] <REFERENCING [Reference]> [FOR EACH ROW | FOR EACH STATEMENT] <WHEN ( [SearchCondition] )> [TriggeredAction] 

where:

TriggerName

Identifies the name to be assigned to the trigger to be created.

ColumnName

Identifies one or more columns in the subject table or view of the trigger whose values must be updated before the trigger's triggered action (TriggeredAction) will be executed.

TableName

Identifies, by name, the subject table of the BEFORE or AFTER trigger to be created.

ViewName

Identifies, by name, the subject view of the INSTEAD OF trigger to be created.

Reference

Identifies one or more transition variables and/or transition tables that are to be used by the trigger's triggered action (TriggeredAction). The syntax used to create transition variables and/or transition tables that are to be used by the trigger's triggered action is:

 <OLD <AS> [CorrelationName]> <NEW <AS> [CorrelationName]> <OLD TABLE <AS> [Identifier]> <NEW TABLE <AS> [Identifier]> 

 

where:

 

CorrelationName

Identifies a name to be used to identify a specific row in the subject table of the trigger, either before it was modified by the trigger's triggered action (OLD <AS>) or after it has been modified by the trigger's triggered action (NEW <AS>).

 

Identifier

Identifies a name that is to be used to identify a temporary table that contains a set of rows found in the subject table of the trigger, either before they were modified by the trigger's triggered action (OLD TABLE <AS>) or after they have been modified by the trigger's triggered action (NEW TABLE <AS>).

 

Each column affected by an activation event (insert, update, or delete operation) can be made available to the trigger's triggered action by qualifying the column's name with the appropriate correlation name or table identifier.

SearchCondition

Specifies a search condition that, when evaluated, will return either TRUE, FALSE, or Unknown. This condition is used to determine whether the trigger's triggered action (TriggeredAction) is to be performed.

TriggeredAction

Identifies the action to be performed when the trigger is activated. The triggered action must consist of one or more SQL statements; when multiple statements are specified, the first statement must be preceded by the keywords BEGIN ATOMIC, the last statement must be followed by the keyword END, and every statement between these keywords must be terminated with a semicolon (;).

Thus, suppose you have a base table named EMPLOYEES that has the following characteristics:

Open table as spreadsheet

Column Name

Data Type

EMPNO

INTEGER

FNAME

CHAR(20)

LNAME

CHAR(30)

TITLE

CHAR(10)

DEPARTMENT

CHAR(20)

SALARY

DECIMAL(6,2)

If you wanted to create a trigger for EMPLOYEES that will cause the value for the column named EMPNO to be incremented each time a row is added to the table, you could do so by executing a CREATE TRIGGER statement that looks something like this:

 CREATE TRIGGER empno_inc AFTER INSERT ON employees FOR EACH ROW UPDATE empno SET empno = empno + 1 

Triggers can also be created using the Create Trigger dialog, which can be activated by selecting the appropriate action from the Triggers menu found in the Control Center. Figure 4-23 shows the Control Center menu items that must be selected to activate the Create Trigger dialog; Figure 4-24 shows how the Create Trigger dialog might look when it is first activated.

image from book
Figure 4-23: Invoking the Create Trigger dialog from the Control Center.

image from book
Figure 4-24: The Create Trigger dialog.

It is important to note that if you want a trigger to be fired whenever a Data Manipulation Language (DML) operation is performed against a table, you must create three separate triggers for that table: one that handles INSERT events, one that handles UPDATE events, and another that handles DELETE events.

Tip 

The activation of one trigger may result in the activation of other triggers (or even reactivation of the same trigger). This behavior is referred to as trigger cascading, and because trigger cascading can occur, a single insert, update, or delete operation can cause a significant number of changes to be made to a database.

User-Defined Data Types

As the name implies, user-defined data types (UDTs) are data types that are created (and named) by database users. With DB2 9, two types of UDTs are available: distinct and structured.

Distinct Data Types

A distinct data type is a user-defined data type that is derived from one of the built-in data types available with DB2. Although a distinct data type shares a common internal representation with a built-in data type, it is considered a separate data type that is distinct from any other data type (hence, the "distinct" in the name). And even though distinct data types share the same representation as other built-in types, the DB2 Database Manager guarantees that strong data typing exists, which means that the value of a user-defined data type is compatible only with values of that same type. As a result, user-defined data types cannot be used as arguments for most of the built-in functions available. (Similarly, a built-in data type cannot be used in arguments or operands designed to use a distinct data type.) Instead, user-defined functions (or methods) that provide similar functionality must be developed when that kind of capability is needed.

Distinct user-defined data types can be created by executing the CREATE DISTINCT TYPE SQL statement. The basic syntax for this statement is:

 CREATE DISTINCT TYPE [TypeName] AS [SourceDataType] <WITH COMPARISONS> 

where:

TypeName

Identifies the name that is to be assigned to the distinct data type to be created.

SourceDataType

Identifies the built-in data type that the distinct data type to be created is to be based on. (Table 4-2 contains a list of valid built-in data type definitions.)

Thus, if you wanted to create a distinct data type named CURRENCY that can be used to store numeric data, you could do so by executing a CREATE DISTINCT TYPE SQL statement that looks something like this:

 CREATE DISTINCT TYPE currency AS NUMERIC(7,2) WITH COMPARISONS 

Distinct data types can also be created using the Create Distinct Type dialog, which can be activated by selecting the appropriate action from the User Defined Distinct Datatypes menu found in the Control Center. Figure 4-25 shows the Control Center menu items that must be selected to activate the Create Distinct Type dialog; Figure 4-26 shows how the Create Distinct Type dialog might look when it is first activated.

image from book
Figure 4-25: Invoking the Create Distinct Type dialog from the Control Center.

image from book
Figure 4-26: The Create Distinct Type dialog.

When a distinct data type is created, six comparison functions (named =, <>, <, <=, >, and >=) may also be created. (Because BLOB, CLOB, DBCLOB, LONG VARCHAR and LONG VARGRAPHIC data types cannot be compared, comparison functions cannot be created for distinct data types that are based on these data types.) These functions allow two instances of the distinct data type to be compared in the same manner as any two values of the same built-in data type can be compared. Thus, the ORDER BY, GROUP BY, and DISTINCT clauses of a SELECT SQL statement can be used with most columns that have been defined using a distinct data type.

Two casting functions are also generated when a distinct data type is created. These functions are used to convert data between the new distinct data type and the built-in data type on which the distinct data type is based. The name of the casting function that will convert a built-in data type value to a distinct data type value is the same as that of the distinct data type itself. Thus, if a distinct data type named EMPID that is based on an INTEGER built-in data type is created, the DB2 Database Manager automatically generates the casting functions EMPID(INTEGER) and INTEGER(EMPID). Both casting functions are extremely efficient because the distinct data type and the built-in type that the functions are based on share the same representation, and no real work is needed to convert values from one data type to the other.

Structured Data Types

A structured data type is a user-defined data type that contains one or more attributes, each of which has a name and data type of its own. A structured data type often serves as the data type of a typed table or view, in which case each column of the table or view derives its name and data type from an attribute of the structured data type. A structured data type can also be created as a subtype of another structured type (referred to as its supertype). In this case, the subtype inherits all the attributes of the supertype and can optionally add additional attributes of its own.

Just as six comparison functions and two casting functions are automatically created when a distinct data type is created, six comparison functions (also called =, <>, <, <=, >, and >=) and two casting functions can be created when a structured data type is created. (By default, the automatic creation of these functions is suppressed.) When created, these comparison functions are used to compare references to the structured data type, but not to the data type values themselves. Likewise, the two casting functions are used to cast between the generated reference type of the structured data type and the underlying data type that the structured data type uses as a reference type. Structured data types are created by executing the CREATE TYPE SQL statement.

User-Defined Functions (or Methods)

User-defined functions (UDFs) are special objects that are used to extend and enhance the support provided by the built-in functions available with DB2 9. Like user-defined data types, user-defined functions (or methods) are created and named by a database user. However, unlike DB2's built-in functions, user-defined functions can take advantage of system calls and DB2's administrative APIs, thereby providing more synergy between applications and databases.

Five types of user-defined functions can be created:

  • Sourced (or Template). A sourced function is constructed from a function that is already registered with a database (referred to as the source function). Sourced functions can be columnar, scalar, or table in nature or they can be designed to overload a specific operator such as +, -, *, and /. When a sourced function is invoked, all arguments passed to it are converted to the data types that are expected by the underlying source function, and the source function itself is invoked. Upon completion, the source function performs any conversions necessary on the results produced and returns them to the calling SQL statement. The most common use of sourced functions is to enable a user-defined distinct data type to inherit selectively some of the semantics of the built-in data type on which it is based.

  • SQL Scalar, Table, or Row. Although a sourced function is constructed from a function that already exists, an SQL function is constructed from the ground up, using only SQL statements. An SQL function can be scalar in nature (scalar functions return a single value and can be specified in an SQL statement wherever a regular expression can be used) or can return a single row or an entire table.

  • External Scalar. An external scalar function is a function that is written using a high-level programming language such as C, C++, or Java and returns a single value. The function itself resides in an external library and is registered in the database, along with any related attributes.

  • External Table. Like external scalar functions, external table functions are written using a high-level programming language. But where an external scalar function returns a single value, an external table function returns a result data set, in the form of a table, to the SQL statement that references it. External table functions are powerful because they enable you to make almost any source of data appear to be a DB2 base table; the result data set returned can be used in join operations, grouping operations, set operations (for example, UNIONs), or any other operation that can be applied to a read-only view. Again, the function itself resides in an external library and is registered in the database, along with any related attributes.

  • OLE DB External Table. Like external table functions, external Object Linking and Embedding (OLE) DB table functions are written using a high-level programming language and return a result data set, in the form of a table, to SQL statements that reference them. However, with OLE DB table functions, a generic built-in OLE DB consumer can be used to interface with any OLE DB provider to access data; you need only to register an OLE DB table function and refer to the appropriate OLE DB provider as the data source. No additional programming is needed. OLE DB is designed to provide access to all types of data in an OLE Component Object Model (COM) environment. Again, the function resides in an external library and is registered in the database, along with any related attributes.

User-defined functions are created (or registered) by executing the CREATE FUNCTION SQL statement. Several flavors of this statement are available, and the appropriate form to use is determined by the type of user-defined function to be created.

Stored Procedures

In a basic DB2 client/server environment, each time an SQL statement is executed against a remote database, the statement itself is sent through a network from the client workstation to a database server. The database server then processes the statement, and the results are sent back, again through the network, to the client workstation. This means that two messages must go through the network for every SQL statement executed. Client/server application development focuses on breaking an application into two separate parts, storing those parts on two different platforms (the client and the server), and having them communicate with each other as the application executes. This allows the code that interacts directly with a database to reside on a database server, where computing power and centralized control can be used to provide quick, coordinated data access. At the same time, the application logic can reside on one or more client workstations so that it can make effective use of all the resources that a workstation has to offer without causing a bottleneck at the server.

If you have an application that contains one or more transactions that perform a relatively large amount of database activity with little or no user interaction, those transactions can be stored on the database server as a stored procedure. When a stored procedure is used, all database processing done by the transaction can be performed directly at the database server. And, because a stored procedure is invoked by a single SQL statement, fewer messages have to be transmitted across the network-only the data that is actually needed at the client workstation has to be sent across. It is important to note that a stored procedure can be invoked by a user-defined function, and a user-defined function can be invoked as part of a stored procedure.

Just as there are different types of user-defined functions available, there are different types of stored procedures available. They are SQL and external.

Tip 

User-defined functions and stored procedures are the only "executable" database objects available. For this reason, both are often referred to as "routines" or "routine objects."

SQL Stored Procedures

As the name implies, an SQL stored procedure is composed entirely of SQL statements. SQL stored procedures are created by executing one form of the CREATE PROCEDURE SQL statement. (Two forms of this statement are available; we'll look at the other form a little later.) The basic syntax for the form of the CREATE PROCEDURE statement that is used to create an SQL stored procedure looks something like this:

 CREATE PROCEDURE [ProcedureName] ( [ParamType] [ParamName] [DataType], ...) <SPECIFIC [SpecificName]> <DYNAMIC RESULT SETS [NumResultSets]> <MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA> <DETERMINISTIC | NOT DETERMINISTIC> <CALLED ON NULL INPUT> <INHERIT SPECIAL REGISTERS> <[OLD | NEW] SAVEPOINT LEVEL> <LANGUAGE SQL> <<NO> EXTERNAL ACTION> [SQLStatements] 

where:

ProcedureName

Identifies the name that is to be assigned to the procedure to be created.

ParamType

Indicates whether the parameter identified by ParamName is an input parameter (IN), an output parameter (OUT), or both an input and an output parameter (INOUT).

ParamName

Identifies the name to be assigned to a procedure parameter.

DataType

Identifies the type of data the procedure expects to receive or send for the parameter identified by ParamName.

SpecificName

Identifies the specific name to be assigned to the procedure. This name can be used later to comment on the procedure or drop the procedure; however, it cannot be used to invoke the procedure.

NumResultSets

Specifies whether the procedure being created will return one or more result data sets and if so, how many. (The default value is 0.)

SQLStatement

Specifies an SQL statement or a compound SQL statement (i.e., two or more SQL statements enclosed with the keywords BEGIN ATOMIC and END and terminated with a semicolon) that is to be executed when the procedure is invoked.

Tip 

When a specific name is assigned to a procedure, the procedure can be dropped by referencing the specific name in a special form of the DROP SQL statement (DROP SPECIFIC PROCEDURE [SpecificName]). However, if no specific name is assigned to a procedure, both the procedure name and the procedure's signature (a list of the data types used by each of the procedure's parameters) must be provided as input to the DROP PROCEDURE statement.

Thus, a simple SQL stored procedure could be created by executing a CREATE PROCEDURE statement that looks something like this:

 CREATE PROCEDURE get_sales    (IN quota INTEGER, OUT retcode CHAR(5))    DYNAMIC RESULT SETS 1    LANGUAGE SQL    BEGIN       DECLARE sqlstate CHAR(5);       DECLARE sales_results CURSOR WITH RETURN FOR          SELECT sales_person, SUM(sales) AS total_sales          FROM sales          GROUP BY sales_person          HAVING SUM(sales) > quota;       DECLARE EXIT HANDLER FOR SQLEXCEPTION          SET retcode = sqlstate;       OPEN sales_results;       SET retcode = sqlstate;    END 

The resulting SQL stored procedure, called GET_SALES, accepts an integer input value (in an input parameter called QUOTA) and returns a character value (in an output parameter called RETCODE) that reports the procedure's success or failure. The procedure body consists of a compound SQL statement that returns a result data set (i.e., an open cursor) containing the name and total sales figures for each salesperson whose total sales exceed the quota specified. This is done by:

  1. Indicating the SQL procedure is to return a result data set by specifying the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE statement and assigning it the value 1.

  2. Declaring a cursor within the procedure body (using the WITH RETURN FOR clause) for the result data set that is to be returned. (A cursor is a named control structure that points to a specific row within a result data set and is used to retrieve values for each row in the set.)

  3. Opening the cursor (which produces the result data set that is to be returned).

  4. Leaving the cursor open when the SQL procedure ends.

It is important to note that when an SQL stored procedure is used to implement a business rule, the logic used to apply that business rule can be incorporated into any application simply by invoking the stored procedure. Thus, the same business rule logic is guaranteed to be enforced across all applications. When business rules change, only the logic in the SQL stored procedure needs to be changed; applications that call the procedure do not have to be modified. (The same can be said for external stored procedures, but the steps required to change the business logic coded in an external stored procedure are a little more complex.)

External Stored Procedures

An external stored procedure is a stored procedure that is written using a high-level programming language such as C, C++, Java, or COBOL. Whereas SQL procedures offer rapid application development and considerable flexibility, external stored procedures can be much more powerful than SQL stored procedures because they can take advantage of system calls and administrative APIs, as well as execute SQL statements. However, this increase in functionality makes them more difficult to create; to create any external procedure, the following steps must be performed:

  1. Construct the body of the procedure, using a supported high-level programming language.

  2. Compile the procedure.

  3. Link the procedure to create a library (or dynamic-link library).

  4. Debug the procedure and repeat steps 2 through 4 until all problems have been resolved.

  5. Physically store the library containing the procedure on the database server. By default, the DB2 Database Manager looks for external user-defined functions in the /sqllib/function and /sqllib/function/unfenced subdirectories (\sqllib\function and \sqllib\function\unfenced subdirectories on Windows). Additionally, the system permissions for the library file containing the procedure must be modified so that all users can execute it. For example, in a UNIX environment, the chmod command is used to make a file executable; in a Windows environment, the attrib command is used for the same purpose.

  6. Register the procedure with a DB2 database using the appropriate form of the CREATE PROCEDURE SQL statement.

The basic syntax for the form of the CREATE PROCEDURE statement that is used to register an external stored procedure (once it has been created as outlined above) looks something like this:

 CREATE PROCEDURE [ProcedureName] ( [ParamType] [ParamName] [DataType], ...) <SPECIFIC [SpecificName]> <DYNAMIC RESULT SETS [NumResultSets]> <NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA > <DETERMINISTIC | NOT DETERMINISTIC> <CALLED ON NULL INPUT> <[OLD | NEW] SAVEPOINT LEVEL> LANGUAGE [C | JAVA | COBOL | CLR | OLE] EXTERNAL <NAME [ExternalName] | [Identifier]> <FENCED <THREADSAFE | NOT THREADSAFE> | NOT FENCED <THREADSAFE>> PARAMETER STYLE [DB2GENERAL | DB2SQL | GENERAL |     GENERAL WITH NULLS | JAVA | SQL] <PROGRAM TYPE [SUB | MAIN]> <DBINFO | NO DBINFO> 

where:

ProcedureName

Identifies the name to be assigned to the procedure to be registered.

ParamType

Indicates whether the parameter identified by ParamName is an input parameter (IN), an output parameter (OUT), or both an input parameter and an output parameter (INOUT).

ParamName

Identifies the name to be assigned to a procedure parameter.

DataType

Identifies the type of data the procedure expects to receive or send for the parameter identified by ParamName.

SpecificName

Identifies the specific name to be assigned to the procedure. This name can be used later to comment on or drop the procedure; however, it cannot be used to invoke the procedure.

NumResultSets

Specifies whether the procedure being registered will return one or more result data sets and if so, how many. (The default value is 0.)

SQLStatements

Specifies one or more SQL statements that are to be executed when the procedure is invoked. These statements make up the body of the procedure.

ExternalName

Identifies the name of the library, along with the name of the function in the library, that contains the executable code for the procedure being registered.

Identifier

Identifies the name of the library that contains the executable code of the procedure being registered, but only if the procedure was written using C or C++. The DB2 Database Manager will look for a function that has the same name as the library name specified.

Many of the clauses used with this form of the CREATE PROCEDURE SQL statement are similar to those used by the form of the CREATE PROCEDURE SQL statement that is used to create an SQL procedure; others are new. The meaning of these clauses are as follows:

  • <NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA>:This clause is used to identify which types of SQL statements have been coded in the body of the external stored procedure. Four different values are available:

    • NO SQL. The body of the stored procedure either does not contain any SQL or contains only nonexecutable SQL statements. (Examples of nonexecutable SQL statements are the INCLUDE and WHENEVER statements.)

    • CONTAINS SQL. The body of the stored procedure contains executable SQL statements that neither read nor modify data.

    • READS SQL DATA. The body of the stored procedure contains executable SQL statements that read but do not modify data.

    • MODIFIES SQL DATA. The body of the stored procedure contains executable SQL statements that both read and modify data.

  • <DETERMINISTIC | NOT DETERMINISTIC>: This clause is used to identify whether the procedure will always return the same results when passed the same parameter values (DETERMINISTIC) or not (NOT DETERMINISTIC). (A stored procedure that applies a 15% increase to any value passed to it would be considered DETERMINISTIC, whereas a stored procedure that generates a unique ID using the TIMESTAMP_ISO() function would be considered NOT DETERMINISTIC.)

  • <CALLED ON NULL INPUT>: When this clause is used, the stored procedure will always be invoked, even if a null value is passed for one or more of its input parameters.

  • <[OLD | NEW] SAVEPOINT LEVEL>: This clause is used to indicate whether or not the procedure is to establish a new "save point" within the transaction that invoked it. (Savepoints are used to shorten rollback recovery windows.)

  • EXTERNAL <NAME [ExternalName] | [Identifier]>: This clause is used to identify two things: the name of the library and, optionally, the name of the function within the library that contains the executable code for the procedure being registered. The high-level programming language used to construct the body of any external stored procedure determines how these names are provided. For example, if an external stored procedure is developed using the C or C++ programming language, the names of the library and function within the library that contains the body of the function can be specified in four different ways:

    • ' LibraryName '

    • ' LibraryName ! FunctionName '

    • ' AbsolutePath '

    • ' AbsolutePath ! FunctionName '

    If a library name is provided instead of an absolute path, the DB2 Database Manager will look in the /sqllib/function and /sqllib/function/unfenced subdirectories (\sqllib\function and \sqllib\function\unfenced subdirectories on Windows) for the library name specified. On the other hand, if an absolute path is provided, the name of the library must be appended to the path, and the DB2 Database Manager will look in the location specified for the appropriate library. (If neither a library name nor an absolute path is provided, the DB2 Database Manager will look in the default subdirectories shown earlier for a library and function that have the same name as the name that is to be assigned to the user-defined function being registered.) If a function name is provided, the DB2 Database Manager will look for a function that has the name specified within the library specified; if no function name is provided, the DB2 Database Manager will look for a function that has the same name as the library name specified.

  • <FENCED | NOT FENCED>: This clause is used to identify whether the external stored procedure is considered "safe" enough to be run in the DB2 Database Manager operating environment's process/address space (NOT FENCED), or not (FENCED). If the FENCED clause is specified (or if neither clause is specified), the DB2 Database Manager will not allow the procedure to access its internal resources.

  • PARAMETER STYLE [DB2GENERAL | DB2SQL | GENERAL | GENERAL WITH NULLS | JAVA | SQL]>: This clause is used to identify the parameter-passing style that the procedure expects the calling application to use when passing values to it. As you can see, there are six parameter-passing styles available:

    • DB2GENERAL. Values are passed and returned using the calling conventions that are used to call a method in a Java class. (This style can only be used when the procedure is written using Java.)

    • DB2SQL. Values are passed and returned using calling conventions that are defined in the SQL/Persistent Stored Modules ISO working draft; along with the arguments identified, the following are passed to the procedure when it is called: a null indicator for each parameter passed, a placeholder for the SQLSTATE to be returned in, the qualified name of the procedure, the specific name of the procedure, and a placeholder for the SQL diagnostic string to be returned in. (This style can only be used when the procedure is written using C/C++, COBOL, or OLE.)

    • GENERAL. Values are passed and returned exactly as they are specified when the procedure is invoked. (This style can only be used when the procedure is written using C/C++ or COBOL.)

    • GENERAL WITH NULLS. Same as GENERAL, with one major difference-an additional argument containing a vector of null indicators is also passed to and returned from the procedure. (This style can only be used when the procedure is written using C/C++ or COBOL.)

    • JAVA. Values are passed and returned using calling conventions that conform to the Java language and SQLJ specifications. (This style can only be used when the procedure is written using Java.)

    • SQL. Same as DB2SQL.

  • <PROGRAM TYPE [SUB | MAIN]>: This clause is used to identify whether the procedure was defined as a main routine (MAIN) or as a subroutine (SUB). If the PROGRAM TYPE SUB clause is specified, the DB2 Database Manager will pass all parameter values to the stored procedure as separate arguments. In this case, the stored procedure can be assigned any name that conforms to the function-naming conventions allowed by the high-level programming language used. On the other hand, if the PROGRAM TYPE MAIN clause is specified, the DB2 Database Manager will pass all parameter values to the stored procedure as a combination of an argument counter and an array of argument values. In this case, the DB2 Database Manager expects the name of the stored procedure to be "main."

  • <DBINFO | NO DBINFO>: This clause is used to identify whether information known by DB2 is to be passed to the stored procedure as an additional argument when it is invoked (DBINFO) or not (NO DBINFO). If the DB2INFO clause is used, the DB2 Database Manager will pass a data structure that contains the following information to the stored procedure at the time it is invoked:

    • The name of the currently connected database

    • The unique application ID that is established for each connection to the database

    • The application run-time authorization ID

    • The database code page

    • The version, release, and modification level of the database server invoking the stored procedure

    • The operating system being used by the server

Thus, if you wanted to register an external stored procedure named EXTRACT_RESUME that is stored as a function (written in C) named ExResume in a library named SProc that resides in the directory C:\StoredProcs, you could do so by executing a CREATE PROCEDURE SQL statement that looks something like this:

 EXEC SQL CREATE PROCEDURE extract_resume     (IN filename  VARCHAR(255),      IN empno     CHAR(6)) SPECIFIC extract_resume DYNAMIC RESULT SETS 0 EXTERNAL NAME 'C:\StoredProcs\SProc!ExResume' LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC FENCED CALLED ON NULL INPUT PROGRAM TYPE SUB; 

When this particular CREATE PROCEDURE SQL statement is executed, an external stored procedure will be registered that:

  • Has been assigned the name EXTRACT_RESUME

  • Has one input parameter called FILENAME that expects a VARCHAR(255) data value and another input parameter called EMPNO that expects a CHAR(6) data value

  • Has been assigned the specific name EXTRACT_RESUME

  • Does not return a result data set

  • Was constructed using the C or C++ programming language

  • Expects calling applications to use the GENERAL style when passing parameters

  • Will always return the same results if called with the same parameter values

  • Is to be run outside the DB2 Database Manager operating environment's address space

  • Is to be called, even if a null value has been provided for one of its parameters

  • Was written as a subroutine

Packages

A package is an object that contains the information needed to process SQL statements associated with a source code file of an application program. High-level programming language compilers do not recognize, and therefore cannot interpret, SQL statements. Therefore, when SQL statements are embedded in a high-level programming language source code file, they must be converted to source code that a high-level programming language compiler can understand; this conversion process is performed by a tool known as the SQL precompiler. (An SQL precompiler is included with the DB2 Software Development Kit and is normally invoked from the Command Line Processor or a batch/make utility file.)

During the precompile process, a source code file containing embedded SQL statements is converted into a source code file that is made up entirely of high-level programming language statements. At the same time, a corresponding package that contains, among other things, the access plans that will be used to process each SQL statement embedded in the source code file is also produced. (Access plans contain optimized information that the DB2 Database Manager uses to execute SQL statements.) This package must reside in a DB2 database that contains the data objects referenced by the package before the corresponding application can be executed against that database.

The process of creating and storing a package in a DB2 database is known as binding, and by default, packages are automatically bound to a database during the precompile process. However, by specifying the appropriate precompiler options, you can elect to store the steps needed to create a package in a file and complete the binding process at a later point in time, using a tool known as the SQL Binder (or simply the Binder). This approach is known as deferred binding.




DB2 9 Fundamentals Certification Study Guide
DB2 9 Fundamentals: Certification Study Guide
ISBN: 1583470727
EAN: 2147483647
Year: 2007
Pages: 93

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net