Triton Consulting

March 2010- Unintended Consequences

We’ve all done it.  You see a great new feature in DB2 and jump headling into using it without fully appreciating all of the implications.  Here’s a case in point:

One of our clients is a large SAP customer running on DB2 for z/OS V9. With a huge number of subsystems to support and over 35,000 tables in each, taking traditional image copies would be a bit of a nightmare. Luckily, DB2 9 for z/OS contains the BACKUP/RESTORE SYSTEM function, which uses Flashcopy technology to rapidly backup and restore an entire system with a single utility. That means no messing with the traditional tablespace-level COPY utility, except when it’s needed for other reasons (such as when taking an inline image copy during an online REORG, to avoid putting the tablespace into COPY PENDING). 

So far, so good. However, a couple of weeks ago the DBAs came in one morning to find a lot of SAP users complaining about database errors, and dozens of tablespaces unable to be updated due to being in a COPY PENDING state. What the heck happened?

Visit our DB2 Geeks to find out

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……

Are you moving to Version 9 in 2010?  Attend the DB2 Symposium on 8-9th March for the DB2 for z/OS Version 9 Transition Class in London.

Look out for your 10% discount code in our newsletter coming out shortly.  Don’t currently receive our newsletter?  Subscribe here  . 

Already subscribe to our newsletter but can’t wait to book?  Email us today and we’ll send you your discount code.

 So, what exactly is spatial data? Here’s a simple definition:

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.

Read the full article