IBM DB2 9.7 Advanced Application Developer Cookbook

You need to have the ALTER privilege on the table that needs to be altered.

You can rename an existing column in the table to a new name without losing the data, privileges, and LBAC policies.

The DB2 command syntax to rename the column is as follows:

ALTER TABLE <SCHEMAS>.<TABLENAME> RENAME COLUMN <COLUMN> TO <NEW COLUMN >

For example:

ALTER TABLE DBUSER.DEPARTMENT RENAME COLUMN LOC TO LOCATION

After renaming the column, the application can start accessing the table without a table REORG requirement.

When an ALTER TABLE RENAME COLUMN command runs on the system, DB2 will rename the column in the table and invalidate the dependent objects (if any) such as views, functions, procedures, materialized query tables (MQT), and so on. Invalidated objects would get validated when the dependent objects are being accessed within the application or outside the application by a user. This automatic revalidation of invalid database objects depends on the value of the database configuration parameter, auto_reval.

Refer to the Using the CREATE WITH ERROR support recipe for more details on automatic revalidation of invalid database objects, discussed in this chapter.

For the existing database objects, we need the CONTROL privilege, as the objects will be dropped and recreated.

When we use CREATE OR REPLACE, it replaces the earlier object, if it already exists; otherwise, it creates the object. This feature helps application developers not to worry about existing objects, but the production support team should be very cautious while using this.

The CREATE OR REPLACE command will create the object specified, if it doesn't exist, or drop and recreate the object, if it's already present. During this process of recreation, it invalidates any dependent objects. Based on the AUTO_REVAL parameter settings, DB2 will automatically revalidate the dependent objects after recreating the objects with the new definition.

By default, autocommit is ON in DB2. That means each SQL statement we execute is a unit of work or a transaction. There are many ways to execute the unit of work; the simplest one is to turn off autocommit, or use db2 + c at the beginning, or use atomic procedures.

The different options available for the DB2 command can be listed using the following command:

C:\>db2 "? options"
db2 [option ...] [db2-command | sql-statement |
            [? [phrase | message | sqlstate | class-code]]]
option: -a, -c, -d, -e{c|s}, -finfile, -i, -lhistfile, -m, -n, -o,
           -p, -q, -rreport, -s, -t, -td;, -v, -w, -x, -zoutputfile.
Option 		Description 				Default Setting
------ 		---------------------------------------- 		---------------
-a 		Display SQLCA 				OFF
-c 		Auto-commit 				ON

In this section, we'll see how multiple ALTER TABLE operations are allowed in three different units of work. This can be implemented in any language, but the user must have privileges to alter a table.

For demonstration purposes, we have created the EMPLOYEEE table, which is a replica of EMPLOYEE of the sample database without the dependent objects. If there are dependent objects, we may have to drop them and perform the ALTER TABLE statement.

REORG TABLE EMPLOYEEE

The following screenshot shows the sample output for the preceding statements:

When a table is altered with operations, such as dropping a column, altering a column data type, or altering the nullability feature of a column, the table may be placed in a REORG PENDING state. While the table is in the REORG state, no queries can be run until the table is brought online from the REORG PENDING state by executing the REORG command. Starting with DB2 9.7, one can perform an unlimited number of ALTER TABLE statements in a single transaction with a maximum of three transactions in a row before the need for table reorganization. This reduces the maintenance window requirement, in the case of a huge data warehouse environment.

First, we will look at the ways in which we can change the AUTO_REVAL configuration parameter.

UPDATE DB CFG FOR <DBNAME> USING AUTO_REVAL [IMMEDIATE|DISABLED|DEFERRED|DEFERRED_FORCE]

CREATE WITH ERROR is supported only when we set AUTO_REVAL to DEFERRED_FORCE and the INVALID objects can be viewed from the SYSCAT.INVALIDOBJECTS system catalog table.

When we create an object without a base reference object, DB2 still creates the object with a name resolution error such as the table does not exist (SQLCODE: SQL0204N SQLSTATE: 42704).

Let's have a quick look at the difference between AUTO_REVAL settings and behavior.

Case 1: AUTO_REVAL=DEFERRED

Case 2: AUTO_REVAL=DEFERRED_FORCE

We need the SYSADM authority to modify the values for DB2 registry variables.

To enable or disable soft invalidation at the instance level, use the DB2 registry variable, DB2_DDL_SOFT_INVAL.

db2set DB2_DDL_SOFT_INVAL=ON
db2stop
db2start

db2set DB2_DDL_SOFT_INVAL=OFF
db2stop
db2start

In DB2 9.7, we have the soft invalidation feature to avoid these lock-waits or deadlocks. Upon activating soft invalidation using the registry variable DB2_DDL_SOFT_INVAL=ON in any transaction, the DDL operations, such as DROP TABLE, ALTER TABLE, and DETACH partitions on database objects will not be stuck because of a lock-wait (SQL0911N Reason Code 68) or a deadlock (SQL0911N Reason Code 2) while the modifying objects are being accessed by other transactions. This is because the current transaction will continue to access the original object definition while the new transaction will make use of the changed object definition of ALTER, DROP, or DETACH if the object being accessed is altered. During the DROP statement, the current transaction would still see the object until the completion of the execution of the transaction and all new transactions would fail to find the dropped object. This way, DB2 9.7 improves the application concurrency for DDL statements.

