AWS Lambda Interface for Db2 for IBM i

This repository contains the infrastructure required to interface with Db2 for IBM i databases. Demo code for example SQL queries is included.

Overview

This architecture provides a generic solution for Db2 interfacing, without any application-specific triggers. It allows us to push Docker images to a repository hosted on ECR and deploy those images to a Lambda function. Lambda can then execute specified queries on a target Db2. Note that the Lambda invocation trigger, the VPC, and Direct Connect are not included in the AWS resources launched in this blog post, but may be necessary depending on your use case. Prerequisites are described the walkthrough.

Components

bin: Helper scripts, see next section for details.
functions: Lambda function code that contains logic for interfacing with Db2.
images: Base Docker images that need to built and uploaded for functions to use.
infrastructure: Additional CloudFormation templates.
res: Additional supporting resources.

Scripts

A set of helper scripts are available in the bin folder to perform deployment operations. The AWS CLI should be properly configured in the terminal before you execute scripts. Environment variables are required; see bin/example.env for details. The scripts are as follows:

build-functions.py: Package the Lambda function Docker images defined in the functions folder and uploads them to the ECR repository. Uses SAM and Docker under the hood. Writes the translated plain-vanilla CloudFormation template out to .aws-sam/build/functions.yaml.
cleanup.bash: Bash wrapper for cleanup script to provide environment variables.
cleanup.py: Cleanup script to delete ECR images and infrastructure deployed from deploy.bash
common: Functions shared between all scripts.
config-db2.py: Prompts for database connection information and then writes it to secrets manager.
deploy-base.py: Deploys the base infrastructure (e.g. ECR repository, DB2 configuration secret).
deploy-functions.py: Loads the template produced by build-functions.py and deploys it.
deploy.bash: Runs all of the deploy scripts in sequence. Takes one parameter, a path to an environment file. If none is provided, .env is used as a default.

Deployment Instructions

The deployment instructions provided here will launch an ECR repository, a Secrets Manager secret, a Lambda function built off images uploaded to the aforementioned ECR repo, and associated ENIs for VPC access. The deployment is automated using bash and Python scripts.

Prerequisites

Clone code repository locally
An AWS account
AWS CLI access
Python 3 installed
Pip installed
Docker installed
AWS SAM
PyYAML
AWS Sam Translator
IBM iAccess ODBC driver RPM package downloaded
VPC connectivity to Db2
At least 2 private subnets for HA
Basic Unix CLI knowledge
A bash shell environment for script execution
Service account credentials for Db2

Provision IBM iAccess Driver

Retrieve a copy of the IBM iAccess ODBC driver RPM package. You may have to contact your Db2 DBA for access.
Copy your downloaded IBM iAccess driver to /aws-lambda-interface-for-db2-i/images/pyodbc-db2/lib/. Create the lib folder if it does not exist.

Set Environment Variables

Navigate to /aws-lambda-interface-for-db2-i/bin/ and copy the example.env file to a new file named .env.

Open the new .env file with an editor. Configure the environment variables found in the file as appropriate:

Variable Name	Description
`ENVIRONMENT_NAME`	An identifier used to classify an environment, such as test, dev, uat, prod.
`PROJECT_NAME`	The name of the project. This will control naming convention across stacks and resources. This should be a lowercase alphanumeric value to pass ECR repository name validation.
`SUBNET_IDS`	Comma-separated list of subnet IDs within the above VPC that are routable to the Db2 instance. You should include at least 2 from separate AZs.
`VPC_ID`	VPC ID for the network that is routable to the Db2 instance.

Run Deployment Script

Ensure that Docker is running locally.
Ensure that your AWS environment is configured correctly. You can check if you are configured with the correct environment by running the following command: aws sts get-caller-identity --query 'Account' --output text. The command will output an account number – make sure that this account number is consistent with the account you wish to deploy to. For more information, see how to configure AWS CLI access.
Execute /aws-lambda-interface-for-db2-i/bin/deploy.bash.

Configure Secrets Manager Secret Details

The deployment script will ask you for secret values. It will display the secret key, the current value (with password masked) and allow you to enter a new value. You can continue to use the old value by simply proceeding with the Enter key.

You must populate the keys with an appropriate value:

Secret Name	Description
`Schema`	Database schema name
`Hostname`	Hostname (or IP address) of database
`Database`	Database name
`Password`	Service account password
`Username`	Service account username

The project will now build and deploy. This will take some time. You can check live deployment status by viewing the stack status and Events tab in the CloudFormation console. Allow up to 30 minutes for deployment.

Modifications for Further Use

Now that you have deployed the infrastructure, you can begin to make meaningful code, logic, and other architectural modifications to adapt the solution to real-world use cases.

Lambda Invocation Modifications

While this blog deploys the necessary infrastructure for basic interfacing, it excludes any Lambda invocation mechanisms, as this will vary based on your use case.

To make modifications to invocation infrastructure, make changes in the /aws-lambda-interface-for-db2-i/infrastructure/ folder. You can add CloudFormation templates for any additional infrastructure here, or modify the existing stack. If you add new files, ensure that you modify the existing deployment scripts accordingly.
Alternatively, you can manually create resources in the AWS console, and then manually link them in the console as well. Instructions vary by trigger type.

Code and Logic Modifications

Code for the transaction logic can be found in /aws-lambda-interface-for-db2-i/functions/db2-injector/src/:
- main.py – Contains Lambda handler to perform custom logic with Lambda invocation. In the provided example file, we start the connection to Db2 and execute two dummy queries.
- base.py – Contains helper methods to construct and execute queries

