# 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](https://www.ibm.com/docs/en/db2/12.1.0?topic=storage-db2remote-identifiers) Amazon S3 storage for the [logarchmeth1](https://www.ibm.com/docs/en/db2/12.1.0?topic=parameters-logarchmeth1-primary-log-archive-method) and [logarchmeth2](https://www.ibm.com/docs/en/db2/12.1.0?topic=parameters-logarchmeth2-secondary-log-archive-method) 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](https://aws.amazon.com/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](https://curl.se/libcurl/) and [libxml2](https://gitlab.gnome.org/GNOME/libxml2/-/wikis/home) installed on the Db2 EC2 instance. **Limitations ** + Only [Db2 11.5.7](https://www.ibm.com/docs/en/db2/11.5.x?topic=new-1157) 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](https://aws.amazon.com/about-aws/global-infrastructure/regional-product-services/). For specific endpoints, see [Service endpoints and quotas](https://docs.aws.amazon.com/general/latest/gr/aws-service-information.html), 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.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/7a10333e-07be-4144-9913-45c60a2f51ea/images/0437d348-1688-4c3e-9aa5-43535afe08c6.png) 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** + [Amazon CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html) helps you monitor the metrics of your AWS resources and the applications you run on AWS in real time. + [AWS Command Line Interface (AWS CLI)](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html) is an open source tool that helps you interact with AWS services through commands in your command-line shell. + [Amazon Elastic Compute Cloud (Amazon EC2)](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/concepts.html) provides scalable computing capacity in the AWS Cloud. You can launch as many virtual servers as you need and quickly scale them up or down. + [Amazon Elastic File System (Amazon EFS)](https://docs.aws.amazon.com/efs/latest/ug/whatisefs.html) helps you create and configure shared file systems in the AWS Cloud. + [AWS IAM Identity Center](https://docs.aws.amazon.com/singlesignon/latest/userguide/what-is.html) helps you centrally manage single sign-on (SSO) access to all of your AWS accounts and cloud applications. + [Amazon Simple Storage Service (Amazon S3)](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) is a cloud-based object storage service that helps you store, protect, and retrieve any amount of data. + [Amazon Virtual Private Cloud (Amazon VPC)](https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html) helps you launch AWS resources into a virtual network that you’ve defined. This virtual network resembles a traditional network that you’d operate in your own data center, with the benefits of using the scalable infrastructure of AWS. **Other tools** + [libcurl](https://curl.se/libcurl/) is a free client-side URL transfer library. + [libxml2](https://gitlab.gnome.org/GNOME/libxml2/-/wikis/home) is a free XML C parser and toolkit. ## Best practices + Follow the principle of least privilege and grant the minimum permissions required to perform a task. For more information, see [Grant least privilege](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies.html#grant-least-priv) and [Security best practices](https://docs.aws.amazon.com/IAM/latest/UserGuide/best-practices.html) in the IAM documentation. ## Epics ### Configure AWS services | Task | Description | Skills required | | --- | --- | --- | | Set up the AWS CLI. | To [download and install the AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.htmlhttps://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html), 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](https://docs.aws.amazon.com/cli/latest/userguide/getting-started-quickstart.html), 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 `Following is an example of the command:`aws iam create-user --user-name db_backup_user`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](https://docs.aws.amazon.com/IAM/latest/UserGuide/security-creds.html#access-keys-and-secret-access-keys) and [Manage access keys for IAM users](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html#Using_RotateAccessKey) 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 --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:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/enable-db2-logarchive-directly-to-amazon-s3-in-ibm-db2-database.html) | 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 `Following is an example of the command:`aws iam create-access-key --user-name db_backup_user`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](https://docs.aws.amazon.com/IAM/latest/UserGuide/security-creds.html#access-keys-and-secret-access-keys) and [Manage access keys for IAM users](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html#Using_RotateAccessKey) 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/.keystore/db6-s3.p12" -pw "" -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/.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](https://www.ibm.com/docs/en/db2/12.1.0?topic=commands-catalog-storage-access) in the IBM Db2 documentation.To create a storage access alias, use the following syntax:
db2 "catalog storage access alias  vendor S3 server  user '' password '' container ''"
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 | ### Update logarchmeth1 location in DB2 and restart DB2 | Task | Description | Skills 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  using LOGARCHMETH1 'DB2REMOTE:////'
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 | ### Check the archive log path in Amazon S3 and db2diag.log | Task | Description | Skills 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:////log1/db2//NODE0000/LOGSTREAM0000/C0000001/ from /db2//log_dir/NODE0000/LOGSTREAM0000/. MESSAGE : ADM1846I  Completed archive for log file "S0000080.LOG" to Completed archive for log file S0000081.LOG to DB2REMOTE:// //log1/db2//NODE0000/LOGSTREAM0000/C0000001/ from /db2//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** + [AWS security credentials ](https://docs.aws.amazon.com/IAM/latest/UserGuide/security-creds.html#access-keys-and-secret-access-keys)(IAM documentation) + [Grant least privilege](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies.html#grant-least-priv) (IAM documentation) + [Manage access keys for IAM users](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html#Using_RotateAccessKey) (IAM documentation) + [Security best practices in IAM](https://docs.aws.amazon.com/IAM/latest/UserGuide/best-practices.html) (IAM documentation) **IBM resources** + [IBM Db2 Database](https://www.ibm.com/products/db2-database) + [logarchmeth1 - Primary log archive method configuration parameter](https://www.ibm.com/docs/en/db2/12.1.0?topic=parameters-logarchmeth1-primary-log-archive-method) + [logarchmeth2 - Secondary log archive method configuration parameter](https://www.ibm.com/docs/en/db2/12.1.0?topic=parameters-logarchmeth2-secondary-log-archive-method) + [Remote storage](https://www.ibm.com/docs/en/db2/12.1.0?topic=databases-remote-storage)