Since its Version 8.2.2 release, also known as Fix Pack 9, the IBM DB2 Universal Database product (DB2 UDB) has provided the capability for a concurrent data reader to access more than one result set in the same database connection and query both of those result sets concurrently. You can download this product, which came out in April 2005, at: http://www-306.ibm.com/software/data/db2/udb/support/downloadv8.html. In this article, I want to introduce you to this new feature and show you some sample code that you can use to deliver this powerful functionality to your DB2 UDB applications.
This sounds a lot like Multiple Active Results Sets (MARS) support SQL Server 2005 release… How did DB2 UDB manage to do this so early?
Microsoft refers to what I am calling Concurrent Data Readers as Multiple Active Result Sets (MARS). MARS is actually more than this, but this is the crux of the new feature scheduled for SQL Server 2005.
All the DB2 UDB family servers have fully supported having multiple statements open per connection for a very long time. The type of statement is irrelevant (SELECT, INSERT, UPDATE, or DELETE). Given the nature of SELECT, INSERT, UPDATE, and DELETE statements, it is easier to have multiple cursors open where the application can alternate fetches between two cursors to show they are concurrent.
SQL Server 2000 has been unable to have more than a single statement open at a time on a connection. If you exceeded this restriction, an open cursor would block out all other statements, not just other cursors.
In SQL Server 2005, Microsoft appears to have changed their model (with their M.A.R.S. feature) to enable multiple statements on a single connection. This implementation was done by adding an abstract layer (session) between the connection and the statement, which may indicate a potential overhead cost to having multiple sessions opened.
To enable this support in DB2 UDB, the IBM development teams did not have to address the same limitations that the Microsoft teams apparently faced, so they could deliver this feature quickly in response to customer demand. In fact, enabling this feature in DB2 UDB was as simple as removing the client side restriction that stood in the way of externalizing this support. The CLI, JDBC, and SQLJ interfaces in DB2 UDB, for example, have had this support for years.
Keep in mind that this capability is not a panacea: there are costs to having multiple statements executing. However, in the DB2 UDB case, associated overhead seems very limited in my tests. Of course, I will give the usual advice about holding any locks held until a statement is closed, but that is business-as-usual and not specific to using multiple active statements at the same time.
Concurrent Data Readers in DB2 UDB Version 8.2.2 or later
The concurrent data reader support in DB2 UDB enables an application to access more than one result set over the same database connection and query them concurrently. Prior to V8.2.2, each database connection could only support one open result set on a connection at any one time.
Quite simply, this feature delivers the capability for your applications to have multiple DB2DataReader objects open on a connection at the same time when each instance of the DB2DataReader is started from a separate command. This will also enable common language runtime (CLR) stored procedures – supported in the DB2 UDB product since September 2004 – to return multiple result sets too.
Concurrent data reader capability is not just limited to the DB2 UDB product running on Linux, UNIX, or Windows operating systems. In fact, this new capability can be extended to support the entire DB2 UDB family – although you will need the DB2 Connect product to support this feature with DB2 UDB for z/OS and DB2 UDB for i5/OS databases.
The following code snippet shows an example of opening more multiple DB2DataReader objects on a connection at the same time:
//Open connection to the database
DB2Connection conn = new DB2Connection (“database=sample;”);
conn.Open();
//Create 2 commands from the same connection
DB2Command cmd1 = conn.CreateCommand();
DB2Command cmd2 = conn.CreateCommand();
cmd1.CommandText = “SELECT SALES_PERSON, SALES FROM SALES”;
cmd2.CommandText = “SELECT DEPTNUMB, DEPTNAME FROM ORG”
//Execute the first query
DB2DataReader dr1 = cmd1.ExecuteReader();
//Execute the second query while the first one is still open
DB2DataReader dr2 = cmd2.ExecuteReader();
//Get results from both data readers
while (dr1.Read())
{
Console.WriteLine(” Sales Person {0}, Sales {1}” dr1.GetString(0), dr1.GetInt16(1));
}
while (dr2.Read())
{
Console.WriteLine(” Department Number {0}, Department Name {1}” dr2.GetInt16(0), dr2.GetString(1));
}
//Close both data readers
dr1.Close();
dr2.Close();
conn.Close();
Of course, you will use the DB2 UDB plug-ins for Visual Studio .NET and the DB2 UDB SQL editor to work with this code. (It provides IntelliSense links to the underlying database schema, collapsible regions, syntax colorization, and more.) I included the code snippet so that you could copy and paste the code for yourself.
An example of this code in the integrated DB2 SQL editor for Visual Studio .NET is shown below:
In the first section of this code sample (1), you can see that the connection to the SAMPLE database is defined and opened. (It is using the default credentials that the user used to log on to the Windows operating system in this case.)
After setting up the connection, two separate command objects (2,3) are instantiated – each a simple SELECT statement from the SAMPLE database that comes with DB2 UDB. If you do not have the SAMPLE database created on your system, you can quickly create it by entering the db2sampl command on your operating system’s command prompt.
Now it is time to execute each query (4,5), but note I have only created one connection. Once each query has been run, the results of each command, which was run from separate data readers on the same connection (6), are returned to the console application.
Of course, like a good developer, you close up your readers and connections (7), and your application is finished.
Wrapping it all up
In this article, I showed you how you can leverage the ability for DB2 UDB V8.2.2 to execute multiple reader commands on a single database connection. As previously stated, you have likely heard of this feature known as MARS in the .NET context through advertisements for the SQL Server 2005 release. The similar concept of performing different reads on the same database connection in DB2 UDB does not have the inherent complexities that seem to come with the MARS programming model in SQL Server 2005.
Concurrent data reader support, along with other features in the SQL Server 2005 and Visual Studio .NET 2005 release (CLR stored procedures, database projects, and more) are prime examples of just how sophisticated the support for .NET applications is in DB2 UDB today! From the tooling to the provider, DB2 UDB and .NET are a dynamic combination!
Ready to try it out for yourself? Get a free trial copy of a DB2 UDB for Windows Server at: www.ibm.com/software/data/db2/udb/support/downloadv8.html. You can also get a free copy of DB2 UDB Personal Edition for development purposes at: http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=db2udbpde.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA’s Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.
Trademarks
IBM, DB2, DB2 Connect, DB2 Universal Database, i5/OS, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademarks of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2005. All rights reserved.
Disclaimer
The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.
