December 17, 2007

Bind Default Adjustments Improve Concurrency and Performance

Have you ever looked at a default parameter and wondered why the heck they picked that as the default? I know I have. I think has IBM generally chosen its default values based on how its systems are being built at the time the feature comes out. However, with DB2 9, IBM has seen a shift in the way applications and systems are being built, and the company is addressing that with these changes.

Because application development has shifted from a local database access with local green screens to a distributed access with GUI Web browsers, the change with CURRENTDATA and ISOLATION level in the BIND command will greatly improve performance and concurrency.

Why are these default values important? In most companies, when IT personnel are new to a product, they usually just accept the defaults until they can learn more about the product and its parameters. But this process can take years. And ultimately, a company may reach such a pain level that it hires a DB2 performance specialist to review the environment and determine what needs to change. Having the defaults set according to what the industry is doing in general should help reduce the problems and keep customers happy.

In DB2 9, the CURRENTDATA default is changed from YES to NO. Why is this important? The CURRENTDATA parameter is used for a couple of different reasons, but its primary purpose today is to help reduce network traffic for distributed applications. More and more companies have moved their data from servers running DB2, SQL Sever and Oracle to a centralized database serving DB2 for z/OS. When all the development was local to DB2 on z/OS, the YES value was probably a good thing. But now, since the majority of data access is through Web browsers and is for reporting purposes, the YES default no longer makes sense.

So what exactly is the CURRENTDATA parameter and what's the difference between NO and YES? CURRENTDATA determines whether to require data currency for read-only and ambiguous cursors when the isolation level of cursor stability (CS) is in effect. In other words, the data row where the cursor is positioned cannot change while the cursor is there. This parameter also determines whether block fetching can be used for distributed, ambiguous cursors. Block fetching is important to reduce the amount of back and forth traffic "messages" your application must make with DB2.

The NO default specifies that currency isn't required for read-only and ambiguous cursors. Block fetching for a distributed, ambiguous cursor is allowed. An ambiguous cursor is one in which FOR FETCH ONLY or FOR READ ONLY isn't specified, and DB2 is unsure if the data will be changed. So this is a good thing because in most cases you're just reading data and you want DB2 to block fetch the data to reduce network traffic.

If your application attempts to dynamically prepare and execute a DELETE WHERE CURRENT OF statement against an ambiguous cursor, after that cursor is opened, use of CURRENTDATA(NO) isn't recommended. You receive a negative SQLCODE if your application attempts a DELETE WHERE CURRENT OF statement for any of these cursors:

  • A cursor that is using block fetching
  • A cursor that is using query parallelism
  • A cursor that is positioned on a row that's modified by this or another application process

The YES default specifies that currency is required for read-only and ambiguous cursors. DB2 acquires page or row locks to ensure data currency. Block fetching for a distributed, ambiguous cursor is inhibited.

The ISOLATION parameter tells DB2 what type of locking your application needs. The old defaults of repeatable read (RR) ensure that your application doesn't read a row that another application has changed until that application releases the row. It also ensures that another application doesn't change a row your application read until your application releases that row. Using ISOLATION(RR) can greatly reduce your concurrency and increase CPU usage.

The most commonly used ISOLATION level, CS, ensures that, like RR, your application doesn't read a row that another application has changed until that application releases the row. Unlike RR, CS doesn't prevent other applications from changing rows that your application reads before your application commits or terminates.

Interestingly, to take advantage of DB2 lock avoidance, you must bind with CURRENTDATA(NO) and ISOLATION(CS). So with the new BIND defaults you not only get improved network traffic and improved performance, but improved concurrency.

Learn more about this and other performance-related information in the DB2 Version 9.1 for "z/OS Performance Monitoring and Tuning Guide."