Thoughts on how to move from Cloud Functions to Code Engine

Moving code and containers

IBM Cloud Code Engine is a fully managed, serverless platform to (not only) run your containerized workloads. It has evolved a lot since March 2021, the time when I published the blog post Migrating Cloud Functions Code to Code Engine. In 2021, there were only Code Engine apps and jobs. Earlier this year, Code Engine added support for functions(Function-as-a-Service, FaaS). Thus, in this post I am going to take a fresh look at that topic and discuss the options on how to move from Cloud Functions to Code Engine. 

From Bluemix to IBM Cloud, from Cloud Foundry to Code Engine

"Bring Your Own Community"

About seven years ago, I started to work with, then blog about Bluemix and Cloud Foundry. Not my first, but one of the first posts is titled "Some fun with Bluemix, Cloud Foundry, Python, JSON and the Weather". Reading that article again I feel nostalgic and it brings back memories of how I learned to deploy my apps to Cloud Foundry. And how I had fun with new cloud technologies.

Db2 on Cloud credentials and how to connect from your app

Over the past 12 months, Db2 on Cloud migrated instances to a new deployment model. It seems to be based on the IBM Cloud Databases model and resulted in a different structure for the service credentials. Previously, Db2 on Cloud instances were accessible on the typical ports 50000 and 50001. The JSON-based credentials object included ready-to-use connection URIs. Now, after the change, the VCAP object has more parts, but is missing the easy-to-use URIs. In the following, I am showing you how to consume the new credential structure in your app and to successfully connect to Db2 on Cloud.

Parse shutting down, move your data

Parse shutting down

This week Parse.com, Facebook’s Mobile Backend as a Service offering, surprised their users. The service will shut down next year and all users are asked to move on. The Parse backend server has been released as open source project, a tool has been made available to migrate data. My Bluemix colleagues have created migration guides.

Mike Elsmore has created a quick overview of how to provision the required services on Bluemix to move over your data from Parse.com. Reading his instructions probably requires more time than the actual migration process. If you are not that deep into Bluemix, want more details, or a simple click of a button to deploy the required components, I would recommend reading the extended tutorial that Andrew Trice wrote. He walks you through the process, step by step and screenshot by screenshot, on how to provision and configure the services, how to move the data, and eventually testing the migrated application. 

Parse is using the NoSQL MongoDB to store the data. You can take a look at the DatabaseAdapter.js and ExportAdapter.js files to see how Parse is using the database and, if you like, write your own adapter for Cloudant/CouchDB or maybe even a relational database like MySQL or DB2.

Given that several Cloud service providers and PaaS hosters have announced shutting down, it is an interesting time. It seems that a new chapter in the Cloud story has begun, market consolidation has started.

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.

Watch this! Move your DB2 monitoring to the in-memory interface (WLM monitoring)

Since its first days as a database management system, DB2 has been been changed. It has been extended by new features to serve customer requirements and has been adapted to the state of the art in hardware and software technology. One major new feature has been the introduction of the DB2 Workload Management in version 9.5 and related more comprehensive monitoring with finer granularity (in-memory metrics monitoring) in version 9.7. As with many product changes, it takes a while for customers to really put them to use and reap the benefits, especially when the existing functionality still works.

Thus I was happy when I saw a new article on IBM developerWorks describing how to move off the (old) snapshot monitoring interfaces in DB2 and to the in-memory metrics monitoring. What is included in the article is an overview of the advantages of the newer interface. This should get you motivated to read the rest of the article (and then to migrate if not done yet). It contains a side-by-side comparison of old and new interfaces and has many sample SQL queries. The queries demonstrate how to obtain DB2 runtime metrics using the old and new interface for some popular monitoring tasks. You can find the documentation of the SQL interface to the in-memory metrics in the DB2 Knowledge Center in this overview. Most of the pages in the manual have further SQL samples to get you started.

So take a look, it will also help you with one of the upcoming DB2 quizzes on this blog.

MySQL-style LIMIT and OFFSET in DB2

