IBM® DB2® Express-C has recently been enhanced with several important features and improvements that were previously available with only fee-based versions of DB2 for Linux®, UNIX®, and Windows®. Learn how you can take advantage of new capabilities related to performance, application development, storage, security, and more.

Raul F. Chong (rfchong@ca.ibm.com), Senior DB2 program manager, IBM

Raul Chong photoRaul F. Chong is a senior DB2 program manager and technical evangelist based at the IBM Toronto Laboratory. His main responsibility is growing the DB2 community around the world. Raul is a DB2 Certified Solutions Expert in both DB2 administration and application development. He has held numerous positions at IBM since 1997 and is the lead author of the book Understanding DB2 - Learning Visually with Examples 2nd Edition (ISBN-10: 0131580183).


developerWorks Contributing author
        level

05 May 2011 (First published 10 June 2010)

Also available in Chinese Russian Spanish

Introduction

DB2 Express-C is the community edition of DB2. You can use it as part of your production system, embed and distribute it in your applications, or simply use it for development purposes. All for free! This image is a link to obtain the DB2 Express-C 9.7.4 free database download.

DB2 Express-C can be installed on servers of any size, although it will use only two cores and 2GB of memory. Moreover, there is no limit to the database size. The robust set of functions provided by DB2 Express-C compares quite favorably to community editions of database programs offered by other vendors.

DB2 Express-C Version 9.7 was released in June, 2009. Since then, the DB2 Express-C image has been refreshed three times. The latest refresh is 9.7.4, which was released on May 2, 2011. This article summarizes some of the new key features and enhancements that have been added between Version 9.7 and the current 9.7.4 refresh. For a complete description of all the new functionality included in Version 9.7 and the refreshed images, refer to the Resources section to see the Overview topic in the IBM DB2 Database for Linux, UNIX, and Windows Information Center.

Feel free to test the examples provided in this article. Unless otherwise indicated, they use the SAMPLE database included with DB2. Note that as you update your DB2 Express-C code to a newer refresh, existing databases also need to be updated using the db2updv97 command. For example, let's say you created the SAMPLE database when you first installed DB2 Express-C 9.7.1. Now, if you want to use the SAMPLE database and take advantage of the new features included in DB2 Express-C 9.7.4, then you need to run: db2updv97 -d sample.

This command will enable new functions or features included with the new refresh on your existing databases. If you are creating new databases after updating the DB2 Express-C code, these databases will automatically have the newest features, so you would not need to run this command.

Locking enhancements

With the new Currently Committed (CC) semantics of the Cursor Stability (CS) isolation level, writers (UPDATE operations) no longer block readers (SELECT operations). Readers now read the currently committed value prior to performing an UPDATE operation. This enhancement improves concurrency and reduces deadlocks.

For example, assume you have a table named T1 with the following contents:

Table 1. Contents of table T1
FIRSTNAME LASTNAME
Raul Chong
Jin Xie

Now, assume that an application named AppA issues the following statement, but does not perform a commit:

update T1 set lastname = 'Smith' where firstname = 'Raul'

Next, an application named AppB issues this statement:

select lastname from T1 where firstname = 'Raul' with CS

Prior to DB2 9.7, the statement issued by AppB would hang because it would wait for the exclusive lock held by the update statement of AppA (the writer) to be released. When you enable this new CS behavior (it is the default behavior for new DB2 9.7 databases), the statement from AppB returns the currently committed value, instead of waiting. In this case, the returned value would be Chong because the update issued by AppA has not yet been committed.


Performance enhancements

DB2 9.7 introduces the statement concentrator feature that improves the performance of programs that use dynamic SQL, and are developed in languages such as Ruby and PHP. The statement concentrator looks for SQL statements that look exactly the same except for their parameter values. Then, as its name suggests, it concentrates those multiple statements into one statement. For example, with statement concentrator the following statements:

SELECT name FROM employee WHERE empid = 10
SELECT name FROM employee WHERE empid = 20
SELECT name FROM employee WHERE empid = 30

are concentrated into:

SELECT name FROM employee WHERE empid = ?

The question mark (?) represents a parameter marker. For this particular example, DB2 would supply the values of 10, 20, and 30 at runtime.

