In the past getting one’s hands a large scale enterprise relational database system such as DB2 or Teradata was a tremendous feat for any developer/implementer looking to boost their skills outside of MS Access.  I am excited for the future now that it seems that all of the major database wielding organizations have jumped on the bandwagon to release “Express” editions of these once coveted and hard to gain access systems.  As of late IBM has added itself to the line-up of companies that include Oracle, Microsoft, and even Teradata, by providing their “Express” edition offering of DB2.

Why They Release Express Editions?

The why should really come as no surprise to anyone ever involved in an open source project or even end-user of open source software.  The goal is proliferation.  The more developers that these database companies can provide their database offering to the more it has the chance of evolving in more ways than the company could imagine.  It also gives way to a whole new developer base that actually learn how to use the database, integrate it, and potentially share information about it via books, forums, blogs, etc.

Are the Database Companies Loosing by Providing an Express Edition?

Let’s face it, companies are in existence because they are there to make money, not to lose it.   Each one of these database companies has a strong strategy in place to ensure that the Express offering does not eat into their Full-Version sales of the database tool.  By providing an express edition, these companies gain so much more than just immediate monetary return on investment.  Through discussion groups, social media, etc. the database gain notoriety and great PR which is not directly quantifiable but certainly impacts the organization in a positive way.

Who are They Competing Against?

Free and Open Source Software databases are absolutely nothing new.  If I were to put a finger on it I believe all large database organizations such as an IBM or Oracle are actually competing (in the case of Oracle, were competing) against MySQL and the amazing community that they have established over the last decade.  On the compact database side, the clear competition to beat there is SQLite which is the main database tightly tucked inside almost every dynamic data driven iPhone and iPad application on the iTunes App Store.

These open source software databases have become more that products but they are now perspectives from which the largest enterprise database organizations are now try to carve their vantage.  Clearly some are doing better than others but in the long run I think the competition is amongst each database organization itself. They are competition against themselves in order to define releases, to define how much scope of the full-version database to place into the expression version, and the like.

Where is the Benefit for End-Users?

From a system integrator perspective, having the ability to have a once shielded multi-million dollar database system now fit nicely on a my laptop or in a VM Image running on my laptop, is a tremendous advantage.  One can now testing installation scenarios without requiring database server time or a company investing thousands of dollars and tons of hours setting up development environments on large scale physical servers.  A developer working as a Programmer Level 2 in their basement cube can now explore the internals of Teradata or DB2 and escalate their position within their organization by simply taking the extra time to learn a new database skillset on their own time in the comfort of their own surroundings.

There are a myriad of benefits from leveraging the express versions of the enterprise databases. I implore everyone to take advantage whenever they can.

Of Course there are Limitations

Circling back around to the fact that database organizations are there to make money, don’t be naive that just because these database expression editions are free that they can do everything that the full-version can.  If this were the case no organization would ever buy the full-version, right?  So, each expression edition you find will come with some level of limitation.  The most consistent limitation these express editions have is on the size which the database can grow.  Most limit the database to under 3 GigaBytes of disk space.  This clearly prevents the express edition for use in large data warehousing systems holding hundreds of Gigs worth of data.  Some also limit the number of processors and some limit the number of functions available when compared to the full-version.  I believe this is by design not only to prevent organizations from trying to leverage the express edition for large scale deployments but also to keep the footprint of the database small enough to fit on a laptop or small VM Image.  Either way, when looking at an express edition for testing or your next application’s back-end data store, keep in mind to assess the limitations of the express version of the database in order to mitigate any conflicts you may encounter during development.

Where are These Expression Versions?

• TeraData Express:
  • http://www.teradata.com/teradata-express/
  • http://www.teradata.com/teradata-express-13-0-windows/
• IBM DB2
  • http://www-01.ibm.com/software/data/db2/express/about.html
  • http://www.ibm.com/developerworks/wikis/display/DB2/DB2+on+Campus+videos
• Oracle XE
  • http://www.oracle.com/technetwork/database/express-edition/11gxe-beta-download-302519.html
• Microsoft SQL Server Express
  • http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

The Oracle 11g db seems to default to expiring a user’s password within a fairly short window.  Recently I was testing an old OBI 11g VM I had put together and when the Weblogic services didn’t start up properly the investigation and troubleshooting phase began.  Since the Weblogic and OBI 11g components rely on the two schemas for MDS and BIPlatform if a connection cannot be made to these schemas for any reason unexpected results will occur for your OBI 11g implementation. Ultimately the resolve was to update the user account with an unlimited password lifetime grant similar to the PL/SQL code below.  This code below simply sets the default profile with which a user/schema may be associated.

ALTER PROFILE DEFAULT LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED;

References:

http://www.odi.ch/weblog/posting.php?posting=520

I was working on quick project and kept bumping an a “failure to initiate auto-extend…” message that was rather irritating.  I typically use SQLDeveloper over Toad and there is not immediate built-in functionality to manage tablespaces in that tool.  A quick Google search gave me some results and I figured I would quickly post the two step extension process here for later reference.

Step 1

Run the PL/SQL code to determine the current size of the tablespace in question. Notice how memory is divided by 1024 to get at the canonical data size.  The tablespace I was using for my project was the SYSTEM tablespace and it is referenced within the code in this post.  If you are using the USERS or a custom tablespace simple substitute your tablespace where you see SYSTEM.

select
file_name,
autoextensible,
(bytes/1024)/1024 usedmb,
(maxbytes/1024)/1024 maxmb,
round((((bytes/1024)/1024)
/ ((maxbytes/1024)/1024)) * 100) used_pct
from dba_data_files
where tablespace_name = 'SYSTEM';

Step 2

As we determined in the previous step, we now know how much of the tablespace is being used and what the current size and max sizes are.  To extend the space run the following query based on the information provided from the Step 1.  I want to increase my maxsize to a value that I know is within the bounds of my current volume but will still allow my data operations to continue without being held hostage by the error that started this discussion. To do that I pick a value such as 2000 MB and multiple it twice by 1024 (i.e.: 2000 x 1024 x 1024) .  The result of that math is the value I place for the maxsize in the following statement.

alter database datafile 'G:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' autoextend on maxsize 2097152000;

Run the statement above and the tablespace maxsize will be set as desired.  Re-run the statement provided in Step 1 to see the values have changed and your percentage of use should now be changed as well.

References

http://garycoy.com/?page_id=13