How-tos

Db2 and Cloud Object Storage: Get Started

Share this post:

For a while, Db2 has built-in support to directly access Cloud Object Storage (COS) like Amazon S3 and IBM COS (Softlayer / IBM Bluemix IaaS). It allows to perform backups (and restores) as well as data load operations to and from the cloud. Phil Nelson wrote an excellent overview and introduction on the IDUG blog with his examples focused on S3. Thus, in the following I am going to mostly cover Db2 using the IBM Cloud Object Storage and fill in some additional details.

Use IBM Cloud Object Storage with Db2

IBM Cloud Object Storage

Overview

Db2 manages Cloud Object Storage location and access to them on the instance level. First, the provider along with the necessary credentials are cataloged and an alias assigned. Thereafter, the alias can be referred to on the database level by using an URI with the DB2REMOTE prefix:

backup db hltest to db2remote://hls3/henrik/backups;
load from db2remote://hlswift/henrik/testdata.csv of del insert into loadtest;

First Steps

To get started with Cloud Object Storage on a recent version of Db2 some requirements need to be met. Because the information about a cloud or remote storage location includes access credentials, Db2 requires the presence of a keystore. The process of creating a local keystore or how to configure a centralized keystore are described in the Db2 Knowledge Center. It is the same process as for native data encryption. If you already have that up and running, you are good to go. Else, the next is to configure the Db2 instance keystore_location and keystore_type settings. In my case it was simply:

update dbm cfg using keystore_location /home/henrik/ks/hlks.p12
update dbm cfg using keystore_type pkcs12

The keystore “hlks.p12” of type PKCS12 is located in the directory “ks”. Thereafter, restart Db2 for the changes to become active.

An alias for a storage location is defined on the instance level using the command CATALOG STORAGE ACCESS ALIAS:

catalog storage access alias hlswift
vendor softlayer
server https://fra02.objectstorage.softlayer.net/auth/v1.0/
user ‘IBMOS1234567:henrik’ password ‘5abc123abc123abc1238791a3f905196af8da497b3c1a1add123abc’;

The above creates a new alias “hlswift” with a Frankfurt-based storage location. The credentials are encrypted and stored in the keystore configured above. Once I have an alias defined I could use it like shown in the backup example above.
The CATALOG command has (security) options to restrict who can use the alias. It is also possible to directly specify which container or storage bucket to use.

Staging Area

As a temporary buffer for uploading or downloading files Db2 uses a staging area. The path for it can be set via the DB2_OBJECT_STORAGE_LOCAL_STAGING_PATH registry variable:

db2set DB2_OBJECT_STORAGE_LOCAL_STAGING_PATH=/tmp

The above sets the staging area to the “/tmp” directory. The default is in the instance directory under “sqllib/tmp/RemoteStorage.xxxx”. The “xxxx” refers to the Db2 partition number. Note that the staging area needs to have enough capacity to hold backup images or load files.

Administration / Maintenance

The defined storage aliases can be printed via the LIST STORAGE ACCESS command. It returns the details on each of the defined storage locations except the passwords. Here is the entry for the alias “hlswift” as defined above:

...
Node 3 entry:

ALIAS=hlswift
VENDOR=softlayer
SERVER=https://fra02.objectstorage.softlayer.net/auth/v1.0/
USERID=IBMOS1234567:henrik
CONTAINER=
OBJECT=
DBUSER=
DBGROUP=SYSADM

Next, to remove an existing alias, use the UNCATALOG STORAGE ACCESS ALIAS command. Last but not least, there is a command to rotate the key for the encrypted credentials, ROTATE MASTER KEY FOR STORAGE ACCESS.

Conclusions

The above provides you with a rough introduction into combining Db2 with Cloud Object Storage (or the other way?) with a focus on the IBM COS. If not done, start by reading Phil Nelson’s introduction at the IDUG website. There are many details that I didn’t cover today which warrant a follow-up blog post.

If you want to learn more about that topic or discuss the blog with me, then meet me at the upcoming IDUG EMEA and Db2 Aktuell conferences. Additionally, you can read more about the features and benefits about IBM Cloud Object Storage here. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Technical Offering Manager / Developer Advocate

More How-tos stories
April 30, 2019

Introducing IBM Analytics Engine v1.2 and Announcing the Deprecation of IBM Analytics Engine v1.0

We are excited to inform you about the new version of IBM Analytics Engine v1.2 that will be available starting May 15, 2019. Along with this release, Analytics Engine v1.0 will be retired.

Continue reading

April 25, 2019

What is Object Storage?

In our latest lightboarding video, Anirup Dutta explains how object storage works, lists some of the benefits, and give you some use cases for when object storage may be your best option.

Continue reading

April 18, 2019

Using Fio to Tell Whether Your Storage is Fast Enough for Etcd

We're going to show you how to use fio—a very popular I/O tester—to evaluate whether your intended storage for etcd is fast enough to support good etcd performance.

Continue reading