![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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 is CHAR.
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 |
|---|---|
| SMALLINT | VARCHAR(6) |
| INTEGER | VARCHAR(11) |
| BIGINT | VARCHAR(20) |
| DECIMAL(p,s) | VARCHAR(p+2) |
| REAL, FLOAT, DOUBLE | VARCHAR(24) |
| DECFLOAT | VARCHAR(42) |
| 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.
Table 3.
SYSDATE/TIMESTAMP example| Query | Result |
|---|---|
| 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
with an ONLINE ALTER statement. This is
followed by a REORG process. This greatly simplifies
the administration process.
The CHAR function is used to convert a numeric
input to a string. The built-in CHAR
function of DB2 9 returns values that are right-justified, which means it will
append zeroes to the left. In DB2 10, the CHAR
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
CHAR
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!
|
Table 4.
CHAR function results in V9 and V10| Results in V9 | Results in V10 |
|---|---|
| 00001. | 1 |
| 00011. | 11 |
| 00111. | 111 |
| 01111. | 1111 |
| 11111. | 11111 |
The 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 TO_NUMBER(STRING). The
alternatives available are DECIMAL(),
INTEGER(), and LONG(), based on the data type of the target. V10 has direct support for
TO_NUMBER.
The 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.
The 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.
Like DECODE, NVL is
another Oracle function available in DB2 with a
different name: COALESCE. DB2 10 has an edge as
it supports NVL directly.
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
MONTHS_BETWEEN| 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.
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 CURRENTACCESSRESOLUTION. It
can be set to values USECURRENTLYCOMMITTED and
WAITFOROUTCOME. The
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 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 TABLESPACE, CREATE
TABLE, 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.
Learn
-
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.
Discuss
- Check out the
developerWorks
blogs and get involved in the
developerWorks community.
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 gunjan_verma@infosys.com or gunjan.verma@rediffmail.com.
