IBM DB2 9.7 Advanced Application Developer Cookbook

By Sanjay Kumar , Mohankumar Saraswatipura
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Application Development Enhancements in DB2 9.7

About this book

With lots of new features, DB2 9.7 delivers one the best relational database systems in the market. DB2 pureXML optimizes Web 2.0 and SOA applications.

DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms.

This DB2 9.7 Advanced Application Developer Cookbook will provide an in-depth quick reference during any application's design and development.

This practical cookbook focuses on advanced application development areas that include performance tips and the most useful DB2 features that help in designing high quality applications. This book dives deep into tips and tricks for optimized application performance.

With this book you will learn how to use various DB2 features in database applications in an interactive way.

Publication date:
March 2012
Publisher
Packt
Pages
442
ISBN
9781849683968

 

Chapter 1. Application Development Enhancements in DB2 9.7

In this chapter, we will focus on the following recipes related to application development enhancements in DB2 9.7 that help the application developer community to use DB2 features, instead of application logic:

  • Changing column names online using the ALTER TABLE operation

  • Using the CREATE OR REPLACE clause while creating objects

  • Using the ALTER TABLE operation in a single transaction

  • Using the CREATE WITH ERROR support

  • Using the soft invalidation and automatic revalidation support

  • Using the ALTER COLUMN SET DATA TYPE extended support

  • Using the new TRUNCATE statement

  • Using the AUTONOMOUS transactions

  • Using implicit casting during application enablement

  • Using the DEFAULT values and NAMED arguments in procedures

Introduction

DB2 9.7 provides many enhanced application features that make an application developer's life easier. In this chapter, we will focus on most of the new application features and their usage along with examples. This helps developers to understand the new features with respect to improving the application portability. The chapter is divided into various recipes and each recipe is followed by an example that helps in understanding the concept better.

 

Introduction


DB2 9.7 provides many enhanced application features that make an application developer's life easier. In this chapter, we will focus on most of the new application features and their usage along with examples. This helps developers to understand the new features with respect to improving the application portability. The chapter is divided into various recipes and each recipe is followed by an example that helps in understanding the concept better.

 

Changing column names online using the ALTER TABLE operation


To rename a column in earlier versions of DB2, we used to recreate the table with a new column name and then insert the data from the earlier table on to a newly created table. The catch here is that while renaming the table, the source table should not have any references such as views, indexes, MQTs, functions, triggers, and constraints. This makes an application developer depend on a database administrator while changing the database object, based on the business requirement. In DB2 9.7, renaming a column is made extremely easy with just a single command inside the application code.

Getting ready

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

How to do it...

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.

How it works…

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.

See also

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

 

Using the CREATE OR REPLACE clause while creating objects


In DB2 9.7, we can create new database objects, such as aliases, procedures, functions, sequences, triggers, views, nicknames, and variables, with a CREATE OR REPLACE clause. These clauses would replace an object if it's already present; otherwise, they create a new object.

The privileges are preserved while replacing an object. In the case of modules, all of the objects within the module are dropped and the replaced version contains no objects.

The main benefit of using this feature is that DB2 doesn't have to wait for a lock on the database object being replaced. Without this feature, we cannot drop an object that is being used. Now DB2 is very intelligent and capable of making a judgment and recreating the object, even if it's been locked.

Getting ready

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

How to do it...

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.

  1. 1. The following set of SQL statements demonstrates the usage of the CREATE OR REPLACE statement.

    CREATE TABLE REPLACE1 (c1 INT, c2 INT)
    CREATE TABLE REPLACE2 (c1 INT, c2 INT)
    CREATE VIEW v1 AS SELECT * FROM REPLACE1
    CREATE VIEW v2 as SELECT * FROM v1
    CREATE FUNCTION fun1()
    LANGUAGE SQL
    RETURNS INT
    RETURN SELECT c1 FROM v2
    CREATE OR REPLACE VIEW v1 AS SELECT * FROM REPLACE2
    
    

    Note

    Downloading the example code

    You can download the example code fles for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the fles e-mailed directly to you.

  2. 2. As we replaced the VIEW v1 with a different base table, VIEW v2 and the function fun1 would get invalidated. The following screenshot shows the sample output for the preceding statements:

