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.

connect_proc and locales: Connecting the dots for combined fun

Last year in a blog article I had written about some fun I had with different locales and the dayname function in DB2. Last week I showed you links to customize the application environment. Why not combine the two for some added fun? Let me show you how to connect the dots. I am going to show you how to adapt the locale setting in DB2 based on user preferences.

First, we need a simple table to store our user preferences:
create table myschema.users (id int, uname varchar(30), locale char(5));

For our testing purposes, two user entries will do:
insert into myschema.users values(1,'HLOESER','de_DE');
insert into myschema.users values(2,'DB2FENC1','fr_FR');

What we will do is to set up our own procedure as connect_proc in DB2. In that procedure we access our small user table, read out the preference for the locale setting based on the value of the SESSION_USER. Then we set CURRENT LOCALE LC_TIME special register which controls the language and behavior for day name, timestamp format, rounding, truncation and some more.

create or replace procedure hl.my_connect()
reads sql data
language sql
BEGIN
   declare loc char(5);
   select locale into loc from myschema.users
        where uname=SESSION_USER;
   set current locale lc_time loc;
END@

After the procedure is created, some testing should be done:
db2 => call my_connect()

  Return Status = 0
What also needs to be done is to allow everyone to execute our procedure:
db2 => grant execute on procedure hl.my_connect to public
DB20000I  The SQL command completed successfully.  
 The final step is to update the database configuration and tell DB2 to use our procedure on every successful connection.
db2 => update db cfg using connect_proc hloeser.my_connect
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

To see the effect of our procedure in place we need to connect as user "hloeser" and as "db2fenc1".

Testing as "hloeser":
select dayname(current date) from dual

1                                                                                                  
----------------------------------------------------------------------------------------------------
Monday


When we connect as "db2fenc1", the result is different:

values(dayname(current date))

1                                                                                                  
----------------------------------------------------------------------------------------------------
lundi 

It seems, replacing the connect_proc with our procedure worked.  I close with one important advise: Leave one database connection open during testing. If your connect procedure is not working well, you need that connection to reset the database configuration (update db cfg using connect_proc '')...

Update: There was a question whether the connection is needed or why I recommend it: The answer is that connect_proc, once set, can only be updated when connected to the database. Unsetting connect_proc can be done when not connected, but the database needs to be inactive. Thus, having the connection saves time when developing.

I'm not you - Ways to customize a DB2 application environment

I am not you, she is not him. Users differ, applications differ. There are two interesting concepts in DB2 that help to adapt an application environment and application and that are not widely known. One is the connect_proc database configuration parameter, the other is conditional compilation of SQL. Let me give you some ideas of what it is and links to dig deeper into it.

Introduced in DB2 9.7 FP3, the connect procedure let's use configure a stored procedure that is invoked whenever someone (an application) connects to DB2. Thus, the session context can be tailored to the user or application by setting the locale, the path to resolve functions, optimization levels and more. Even though the procedure is not allowed to modify the database it could invoke another routine with an autonomous transaction. That way some information could be logged ("track", "audit") or another event be triggered. The DB2 Information Center has some examples on how to use connect_proc for session customization. Serge Rielau showed how to implement some logging.

Conditional compilation allows to tailor the compiled SQL to the actual environment. You could just use procedure or function code for little or for big endian - making it execute faster, implement your own routine in different ways depending on the DB2 version - use a more efficient implementation with newer DB2 versions, or plug in a lighter version of code - maybe without debug code. The key is to use SQL_CCFLAGS, flags for conditional SQL compilation. The variable can be set at the database or session level and then referenced within the actual SQL code. Special "_IF"/"_ELSEIF"/"_THEN"/"_END" directives do the trick of selecting the code you want. This is similar to shell scripting and programming languages.
In the DB2 system catalog you can find out which flags were set when a routine or trigger was compiled ("whom to blame..."). Of course the currently set value can be retrieved using the CURRENT SQL_CCFLAGS special registry.

Of course it is possible to combine the two. Develop your own library of connect-related actions, audit, and setup routines, then set SQL_CCFLAGS to implement just the mix you need for a specific application environment.

Everything is my IP: Obfuscating a "Good Morning"

Want to impress or scare your co-workers? Or show your boss how to hide Intellectual Property (IP)? Try this with DB2:

db2 => create view hello  WRAPPED SQL10050 ablGWmdiWmtmTmdKTmJqTmdKUmteUmduUnZq1mZK2idaWmdaWmdaWndjHBcaGicaGQIwPJVuycUYnzvpYqeM0J9mNKgGspriVQtaGRWxKEiYWqvXK6WNaJSUbhrqa
DB20000I  The SQL command completed successfully.
db2 => select * from hello

GREET      
------------
Good Morning

  1 record(s) selected.

Even though the functionaliy for it was introduced in DB2 9.7 FP2, probably not many have looked at obfuscation of SQL code, i.e., the body of routines, views, triggers, and packages or modules. Basically, if some business or application logic is inside the database, obfuscation might be an option. This could be either because you want to protect your IP or for security reasons. At the time code obfuscation was released for DB2 in 2010 I wrote that it would be possible to abandon spaghetti code as a way of protection.

