DB2 SQL: Stages 3 and 4 By Craig S. Mullins All good DB2 developers and DBAs know about Stage 1 and Stage 2 predicates, right? But have you ever heard of Stage 3 and Stage 4 predicates? Well, you’re about to! First of all, let’s do a quick review to catch those readers who don’t know what Stage 1 and...
DB2
New Year’s Resolutions for DBAs
New Year’s Resolutions for DBAs By Craig Mullins At the beginning over every year many of us take the time to cobble together some resolutions for the coming year. We plan to lose weight, save money, stop smoking, and so on. Usually, it doesn’t take long before we’ve abandoned these resolutions. Perhaps we’d be wiser to make some business related...
Reading Things That Aren’t There… and Missing Things That Are!
Reading Things That Aren’t There… and Missing Things That Are! By Craig Mullins You can shoot yourself in the foot using DB2 if you are not careful. There are options that you can specify that may cause you to read data that is not really in the database. And, alternately, you can set things up so that you miss reading...
Materialized Query Tables…and the Death of Denormalization?
Materialized Query Tables…and the Death of Denormalization? By Craig S. Mullins Materialized Query Tables, or MQTs, are provided by many relational database systems as a means of speeding up access to static data. You also may know them as Automatic Summary Tables, or ASTs, but regardless of the name, the functionality is basically the same. Though not exclusively for data...
SELECT from DELETE, UPDATE, and MERGE
SELECT from DELETE, UPDATE, and MERGE By Craig S. Mullins A nice and relatively new SQL feature of DB2 for z/OS offers the ability to SELECT from DELETE, UPDATE, and MERGE statements. This was introduced in Version 9 and is similar to the SELECT from INSERT feature that was introduced with DB2 V8. So, before looking at the new V9...
The ‘Dirty’ Read (AKA Uncommitted Read)
The "Dirty" Read (AKA Uncommitted Read) By Craig S. Mullins Anyone accustomed to application programming when access to a database is required understands the potential for concurrency problems. When one application program attempts to read data that is in the process of being changed by another, the DBMS must forbid access until the modification is complete in order to ensure...
DB2 Buffer Pool Essentials (Part 2)
DB2 Buffer Pool Essentials (Part 2) By Craig S. Mullins Buffer Pool Page Stealing Algorithm Although most DB2 buffer pools are set up as discussed in the previous section, you can modify the page-stealing algorithm used by DB2 virtual buffer pools using the PGSTEAL parameter. When DB2 removes a page from the buffer pool to make room for a newer...
Using Nulls in DB2
Using Nulls in DB2 By Craig S. Mullins A null represents missing or unknown information at the column level. If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known....
Don’t Forget DISPLAY as a Part of Your DB2 Tuning Efforts (part 3)
Don’t Forget DISPLAY as a Part of Your DB2 Tuning Efforts (part 3) Utility Execution Information If you are charged with running (IBM) DB2 utilities, another useful command is DISPLAY UTILITY. Issuing a DISPLAY UTILITY command will cause DB2 to display the status of all active, stopped, or terminating utilities. So, if you are in over the weekend running REORGs,...
Don’t Forget DISPLAY as a Part of Your DB2 Tuning Efforts (part 2)
Don’t Forget DISPLAY as a Part of Your DB2 Tuning Efforts (part 2) Of course, there are many additional options that can be used in conjunction with the DISPLAY DATABASE command. The following options can be used to narrow down the amount of information displayed: USE displays what processes are using resources for the page sets in the database CLAIMERS...