This feature provides a significant performance boost over prior releases of DB2. Without the statement concentrator feature, prior releases of DB2 would treat each statement in the above example as unique, and would compile and execute each statement separately. However, the access plan for all the statements was likely the same, so it was a waste of resources to repeatedly calculate it. With the statement concentrator feature, DB2 9.7 now compiles the statement only once to obtain the access plan for the statement, and then reuses it.

Another performance enhancement is related to the storing of large objects (LOBs). You can now store LOBs that are smaller than a specified size inline with the data rows. This improves performance for these LOBs because you can access them through the buffer pool.


Application development enhancements

DB2 9.7 includes the following improvements for application developers.

  • Triggers and user-defined functions (UDFs) now have enhanced SQL PL support. Previous to DB2 9.7, they only supported inline SQL PL, which is a small subset of SQL PL. In addition, triggers can now contain UPDATE, DELETE, and INSERT operations together in a single clause as the trigger event. For example:
    CREATE TABLE COMPANY_STATS (NBEMP INTEGER)
    !
    
    CREATE TRIGGER HIRED
     AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
     REFERENCING NEW AS N OLD AS O FOR EACH ROW
       BEGIN
             IF INSERTING
             THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
             END IF;
     
             IF DELETING
             THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
             END IF;
    
             IF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
             THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%';
             END IF;
       END
    !

    In previous versions, you had to create separate triggers for each trigger event. Note as well in the example that INSERTING, DELETING and UPDATING are special predicates that can be used as boolean conditions for identifying trigger actions. Also, BEFORE triggers can now contain UPDATE, DELETE, INSERT and routines that modify data. In previous versions this was only allowed for AFTER triggers.
  • For SQL PL stored procedures and UDFs, you can specify default parameter values and assign them by name. In addition, new Boolean, cursor, row, and array data types are now supported.
  • The TRUNCATE SQL statement is now supported for fast deletes.
  • The LISTAGG aggregate function is now supported. As its name implies, this function creates a list by aggregating a set of strings in the order specified by the WITHIN GROUP clause. For example, let's say you connect to the SAMPLE database and execute the following:
       SELECT 
          workdept, 
          LISTAGG(lastname, ', ') WITHIN GROUP (ORDER BY lastname)
          FROM emp 
          GROUP BY workdept

    In the example, the workdept column, and a list of last names separated by commas would be returned in alphabetical order:
    A00   HAAS, HEMMINGER, LUCCHESSI, O'CONNELL, ORLANDO
    B01   THOMPSON
    C01   KWAN, NATZ, NICHOLLS, QUINTANA
    D11   ADAMSON, BROWN, JOHN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER, YAMAMOTO, 
          YOSHIMURA
    D21   JEFFERSON, JOHNSON, MARINO, MONTEVERDE, PEREZ, PULASKI, SMITH
    E01   GEYER
    E11   HENDERSON, PARKER, SCHNEIDER, SCHWARTZ, SETRIGHT, SMITH, SPRINGER
    E21   ALONZO, GOUNOT, LEE, MEHTA, SPENSER, WONG
  • Alternative SQL syntax, such as the use of ROWNUM and ROWID is now supported.
  • DB2 9.7 now supports the concept of a module (this is similar to the concept of a Package in Oracle), that you can use to bundle related database object definitions.
  • DB2 9.7 now supports implicit casting between data types. This is useful for dynamic languages such as PHP and Ruby. For example, the following is now allowed:
    create table t1 (col1 int)
    select * from t1 where col1 = '42'

    In the example, you can now compare the string value 42 to the integer column col1.
  • There are many new functions for manipulating dates, strings, and other kinds of data.
  • DB2 now supports Create Global Temporary Tables (CGTTs), which are temporary tables with data that is only available during the session. The difference between CGTTs and Declared Global Temporary Tables (DGTTs) is that in the case of CGTTs, the table definition is persisted in the DB2 catalog tables. So once a CGTT is created, it can be used by any session without having to create it again. The rows, however, are independent per session.
  • You can now use public aliases (also known as public synonyms) to reference global objects. Prior to DB2 9.7, all DB2 objects had a unique two-part name. The first part was the schema name and the second part was the object name. By creating a public synonym, you can reference an object without the schema name regardless of the user ID connected to the database, as shown in the following example.
    connect to sample user arfchong using mypsw
    create public synonym raul for table arfchong.staff
    select * from raul                                 ## works OK
    select * from arfchong.raul                        ## Error
    connect to sample user db2admin using psw
    select * from raul                                 ## works OK
  • DB2 9.7 has relaxed its object dependency rules to allow for more flexible schema evolution. For example, assume you had the following dependency:

    View2 --> View1 --> Table1

    Previously, if you wanted to change View1, you first had to drop View2 because it was dependent on View1. But with DB2 9.7, dropping View 2 would no longer be required. Now, DB2 performs the revalidation of View2 automatically for you either immediately or when the view is used again. This can be helpful when you are testing applications, or when you need to change the database schema (for example, by adding columns).

    Some other improvements in the schema evolution area are:

    • You can rename columns.
    • The REPLACE option has been added to several CREATE statements so that you no longer need to issue a drop statement. Therefore, all privileges on the object remain.
    • ALTER COLUMN can now use SET DATA TYPE for all types.
  • The Visual Studio add-ins have been enhanced as well as the IBM Data Server Provider for .NET. This provider now supports host variables, modules, the ARRAY data type, compound statements, and variable length timestamps.

    32-bit versions of IBM Data Server Provider for .NET are included in the 64-bit driver package. When you install the 64-bit drivers, the 32-bit drivers are also installed in a separate directory named sqllib\bin\netf20_32.

    There is also a new db2dsdriver.cfg file keyword, QueryTimeout, which defines how long a client should wait for a query to run before timing out.

    Other enhancements include the ability for CALL statements to take named arguments in any order.

  • JDBC and SQLJ enhancements have been made in order to support some of the other enhancements described earlier.
  • New data types, such as NUMBER, VARCHAR2, and TIMESTAMP with a fractional seconds precision setting, are now supported.
  • There is improved support for Python-DB2 applications.
  • The pureXML enhancements include support for Declared Global Temporary Tables.
  • New SQL syntax using the LIMIT and OFFSET clauses is now supported. The LIMIT clause is helpful when you want to limit the number of records returned by an SQL statement. For example, if the table USERS has 100 records, but you want to return only five records, you can use the following statement:
    SELECT * FROM users LIMIT 5

    The OFFSET clause indicates how many rows are skipped from the first row to start returning results. For example, if you want to return rows 11 to 15 from the table USERS, you can use the following statement:

    SELECT * FROM users LIMIT 5 OFFSET 10

    These two clauses are useful when you want to scroll through multiple page results in your code directly from the database rather than filtering them at the application tier.

    The LIMIT and OFFSET clauses are enabled by default. However, if you find these clauses are not working, you can ensure they are enabled by executing the following commands:

    $ db2set DB2_COMPATIBILITY_VECTOR=MYS
    $ db2stop
    $ db2start
  • There is a new CONNECT_PROC database configuration parameter that you can set to the name of a stored procedure. This procedure would automatically be invoked during the connection process to a database, and could therefore be used to configure parameters for the connection from one central location. For example, this procedure could set special registers such as CURRENT_PATH, CURRENT_SCHEMA, and CURRENT LOCALE LC_TIME, as well as global variables without changing the application.
  • There is also a new feature for the LIKE predicate that makes it possible to use a column reference as pattern expression. The LIKE predicate can therefore be used as a fuzzy join predicate. For example when using an INNER JOIN you compare two columns of different tables and test for equality. Now, instead of testing for equality, you can test based on the LIKE predicate. For example, let's say you have the following two tables:
