Connecting to IBM DB2 for z/OS Databases with the AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from IBM Db2 for z/OS to the following targets.
Amazon RDS for MySQL
Amazon Aurora MySQL-Compatible Edition
Amazon RDS for PostgreSQL
Amazon Aurora PostgreSQL-Compatible Edition
Prerequisites for Db2 for z/OS as a source database
The IBM Db2 for z/OS version 12 function level 100 database version doesn't support most new capabilities of IBM Db2 for z/OS version 12. This database version provides support for fallback to Db2 version 11 and data sharing with Db2 version 11. To avoid the conversion of unsupported features of Db2 version 11, we recommend that you use an IBM Db2 for z/OS database function level 500 or higher as a source for AWS SCT.
You can use the following code example to check the version of your source IBM Db2 for z/OS database.
SELECT GETVARIABLE('SYSIBM.VERSION') as version FROM SYSIBM.SYSDUMMY1;
Make sure that this code returns version DSN12015
or higher.
You can use the following code example to check the value of the APPLICATION COMPATIBILITY
special register in your source IBM Db2 for z/OS database.
SELECT CURRENT APPLICATION COMPATIBILITY as version FROM SYSIBM.SYSDUMMY1;
Make sure that this code returns version V12R1M500
or higher.
Privileges for Db2 for z/OS as a source database
The privileges needed to connect to a Db2 for z/OS database and read system catalogs and tables are as follows:
SELECT ON SYSIBM.LOCATIONS
SELECT ON SYSIBM.SYSCHECKS
SELECT ON SYSIBM.SYSCOLUMNS
SELECT ON SYSIBM.SYSDATABASE
SELECT ON SYSIBM.SYSDATATYPES
SELECT ON SYSIBM.SYSDUMMY1
SELECT ON SYSIBM.SYSFOREIGNKEYS
SELECT ON SYSIBM.SYSINDEXES
SELECT ON SYSIBM.SYSKEYCOLUSE
SELECT ON SYSIBM.SYSKEYS
SELECT ON SYSIBM.SYSKEYTARGETS
SELECT ON SYSIBM.SYSJAROBJECTS
SELECT ON SYSIBM.SYSPACKAGE
SELECT ON SYSIBM.SYSPARMS
SELECT ON SYSIBM.SYSRELS
SELECT ON SYSIBM.SYSROUTINES
SELECT ON SYSIBM.SYSSEQUENCES
SELECT ON SYSIBM.SYSSEQUENCESDEP
SELECT ON SYSIBM.SYSSYNONYMS
SELECT ON SYSIBM.SYSTABCONST
SELECT ON SYSIBM.SYSTABLES
SELECT ON SYSIBM.SYSTABLESPACE
SELECT ON SYSIBM.SYSTRIGGERS
SELECT ON SYSIBM.SYSVARIABLES
SELECT ON SYSIBM.SYSVIEWS
To convert Db2 for z/OS tables to PostgreSQL partitioned tables, gather statistics
on tablespaces and tables in your database using the RUNSTATS
utility
as shown following.
LISTDEF YOURLIST INCLUDE TABLESPACES DATABASE
YOURDB
RUNSTATS TABLESPACE LIST YOURLIST TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL REPORT YES SHRLEVEL REFERENCE
In the preceding example, replace the
placeholder with the name of the source database.YOURDB
Connecting to Db2 for z/OS as a source
Use the following procedure to connect to your Db2 for z/OS source database with AWS SCT.
To connect to an IBM Db2 for z/OS source database
-
In the AWS Schema Conversion Tool, choose Add source.
-
Choose Db2 for z/OS, then choose Next.
The Add source dialog box appears.
-
For Connection name, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
-
Use database credentials from AWS Secrets Manager or enter them manually:
-
To use database credentials from Secrets Manager, use the following instructions:
-
For AWS Secret, choose the name of the secret.
-
Choose Populate to automatically fill in all values in the database connection dialog box from Secrets Manager.
For information about using database credentials from Secrets Manager, see Configuring AWS Secrets Manager in the AWS Schema Conversion Tool.
-
-
To enter the IBM Db2 for z/OS source database connection information manually, use the following instructions:
Parameter Action Server name Enter the Domain Name System (DNS) name or IP address of your source database server.
Server port Enter the port used to connect to your source database server.
Location Enter the unique name of the Db2 location you want to access.
User name and Password Enter the database credentials to connect to your source database server.
AWS SCT uses the password to connect to your source database only when you choose to connect to your database in a project. To guard against exposing the password for your source database, AWS SCT doesn't store the password by default. If you close your AWS SCT project and reopen it, you are prompted for the password to connect to your source database as needed.
Use SSL Choose this option if you want to use Secure Sockets Layer (SSL) to connect to your database. Provide the following additional information, as applicable, on the SSL tab:
-
Trust store: The location of a trust store containing certificates. For this location to appear here, make sure to add it in Global settings.
Store password AWS SCT creates a secure vault to store SSL certificates and database passwords. By turning this option on, you can store the database password and connect quickly to the database without having to enter the password.
Db2 for z/OS driver path Enter the path to the driver to use to connect to the source database. For more information, see Installing JDBC drivers for AWS Schema Conversion Tool.
If you store the driver path in the global project settings, the driver path doesn't appear on the connection dialog box. For more information, see Storing driver paths in the global settings.
-
-
-
Choose Test Connection to verify that AWS SCT can connect to your source database.
-
Choose Connect to connect to your source database.
Privileges for MySQL as a target database
The privileges required for MySQL as a target are as follows:
CREATE ON *.*
ALTER ON *.*
DROP ON *.*
INDEX ON *.*
REFERENCES ON *.*
SELECT ON *.*
CREATE VIEW ON *.*
SHOW VIEW ON *.*
TRIGGER ON *.*
CREATE ROUTINE ON *.*
ALTER ROUTINE ON *.*
EXECUTE ON *.*
SELECT ON mysql.proc
INSERT, UPDATE ON AWS_DB2ZOS_EXT.*
INSERT, UPDATE, DELETE ON AWS_DB2ZOS_EXT_DATA.*
CREATE TEMPORARY TABLES ON AWS_DB2ZOS_EXT_DATA.*
You can use the following code example to create a database user and grant the privileges.
CREATE USER '
user_name
' IDENTIFIED BY 'your_password
'; GRANT CREATE ON *.* TO 'user_name
'; GRANT ALTER ON *.* TO 'user_name
'; GRANT DROP ON *.* TO 'user_name
'; GRANT INDEX ON *.* TO 'user_name
'; GRANT REFERENCES ON *.* TO 'user_name
'; GRANT SELECT ON *.* TO 'user_name
'; GRANT CREATE VIEW ON *.* TO 'user_name
'; GRANT SHOW VIEW ON *.* TO 'user_name
'; GRANT TRIGGER ON *.* TO 'user_name
'; GRANT CREATE ROUTINE ON *.* TO 'user_name
'; GRANT ALTER ROUTINE ON *.* TO 'user_name
'; GRANT EXECUTE ON *.* TO 'user_name
'; GRANT SELECT ON mysql.proc TO 'user_name
'; GRANT INSERT, UPDATE ON AWS_DB2ZOS_EXT.* TO 'user_name
'; GRANT INSERT, UPDATE, DELETE ON AWS_DB2ZOS_EXT_DATA.* TO 'user_name
'; GRANT CREATE TEMPORARY TABLES ON AWS_DB2ZOS_EXT_DATA.* TO 'user_name
';
In the preceding example, replace user_name
with the name of your user.
Then, replace your_password
with a secure password.
To use Amazon RDS for MySQL as a target, set the log_bin_trust_function_creators
parameter
to true, and the character_set_server
to latin1
. To configure these parameters,
create a new DB parameter group or modify an existing DB parameter group.
To use Aurora MySQL as a target, set the log_bin_trust_function_creators
parameter
to true, and the character_set_server
to latin1
.
Also, set the lower_case_table_names
parameter to true. To configure these
parameters, create a new DB parameter group or modify an existing DB parameter group.
Privileges for PostgreSQL as a target database
To use PostgreSQL as a target, AWS SCT requires the CREATE ON DATABASE
privilege.
Make sure that you grant this privilege for each target PostgreSQL database.
To use Amazon RDS for PostgreSQL as a target, AWS SCT requires the rds_superuser
privilege.
To use the converted public synonyms, change the database default search path to
"$user", public_synonyms, public
.
You can use the following code example to create a database user and grant the privileges.
CREATE ROLE
user_name
LOGIN PASSWORD 'your_password
'; GRANT CREATE ON DATABASEdb_name
TOuser_name
; GRANT rds_superuser TOuser_name
; ALTER DATABASEdb_name
SET SEARCH_PATH = "$user", public_synonyms, public;
In the preceding example, replace user_name
with the name of your user.
Then, replace db_name
with the name of your target database.
Finally, replace your_password
with a secure password.
In PostgreSQL, only the schema owner or a superuser
can drop a schema. The owner can drop a schema
and all objects that this schema includes even if the owner of the schema doesn't own some of its objects.
When you use different users to convert and apply different schemas to your target database,
you can get an error message when AWS SCT can't drop a schema. To avoid this error message,
use the superuser
role.
Db2 for z/OS to PostgreSQL conversion settings
To edit Db2 for z/OS to PostgreSQL conversion settings, choose Settings, and then choose Conversion settings. From the upper list, choose Db2 for z/OS, and then choose Db2 for z/OS – PostgreSQL or Db2 for z/OS – Amazon Aurora (PostgreSQL compatible). AWS SCT displays all available settings for IBM Db2 for z/OS to PostgreSQL conversion.
Db2 for z/OS to PostgreSQL conversion settings in AWS SCT include options for the following:
-
To limit the number of comments with action items in the converted code.
For Add comments in the converted code for the action items of selected severity and higher, choose the severity of action items. AWS SCT adds comments in the converted code for action items of the selected severity and higher.
For example, to minimize the number of comments in your converted code, choose Errors only. To include comments for all action items in your converted code, choose All messages.
-
To generate unique names for constraints in the target database.
In PostgreSQL, all constraint names that you use must be unique. AWS SCT can generate unique names for constraints in the converted code by adding a prefix with the table name to the name of your constraint. To make sure that AWS SCT generates unique names for your constraints, select Generate unique names for constraints.
-
To keep the formatting of column names, expressions, and clauses in DML statements in the converted code.
AWS SCT can keep the layout of column names, expressions, and clauses in DML statements in the similar position and order as in the source code. To do so, select Yes for Keep the formatting of column names, expressions, and clauses in DML statements.
-
To exclude table partitions from the conversion scope.
AWS SCT can skip all partitions of a source table during the conversion. To do so, select Exclude table partitions from the conversion scope.
-
To use automatic partitioning for tables that are partitioned by growth.
For data migration, AWS SCT can automatically partition all tables that are larger than the specified size. To use this option, select Enforce partition of tables larger than, and enter the tables size in gigabytes. Next, enter the number of partitions. AWS SCT considers the direct access storage device (DASD) size of your source database when you turn on this option.
AWS SCT can determine the number of partitions automatically. To do so, select Increase the number of partitions proportionally, and enter the maximum number of partitions.
-
To return dynamic result sets as an array of values of the refcursor data type.
AWS SCT can convert source procedures that return dynamic result sets into procedures which have an array of open refcursors as an additional output parameter. To do so, select Use an array of refcursors to return all dynamic result sets.
-
To specify the standard to use for the conversion of date and time values to string representations.
AWS SCT can convert date and time values to string representations using one of the supported industry formats. To do so, select Use string representations of date values or Use string representations of time values. Next, choose one of the following standards.
International Standards Organization (ISO)
IBM European Standard (EUR)
IBM USA Standard (USA)
Japanese Industrial Standard Christian Era (JIS)