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.
Script:
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')
Henrik's thoughts on life in IT, data and information management, cloud computing, cognitive computing, covering IBM Db2, IBM Cloud, Watson, Amazon Web Services, Microsoft Azure and more.
Friday, February 27, 2015
DB2 Battle: Optimization Profile vs. Statement Concentrator (Part 1)
DB2 explain output |
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 ?
Labels:
applications,
best practices,
catalog,
DB2,
diagnostics,
fun,
indexing,
IT,
performance,
Python,
query,
sql,
version 10.5
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"
Features:
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
restricted.
Explanation:
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
Encryption
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"
Encryption
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.
Subscribe to:
Posts (Atom)