© 2002 International Business Machines Corporation. All rights reserved.

DB2DD: Curt, thanks for talking with us again. The last time we talked, you gave us some interesting insights into the direction that the database industry and more specifically, DB2 for z/OS, is headed. Now we'd like you to get really specific about what people can expect to see in the next version of DB2 for z/OS.

Curt: First of all, everyone should read the DB2 Developer Domain interview with Roger Miller. He covers a lot of the features related to availability, very large databases, increased limits, and so on. He and I will both be talking in detail about future DB2 enhancements at the Data Management Technical Conference in September, too. My talk will focus on the SQL and networking enhancements.

DB2DD: Great, let's start with SQL enhancements.

Curt: It's key to understand what we're doing with Vnext. Most DB2 customers know that we typically announce a new release of the DB2 for z/OS product within 12-18 months of the previous product's general availability. It's already been over 18 months, and we still haven't announced a new release of the product.

We're taking longer with our Vnext release because it contains a great deal of new function, which means we need to spend extra time on testing and customer validation of the product. As a rough point of comparison, it is expected to be over three times the size of our DB2 Version 7 deliverable.

With all these enhancements, we are making big strides in SQL improvements: new SQL syntax, SQL scalability improvements, and SQL performance enhancements.

For example, we are working on a large number of exciting new SQL syntax improvements, including:

• GET DIAGNOSTICS
• SEQUENCEs
• Dynamic scrollable cursors
• Scalar fullselect
• Multiple DISTINCT clauses
• GROUP BY expression
• Qualified column names on INSERT and UPDATE SET clause
• Intermixing EBCDIC, ASCII, or Unicode columns in a single SQL statement
• Unicode support in SQL
• SELECT from an INSERT statement

In addition, we are making major changes in our SQL system limits by:

• Extending support for long names in SQL objects to 30 characters for column names and to 128 characters for most other SQL objects;
• Expanding support for SQL statements up to 2 megabytes in length;
• Increasing the length of literals and predicates to 32 kilobytes; and
• Extending JOIN to allow up to 225 tables in a single statement.

DB2DD: The SELECT FROM INSERT syntax sounds interesting. Can you tell us how would customers use that?

Curt: Sure. Databases have become a lot more sophisticated in the past few years. Customers can do all sorts of things inside the database that cause an inserted row to be changed before it gets recorded on the disk. For example, they can have user-defined defaults for the columns, ROWIDs, IDENTITY columns, SEQUENCEs, triggers that modify the columns, and so on. In most of these cases, the application program needs to know the final values of the columns that were written on the disk. As an example, the application would typically need to know the value of an IDENTITY column that is used to generate a unique invoice number immediately after issuing the INSERT statement. Today, it is sometimes difficult to get this information out of the database, since you have a chicken-or-the-egg problem. You need to issue a SELECT statement to retrieve the column value, but you can't easily formulate the correct WHERE predicate to find the row you just inserted if the key was generated by the database system.

The SELECT FROM INSERT syntax is an extremely elegant solution to this problem. It allows you both insert the row and retrieve the values of the columns on a single SQL operation. You don't have to formulate a WHERE clause, so it's very simple to use. It also performs very well because it can accomplish both the INSERT and the SELECT in a single SQL operation. Here is an example in which the value for the current date is selected from an INSERT into T1:

 
SELECT C3 FROM 
   	INSERT (C1, C3, C5) INTO T1 
            VALUES('ABC', CURRENT DATE, 'DEF'); 

DB2DD: You mentioned something about SQL scalability improvements and SQL performance enhancements earlier. What sort of improvements can we expect?

Curt: Vnext will be the first release of the DB2 for z/OS product that requires 64-bit virtual addressing, which will greatly increase the amount of memory we have available inside the database engine. We are also making major changes to our internal SQL control block structures, so that we are more efficient in how we use that memory. This will be a significant boost in scalability for our customers.

We are working on a lot of important enhancements that will help improve the performance of applications. As always, our cost-based optimizer is the key technology that allows us to deliver the best possible performance. Work is underway to make significant improvements to our optimizer technology:

• Materialized query tables will allow our optimizer to do sophisticated rewrite of the user's SQL query, which can yield huge performance gains.
• Our star join support is being enhanced to use sparse indexing to dramatically improve our join performance for complex queries.
• We are also enhancing our optimizer cost model to use parallel sort technology when it is advantageous to do so.
• Both the optimizer and the SQL runtime are being enhanced to be much more forgiving of column and host variable datatype or length mismatches. This allows us to make predicates sargable (stage 1) in many more situations, which can be an important performance improvement for many workloads.

We are also providing support for performing multiple INSERT or FETCH operations on a single SQL call, which is a particularly important requirement for batch applications. For example:

 
INSERT INTO T1 FOR :hv ROWS 
      VALUES( :ARRAY1, :ARRAY2) ATOMIC; 

DB2DD: Can you do this dynamically, too?

Curt: Sure, here is an example doing the same things dynamically:

 
STMT = 'INSERT INTO T1 
               VALUES( ?, ?) 
               FOR MULTIPLE ROWS ATOMIC'; 
 
PREPARE S1 FROM STMT; 
 
EXECUTE S1 FOR :hv ROWS 
                  USING :ARRAY1, :ARRAY2; 

DB2DD: What do you have in plan to help make it easier to port applications from other vendors?

