Wednesday, July 30, 2014

DB2 Quiz: Which tool provides this output?

Another day, another DB2 quiz. Below is the partial output produced by a DB2 tool. Which tool is it? It is part of every DB2 LUW installation.

COLUMN NAME               : ID
 Length of column name     : 2
 Column id                 : 0
 Col type {hex, type}      : 0x0001 ,  INTEGER
 Col dist. type {hex, type}: 0x0018 ,  INTEGER
 Column length             :      4
 Blob length               : 4
 Statistic offset          : 480
 Column String Units       : N
 Flags                     : 0x20000090
                             - SQLRG_MIXED
                             - SQLRG_NULLIND
 Flags2                    : 0x00000000
 Codepage                  : 0
 Collation name            : NOT_APPLICABLE
 Collation ID in hex       : B'FF FF FF FF FF FF FF FF
 Length of user default    : 0
 Logged                    : 0
 Compact                   : 0
 Inline Length             : 0
 Datalink Features         : 000000
 Ref. row type desc. offset: 0
 No. unique values in col. : 2
 Average column length     : 5
 Expected Change Rate      : 0.000781
 Percentage Encoded        : -1
 Page Variance Ratio       : -1
 Source column ID          : 0
 High2key and Low2key len. : 8
 Avg column length in chars: -1
 Delimiter length          : -1
 Number of Subelements     : -1
 Number of nulls           : 0
 High2key                  : 2
 Low2key                   : 1
 Statistic Descriptor      :
  colid                    : 0
  fd offset                : 16
  hd offset                : 64
  nmostfreq                : 2
  mtiles                   : 4
 Histogram Descriptor      : Count   Distcount   Value_len   Value
                             ---------------------------------------------
                             0     0     4            1
                             1280     0     4            1
                             1280     0     4            2
                             2560     0     4            2
 Frequency Descriptor      : Count   Value_len   Value
                             ---------------------------------
                             1280  4             1
                             1280  4             2
 Security Label ID         : 0

           
 COLUMN NAME               : S
 Length of column name     : 1
 Column id                 : 1
 Col type {hex, type}      : 0x0101 ,  VARCHAR
 Col dist. type {hex, type}: 0x0038 ,  VARCHAR
 Column length             :   2000
 Blob length               : 2000
 Statistic offset          : 504
 Column String Units       : S
 Flags                     : 0x20000090
                             - SQLRG_MIXED
                             - SQLRG_NULLIND
 Flags2                    : 0x00000000
 Codepage                  : 1208
 Collation name            : IDENTITY
 Collation ID in hex       : B'00 00 00 02 FF 00 FF FF
 Length of user default    : 0
 Logged                    : 0
 Compact                   : 0
 Inline Length             : 0
 Datalink Features         : 000000
 Ref. row type desc. offset: 0
 No. unique values in col. : 2
 Average column length     : 86
 Expected Change Rate      : 0.000781
 Percentage Encoded        : -1
 Page Variance Ratio       : -1
 Source column ID          : 0
 High2key and Low2key len. : 66
 Avg column length in chars: 81
 Delimiter length          : -1
 Number of Subelements     : -1
 Number of nulls           : 0
 High2key                  : {'This is a long teeeeeeeeeeeeeeee}
 High2key in Hex Format    : 27546869732069732061206c6f6e67207465656565656565656565656565656565
 Low2key                   : {'Another                         }
 Low2key in Hex Format     : 27416e6f7468657220202020202020202020202020202020202020202020202020
 Statistic Descriptor      :
  colid                    : 1
  fd offset                : 16
  hd offset                : 96
  nmostfreq                : 2
  mtiles                   : 4
 Histogram Descriptor      : Count   Distcount   Value_len   Value  Hex(value)
                             ---------------------------------------------
                             0     0     8            {'Another}   27416e6f74686572
                             1280     0     8            {'Another}   27416e6f74686572
                             1280     0     33           {'This is a long teeeeeeeeeeeeeeee}   27546869732069732061206c6f6e67207465656565656565656565656565656565
                             2560     0     33           {'This is a long teeeeeeeeeeeeeeee}   27546869732069732061206c6f6e67207465656565656565656565656565656565
 Frequency Descriptor      : Count   Value_len   Value  Hex(value)
                             ---------------------------------
                             1280  8             {'Another}   27416e6f74686572
                             1280  33            {'This is a long teeeeeeeeeeeeeeee}   27546869732069732061206c6f6e67207465656565656565656565656565656565
 Security Label ID         : 0
 

Monday, July 28, 2014

What's Going On? - DB2 Workload Management: Identification of Activities

In an earlier blog post I had written about why Workload Management is needed. It's not just something for the database system or on the operating system level, it is really useful and done in "real life". But what is managed in the system, how are you able to identify activies in a DB2 database system? I am going to explain that today.

Before I dig deeper into the identification, first we need to clarify what is meant with "activity". It could be almost anything going on in the database system that is related to a single database and could be both user- and system-related tasks. The important distinction is that is on the database level, not for a DB2 instance. Identification of activities deals with three questions: WHO is doing WHAT on my database and WHERE is that data located?

The WHO can be answered by looking at the connection properties such as:
  • Who is the user and which group does the user belong to? 
  • Is the user operating in a special role?
  • From where is the user connecting, does the machine have a name, is it from a specific application?
In DB2 these properties can be checked and used for identification of an activity by defining a workload (CREATE WORKLOAD). The workload object deals with the WHO. The WHAT and WHERE aspect of identification are handled by work classes. They are defined as part of a so-called work class set (CREATE WORK CLASS SET). Each work class can be used to identify an activity based on the type of work it is performing on the database and its related costs (the WHAT part). The type of work could be any combination of LOADing data, defining, altering or dropping objects (DDL), or read or write operations as part of select, insert, update, and delete statements (DML). The cost is what has been estimated by the DB2 compiler/optimizer and is the expected overall execution cost (timeron cost) or the cardinality (how many rows are we expecting in the result). Note that the actual cost of an activity is dealt with as part of controlling and managing the activities which I plan to describe in a later article.

By specifying a "data tag" for a work class, it can be related to storage groups or tablespaces and their priority. This is how activity can be identified by from WHERE the data is processed.

Because multiple work classes in a work class set could identify the same activity, the individual work classes can be ordered/positioned within the set. That way a work class with several properties could pick a very specific activity whereas other activities would be mapped to more general work classes.

Using the concepts of WORKLOAD and WORK CLASS SET it is possible to identify an activity. They help to understand what is going on in the DB2 database system. It is the prerequisite for actively controlling and managing the activities in the system by assigning resources.

Quiz? State of Affairs...!

This is not really a quiz (if you followed by advice to try out IBM Bluemix), but merely shows the state of affairs:

Current State
What does it say to the trained eyes? Where was screenshot taken, what does it indicate? Any takers?

LinkWithin

Related Posts with Thumbnails