Security and DB2 LUW

Did you recently start paying more attention to credit card bills? Thinking twice before speaking on the phone or sending a text message? Cutting short on communication with your partner...? Awareness for topics such as privacy and data security has increased dramatically over the past few months. In some industries such as banking, the supervisory authorities - in Germany it is BaFin - have tightened regulations over several years, requiring changes to how databases are set up and administrated, how data can be stored and accessed. As I recently declared 2014 as the year of database security, I thought collecting some related DB2 resources would be a good way to promote it. Here we go...

When you work with DB2 for Linux, UNIX, and Windows, and are researching a topic, then the DB2 Information Center is a good start. It has an entire section on security (look at the navigation section on the left). It explains the DB2 Security Model, various security-related concepts, and has links and background information on some IBM InfoSphere Guardium tools. Many security and auditing tools as well as the Data Encryption (formerly Encryption Expert) product are labeled Guardium.

Other places to visit are the DB2 Best Practices, IBM Redbooks, and IBM developerWorks (list of DB2 security articles). There is a IBM Data Server Security best practices paper and also a redbook "DB2 Security and Compliance Solutions for Linux, UNIX, and Windows". You can also learn about security functionality when attending one of the offered Information Management bootcamps or taking a DB2 class through Learning Services.

Last but least, before I start my weekend, I would like to point to the blog articles I have written on DB2 security topics.

Have a nice weekend and watch your transactions...

New Redbook: Leveraging DB2 10 for High Performance of Your Data Warehouse

A new (almost) and very interesting IBM Redbook has been published last week: Leveraging DB2 10 for High Performance of Your Data Warehouse. Though the title says "DB2 10", parts of the book deal with DB2 10.5 and the BLU Acceleration technology. And the reason I said "almost new" is that the book is partially based on the redbook from 2010 "InfoSphere Warehouse: A Robust Infrastructure for Business Intelligence". As you can see, DB2 has a proven track record of supporting Data Warehouses and Data Marts.

The new book shows you how to get started with column-oriented storage that is key to benefitting from BLU Acceleration. It covers how to create data marts, load them, understand query plans (including the CTQ operator) and also how to monitor the system.

Similar to many other IBM Redbooks, this one is available for download as PDF, as EPUB or to view in HTML.

BTW: You can find all my DB2 BLU-related articles using this link.

Using hidden columns for range partitioning (Temenos T24)

In one of my post regarding performance tuning for Temenos T24 on DB2 I hinted that a hidden column could be used to range partition a table. There have been questions on how to do it and what to use. As always, it depends... ;-) Below I give you some examples of how it could be done, not only for Temenos T24.

I already introduced you to the concept of implicitly hidden columns some time back. DB2 allows to hide and unhide columns, thereby making it possible to have columns not showing up in the default result set. That feature can be used to alter the table layout (database schema) without impacting the application. Here, in the examples below, range partitioning is introduced with the hidden column as foundation for the data ranges.

CREATE TABLE ACCOUNT 
(
     RECID VARCHAR(10) NOT NULL PRIMARY KEY,
     D DATE IMPLICITLY HIDDEN DEFAULT CURRENT DATE,
     XMLRECORD XML
)
PARTITION BY RANGE(D)
(STARTING FROM '01.01.2012' ENDING AT '31.12.2014' EVERY (3 MONTHS));

For the ACCOUNT table we have the regular RECID and XMLRECORD columns and then "sneak in" a column D. Because RECID and D won't be updated they come first. The hidden column D has a default value, so that automatically the current date is inserted. The column is also used to define data ranges with each range covering 3 months.

The next example uses a different approach. An identity column ANYID is introduced and for new rows we would cycle through the specified set of values. This would evenly distribute new rows across all ranges.

CREATE TABLE ACCOUNT 
(
     RECID VARCHAR(10) NOT NULL PRIMARY KEY,
     ANYID SMALLINT NOT NULL 
          GENERATED ALWAYS AS IDENTITY 
          (START WITH 1, INCREMENT BY 1, MINVALUE 1, MAXVALUE 10,
          CYCLE, CACHE 10000, NO ORDER) IMPLICITLY HIDDEN,
     XMLRECORD XML INLINE LENGTH 32000
)
PARTITION BY RANGE(ANYID) (STARTING(1) ENDING (10) EVERY 1);


It is worth pointing out that such an approach should always be tested first before moving it into production.

!!!STOP!!! Birthday Party for 5 Years of Blogging (Your participation needed)

Five years ago, on January 9th 2009, I started this blog. Time to look back and to

by John Hritz, CC-BY-2.0

celebrate. But also time to look forward. And I need your help with both. Please continue reading, 5 minutes are needed.

In late 2008 I was looking for an easy way to share tips&tricks about DB2. Over the holidays I thought about trying out "blogging" and started it in January 2009. And now I can't believe that 5 years passed already. Time to celebrate: Some extra chocolate for me today and a big THANK YOU to you for reading what I write.

As part of the celebration I am looking for some gifts, i.e. your feedback:
Please send me an email to "hloeser" at the domain "de.ibm.com" with a small note about what you like in the blog.

• Did it help you with some specific aspects of DB2, like migration from Oracle, XML processing, taming the beast...?
• Are you reading this blog because grammar my sometimes funny it looks?
• Do you like the articles labeled "fun"?
• Did you read my now "dated" articles on April Fools Days?
• Did you try to solve all the quizzes?
• Did you come to my blog for the series on epilepsy?
• Did you come here by mistake after an Internet search?
• Anything else?

And what do you want to read in the future? Again, please celebrate with me and send a quick email with some feedback. If you want to stay anonymous, please leave a comment.