Curt: We've done surveys over the past two years to discover which items were the biggest obstacles in porting applications to DB2 for z/OS. When we analyzed that data, things like long SQL object names, sequences, Unicode support, and scalar fullselect were the top items on the list (all of which are in the plans for Vnext). In fact, Vnext includes support for all of the top ten items that came out of that survey, so this new release is going to give us a lot more flexibility in porting user-written applications from Oracle or Microsoft® SQL Server to our platform. We're also working on a number of enhancements that were specifically requested by business partners such as SAP, PeopleSoft, and Siebel. All these things will make it possible for many new vendor applications to run on DB2 for z/OS.

DB2DD: What's new for Java^TM developers?

Curt: We have more choices for Java developers. We will be supporting both Type 2 and Type 4 Java drivers which will be updated to support the JDBC/SQLJ 3.0 standard, including such things as savepoints, connection pooling improvements, the ability to reuse PreparedStatements, multiple open ResultSets for a single stored procedure, WITH HOLD cursors, and improved BLOB and CLOB support, among others.

Another thing that both Java and other DDF applications can use is a new CURRENT PACKAGE PATH special register, which lets the application specify a search list of package schemas (similar to PKLIST on DB2 PLANs). This will give the Java developers the same flexibility in managing SQL packages that we offer to static SQL users under CICS and IMS. Here's an example:

 
SET CURRENT PACKAGE PATH = 
         ALPHA, 
         BETA, 
         PROD 

In this example, DB2 will find the "best fit" package starting with packages in schema ALPHA. If no package is found in ALPHA, DB2 will check for packages in schema BETA. If the package is still not found, DB2 will seach for the package in schema PROD. This capability will make it very easy for the application developer to create test versions of a subset of the packages in a given application, and easily control which end users are exposed to the new packages.

DB2DD: What is DB2 for z/OS doing in terms of XML support?

Curt: The DB2 XML Extender is supported on z/OS. In Vnext, we are pushing more XML support into the engine by providing support for some of the built-in XML publishing functions that are currently in the process of being standardized in the ANSI SQL committee, such as XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLCONCAT, XMLAGG, and XML2CLOB.

DB2DD: z/OS is known as a highly secure platform. Are there additional security enhancements that people should be aware of?

Curt: We have had a lot of requests for row-level security for applications that need more granular security schemes. For example, in organizational hierarchies, it is desirable to set up a hierarchy in which employees can see their own payroll data, a first line manager can see his or her payroll information and all of the employees reporting to that manager, and so on. In addition, government security schemes often include a security hierarchy such as TOP SECRET, SECRET, or UNCLASSIFIED. We are currently developing support for row-level security that includes support for these types of hierarchical security schemes. Basically, a table will be able to activate this support by adding a specially named column that acts as the security label. For example, an application might want to have a hierarchy representing the colors of the rainbow as shown in Figure 1.

At the top of the hierarchy, RAINBOW would be a security label that includes all the colors (RED, ORANGE, YELLOW, GREEN, BLUE, INDIGO, VIOLET). At the middle of the hierarchy, you could have other security labels: PASTEL (BLUE, INDIGO, VIOLET) and SUNSET (RED, ORANGE, YELLOW).

As shown in Figure 2, with that hierarchy established in the security manager layer, the system would understand that users with authority to access RAINBOW can access anything. Someone with authority to access PASTEL information can access any row associated with BLUE, INDIGO, VIOLET, or PASTEL. Someone with SUNSET can access SUNSET, RED, ORANGE, YELLOW. This is a lot more powerful than just having an exact match on security label (i.e., user's label must exactly match the data's label), since it has the notion of "groups" that make security administration easier to manage.

With this additional capability, we'll be able to implement that type of security scheme without requiring the application to access the data using special views or predicates.

DB2DD: Are there more security-related items coming soon in DB2 for z/OS?

Curt: There is another interesting security feature called security global variables. These variables can be set by the customer's connection or signon exit. A built-in function is used to retrieve the variable value. The built-in function can be used in views, triggers, stored procedures, and constraints to enforce a security policy. As an example, the customer might have a connection or signon exit that examines the SQL user's IP address, and maps the IP address to site within the company, which can be recorded in a variable named CAMPUS. The customer could create a view the uses the value of the CAMPUS named variable to restrict the end user's access to a subset of the rows in the DB2 tables.

 
CREAT VIEW V1 AS  SELECT * FROM T1 
WHERE COL5=DB2_SECURE_VAR('CAMPUS'); 

DB2DD: Are there any closing thoughts you'd like to add?

Curt: I talked about the SQL improvements that we're building, but we've also got a significant number of availability improvements, expanded support for very large databases, indexing improvements, and network computing enhancements. Customers will want to position themselves to take advantage of these new capabilities, so it is important to start moving onto DB2 for OS/390 and z/OS V7 if they aren't already there.

DB2DD: Curt, if people at the technical conference have more questions for you, where can they find you?

Curt: My session numbers are F02, Z13, and Z40. I'll also be around in the Birds of a Feather sessions in the evenings.

Notices

All statements regarding IBM's future direction or intent are subject to change without notice, and represent goals and objectives only.

About the author

Curt Cotner started in the IBM DB2® for OS/390® as a developer for the network component of the DB2 product. That assignment eventually grew into a broader role involving the design and implementation of all areas of the product that deal with client/server and network computing. Currently he's the lead architect for DB2 for z/OS^TM, where he is responsible for developing the overall DB2 for z/OS product plan and strategy, and for driving the evolution of DB2's network computing strategy.

He can be reached at cotner@us.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Notices
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers