Friday, February 27, 2015

DB2 Battle: Optimization Profile vs. Statement Concentrator (Part 2)

Today I wanted to try out using DB2 optimization profiles for a statement impacted by the statement concentrator. In part 1 I gave the background, showed how I created an optimization profile and that a simple explain statement didn't return what I expected. In this final part I am going to look at DB2 section actuals to hopefully proof that my optimization guideline works as I had hoped.

Because all my "explain plan for select ..." statements resulted in the same access plan, I decided to use session actuals to look at how statements are really executed within DB2. The actuals are kind of a live log of the real statement execution costs and the applied access plan. The first step towards session actuals is to have a workload and an activity event monitor and to switch the monitor on:

db2 "create workload betw applname('python') collect activity data with details,section"
db2 "create event monitor betwmon for activities write to table"
db2 "set event monitor betwmon state 1"

The above statements create a workload which collects section data. The application name (APPLNAME) is "python" because I use a Python script (see below) for parts of the testing.

import ibm_db
conn = ibm_db.connect("hltest","hloeser","secretpw")
ibm_db.exec_immediate(conn, 'set current optimization profile="HLOESER"."PROFILE_BETW"')
ibm_db.exec_immediate(conn, 'select id, s from betw where id between 2 and 20')

DB2 Battle: Optimization Profile vs. Statement Concentrator (Part 1)

DB2 explain output
Today I wanted to try using a DB2 Optimization Profile for a statement impacted by the DB2 Statement Concentrator. It turned out to be a longer exercise than thought, but also with more fun, covering a lot of the DB2 tuning and monitoring infrastructure. So set some minutes aside and follow my journey into the world of query optimization, workload monitoring, session actuals, and more. I split it into two parts because of length (Update on 02-Jul-2015: A third part is here).

The original question I tried to answer was related to REOPT and a query like this:
select id, s
from betw
where id between ? and ?

Wednesday, February 11, 2015

DB2 License Management, new Offerings, and an hour of fun

DB2 license management

Last month several changes for DB2 10.5 were announced. This included the new built-in database encryption ("native  encryption") as well as ability to license high-end functionality as add-on to base editions of DB2. The page "Functionality in DB2 product editions and DB2 offerings" provides a good overview of what is available in each of the editions, including the new offerings. With all the good news some customers asked me whether they would need to reinstall DB2 to use new functionality or when upgrading, e.g., from DB2 Enterprise Server Edition (DB2ESE) to DB2 Advanced Enterprise Server Edition (DB2AESE). The short answer is: "Single image". The longer is about to follow and involves an hour of fun playing with DB2...

DB2 is really flexible in how it gets deployed. This includes platforms, resource usage, shared disk or database partitioning models, product names, and product features. A key to this ability is some intelligent code and a related license management. I have written about the tool db2licm as well as license management and enforcement in the past, but let's take another look at db2licm, the license management tool. The tool allows to list, add, and remove licenses, change the way licenses are enforced, and to generate reports.

To list the license or licenses for my DB2 instance, I use the option "-l". As can be seen I have the "DB2 Developer Edition" (DB2DE) installed. It includes all features and is suitable for development environments.

[hl@mymachine] db2licm -l
Product name:                     "IBM DB2 Developer Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2de"
Version information:              "10.5"


To demonstrate recent changes to DB2, I need to get to a different DB2 edition. "db2licm -r db2de" removes the license of the installed Developer Edition. Invocation with the "-a" option and a license file installs a license. An overview of license files is available in the Knowledge Center. I chose a Workgroup Server Edition (DB2WSE) for my tests:

[hl@mymachine] db2licm -l
Product name:                     "DB2 Workgroup Server Edition"
License type:                     "Authorized User Single Install"
Expiry date:                      "Permanent"
Product identifier:               "db2wse"
Version information:              "10.5"
Max amount of memory (GB):        "128"
Enforcement policy:               "Hard Stop"
Number of licensed authorized users: "25"
IBM DB2 BLU Acceleration In-Memory Offering:          "Not licensed"
IBM DB2 Performance Management Offering:              "Not licensed"
IBM DB2 Encryption Offering:                          "Not licensed"
IBM DB2 Business Application Continuity Offering:     "Not licensed"

As can be seen, the Workgroup Server Edition is available and uses up to 128 GB of main memory, even if more memory is available on the machine. Interesting to note is the "Hard Stop" enforcement policy. It indicates that DB2 would block my attempts to use any unlicensed features. How about trying it out? I restarted my instance and tried it:

[hl@mymachine] db2 create db enc2 encrypt
SQL8029N  A valid license key was not found for the requested functionality.
Reference numbers: "8".

Because the edition does not allow to use database encryption my attempt to create an encrypted database is blocked. Can I change that? Yes, switching to a soft enforcement policy would allow using a feature like encryption, but would log it as license violation (see later).

[hl@mymachine] db2licm -e db2wse soft

LIC1413W  A soft stop enforcement policy has been set. This enforcement
      policy specifies that unlicensed requests will be logged but not


You issued the db2licm command with the -e parameter, to update the
enforcement policy, and specified the value SOFT. (For example, db2licm
-e db2ese SOFT.) The value SOFT specifies that unlicensed requests will
be logged but not restricted.

User response:

If you want unlicensed requests to be stopped, you must change the
enforcement policy to HARD. For example, db2licm -e db2ese HARD.

LIC1411I  Enforcement policy type updated successfully.

I restarted my DB2 instance and then tried again to create an encrypted database:
[hl@mymachine] db2 create db enc2 encrypt
DB20000I  The CREATE DATABASE command completed successfully.

With the soft enforcement it is possible to use unlicensed features, thereby violating the license terms (which is the reason to have HARD STOP). Anyway, after some testing with encryption and compression, I checked my compliance:

[hl@mymachine] db2licm -g lic.out

LIC1440I  License compliance report generated successfully.

[hl@mymachine] cat lic.out

License Compliance Report

DB2 Workgroup Server Edition   Violation
        Data Compression
        Index Compression
        Columnar storage

IBM DB2 BLU Acceleration In-Memory Offering:          "Violation"
        Query Parallelism
        Workload Manager
        Columnar storage

IBM DB2 Performance Management Offering:              "Violation"
        Workload Manager

IBM DB2 Encryption Offering:                          "Violation"

IBM DB2 Business Application Continuity Offering:     "Not used"

Not bad, right? Lots of violations in few minutes and all the new DB2 feature offerings are listed. To be compliant again, I installed the license for DB2DE again and reran the report:

[hl@mymachine] cat lic.out
LIC1439I  DB2 server has detected that "IBM DB2 Developer Edition" is installed on this system.  Products and functions obtained via this offering may only be used for testing or development purposes as outlined in your License Agreement.  The License Agreement for this offering is located in the 'license' directory in the installation path for this product.

As this I am using an universal license, there isn't much to report. However, the license terms point out that I cannot use that edition for production systems. Hence the long informational message.

That's it in terms "do I need to reinstall DB2 to make use of new feature offerings?". No, just apply the correct license or license mode.