If you make use of RTS and DEFINE NO, you might want to review your processes to ensure that you’re picking up all of the tablespaces you should be……
Since V7 of DB2 for z/OS, IBM has provided a Real Time Statistics (RTS) feature, which is a set of tables that DB2 keeps (more or less) up to date with recent activity against all tables and indexes in the system. In particular, the RTS tables provide detailed information on the amount of update activity that has happened since a given object was last REORGed or had RUNSTATS executed against it. That information is obviously very useful when determining when those utilities need to be run next, so many sites base their housekeeping schedules on this information (either using their own bespoke code and procedures, or a vendor tool such as IBM’s DB2 Automation Tool). In particular, looking at the number of INSERT/UPDATE/DELETE operations as a proportion of the total rows in the table (using the TOTALROWS column) is useful, so you can reorg a table when more than 10% of the rows have been changed, for example.
We came across a problem recently, where one of our customers was using this feature in a DB2 9 for z/OS subsystem that had lots of DEFINE NO tablespaces (which instructs DB2 to defer creating the underlying VSAM dataset until the table is actually referenced – very handy for ERP systems such as SAP that create a large number of tables that are never used in a given environment).
For normal DEFINE YES tablespaces, a row is created in the RTS tables as soon as the tablespace is created, with the statistics initially set to zero values and updated in real time to reflect activity against the underlying table. Unfortunately, the situation is different for DEFINE NO tablespaces: the row isn’t created until the VSAM dataset is defined on first use (no problem there) but instead of zeroes some fields (including the number of rows in the TOTALROWS column) are set to null instead. That null value isn’t updated until the first REORG or LOAD is run against the table, so if the table is populated via INSERT rather than LOAD, your nice RTS monitoring routines may never trigger that first REORG!
This “feature” has been reported to IBM, but in the meantime if you make use of RTS and DEFINE NO, you might want to review your processes to ensure that you’re picking up all of the tablespaces you should be……
January 26, 2010, 11 a.m., Eastern Standard Time, 4:00 p.m. UTC
Learn about the wealth of new features in DB2 9 for z/OS! Join this free teleconference from IBM and you will see how it’s new features improve business efficiency and reduce costs by increasing the productivity of administrators and programmers. In the presentation you will see how some of the common database administration and programming tasks can be done more efficiently in DB2 9. There will also be hints and tips about less obvious usage capabilities of selected DB2 9 features that solve some long-standing programming challenges that impact resources such as application controlled locking, destructive reads, efficient mass deletes and more.
In this session IBM will:
Explain the rationale behind implementing selected DB2 9 features
Provide usage recommendations for optimal exploitation of new functions
Describe less-known techniques to address specific programming tasks
Pinpoint areas where DB2 administrators and programmers can exploit the full power of DB29
Highlight areas likely to improve efficiency and productitivty of DB2 administrators and programmers
Speaker – Namik Hrle, IBM Distinguished Engineer, IBM Software Group, Information Management
If you’re looking to upgrade to DB2 z/OS version 9 or already have but want to get more from your investment then book some time in your diary to attend these upcoming IBM Webcast events:
Hints and tips to get the most out of IBM DB2 9 for z/OS – click here to register.
Join this complimentary teleconference and learn about the wealth of new features in DB2 9 for z/OS. You’ll see how its new features improve business efficiency and reduce costs by increasing the productivity of administrators and programmers.
Looking to Upgrade to DB2 9 for z/OS and improve productivity & reduce costs? Hear directly from one of our customers – UK Land Registry! – click here to register.
Listen to how Land Registry, a UK Government organization successfully upgraded to DB2 9 and is reaping the business benefits. This presentation will take you through the journey from start to finish, highlighting the risk-mitigating activities that were undertaken during the project lifecycle that contributed to a virtually impact free migration.
Despite several key performance enhancements delivered in DB2 version 8, some new customers saw little or no overall improvement due to the overheads inherent in the move to 64-bit architecture and other architectural changes. DB2 9 reverses this trend, delivering another set of significant performance enhancements without any additional overheads.
The latest news on what’s to come in the next version of DB2, currenly named DB2 X is largely around CPU savings. There is also a major push on analytics as part of the Information-Led Transformation Strategy that IBM have developed. As a result there have been extensions to the core databse engine in DB2 X to support warehousing queries and IBM has begin to talk of a much more fundamental and exciting development in the shape of it’s “Smart Analytics Optimiser”. This is essentially a BI/analytics appliance aimed at boosting the database query performance of the servers that it is attached to. The really big news is that it will be available to connect to system z servers very allowing DB2 for z/OS to offload suitable workload to the SAO for processing this bringing performance and cost advantages to the business.
So, what exactly is spatial data? Here’s a simple definition:
Spatial data represents real-world features and their relationship to one another. This includes geographic features (rivers, cities, mountains, forests, seas, lakes, etc), areas (flood zones, military exclusion zones, sales territories, etc and even events that occur at a specific location (a car accident, a crime, etc).
In its most basic form, spatial data is made up of one or more sets of co-ordinates that specify a location (usually but not always on planet Earth!).
Most businesses today already store extensive location data, for customers, suppliers, competitors, branches and many other items. For many large corporations, that data is stored in DB2 for z/OS. The availability of spatial features within DB2 for z/OS will allow those organisations to derive valuable new information and deliver new capabilities to their customers.
One of the major headaches all DB2 users face when upgrading to a new release is the possibility of access path regression. In order to benefit from enhancements to the optimiser, plans and packages need to be rebound under the new release. The vast majority of the time, this will result in the same or better access path being selected, but just occasionally DB2 may select a worse one and performance suffers.
IBM has delivered some very useful new functionality in the maintenance stream for DB2 9 for z/OS to help to address this issue. PK52523 provides some new options for REBIND that allow the old version of an access path to be stored. If regression occurs, the previous access path can be quickly and easily re-established with another REBIND. This will be a significant benefit for V8 customers moving to V9, removing one of the biggest migration pain points. This functionality will be further enhanced in future releases of DB2.
