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.