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
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.
With the new Currently Committed (CC) semantics of the Cursor Stability
(CS) isolation level, writers (
operations) no longer block readers (
operations). Readers now read the currently committed value prior to
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
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
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.
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
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.
TRUNCATE SQLstatement is now supported for fast deletes.
LISTAGGaggregate 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
workdeptcolumn, 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
ROWIDis 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
42to the integer column
- 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.
REPLACEoption has been added to several
CREATEstatements so that you no longer need to issue a drop statement. Therefore, all privileges on the object remain.
ALTER COLUMNcan now use
SET DATA TYPEfor 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,
ARRAYdata 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
CALLstatements 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
TIMESTAMPwith 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
OFFSETclauses is now supported. The
LIMITclause 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
OFFSETclause 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.
OFFSETclauses 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:
- 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
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:
2 is the second log that had a file name
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:
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.
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
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.
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.
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!
- Learn more about the new functionality in Version 9.7.4 from the What's New overview topic in the IBM DB2 Database for Linux, UNIX, and Windows Information Center.
- See the About DB2 Spatial Extender topic in the IBM DB2 Database for Linux, UNIX, and Windows Information Center.
- See the Discover new features in DB2 Express-C 9.5.2 article for more information on the Text Search functionality.
- Check out the Getting Started with DB2 Express-C - 3rd Edition free eBook, where many of the enhancements introduced in this article are described in more detail, and has been updated for DB2 9.7.
- Learn more about IBM Spatial Offerings.
- Learn more about the Amazon Cloud.
- Learn more about the IBM Cloud.
- Learn more about Rightscale.
- Learn more about DB2 Express-C.
- Visit the DB2 for Linux, UNIX, and Windows page on developerWorks, where you can find the resources you need to advance your DB2 skills.
- 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.
- 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.
- Participate in the discussion forum.
- Visit the DB2 My developerWorks group for a place to interact and collaborate with other DB2 users (all versions and all platforms).
- Check out the developerWorks blogs and get involved in the developerWorks community.