COMPANY
NAMEPHONE
ACME416-123-1111
SOMOS905-122-9999
ZZZ713-999-8888
XLS647-123-9999
TELX416-590-1224
PHONECODES
CityAREACODE
Toronto416
Mississauga905
Oshawa647
Houston713
  • Now let's say you want to find the city for each company based on their telephone area code. In this case, you can run a query like this:
      SELECT A.name, B.city 
         FROM company A, phonecodes B 
         WHERE B.areacode LIKE substr(A.phone,1,3)

    In this query, the fuzzy join happens between the AREACODE column of the PHONECODES table, and the first three characters of the PHONE column in the COMPANY table which we use in the LIKE predicate. The result would be:
     NAME       CITY
    ---------- ---------------
    ACME       Toronto
    TELX       Toronto
    SOMOS      Mississauga
    XLS        Oshawa
    ZZZ        Houston

Recovery and storage enhancements

Current and prospective DB2 users often ask how much DB2 can store. With each new release of the product, the IBM DB2 development teams continue to push the limits. For DB2 9.7, the limits on the size of large and temporary table spaces have been increased four-fold. For example, a table space with 32KB page size can now store 64TB. Previously it could store only 16TB.

There is a new tool called db2cklog that you can use to check the validity of archive log files before using these files during a roll forward recovery. To run the tool you can type something like this:

