So, you downloaded the free DB2 Express-C and you have started to play around with it. Maybe you created sample database and started to explore it using DB2 Control Center. If you are a developer, you may find that DB2 Control Center does not quite fit what you think of a a development tool. It is more in tune with what a DBA might want to do. So, you may have taken our advice and downloaded the new IBM Data Studio. After all, it is free so why not! If you have not done this already, go get it from the DB2 Express-C download page. Give yourself a bit of time and have at least 600MB free on your hard drive to hold Data Studio installer as this is not a small download. Don’t say I did not warn you.
Let’s say you downloaded and installed the Data Studio. This is where the first surprise may come in. If you install products like most people i.e. click on Next button until you get to the finish button without reading much on the way, you may be surprised to find that there is no shortcut for Data Studio in your Start/All Programs/IBM DB2 folder. Don’t panic. Your install in all likelyhood has indeed succeded and you do have Data Studio on your machine. So, where is it? Go to Start/All Programs/IBM Software Development Platform and you should see IBM Data Studio Folder and in it a shortcut for launching Data Studio.
At this time you are probably wondering “why in the world would IBM do this; doesn’t IBM DB2 folder make a lot more sense?” Yes it does … if you are using DB2. Notice however that we did not call this product “DB2 Data Studio”. That is because in addition to DB2 for Linux, Unix and Windows, DB2 for z/OS and DB2 for i5/OS, Data Studio also supports IDS (Informix Dynamic Server) databases. In the future, it is also not unreasonable to expect Data Studio to work with other databases say MySQL, Oracle, SQL Server etc. As a matter of fact, in a way it already does. Just take a look at the Rational Data Architect products in the Data Studio portfolio which already supports a number of non-IBM database servers.
This brings me to the second reason why Data Studio is in the IBM Software Development Platform
folder. If you have any of the IBM Rational products installed on your machine you will find them in the same folder. IBM Software Development Platform is IBM platfrom for delivering a set of tools that implement a complete software development lifecycle. Data Studio handles the data lifecycle within the scope of appilcation development. I know this sounds like marketeering but there is actually a lot of meat to this. For example, if you do have any of the Rational software development tools, you will find that Data Studio you just installed, plugs itself right in to these tools (e.g. RAD - Rational Application Developer, RSA - Rational System Architect). As a Java developer you will find this very convenient as both your Java programming and your database developement tasks can be done from a single IDE without having to start multiple copies of Eclipse and minimizing your risk of developing a curpal tunnel syndrome from switching between IDEs all the time (yes, it is my lame attempt at a joke).
OK, so you may now understand why Data Studio it is where it is but none of these points apply to you. You don’t do Java, Lifecycle is the name of your stationary bike that you use to excercise, and you are just happy to be working with DB2 and don’t need another database, thank you very much. No problem. Just create a shortcut on your Windows desktop, or the Quick Launch toolbar and you have a one click access to the Data Studio.
Tags: DB2 · Tools
Just like any other DBMS, DB2 keeps all of your data in tables. The tables are organized in a usual i.e. data is stored in rows with each row having one or more columns. This is true even in the case when the data that is stored as XML. Hopefully it is not a surprise that DB2 Express-C has a “hybrid” storage engine that stores both relational data and XML data in their native formats. We call DB2’s ability to deal with XML data “pureXML”. For XML, this means that XML documents are stored in DB2 as pre-parsed trees. These XML trees are not a replacement for rows and columns. Instead, XML is just another data type for a column and each table can have zero or more columns of XML type.
Since table objects (or just tables) are so fundamental to your ability to store and retrieve data there comes a time when you want to get some information on a particular table in your database. For example, you may want to know how much disk space has been allocated for a particular table, or what state it is in. Should it be reorganized? Has the last load job finished loading data in to it and so on. If you were using MySQL, you may have just looked at the file that represents a table and were able to figure out disk usage that way. There is a better way to do this in DB2 Express-C (and every other DB2 for Linux, Unix and Windows v9.5). Since DB2 speaks SQL, it makes sense to query information about its tables using SQL. DB2 provides not one but 2 ways of getting cornucopia of information about tables via SQL. One way is to use a SYSIBMADM.ADMINTABINFO view. At this point in time you may be tempted to just fire up your DB2 Command Line Processor (CLP) and type in this query:
SELECT * FROM SYSIBMADM.ADMINTABINFO
Go ahead and try this. I bet you are not very impressed with the results. You are getting the right information but it is just too much of a good thing. The query is returning about 29 columns and this is for every table in your database. CLP is not the ideal way to display such query results. The good thing is that this is just a view and we are using SQL. Obviously we can apply a predicate to, for example, only return information for a particular table and specify only the columns we are interested in to make it more manageable. And, we can always use a better tool than CLP to run the query; say DB2 Control Center.
Instead of retrieving all of the columns in the view, you will likely be interested in a subset of columns. DB2 information Center provides a very comprehensive description of each column in this view. I will provide a much shorter description here to save you the hastle of trying to figure out what information applies to DB2 Express-C and what you can ignore.
As you can see, many of the columns are not applicable to the DB2 Express-C environment but they are there to preserve 100% compatibility with other editions of DB2.
At the start of the post I said that there are two SQL based ways to get detailed information on a DB2 table. ADMINTABINFO view is one. The other is a to use SYSPROC.ADMIN_GET_TAB_INFO_V95 table function. This function, like any other table function, can be used in a SELECT statement. It accepts table schema and table name as parameters and returns exactly the same information as the ADMINTABINFO view.
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_INFO_V95('LEON', 'DEPARTMENT'))
I recommend using ADMINTABINFO and to stay away from using ADMIN_GET_TAB_INFO_V95 table function directly. There is absolutely nothing that the ADMINTABINFO can not provide that ADMIN_GET_TAB_INFO_V95 can. And, you probably already noticed “V95″ in the name. This on its own should serve as the warning sign.
Tags: Administration · DB2 · Programming
Given that our American neighbors are celebrating the declaration of their independence today I figured I will post a declaration of independence for this blog. As of today this blog is no longer hosted on WordPress.com. Instead, FreeDB2.com is not hosted on self-managed on an ISP account (on Site5.com). What does this switch mean to those of you who follow this blog? Nothing really. You should not see any changes. The url for the blog will remain http://FreeDB2.com and I’ve migrated all posts and comments from the existing blog to this new blog.
So, why this change? First, I’d like to say that I think WordPress.com is great. With absolutely no money you can have a very capable and customizable blog in no time at all. If you have something to say you can use WordPress.com to get yourself a podium on the web to speak from within minutes without paying a single cent. And since this is the same philosophy as DB2 Express-C i.e. “free” this appeals to me. Roughly over eight thousands new blogs are started on Wordpress.com every day. So, why change? As great as “free” is, sometimes paying money makes sense. WordPress.com is quite flexible and allows one to use variety of templates and even widgets to customize the blog. However, if you want to do a bit more like use custom CSS or to use your own domain name like FreeDB2.com you have to buy inexpensive upgrades. No big deal; I actually did do that to have FreeeDB2.com as the url for the blog. Unfortunately what you can not buy are plug-ins and WordPress has a pretty significant ecosystem of plug-ins that help you do anything from getting a better handle on your blog visitors through Google Analytics to getting greater exposure for your blog with search engines. There are 2,842 plug-ins listed in the WordPress Plug-in directory.
To me, being able to use plug-ins justifies paying extra to host WordPress software on my own ISP account as opposed to a hosted WordPress.com account. We already host DB2onRails.com with an inexpensive shared hosting plan on Site5 so, adding another blog there is actually not costing any extra money since we still have capacity to spare. Not that paying extra $5 per month would make me go broke.
So, in the next weeks and months, I plan to make the most of the newly found independence of this blog. Some of the plug-ins I plan to use will be invisible to those of you that follow the blog. Some will be visible and hopefully, will deliver a better reading experience. Please let me know through your comments how this experiment is working out.
Tags: DB2 · Random thoughts
Before I get too far in to this I’d like to get a couple of things straight:
- I am biased and
- I actually like Oracle XE but I like DB2 Express-C more … see point 1
Now that I got this off my chest, I wanted to do a quick comparison between these two products from the two leaders in the commercial database server market. Both DB2 Express-C and Oracle XE (Express Edition) have much in common. Both are built on the same code that their very popular and at times expensive commercial counterparts are. Both can be downloaded and used for free to build applications, deploy them in production and even distribute as part of your application. Both DB2 Express-C and Oracle XE claim that you can build your application on them and when you “grow up” and need to scale you can upgrade to a paid edition without expensive recoding and migration of your application. So, what’s the diff?
To be net, the biggest difference is that Oracle XE restricts you to storing maximum of 4GB of data. In my not so humble opinion this is an absolute joy killer. How can you use a product that limits how much data you an collect and stops in its tracks if you go over the limit. Say you are an ISV and you bundled Oracle XE in to your application and distributed to your customers. Now imagine your embarrassment and your customers’ “emotion” directed at your support staff when your application just stops working because they stored more than 4GB of data. You may say “yeah Leon, you are full of it. 4GB is a lot.” Bill Gates once said that nobody would ever need more than 64MB or RAM. For the record, I have 3GB of RAM in my laptop and with all due respect for Mr. Gates, I do need it all of it. But I digress. What is 4GB of data? How about just 2 BLOBs with pictures or a couple dozen or so scans of your purchase orders, invoices and other business documents. What about your digital media library. I have 1TB NAS at home and it is starting to fill up with pictures, home movies and iTunes stuff (yes, all legal; I am glad you asked :-)). iTunes library is in XML and DB2 Express-C does XML really well … more on that later. 4GB is half of a decent memory stick. 4GB is nothing in our digital age. DB2 Express-C has no limit on how much data you can store. If you the disk space it will use it. Gigabytes, terabytes maybe even petabytes.
A few less dramatic points. Oracle XE runs on 32-bit Windows and Linux. DB2 Express-C does both 32 and 64-bit Windows and Linux but it also runs on Solaris X86. And for those with System p or System i servers, it is also a choice for running on Linux partitions on these servers. These POWER processors can give you a lot of oomph. So, if you have an System i (AS/400 for old timers) you can install DB2 Express-C on a Linux partition and get blazing fast replication of your AS/400 data in to DB2 Express-C. And did I mention that DB2 Express-C is available in 64-bit versions while Oracle XE is 32-bit only?
Speaking of hardware resources. Both Oracle XE and DB2 Express-C can be installed on any size machine be that a laptop with a single processor and 1GB of memory (3GB in my case) or a good size server with 4 multi-core CPUs and 16GB of memory. That’s nice! Both products will use up to a set amount of hardware resources though. Oracle XE will use a maximum of 1 processor and will not allocate more than 1GB of memory. DB2 Express-C will go for double that. It will schedule its work on 2 processor cores and will allocate maximum of 2GB of memory. However, if you were to purchase optional subscription you can double resource allocations to 4 processor cores and 4GB of memory. And remember, there is never any restriction on the amount of data you can store. An astute reader may be wondering about the usefulness of the 64-bit version of DB2 Express-C in a 2GB or even 4GB environment. If you are wondering, read my blog post on the subject. So, DB2 Express-C can take advantage of 2-4 times the hardware resource when compared to Oracle XE. But what does this really mean? Well, by itself it means nothing. However, when you consider the fact that for database performance having extra memory is probably the most important aspect you understand that you have a potential of much higher performance with DB2 Express-C. What is more important is not only DB Express-C can take advantage of more memory when available it also needs much less memory to run. It turns out that Oracle XE is not quite the slimmed down version it claims to be. Here Windows task manager shows that while Oracle XE instance is up but is idle (not processing any queries) the commit charge is 1581MB
Now we stop Oracle XE instance
If you do a quick math you will see that just starting Oracle XE instance consumed about 600MB.
Let’s get back to the code base. Both products use the same code base as their priced editions. However, Oracle XE is still at 10g R2 level despite the fact that 11g shipped almost a year ago. As a matter of fact, Oracle XE has not to my knowledge had any updates at all, not even for critical security flaws. DB2 Express-C on the other hand is constantly updated to the latest level of the DB2 code base. At this point in time it is v9.5 while initial release was v8.2. The free version of DB2 Express-C does not get Fixpacks but it is updated as the new releases of DB2 come out. However, DB2 Express-C does provide an option of low cost support delivered as a yearly subscription. If you were to purchase this subscription, you would get 24*7 support delivered by IBM engineers world wide. You also get double the resource allowance (4 processor cores and 4GB of memory), SQL based data replication and high availability clustering and off-site disaster recovery (HADR). Nothing like that is available with Oracle XE. If you are looking for data replication (Oracle Streams) you will need to move to Enterprise Edition ($47.5K per processor). Same is true for high availability (DataGuard). Even Oracle backup and recovery (RMAN) requires a move to Oracle Standard Edition One.
I saved the best for last. One of the really cool features in DB2 Express-C (and all other DB2 editions) is pureXML. Basically, DB2 Express-C database engine has hybrid data store. On the one hand, it is a very efficient high performing relational storage and query engine. On the other hand, it is also a storage engine that knows how to store parsed indexed XML trees in their native format and how to query data out of these trees using XQuery language. So, if you want to store your iTunes library in DB2 or maybe a large collection of insurance policy documents using industry standard ACCORD schema you can do so efficiently with full fidelity and be able to query these documents with the speed that you would expect from relational databases. It is really very fast.
As I said at the start of this post, I am naturally biased towards DB2 Express-C. However, I like to think that my bias is not just blind affection for my employer or the product I’ve been associated with for the last 15 years. I do feel that DB2 Express-C represents much better value than Oracel XE for anyone but a hobyist who is already well versed in Oracle. I just do not see how majority of peopel deploying real applcaitions can live with restriction on the amount of data their database can manage and such a low one to boot. Also, given memory consumption of Oracle XE and the very low 1GB memory limit, I am sceptical that one can expect perforamcne levels required for real world applcaitions. Last but not least, our IT peopel made me uninstall Oracle XE because it failed security vulnerability scans … it is an unpatched 10g R2 version with dozens of well document vulnerabilities that I simply have no way of fixing. So, while I like Oracle XE for tinkering because it is much easier than other Oracle database editions, I don’t see it being usefull for any kind of real life deployment.
Tags: DB2 · Oracle
“IBM may open source DB2″ is the headline headline in today’s article by Tom Espiner on ZDNet.co.uk. The article is based on the interview with Chris Livesey, IBM UK director of information management. I posted a comment on the article but I figured I’d provide a more complete explanation here in the blog.
Before I get in to the details, I wanted to just reemphasize that what you are reading here is NOT an official IBM position but an opinion that is mine and mine alone.
If you follow this blog than you already know that that IBM provides a product called DB2 Express-C which follows the now typical open source business model i.e. free license to use with available optional for fee service and support. It is important to note that “open source business model” is not the same thing as “open source” product. There are many definitions of “Open Source” and I can easily start a holly war on the subject here but I don’t intend to; I am sorry if my definition of the “Open Source” does not comply with naming police requirements. In general, for DB2 to be considered open source we would have to make the source code available for download and inspection by anyone who wishes to do so and is able and willing to comply with the legal requirements of one of the commonly used open source licenses (a great subject of debate by many open source purists). It would typically also mean that IBM would be willing to receive contributions from the community in the form of code patches or even entire new modules and would not object to people creating completely new diverging branches of code. As I said before, I am playing fast and loose with the definition of the open source in an effort to explain what DB2 does or does not do.
Just to recap, DB2 is not available as open source. I can’t comment with any degree of certainty on the future but my crystal ball is not showing open source plans for DB2. Over the years, IBM has shown great commitment to the open source movement by donating its technologies to the open source communities. In the database space, for example, IBM contributed Derby to Apache. So, we do open source big time. However, we do it judiciously and specifically when we have a strong belief that open source would provide great value to the community and the industry. At the time of release of DB2 Express-C we had extensive consultations with key customers and industry analysts. The feedback we were getting (and continuing to get) was that “open source” would not be beneficial. The number of people that would be interested in seeing DB2 source code can probably counted on 2 hands and half of those are likely to be our competitors. This is not a scientifically derived measurement but a general feeling. The number of people who would actually be able to contribute code is a small fraction of that at best. On the other hand we have a number of corporate customers who are very clear that they have great difficulties acquiring open source products. Corporate procurement departments will disallow open source product acquisition because of intellectual property concerns. Even when open source is grudgingly allowed, customers are demanding “Indemnification”. Open source vendors such as MySQL had to respond by offering indemnification at (hefty?) extra charge. In the past suing MySQL was not a profitable proposition. It remains to be seen if now that MySQL is part of the SUN Microsystems, a company with much deeper pockets, this will still be the case.
What we did get a lot of input on is a desire to have a free DB2 product. Many of our customers as well as students, hobbyist and solution providers really like the ease of acquisition of the open source products i.e. no purchase orders, approvals, budgets etc. We on the other hand are very proud of our technology and want to get it in to as many hands as possible. We want everybody who ever touched XML to be able to experience the benefits of using DB2 pureXML and we don’t want barriers such as lack of approved budget to stand in the way. So, you can download DB2 Express-C, full function real DB2 and we offer for free. You can build the latest wizbang (Web 1.0 term for term for “cool”) Web 2.0 and Enterprise 2.0 applications with XML, web services, Atom, REST, SOA, Ruby on Rails, PHP, .NET, Java (and every other acronym you can think of) and deploy it for free. And if you need 24×7 support and backing from IBM you can get low cost (same price as MySQL Enterprise Gold) optional subscription. If you are in the business of building and selling software you can even bundle DB2 Express-C as part of your solution absolutely free. Our ISVs seem to like that a lot. They can now distribute an industrial strength commercial database as part of their offering without the legal concerns yet have the key benefit of open source. What they are not giving up on is the great technology and support that IBM can deliver 24 hours a day seven days a week in any corner of the globe in the language they speak. And this support is delivered by IBM and not subcontracted to a third party. It is delivered by exactly the same team that provides support for all other versions of DB2 and has complete access in to the engineers in our labs.
To sum this up “IBM may open source DB2″ is a very catchy title for an article but it really does not represent our plans for DB2. IBM has contributed to the open source before and will continue to do so I am sure. But in my opinion there is no benefit in contributing DB2 to the open source. I believe that our partners and customers, and industry overall, is much better served by the current DB2 Express-C offering as it delivers the key benefits of open source without the downside that is typically associated with the open source.
Tags: DB2 · Open Source
