Trip report: Sustainability management and reporting

Last Friday, I attended the annual conference of the Bodensee Innovation Cluster for digital change (changes due to digitalization). The conference had several interesting talks and included workshops. Let me give you a quick overview of the innovation cluster, then delve into the sustainability topic which was part of the conference.

Introduction and resources for migrating from Oracle to DB2

Well, a boring headline for an interesting topic. Originally I had planned to title today's blog entry "Forget about Black Friday and Cyber Monday - Save big by moving to DB2". But then writing this entry has dragged on for some days...

Database Conversion Workbench

In the past weeks I have been asked several times about how to migrate off Oracle and on to DB2. Let me give you a quick overview of the technical side, for the financial part you have to ask an IBM business partner or an IBM seller. Although you can move to DB2 from database systems like Microsoft SQL Server, MySQL, and others, I will focus on Oracle because of the compatibility features built into DB2.

When moving off Oracle this could be for a SAP system (or other vendor application) or other applications ("non-SAP"). For SAP environments and applications from several other software vendors there is a (kind of) standardized process to migrate a system. The reason is that there are database-specific definitions and feature exploitations. A great example is how SAP is making use of the DB2-only BLU Acceleration to boost performance for analytic environments. Many software vendors provide tools for database migration and related processes or services.

For custom scenarios where the application code is available, a manual migration applies. The traditional barrier to a migration, the (more or less) incompatibility of products, has been torn down by adding compatibility features to DB2. Some of those features come ready to use by any user, some require specific preparation of DB2 because they may impact the traditional handling/"look and feel". The DB2 Knowledge Center has a good introduction and overview into that topic: "DB2 Compatibility Features". If you are comping to DB2 with a background in Oracle then use the Terminology Mapping to discover how products, features, and commands are named in the world of DB2.

From release to release there have been several enhancements to the SQL compatibility with database vendors such as Oracle. An overview by release can be found in the related "What's New" section of each of the recent DB2 releases:

• SQL compatibility enhancements in DB2 9.7
• The added compatibility features in DB2 10.1
• Even more SQL compatibility enhancements in DB2 10.5

I have to point out that the features mentioned in the linked documents are only related to the SQL language, but that there have been several other features dedicated to making a transition from Oracle to DB2 as smooth as possible. Some of them are covered in the section "Application development enhancements":

• New features and enhancements related to application development in DB2 9.7
• Application development improvements in DB2 10.1
• The related section for DB2 10.5 (only JSON support listed)

If you prefer a book instead of the DB2 Knowledge Center, then I recommend the IBM Redbook "Oracle to DB2 Conversion Guide: Compatibility Made Easy". It gives an overview of DB2, the tools needed for a migration in a non-SAP environment, and the conversion approach. In the appending you will also find a nice terminology mapping, i.e., explaining how Oracle commands and features are named in the world of DB2.

A key tool for custom migrations is the Database Conversion Workbench (DCW). It is a plugin into the IBM Data Studio, a free tool for database administration, design, and SQL development. The DCW allows to analyze a database schema with respect to DB2 compatibility. The result is a valuable foundation for estimating the overall conversion effort. Once that is done (or not needed), the Database Conversion Workbench helps in the process of moving the database schema, database-side SQL packages, and thereafter the data from another database system to DB2. DCW also includes a package visualizer to help understand package dependencies which simplifies the code conversion. See this page for an overview and more resources around the Database Conversion Workbench.

An important DB2 feature related to the compatibility is the ability to run PL/SQL code. Several administrative PL/SQL packages ship with DB2 which can be found in the "built-in modules" section. Moreover, there are also some PL/SQL packages that can be used "as is" and are available from IBM developerWorks in the database conversion community: See here for the add-on PL/SQL packages.
That's all for now with my overview of resources for the Oracle to DB2 migration. I hope that it provides a good introduction into that (sometimes confusing) topic.

BTW: I have been covering many migration-related topics in my blog. Serge Rielau and now Rick Swagerman have provided many SQL tips for DB2 in their blog.

New DB2 BLU video - Analytics on IBM POWER: the game is changing

Do you like playing "Connect Four" (4 gewinnt, Four in a Row, Fire på stribe, Connecta 4, ...)? Well, there is a new new video talking about DB2 with BLU Acceleration, SAP BW, and DB2 on POWER. And it has an ending that is typical for IBM :) The customer wins. See for yourself...

Boring news? Yet another benchmark record for DB2 on POWER