How it works…

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.

 

Using the ALTER TABLE operation in a single transaction


When we perform the ALTER TABLE operations, such as dropping a column, in earlier versions of DB2, modifying the data types would force the database administrator to perform REORG on the table before any application would work on the table. Now in DB2 9.7, one can perform an unlimited number of table alterations within a single unit of work. This will allow a data modeler to manage the alteration of the column easily.

Getting ready

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

How to do it...

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.

  1. 1. Set autocommit to ON: This is the default behavior for the DB2 prompt, but it can be explicitly specified as follows:

    C:\>db2 +c
    
  2. 2. Alter a table: Since autocommit is set to ON, it means that every statement executed is a transaction in itself. We will alter the same table in different transactions.

    ALTER TABLE EMPLOYEEE DROP COLUMN SALARY
    ALTER TABLE EMPLOYEEE ALTER COLUMN EDLEVEL DROP NOT NULL
    ALTER TABLE EMPLOYEEE DROP COLUMN SEX
    
    
  3. 3. Reorganize the table: Since we have dropped some columns from the table, the table goes into the REORG PENDING state. To bring the table out of the REORG PENDING state, we can use the REORG command as follows:

REORG TABLE EMPLOYEEE

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

How it works…

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.

 

Using the CREATE WITH ERROR support


The AUTO_REVAL database configuration parameter controls the revalidation and invalidation semantics in DB2 9.7. This configuration parameter can be altered online without taking the instance or the database down. By default, this is set to DEFERRED and can take any of the following values:

  • IMMEDIATE

  • DISABLED

  • DEFERRED

  • DEFERRED_FORCE

Now that we know all of the REVALIDATION options available in DB2 9.7, let's understand more about the CREATE WITH ERROR support. Certain database objects can now be created, even if the reference object does not exist. For example, one can create a view on a table which never existed. This eventually errors out during the compilation of the database object body, but still creates the object in the database keeping the object as INVAILD until we get the base reference object.

How to do it...

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.

  1. 1. Update the database configuration parameter AUTO_REVAL to DEFERRED_FORCE.

    UPDATE DB CFG FOR SAMPLE USING AUTO_REVAL DEFERRED_FORCE
    
    
  2. 2. Try to create a view v_FMSALE, referring to the FMSALE base table. Since we do not have the base table currently present in the database, DB2 9.7 still creates the view, marking it as invalid until we create the base reference object. This wasn't possible in the earlier versions of DB2.

    CREATE VIEW c_FMSALE AS SELECT * FROM FMSALE
    
    
  3. 3. How do you verify if the object is invalid? The following SQL query on the system catalog table, SYSCAT.INVALIDOBJECTS, shows why the database object is in an invalid state:

    SELECT OBJECTNAME, SQLCODE, SQLSTATE FROM SYSCAT.INVALIDOBJECTS
    
    
  4. 4. Once you create the base reference object and access the invalid object, DB2 revalidates and marks it as valid.

  5. 5. The following screenshot illustrates the sample output for the preceding statements:

How it works...

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).

  1. 1. DB2 creates an object even if the reference column does not exist with the error codes (SQLCODE: SQL0206N SQLSTATE: 42703).

  2. 2. If the referenced function is not present, we get SQLCODE: SQL0440N SQLSTATE: 42884.

  3. 3. When AUTO_REVAL is set to IMMEDIATE, all of the dependent objects will be revalidated as soon as they get invalidated. This is applicable to ALTER TABLE, ALTER COLUMN, and OR REPLACES SQL statements.

  4. 4. When AUTO_REVAL is set to DEFERRED, all of the dependent objects will be revalidated only after they are accessed the very next time; until then, they are seen as INVALID objects in the database.

  5. 5. When AUTO_REVAL is set to DEFERRED_FORCE, it is the same as DEFERRED plus the CREATE WITH ERORR feature is enabled.

There's more...

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

