Integrate Db2 with S3-based cloud storage.

Recently, I was contacted about an old blog post where I discussed how to access Cloud Object Storage from Db2. Since writing that article, both Db2 and (IBM) Cloud Object Storage have evolved. That means it is time for an update on how to backup data to cloud storage, load from external storage, and even directly query data somewhere in the Internet.

S3 API

Originally, S3 was just the shortname of AWS’s Simple Storage Service. Because of its wide spread, its S3 API was adopted by many other storage providers. This includes IBM Cloud Object Storage.

Earlier, IBM was offering an OpenStack Swift-based cloud storage that was part of its Softlayer acquisition.

Db2 and S3 API-based storage

Db2 integrates with S3-based storage in different areas. One way to access S3 storage is to use the CATALOG STORAGE ACCESS command that I discussed in my old blog (see there for details). It allows commands like LOAD, INGEST, BACKUP, and RESTORE to access files hosted on cloud object storage solutions.

Another option is the CREATE EXTERNAL TABLE statement. It allows you to create a table that has its data not stored in the database, but with the data in a (remote) file. This includes S3- and SWIFT-based cloud storage. 

You can even dynamically reference such an external table without first creating it. See the external-table-reference definition as part of the FROM clause in subselects.

All that is needed to access the IBM Cloud Object Storage (COS) using the S3 API is information on its endpoint (the URL), the right credentials, and the bucket (folder) name. As a beginner, obtaining the right credentials might be tricky. The reason is that the usual API key or username/password do not work. Special HMAC credentials are needed. But you can create them the same way you generate regular storage credentials, only an additional parameter is needed.

Db2 and S3 examples

What does it look like when accessing IBM Cloud Object Storage (COS) from Db2 when using the S3 API? Here are some examples to get you started:

Catalog a COS instance in Db2:

CATALOG STORAGE ACCESS alias mys3 
VENDOR S3
SERVER s3.eu-de.cloud-object-storage.appdomain.cloud 
USER 'f7foobarxxxcfeb4bxxx' 
PASSWORD '22223333foobarfoobar22223333'" 

Use a specific file in a COS bucket to define an external table:

CREATE EXTERNAL TABLE exttab2(a int)
USING (DATAOBJECT 'mydatafile.dat' 
S3('s3.eu-de.cloud-object-storage.appdomain.cloud', 'f7foobarxxxcfeb4bxxx', '22223333foobarfoobar22223333', 'mybucket' ) ) 

Reference a file hosted in a COS bucket to insert its data into a database table:

INSERT INTO mytable
SELECT * FROM EXTERNAL 'mydata.txt' 
USING (CCSID 1208 s3('s3.eu-de.cloud-object-storage.appdomain.cloud', 'f7foobarxxxcfeb4bxxx', '22223333foobarfoobar22223333', 'myotherbucket')) 

The above links and examples should get you over any bumps and let you make use of cloud storage as an additional backup location (disaster recovery) or as a way to easily integrate external data. 

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.