August 27, 2020 By Henrik Loeser 2 min read

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

Was this article helpful?
YesNo

More from Cloud

Enhance your data security posture with a no-code approach to application-level encryption

4 min read - Data is the lifeblood of every organization. As your organization’s data footprint expands across the clouds and between your own business lines to drive value, it is essential to secure data at all stages of the cloud adoption and throughout the data lifecycle. While there are different mechanisms available to encrypt data throughout its lifecycle (in transit, at rest and in use), application-level encryption (ALE) provides an additional layer of protection by encrypting data at its source. ALE can enhance…

Attention new clients: exciting financial incentives for VMware Cloud Foundation on IBM Cloud

4 min read - New client specials: Get up to 50% off when you commit to a 1- or 3-year term contract on new VCF-as-a-Service offerings, plus an additional value of up to USD 200K in credits through 30 June 2025 when you migrate your VMware workloads to IBM Cloud®.1 Low starting prices: On-demand VCF-as-a-Service deployments begin under USD 200 per month.2 The IBM Cloud benefit: See the potential for a 201%3 return on investment (ROI) over 3 years with reduced downtime, cost and…

The history of the central processing unit (CPU)

10 min read - The central processing unit (CPU) is the computer’s brain. It handles the assignment and processing of tasks, in addition to functions that make a computer run. There’s no way to overstate the importance of the CPU to computing. Virtually all computer systems contain, at the least, some type of basic CPU. Regardless of whether they’re used in personal computers (PCs), laptops, tablets, smartphones or even in supercomputers whose output is so strong it must be measured in floating-point operations per…

IBM Newsletters

Get our newsletters and topic updates that deliver the latest thought leadership and insights on emerging trends.
Subscribe now More newsletters