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".
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.
Thursday, June 28, 2012
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:
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.
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... ;-)
- A tutorial on RDF application development with DB2 is on developerWorks.
- The SPARQL query language as defined by the W3C.
- Wikipedia has an overview of the Resource Description Framework (RDF) with many links.
- Most of the RDF/SPARQL-related content in the DB2 Information Center is in the application development section. "RDF application development for DB2 data servers" is the starting point.
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:
1
----------------------------------------------------------------------------------------------------
venerdì
1 record(s) selected.
If your language or country is not listed, try it yourself. Have a nice weekend.
Let's give it a try, it is Friday afternoon:
db2 => values(dayname(current date))
1
----------------------------------------------------------------------------------------------------
Friday
1 record(s) selected.
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.
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
----------------------------------------------------------------------------------------------------
Jumat
1 record(s) selected.
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.
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.
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:
Here are some resources:
- An overview of all bootcamp types, covering DB2, InfoSphere products, Informix, and more: http://www.ibm.com/developerworks/data/bootcamps
- Agenda, dates, and registration links for DB2 10 bootcamps can be found here: http://www.ibm.com/developerworks/wikis/display/im/DB2+10+for+Linux%2C+UNIX+and+Windows+Bootcamp
- If you know about Oracle database technology and want to leverage your existing skills, then you should attend the DB2 10 Workshop for Oracle Professionals. Dates, agenda, and registration links are here: http://www.ibm.com/developerworks/wikis/display/im/DB2+10+Workshop+for+Oracle+Professionals
Subscribe to:
Posts (Atom)