This morning I answered an interesting DB2 XQuery/SQL question about parameter passing. The core issue was related to the different data models and bridging them when data is passed from the SQL world to XQuery or back. After checking this blog's archive I found that I hadn't written about this common issue before. So let's start by showing you what worked and what not:
create table tok(id int, doc xml);
insert into tok values(1,'<a><b>IBM</b></a>');
insert into tok values(2,'<a><b>BLA</b></a>');
First I create table and insert two simple documents. Now we are ready to query the table:
select * from tok
where xmlexists('$DOC/a[b = ("IBM","whatever")]');
ID DOC ----------- -------------------------------
1 <a><b>IBM</b></a>
1 record(s) selected.
select * from tok where xmlexists('$DOC/a[b = $para]' passing cast('IBM,whatever' as varchar(200)) as "para");
ID DOC ----------- -------------------------------
0 record(s) selected.
In the first query above we directly evalute a comparison against a list of string values (IBM, BLA). It matches the first XML document. The second query passes the same comma-separated value in, but none of the documents is found. What happened?
In XQuery the comparisons above are called "general comparison" (there is also value comparison and node comparison). The general comparison returns true above if some value in the first sequence is equal to some value in the second sequence. The important piece of information is sequence. In the first query, the '("IBM", "BLA")' constructs a sequence with two string values as items . The "(" and ")" are used as sequence constructor. In the second query, a single string value is passed in. There are probably different ways of composing the string, but it remains a string.
What can be done? For the example above XQuery and XPath have the function fn:tokenize(). That function splits up a string into a sequence of substrings. Thus, we would have a sequence again that could be used in the comparison. Let's see whether it works:
select * from tok where xmlexists('$DOC/a[b = fn:tokenize($para,",")]' passing cast('IBM,whatever' as varchar(200)) as "para");
ID DOC ----------- -------------------------------
1 <a><b>IBM</b></a>
1 record(s) selected.
As it can be seen, the tokenize helps to split up the parameter into a sequence and the query result is as expected again.
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.
Monday, March 25, 2013
Friday, March 22, 2013
Photo campaign for epilepsy awareness and tolerance
I just returned from two business trips and looked over the mail (paper-based). Included was one epilepsy-related magazine that we receive since we co-founded a local support group for parents of children with epilepsy. A story in the current edition of that magazine is about a photo campain named DOWNsideUP.
The photographer, Xavier Escalere, lost his then 25 year old girlfriend when she drowned because of an epileptic seizure. He started a campaign for more awareness of epilepsy and to create more tolerance. Many people still need to hide that they have epilepsy.
The photos on DOWNsideUP are shot in many continents/countries and provide surprising perspectives.
The photographer, Xavier Escalere, lost his then 25 year old girlfriend when she drowned because of an epileptic seizure. He started a campaign for more awareness of epilepsy and to create more tolerance. Many people still need to hide that they have epilepsy.
The photos on DOWNsideUP are shot in many continents/countries and provide surprising perspectives.
Monday, March 18, 2013
Upcoming DB2 Bootcamps
"Information Management Bootcamps are technical workshops in a
classroom setting. They are held throughout the year in different
regions worldwide and there is no charge to participate. These bootcamps
contain detailed lectures and labs for deep-dive learning of DB2 10."
Right now three different bootcamps are offered regularly for DB2. Dates for upcoming bootcamps with registration links can be found on the respective webpage which also shows the agenda, prerequisites, and much more:
The DB2 for Linux, UNIX, and Windows Bootcamp is the foundation. It usually runs 4 days and gives an overview and introduction to the many features of DB2.
The DB2 Performance Tuning and Monitoring Clinic takes a look at performance best practices, monitoring APIs and related topics.
The DB2 Advanced pureScale Bootcamp gives a deep dive into the pureScale technology, shows best practices for setup, administration, and diagnostics and allows participants to get "dirty hands" with small DB2 clusters.
Take a look, register, and improve your DB2 skills. BTW: At the end of a bootcamp, usually free certifications are offered.
Right now three different bootcamps are offered regularly for DB2. Dates for upcoming bootcamps with registration links can be found on the respective webpage which also shows the agenda, prerequisites, and much more:
The DB2 for Linux, UNIX, and Windows Bootcamp is the foundation. It usually runs 4 days and gives an overview and introduction to the many features of DB2.
The DB2 Performance Tuning and Monitoring Clinic takes a look at performance best practices, monitoring APIs and related topics.
The DB2 Advanced pureScale Bootcamp gives a deep dive into the pureScale technology, shows best practices for setup, administration, and diagnostics and allows participants to get "dirty hands" with small DB2 clusters.
Take a look, register, and improve your DB2 skills. BTW: At the end of a bootcamp, usually free certifications are offered.
Tuesday, March 12, 2013
SQL Quiz: How to get this XML output?
Here is a small SQL quiz for you. I start with the following statements against a DB2 database:
drop table contacts;
create table contacts(
id int unique not null,
fname varchar(40),
lname varchar(40),
cc char(2));
create index cc_idx on contacts(cc);
insert into contacts values(1,'Klaus','Störtebeker','DE'),(2,'Bugs','Bunny','US'),(3,'Robin','Hood','GB'),(4,'Mickey','Mouse','US');
Next, I execute a query and the output is as follows:
<data><contact><first>Klaus</first><last>Störtebeker</last><country>DE</country></contact><contact><first>Robin</first><last>Hood</last><country>GB</country></contact></data>
And the question for you: What is an efficient way to produce the output above? Post your proposals as comments...
drop table contacts;
create table contacts(
id int unique not null,
fname varchar(40),
lname varchar(40),
cc char(2));
create index cc_idx on contacts(cc);
insert into contacts values(1,'Klaus','Störtebeker','DE'),(2,'Bugs','Bunny','US'),(3,'Robin','Hood','GB'),(4,'Mickey','Mouse','US');
Next, I execute a query and the output is as follows:
<data><contact><first>Klaus</first><last>Störtebeker</last><country>DE</country></contact><contact><first>Robin</first><last>Hood</last><country>GB</country></contact></data>
And the question for you: What is an efficient way to produce the output above? Post your proposals as comments...
pureXML: The two most beautiful words in the English language
From 2008, but still current and funny: "Dragons are green, my friend"
The Pitch Meeting, part 1
The Pitch Meeting, part 2
The Pitch Meeting, part 3
The Pitch Meeting, part 1
The Pitch Meeting, part 2
The Pitch Meeting, part 3
Wednesday, March 6, 2013
Video: Live migration of OpenBravo from Oracle to DB2
Interested in DB2's SQL compatibility and migration from Oracle to DB2? The following video is about a year old now and shows Serge Rielau moving the open source ERP system OpenBravo from Oracle to DB2. Since the time the video was created, some things have changed:
- DB2 is now at version 10.1 and has more compatibility features.
- The tools MEET and IBM Data Movement Tool have been mostly replaced by the Database Conversion Workbench.
Tuesday, March 5, 2013
New page with DB2 pureScale-related acronyms
I started a new permanent page with DB2 pureScale-related acronyms. This is some work in progress as I plan to add links and some more terms. However, as I was searching for such a list, I wanted to point you to it as early as possible. Let me know which other terms to add.
Subscribe to:
Posts (Atom)