You can modify and extend to the existing tables and queries in these files. As seen in the base.py example, you can create a dictionary of table names and a list of SQL queries. The _construct_methods() method in base.py will formulate methods for the queries so that any query defined in the _query_methods list is callable by its name attribute.

The _table_names dictionary contains the names of Db2 tables to target for your queries. This is structured like so:
```
_table_names = {
    "table_name_1": "EXAMPLE1",
    "table_name_2": "EXAMPLE2",
}
```
- The keys for the dictionary refer to the Python variable name for the table, whereas the value is the actual table name in Db2.

The _query_methods list contains a list of dictionaries for each query with additional metadata, and is formulated like so:

_query_methods = [
    {
        "name": "_example_select_query",
        "base": "_execute_transaction",
        "table": "table_name_1",
        "query": """
        SELECT FROM {0}.{1} WHERE ATTRIBUTE_1 = ? AND ATTRIBUTE_2 = ?
        """,
    },
    {
        "name": "_example_delete_query",
        "base": "_execute_transaction",
        "table": "table_name_2",
        "query": """
        DELETE FROM {0}.{1} WHERE ATTRIBUTE_1 = ? AND ATTRIBUTE_2 = ?
        """,
    },
]

As shown, each query dictionary in the list contains a name, base, table, and query attribute. The attributes refer to the following:

Attribute Name	Description
`name`	The name of the invocation method once the query has been constructed.
`base`	The base function to use for the query. Typically, transactions are invoked using the `_execute_transaction()` function, but you can write custom functions to add functionality to your transaction responses, such as formatting strings returned in `SELECT` queries.
`table`	The table name to perform your query on. This will be one of the keys in the `_table_names` dictionary.
`query`	The query string to use. The string will be replaced with the schema and table names, as well as any additional parameters you provide as arguments during method invocation. In the format above, `{0}` refers to the schema, `{1}` refers to the table, and `?` refers to each parameter given, in relative order.

You can instantiate your query object by passing the global connection and schema variables to the object initializer. You can then invoke query execution directly by calling the name attribute of each query listed in _query_methods. Lines 41 and 42 of main.py provide an example. Print the results of the query to check them later.
Finally, to deploy your modifications, execute the deployment script /aws-lambda-interface-for-db2-i/bin/deploy.bash again. You can skip secret value entry by proceeding with the Enter key.
All subsequent modifications to code in /aws-lambda-interface-for-db2-i/functions/db2-injector/src/ require re-running the deployment script. The script will only affect updated components. Ensure

Testing Connectivity

Once modified and deployed, we can finally test our connectivity by simply invoking our Lambda function.

Test Invocation

Ensure that during testing, your code is only set to run queries that will not perform critical operations on your database. For example, use a read-only operation such as SELECT. You can print the response of your query to check for connectivity.
If you do not have any additional trigger infrastructure deployed, you can simply invoke your function manually by navigating to the Lambda console and sending any well-formed test event, provided that your Lambda handler does not currently have custom logic to handle event payloads. Click the Test button to send the payload and invoke your Lambda function.

Checking Connectivity

Check connectivity by viewing the CloudWatch logs for your Lambda function after invocation. If you cannot find the log group, you can open the logs from the Lambda console under the Monitor tab. Click on the View logs in CloudWatch button, and then select the most recent log stream.
Once you are viewing the correct log stream, check the logs to see if the printed values match the expected results from your custom queries. Should the connection or query fail, you will see an exception message.

Troubleshooting

The following table provides common issues and solutions.

Issue	Tips
No logs in CloudWatch	Wait a couple minutes and refresh - CloudWatch logs can lag slightly behind.
	Check to see if you are in the correct log group.
	Check Lambda CloudWatch metrics to see if the function has been invoked.
Logs show `SQLDriverConnect` exceptions	Verify that your Secrets Manager credentials and configuration is correct
	Test connectivity to Db2 with an EC2 instance in the same environment. Install a database client such as DBeaver, and then try to connect from it.
	Verify that your target VPC and subnets have correct network configurations to make connections out to your Db2 database.
	Check with your DBA to see if service credentials are locked or expired. Multiple connection attempts with incorrect credentials could cause a credential freeze on Db2.
	Look up common TCP/IP communication errors.
Logs show `SQLExecDirectW` exceptions	Your query may not be well formed - double check to ensure that your query syntax is correct.
	Check that the schema, table, and any parameters passed are correct.
	Look up error code in SQLSTATE Messages.

Cleaning Up

To avoid incurring future charges, delete Docker images and all resources created by this stack if not in use.

Automated Deletion

You can automate stack deletion by running the provided cleanup script:

Execute the /aws-lambda-interface-for-db2-i/bin/cleanup.bash script. Note that this will remove everything previously deployed, including the Lambda function, IAM role, secret, Docker images, and ECR repository. The stack deletion can take upwards of 45 minutes to complete due to the ENIs created. This will not modify your code changes.

Manual Deletion

You can also manually delete the stacks:

Delete images from the your ECR repository
1. Navigate to the ECR console.
2. Select all images hosted in your repository created as part of this stack
3. Select Delete
Delete the CloudFormation stacks
1. Navigate to the CloudFormation console.
2. Delete the stacks titled [project]-[environment]-functions and [project]-[environment]-base. The order in which you delete the stacks does not matter.
3. The [project]-[environment]-functions stack deletion can take upwards of 45 minutes to complete due to the ENIs created.

Security

See CONTRIBUTING for more information.

License

This project is licensed under the Apache-2.0 License. Db2 and IBM are trademarks of International Business Machines Corporation.

ejschaefer/aws-lambda-interface-for-db2-i