The following is the list of DDL statements for which soft invalidation is supported in DB2 9.7:

As discussed in the earlier recipe, DB2 9.7 supports automatic object revalidation, based on the database configuration parameter's AUTO_REVAL setting.

Normally, the object would get revalidated whenever the application or the user accesses the invalid object, if AUTO_REVAL is set to DEFERRED. If we set AUTO_REVAL to IMMEDIATE, the objects get revalidated immediately after they become invalid.

To perform the ALTER COLUMN SET DATA TYPE action, the user needs to have one of the following authorizations on the object:

We can do it using ALTER COLUMN SET DATA TYPE as follows:

ALTER TABLE SALES ALTER COLUMN SALES SET DATA TYPE SMALLINT
ALTER TABLE EMPLOYEE ALTER COLUMN COMM SET DATA TYPE INTEGER

The preceding SQL statements try to change the data type from one to another.

The reason for the failure in the case of second ALTER COLUMN statement is because an MQT "ADEFUSR" is referring to the base table EMPLOYEE.

db2 "? SQL0270N"
21

A column cannot be dropped or have its length, data type, security, or nullability altered on a table that is a base table for a materialized query table.

We can change the COLUMN type in the base table as follows:

CREATE TABLE BTABLE (C1 INT, C2 INT);
CREATE VIEW v1 AS SELECT C1, C2 FROM BTABLE;
CREATE VIEW v2 AS SELECT C1, C2 FROM V1;
ALTER TABLE BTABLE ALTER COLUMN C1 SET DATA TYPE SMALLINT;
REORG TABLE BTABLE;
SELECT SUBSTR(OBJECTNAME,1,20) NAME, SQLCODE, SQLSTATE, \
OBJECTTYPE FROM SYSCAT.INVALIDOBJECTS WHERE OBJECTNAME IN ('V1','V2')
SELECT * FROM v2;

The ALTER COLUMN SET DATA TYPE statement downcasts the data type INT to SMALLINT, which invalidates the views V1 and V2. Since we have AUTO_REVAL set to DEFERRED, the dependent objects become invalid until used.

As soon as we access the dependent objects after altering the column data type, objects become valid and you won't see them in the SYSCAT.INVALIDOBJECTS system catalog table.

We need one of the following privileges to execute the TRUNCATE command:

TRUNCATE is just a simple command that can also be embedded in any host language.

TRUNCATE TABLE <SCHEMA>.<TABLE> DROP STORAGE IMMEDIATE
TRUNCATE TABLE EMPLOYEEE DROP STORAGE IMMEDIATE

TRUNCATE TABLE <SCHEMA>.<TABLE> REUSE STORAGE IMMEDIATE
TRUNCATE TABLE EMPLOYEEE REUSE STORAGE IMMEDIATE

The following screenshot illustrates the sample output for the TRUNCATE command:

The TRUNCATE statement cannot be rolled back, as with the DELETE statement. This is very useful if you have tons of records to be deleted, saving archive log space and time.

The sample table used in this recipe had 0.2 million rows. TRUNCATE deleted all rows in a second, where the same set of records DELETE would take 10 seconds or more on an average-performing system, and sometimes we may hit the condition when the transaction log is full and may need to change the LOGSECOND/LOGFILSZ parameter.

DELETE FROM EMPLOYEEE

One can use TRUNCATE on a table, which is present on the current server. The TRUNCATE statement cannot be used against the following database objects:

If the table that we are truncating is a root table in the hierarchy, then all tables in the hierarchy are truncated.

The DROP STORAGE or REUSE STORAGE clause specifies whether to drop or reuse the existing allocated storage space for the table.

The IMMEDIATE clause is mandatory, where it specifies if the TRUNCATE operation is processed immediately and cannot be undone. Always and always, the TRUNCATE statement should be the first statement in the transaction. If we have many statements inside the transaction, other operations can be undone, except the TRUNCATE operation.

IGNORE DELETE TRIGGERS or RESTRICT WHEN DELETE TRIGGERS specifies if any delete triggers, which are defined on the table, would not be activated by the TRUNCATE operation and is the default behavior. Otherwise, an error is returned in the case of RESTRICT WHEN DELETE TRIGGER.

There are different ways to delete the data without logging the activity in the transaction logs other than TRUNCATE. They are explained as follows:

db2 +c "ALTER TABLE EMPLOYEEE ACTIVATE NOT LOGGED INITIALLY"
db2 "DELETE FROM EMPLOYEEE"
db2 "COMMIT"

db2 "LOAD FROM 1.del OF DEL REPLACE INTO EMPLOYEEE"

db2 +c "ALTER TABLE EMPLOYEEE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE"
db2 "COMMIT"