Yesterday, IBM announced another world record, this time for the Two-Tier SAP Sales and Distribution (SD) Standard Application Benchmark. Again, this was based on DB2 for Linux, UNIX, and Windows running on the IBM POWER platform.

Now combine this very competitive speed and throughput with very competitive pricing and you should have a winner. If you are on Oracle right now then, yes, DB2 understands PL/SQL as well.

Catching up - 5 weeks in review

Well, I am currently catching up. A very busy time, some offline vacation, a surgery with time off, and some busy days getting back "into" work have left me not posting anything for the past 5 weeks. And what has happened!

DB2 9.7 FP2 (9.7.2) is out now with some fixes and new features - more on that in a separate post. For those on DB2 Express-C, the good news is that IBM has updated the free to develop, free to deploy, free to distribute version of DB2, too.

Speaking of free products, there is a free eBook Getting started with IBM Data Studio for DB2. Actually, this is old news, but what is new is that there is now a free German version of that book, named Einstieg in IBM Data Studio für DB2.

While I am at German and Germans, I was surprised to read that SAP is buying Sybase. Apparently, this deal is to move SAP into the mobile space, not so much about Sybase's dying database business. And there was another interesting acquisition in the database area, too.
Teradata has bought xkoto and apparently during that process stopped selling new GRIDSCALE licenses. There is discussion, not just at IBM business partners like Triton Consulting, about the increased importance of the DB2 pureScale technology as it suddenly becomes the only available solution to address scalability and cluster high availability. Speaking of DB2 pureScale, the related Information Center is now open to the public.

There was so much more in the past 5 weeks, I will update you once Germany has won the soccer world championship... ;-)

Small is beautiful (and fast): LOB Inlining

In the past I had emphasized that LOBs and XML data are different in DB2, even though both are stored in the LONG tablespace if you wish so (LONG IN option during CREATE TABLE). XML data is buffered, i.e., pages with XML data are held in the bufferpool, LOBs require a direct fetch via their descriptor and are not buffered by DB2. XML columns do not have a length specification, LOB columns have the maximum length specified. The bigger the maximum possible LOB size is, the larger is the LOB descriptor that usually is stored as part of the row.

Now, starting with DB2 9.7, they have a neat feature in common: Both can be inlined. What is inlining and what are the benefits (for LOBs)?

Because LOBs and XML data can be quite large, they are stored outside the regular row data, i.e., outlined, and a LOB descriptor points to the large object. With inlining however, when the data fits into the regular data pages, it is stored as part of the row, i.e., inline.
DBAs can specify the maximum inline size for each LOB or XML column by using "INLINE LENGTH xxx" during CREATE/ALTER TABLE. The total row size, i.e., the sum of all column sizes and some overhead, needs to fit into the data pages. What this means is that if you use 8k pages, an inline length of 15000 won't work for sure. In contrast to XML columns, if no inline length is specified, for LOB columns an inline length equal to the descriptor size is set implicitly.

Why store 200 bytes outlined and have a 220 bytes LOB descriptor in the row (total 420 bytes), when all can be stored in 200 bytes in the row? And now we are already talking about the benefits of LOB inlining. If you have lots of small LOBs, inlining can improve performance and reduce storage significantly. Storage is reduced because the extra LOB descriptor is avoided. In addition, LOB data stored in the row, i.e., inlined, benefits from row compression when enabled. The performance improvement comes from the direct access (no LOB descriptor involved) and the fact that regular table data is cached in the bufferpool. The direct fetch operations to the unbuffered LOBs are avoided.

As shown, LOB inlining has many benefits and if your Large OBjects (LOBs) are really small objects you should exploit this feature. I found this article describing how LOB inlining in DB2 is used for SAP databases. Because many small LOBs exist to store properties, this can have significant performance benefits.

Wow, Oracle combines database system with disks (again!)

Yesterday was the much overhyped event of a company announcing some dbms coupled with newer disk subsystems and an increased cache. The result is that as long as everything fits into memory and cache, performance will benefit from it. If not, it still sucks.

A nice summary of the event is over at The Register:

If Oracle is trying to convince Sun customers that it is committed to the Sparc platform, perhaps it is not trying hard enough.

and

Today's Exadata V2 launch event started 15 minutes late, if you missed the Webcast launch (you can see the replay here when it becomes available), and started with an "extreme performance" theme that showed Captain Larry Ellison and Oracle's World Cup catamaran striking impressive poses, and then cut to a live Ellison in the studio, who doesn't do his own clicking during presentations and started out by admonishing some worker with "Next slide, please, I already know who I am."

BTW: While you are at it, please read here how companies have moved from Oracle to DB2.