Posts in Category: DB2 pureXML

Search for the XML Superstar

IDUG (the International DB2 Users Group) is sponsoring a worldwide contest initiative called The XML challenge – Search for the xml superstar. This contest aims to recognize developers (students or professionals) that create XML solutions, in one of the following categories: Video, Gadget, Query, PortableApp and XML Contest.

They are offering thousands of dollars in prizes, including Wiis, Zunes, iPods, Conference passes, Notebooks, GPS, etc…

If you live in US, you can submit your Video and Gadget entries until December 16th and 17th, respectively. The XML programming contest has also started and submissions will be accepted till January 31st.

For other countries, keep checking the website for updates on your local contest.

Harvard and MIT

Originally uploaded by Vitor Rodrigues.

Last month I completed the visit of the 4 major US universities that were on my To Visit list. After Sanford and Berkeley during my internship in California, visited Boston this October for a conference and had the opportunity to visit the Harvard and MIT campuses.

While Harvard is a beautiful place to visit with all the fancy coffee shops and restaurants around Harvard Square and a very nice bookstore, that makes you feel you are still in the XVIII century.

As for MIT, not much to see, besides lots of geeks (yes, they are easily spotable) and lots lots of bicycles in the parking lots. Just a few funny buildings like the ones in the picture.

As for Boston city, although at first it looks just like a small size NYC, after a few walks around the city you can feel its own vibe and style. For food, you must try the Italian restaurantss in the North End. For guided tours, the list of attractions listed on the trolleys is just a joke, i.e., “Access to MIT campus and Harvard Square” means: we let you out close to a subway station where you can catch the subway to there :-)

DB2 on Rails update

I’m back to fiddling around with my Ruby on Rails experiments(1)(2). I was able to create a very useful 2-way mapping between Ruby objects and xml data stored in DB2 pureXML. Basically, trying to replicate some of ActiveRecord’s functionality but for XML data. I still find it odd, though, that both ROXML and xml-mapping haven’t had much activity as of late. I’m wondering if there is any new OXM library around that I don’t know of.

Also on the same topic:

  • the main DB2 on Rails website is up and running again, with a revamped design and now using wordpress instead of typo.
  • a new version of the ibm_db driver was also released, containing several bugfixes. Update it through gems (gem update ibm_db) or from here: 

DB2 on Mac

Last week, after my presentation at University of Minho about the DB2 on Campus and DB2 Student Ambassador programs and the pureXML features in DB2, one student came to me and asked me if DB2 was available for Mac. My answer was a ‘no’, but things will change pretty soon. 

My ‘office neighbor’ Antonio Cangiano just made public IBM’s intention of releasing a DB2 Express-C port to Intel Mac. This is one more big step from DB2 Express-C towards the community, after very open licensing conditions, Ruby on Rails driver and adapter, soon-to-come Python and Django driver and adapter, DB2 Express-C orum and DB2 Express-C blog, etc….


DB2 Student Ambassador

After reading Alcides Fonseca’s post regarding is opportunity to become an MSP and asking whether other companies have similar programs, I would like to introduce two programs related with DB2 that are targeted for university students: DB2 On Campus and DB2 Student Ambassador.

DB2 on Campus: The DB2 on Campus Program is a project designed to promote the use of DB2 in universities. The logistics and delivery of the program is performed between the DB2 Express-C team, and the local IBM Academic Initiative representative. The program consists of the following items and activities:

  1. A speech titled “The role of DB2 Express-C in the Information on Demand World”. This speech explains DB2 9 technology, with an emphasis on pureXML™, and encourages students to use DB2 Express-C, create university DB2 user groups, participate in the DB2 forums, and write documents about DB2. All of these activities can help them to advance their future careers.
  2. Introductory DB2 courses to obtain DBA and developer skills.
  3. Free DB2 books donated to the university library
  4. Free DB2 certification exams offered to the students

I will be at University of Minho on September 19th doing the speech for the DB2 on Campus program. There was not enough time to prepare the full-day event, but if you or someone at your university is interested in having this full-day event happening at your university, please feel free to email me and I’ll put you in contact with the program’s manager in order to schedule the event (my email is vrodrig at

There is a DB2 On Campus Facebook group. Follow these instructions to join. 

Subscribe to the DB2 Express-C blog to keep updated on news regarding this free to develop, deploy and distribute database(1). 

DB2 Student Ambassador: This is a program for individual students that want to promote the use of DB2 at their education institutions. Though the program is voluntary, it gives students a chance to learn more about DB2, develop presentation skills and marketing skills. All of these skills will be valuable in their careers. At the end of the program, the student will receive a letter of recommendation from IBM including all the details of the activities performed. The more activities performed, the better the letter of recommendation! We hope this will help the student get a job more easily in the future.

I consider this to be an excellent opportunity to get involved and interact with developers from a huge project like DB2, distributed across Toronto and Silicon Valley laboratories.

More information  about this program can be found here:


Although I’m only familiar with the DB2 programs, for the ones interested, there is also an IBM Academic Initiative program, focused on Open Standars, Open Source and IBM resources for the academic world.

The opportunities are out there, so just grab the one you like and boost your career :-)