db2cklog 2

Where 2 is the second log that had a file name of S0000002.LOG.


Security enhancements

With earlier versions of DB2, system administrators pretty much had access to everything. With DB2 9.7, the security model has been enhanced to allow for separation of duties. This minimizes the risk of data exposure and can help organizations meet government compliance requirements. There are several new administration authority levels, two of which are: DATAACCESS and ACCESSCTRL, that you can use to give users the authority to access data or grant access to data, respectively. This means that a security administrator could revoke these levels of authority for a system administrator or database administrator, which would essentially leave that user with no access to any data.

There are also auditing improvements that allow for the replay of past database activities. For example, if you need to analyze how a given request that happened a few years back affected some tables, you can now use the database audit information to obtain what you need for that analysis.


Manageability enhancements

DB2 now provides you with the capability to move tables online to a different table space. This means that even while users are accessing a table, you can change where the table storage resides. This is one more example of how DB2 strives to provide 24x7 availability for your data. If a DBA detects that the storage in one of the table spaces is filling up quickly, he or she can use the ADMIN_MOVE_TABLE stored procedure supplied with DB2 9.7 to move the table to another table space that has free space. Users would not be impacted at all; they would be able to SELECT, INSERT, UPDATE, and DELETE as usual. To implement this function, DB2 creates a shadow copy of the table and keeps track of the changes made to the original table. The changes are then applied later to the shadow copy.

Other improvements in the area of manageability are related to transporting a database schema. You can think of an SQL schema in DB2 as a way to group different database objects such as tables, views, indexes, and others. For example, assume the table RFCHONG.T1 and the view RFCHONG.V1 are using the same schema named RFCHONG. Sometimes you may want to transport all of the objects that use one schema to another database. One method of doing this, which is applicable only to tables, is to use the db2move utility. You can now use the RESTORE command with the TRANSPORT option to copy table spaces and SQL schemas as a set from a database backup image to another active database.


Installation enhancements

You can now use the db2val DB2 copy validation tool command to quickly verify that you correctly configured your installation of DB2 Express-C.

The footprint of DB2 Express-C images for both Linux and Windows have been reduced considerably (up to 37 percent reduction). DB2 Express-C images are also available in the Amazon Cloud, the IBM Cloud, and included in server templates in Rightscale. See the Resources section for links to the Amazon Cloud, IBM Cloud, and Rightscale.


DB2 Spatial Extender and Text Search

Although spatial capabilities with DB2 have been available for close to 10 years, not many users know about it. So although this functionality is not specifically new for the 9.7 refreshed images, it is worth noting here that you can take advantage of the DB2 Spatial Extender free of charge in all DB2 editions, including DB2 Express-C. The Spatial Extender allows you to work with spatial and geodetic data using SQL. For example, this capability can help you answer questions like, "What is the closest retail outlet for each customer who lives in Toronto and spent more than $3000 last year with us?"

You can even use DB2 Spatial Extender for medical applications. For example, it could help answer the question, "What are the patterns of malignant cells in an MRI brain scan?"

For more information, refer to the Resources section to see the About DB2 Spatial Extender topic in the IBM DB2 Database for Linux, UNIX, and Windows Information Center.

In DB2 Express-C 9.5.2, Text Search was integrated to the DB2 product and allowed you to perform fast searches on text columns. With DB2 Express-C 9.7.4 there have been further improvements in the areas of performance, configuration and tuning. For more information on Text Search, refer to the Discover new features in DB2 Express-C 9.5.2 article in the Resources section.


Conclusion

This article highlighted some of the more notable enhancements that have been introduced to DB2 Express-C since version 9.7 up to the current 9.7.4 refresh. This latest version of IBM's free community edition of DB2 includes new capabilities related to performance, application development, storage, security, and more - some of which were previously available only with fee-based versions of DB2.

Enjoy the new DB2 Express-C 9.7.4!

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  • Download DB2 Express-C 9.7.4, a fully licensed product available at no-charge for the community.

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=494493
ArticleTitle=What's new with DB2 Express-C 9.7.4
publish-date=05052011