Wednesday, September 25, 2013

xkcd comic: Opinions on Internet Privacy - what to add?

xkcd has a nice comic about different opinions on Internet Privacy. They are all good and valid, but are missing opinions of those who really care about data: Us. So what would be a panel or frame you what like to add to that comic..?

Let me add this one: The IT-conscious meteorologist - "This must be real, there are no clouds in Utah".

Tuesday, September 24, 2013

ROI at IOD 2013, Some numbers for your boss

The Information On Demand conference 2013 is a good month away and it is time to register. There is the IBM IOD Hub at to share information in advance of the conference, during it, and later report on all the stuff you missed (and some marketing of course... :). The following infographic is from that side and lists some reasons why to attend and why your money-conscious boss should send you there (I have to talk with my bosses now).

At past conferences I successfully did some product certifications, visited hands-on labs (actually teaching them), and had good technical sessions that provided new insights into existing products and showed technical directions and technology trends (what to prepare for, what to investigate, etc.). And most importantly, maybe not to emphasize when talking with your boss, is what is listed on the lower right: FUN.
Top Five Reasons Infographic.jpg

Everything is my IP: Obfuscating a "Good Morning"

Want to impress or scare your co-workers? Or show your boss how to hide Intellectual Property (IP)? Try this with DB2:

db2 => create view hello  WRAPPED SQL10050 ablGWmdiWmtmTmdKTmJqTmdKUmteUmduUnZq1mZK2idaWmdaWmdaWndjHBcaGicaGQIwPJVuycUYnzvpYqeM0J9mNKgGspriVQtaGRWxKEiYWqvXK6WNaJSUbhrqa
DB20000I  The SQL command completed successfully.
db2 => select * from hello

Good Morning

  1 record(s) selected.

Even though the functionaliy for it was introduced in DB2 9.7 FP2, probably not many have looked at obfuscation of SQL code, i.e., the body of routines, views, triggers, and packages or modules. Basically, if some business or application logic is inside the database, obfuscation might be an option. This could be either because you want to protect your IP or for security reasons. At the time code obfuscation was released for DB2 in 2010 I wrote that it would be possible to abandon spaghetti code as a way of protection.

It is pretty simple to use obfuscation as everything needed is in the built-in module DBMS_DDL. It has a function WRAP and a procedure CREATE_WRAPPED. When WRAP is called with a valid statement as parameter, it returns a string with the DDL statement to create a wrapped, i.e. obfuscated, version of it. The procedure CREATE_WRAPPED is called with a valid DDL statement, transforms it to the wrapped version and directly executes it. WRAP could be called to produce encoded statements to be embedded in text files. The files would be given to customers. CREATE_WRAPPED could be called directly from an application which creates objects inside a database. Customers would have a hard(er) time to reverse engineer the application logic of functions, procedures, or entire packages created in the database.

So instead of creating the view "hello" as shown above, my application could have issued the following statement:
call dbms_ddl.create_wrapped('create view hello (greet) as select ''Good Morning'' from dual')

And now you know the logic that was hidden inside the strange string of my first SQL statement above ("ablGWmdi..."). Try to memorize that pattern and impress your co-workers or boss...

BTW: If you are a hardcore DB2 user and have never used the DUAL table, read here.

Friday, September 20, 2013

BLU: Begin Learning blU - A good overview and introduction

A so-called rapid adoption guide for DB2 with BLU Acceleration has just been published. It gives an overview of all relevant topics, provides the necessary information at a glance and then links to more resources for those interested in the details. Are you ready to Begin Learning blU (BLU)?

That's all for today. Quick, simple and to the point like the rapid adoption guide... :)

Thursday, September 19, 2013

Redpaper: IBM Predictive Maintenance and Quality Technical Overview

When I first saw the title of a new IBM Redpaper, I got curious about the term "predictive maintenance". However, in the IBM Predictive Maintenance and Quality Technical Overview paper the different forms of maintenance are described:
  • Reactive: Replace it after it has failed
  • Preventive: Replace it according to a (manufacturer) suggested maintenance schedule
  • Condition-based: Monitor by regular inspections and then service or replace
The approach of preventive maintenance uses sensor data provided by the devices and the experience from using that equipment in a specific environment (your environment) to predict possible failures and also to predict the quality of individual parts. Overall this leads to better use of the equipment and optimized maintenance.

This alone makes it an interesting topic. When you look at the technical details of the solution you will find DB2 and some IBM business inteligence
/business analytics products involved.

I recently wrote about adjusting for "back to school". Wouldn't be it nice to have a predictive maintenance solution for the kids' school stuff? Often it is replacing pens, pencils, exercise and copy books by request only or after getting a "hint" from the teacher...

You can find more on DB2 and analytics by reading my posts on DB2 with BLU Acceleration, the new way of accelerating analytic workloads with DB2.

Tuesday, September 17, 2013

(Updated) Quiz: What are these tables for?

Here is a quiz for all those administrators and DB2 fans that have insights into more than the core engine (that is the hint...:).

What are these two tables used for?

hloeser@rotach:~$ db2 "describe table MANAGED_DATABASE"

                           Data type                     Column
Column name                schema    Data type name      Length     Scale Nulls
-------------------------- --------- ------------------- ---------- ----- ------
NAME                       SYSIBM    VARCHAR                    255     0 No   

  1 record(s) selected.

