INCLUDE columns in IUD operations?!

When I read a question about INCLUDE columns that I received I first thought that indexes were meant. The reason is that for performance reason, to achieve index-only access, additional columns, not contributing to the index key, are added to an index during CREATE INDEX. These columns are called "include columns". However, the question was about include columns for INSERT, UPDATE, and DELETE. So what are they?

In OLTP environments it is often necessary to retrieve data that was just modified, i.e., inserted, updated, deleted. To save CPU cycles and improve response time why not combine the SELECT with the other statement? How this can be done is documented as part of the IUD operations (see links above) and the so-called data-change-table-reference clause. With DB2 you can access the data before (OLD TABLE) or after (NEW TABLE) that change was applied.


db2 => create table ict(id int, s varchar(80))
DB20000I  The SQL command completed successfully.
 
db2 => select * from ict

ID          S                                                               
----------- --------------------------------------------------------------------

  0 record(s) selected.
 
db2 => select id,s from new table(insert into ict values(1,'Hello World'))

ID          S                                                                    
----------- --------------------------------------------------------------------
          1 Hello World                                                          

  1 record(s) selected.

db2 => select id,s from old table(update ict set s='I am quiet' where id=1)

ID          S                                                                   
----------- --------------------------------------------------------------------
          1 Hello World                                                         

  1 record(s) selected.

db2 => select id,s from new table(update ict set s='Hello again' where id=1)

ID          S                                                                    
----------- ---------------------------------------------------------------------
          1 Hello again                                                         

  1 record(s) selected.

db2 => select id,s from old table(delete from ict where id=1)

ID          S                                                                   ----------- --------------------------------------------------------------------
          1 Hello again                                               

  1 record(s) selected.
 
db2 => select * from ict

ID          S                                                                   
----------- ---------------------------------------------------------------------

  0 record(s) selected.

So far, so good, but still no include columns. They are needed when you want to return more than what is in the rows that you modify, i.e., more columns are needed in the result set.

db2 => select id, s, ts from new table(insert into ict(id, s) include (ts timestamp) values(1,'Hello World',current timestamp),(2,'Hello again',current timestamp))

ID          S                                          TS           
----------- ------------------------------------------ --------------------------
          1 Hello World                                2012-10-24-15.08.06.762079
          2 Hello again                                2012-10-24-15.08.06.762079

  2 record(s) selected.

Of course there are better things to pass around than the timestamp. It could be a sequence value like the insert order, a function call, or whatever you make up.

That's all I wanted to include into this article...

Law License Enforcement options in DB2

Yesterday I received an interesting question regarding licensed features in DB2 and how the license is enforced. Someone was able to create an MQT even though the DB2 edition does not include support for it. But why? I try to explain.

The DB2 Information Center has an overview of features by edition. DB2 utilizes a license management server to control what features can be used and for reporting and auditing of compliance. The db2licm tool is the interface to the server and is used to add licenses, list features, and much more. Traditionally, DB2 had a "soft stance" on license enforcement, e.g., unlicensed features could be used without the software complaining (only the lawyers).

By customer request that eventually changed, so that compliance could be enforced on company level (and lawyers and finance could focus on keeping the books clean). From my time as DB2 developer I remember discussions on how to implement certain hard stops. In DB2 V9.5 hard enforcement of license compliance was offered for the first time AFAIK. I found these two DB2 9.5 fixpack notes on FP2 and enforcement of pureXML and storage features (which I was involved in) and FP4 changes on enforcement of WLM, CPU, and memory usage.

When installing DB2, by default enforcement is set to SOFT as it seems:
hloeser@ems:~$ db2licm -l show detail
Product name:                     "IBM Database Enterprise Developer Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2dede"
Version information:              "10.1"

Product name:                     "DB2 Advanced Enterprise Server Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2aese"
Version information:              "10.1"
Enforcement policy:               "Soft Stop"

The behavior can be changed using the "-e" (enforcement) option of db2licm:
hloeser@ems:~$ db2licm -e db2aese HARD

LIC1412W  A hard stop enforcement policy has been set. This enforcement
      policy stops unlicensed requests.

Explanation:

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

User response:

As a mechanism for you to keep track of, and differentiate, the DB2
database products and features installed on your system, it is
recommended that you register the license key for each DB2 database
product and feature.

If you want unlicensed requests to be logged but not restricted, change
the enforcement policy to SOFT. For example, db2licm -e db2ese SOFT



LIC1411I  Enforcement policy type updated successfully.

As I have AESE installed, it does not really allow me to show you an example. But you could try to compress or create an MQT on DB2 Express-C. Here is the output after switching the enforcement:
hloeser@ems:~$ db2licm -l
Product name:                     "IBM Database Enterprise Developer Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2dede"
Version information:              "10.1"

Product name:                     "DB2 Advanced Enterprise Server Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2aese"
Version information:              "10.1"
Enforcement policy:               "Hard Stop"

As mentioned, SOFT has been the traditional way and is the default in DB2. However, to be compliant and to be on the safe side for audits, switching to HARD is a good idea.

