IBM DB2 9.7 Advanced Application Developer Cookbook: This cookbook is essential reading for every ambitious IBM DB2 application developer. With over 70 practical recipes, it will help you master the most sophisticated elements and techniques used in designing high quality DB2 applications.

DB2 provides many features that an application developer can use. In this chapter, we will focus on all the basic techniques and aspects of application development that are very generic and can be used with any programming language. It will also help developers to design their applications in an efficient manner and to use the database features efficiently. The chapter is divided into various recipes and each recipe includes appropriate examples that help to understand the concept better.

Authorization is a security mechanism by which DB2 determines whether a user is allowed to perform a certain action or not. DB2 provides various authorities for the administration of databases and their environment. We can grant these authorities to different users to perform a certain set of operations. These operations could be installation, migration, backups, maintenance activities, data loads, so on and so forth. The authorities comprise certain privileges that are necessary to perform a certain task. DB2 provides two levels of authorities:

Instance-level authorities allow the user to perform the instance-level activities, such as upgrading databases, instance performance monitoring, managing disk space, and so on. This level of authorization doesn't provide access to data in the database.

DB2 provides four types of instance-level authorities:

This category of authorities allows a user to perform activities specific to a database. These activities are viewing/modifying data in the database, granting and revoking privileges to users, performing data load operations, running maintenance activities like collecting statistics, backups and restores, and so on.

GRANT <authority_name> ON DATABASE <db_name> to USER/ROLE/GROUP <name>

Privileges are the next level of security mechanism that can be implemented at database object level. A privilege determines the permission of performing a task on an object. A user who creates an object in the database implicitly acquires all the privileges associated with that object. Privileges can be divided into three categories:

When the complete SQL statement is known at pre-compile time, it is known as static SQL. All aspects of the SQL statement, such as the table name, column names, filter criteria, and so on must be specified at pre-compile time. The only thing that can be passed at the time of execution are host variables. Even if we have host variables, the complete information such as data type, length, and so on, must be available at the pre-compile time. Static SQL statements can be embedded in all languages except interpreted languages. At the time of pre-compilation, the language pre-processor converts the static SQL code into database manager run-time service API calls which the compiler can understand. In the case of interpreted languages, there is no concept of pre-processing, so everything becomes dynamic SQL there. In this section, we will see how to implement static SQL.

The advantages of static SQL are:

When an SQL statement or some part of it is not known until the execution time, then these statements are executed dynamically. In the case of dynamic SQL, the statement is compiled at runtime. It also means that the access plan is created at runtime, which can benefit from the latest statistics. When the database manager runs a dynamic SQL or XQuery statement, it creates an access plan based on the current system statistics and current configuration parameters. This access plan might change from one execution of the statements, application program to the other.

Dynamic SQL can be used when:

Declared Global Temporary Tables (also known as DGTTs) are used to store temporary results within an application. Because these tables are only used for temporary storage, they do not persist. They do not appear in the system catalog either. And because they don't persist, they can't be shared with other applications. When the application using this table terminates, any data in the table is deleted and the table is dropped.

Another key difference between DGTTs and regular tables is that the rows in a DGTT cannot be locked, as the temporary tables can't be shared among different applications. In this recipe, we will create a declared global temporary table.

Starting from DB2 9.7, we can create declared temporary tables with XML columns in them. DGTTs can contain XML data and they can be used just like regular tables.

If we are processing a large amount of data from a temporary table, then we can use indexes to get performance benefits. We can create indexes on regular columns as well as on XML columns. The indexes will also be stored in the same table space in which the temporary table is defined.

DB2 9.7 introduced a new type of temporary table: Created Global Temporary Table, also known as CGTT. The purpose of using a created global temporary table is the same as a declared global temporary table, which is to store the intermediate results within an application. There are some key differences between the two. The biggest difference between a CGTT and a DGTT is the existence in system catalogs. The definition of the created global temporary tables are stored in system catalogs, while it's not done for a DGTT. The content of a CGTT is still private to a session. Because the definitions of a CGTT is present in system catalogs, it persists. The advantage of its persistence is that it is usable in functions, procedures, and so on. Also, we don't need to declare the table every time in our application before using it. In this recipe, we will see how to create a created global temporary table (CGTT).

Generated columns are special types of columns in a table whose values are generated by an expression. In this recipe, we will talk about how to create tables with generated columns and will see the use cases for it. In this recipe, we will create a table with a generated column.

INSERT INTO tab1(c1, c2) VALUES(5, 10);
INSERT INTO tab1(c1, c2) VALUES(30, 20);
SELECT * FROM tab1;
Results:
C1 C2 MAX_COL
------ ------- --------
5 10 10
30 20 30

The savepoints are used to divide a big transaction into smaller sub-transactions. It gives the developer the ability to do partial rollbacks, saving re-execution time. Multiple savepoints can be created in a transaction and we can roll back to any savepoint without affecting the processing done before the savepoint. In this recipe, we will see how to create a savepoint.

SAVEPOINT svpnt_temp
UNIQUE
ON ROLLBACK RETAIN CURSORS
ON ROLLBACK RETAIN LOCKS

As we discussed in the previous recipe, the motivation behind creating savepoints is to divide a transaction into sub-tasks. It allows us to partially roll back a transaction, saving total execution time. Once a transaction is rolled back to a savepoint, the savepoint still exists but all nested savepoints no longer exist. In this recipe, we will see how to use savepoints in a ROLLBACK statement.

JDBC drivers available in DB2 provide the setSavepoint() method to create a savepoint. In this recipe, we will use savepoints in a JDBC application.

CREATE TABLE TEST_SVPT (
EMPNO INTEGER,
EMPNAME VARCHAR(20),
MGRNO INTEGER);

We can directly embed SQL SAVEPOINT commands to implement savepoints in SQLJ. Let's see how to do it.

CREATE TABLE TEST_SVPT (
EMPNO INTEGER,
EMPNAME VARCHAR(20),
MGRNO INTEGER);

Many applications require sequential numbers to be generated for key values such as the item number, invoices, and so on. Sequences offer a very good solution to such applications. Sequences are database objects that allow a user to generate key values automatically. Because these values are generated within the database, there is no chance of the duplicate values being present. DB2 keeps track of the values being generated and knows the next values, so even in the case of a crash or a failure, the new values are correct. It also gives better performance as compared to other approaches of generating the values manually. In this recipe, we will see how sequences can be created and used, and we will also see how it is different from an identity column. In this recipe, we will see how to create sequence objects.

We can alter a sequence object to perform tasks, such as restarting the sequence, changing the sequence behavior like increment interval and other attributes. We cannot change the sequence data type once it is created. In such cases, we need to drop the sequence and recreate with the new definition. If a sequence is altered, then all the values present in cache are also lost.

ALTER SEQUENCE item_num MINVALUE 1000;

ALTER SEQUENCE...

The sequence objects create values that get incremented under the application control. We can use NEXT VALUE and PREVIOUS VALUE commands to use sequence values. Alternatively, we can also use NEXTVAL and CURRVAL instead of NEXT VALUE and PREVIOUS VALUE respectively. These alternatives are only for compatibility with the previous versions of DB2 and are not recommended to be used.

VALUES NEXT VALUE FOR item_num;
VALUES item_num NEXTVAL;

INSERT INTO item_tbl(ITEM_NUM) VALUES (NEXT VALUE FOR item_num);
INSERT INTO item_tbl(ITEM_NUM) VALUES (item_num NEXTVAL);