As I talked about last week, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these really stand out and in the coming weeks I will write about some that can really help you reduce your CPU demand on your system and application environments.
One of the new features, selecting modified data, helps your applications minimize the number of SQL calls and the number of trips to the DB2 system. This feature provides the ability to get data values with SQL SELECTs clauses wrapped around your standard UPDATE, INSERT, MERGE and DELETE SQL statements.
By providing for the ability to perform an SQL SELECT from an UPDATE, INSERT, MERGE or DELETE SQL statement, the application is able to retrieve many important values previously retrieved in additional SQL statements. By retrieving these data values when the UPDATE, INSERT, MERGE or DELETE SQL statement is executed, the application can get data values for ROWIDs, identity, sequence, timestamp columns and other values or defaults in the database.
What is also great about using a SQL statement to select the modified data is that the application can get the data values of columns modified by BEFORE INSERT triggers. By getting these column data values, your application can use them in other parts of the application immediately. This is especially valuable for maintaining referential integrity relationships that need the values of ROWIDs, identity or sequence column values.
The new feature of selecting modified data or SQL SELECT from an UPDATE, INSERT, MERGE or DELETE SQL statements, is very powerful also because it can eliminate a large amount of network traffic and additional SQL calls. Processes and their related SQL that were done in multiple calls to DB2 can now be done in a single call. Additionally before selecting modified data SQL enhancement, these old multiple SQL calls sometimes had to transmit large chunks of data back and forth across the network. Now all that extra data transmission traffic is eliminated.
So review your current applications and see where there are multiple related SQL statements. By implementing this new powerful feature of selecting modified data or SQL SELECT from an UPDATE, INSERT, MERGE or DELETE SQL statement, you can dramatically reduce the number of SQL Calls, reduce your network traffic, and improve your application response time while reducing your overall CPU demand.
Leave a Reply