Knowledge of complex database objects

[Note]

Complex objects include:

Creating a user-defined temporary table

A user-defined temporary table is needed by applications you are writing to work with data in the database. Results from manipulation of the data need to be stored temporarily in a table.

The description of this table does not appear in the system catalog making it not persistent for, and not able to be shared with, other applications.

When the application using this table terminates or disconnects from the database, any data in the table is deleted and the table is implicitly dropped.

A user temporary table space must exist before creating a user-defined temporary table.

To create a user-defined temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement. The statement is used from within an application.

Example of how to define a temporary table:

DECLARE GLOBAL TEMPORARY TABLE gbl_temp
    LIKE empltabl
    ON COMMIT DELETE ROWS
    NOT LOGGED
    IN usr_tbsp					
					

This statement creates a user temporary table called gbl_temp. The user temporary table is defined with columns that have exactly the same name and description as the columns of the empltabl. The implicit definition only includes the column name, data type, nullability characteristic, and column default value attributes. All other column attributes including unique constraints, foreign key constraints, triggers, and indexes are not defined. When a COMMIT operation is performed, all data in the table is deleted if no WITH HOLD cursor is open on the table. Changes made to the user temporary table are not logged. The user temporary table is placed in the specified user temporary table space. This table space must exist or the declaration of this table will fail.

If a ROLLBACK or ROLLBACK TO SAVEPOINT is specified when creating this table, either you can specify to delete all the rows in the table (DELETE ROWS, which is the default), or you can specify that the rows of the table are to be preserved (PRESERVE ROWS).

Identity columns

An identity column provides a way for DB2 Database for Linux, UNIX, and Windows to automatically generate a unique numeric value for each row in a table. A table can have a single column that is defined with the identity attribute. Examples of an identity column include order number, employee number, stock number, and incident number.

Values for an identity column can be generated always or generated by default:

  1. An identity column that is defined as generated always prevents the overriding of values in an SQL statement. Its values are always generated by DB2 database manager; applications are NOT allowed to provide an explicit value. There is no guarantee on the uniqueness of values found within generated always columns. To guarantee uniqueness of values in the column, a unique index should be defined on the column.

  2. An identity column that is defined as generated by default gives applications a way to explicitly provide a value for the identity column. If a value is not given, DB2 generates one, but cannot guarantee the uniqueness of the value in this case. Generated by default is meant to be used for data propagation, in which the contents of an existing table are copied, or for the unloading and reloading of a table.

Identity columns are ideally suited to the task of generating unique primary key values. Applications can use identity columns to avoid the concurrency and performance problems that can result when an application generates its own unique counter outside of the database. For example, one common application-level implementation is to maintain a 1-row table containing a counter. Each transaction locks this table, increments the number, and then commits; that is, only one transaction at a time can increment the counter. In contrast, if the counter is maintained through an identity column, much higher levels of concurrency can be achieved because the counter is not locked by transactions. One uncommitted transaction that has incremented the counter will not prevent subsequent transactions from also incrementing the counter.

The counter for the identity column is incremented (or decremented) independently of the transaction. If a given transaction increments an identity counter two times, that transaction may see a gap in the two numbers that are generated because there may be other transactions concurrently incrementing the same identity counter (that is, inserting rows into the same table). If an application must have a consecutive range of numbers, that application should take an exclusive lock on the table that has the identity column. This decision must be weighed against the resulting loss of concurrency. Furthermore, it is possible that a given identity column can appear to have generated gaps in the number, because a transaction that generated a value for the identity column has rolled back, or the database that has cached a range of values has been deactivated before all of the cached values were assigned.

The sequential numbers that are generated by the identity column have the following additional properties:

To create an identity column, use the CREATE TABLE statement with the GENERATED clause and make sure it contains the IDENTITY keyword because GENERATED can also be used to generate other values automatically that are not identity columns. Here is an example:

CREATE TABLE product (
	productno INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 200 INCREMENT BY 1),
	description VARCHAR(50)
)
					

The column productno is an INTEGER defined as an identity column that is always generated. The value generated will start from 200, and it will be incremented by 1. Let's perform a few INSERT statements and see the results obtained:

INSERT INTO product VALUES (DEFAULT,'banana');       --->inserts 200,banana
INSERT INTO product (description) VALUES ('apple');  --->inserts 201,apple
INSERT INTO product VALUES (300,'pear');             --->error SQL0798N
COMMIT;

INSERT INTO product (description) VALUES ('orange'); --->inserts 202,orange
ROLLBACK;

INSERT INTO product (description) VALUES ('plum');   --->inserts 203,plum
COMMIT;
					

The following query shows the final result:

SELECT * FROM product;

PRODUCTNO   DESCRIPTION
----------- ------------
200         banana
201         apple
203         plum
					

The first two INSERT statements show that two identity column values were generated: 200 and 201. The third INSERT statement returns an error because you CANNOT explicitly insert a value for an identity column generated as ALWAYS. After the third INSERT statement, we issue a COMMIT to guarantee these rows are stored in the database. The fourth INSERT statement causes another identity column value, 202, to be generated; however, we issue a ROLLBACK statement right after, so this row is not stored in the database. Note that the final INSERT statement, which inserts the product plum, generates a value of 203, not 202 since 202 was generated and assigned to the previous statement.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz