Enable DB2 log archiving directly to Amazon S3 in an IBM Db2 database - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsBest practicesEpicsRelated resources

Enable DB2 log archiving directly to Amazon S3 in an IBM Db2 database

Ambarish Satarkar, Amazon Web Services

Summary

This pattern describes how to use Amazon Simple Storage Service (Amazon S3) as catalog storage for archive logs that are generated by IBM Db2, without using a staging area.

You can specify DB2REMOTE Amazon S3 storage for the logarchmeth1 and logarchmeth2 log archive method configuration parameters. You can use the logarchmeth1 parameter to specify the primary destination for logs that are archived from the current log path. With this capability, you can archive and retrieve transaction logs to and from Amazon S3 directly, without using a staging area.

Amazon S3 stores the data that’s uploaded to it across at least three devices in a single AWS Region. Millions of customers of all sizes and industries use Amazon S3 for storing enterprise backups given its high availability, flexible storage options, lifecycle policies, and security.

Prerequisites and limitations

Prerequisites 

  • An active AWS account.

  • IBM Db2 database running on an Amazon Elastic Compute Cloud (Amazon EC2) instance.

  • AWS Command Line Interface (AWS CLI) installed

  • libcurl and libxml2 installed on the Db2 EC2 instance.

Limitations 

  • Only Db2 11.5.7 or later allows log archiving directly to Amazon S3 storage.

  • Some AWS services aren’t available in all AWS Regions. For Region availability, see AWS Services by Region. For specific endpoints, see Service endpoints and quotas, and choose the link for the service.

  • In all configurations, the following limitations exist for Amazon S3:

    • AWS Key Management Service (AWS KMS) is not supported.

    • AWS role-based (AWS Identity and Access Management (IAM)) or token-based (AWS Security Token Service (AWS STS)) credentials are not supported.

Product versions

  • AWS CLI version 2 or later

  • IBM Db2 11.5.7 or later

  • Linux SUSE Linux Enterprise Server (SLES) 11 or later

  • Red Hat Enterprise Linux (RHEL) 6 or later

  • Windows Server 2008 R2, 2012 (R2), 2016, or 2019

Architecture

The following diagram shows the components and workflow for this pattern.

Workflow to use Amazon S3 for catalog storage for archive logs generated by Db2.

The architecture on the AWS Cloud includes the following:

  • Virtual private cloud (VPC) – A logically isolated section of the AWS Cloud where you launch resources.

  • Availability Zone – Provides high availability by running the Db2 LUW (Linux, Unix, Windows) workload in an isolated data center within the AWS Region.

  • Public subnet – Provides RDP (Remote Desktop Protocol) access for administrators and internet connectivity through a NAT gateway.

  • Private subnet – Hosts the Db2 LUW database. The Db2 LUW instance is configured with the LOGARCHMETH1 parameter. The parameter writes database log archive files directly to an Amazon S3 path through the gateway endpoint.

The following AWS services provide support:

  • Amazon S3 – Serves as the durable, scalable storage location for Db2 log archive files.

  • Amazon Elastic File System (Amazon EFS) – Provides a shared, fully managed file system that Db2 can use for database backups and staging. Db2 can also use Amazon EFS as a mount point for log files before they are archived to Amazon S3.

  • Amazon CloudWatch – Collects and monitors metrics, logs, and events from Db2 and the underlying EC2 instances. You can use CloudWatch to create alarms, dashboards, and automated responses to performance or availability issues.

Automation and scale

  • This pattern provides a fully automated solution to store Db2 log archive backup.

  • You can use the same Amazon S3 bucket to enable log archive of multiple Db2 databases.

Tools

AWS services

Other tools

  • libcurl is a free client-side URL transfer library.

  • libxml2 is a free XML C parser and toolkit.

Best practices

Epics

TaskDescriptionSkills required

Set up the AWS CLI.

To download and install the AWS CLI, use the following commands:

i) curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
ii) unzip awscliv2.zip
iii) sudo ./aws/install
AWS systems administrator, AWS administrator

Configure the AWS CLI.

To configure the AWS CLI, use the following commands:

$ aws configure
AWS Access Key ID [None]:*******************************
AWS Secret Access Key [None]: ***************************
Default region name [None]: < aws region >
Default output format [None]: text
AWS systems administrator, AWS administrator

Create IAM user.

To create an IAM user to use later for the Db2 database connection with Amazon S3, use the following command:

aws iam create-user --user-name <unique username>

Following is an example of the command:

aws iam create-user --user-name db_backup_user

Warning

This scenario requires IAM users with programmatic access and long-term credentials, which presents a security risk. To mitigate this risk, we recommend that you provide these users with only the permissions they require to perform the task and that you remove these users when they are no longer needed. Access keys can be updated if necessary. For more information, see AWS security credentials and Manage access keys for IAM users in the IAM documentation.

AWS systems administrator

Create Amazon S3 bucket.

To create an Amazon S3 bucket for storing the database backup, use the following command:

aws s3api create-bucket --bucket <unique bucket name> --region <aws region>

Following is an example command:

aws s3api create-bucket --bucket myfirstbucket --region af-south-1