It is pretty simple to use obfuscation as everything needed is in the built-in module DBMS_DDL. It has a function WRAP and a procedure CREATE_WRAPPED. When WRAP is called with a valid statement as parameter, it returns a string with the DDL statement to create a wrapped, i.e. obfuscated, version of it. The procedure CREATE_WRAPPED is called with a valid DDL statement, transforms it to the wrapped version and directly executes it. WRAP could be called to produce encoded statements to be embedded in text files. The files would be given to customers. CREATE_WRAPPED could be called directly from an application which creates objects inside a database. Customers would have a hard(er) time to reverse engineer the application logic of functions, procedures, or entire packages created in the database.

So instead of creating the view "hello" as shown above, my application could have issued the following statement:
call dbms_ddl.create_wrapped('create view hello (greet) as select ''Good Morning'' from dual')

And now you know the logic that was hidden inside the strange string of my first SQL statement above ("ablGWmdi..."). Try to memorize that pattern and impress your co-workers or boss...

BTW: If you are a hardcore DB2 user and have never used the DUAL table, read here.

Redbook updated for DB2 10.5: Oracle to DB2 Conversion Guide - Compatibility Made Easy

The IBM Redbook "Oracle to DB2 Conversion Guide - Compatibility Made Easy" has been updated to include coverage for DB2 10.5. The book now describes the new compatibility features added as part of DB2 10.5 and the improved functionality of the Database Conversion Workbench. Readers will also find the DB2 Advanced Workgroup Server Edition which was added in the latest release.

Happy conversions, may all of your applications convert... ;-)

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.

Answer to DB2 Quiz: Don't forget to close what was opened...

Earlier this week I posted a DB2 quiz showing an error message. It showed that creating a function failed with an "end of file reached" error. I received some feedback by email and the consensus was that the error somehow was caused by the earlier executed scripts. But what exactly is the cause?

In the quiz I had mentioned that we ran into the error during a proof of concept when porting an application from Oracle to DB2. This is a first hint. The DB2 Information Center has a document describing how to set up the DB2 environment for Oracle application enablement. In there are two versions for executing SQL statements listed, one based on clpplus, another one using the DB2 Command Line Processor. For running statements with the DB2 CLP it is recommended to use "SET SQLCOMPAT PLSQL" first. That switches the CLP to taking the forwad slash ("/") on a new line as PL/SQL statement termination character. Using "SET SQLCOMPAT DB2" switches back to regular operations.

The error "end of file reached" indicates that a statement is incomplete. This could mean a statement is missing a clause (usually a syntax error) or the statement or file is missing something. Is it a statement terminator? What we tried then while debugging is to execute a simple CREATE TABLE:

>>> db2 -tf table2.sql
DB20000I  The SQL command completed successfully.

Strangely, this works. Is it related to our function definition? We opened another window with a new DB2 connection and the CREATE FUNCTION when directly pasted into the CLP window succeeded. And then it dawned on us...

As with other settings, including the database connection, they are kept between invocations of DB2 CLP. In the example in the quiz we only open the database connection once. In one of the scripts that we executed, the SET SQLCOMPAT PLSQL was set, but not reset to the default using SET SQLCOMPAT DB2. Simple DDL like CREATE TABLE seems unimpacted by this setting. However, creating more complex objects like the shown function gives an error. I asked the DB2 Information Development team (the one creating the documentation) to provide details on the SQLCOMPAT switch.

What can be taken away is to make sure that when putting a switch into a file, make sure switching back to defaults is part of the same file. Sounds like best practices from coding days... ;-)

BTW: I disabled captcha codes for comments. This should make it more interactive and lead to less emails. Have a great weekend.

DB2 Quiz: Why the error? (create function)

Last week during a Proof-of-Concept (PoC) with an ISV we tried to port and deploy database objects from Oracle to DB2. During some testing I ran into a strange error (at least at first) and only stepping back and getting some coffee helped to explain it. Can you guess what went wrong and why we ran into the error?

For the actual porting we used the IBM Data Movement Tool, but the DB2 Command Line Processor for some side testing. Here is what we did on the command line:

>>> db2 connect to mydb

   Database Connection Information

 Database server        = DB2/LINUX 10.1.0
 SQL authorization ID   = HLOESER
 Local database alias   = MYDB


>>> db2 -tf script1.sql
DB20000I  The SQL command completed successfully.

>>> db2 -tf scriptN.sql
DB20000I  The SQL command completed successfully.

>>> db2 -tf func.sql
DB21007E  End of file reached while reading the command.

>>> cat func.sql
CREATE OR REPLACE FUNCTION TAN (X DOUBLE)
     RETURNS DOUBLE
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN SIN(X)/COS(X);

>>> db2 -tf func.sql
DB21007E  End of file reached while reading the command.

Basically we successfully tested several SQL scripts before we ran into the "end of file" error. There were no special hidden characters in the file. Opening another database connection in another window and executing the script there succeeded.

Do you know why? Any guesses? I will post the solution in a follow-up article.

