DB2 UDB for OS/390 and z/OS Application and SQL Performance Trade-Offs

by Daniel L. Luksetich
April 1, 2005

Performance is a primary concern for most application implementations, but what’s the cost of high performance? We use relational databases for a reason. They’re supposed to make things easier by separating the user from the responsibility of having to know where and how their data is stored and managed. All the user needs to know is what they want, and the database is supposed to find it. The SQL language itself is a powerful programming language, enabling users to quickly answer complex questions. Advanced database objects such as constraints, triggers, stored procedures, and user-defined functions also play a significant role in application development and performance.

Any advanced database and application design can have a significant performance impact, so we spend time tuning, but at what cost? There have to be trade-offs in any database design. Do you want centralization, reusability, security, availability, ease of maintenance, quick time to delivery, or do you want the highest performance? Maybe you just want it all.

Locating Code at the Server

There are many advanced features of DB2 Universal Database (UDB) for z/OS that let you take advantage of the power of the mainframe server:

- Complex SQL statements

- User-Defined Functions (UDFs)

- Stored procedures

- Triggers and constraints.

These advanced features facilitate rapid application development by pushing some of the logic of the application into the database server. Usually, advanced functionality can be incorporated into the database using these features at a much lower development cost than coding the feature into the application itself. A feature such as database-enforced Referential Integrity (RI) is a perfect example of something that’s easy to implement in the database, but would take significantly longer time to code in a program.

These advanced database features also let you place application logic as part of the database engine itself, making this logic more easily reusable. Reusing existing logic will mean faster time-to-market for new applications that need that logic; having the logic centrally located makes it easier to manage than client code. Often, having data-intensive logic located on the database server will result in improved performance as that logic can process the data at the server, and only return a result to the client (see Figure 1).

Complex SQL

SQL has evolved into a robust programming language. With correlated nested table expressions, UDFs, common table expressions, and recursion, most program processes can be written as a single SQL statement. This is extremely valuable because SQL is the world’s most portable language. Yes, Java can run on a PC, mainframe, midrange server, or cellular telephone, but SQL is even more portable because it always runs on the server to which it’s directed. For mainframe applications, this is extraordinarily valuable. Any statement that can run under SPUFI on the mainframe can be cut and pasted into any piece of application code using any of several programming languages on several client platforms.