Case 1: AUTO_REVAL=DEFERRED

  1. 1. When the table T1, on which the view V1 depends, is dropped, the drop would be successful, but V1 would be marked as invalid.

  2. 2. After creating T1, V1 would still be marked as invalid until explicitly used.

Case 2: AUTO_REVAL=DEFERRED_FORCE

  1. 1. One can create an object without having the base reference object present in the database; this only happens when we set AUTO_REVAL to DEFERRED_FORCE.

  2. 2. Object revalidation happens when an object is being accessed.

 

Using the soft invalidation and automatic revalidation support


In the earlier versions of DB2, whenever an object was altered or dropped, an exclusive lock was applied to ensure that no user accessed the object. This locking resulted in lock-waits or the rolling back of the transaction because of the deadlocks.

Getting ready

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

How to do it...

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

  • To enable soft invalidation at the instance level, set the value of the DB2_DDL_SOFT_INVAL registry variable to ON.

db2set DB2_DDL_SOFT_INVAL=ON
db2stop
db2start

  • To disable soft invalidation at the instance level, set the value of the DB2_DDL_SOFT_INVAL registry variable to OFF.

db2set DB2_DDL_SOFT_INVAL=OFF
db2stop
db2start

How it works...

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:

  • CREATE OR REPLACE ALIAS

  • CREATE OR REPLACE FUNCTION

  • CREATE OR REPLACE TRIGGER

  • CREATE OR REPLACE VIEW

  • DROP ALIAS

  • DROP FUNCTION

  • DROP TRIGGER

  • DROP VIEW

There's more...

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.

 

Using the ALTER COLUMN SET DATA TYPE extended support


ALTER COLUMN SET DATA TYPE was present in the earlier versions of DB2 as well, supporting SMALLINT to INTEGER, INTEGER to BIG, REAL to DOUBLE, and BLOB(n) to BLOB(n+m) conversions; data types could not be cast to smaller data types. In DB2 9.7, the ALTER TABLE statement is extended to support all compatible types, from casting to small data types.

In some cases, data may be truncated upon altering the column data type such as DECIMAL to INTEGER. To avoid the data loss issues, DB2 9.7 scans the column data before the change and writes the error messages, such as overflow errors and truncation errors, into the notification log.

The column data type is set to a new data type only if there is no error reported during the column data scan phase.

Getting ready

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

  • ALTER privilege

  • CONTROL privilege

  • ALTERIN privilege on the schema

  • DBADM authority

How to do it...

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;

How it works...

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.

There's more...

  • Casting of VARCHAR, VARGRAHIC, BLOB, CLOB, and DBCLOB data types to types smaller than the current one will not scan the table for a compatibility check, as this casting is not supported by DB2 9.7

  • In the case of range partitioning tables, the string data type cannot be altered if it's a part of the range partitioning key

  • The identity column cannot be altered in DB2 9.7

  • In the case of the database partitioning feature, if the altering column is a part of the distribution key, then the new data type must meet the following listed conditions:

    • Same data type as the current one

    • Same column length

    • FOR BIT DATA cannot be modified in the case of CHAR and VARCHAR

 

Using the new TRUNCATE statement


In the earlier version of DB2, in order to empty the tables, we used the DELETE statement. The DELETE statement logs everything, so it's not efficient when we are dealing with a large volume of data. An alternate solution is to load the table using a null file and replacing the table data with it. In DB2 9.7, the TRUNCATE command is introduced, which deletes the data from a table quickly and does not log the activity, resulting in very good performance.

Getting ready

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

  • DELETE privilege

  • CONTROL privilege

  • DATAACCESS authority

How to do it...

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

  • Truncating a table with DROP STORAGE: The TRUNCATE command deletes all the rows from a table. We have the option to retain or drop the space allocated for the table. The default is to drop the storage.

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

  • Truncating a table with REUSE STORAGE: We can use the REUSE STORAGE clause in the TRUNCATE command, if we do not want to drop the storage. In this case, the space remains allocated to the table and can be used for the new data.

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

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

How it works...

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:

  • Cataloged table

  • Nickname

  • View

  • Sub table

  • Staging table

  • System MQT

  • Range Clustered table

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's more...

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

  • Disable logging for a table: ACTIVATE NOT LOGGED INITIALLY is an attribute of the table for a unit-of-work operation. During this, any changes made to the table by INSERT, UPDATE, DELETE, CREATE INDEX, DROP INDEX, and ALTER TABLE are not logged.

    Now let's see how we can delete the table data without logging:

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

  • Using LOAD with REPLACE: Another method is to use the LOAD command to delete the data where 1.del is an empty file.

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

  • Replace the data with an empty table: Yet another method is to use NOT LOGGED INITIALLY WITH EMPTY TABLE.

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.

 

Using AUTONOMOUS transactions


DB2 9.7 provides ways to execute and commit a block of SQL statements independent of the outcome of invoking a transaction. For example, if transaction A invokes transaction B, which is AUTONOMOUS in nature, transaction B commits its work even if transaction A fails.

This feature enables application portability from any RDBMS that supports AUTONOMOUS transactions to DB2 9.7.

How to do it...

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.

  1. 1. Create two new tables to capture the update activity on an employee's salary. The table eLogData is to log the autonomous transaction activity and the table eNoLog is to log the non-autonomous transaction activity. This is explained in the following code:

    CREATE TABLE eLogData
    (LOGINID VARCHAR(10),
    EMPCODE VARCHAR(6),
    QUERYTIME TIMESTAMP,
    OLDSALARY DECIMAL(9,2),
    NEWSALARY DECIMAL(9,2))@
    CREATE TABLE eNoLog
    (LOGINID VARCHAR(10),
    EMPCODE VARCHAR(6),
    QUERYTIME TIMESTAMP,
    OLDSALARY DECIMAL(9,2),
    NEWSALARY DECIMAL(9,2))@
    
    
  2. 2. Create an AUTONOMOUS transaction procedure, logData, and a non-autonomous transaction procedure, noLog, as follows:

    CREATE OR REPLACE PROCEDURE
    logData (IN hrLogin varchar(10),
    IN empNo VARCHAR(6),
    IN queryTime TIMESTAMP,
    IN oldSalary DECIMAL(9,2),
    IN newSalary DECIMAL(9,2))
    LANGUAGE SQL
    AUTONOMOUS
    BEGIN
    INSERT INTO eLogData VALUES
    (HRLOGIN,
    EMPNO,
    QUERYTIME,
    OLDSALARY,
    NEWSALARY);
    [email protected]
    CREATE OR REPLACE PROCEDURE
    noLog (IN hrLogin varchar(10),
    IN empNo VARCHAR(6),
    IN queryTime TIMESTAMP,
    IN oldSalary DECIMAL(9,2),
    IN newSalary DECIMAL(9,2))
    LANGUAGE SQL
    BEGIN
    INSERT INTO eNoLog VALUES
    (HRLOGIN,
    EMPNO,
    QUERYTIME,
    OLDSALARY,
    NEWSALARY);
    [email protected]
    
    
  3. 3. Create a procedure to update the salary, and if the salary is more than 400,000, the update would roll back, as this needs an approval from the executive committee.

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;
[email protected]

  • The sample output of the preceding example looks similar to the following screenshot:

How it works…

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.

 

Using implicit casting during application enablement


Typecasting is very common in application development. It means changing the data type from one to another. This is required in assignment operations and comparisons.

The necessity of type casting lies in the database or programming language. In this section, we will only focus on databases. Prior to DB2 9.7, databases supported strong typing for comparisons and assignments. For example, you could assign only an integer value to an integer data type. It didn't allow you to assign any other numeric data type to an integer variable without casting it to the integer explicitly. This restriction is known as strong typing.

