Connect to HAQM RDS for Db2 using AWS CloudShell

Connecting to an HAQM Relational Database Service (HAQM RDS) for Db2 instance has traditionally required spinning up an HAQM Elastic Compute Cloud (HAQM EC2) bastion host or running Db2 clients locally. With the new AWS CloudShell virtual private cloud (VPC) integrated environments, you can now securely connect—with no HAQM EC2 required, no local installs, and no cost beyond normal HAQM RDS and AWS networking.

In this post, we show you how to connect to HAQM RDS for Db2 using CloudShell.

Solution overview

CloudShell offers the following benefits:

Zero-cost client – CloudShell is free; you only pay standard network and HAQM RDS charges
Same subnet – CloudShell offers minimal latency—your CloudShell session sits alongside your RDS database in the VPC
No HAQM EC2 – You don’t have to provision, patch, or manage a bastion host
Preinstalled AWS CLI – The AWS Command Line Interface (AWS CLI) comes configured in CloudShell by default, and CloudShell and now fully supports custom VPC networking

The solution consists of the following steps:

Launch CloudShell in your VPC.
Download and install the IBM Data Server Driver thin client.
Configure both plain-text (TCP/IP) and SSL connections.
Test connectivity with IBM’s Command line processor plus (CLPPlus).

Prerequisites

You should have the following prerequisites:

An existing RDS for Db2 instance, reachable in a VPC
A VPC subnet and security group that allows inbound access on your Db2 ports (default TCP 50000+ or SSL 50xxx)
An HAQM Simple Storage Service (HAQM S3) bucket you control, or a publicly accessible download link, to fetch the IBM Data Server Driver
You need an IBM ID to download the Db2 driver:
Access to HAQM CloudShell

Launch CloudShell in your VPC

Complete the following steps to launch CloudShell in your VPC:

Sign in to the AWS Management Console and choose CloudShell in the menu bar.
In the CloudShell window, choose Actions and Create VPC Environment.
For Name, enter a name (for example, PRIVATE).
For VPC, choose the VPC hosting your RDS for Db2 database.
For Subnet, choose the subnet that belongs to the Availability Zone in which your RDS for Db2 instance is hosted.
For Security group(s), pick up to five, and choose the same ones which are associated with the RDS for Db2 instance.
Choose Create.

CloudShell will restart inside your private subnet. CloudShell sessions time out after 30 minutes of inactivity. The following steps for manual installation are for learning purposes. You can choose to use the automation provided in this post for either Db2 Data Server client or Db2 runtime client.

Download and install the IBM Data Server Driver thin client

The IBM Data Server Driver provides the lightweight CLI tools you need—without installing full Db2. At time of writing, version 11.5.9 is current for HAQM RDS for Db2. Complete the following steps:

Download the Linux x64 tarball from IBM Fix Central:
1. Choose Information Management on the Product Group dropdown menu.
2. Choose IBM Server Client Data Packages on the dropdown menu.
3. Choose 11.5.* on the installed version dropdown menu.
4. Select Linux 64 bit, x86_64 on the platform dropdown menu.
5. Choose Continue and choose Browse for fixes.
6. Select IBM Data Server Driver Package and download file v11.5.9_linuxx64_dsdriver.tar.gz.Note: You can choose the later version of the driver if available.
Copy the file into an S3 bucket your AWS account can read (if you can’t use CloudShell to download directly):
```
aws s3 cp s3://<your-bucket>/v11.5.9_linuxx64_dsdriver.tar.gz .
```
Or, if you have a direct IBM download link, use the following command:
```
wget "<IBM-FixCentral-URL>"
```

Extract and install the driver:

tar xvfz v11.5.9_linuxx64_dsdriver.tar.gz
cd dsdriver
# Fix shebang for bash
sed -i '1s|#! */bin/ksh -p|#! /bin/bash -p|' installDSDriver
./installDSDriver

Enable the driver environment on every new shell:

echo "source $HOME/dsdriver/db2profile" >> ~/.bashrc
source $HOME/dsdriver/db2profile

Configure a TCP/IP (plain) connection

Use the following code to obtain the Db2 endpoint and port of the RDS for Db2 instance. Provide the name of your RDS for DB2 instance and your AWS Region and profile values:

DB_INSTANCE_IDENTIFIER=<my-db2-instance-identifer>
aws rds describe-db-instances \
--db-instance-identifier $DB_INSTANCE_IDENTIFIER\
--query "DBInstances[0].Endpoint.[Address,Port]" \
--region us-east-1 --profile default \
--output text

Use db2cli to register your RDS for Db2 endpoint.

We first register RDSADMIN database through which you can run administrative RDS for Db2 stored procedures to manage your Db2 databases. Provide the endpoint URL of HAQM RDS for DB2 and the TCPIP port of HAQM RDS for Db2.

db2cli writecfg add \
  -dsn RDSAMIN \
  -database RDSADMIN \
  -host <your-db2-endpoint> \
  -port <your-db2-tcpip-port> \
  -parameter "Authentication=SERVER_ENCRYPT"

Similarly, use the following command to register user’s databases.

db2cli writecfg add \
  -dsn TEST_TCP \
  -database <DBNAME> \
  -host <your-db2-endpoint> \
  -port <your-db2-tcpip-port> \
  -parameter "Authentication=SERVER_ENCRYPT"

In the preceding code, the Authentication=SERVER_ENCRYPT enforces server-side encryption without SSL.

The Db2 thin driver comes with clpplus utility that you can use to connect with the remote RDS for Db2 database. You can learn more on clpplus here. Test the configuration with the following command:

clpplus -nw MasterUserName/MasterUserPassword@TEST_TCP

You should see the following output:

CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.
Database Connection Information :
Hostname = your-db2-endpoint.rds.amazonaws.com
Database server = DB2/LINUXX8664  SQLxxxxxx
SQL authorization ID = <Master user> 
Local database alias = <DSN Name> 
Port = <Port Number>
SQL>

Configure an SSL connection

Complete the following steps to configure an SSL connection:

Download the HAQM RDS SSL certificate chain for your Region. You can use the following command to download the certificate chain direct in CloudShell.

$ cd dsdriver
$ REGION=us-east-1 # Replace region name with your region name  
$ curl -sS "http://truststore.pki.rds.amazonaws.com/$REGION/$REGION-bundle.pem" -o $REGION-bundle.pem

Obtain the Db2 endpoint of the RDS for Db2 instance. Provide the name of your RDS for Db2 instance and your Region and profile values:

DB_INSTANCE_IDENTIFIER=<my-db2-instance-identifer>
aws rds describe-db-instances \
 --db-instance-identifier "$DB_INSTANCE_IDENTIFIER" \
--query "DBInstances[0].Endpoint.Address" \
--region us-east-1 --profile default \
--output text

Register the SSL DSN. Provide the name, endpoint URL, and SSL port of your HAQM RDS for Db2 database, and the name of the certificate chain.
```
db2cli writecfg add \
-dsn TEST_SSL \
-database <DBNAME> \
-host <your-db2-endpoint> \
-port <your-db2-ssl-port> \
-parameter "SSLServerCertificate=$PWD/$REGION-bundle.pem;SecurityTransportMode=SSL;TLSVersion=TLSV12"
```
SecurityTransportMode=SSL enforces server-side encryption with SSL. SSLServerCertificate=$PWD/$REGION-bundle.pem uses the certificate chain of your RDS for Db2 instance. The parameter TLSVersion is set to TLSV12.
Verify your db2dsdriver.cfg to check the DSN alias name as you will use that alias name to connect to RDS for Db2 using clpplus. You will see two <database> entries—one for TCP/IP and one for SSL with a certificate.
```
cat cfg/db2dsdriver.cfg
```

You can also run db2cli validate command to check validity of the db2dsdriver.cfg.

db2cli validate

To test the SSL DSN, use the following code:

clpplus -nw MasterUserName/MasterUserPassword@DSNAliasName

The output should confirm SSL port number and the hostname:

CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.
Database Connection Information :
---------------------------------
Hostname = your-db2-endpoint.rds.amazonaws.com 
Database server = DB2/LINUXX8664  SQL110590 
SQL authorization ID = <Master user> 
Local database alias = <DSN Name> 
Port = <Port Number>
SQL>

Limitations

CloudShell is a quick way to connect to HAQM RDS for Db2; it’s not a replacement for a regular Db2 client that you might need either using your applications servers or an HAQM EC2 machine to use a thick Db2 client. Additionally, a CloudShell session using a VPC is limited to 30 minutes of inactivity time, then it resets the CloudShell session. This means you will need to install the Db2 client again.

Next steps

As a next step, you can build scripts for installing clients automatically. The following code shows an example:

curl -sL http://bit.ly/db2client | bash

Note: The above short URL points to – http://aws-blogs-artifacts-public.s3.us-east-1.amazonaws.com/artifacts/DBBLOG-4900/installDSDriver.sh

The above command installs Db2 thin client in the CloudShell with clpplus command. It downloads script create-ds-entry.sh that you can run to register your databases to connect using clpplus.

source create-ds-entry.sh

If you want to install Db2 runtime client instead of Data Server thin client, you can use the following command.

curl -sL http://bit.ly/db2thickclient | bash

Note: The above short URL points to – http://aws-blogs-artifacts-public.s3.us-east-1.amazonaws.com/artifacts/DBBLOG-4900/installRTDriver.sh

The above command installs Db2 runtime client in the CloudShell with Db2 Command Line Processor (CLP). It downloads script create-rt-entry.sh that you can source to run to register your databases to connect using db2 command.

source create-rt-entry.sh

If you run into the 30-minute inactivity timeout, you can run the script again to install and register your RDS for Db2 database to connect again.

Conclusion

In this post, we demonstrated how, with just a few commands, you can run CLPPlus or Db2 CLP against HAQM RDS for Db2 entirely inside CloudShell. No EC2 instance or local installs are required—just a clean, serverless-style workflow. Try out this solution for your own use case, and share your thoughts in the comments.

AWS Database Blog