Thursday, June 28, 2012

Want to learn about DB2 security? New best practices paper

A new DB2 Best Practices paper has been published on developerWorks: DB2 best practices: A practical guide to restrictive databases. Did you know that instead of CREATE DATABASE foo you could also say CREATE DATABASE foo RESTRICTIVE? But what are the differences? The restrictive database has only very few privileges granted to the group PUBLIC and thereby starts off with increased security.

In the new paper the authors compare how the non-restrictive and restrictive databases differ in terms of privileges with a focus on common use cases and administrative groups. It helps to understand how to make use of restrictive databases. While in regular databases a typical problem to secure the database, in a restrictive database it means granting the needed privileges so users can "work".

Monday, June 25, 2012

How to communicate within your family (kids, significant other, DB2)...

Healthy families talk with each other. But (usually) I speak differently with my wife than with my kids. There is no manual on how to address everyone individually or how to conduct get heard by all of them. Fortunately, there is a guide for the DB2 family of products. And it has been updated recently.

When I give talks about the DB2 family I always mention that we strive to coordinate new features, learn from each other (yes, even mistakes), and try to speak the same language. Time travel queries (temporal data), row and column access control, the XML data type, binary XML, etc. are some of the more visible recent coordinated endeavors. Now the so-called SQL Reference for Cross-Platform Development has been updated to reflect new DB2 features. That page is very useful for some other reasons:
  • It offers links to older versions of the SQL Reference for Cross-Platform Development.
  • It has links to a lot of documentation for DB2 z/OS, DB2 for Linux, UNIX, and Windows, and for DB2 i.
  • All versions combined, it has 10 years of best practices of application development.
That's all for now, I have to go prepare the dinner table. Some family discussion is about to start...

Wednesday, June 20, 2012

NoSQL, SPARQL, RDF, Graph Store, and more buzzwords - all new in DB2 10

One of the smaller headlines during the DB2 10 announcement was the "NoSQL Graph Store". In the "What is new" section of the DB2 Information Center it is listed as "RDF application development support has been added". However, if you work in/with the Semantic Web, it is an important addition as it provides some robust and performant infrastructure. In the following, I will point you to some resources around SPARQL and RDF stores in DB2.


As the data can be represented in XML, a question about support for graph stores is actually a question that pops up from time to time in DB2 pureXML discussions. For those searching for a combination of the buzzwords and DB2, the search engines should now point you to the right pages... ;-)

Friday, June 15, 2012

It is "venerdi" in "it" - a Friday idea

It is Friday and close to afternoon. What to do next? I type "Friday" into the search field of the DB2 Information Center and in the result list I notice the function DAYNAME. In the description I notice that I can provide a locale or language code as parameter.

Let's give it a try, it is Friday afternoon:
db2 => values(dayname(current date))

1                                                                                                  
----------------------------------------------------------------------------------------------------
Friday                                                                                             

  1 record(s) selected.
 
db2 => values(dayname(current date, 'de'))

1                                                                                                  
----------------------------------------------------------------------------------------------------
Freitag                                                                                            

  1 record(s) selected.

db2 => values(dayname(current date,'fr'))

1                                                                                                  
----------------------------------------------------------------------------------------------------
vendredi                                                                                           

  1 record(s) selected.

db2 => values(dayname(current date,'es'))

1                                                                                                  
----------------------------------------------------------------------------------------------------
viernes                                                                                            

  1 record(s) selected.
 
db2 => values(dayname(current date,'in'))

1                                                                                                  
----------------------------------------------------------------------------------------------------
Jumat                                                                                              

  1 record(s) selected.
 
db2 => values(dayname(current date,'it'))

1                                                                                                  
----------------------------------------------------------------------------------------------------
venerdì                                                                                            

  1 record(s) selected.

If your language or country is not listed, try it yourself. Have a nice weekend.

Wednesday, June 13, 2012

DB2 pureXML and bufferpools - revisited

Some years ago I wrote that XML data is buffered for performance reason and looked at the XDA object. Now I found out that I didn't answer the "buffered or not" question entirely. The question which bufferpools are utilised depending on inlined or regular XML storage remains. So let me answer it.

CREATE TABLE myX (id INT, x1 XML INLINE LENGTH 500, x2 XML) IN myT1 LONG IN myT2;

Considering the above CREATE TABLE statement, data for table myX would be stored in two tablespaces, myT1 and myT2, and use the associated bufferpool(s). If the internal representation of the XML data for column x1 would be up to 500 bytes, the row data consisting of an integer value for id, the XML data for x1, and a descriptor pointing to the XML data of x2 would be stored in the DAT object and hence in tablespace myT1. When accessed, the data would go to the bufferpool associated with myT1. If the XML data for x1 would be larger than the 500 bytes, the row would hold an integer and two descriptors (one each for x1 and x2).
The descriptors would point to entries in data pages in the XDA object which is stored in tablespace myT2. All XML data for x2 and the data too long to be inlined for x1 would be stored in myT2. When accessed, the bufferpool associated with tablespace myT2 would hold the pages.

In short again: XML data is buffered and it goes through the bufferpool associated with the tablespace it is stored in. This is the same as with regular relational data or index pages.

Learn about DB2 10 in free bootcamps

As you might know, part of my job is to teach so-called bootcamps, free classes offered to business partners and customers. With DB2 10 fresh out the door it also means updated bootcamp content and slightly more bootcamps offered worldwide. At the end of each bootcamp we typically offer free product certification. Do you wonder whether a bootcamp is offered near you?

Here are some resources:
Will I see you soon in one of the bootcamps...?