Starting from DB2 9.7, it indirectly supports weak typing (from a user's point-of-view) but internally it casts the value to the required data type implicitly. This is commonly known as implicit casting. Implicit casting is the automatic conversion of one data type into another. During any comparison operation or assignment operation, if DB2 encounters different data types, then it uses implicit casting to do the required conversion. Implicit casting is based on a predefined set of conversion rules.

Getting ready…

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.

How to do it...

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.

  • Casting numeric to string data types: The EMPNO column in the EMPLOYEE table is of CHAR (6) data type. You can either pass the parameter as a string or as a numeric value and DB2 takes care of implicitly casting it.

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

  • Casting string to numeric data types: The DEPTNUMB column in the ORG table is of the SMALLINT data type. Let's see how DB2 converts a string value to an integer value:

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

  • Implicit casting in the BETWEEN predicate: The DEPTNUMB column in the ORG table is of the SMALLINT data type. Let's see how we can exploit implicit casting in the BETWEEN predicate of a SELECT query:

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

  • Using implicit casting in arithmetic operations: Implicit casting is also supported in arithmetic operations. The SALARY column in the EMPLOYEE table is DECIMAL(9,2). Let's apply some calculations on SALARY using different data types:

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';

How it works…

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.

There's more…

  • Implicit casting is also used during function resolution. For instance, if the data types of function parameters do not match with the data types of arguments supplied during the function call, then the data types of arguments are implicitly cast to the data types of the parameters.

  • Implicit casting becomes very handy during application migration. If you have an application that runs on any other database other than DB2, then the effort required to modify such applications to run on DB2 reduces significantly.

  • Implicit casting is also supported in federation.

 

Using the DEFAULT values and NAMED arguments in procedures


When we define a stored procedure, it also has IN and OUT parameters associated with it. The stored procedures can be invoked from any host language or by command line. To call a stored procedure, we need to provide the procedure name and parameters. Since we need to process the IN and OUT parameter values, we will have to use host language variables. To make this happen, we can use parameter markers.

A parameter marker acts as a place holder in an SQL statement. Normally, parameter markers are identified by question marks (?). DB2 9.7 also provides support for named parameter markers. It means that we can assign names to the parameter markers and refer to them by using these names. We can also assign DEFAULT values for these parameter markers while creating the procedure.

Getting ready

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:

  • CREATIN or IMPLICIT schema privilege, whichever is applicable

  • Privileges needed to execute all the SQL statements used in the procedure

How to do it…

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.

  • Creating a stored procedure with named parameters: We will create a stored procedure with two input parameters, both defined with DEFAULT values. The first input parameter accepts a DATE value and another parameter accepts an offset value. The procedure calculates the month by adding up the date and offset. As we are aware, in DB2 9.7, when defining the stored procedure, the application developer can provide default values so that if there is no input from the user while invoking the procedure, it uses the default set values.

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 @

  • Calling the procedure with named parameters: In the preceding example, we illustrated how to call a normal procedure in any DB2 version. With the default and named parameters, we need not provide all input values in the procedure call statement. We also don't have to use the same order of values as the parameters are defined in the procedure. Consider the following examples where both these cases are illustrated:

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

How it works…

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.

  • We don't need to specify the parameters in the order of procedure definition.

  • We can also define the DEFAULT values for parameters.

  • We don't need to specify all parameters in the procedure call statement. In such cases, the default values will be used.

  • Applications become easier to read and understand.

About the Authors

  • Sanjay Kumar

    Sanjay Kumar started his career as a database developer and DB2 consultant in IBM India Software Labs. He is currently working as Database Architect for Data Warehouse Applications for an Investment banking firm. He has extensive experience in DB2 application development and performance tuning. He is an IBM certified application developer (DB2 9.7), IBM certified solution developer, and IBM Certified Advanced Database Administrator.

    Browse publications by this author
  • Mohankumar Saraswatipura

    Mohankumar Saraswatipura is a database solutions architect focusing on IBM Db2, Linux, Unix, Windows, and SAP HANA solutions. He is an IBM Champion (2010-2018) and a DB2's Got Talent 2013 winner. He is also a frequent speaker at the DB2Night Show and IDUG North America conferences. He has written dozens of technical papers for IBM developerWorks, Data Magazine, and the DB2 10.1/10.5 certification guide. He holds a Master's of technology in computer science and an executive MBA from IIM Calcutta.

    Browse publications by this author
Book Title
Access this book, plus 7,500 other titles for FREE
Access now