DB2 10 for z/OS: A compelling case for upgrading

Learn how IBM® DB2® 10 for z/OS® compares with its predecessors and get an introduction to some of the salient features of DB2 10 for z/OS.

Share:

Gunjan Verma (gunjan_verma@infosys.com), Project Manager, Infosys Technologies Limited

Photo of author Gunjan VermaGunjan 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.



14 June 2012

Also available in Russian

Introduction

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

Typecasting

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 typecastingQuery 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
SMALLINTVARCHAR(6)
INTEGERVARCHAR(11)
BIGINTVARCHAR(20)
DECIMAL(p,s)VARCHAR(p+2)
REAL, FLOAT, DOUBLE VARCHAR(24)
DECFLOATVARCHAR(42)
CHAR, VARCHAR, GRAPHIC, VARGRAPHIC DECFLOAT(34)

CURRENT TIMESTAMP

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
QueryResult
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY12011-08-02-12:40:10.214516
SELECT CURRENT TIMESTAMP(12) FROM SYSIBM.SYSDUMMY12011-08-02-12:40:10.214516126547
SELECT SYSDATE FROM SYSIBM.SYSDUMMY12011-08-02-12:40:10

ALTER statement

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.

CHAR

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 V9Results in V10
00001.1
00011.11
00111.111
01111.1111
11111.11111

CONCAT

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.

TO_NUMBER

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.

TRIM

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.

DECODE

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.

NVL

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.

MONTHS_BETWEEN

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
QuerySELECT 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 V91.0000000000 (Ignores the Time difference of 1 hour)
Result on V101.001388888888 (takes the time difference of 1 hour in consideration)

Key differentiators of V10 — Performance perspective

Statistic collection

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


Conclusion

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.

Resources

Learn

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=819029
ArticleTitle=DB2 10 for z/OS: A compelling case for upgrading
publish-date=06142012