An "ancient" but not yet well-known feature in DB2 is the support of MySQL/PostgreSQL-style LIMIT and OFFSET in SELECT statements and searched UPDATE/DELETE. Unfortunately, it is not really documented and I am working on getting some more documentation added (think about the "mostly harmless" as in the Hitchhiker's Guide to the Galaxy).

To demonstrate what is possible, I created a small table full of good stuff:
db2 => create table sweets(id int, desc varchar(60), quant int)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(1,'dark chocolate',4)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(2,'marzipan bar',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(3,'almond cookies',10)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(4,'granola bar',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(5,'nut chocolate',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(6,'chocolate-covered peanuts',1)
DB20000I  The SQL command completed successfully.
db2 => select * from sweets limit 5
SQL0104N  An unexpected token "limit" was found following "".  Expected tokens
may include:  "FETCH FIRST ROWS ONLY".  SQLSTATE=42601

By default, DB2 does not know about LIMIT and OFFSET either. But setting the DB2_COMPATIBILITY_VECTOR to MYS (as in MySQL) or "04000" enables the syntactic alternative to the FETCH FIRST n ROWS ONLY. After restarting DB2 the SELECT works:

db2 => select * from sweets limit 5

ID          DESC                                                QUANT
----------- --------------------------------------------------- -----------
          1 dark chocolate                                                4
          2 marzipan bar                                                  1
          3 almond cookies                                               10
          4 granola bar                                                   1
          5 nut chocolate                                                 1

  5 record(s) selected.

The following two examples show the use of the OFFSET, i.e., where to start in the result set. I combined it with ordering on the quantity.

db2 => select quant,desc from sweets order by num limit 3 offset 2

QUANT       DESC                                                       
----------- ------------------------------------------------------------
          1 nut chocolate                                              
          1 chocolate-covered peanuts                                  
          4 dark chocolate                                             

  3 record(s) selected.

db2 => select quant,desc from sweets order by num limit 2,3

QUANT       DESC                                                       
----------- ------------------------------------------------------------
          1 nut chocolate                                              
          1 chocolate-covered peanuts                                  
          4 dark chocolate                                             

  3 record(s) selected.

Note that in the second example a special abbreviated syntax is used. The first number is the offset where to start, followed by the number of rows to return.

As said, there is not much in the DB2 Information Center (yet). Serge has some more background in his blog about different ways of paging through result sets.

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... ;-)

Oracle DBLINK and DB2 Three-Part-Names for Federation

One of the new features that went mostly unnoticed for DB2 10.1 and DB2 10.5 is the support for so-called three-part-names (nice pun?) for Federation. It allows the access to remote tables without the need to create nicknames. Thus, applications and users can be more dynamic and agile in what they access (there are drawbacks to it, too). When the DB2_COMPATIBILITY_VECTOR is set accordingly, even the Oracle-like DBLINK syntax of "schema.table@database" is tolerated. So much for the theory, I tried to get it to work on my laptop...

I already had two different databases created, and the DB2 compatibility vector is set to "ORA". Thus, I could focus on Federation-related commands. The first thing to do is to enable my DB2 instance for Federation (FEDERATED is the configuration parameter).
db2 update dbm cfg using federated yes
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

The next commands are directly executed in my first, "local" database, after connecting to it:
db2 => create wrapper drda
DB20000I  The SQL command completed successfully.


This creates the DRDA wrapper which is required to access DB2 sources. If you want to access Oracle databases from within DB2, you would need to create the NET8 or Oracle wrapper and some more steps which are described here. As I am going to access a local DB2 database, I don't need to catalog any nodes or map users. So what is left is to create a server, i.e., to make the database I want to access known to my database I am operating with.

db2 => create server myserver type db2/cs version 10.5 wrapper drda authorization hloeser password "secrets4you" options (dbname 'DBTWO')
DB20000I  The SQL command completed successfully.


Thereafter, I can already access the remote table using either a three-part-name or the Oracle dblink syntax.

db2 => select * from myserver.henrik.worldtest

ID          S             
----------- ---------------
          1 Hello World   

  1 record(s) selected.
 
db2 => select * from henrik.worldtest@myserver

ID          S             
----------- ---------------
          1 Hello World   

  1 record(s) selected.

Both statements do not require a nickname to be created, thus saving administration time and allowing to migrate Oracle applications more easily. One thing to note is that I need to connect to my local database with the user option in order for Federation to pass on my credentials. That is "connect to dbone user henrik" will work, just using "connect to dbone" will bring you authentication errors or SQL0204N "... is an undefined name".

Have you tried out dblink syntax with DB2? Comments?

IOQWT - A typical IBM acronym that works for DB2 BLU

IOQWT is a typical acronym, used to deal with lengthy product names like IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows. It is part of the InfoSphere Optim suite of tools for data lifecycle management. IOQWT usually helps to tune single queries or entire workloads. Some licenses for the tool are included in Advanced Editions and the Developer Edition of DB2. That is how I got my fingers on it. And the reason I had to try out using IOQWT is that the so-called Workload Table Organization Advisor (WTOA...?) can predict whether it makes sense to convert tables from row organization to a column-organized layout, i.e., whether DB2 with BLU Acceleration is a big benefit.

My journey started by starting up Data Studio with IOQWT integration. In the so-called Data Source Explorer I had to activate my database for tuning. After the license had been applied and additional internal-use tables been created, I could start tuning, i.e., invoke the tuning advisors. The first was to tell IOQWT what statements to analyze. It allows to directly paste a single query as text, however, it only recommends table organizations for a workload, a set of queries. Thus I chose to import a file with three queries. My test database uses a TPC-H schema and I imported 3 queries.

After saving the 3 queries to a workload, IOQWT was ready for action. As next step I could select what types of advisors it should run and what type of tuning suggestions I was interested in. After marking Table Organization as the kind of advice I proceeded to the next step, waiting for results. :)

As you can see below, IOQWT analyzed six tables from my small TPC-H database. If I would convert tables to column organization (BLU Acceleration), it predicted a performance improvement of 98.79%. The most gain would be for the first query which right now has the most costs and would have least costs associated after the conversion. So DB2 with BLU Acceleration seems to make sense for my workload and by analyzing it in IOQWT I got that information without converting my database.

That's it for today, see older posts on DB2 with BLU Acceleration.

Results from Optim Query Workload Tuner suggesting DB2 BLU

DB2 SQL Compatibility: CREATE INDEX ... EXCLUDE NULL KEYS

One of the often problems when migrating databases from Oracle to DB2 were with unique indexes. Oracle does not include and consider NULL keys when checking for uniqueness of values, DB2 does - or better: did. Starting with DB2 10.5 it is possible now to specify whether to INCLUDE NULL KEYS (the default) or to EXCLUDE NULL KEYS when CREATE INDEX is executed. This allows to have either the classic DB2 semantics or to mimic the behavior of other database systems. And thereby it reduces migration effort further.

In the following I tried the new feature by creating two tables, T1 and T2, with a unique index each. On T2 the index uses "exclude null keys". So let's see how they respond to my attempts to insert some values...:


create table t1(id int, s varchar(60))
DB20000I  The SQL command completed successfully.
create table t2(id int, s varchar(60))
DB20000I  The SQL command completed successfully.
create unique index t1_id_u_idx on t1(id)
DB20000I  The SQL command completed successfully.
create unique index t2_id_u_idx on t2(id) exclude null keys
DB20000I  The SQL command completed successfully.
insert into t1 values(1,'one')
DB20000I  The SQL command completed successfully.
insert into t2 values(1,'one')
DB20000I  The SQL command completed successfully.
insert into t1 values(2,'two')
DB20000I  The SQL command completed successfully.
insert into t2 values(2,'two')
DB20000I  The SQL command completed successfully.
insert into t1 values(2,'two again')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "HLOESER.T1" from having duplicate values for the index key. 
SQLSTATE=23505
insert into t2 values(2,'two again')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "HLOESER.T2" from having duplicate values for the index key. 
SQLSTATE=23505
insert into t1 values(null,'null')
DB20000I  The SQL command completed successfully.
insert into t2 values(null,'null')
DB20000I  The SQL command completed successfully.
insert into t1 values(null,'null again')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "HLOESER.T1" from having duplicate values for the index key. 
SQLSTATE=23505
insert into t2 values(null,'null again')
DB20000I  The SQL command completed successfully.

As can be seen, both indexes check uniqueness of non-NULL values. When I try to insert the value "2" again, an error is returned. Both indexes accept a NULL value. However, on T1 only a single NULL value can be inserted and then a violation of the uniqueness criteria is reported. On the second table we are able to insert another NULL value without problems because NULL keys are not included in the index.

Try it yourself, best by migrating a database from Oracle to DB2... ;-)

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.

San Francisco vacation, Audi cars, and DB2 replacing Oracle

Long title to an interesting journey...

I just returned from family vacation (and hence offline) that we spent in San Jose and San Francisco (California). This included more than a dozen Cable Car rides, walking steep hills, and enjoying Fog City. Could you imagine going down the hills in SF on skiers?



The marketing guys of Audi of course could. Speaking of Audi, which is part of the Volkswagen group: They were today featured in a press article because Audi has been replacing Oracle and HP with DB2 on Power. Switching over was not an uphill battle, but a smooth ride. And the system stability wasn't going downhill either...

Business trips and database migrations

As you might have guessed I have been travelling and I am going to travel again. Every business trip brings some risks and has associated costs, but each one also has its opportunities or specific goals. Many of my business trips are related to database migration projects, enabling customers or business partners to move from Oracle to DB2.

Depending on the trip destination there is more or less risk associated. There could be accidents, health issues, robbery or mugging, the risk of loosing your luggage, and of course of not getting enough sleep. To mitigate some of the risks, preparation is key, first of them knowing about the involved risks, what to watch out for. Often, you can benefit from the experience of others, e.g., reading a travel guide, reading hotel reviews, etc.

In terms of costs, usually there is a rough estimate based on data that can be quickly gathered and some travel experience. It helps to decide whether it is worth travelling. Would someone spend 5000 EUR for a 1000 EUR opportunity? What brings the trip in the long run?

Migration projects are similar to business trips. There are costs associated, there are risks, but also a benefit. Companies migrate from Oracle to DB2 because they want to save money, perform better, simplify their data centers and reduce the server and storage footprint, grow their businesses, enjoy competitive advantages, benefit from technology advances, or any combination of these. So they look into what it costs to get there and turn to IBM. Based on data that can be quickly gathered and migration experiences, a decision to move forward and to look into details can be made - similar to a business trip. Tools like MEET allow to analyse the level of compatibility and to quickly give an overview of problems. Experience with Oracle to DB2 migration projects, big and small, allows to attach costs to the issues found. A proof of concept allows to gain experience, similar to some business trips in a controlled environment to validate assumptions and to build up a portfolio of hotel and airline reviews, and to train the business travellers.

And finally comes the decision to embark, to start the journey. There are risks, but there are also best practices that can be followed and tour guides that can help if needed. As with business trips, there are tour reports of others: Coca-Cola about moving from Oracle to DB2 at the IOD conference in October, customers talking at the IDUG conferences about their migration projects, blog posts detailing why projects succeeded and lessons learned.

At some point everybody faces the question the first time: Do I want to travel? Especially for migration projects it is an unusual question that might only come up once and not everybody feels comfortable with. It might be similar to the first trip to a foreign country. But then there are resources to help and to assist with the decision and along the journey.

Forrester Analyst: Customers report success with DB2's out-of-the-box compatibility to Oracle's SQL

Forrester Research analyst Noel Yuhanna posted a blog entry titled "Database Migrations are Finally Becoming Simpler". Based on customer feedback he got, one can see 90% or more compatibility and hence the migration effort for moving from Oracle to DB2 requires only days or weeks instead of months or years. Noel Yuhanna says "This is huge".

Database customer want to save millions of dollars, but earlier a migration was costly and painful and required a larger investment of their own. With the DB2 compatibility layer the migration becomes simpler.

SSacSA - A typical IBM acronym, but not the typical product (Move from Sybase to DB2)

When I first heard about SSacSA (it's part of the new features in DB2 9.7 FP 2), I thought that this is another typical IBM acronym I had to learn. But when I looked closer at it, and it stands for DB2 SQL Skin Feature 1.0 for applications compatible with Sybase ASE (what a name for itself!), I wasn't scared of the long name and its acronym anymore. The biggest reason is that this feature is very useful and can save customers lots of money.

With this SQL skin (it's on the very outside of the DB2 engine, in the JDBC layer) it is possible to take applications written against Sybase ASE and let them run against DB2 without too much migration effort. The trick is that SQL statements written in the Sybase dialect are caught in the JDBC driver and rewritten to DB2's version of the SQL language. The statements can then be executed in DB2 and the application doesn't notice it is now running on top of DB2.

A chat with the lab (cwtl?) is scheduled for tomorrow, Wednesday 23rd, to discuss this new offering. You can register here.