(1) Yes, it is the fully loaded version available for free, with one limitation: it can only use up to 4GB of RAM :-) 


Recent readings

How to get an access plan in DB2 using db2exfmt

The most common way used to generate and retrieve access plans in DB2 is by using DB2 Control Center. CC provides a graphical representation of a query’s access plan, and it also includes an Index Advisor, that you can use when you are not sure about which indexes to create and to use.

However, not always you have access to a graphical environment (needed to run DB2 Control Center). For the command line, there are two utilities that you can use. DB2 EXPLAIN command and the db2exfmt utility. Although the first one is more complete, I find the second one to be easier to use. So, in order to get an access plan for your query using db2exfmt, you just need to do:

  • db2 -tvf ~/sqllib/misc/EXPLAIN.DDL (create the explain tables where all the explain data will be stored)
  • db2 set current explain mode explain (this will put DB2 in explain mode
    and all the subsequent queries won’t be run, but explain data will be
  • run your workload (e.g., db2 -tvf query.sql)
  • db2exfmt -d dbname -1 -o output.txt (formats the information on the explain tables)

 Detailed information about the access plan for your query will be in the file output.txt. By using this information, you can see which indexes are being used or not as also other performance considerations about your query.

PS: don’t forget to run "db2 set current explain mode no" when you are done with your access plans, so the queries will be executed. 

Old Year, New Year

Originally uploaded by Vitor Rodrigues.

For the first time in many months, I really gave some rest to my laptop during my vacations. Last week was spent mostly with family, girlfriend and friends. So, only now I got to do the review of the year 2006:

  •  started the year looking for a regular job to start after the end of my internship at IBM in Silicon Valley (my internship was going until August, but Visa applications need to be filed by April, so you need to have everything set by then).
  • after a few job interviews, got job offers from both IBM and eBay (received both within a 20 minute interval :-)).
  • decided to accept IBM’s offer. Although eBay’s offer was economically better, I really enjoy the work environment at IBM, I have most of my friends there, and more important than that, I was proposed new job responsabilities that made me prefer this position.
  • presented at some internal conferences about using a Wiki environment in a big scale software testing project.
  • presented at NPUC about using Wikis to give power to team members in the creation of an information repository for cross-team utilization.
  • hired as a regular employee in March, 5 months before the end of my internship.
  • published a IBM white paper on XML Indexing in DB2 9 pureXML.
  • decided to accept an opportunity for a 1-year international assignment at IBM Toronto Laboratory in Canada, from August 2006 to August 2007.
  • after 18 months in California, I finally enjoyed some vacations in Portugal, visiting some places I hade never been before, like Tomar, Sintra, Evora and Vila Vicosa.
  • move to Canada.
  • co-lectured a workshop at CASCON 2006.
  • visited New York for the first time.
  • published a techincal paper in the IBM developerworks website.
  • finished another soon to be published white paper also related with DB2 pureXML.
  • contributed to the DB2 pureXML demo.
  • came home for Christmas and New Year.

Lots of stuff happening, mostly at work. I have the feelling that I could have done more, but I’m still happy with the results. I did more/better than what I was expecting one year ago.

In the personal life, besides the long awaited vacations in Portugal, all the other events where caused by work or work related (look for job, move to canada, trip to NY).

 For 2007:


  • professionally: improve presentation, communication, organization skills.  Find time to invest in side/innovative projects. Read more.
  • personally: more local exploring. Sometimes there are lots of interesting stuff in the place we live that we ignore and only later we realize how interesting that place was. Start to read. Save more money (hint: no more gadgets :-)).


DB21034E when creating stored procedure in DB2

While I was trying to create a stored procedure in my DB2 Express-C windows installation, I got the following error message:

DB21034E The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0219N The required Explain table "SCHEMA.EXPLAIN_INSTANCE" does not exist.

At first, this error message didn’t make much sense. Why would DB2 need to use the explain tables if I’m not running this command in the explain mode? Even doing a db2 set current explain mode no didn’t seem to help. But, creating the explain tables would solve the problem (you can create the explain tables by hand by runing db2 -tvf ~/SQLLIB/MISC/EXPLAIN.DDL). If creating the explain tables would solve the problem, it was because somehow, DB2 was running in the explain mode when creating the stored procedures.

Investigating the DB2 registry variables (db2set -all) I noticed the existence of the variable:

 [i] DB2_SQLROUTINE_PREPOPTS=explain yes

A search for this term on DB2 documentation and the answer was found. The variable DB2_SQLROUTINE_PREPOPTS is used to specify the precompile and binding options for store procedures. After knowing that was the problem, the fix is easy. Just update the registry variable running db2set DB2_SQLROUTINE_PREPOPTS="explain no".

Note: Since this is an instance level variable, the changes will only make effect after you restart your DB2 instance. Just run db2stop and db2start and you’re good to go!