Sunday, December 1, 2013

When DB2 creates result table of a query and how insert, update, delete affects the result table


IBM manual does not clearly explain when it exactly creates a result table. Below information is taken from DB2 10 manual.
DB2 can process a cursor in two different ways:
  • It can create a temporary copy of the result table during the execution of the OPEN statement. You can specify INSENSITIVE SCROLL on the cursor to force the use of a temporary copy of the result table.
  • It can derive the result table rows as they are needed during the execution of later FETCH statements.
If the result table is not read-only, DB2 uses the latter method. If the result table is read-only, either method could be used. The results produced by these two methods could differ in the following respects:
When a temporary copy of the result table is used: Insert operations that are executed while the cursor is open cannot affect the result table once all the rows have been materialized in the temporary copy of the result table. For a scrollable insensitive cursor, update and delete operations that are executed while the cursor is open cannot affect the result table. For a scrollable sensitive static cursor, update and delete operations can affect the result table if the rows are subsequently fetched with sensitive FETCH statements.
When a temporary copy of the result table is not used: Insert, update, and delete operations that are executed while the cursor is open can affect the result table. The effect of such operations is not always predictable.

Wednesday, November 27, 2013

Why CPU time varies between different runs of the same job or job step

Following information was taken from IBM manual "z/OS V1R11.0 MVS System Management Facilities (SMF) z/OS V1R11.0 SA22-7630-21"


There are many reasons why CPU time varies between different runs of the same job or job step. The following list describes some of the major causes of variation:
  • Cycle stealing on systems with integrated channels — CPU instruction processing is temporarily suspended when channels require the use of hardware resources shared with the CPU.
  • CPUs using a high speed buffer — CPU time may vary due to any of the following:
    • Buffer interference caused by concurrent tasks
    • Partial or full disabling of a buffer because of storage errors
    • Translation lookaside buffer (TLB) affect on instruction processing rate.
  • Storage access — The CPU cannot access central storage if a channel is using it. Storage-access time depends on CPU architecture such as interleaving, data widths and paths.
  • DASD space allocation — If the number of extents is not exactly the same as before, additional end-of-extent processing is required.
  • Temporary I/O errors — Additional processing may be required for temporary I/O errors.
  • Expanded storage frame accesses — If a run has a different number of such accesses than another run, the CPU time may differ. Accessing expanded storage requires more time than central.
  • BLDL/FIND requests — If BPAM is used extensively, CPU time for processing BLDL/FIND requests varies if there was change in the PDS directory. That is, a change in the location of the entry for the required member is reflected by a change in the time needed to find the block containing it.
  • STOW processing — A difference in the PDS directory may vary STOW processing time because of the additional reordering or bumping that may be necessary.
  • Macro processing — Processing time for macros such as LINK, LOAD, XCTL, ATTACH and BLDL is affected by where the requested module is located. For example, CPU time may be less if the module is in the LPA and JOBLIBS and STEPLIBS are not used.
  • Availability of serially reusable resources (locks) — For example, the system ENQ routine time will vary depending on whether the resource is available. DEQ time increases if other tasks have subsequently requested the resource that the current task is releasing.
  • Wait processing — CPU time varies depending on whether ECBs have been posted before issuing the WAIT macro instruction.
  • Lock spins — If a job is run on an MP, CPU time may vary due to lock spins encountered in supervisor services.
  • Queue searching — System service time varies with the status of the queue environment.
  • Generalized trace facility (GTF) — When GTF is active, CPU time increases depending on the system functions (SVC, SSCH, IO, PCI, DSP) that are selected for current GTF recording. If USR functions are to be recorded and the application contains GTRACE macros, the CPU time variability is even more pronounced.
  • System resource manager (SRM) — SRM processing may cause CPU time to vary when it is invoked from supervisor services that issue SYSEVENT, such as ENQ, WAIT (LONG=YES option), TPUT and TGET.
  • Page stealing — Page stealing affects the number of page faults that a particular job incurs. CPU time varies depending on both the number of page faults resolved by I/O and the number of page faults resolved by reclaim.
  • Sequential access method and chain scheduling — CPU time can vary from run to run depending on the amount of chain scheduling that was successful. The number of starts for I/O will vary under different system loads.
  • MP-serialization — Contention for resources between CPs in a processor will vary.
  • Branch tracing and mode tracing — These operations require additional processing.
  • SLIP PER trap — Dealing with PER interrupts requires additional processing.