Abstract or structured data types are type mechanisms for modeling and storing complex objects in a relational database. Structured types may have multiple fields embedded within them, for example, a geometric shape (a list of Cartesian coordinates), or an employee (name, address, gender, birth date, and employee number) can be modeled and stored in DB2 Universal Database.
Structured type support has been extended to provide the ability to create tables with structured type columns. Additionally, structured types can be nested within a structured type. This means that the attributes of a structured type are no longer restricted to the base SQL types, they can now be of another structured type.
In Version 7, you can define functions with input parameters or parameters on the RETURNS clause that are structured types. You can also define methods for each structured data type, which permit the encapsulation of behavior with data. A method is defined very much like a function, but its use is strictly associated with structured types. It is essentially a routine with a structured type instance as its implicit first argument.
The Reorganize Table (REORG) and the db2look utilities can be used on tables with structured type columns. See the Command Reference for more information on REORG and the Administration Guide: Implementation and Administration Guide: Performance for more information on structured types and db2look.
Transform functions enable the use of structured type columns with user written programs. Transform functions convert the complex structure within a structured data type into an ordered set of its base SQL types. They also convert the base attributes back to their structured types. These transforms are required to move structured types in and out of a database. See the Administration Guide: Implementation for more information.
An SQL-bodied function contains simple SQL procedure statements within its body, which is embedded into the calling SQL (similar to a macro). This allows the query compiler to optimize the whole SQL statement including the SQL-bodied function. Structured data types make use of SQL-bodied functions for their transform functions (see Transform Functions) and methods (see Structured Types).
DB2 Version 7.2 provides dynamic compound statements, a new type of compound SQL, to help in reducing the database manager overhead and improve performance in handling requests over the network. Dynamic compound statements are ideal for short scripts involving little control flow logic, but significant data flow.
Within the dynamic compound statement, you can:
DB2 compiles the dynamic compound statement as a single statement. See the DB2 Release Notes for more information.
DB2 Version 7.2 provides the ability to perform procedural logic in stored procedures, triggers, and SQL functions through a number of SQL-controlled statements.
Prior to Version 7.2, triggers provided only simple sequencing - there was no conditional logic or loops. This enhancement to triggers allows you to migrate your applications to DB2 more easily. You will also benefit from the enhancement to SQL functions, such as SCALAR, TABLE, or ROW. For example, you can either use SQL table functions with control logic in the Data Warehouse Center or SQL scalar functions to map other database management systems to DB2.
Version 7.2 enables the following control statements in triggers and SQL functions:
The control statements are integrated into the DB2 query compiler. For more information on SQL control statements and variables, see the SQL Reference. For more information on this Version 7.2 enhancement, see the DB2 Release Notes.
You can now use referential integrity constraints and triggers with typed tables.