Now that we know we have many ways to delete data, we should be using the right method in the right situation. For example, when one uses the ACTIVATE NOT LOGGED INITIALLY option and the unit of work fails, the table has to be rebuilt and the data is lost. In any DB2 High Availability and Disaster Recovery (HADR) setup, be very sure that only tables (the data of which can be easily reproducible) can be marked as NOT LOGGED INITIALLY if required, else we may end up losing the data upon a DR switch. Also, be very cautious while working in a huge data warehouse environment in LOAD with the REPLACE clause. When the data is distributed across multiple partitions, one can expect the APPLHEAPSZ error and the table may go inaccessible. There is also the issue of running out of the utility's heap space UTIL_HEAP_SZ, if you have many data range partitions.

Let's understand the concept and the usage part of the AUTONOMOUS transaction with an example.

In an organization, the HR director wants to make sure all the salary updates are captured for audit purposes. To fulfill this request, the application developer provides an AUTONOMOUS -based code to capture the salary updates and the HR director who performs the change.

The salary, which is greater than 400,000 should only be updated by the HR director after the executive committee's approval is received, but the attempt should be captured in case anyone other than the director tries to update it.

To implement an autonomous transaction, use the AUTONOMOUS keyword while creating the procedure. The AUTONOMOUS procedure runs in its own session independent of the calling procedure. A successful AUTONOMOUS procedure commits implicitly at the end of the execution and an unsuccessful one will roll back the changes.

CREATE OR REPLACE PROCEDURE
UpdateSalary (IN empCode VARCHAR(6),
IN newSalary DECIMAL (9,2))
LANGUAGE SQL
BEGIN
DECLARE oldSalary DECIMAL(9,2);
DECLARE eSal DECIMAL(9,2);
DECLARE QueryTime TIMESTAMP;
SET QueryTime= CURRENT TIMESTAMP;
SELECT salary INTO eSal FROM EMPLOYEE WHERE empNo=empCode;
SET oldSalary=eSal;
CALL logData ('Tim Wilc', empCode, QueryTime, oldSalary, newSalary );
CALL noLog ('Tim Wilc', empCode, QueryTime, oldSalary, newSalary );
UPDATE EMPLOYEE SET SALARY=newSalary WHERE EMPNO=empcode;
IF newSalary > 400000 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END@

This sample demonstrates how an AUTONOMOUS transaction differs from the standard stored procedure transaction. When Tim Wilc updates the salary of CHRISTINE to 280000 in the employee table of the sample database, both the procedures caught the change. However, while updating the salary of CHRISTINE to 480000, this activity is only caught in the AUTONOMOUS transaction, as it executes the procedure, independent of the invoking procedure.

In this section, we will see a few examples where we can exploit implicit casting between different data types. However, the support of implicit casting is not limited to the following scenarios. All of the following examples use the SAMPLE database.

We have many types of casting available in DB2, including casting numeric to string, string to numeric, and casting in the BETWEEN predicate and arithmetic operations. We will discuss each one of them in detail with an example.

SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO = '000250';
SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO = 000250;

SELECT * FROM ORG WHERE DEPTNUMB=10;
SELECT * FROM ORG WHERE DEPTNUMB='10';

SELECT * FROM ORG where DEPTNUMB BETWEEN 10 AND 50;
SELECT * FROM ORG where DEPTNUMB BETWEEN '10' AND '50';

SELECT EMPNO, SALARY FROM EMPLOYEE WHERE EMPNO = '000200';
UPDATE EMPLOYEE SET SALARY = SALARY + '1000' + 1500 + BIGINT(2000) WHERE EMPNO = 000200;
SELECT EMPNO, SALARY FROM EMPLOYEE WHERE EMPNO = '000200';

In DB2 9.7, application development is made a lot easier with the help of implicit casting. It allows data types to be compared, even if the data types are of a different kind. Prior to DB2 9.7, DB2 would normally raise an error stating data type mismatch. In the current version, DB2 will automatically convert the data types to a common, more appropriate format.

In this section, we will see a few examples where we can use named and default parameters in the SQL stored procedure. We need the following privileges or authorities to create a stored procedure:

In this example, we will create a procedure with named/default parameters and will see how we can use named parameters while invoking the procedure.

CREATE PROCEDURE namedParmDefault ( OUT out_month SMALLINT,
IN in_date DATE DEFAULT '1900-01-01',
IN in_offset INT DEFAULT 0)
LANGUAGE SQL
BEGIN
SELECT MONTH(in_date + in_offset DAYS)
INTO out_month
FROM SYSIBM.SYSDUMMY1;
END @

CALL NamedParmDefault(?)@
CALL NamedParmDefault(?, in_offset=>100, in_date=>CURRENT DATE)@

In the earlier examples, we had learnt that in addition to providing the default values, DB2 9.7 also provides the flexibility for a developer to change the parameter order in which the procedure can be invoked. With this new capability of DB2 9.7, the application developer can code less error-prone SQL procedures.