AWS systems administrator

Authorize the IAM user.

To authorize the newly created IAM user to have Amazon S3 permissions, use the following steps:

  1. To generate a new IAM policy file db2bucket.json for the Amazon S3 bucket, use the following command:

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "ListObjectsInBucket",
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": [
                "arn:aws:s3:::myfirstbucket"
            ]
        },
        {
            "Sid": "AllObjectActions",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::myfirstbucket/*"
            ]
        }
    ]
}

  2. Create an IAM policy based on the db2bucket.json file. The JSON output shows the Amazon Resource Name (ARN) for the policy: "Arn": "arn:aws:iam::111122223333:policy/db2s3policy".

    aws iam create-policy \
      --policy-name db2s3policy \
      --policy-document file://db2bucket.json

  3. Attach the IAM policy to the IAM user with the policy ARN.

    aws iam attach-user-policy --user-name db_backup_user --policy-arn "arn:aws:iam::111122223333:policy/db2s3policy"
AWS systems administrator, AWS administrator

Create access key.

To generate an access key to programmatically access Amazon S3 from the DB2 instance, use the following command:

aws iam create-access-key --user-name <username>

Following is an example of the command:

aws iam create-access-key --user-name db_backup_user

Warning

This scenario requires IAM users with programmatic access and long-term credentials, which presents a security risk. To mitigate this risk, we recommend that you provide these users with only the permissions they require to perform the task and that you remove these users when they are no longer needed. Access keys can be updated if necessary. For more information, see AWS security credentials and Manage access keys for IAM users in the IAM documentation.

AWS systems administrator

Create a PKCS keystore.

To create a PKCS keystore to store the key and create a secret access key to transfer the data to Amazon S3, use the following command: 

gsk8capicmd_64 -keydb -create -db "/db2/db2<sid>/.keystore/db6-s3.p12" -pw "<password>" -type pkcs12 -stash
AWS systems administrator

Configure DB2 to use the keystore.

To configure DB2 to use the keystore with the keystore_location and keystore_type parameters, use the following commands:

db2 "update dbm cfg using keystore_location /db2/db2<sid>/.keystore/db6-s3.p12 keystore_type pkcs12"
AWS systems administrator

Create a DB2 storage access alias.

A storage access alias specifies the Amazon S3 bucket to use. It also provides the connection details such as the username and password that are stored in the local keystore in an encrypted format. For more information, see CATALOG STORAGE ACCESS command in the IBM Db2 documentation.

To create a storage access alias, use the following syntax:

db2 "catalog storage access alias <alias_name> vendor S3 server <S3 endpoint> user '<access_key>' password '<secret_access_key>' container '<bucket_name>'"

Following is an example:

db2 "catalog storage access alias DB2BKPS3 vendor S3 server s3.us-west-2.amazonaws.com user '*******************' password '*********************' container 'myfirstbucket'"
AWS systems administrator
TaskDescriptionSkills required

Update the LOGARCHMETH1 location.

To use the storage access alias that you defined earlier, update the LOGARCHMETH1 database parameters, use the following command:

db2 update db cfg for <DBNAME> using LOGARCHMETH1 'DB2REMOTE://<storage_alias_name>//<sub folder>'

To separate the logs from other files, specify a subdirectory (that is, the Amazon S3 bucket prefix) TESTDB_LOGS in which to save the logs within the S3 bucket.

Following is an example:

db2 update db cfg for ABC using LOGARCHMETH1 'DB2REMOTE://DB2BKPS3//TESTDB_LOGS/'

You should see the following message: DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

AWS systems administrator

Restart DB2.

Restart the DB2 instance after reconfiguring it for log archiving.

However, if LOGARCHMETH1 was previously set to any file system location, then restart is not required.

AWS administrator, AWS systems administrator
TaskDescriptionSkills required

Check the archive log in Amazon S3.

At this point, your database is completely configured to archive the transaction logs directly to the Amazon S3 storage. To confirm the configuration, start executing transactional activities on the database to start consuming (and archiving) the log space. Then, check the archive logs in Amazon S3.

AWS administrator, AWS systems administrator

Check archive log configuration in db2diag.log.

After you check the archive log in Amazon S3, look for the following message in the DB2 diagnostic log db2diag.log:

MESSAGE : ADM1846I  Completed archive for log file "S0000079.LOG" to Completed archive for log file S0000080.LOG to DB2REMOTE://<AWS S3 Bucket Name>/<SID>/log1/db2<sid>/<SID>/NODE0000/LOGSTREAM0000/C0000001/ from /db2/<SID>/log_dir/NODE0000/LOGSTREAM0000/. MESSAGE : ADM1846I  Completed archive for log file "S0000080.LOG" to Completed archive for log file S0000081.LOG to DB2REMOTE://<AWS S3 Bucket Name> /<SID>/log1/db2<sid>/<SID>/NODE0000/LOGSTREAM0000/C0000001/ from /db2/<SID>/log_dir/NODE0000/LOGSTREAM0000/.

This message confirms that the closed DB2 transaction log files are being archived to the (remote) Amazon S3 storage.

AWS systems administrator

Related resources

AWS service documentation

IBM resources