AWS AmazonRDS documentation change
Summary
Added documentation for generating db2exfmt reports to analyze query performance issues
Security assessment
This change documents a performance analysis tool (db2exfmt) for query debugging. There is no evidence of security vulnerabilities being addressed, nor does it introduce new security features. The focus is on performance optimization.
Diff
diff --git a/AmazonRDS/latest/UserGuide/db2-managing-databases.md b/AmazonRDS/latest/UserGuide/db2-managing-databases.md index cf29baa12..5a3ad66cc 100644 --- a//AmazonRDS/latest/UserGuide/db2-managing-databases.md +++ b//AmazonRDS/latest/UserGuide/db2-managing-databases.md @@ -7 +7 @@ -Creating a databaseConfiguring settings for a databaseModifying database parametersConfiguring log retentionListing log informationUsing fine-grained access control (FGAC)Deactivating a databaseActivating a databaseReactivating a databaseDropping a databaseBacking up a databaseCopying archive logs to Amazon S3Restoring a databaseListing databasesCollecting information about databasesForcing applications off of databasesGenerating performance reports +Creating a databaseConfiguring settings for a databaseModifying database parametersConfiguring log retentionListing log informationUsing fine-grained access control (FGAC)Deactivating a databaseActivating a databaseReactivating a databaseDropping a databaseBacking up a databaseCopying archive logs to Amazon S3Restoring a databaseListing databasesCollecting information about databasesForcing applications off of databasesGenerating performance reportsGenerating db2exfmt reports @@ -50,0 +51,2 @@ You can also perform common management tasks such as monitoring, maintenance, an + * Generating db2exfmt reports + @@ -543,0 +546,107 @@ The following example shows that the script file is located in the `perf` direct +## Generating db2exfmt reports + +`db2exfmt` is a query performance analyzer tool that you can use to debug query performance issues in an RDS for Db2 database. + +###### Prerequisites + +Before using `db2exfmt`, ensure the following requirements are met: + +Database access + + + * Run `db2exfmt` only against user databases. + + * Cannot run `db2exfmt` against RDSADMIN. It will generate an authorization error (SQL0551N). + + + + +Client setup + + + * The target RDS for Db2 database must be cataloged and accessible from the client machine where you run the `db2exfmt` command. + + * IBM client installations do not package the `db2exfmt` binary. A full Db2 installation is required, such as Db2 Community Edition. + + + + +Confirm that the `db2exfmt` binary is available on the client machine: + + + ls -l DB2_INSTALL_PATH/bin/db2exfmt + +###### To run db2exfmt + + 1. Create a shell script (for example, `db2exfmt.sh`) to run the `db2exfmt` tool. + + #!/bin/bash + MYUSER=master_username # Master user name + MYPASS=master_password # Master user password + + DBNAME=database_name # Database name + QRY=q1.sql # Filename that contains the SQL statement + OUTPUT_FL=my_db2exfmt.out # Filename where db2exfmt output will be saved + + echo "select * from MASTERUSER.NAMES ;" > $QRY + + db2 connect to $DBNAME user $MYUSER using $MYPASS + db2 set current query optimization = 5 + db2 set current explain mode explain + db2 -tvf $QRY + db2 set current explain mode no + db2exfmt -d $DBNAME -g TIC -w -1 -n % -s % -# 0 -u $MYUSER $MYPASS -o ${OUTPUT_FL} + +This script does the following: + + * Connects to the RDS for Db2 database + + * Sets the query optimization level + + * Enables explain mode to capture query execution plans + + * Executes the SQL query + + * Runs `db2exfmt` to generate a formatted analysis report + +For a complete list and explanation of `db2exfmt` command parameters, see [db2exfmt - Explain table format command](https://www.ibm.com/docs/en/db2/11.5.x?topic=commands-db2exfmt-explain-table-format) in the IBM Db2 documentation. + + 2. Execute the script: + + $ ./db2exfmt.sh + + + + +Sample output: + + + Database Connection Information + + Database server = DB2/LINUXX8664 11.5.9.0 + SQL authorization ID = MASTERUS... + Local database alias = UHPROD01 + + DB20000I The SQL command completed successfully. + DB20000I The SQL command completed successfully. + select * from MASTERUSER.NAMES + SQL0217W The statement was not executed as only Explain information requests + are being processed. SQLSTATE=01604 + + DB20000I The SQL command completed successfully. + DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 + Licensed Material - Program Property of IBM + IBM DATABASE 2 Explain Table Format Tool + + Connecting to the Database as user masteruser. + Connect to Database Successful. + Using SYSTOOLS schema for Explain tables. + Output is in my_db2exfmt.out. + Executing Connect Reset -- Connect Reset was Successful. + +If `SYSTOOLS.EXPLAIN*` tables are not found on the target database, create the explain tables by running the following commands: + + + db2 -v CONNECT TO database_name user master_username using master_password + db2 -v "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))" +