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.
Key differentiators of DB2 10 — SQL coding perspective
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
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
SYSDATE as a synonym for
CURRENT TIMESTAMP. 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
ALTER statement are available with
ONLINE ALTER statement. This is followed by
REORG process. This greatly simplifies the
CHAR function is used to convert a numeric
input to a string. The built-in
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 output.
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!
function 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 numeric argument.
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 function called
STRIP available 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 different name:
COALESCE. DB2 10 has an edge as it supports
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 the
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)|
Key differentiators of V10 — Performance perspective
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 frequent
RUNSTATS, which is
not very user-friendly.
Fast data access
DB2 10 utilizes a hash access method for faster access to data. This uses an internal hash algorithm to locate data rows.
Support for concurrent users
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.
Concurrent data access
DB2 10 has new parameters and settings that give users a lot of control
during data access in concurrent environments. One such parameter is
CURRENTACCESSRESOLUTION. It can be set to
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.
WAITFOROUTCOME 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 (inserted/updated).
Reduction in catalog contention
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
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 more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
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.
Dig deeper into Information management on developerWorks
Experiment with new directions in software development.
Read and subscribe for the best and latest technical info to help you deal with your development challenges.
Software development in the cloud. Register today and get free private projects through 2014.
Evaluate IBM software and solutions, and transform challenges into opportunities.