Samstag, 19 of April of 2014

Archives from month » Juni, 2012

Perl Script xmpPerlDB2Test.pl to verify DBI and DBD::DB2 installation

This Perl script verifies the interface to DB2 whicht consists of the general database interface, called DBI, and the DB2-specific supplement, called DBD::DB2.

This script uses the xmpPerl.pm library, which is also available at this site.


SQL timeouts due to automatic revalidation

DB2 V9.7 for Linux/Unix/Windows supports automatic revalidation of objects which is controlled by the database configuration value AUTO_REVAL. If requested by database administrator, this feature for example revalidates a views which was temporary invalid due to a DROP and re-CREATE of a base table or view. But administrators should play attention: Autoamtic revalidation may lead to lock timeouts (SQL0913N) under certain circumstances!

Example: You’ve DROP’ped and re-CREATE’d a table which is part of a view. Without automatic revalidation that view would be not usable and marked as INVALID. Usually you would have to re-CREATE the view. But if you’ve activated automatic revalidation (AUTO_EVAL [IMMEDIATE, , DEFERRED, DEFERRED_FORCE]) DB2 checks whether the invalidated object may be used IMMEDIATE, or when the first process/user try to access the object, so revalidation takes place in mode DEFERRED.

As long as no parallel processing requires an access to that object, auto revalidation works fine. But concurrent processing fails with SQL0913N because exclusive X-locks are hold until the end of the unit-of-work which has initiated the revalidation.

Example:

(1) Unit of work: (2) Concurrent unit of work:
DROP and CREATE of a base table,
view get invalidated
 
1. SELECT on view  
AUTO_REVAL starts revalidation 2. SELECT on view
data retrieval (S-lock) starts,
but view is locked with X-lock
waiting
still retrieving waiting
still retrieving timeout -> -913,
unit of work abends
finishes work,
commits work,
view revalidated and active,
accessible to concurrent processes.
 

Typical DB2 9.7 lock report:

Solution:

In case of concurrent processing you have to control and initiate the revalidation yourself:

    1. determine invalidates objects, e.g. views:

    1. initiate revalidation yourself


Perl statements to generate (DB2) timestamp

Do you want to generate a timestamp in external DB2 format YYYY-MM-DD-HH.MM.SS.ssssss?

Use either following functions which is already part of the xmpPerl.pm function library:

Or try following Perl statements which are compatible to POSIX and are portable to other platforms:


DB2 10 for z/OS System & Application Monitoring and Tuning

DB2 10 for z/OS

System & Application Monitoring and Tuning

IBM Silicon Valley Lab, San Jose, CA

May, 2012