Business Travel and DB2 SQL Compatibilty

As part of my job I travel a lot within Europe. Most countries accept the Euro and participate in the Schengen Agreement (forming the Schengen Area) which means borderless travel and few, if any, controls (more that in a future blog post). What remains are the different languages and the different culture, including food. During one of the recent journeys I came up with a nice comparison of the DB2 SQL (Oracle) Compatibility and understanding the different European languages.

For the performance of PL/SQL and the Oracle SQL dialect in DB2 we often have to point out that statements are compiled to regular DB2 runtime code (so-called sections and packages). At runtime DB2 basically doesn't know whether DB2's SQL or the Oracle-like syntax was used. Both perform equally well as there is only one runtime infrastructure, no emulation. The same is true for SQL vs. XQuery, there is the same single runtime infrastructure.

In school I learned English, Latin, and French, later at university Spanish. I don't speak Latin, French, or Spanish fluently, but I understand enough to get around. The same for Dutch, Danish, etc. What that means is that I don't need a translator, but can directly understand local road signs, hotel employees, newspapers. This gives a "performance advantage", again, as I (my "runtime infrastructure") can process the languages directly, not all ("compatibility rate"), but enough for most common situations. Here DB2 and I differ as DB2 has a higher compatibility rate...

DB2 and important stuff my smartphone can do...

My smartphone can do a lot of cool stuff (and I won't tell you whether it is Android-based or an iPhone). I can make calls on it, I can check and write emails, get directions within cities I travel to, I can listen to music, take and watch pictures, use it for boarding airplanes or trains (mobile ticket), and I can read interesting blog posts on the upcoming DB2 10:

• Matthias Nicola on new XML features and enhancements in DB2 10
• Serge Rielau on more than 98% Oracle compatibility and on faster PL/SQL in DB2
• Dave Beulke on why DB2 10 for LUW extends IBM's DBMS leadership

Last but not least, my smartphone has two advanced features that I will use over the following days:
It has this tiny power button that I will use to turn it off over the weekend. And finally it has a calendar and alarm function: DB2 10 will be available on April 30th, this next Monday. My alarm is set, so I can download DB2, access the Information Center with details on the cool new stuff. Yes, DB2 is cool, too...

DB2 and PL/SQL features

Today, I answered an email in which I was asked whether DB2 supports PL/SQL and such features as exception handling, the Oracle data dictionary (system catalog), triggers, etc. My response was that many features, especially the most important ones, are supported and that it has become fairly easy - compared to some years back - to move from Oracle to DB2. Many companies are considering this, often as a way to start a dual vendor strategy and to save money.

How do you find out what is supported and how do you find more information? Here are some links:

• Introduction to compatibility features
• DB2 Information Center start page on PL/SQL support
• PL/SQL features in DB2
• Mapping of Oracle to DB2 error codes and exceptions
• Oracle data dictionary views in DB2
• Serge Rielau's blog with many SQL tips (not just for (former) Oracle users)
• A wiki on developerWorks with Oracle to DB2 migration tips & tricks and some news
• Henrik's resources page

Happy reading and have a nice weekend...

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.

Obfuscate your DDL statements (aHJgFSG127_henRIK)

It's always interesting to take a look at the new functionality in a new (fixpack) release. DB2 9.7 FP2 adds functionality to obfuscate DDL statements. You might state that with all the parameters and options or page-long spaghetti code inside stored procedures DDL statements already are confusing. Well, now you can add another layer of obfuscation with the help of DB2.

The key to obfuscation is to know about the new WRAP function and CREATE_WRAPPED procedure in the DBMS_DDL module and the WRAPPED keyword. You can pass a string with a regular DDL statement to either routines. In the case of WRAP a string (CLOB) with the encoded, i.e., obfuscated statement is returned. When CREATE_WRAPPED is used, the statement is directly deployed into the database, i.e., it is executed and thereby the obfuscated statement text stored in the system catalog.

Obfuscation works for the following types of objects:

• procedure
• functions
• triggers
• views
• PL/SQL packages and package bodies
• modules to which functions or procedure are added or which are published

An obfuscated statement could look like the following:

CREATE PROCEDURE henrik.otest(col1 varchar(20)) WRAPPED SQL09072 aHJgFSG123_henRIKobFU3cAtEDtHIsSTRinGANdmaDEITun4eaDABle

The SQL09072 is a version identifier (DB2 9.7 FP2), so that DB2 remembers what version of the encoding function was used.

So try to not use any spaghetti code anymore, but start WRAPing your procedures...

My SYSDUMMY1 has turned DUAL

For ages I have used the SYSDUMMY1 view to evaluate simple expressions or retrieve registry variables (some of that could have been done using the VALUES expression). It seems that I will switch to a shorter version of it and in the future will most of the times use DUAL (SYSIBM.DUAL).

Once the DB2_COMPATIBILITY_VECTOR includes 0x02, you can even leave out the SYSIBM schema and just use DUAL:

db2 => select current_date from dual

1
----------
03/23/2010

  1 record(s) selected.

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.