BTW: You can generate a compliance report using db2licm:
hloeser@ems:~$ db2licm -g comp.txt

LIC1440I  License compliance report generated successfully.Anything troublesome in there...?

Updated Redbook for DB2 10: High Availability and Disaster Recovery Options

Well, there is not much to say about this existing Redbook that has been updated to reflect DB2 10.1 for Linux, UNIX, and Windows and current technologies. The "High Availability and Disaster Recovery Options for DB2 for Linux, UNIX, and Windows" Redbook describes and explains technologies like IBM Tivoli TSA, PowerHA SystemMirror, Microsoft Windows Failover Cluster, WebSphere Q Replication or InfoSphere CDC.

With close to 600 pages it also requires your high availability...

Answer to DB2 Quiz: Don't forget to close what was opened...

Earlier this week I posted a DB2 quiz showing an error message. It showed that creating a function failed with an "end of file reached" error. I received some feedback by email and the consensus was that the error somehow was caused by the earlier executed scripts. But what exactly is the cause?

In the quiz I had mentioned that we ran into the error during a proof of concept when porting an application from Oracle to DB2. This is a first hint. The DB2 Information Center has a document describing how to set up the DB2 environment for Oracle application enablement. In there are two versions for executing SQL statements listed, one based on clpplus, another one using the DB2 Command Line Processor. For running statements with the DB2 CLP it is recommended to use "SET SQLCOMPAT PLSQL" first. That switches the CLP to taking the forwad slash ("/") on a new line as PL/SQL statement termination character. Using "SET SQLCOMPAT DB2" switches back to regular operations.

The error "end of file reached" indicates that a statement is incomplete. This could mean a statement is missing a clause (usually a syntax error) or the statement or file is missing something. Is it a statement terminator? What we tried then while debugging is to execute a simple CREATE TABLE:

>>> db2 -tf table2.sql
DB20000I  The SQL command completed successfully.

Strangely, this works. Is it related to our function definition? We opened another window with a new DB2 connection and the CREATE FUNCTION when directly pasted into the CLP window succeeded. And then it dawned on us...

As with other settings, including the database connection, they are kept between invocations of DB2 CLP. In the example in the quiz we only open the database connection once. In one of the scripts that we executed, the SET SQLCOMPAT PLSQL was set, but not reset to the default using SET SQLCOMPAT DB2. Simple DDL like CREATE TABLE seems unimpacted by this setting. However, creating more complex objects like the shown function gives an error. I asked the DB2 Information Development team (the one creating the documentation) to provide details on the SQLCOMPAT switch.

What can be taken away is to make sure that when putting a switch into a file, make sure switching back to defaults is part of the same file. Sounds like best practices from coding days... ;-)

BTW: I disabled captcha codes for comments. This should make it more interactive and lead to less emails. Have a great weekend.

DB2 Quiz: Why the error? (create function)

Last week during a Proof-of-Concept (PoC) with an ISV we tried to port and deploy database objects from Oracle to DB2. During some testing I ran into a strange error (at least at first) and only stepping back and getting some coffee helped to explain it. Can you guess what went wrong and why we ran into the error?

For the actual porting we used the IBM Data Movement Tool, but the DB2 Command Line Processor for some side testing. Here is what we did on the command line:

>>> db2 connect to mydb

   Database Connection Information

 Database server        = DB2/LINUX 10.1.0
 SQL authorization ID   = HLOESER
 Local database alias   = MYDB


>>> db2 -tf script1.sql
DB20000I  The SQL command completed successfully.

>>> db2 -tf scriptN.sql
DB20000I  The SQL command completed successfully.

>>> db2 -tf func.sql
DB21007E  End of file reached while reading the command.

>>> cat func.sql
CREATE OR REPLACE FUNCTION TAN (X DOUBLE)
     RETURNS DOUBLE
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN SIN(X)/COS(X);

>>> db2 -tf func.sql
DB21007E  End of file reached while reading the command.

Basically we successfully tested several SQL scripts before we ran into the "end of file" error. There were no special hidden characters in the file. Opening another database connection in another window and executing the script there succeeded.

Do you know why? Any guesses? I will post the solution in a follow-up article.

IBM PureData follows PureFlex and PureApplication Systems

IBM PureData System for Transactions and IBM PureData System for Analytics are two new categories of IBM's expert integrated systems, the so-called PureSystems.

The PureData System for Transactions is built on the DB2 pureScale technology and hence provides scalability and operational continuity. Using DB2's SQL compatibility mode it is also an attractive offer for customers wishing to move away from Oracle database systems.
The PureData System for Analytics actually distinguishes between Analytics and Operational Analytics. The former is built using Netezza technology, the later follows the tradition of InfoSphere Warehouse and IBM Smart Analytics System.

The common benefit of all three new systems is that they provide simplification from A-Z, starting from procurement (one HW/SW piece only) over getting ready for production (my colleagues are claiming hours) to maintenance/administration. They all integrate best practices (like those found here) from experience in the field when working with customers.

BTW: The new systems are nothing for you if you like to fiddle around with OS and DB2 configuration paramaters and want to personally tune everything. That was already done by IBM experts.