hloeser@rotach:~$ db2 "describe table MANAGED_DATABASE_PROPS"

                           Data type                     Column
Column name                schema    Data type name      Length     Scale Nulls
-------------------------- --------- ------------------- ---------- ----- ------
NAME                       SYSIBM    VARCHAR                    255     0 No   
PROPERTY_KEY               SYSIBM    VARCHAR                    255     0 No   
PROPERTY_VALUE             SYSIBM    VARCHAR                    255     0 Yes  

  3 record(s) selected.

Any guesses or even some background information you want to share?

(Updated) Hint 2: It is related to Data Studio.

Monday, September 16, 2013

Redbook updated for DB2 10.5: Oracle to DB2 Conversion Guide - Compatibility Made Easy

The IBM Redbook "Oracle to DB2 Conversion Guide - Compatibility Made Easy" has been updated to include coverage for DB2 10.5. The book now describes the new compatibility features added as part of DB2 10.5 and the improved functionality of the Database Conversion Workbench. Readers will also find the DB2 Advanced Workgroup Server Edition which was added in the latest release.

Happy conversions, may all of your applications convert... ;-)

Friday, September 13, 2013

Gradual Adjustments: Back to school, epilepsy medication, and DB2

This week school started again for the kids (and parents). It means to adjust to getting up early in the morning (where is the extra hour of sleep?), getting used to new and different schedules, and coping with homework. It is a process that isn't done in the blink of an eye, but which takes time. Most adjustments are "in the system" within a day or two, the rest is a matter of few weeks until everybody is really back in school mode/mood.

As I wrote before, one of my sons has/had epilepsy. Since middle of June we are happy to phase out his medication. Every 4-5 weeks we are reducing the dose of tablets he has to take, so that his body can adjust to the changed "chemical cocktail". After each change it takes a week to get used to it, then the remaining 3-4 weeks to really adapt to the new dose. Getting rid of medication is not an abrupt event, it takes a while.

Guess what DB2' autonomic features, especially the self-tuning memory manager (STMM) are doing? For a change in the workload characteristics STMM tries to adjust the configuration, the amount of available memory for different consumers. First it usually is a bigger adjustment, then it gradually moves to the final state. In a pureScale environment, previously a single member was in charge of choosing the memory configuration if STMM was active. Starting with DB2 10.5, you can specify whether each member is adjusting the memory configuration on its own, or a single member should do it and whether you pick that member or DB2 dynamically picks it.
So is it like "back to school"? I guess not, every family member has to adjust at the same pace and over night...

Thursday, September 5, 2013

Oracle DBLINK and DB2 Three-Part-Names for Federation

One of the new features that went mostly unnoticed for DB2 10.1 and DB2 10.5 is the support for so-called three-part-names (nice pun?) for Federation. It allows the access to remote tables without the need to create nicknames. Thus, applications and users can be more dynamic and agile in what they access (there are drawbacks to it, too). When the DB2_COMPATIBILITY_VECTOR is set accordingly, even the Oracle-like DBLINK syntax of "schema.table@database" is tolerated. So much for the theory, I tried to get it to work on my laptop...

I already had two different databases created, and the DB2 compatibility vector is set to "ORA". Thus, I could focus on Federation-related commands. The first thing to do is to enable my DB2 instance for Federation (FEDERATED is the configuration parameter).
db2 update dbm cfg using federated yes

The next commands are directly executed in my first, "local" database, after connecting to it:
db2 => create wrapper drda
DB20000I  The SQL command completed successfully.

This creates the DRDA wrapper which is required to access DB2 sources. If you want to access Oracle databases from within DB2, you would need to create the NET8 or Oracle wrapper and some more steps which are described here. As I am going to access a local DB2 database, I don't need to catalog any nodes or map users. So what is left is to create a server, i.e., to make the database I want to access known to my database I am operating with.

db2 => create server myserver type db2/cs version 10.5 wrapper drda authorization hloeser password "secrets4you" options (dbname 'DBTWO')
DB20000I  The SQL command completed successfully.

Thereafter, I can already access the remote table using either a three-part-name or the Oracle dblink syntax.

db2 => select * from myserver.henrik.worldtest

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

  1 record(s) selected.

db2 => select * from henrik.worldtest@myserver

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

  1 record(s) selected.

Both statements do not require a nickname to be created, thus saving administration time and allowing to migrate Oracle applications more easily. One thing to note is that I need to connect to my local database with the user option in order for Federation to pass on my credentials. That is "connect to dbone user henrik" will work, just using "connect to dbone" will bring you authentication errors or SQL0204N "... is an undefined name".

Have you tried out dblink syntax with DB2? Comments?

Best Practices for DB2 with BLU Acceleration

A new Best Practices paper has been published for DB2, this time on using BLU Acceleration. The paper "Best Practices: Optimizing analytic workloads using DB2 with BLU Acceleration" can be downloaded from the Best Practices website. To get the most out of BLU the authors cover topics like identifying good workloads, capacity planning and configuration, then dig deeper into day to day activities. This includes understanding the synopsis tables and query plans and how to monitor the system.

Personally I like the fact that several from my former SVL team contributed to the paper ("Hello" from Germany).

BTW: This is another DB2 BLU-related post.