Is your existing DB2 for z/OS version restricting you in some ways? Do you hate explicit
typecasting or the absence of some built-in functions like
CHAR? If so,
it's time to move to DB2 10, which has a whole new set of features to make
your DB2 for z/OS experience better. You don't even have to be on DB2 9
to be able to move to V10.
Thanks to skip-level migration, you can move directly from V8 to V10. As a part
of a banking product migration team, I have worked extensively on DB2 for
z/OS V9 and moved to DB2 10 for z/OS, and my personal experience is that it is much easier to
migrate to DB2 10 than migrating to other DBMSes. The application-layer migration is
seamless, requiring negligible code changes, and the database scripts work
as-is. This article focuses on some key differentiators for DB2 10 from an SQL
coding perspective. I will also briefly touch upon the advantages that DB2
10 for z/OS offers in terms of performance. DB2 10 for z/OS has an edge over earlier versions
in terms of processing, security, and scalability. The examples
used here compare features of DB2 9.1 with DB2 10.
This is one major area where V10 scores over its predecessors. The previous versions of DB2 do not support implicit typecasting. This means that every time you do a comparison or assignment, the left-hand and right-hand sides have to be of same data type. If not, one needs to be explicitly type-cast to the data type of the second. Although, relying on implicit typecasting is a bad programming practice and, ideally, one should have the same data types going into a comparison or assignment, but implicit typecasting is still a good to have feature on any database. Databases like Oracle support it, and if you are in for a migration from Oracle to DB2, this will save a lot of code changes otherwise required.
Table 1. Typecasting example
|Query with explicit typecasting||Query with implicit typecasting|
|Select * from Tab1 where a=TO_NUMBER(b)||Select * from Tab1 where a=b|
NOTE: Here the data type of column A in Tab1 is
INTEGER, and the data type of
column B in Tab1 is
There is a wide range of casts supported within built-in data types. The following table gives the details.
Table 2. Implicit type casts supported in V10
|Source data type||Target data type|
|REAL, FLOAT, DOUBLE||VARCHAR(24)|
|CHAR, VARCHAR, GRAPHIC, VARGRAPHIC||DECFLOAT(34)|
CURRENT TIMESTAMP is a special register that returns the current date and time. In
V10, we can specify the precision for microseconds within a range of 0-12. The
default precision is 6. We can also use
as a synonym for
The provision of using
SYSDATE or specifying the precision of the result is not
available in previous versions of DB2.
|SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1||2011-08-02-12:40:10.214516|
|SELECT CURRENT TIMESTAMP(12) FROM SYSIBM.SYSDUMMY1||2011-08-02-12:40:10.214516126547|
|SELECT SYSDATE FROM SYSIBM.SYSDUMMY1||2011-08-02-12:40:10|
ALTER statement is used to change the attributes
of a tablespace, table, or index. In
DB2 10, some common schema change features of
ALTER statement are available
ONLINE ALTER statement. This is
followed by a
REORG process. This greatly simplifies
the administration process.
CHAR function is used to convert a numeric
input to a string. The built-in
function of DB2 9 returns values that are right-justified, which means it will
append zeroes to the left. In DB2 10, the
function returns left-justified values. In this case, no zeroes are
appended to the left, and the string output is same as the numeric input, and an extra trim is not required to fix the
Listing 1. Sample inserts using
CREATE TABLE TEST_CHAR (C1 DECIMAL(5,0)! INSERT INTO TEST_CHAR VALUES (1)! INSERT INTO TEST_CHAR VALUES (11)! INSERT INTO TEST_CHAR VALUES (111)! INSERT INTO TEST_CHAR VALUES (1111)! INSERT INTO TEST_CHAR VALUES (11111)! SELECT CHAR(C1) FROM TEST_CHAR!
CHARfunction results in V9 and V10
|Results in V9||Results in V10|
CONCAT function, or
|| operator, is used join two strings. In DB2 9, as implicit
typecasting is not supported,
CONCAT can take
in only two string arguments.
In V10, since implicit typecasting is supported, we can concatenate a string to a
To concatenate a string a with a number 5, the query in DB2 9 will be:
Listing 2. Concatenating a string in V9
SELECT ‘a’||’5’ FROM SYSIBM.SYSDUMMYU Or SELECT ‘a’||CAST(5 AS CHAR(1) FROM SYSIBM.SYSDUMMYU
In V10, the following query will work:
Listing 3. Concatenating a string in V10
SELECT ‘a’ ||5 FROM SYSIBM.SYSDUMMYU
This saves a lot of code changes during a porting activity because most other DBMSes support implicit typecasting.
DB2 9 does not support
alternatives available are
LONG(), based on the data type of the target. V10 has direct support for
TRIM function is used to remove leading and
training blank spaces from a string. DB2
9 does not support
TRIM. There is another
in V9, which does the same work as TRIM. In V10,
TRIM is available.
DECODE function is used as an abbreviated
IF in SQL queries. It is supported in
Oracle, but in DB2 9, the same functionality is supported by the
CASE function. The syntax
of the two functions is a little different, which makes porting from Oracle to DB2 9
a little tricky. DB2 10 supports
CASE in a way
similar to Oracle, making DB2 10 adoption easier in any porting exercise.
another Oracle function available in DB2 with a
COALESCE. DB2 10 has an edge as
In DB2 9,
MONTHS_BETWEEN returns the difference of months between two dates after
ignoring the timestamp part of the dates. DB2 10 has better support here and takes
TIMESTAMP part of the date into consideration while calculating the result.
Table 5. Example of
|Query||SELECT MONTHS_BETWEEN (TO_DATE('12/05/2011 3:24:20 PM', 'DD/MM/YYYY HH:MI:SS PM'), TO_DATE('12/04/2011 2:24:20 PM', 'DD/MM/YYYY HH:MI:SS PM')) FROM SYSIBM.SYSDUMMYU|
|Result on V9||1.0000000000 (Ignores the Time difference of 1 hour)|
|Result on V10||1.001388888888 (takes the time difference of 1 hour in consideration)|
In DB2 10, a new set of stored procedures and catalog tables have been
introduced to govern statistics collection. There is an automatic
scheduling process, which
takes care of statistic collection, doing away with the need to do
which is not very user-friendly.
DB2 10 utilizes a hash access method for faster access to data. This uses an internal hash algorithm to locate data rows.
Due to increased memory, DB2 10 allows 5-10 times more concurrent users in a single DB2 member. This is a huge incentive to use DB2 10, especially for large applications that handle huge load.
DB2 10 has new parameters and settings that give users a lot of control during data
access in concurrent environments. One such parameter is
can be set to values
USECURRENTLYCOMMITTED setting gives users the flexibility to retrieve currently
committed data from the database, thus increasing concurrency. This setting means
that the system will ignore rows being changed (inserted/updated) and will
fetch only committed rows. The
setting ensures that if a data update is
in progress, all data access requests wait for the current transaction to
complete. In this setting, the system will not ignore rows being changed
In previous DB2 versions, catalog tables use page-level locking. This means
that the commands that write data to catalog tables like
CREATE VIEW, and
CREATE INDEX lock the catalog tables, and concurrent usage of
these commands causes contention on catalog tables. In DB2 10, catalog tables use
row-level locking, reducing catalog contention significantly.
With the kind of benefits and IBM support that DB2 10 comes with, it only seems to be a wise choice to migrate to DB2 10 for z/OS. It has backward-compatibility, is better in terms of SQL query writing and coding, and is leaps and bounds ahead of previous versions in terms of performance.
Read Ten reasons to upgrade to DB2.
Check out the DB2 10 for z/OS information.
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Get products and technologies
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
- Now you can use
DB2 for free. Download DB2 Express-C, a no-charge
version of DB2 Express Edition for the community that offers the same core
data features as DB2 Express Edition and provides a solid base to build
and deploy applications.
Gunjan is a project manager with over eight years of experience advising financial services companies in defining and driving their database strategy. Most recently, she has led the adoption of DB2 for a leading banking product. She is the co-author for the artiicle "Oracle to DB2 V9.1 for z/OS migration demystified!" She can be reached at email@example.com or firstname.lastname@example.org.