`.
You can find examples in the table following.
| Oracle statement | MySQL statement |
| --- | --- |
| CREATE PROCEDURE
TEST_ORA_PG.P_WITH_SELECT_VARIABLE_01
(p_state IN NUMBER)
AS
l_dept_id NUMBER := 1;
BEGIN
FOR cur IN
(WITH dept_empl(id, name, surname,
lastname, state, dept_id)
AS
(
SELECT id, name, surname,
lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE state = p_state AND
dept_id = l_dept_id)
SELECT id,state
FROM dept_empl
ORDER BY id) LOOP
NULL;
END LOOP;
| CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_VARIABLE_01(IN par_P_STATE DOUBLE)
BEGIN
DECLARE var_l_dept_id DOUBLE DEFAULT 1;
DECLARE var$id VARCHAR (8000);
DECLARE var$state VARCHAR (8000);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT
ID, STATE
FROM (SELECT
ID, NAME, SURNAME, LASTNAME, STATE, DEPT_ID
FROM TEST_ORA_PG.DEPT_EMPLOYEES
WHERE STATE = par_p_state AND DEPT_ID = var_l_dept_id) AS dept_empl
ORDER BY ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN cur;
read_label:
LOOP
FETCH cur INTO var$id, var$state;
IF done THEN
LEAVE read_label;
END IF;
BEGIN
END;
END LOOP;
CLOSE cur;
END;
|
| CREATE PROCEDURE
TEST_ORA_PG.P_WITH_SELECT_REGULAR_MULT_01
AS
BEGIN
FOR cur IN (
WITH dept_empl AS
(
SELECT id, name, surname,
lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE state = 1),
dept AS
(SELECT id deptid, parent_id,
name deptname
FROM test_ora_pg.department
)
SELECT dept_empl.*,dept.*
FROM dept_empl, dept
WHERE dept_empl.dept_id = dept.deptid
) LOOP
NULL;
END LOOP;
| CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl
`(id, name, surname, lastname, state, dept_id)
AS
(SELECT id, name, surname, lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE state = 1);
CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept
`(deptid, parent_id,deptname)
AS
(SELECT id deptid, parent_id, name deptname
FROM test_ora_pg.department);
CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01()
BEGIN
DECLARE var$ID DOUBLE;
DECLARE var$NAME VARCHAR (30);
DECLARE var$SURNAME VARCHAR (30);
DECLARE var$LASTNAME VARCHAR (30);
DECLARE var$STATE DOUBLE;
DECLARE var$DEPT_ID DOUBLE;
DECLARE var$deptid DOUBLE;
DECLARE var$PARENT_ID DOUBLE;
DECLARE var$deptname VARCHAR (200);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT
dept_empl.*, dept.*
FROM TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl
` AS dept_empl,
TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept
` AS dept
WHERE dept_empl.DEPT_ID = dept.DEPTID;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN cur;
read_label:
LOOP
FETCH cur INTO var$ID, var$NAME, var$SURNAME,
var$LASTNAME, var$STATE, var$DEPT_ID, var$deptid,
var$PARENT_ID, var$deptname;
IF done THEN
LEAVE read_label;
END IF;
BEGIN
END;
END LOOP;
CLOSE cur;
END;
call test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01()
|
| CREATE PROCEDURE
TEST_ORA_PG.P_WITH_SELECT_VAR_CROSS_02(p_state IN NUMBER)
AS
l_dept_id NUMBER := 10;
BEGIN
FOR cur IN (
WITH emp AS
(SELECT id, name, surname,
lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE dept_id > 10
),
active_emp AS
(
SELECT id
FROM emp
WHERE emp.state = p_state
)
SELECT *
FROM active_emp
) LOOP
NULL;
END LOOP;
END;
| CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_VAR_CROSS_01$emp
`(id, name, surname, lastname, state, dept_id)
AS
(SELECT
id, name, surname, lastname,
state, dept_id
FROM TEST_ORA_PG.DEPT_EMPLOYEES
WHERE DEPT_ID > 10);
CREATE PROCEDURE
test_ora_pg.P_WITH_SELECT_VAR_CROSS_02(IN par_P_STATE DOUBLE)
BEGIN
DECLARE var_l_dept_id DOUBLE DEFAULT 10;
DECLARE var$ID DOUBLE;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT *
FROM (SELECT
ID
FROM
TEST_ORA_PG.
`P_WITH_SELECT_VAR_CROSS_01$emp` AS emp
WHERE emp.STATE = par_p_state)
AS active_emp;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN cur;
read_label:
LOOP
FETCH cur INTO var$ID;
IF done THEN
LEAVE read_label;
END IF;
BEGIN
END;
END LOOP;
CLOSE cur;
END;
|
# Migrating from Oracle Database to Amazon RDS for Oracle with AWS Schema Conversion Tool
Some things to consider when migrating Oracle schema and code to Amazon RDS for Oracle:
+ AWS SCT can add directory objects to the object tree. *Directory objects* are logical structures that each represent a physical directory on the server's file system. You can use directory objects with packages such as DBMS\$1LOB, UTL\$1FILE, DBMS\$1FILE\$1TRANSFER, the DATAPUMP utility, and so on.
+ AWS SCT supports converting Oracle tablespaces to an Amazon RDS for Oracle DB instance. Oracle stores data logically in tablespaces and physically in data files associated with the corresponding tablespace. In Oracle, you can create a tablespace with data file names. Amazon RDS supports Oracle Managed Files (OMF) for data files, log files, and control files only. AWS SCT creates the needed data files during conversion.
+ AWS SCT can convert server-level roles and privileges. The Oracle database engine uses role-based security. A role is a collection of privileges that you can grant to or revoke from a user. A predefined role in Amazon RDS, called DBA, normally allows all administrative privileges on an Oracle database engine. The following privileges are not available for the DBA role on an Amazon RDS DB instance using the Oracle engine:
+ Alter database
+ Alter system
+ Create any directory
+ Grant any privilege
+ Grant any role
+ Create external job
You can grant all other privileges to an Amazon RDS for Oracle user role, including advanced filtering and column privileges.
+ AWS SCT supports converting Oracle jobs into jobs that can run on Amazon RDS for Oracle. There are a few limitations to the conversion, including the following:
+ Executable jobs are not supported.
+ Schedule jobs that use the ANYDATA data type as an argument are not supported.
+ Oracle Real Application Clusters (RAC) One Node is an option to the Oracle Database Enterprise Edition that was introduced with Oracle Database 11g Release 2. Amazon RDS for Oracle doesn’t support the RAC feature. For high availability, use Amazon RDS Multi-AZ.
In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica. This functionality provides data redundancy, eliminates I/O freezes, and minimizes latency spikes during system backups.
+ Oracle Spatial provides a SQL schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial data in an Oracle database. Oracle Locator provides capabilities that are typically required to support internet and wireless service-based applications and partner-based GIS solutions. Oracle Locator is a limited subset of Oracle Spatial.
To use Oracle Spatial and Oracle Locator features, add the SPATIAL option or LOCATOR option (mutually exclusive) to the option group of your DB instance.
There are some prerequisites to using Oracle Spatial and Oracle Locator on an Amazon RDS for Oracle DB instance:
+ The instance should use Oracle Enterprise Edition version 12.1.0.2.v6 or higher, or 11.2.0.4.v10 or higher.
+ The instance should be inside a virtual private cloud (VPC).
+ The instance should the DB instance class that can support the Oracle feature. For example, Oracle Spatial is not supported for the db.m1.small, db.t1.micro, db.t2.micro, or db.t2.small DB instance classes. For more information, see [DB instance class support for Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html#Oracle.Concepts.InstanceClasses).
+ The instance must have the Auto Minor Version Upgrade option enabled. Amazon RDS updates your DB instance to the latest Oracle PSU if there are security vulnerabilities with a CVSS score of 9\$1 or other announced security vulnerabilities. For more information, see
[Settings for Oracle DB instances](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ModifyInstance.Oracle.html#USER_ModifyInstance.Oracle.Settings).
+ If your DB instance is version 11.2.0.4.v10 or higher, you must install the XMLDB option. For more information, see
[Oracle XML DB](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.XMLDB.html).
+ You should have an Oracle Spatial license from Oracle. For more information, see [Oracle Spatial and Graph](https://shop.oracle.com/apex/product?p1=OracleSpatialandGraph) in the Oracle documentation.
+ Data Guard is included with Oracle Database Enterprise Edition. For high availability, use Amazon RDS Multi-AZ feature.
In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica. This functionality provides data redundancy, eliminates I/O freezes, and minimizes latency spikes during system backups.
+ AWS SCT supports converting Oracle DBMS\$1SCHEDULER objects when migrating to Amazon RDS for Oracle. The AWS SCT assessment report indicates if a schedule object can be converted. For more information on using schedule objects with Amazon RDS, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html#Appendix.Oracle.CommonDBATasks.ModifyScheduler).
+ For Oracle to Amazon RDS for Oracle conversions, DB Links is supported. A database link is a schema object in one database that enables you to access objects on another database. The other database doesn’t need to be an Oracle database. However, to access non-Oracle databases you must use Oracle Heterogeneous Services.
Once you create a database link, you can use the link in SQL statements to refer to tables, views, and PL/SQL objects in the other database. To use a database link, append `@dblink` to the table, view, or PL/SQL object name. You can query a table or view in the other database with the SELECT statement. For more information about using Oracle database links, see the [Oracle documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12083).
For more information about using database links with Amazon RDS, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Database.html#Appendix.Oracle.CommonDBATasks.DBLinks).
+ The AWS SCT assessment report provides server metrics for the conversion. These metrics about your Oracle instance include the following:
+ Computation and memory capacity of the target DB instance.
+ Unsupported Oracle features such as Real Application Clusters that Amazon RDS doesn't support.
+ Disk read-write load
+ Average total disk throughput
+ Server information such as server name, OS, host name, and character set.
## Privileges for RDS for Oracle as a target
To migrate to Amazon RDS for Oracle, create a privileged database user. You can use the following code example.
```
CREATE USER user_name IDENTIFIED BY your_password;
-- System privileges
GRANT DROP ANY CUBE BUILD PROCESS TO user_name;
GRANT ALTER ANY CUBE TO user_name;
GRANT CREATE ANY CUBE DIMENSION TO user_name;
GRANT CREATE ANY ASSEMBLY TO user_name;
GRANT ALTER ANY RULE TO user_name;
GRANT SELECT ANY DICTIONARY TO user_name;
GRANT ALTER ANY DIMENSION TO user_name;
GRANT CREATE ANY DIMENSION TO user_name;
GRANT ALTER ANY TYPE TO user_name;
GRANT DROP ANY TRIGGER TO user_name;
GRANT CREATE ANY VIEW TO user_name;
GRANT ALTER ANY CUBE BUILD PROCESS TO user_name;
GRANT CREATE ANY CREDENTIAL TO user_name;
GRANT DROP ANY CUBE DIMENSION TO user_name;
GRANT DROP ANY ASSEMBLY TO user_name;
GRANT DROP ANY PROCEDURE TO user_name;
GRANT ALTER ANY PROCEDURE TO user_name;
GRANT ALTER ANY SQL TRANSLATION PROFILE TO user_name;
GRANT DROP ANY MEASURE FOLDER TO user_name;
GRANT CREATE ANY MEASURE FOLDER TO user_name;
GRANT DROP ANY CUBE TO user_name;
GRANT DROP ANY MINING MODEL TO user_name;
GRANT CREATE ANY MINING MODEL TO user_name;
GRANT DROP ANY EDITION TO user_name;
GRANT CREATE ANY EVALUATION CONTEXT TO user_name;
GRANT DROP ANY DIMENSION TO user_name;
GRANT ALTER ANY INDEXTYPE TO user_name;
GRANT DROP ANY TYPE TO user_name;
GRANT CREATE ANY PROCEDURE TO user_name;
GRANT CREATE ANY SQL TRANSLATION PROFILE TO user_name;
GRANT CREATE ANY CUBE TO user_name;
GRANT COMMENT ANY MINING MODEL TO user_name;
GRANT ALTER ANY MINING MODEL TO user_name;
GRANT DROP ANY SQL PROFILE TO user_name;
GRANT CREATE ANY JOB TO user_name;
GRANT DROP ANY EVALUATION CONTEXT TO user_name;
GRANT ALTER ANY EVALUATION CONTEXT TO user_name;
GRANT CREATE ANY INDEXTYPE TO user_name;
GRANT CREATE ANY OPERATOR TO user_name;
GRANT CREATE ANY TRIGGER TO user_name;
GRANT DROP ANY ROLE TO user_name;
GRANT DROP ANY SEQUENCE TO user_name;
GRANT DROP ANY CLUSTER TO user_name;
GRANT DROP ANY SQL TRANSLATION PROFILE TO user_name;
GRANT ALTER ANY ASSEMBLY TO user_name;
GRANT CREATE ANY RULE SET TO user_name;
GRANT ALTER ANY OUTLINE TO user_name;
GRANT UNDER ANY TYPE TO user_name;
GRANT CREATE ANY TYPE TO user_name;
GRANT DROP ANY MATERIALIZED VIEW TO user_name;
GRANT ALTER ANY ROLE TO user_name;
GRANT DROP ANY VIEW TO user_name;
GRANT ALTER ANY INDEX TO user_name;
GRANT COMMENT ANY TABLE TO user_name;
GRANT CREATE ANY TABLE TO user_name;
GRANT CREATE USER TO user_name;
GRANT DROP ANY RULE SET TO user_name;
GRANT CREATE ANY CONTEXT TO user_name;
GRANT DROP ANY INDEXTYPE TO user_name;
GRANT ALTER ANY OPERATOR TO user_name;
GRANT CREATE ANY MATERIALIZED VIEW TO user_name;
GRANT ALTER ANY SEQUENCE TO user_name;
GRANT DROP ANY SYNONYM TO user_name;
GRANT CREATE ANY SYNONYM TO user_name;
GRANT DROP USER TO user_name;
GRANT ALTER ANY MEASURE FOLDER TO user_name;
GRANT ALTER ANY EDITION TO user_name;
GRANT DROP ANY RULE TO user_name;
GRANT CREATE ANY RULE TO user_name;
GRANT ALTER ANY RULE SET TO user_name;
GRANT CREATE ANY OUTLINE TO user_name;
GRANT UNDER ANY TABLE TO user_name;
GRANT UNDER ANY VIEW TO user_name;
GRANT DROP ANY DIRECTORY TO user_name;
GRANT ALTER ANY CLUSTER TO user_name;
GRANT CREATE ANY CLUSTER TO user_name;
GRANT ALTER ANY TABLE TO user_name;
GRANT CREATE ANY CUBE BUILD PROCESS TO user_name;
GRANT ALTER ANY CUBE DIMENSION TO user_name;
GRANT CREATE ANY EDITION TO user_name;
GRANT CREATE ANY SQL PROFILE TO user_name;
GRANT ALTER ANY SQL PROFILE TO user_name;
GRANT DROP ANY OUTLINE TO user_name;
GRANT DROP ANY CONTEXT TO user_name;
GRANT DROP ANY OPERATOR TO user_name;
GRANT DROP ANY LIBRARY TO user_name;
GRANT ALTER ANY LIBRARY TO user_name;
GRANT CREATE ANY LIBRARY TO user_name;
GRANT ALTER ANY MATERIALIZED VIEW TO user_name;
GRANT ALTER ANY TRIGGER TO user_name;
GRANT CREATE ANY SEQUENCE TO user_name;
GRANT DROP ANY INDEX TO user_name;
GRANT CREATE ANY INDEX TO user_name;
GRANT DROP ANY TABLE TO user_name;
GRANT SELECT_CATALOG_ROLE TO user_name;
GRANT SELECT ANY SEQUENCE TO user_name;
-- Database Links
GRANT CREATE DATABASE LINK TO user_name;
GRANT CREATE PUBLIC DATABASE LINK TO user_name;
GRANT DROP PUBLIC DATABASE LINK TO user_name;
-- Server Level Objects (directory)
GRANT CREATE ANY DIRECTORY TO user_name;
GRANT DROP ANY DIRECTORY TO user_name;
-- (for RDS only)
GRANT EXECUTE ON RDSADMIN.RDSADMIN_UTIL TO user_name;
-- Server Level Objects (tablespace)
GRANT CREATE TABLESPACE TO user_name;
GRANT DROP TABLESPACE TO user_name;
-- Server Level Objects (user roles)
/* (grant source privileges with admin option or convert roles/privs as DBA) */
-- Queues
grant execute on DBMS_AQADM to user_name;
grant aq_administrator_role to user_name;
-- for Materialized View Logs creation
GRANT SELECT ANY TABLE TO user_name;
-- Roles
GRANT RESOURCE TO user_name;
GRANT CONNECT TO user_name;
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *your\$1password* with a secure password.
## Limitations when converting Oracle to Amazon RDS for Oracle
Some limitations you should consider when migrating Oracle schema and code to Amazon RDS for Oracle:
+ A predefined role in Amazon RDS, called DBA, normally allows all administrative privileges on an Oracle database engine. The following privileges are not available for the DBA role on an Amazon RDS DB instance using the Oracle engine:
+ Alter database
+ Alter system
+ Create any directory
+ Grant any privilege
+ Grant any role
+ Create external job
You can grant all other privileges to an Oracle RDS user role.
+ Amazon RDS for Oracle supports traditional auditing, fine-grained auditing using the DBMS\$1FGA package, and Oracle Unified Auditing.
+ Amazon RDS for Oracle doesn’t support change data capture (CDC). To do CDC during and after a database migration, use AWS Database Migration Service.
# Connecting to PostgreSQL Databases with the AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, database code objects, and application code from PostgreSQL to the following targets:
+ Amazon RDS for MySQL
+ Amazon Aurora MySQL-Compatible Edition
+ Amazon RDS for PostgreSQL
+ Amazon Aurora PostgreSQL-Compatible Edition
For more information, see the following sections:
**Topics**
+ [Privileges for PostgreSQL as a source database](#CHAP_Source.PostgreSQL.Permissions)
+ [Connecting to PostgreSQL as a source](#CHAP_Source.PostgreSQL.Connecting)
+ [Privileges for MySQL as a target database](#CHAP_Source.PostgreSQL.ConfigureMySQL)
## Privileges for PostgreSQL as a source database
The privileges required for PostgreSQL as a source are as follows:
+ CONNECT ON DATABASE **
+ USAGE ON SCHEMA **
+ SELECT ON ALL TABLES IN SCHEMA **
+ SELECT ON ALL SEQUENCES IN SCHEMA **
## Connecting to PostgreSQL as a source
Use the following procedure to connect to your PostgreSQL source database with the AWS Schema Conversion Tool.
**To connect to a PostgreSQL source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **PostgreSQL**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the PostgreSQL source database connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.PostgreSQL.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Privileges for MySQL as a target database
The privileges required for MySQL as a target when you migrate from PostgreSQL are as follows:
+ CREATE ON \$1.\$1
+ ALTER ON \$1.\$1
+ DROP ON \$1.\$1
+ INDEX ON \$1.\$1
+ REFERENCES ON \$1.\$1
+ SELECT ON \$1.\$1
+ CREATE VIEW ON \$1.\$1
+ SHOW VIEW ON \$1.\$1
+ TRIGGER ON \$1.\$1
+ CREATE ROUTINE ON \$1.\$1
+ ALTER ROUTINE ON \$1.\$1
+ EXECUTE ON \$1.\$1
+ INSERT, UPDATE ON AWS\$1POSTGRESQL\$1EXT.\$1
+ INSERT, UPDATE, DELETE ON AWS\$1POSTGRESQL\$1EXT\$1DATA.\$1
+ CREATE TEMPORARY TABLES ON AWS\$1POSTGRESQL\$1EXT\$1DATA.\$1
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 INSERT, UPDATE ON AWS_POSTGRESQL_EXT.* TO 'user_name';
GRANT INSERT, UPDATE, DELETE ON AWS_POSTGRESQL_EXT_DATA.* TO 'user_name';
GRANT CREATE TEMPORARY TABLES ON AWS_POSTGRESQL_EXT_DATA.* TO 'user_name';
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *your\$1password* with a secure password.
To use Amazon RDS for MySQL or Aurora MySQL as a target, set the `lower_case_table_names` parameter to `1`. This value means that the MySQL server handles identifiers of such object names as tables, indexes, triggers, and databases as case insensitive. If you have turned on binary logging in your target instance, then set the `log_bin_trust_function_creators` parameter to `1`. In this case, you don't need to use the `DETERMINISTIC`, `READS SQL DATA` or `NO SQL` characteristics to create stored functions. To configure these parameters, create a new DB parameter group or modify an existing DB parameter group.
# Connecting to SAP Databases with the AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, database code objects, and application code from SAP (Sybase) Adaptive Server Enterprise (ASE) to the following targets:
+ Amazon RDS for MySQL
+ Amazon Aurora MySQL-Compatible Edition
+ Amazon RDS for MariaDB
+ Amazon RDS for PostgreSQL
+ Amazon Aurora PostgreSQL-Compatible Edition
For more information, see the following sections:
**Topics**
+ [Privileges for SAP ASE as a source database](#CHAP_Source.SAP.Permissions)
+ [Connecting to SAP ASE (Sybase) as a source](#CHAP_Source.SAP.Connecting)
+ [Privileges for MySQL as a target database](#CHAP_Source.SAP.ConfigureMySQL)
+ [SAP ASE to MySQL conversion settings](#CHAP_Source.SAP.MySQLConversionSettings)
+ [Privileges for PostgreSQL as a target database](#CHAP_Source.SAP.ConfigurePostgreSQL)
+ [SAP ASE to PostgreSQL conversion settings](#CHAP_Source.SAP.PostgreSQLConversionSettings)
## Privileges for SAP ASE as a source database
To use an SAP ASE database as a source, you create a database user and grant permissions. To do this, take the following steps.
**Create and configure a database user**
1. Connect to the source database.
1. Create a database user with the following commands. Provide a password for the new user.
```
USE master
CREATE LOGIN min_privs WITH PASSWORD
sp_adduser min_privs
grant select on dbo.spt_values to min_privs
grant select on asehostname to min_privs
```
1. For every database you are going to migrate, grant the following privileges.
```
USE
sp_adduser min_privs
grant select on dbo.sysusers to min_privs
grant select on dbo.sysobjects to min_privs
grant select on dbo.sysindexes to min_privs
grant select on dbo.syscolumns to min_privs
grant select on dbo.sysreferences to min_privs
grant select on dbo.syscomments to min_privs
grant select on dbo.syspartitions to min_privs
grant select on dbo.syspartitionkeys to min_privs
grant select on dbo.sysconstraints to min_privs
grant select on dbo.systypes to min_privs
grant select on dbo.sysqueryplans to min_privs
```
## Connecting to SAP ASE (Sybase) as a source
Use the following procedure to connect to your SAP ASE source database with the AWS Schema Conversion Tool.
**To connect to a SAP ASE source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **SAP ASE**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the SAP ASE source database connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SAP.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. 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 \$1.\$1
+ ALTER ON \$1.\$1
+ DROP ON \$1.\$1
+ INDEX ON \$1.\$1
+ REFERENCES ON \$1.\$1
+ SELECT ON \$1.\$1
+ CREATE VIEW ON \$1.\$1
+ SHOW VIEW ON \$1.\$1
+ TRIGGER ON \$1.\$1
+ CREATE ROUTINE ON \$1.\$1
+ ALTER ROUTINE ON \$1.\$1
+ EXECUTE ON \$1.\$1
+ INSERT, UPDATE ON AWS\$1SAPASE\$1EXT.\$1
+ CREATE TEMPORARY TABLES ON AWS\$1SAPASE\$1EXT.\$1
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 INSERT, UPDATE ON AWS_SAPASE_EXT.* TO 'user_name';
GRANT CREATE TEMPORARY TABLES ON AWS_SAPASE_EXT.* TO 'user_name';
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *your\$1password* with a secure password.
To use Amazon RDS for MySQL or Aurora MySQL as a target, set the `lower_case_table_names` parameter to `1`. This value means that the MySQL server handles identifiers of such object names as tables, indexes, triggers, and databases as case insensitive. If you have turned on binary logging in your target instance, then set the `log_bin_trust_function_creators` parameter to `1`. In this case, you don't need to use the `DETERMINISTIC`, `READS SQL DATA` or `NO SQL` characteristics to create stored functions. To configure these parameters, create a new DB parameter group or modify an existing DB parameter group.
## SAP ASE to MySQL conversion settings
To edit SAP ASE to MySQL conversion settings, choose **Settings**, and then choose **Conversion settings**. From the upper list, choose **SAP ASE**, and then choose **SAP ASE – MySQL** or **SAP ASE – Amazon Aurora (MySQL compatible)**. AWS SCT displays all available settings for SAP ASE to PostgreSQL conversion.
SAP ASE to MySQL 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 use the exact names of the source database objects in the converted code.
By default, AWS SCT converts the names of database objects, variables, and parameters to lowercase. To keep the original case for these names, select **Treat source database object names as case sensitive**. Choose this option if you use case-sensitive object names in your source SAP ASE database server.
## 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 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 DATABASE db_name TO user_name;
ALTER DATABASE db_name SET SEARCH_PATH = "$user", public_synonyms, public;
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *db\$1name* with the name of your target database. Finally, replace *your\$1password* 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.
## SAP ASE to PostgreSQL conversion settings
To edit SAP ASE to PostgreSQL conversion settings, choose **Settings**, and then choose **Conversion settings**. From the upper list, choose **SAP ASE**, and then choose **SAP ASE – PostgreSQL** or **SAP ASE – Amazon Aurora (PostgreSQL compatible)**. AWS SCT displays all available settings for SAP ASE to PostgreSQL conversion.
SAP ASE 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 define the template to use for the schema names in the converted code. For **Schema name generation template**, choose one of the following options:
+ **** – Uses the SAP ASE database name as a schema name in PostgreSQL.
+ **** – Uses the SAP ASE schema name as a schema name in PostgreSQL.
+ **\$1** – Uses a combination of the SAP ASE database and schema names as a schema name in PostgreSQL.
+ To use the exact names of the source database objects in the converted code.
By default, AWS SCT converts the names of database objects, variables, and parameters to lowercase. To keep the original case for these names, select **Treat source database object names as case sensitive**. Choose this option if you use case-sensitive object names in your source SAP ASE database server.
For case-sensitive operations, AWS SCT can avoid the conversion of database object names to lowercase. To do so, select **Avoid casting to lowercase for case sensitive operations**.
+ To allow the use of indexes with the same name in different tables in SAP ASE.
In PostgreSQL, all index names that you use in the schema must be unique. To make sure that AWS SCT generates unique names for all your indexes, select **Generate unique names for indexes**.
# Connect Microsoft SQL Servers with AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, database code objects, and application code from SQL Server to the following targets:
+ Amazon RDS for MySQL
+ Amazon Aurora MySQL-Compatible Edition
+ Amazon RDS for PostgreSQL
+ Amazon Aurora PostgreSQL-Compatible Edition
+ Amazon RDS for SQL Server
+ Amazon RDS for MariaDB
**Note**
AWS SCT does not support using Amazon RDS for SQL server as a source.
You can use AWS SCT to create an assessment report for the migration of schemas, database code objects, and application code from SQL Server to Babelfish for Aurora PostgreSQL, as described following.
**Topics**
+ [Privileges for Microsoft SQL Server as a source](#CHAP_Source.SQLServer.Permissions)
+ [Using Windows Authentication when using Microsoft SQL Server as a source](#CHAP_Source.SQLServer.Permissions.WinAuth)
+ [Connecting to SQL Server as a source](#CHAP_Source.SQLServer.Connecting)
+ [Converting SQL Server to MySQL](CHAP_Source.SQLServer.ToMySQL.md)
+ [Migrating from SQL Server to PostgreSQL with AWS Schema Conversion Tool](CHAP_Source.SQLServer.ToPostgreSQL.md)
+ [Migrating from SQL Server to Amazon RDS for SQL Server with AWS Schema Conversion Tool](CHAP_Source.SQLServer.ToRDSSQLServer.md)
## Privileges for Microsoft SQL Server as a source
The privileges required for Microsoft SQL Server as a source are as follows:
+ VIEW DEFINITION
+ VIEW DATABASE STATE
The `VIEW DEFINITION` privilege enables users that have public access to see object definitions. AWS SCT uses the `VIEW DATABASE STATE` privilege to check the features of the SQL Server Enterprise edition.
Repeat the grant for each database whose schema you are converting.
In addition, grant the following privileges on the `master` database:
+ VIEW SERVER STATE
+ VIEW ANY DEFINITION
AWS SCT uses the `VIEW SERVER STATE` privilege to collect server settings and configuration. Make sure that you grant the `VIEW ANY DEFINITION` privilege to view endpoints.
To read information about Microsoft Analysis Services, run the following command on the `master` database.
```
EXEC master..sp_addsrvrolemember @loginame = N'', @rolename = N'sysadmin'
```
In the preceding example, replace the `` placeholder with the name of the user that you granted with the privileges before.
To read information about SQL Server Agent, add your user to the `SQLAgentUser` role. Run the following command on the `msdb` database.
```
EXEC sp_addrolemember , ;
```
In the preceding example, replace the `` placeholder with the name of the SQL Server Agent role. Then replace the `` placeholder with the name of the user that you granted with the privileges before. For more information, see [Adding a user to the SQLAgentUser role](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.Agent.html#SQLServerAgent.AddUser) in the *Amazon RDS User Guide*.
To detect log shipping, grant the `SELECT on dbo.log_shipping_primary_databases` privilege on the `msdb` database.
To use the notification approach of the DDL replication, grant the `RECEIVE ON .` privilege on your source databases. In this example, replace the `` placeholder with the schema name of your database. Then, replace the `` placeholder with the name of a queue table.
## Using Windows Authentication when using Microsoft SQL Server as a source
If your application runs on a Windows-based intranet, you might be able to use Windows Authentication for database access. Windows Authentication uses the current Windows identity established on the operating system thread to access the SQL Server database. You can then map the Windows identity to a SQL Server database and permissions. To connect to SQL Server using Windows Authentication, you must specify the Windows identity that your application is using. You must also grant the Windows identity access to the SQL Server database.
SQL Server has two modes of access: Windows Authentication mode and Mixed Mode. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed Mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled. For more information about Windows Authentication, see the Microsoft Windows documentation.
The possible example for creating a user in TEST\$1DB is shown following.
```
USE [TEST_DB]
CREATE USER [TestUser] FOR LOGIN [TestDomain\TestUser]
GRANT VIEW DEFINITION TO [TestUser]
GRANT VIEW DATABASE STATE TO [TestUser]
```
### Using Windows Authentication with a JDBC connection
The JDBC driver does not support Windows Authentication when the driver is used on non-Windows operating systems. Windows Authentication credentials, such as user name and password, are not automatically specified when connecting to SQL Server from non-Windows operating systems. In such cases, the applications must use SQL Server Authentication instead.
In JDBC connection string, the parameter `integratedSecurity` must be specified to connect using Windows Authentication. The JDBC driver supports Integrated Windows Authentication on Windows operating systems through the `integratedSecurity` connection string parameter.
To use integrated authentication
1. Install the JDBC driver.
1. Copy the `sqljdbc_auth.dll` file to a directory on the Windows system path on the computer where the JDBC driver is installed.
The `sqljdbc_auth.dll` files are installed in the following location:
<*installation directory*>\$1sqljdbc\$1<*version*>\$1<*language*>\$1auth\$1
When you try to establish a connection to SQL Server database using Windows Authentication, you might get this error: This driver is not configured for integrated authentication. This problem can be solved by performing the following actions:
+ Declare two variables that point to the installed path of your JDBC:
`variable name: SQLJDBC_HOME; variable value: D:\lib\JDBC4.1\enu` (where your sqljdbc4.jar exists);
`variable name: SQLJDBC_AUTH_HOME; variable value: D\lib\JDBC4.1\enu\auth\x86` (if you are running 32bit OS) or `D\lib\JDBC4.1\enu\auth\x64` (if you are running 64bit OS). This is where your `sqljdbc_auth.dll` is located.
+ Copy `sqljdbc_auth.dll` to the folder where your JDK/JRE is running. You may copy to lib folder, bin folder, and so on. As an example, you might copy to the following folder.
```
[JDK_INSTALLED_PATH]\bin;
[JDK_INSTALLED_PATH]\jre\bin;
[JDK_INSTALLED_PATH]\jre\lib;
[JDK_INSTALLED_PATH]\lib;
```
+ Ensure that in your JDBC library folder, you have only the SQLJDBC4.jar file. Remove any other sqljdbc\$1.jar files from that folder (or copy them to another folder). If you are adding the driver as part of your program, ensure that you add only SQLJDBC4.jar as the driver to use.
+ Copy sqljdbc\$1auth.dll the file in the folder with your application.
**Note**
If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc\$1auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc\$1auth.dll file in the x64 folder.
When you connect to a SQL Server database, you can choose either **Windows Authentication** or **SQL Server Authentication** for the **Authentication** option.
## Connecting to SQL Server as a source
Use the following procedure to connect to your Microsoft SQL Server source database with the AWS Schema Conversion Tool.
**To connect to a Microsoft SQL Server source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Microsoft SQL Server**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Microsoft SQL Server source database connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
# Converting SQL Server to MySQL
To emulate Microsoft SQL Server database functions in your converted MySQL code, use the SQL Server to MySQL extension pack in AWS SCT. For more information about extension packs, see [Using extension packs with AWS Schema Conversion Tool](CHAP_ExtensionPack.md).
**Topics**
+ [Privileges for MySQL as a target database](#CHAP_Source.SQLServer.ToMySQL.ConfigureTarget)
+ [SQL Server to MySQL conversion settings](#CHAP_Source.SQLServer.ToMySQL.ConversionSettings)
+ [Migration considerations](#CHAP_Source.SQLServer.ToMySQL.MigrationConsiderations)
## Privileges for MySQL as a target database
The privileges required for MySQL as a target are as follows:
+ CREATE ON \$1.\$1
+ ALTER ON \$1.\$1
+ DROP ON \$1.\$1
+ INDEX ON \$1.\$1
+ REFERENCES ON \$1.\$1
+ SELECT ON \$1.\$1
+ CREATE VIEW ON \$1.\$1
+ SHOW VIEW ON \$1.\$1
+ TRIGGER ON \$1.\$1
+ CREATE ROUTINE ON \$1.\$1
+ ALTER ROUTINE ON \$1.\$1
+ EXECUTE ON \$1.\$1
+ INSERT, UPDATE ON AWS\$1SQLSERVER\$1EXT.\$1
+ INSERT, UPDATE, DELETE ON AWS\$1SQLSERVER\$1EXT\$1DATA.\$1
+ CREATE TEMPORARY TABLES ON AWS\$1SQLSERVER\$1EXT\$1DATA.\$1
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 INSERT, UPDATE ON AWS_SQLSERVER_EXT.* TO 'user_name';
GRANT INSERT, UPDATE, DELETE ON AWS_SQLSERVER_EXT_DATA.* TO 'user_name';
GRANT CREATE TEMPORARY TABLES ON AWS_SQLSERVER_EXT_DATA.* TO 'user_name';
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *your\$1password* with a secure password.
If you use a MySQL database version 5.7 or lower as a target, then run the following command. For MySQL databases version 8.0 and higher, this command is deprecated.
```
GRANT SELECT ON mysql.proc TO 'user_name';
```
To use Amazon RDS for MySQL or Aurora MySQL as a target, set the `lower_case_table_names` parameter to `1`. This value means that the MySQL server handles identifiers of such object names as tables, indexes, triggers, and databases as case insensitive. If you have turned on binary logging in your target instance, then set the `log_bin_trust_function_creators` parameter to `1`. In this case, you don't need to use the `DETERMINISTIC`, `READS SQL DATA` or `NO SQL` characteristics to create stored functions. To configure these parameters, create a new DB parameter group or modify an existing DB parameter group.
## SQL Server to MySQL conversion settings
To edit SQL Server to MySQL conversion settings, in AWS SCT choose **Settings**, and then choose **Conversion settings**. From the upper list, choose **SQL Server**, and then choose **SQL Server – MySQL**. AWS SCT displays all available settings for SQL Server to MySQL conversion.
SQL Server to MySQL 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 allow your source SQL Server database to store the output of `EXEC` in a table. AWS SCT creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines for handling open datasets**.
## Migration considerations
Consider these things when migrating a SQL Server schema to MySQL:
+ MySQL doesn’t support the `MERGE` statement. However, AWS SCT can emulate the `MERGE` statement during conversion by using the `INSERT ON DUPLICATE KEY` clause and the `UPDATE FROM and DELETE FROM` statements.
For correct emulation using `INSERT ON DUPLICATE KEY`, make sure that a unique constraint or primary key exists on the target MySQL database.
+ You can use a `GOTO` statement and a label to change the order that statements are run in. Any Transact-SQL statements that follow a `GOTO` statement are skipped, and processing continues at the label. You can use `GOTO` statements and labels anywhere within a procedure, batch, or statement block. You can also nest `GOTO` statements.
MySQL doesn’t use `GOTO` statements. When AWS SCT converts code that contains a `GOTO` statement, it converts the statement to use a `BEGIN…END` or `LOOP…END LOOP` statement. You can find examples of how AWS SCT converts `GOTO` statements in the table following.
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.ToMySQL.html)
+ MySQL doesn't support multistatement table-valued functions. AWS SCT simulates table-valued functions during a conversion by creating temporary tables and rewriting statements to use these temporary tables.
# Migrating from SQL Server to PostgreSQL with AWS Schema Conversion Tool
You can use the SQL Server to PostgreSQL extension pack in AWS SCT. This extension pack emulates SQL Server database functions in the converted PostgreSQL code. Use the SQL Server to PostgreSQL extension pack to emulate SQL Server Agent and SQL Server Database Mail. For more information about extension packs, see [Using extension packs with AWS Schema Conversion Tool](CHAP_ExtensionPack.md).
**Topics**
+ [Privileges for PostgreSQL as a target database](#CHAP_Source.SQLServer.ToPostgreSQL.ConfigurePostgreSQL)
+ [SQL Server to PostgreSQL conversion settings](#CHAP_Source.SQLServer.ToPostgreSQL.ConversionSettings)
+ [Converting SQL Server partitions to PostgreSQL version 10 partitions](#CHAP_Source.SQLServer.ToPostgreSQL.PG10Partitions)
+ [Migration considerations](#CHAP_Source.SQLServer.ToPostgreSQL.MigrationConsiderations)
+ [Using an AWS SCT extension pack to emulate SQL Server Agent in PostgreSQL](CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.md)
+ [Using an AWS SCT extension pack to emulate SQL Server Database Mail in PostgreSQL](CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.md)
## 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 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 DATABASE db_name TO user_name;
ALTER DATABASE db_name SET SEARCH_PATH = "$user", public_synonyms, public;
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *db\$1name* with the name of your target database. Finally, replace *your\$1password* 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.
## SQL Server to PostgreSQL conversion settings
To edit SQL Server to PostgreSQL conversion settings, choose **Settings**, and then choose **Conversion settings**. From the upper list, choose **SQL Server**, and then choose **SQL Server – PostgreSQL**. AWS SCT displays all available settings for SQL Server to PostgreSQL conversion.
SQL Server 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 allow to use indexes with the same name in different tables in SQL Server.
In PostgreSQL, all index names that you use in the schema, must be unique. To make sure that AWS SCT generates unique names for all your indexes, select **Generate unique names for indexes**.
+ To convert SQL Server procedures to PostgreSQL functions.
PostgreSQL version 10 and earlier doesn't support procedures. For customers who aren't familiar with using procedures in PostgreSQL, AWS SCT can convert procedures to functions. To do so, select **Convert procedures to functions**.
+ To emulate the output of `EXEC` in a table.
Your source SQL Server database can store the output of `EXEC` in a table. AWS SCT creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines for handling open datasets**.
+ To define the template to use for the schema names in the converted code. For **Schema name generation template**, choose one of the following options:
+ **** – Uses the SQL Server database name as a schema name in PostgreSQL.
+ **** – Uses the SQL Server schema name as a schema name in PostgreSQL.
+ **\$1** – Uses a combination of the SQL Server database and schema names as a schema name in PostgreSQL.
+ To keep the letter case of your source object names.
To avoid conversion of object names to lower case, select **Avoid casting to lower case for case sensitive operations**. This option applies only when you turn on case sensitivity option in your target database.
+ To keep the parameter names from your source database.
To add double quotation marks to the names of parameters in the converted code, select **Keep original parameter names**.
## Converting SQL Server partitions to PostgreSQL version 10 partitions
When you convert a Microsoft SQL Server database to Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) or Amazon Relational Database Service for PostgreSQL (Amazon RDS for PostgreSQL), be aware of the following.
In SQL Server, you create partitions with partition functions. When converting from a SQL Server portioned table to a PostgreSQL version 10 partitioned table, be aware of several potential issues:
+ SQL Server allows you to partition a table using a column without a NOT NULL constraint. In that case, all NULL values go to the leftmost partition. PostgreSQL doesn’t support NULL values for RANGE partitioning.
+ SQL Server allows you to create primary and unique keys for partitioned tables. For PostgreSQL, you create primary or unique keys for each partition directly. Thus, PRIMARY or UNIQUE KEY constraint must be removed from their parent table when migrating to PostgreSQL. The resulting key names take the format `_`.
+ SQL Server allows you to create foreign key constraint from and to partitioned tables. PostgreSQL doesn’t support foreign keys referencing partitioned tables. Also, PostgreSQL doesn’t support foreign key references from a partitioned table to another table.
+ SQL Server allows you to create indexes for partitioned tables. For PostgreSQL, an index should be created for each partition directly. Thus, indexes must be removed from their parent tables when migrating to PostgreSQL. The resulting index names take the format `_`.
+ PostgreSQL doesn’t support partitioned indexes.
## Migration considerations
Some things to consider when migrating a SQL Server schema to PostgreSQL:
+ In PostgreSQL, all object’s names in a schema must be unique, including indexes. Index names must be unique in the schema of the base table. In SQL Server, an index name can be the same for different tables.
To ensure the uniqueness of index names, AWS SCT gives you the option to generate unique index names if your index names are not unique. To do this, choose the option **Generate unique index names** in the project properties. By default, this option is enabled. If this option is enabled, unique index names are created using the format IX\$1table\$1name\$1index\$1name. If this option is disabled, index names aren’t changed.
+ A GOTO statement and a label can be used to change the order that statements are run in. Any Transact-SQL statements that follow a GOTO statement are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can also be nested.
PostgreSQL doesn’t use GOTO statements. When AWS SCT converts code that contains a GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement. You can find examples of how AWS SCT converts GOTO statements in the table following.
**SQL Server GOTO statements and the converted PostgreSQL statements**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.ToPostgreSQL.html)
+ PostgreSQL doesn't support a MERGE statement. AWS SCT emulates the behavior of a MERGE statement in the following ways:
+ By INSERT ON CONFLICT construction.
+ By using the UPDATE FROM DML statement, such as MERGE without a WHEN NOT MATCHED clause.
+ By using CURSOR, such as with a MERGE with DELETE clause or by using a complex MERGE ON condition statement.
+ AWS SCT can add database triggers to the object tree when Amazon RDS is the target.
+ AWS SCT can add server-level triggers to the object tree when Amazon RDS is the target.
+ SQL Server automatically creates and manages `deleted` and `inserted` tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. AWS SCT can convert the usage of these tables inside DML trigger statements.
+ AWS SCT can add linked servers to the object tree when Amazon RDS is the target.
+ When migrating from Microsoft SQL Server to PostgreSQL, the built-in SUSER\$1SNAME function is converted as follows:
+ SUSER\$1SNAME – Returns the login name associated with a security identification number (SID).
+ SUSER\$1SNAME() – Not supported.
+ SUSER\$1SNAME() CURRENT\$1USER – Returns the user name of the current execution context.
+ SUSER\$1SNAME(NULL) – Returns NULL.
+ Converting table-valued functions is supported. Table-valued functions return a table and can take the place of a table in a query.
+ PATINDEX returns the starting position of the first occurrence of a pattern in a specified expression on all valid text and character data types. It returns zeros if the pattern is not found. When converting from SQL Server to Amazon RDS for PostgreSQL, AWS SCT replaces application code that uses PATINDEX with aws\$1sqlserver\$1ext.patindex(, ) .
+ In SQL Server, a user-defined table type is a type that represents the definition of a table structure. You use a user-defined table type to declare table-value parameters for stored procedures or functions. You can also use a user-defined table type to declare table variables that you want to use in a batch or in the body of a stored procedure or function. AWS SCT emulated this type in PostgreSQL by creating a temporary table.
When converting from SQL Server to PostgreSQL, AWS SCT converts SQL Server system objects into recognizable objects in PostgreSQL. The following table shows how the system objects are converted.
| MS SQL Server use cases | PostgreSQL substitution |
| --- | --- |
| SYS.SCHEMAS | AWS\$1SQLSERVER\$1EXT.SYS\$1SCHEMAS |
| SYS.TABLES | AWS\$1SQLSERVER\$1EXT.SYS\$1TABLES |
| SYS.VIEWS | AWS\$1SQLSERVER\$1EXT.SYS\$1VIEWS |
| SYS.ALL\$1VIEWS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1VIEWS |
| SYS.TYPES | AWS\$1SQLSERVER\$1EXT.SYS\$1TYPES |
| SYS.COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1COLUMNS |
| SYS.ALL\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1COLUMNS |
| SYS.FOREIGN\$1KEYS | AWS\$1SQLSERVER\$1EXT.SYS\$1FOREIGN\$1KEYS |
| SYS.SYSFOREIGNKEYS | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSFOREIGNKEYS |
| SYS.FOREIGN\$1KEY\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1FOREIGN\$1KEY\$1COLUMNS |
| SYS.KEY\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.SYS\$1KEY\$1CONSTRAINTS |
| SYS.IDENTITY\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1IDENTITY\$1COLUMNS |
| SYS.PROCEDURES | AWS\$1SQLSERVER\$1EXT.SYS\$1PROCEDURES |
| SYS.INDEXES | AWS\$1SQLSERVER\$1EXT.SYS\$1INDEXES |
| SYS.SYSINDEXES | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSINDEXES |
| SYS.OBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1OBJECTS |
| SYS.ALL\$1OBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1OBJECTS |
| SYS.SYSOBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSOBJECTS |
| SYS.SQL\$1MODULES | AWS\$1SQLSERVER\$1EXT.SYS\$1SQL\$1MODULES |
| SYS.DATABASES | AWS\$1SQLSERVER\$1EXT.SYS\$1DATABASES |
| INFORMATION\$1SCHEMA.SCHEMATA | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1SCHEMATA |
| INFORMATION\$1SCHEMA.VIEWS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1VIEWS |
| INFORMATION\$1SCHEMA.TABLES | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1TABLES |
| INFORMATION\$1SCHEMA.COLUMNS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1COLUMNS |
| INFORMATION\$1SCHEMA.CHECK\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CHECK\$1CONSTRAINTS |
| INFORMATION\$1SCHEMA.REFERENTIAL\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1REFERENTIAL\$1CONSTRAINTS |
| INFORMATION\$1SCHEMA.TABLE\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1TABLE\$1CONSTRAINTS |
| INFORMATION\$1SCHEMA.KEY\$1COLUMN\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1KEY\$1COLUMN\$1USAGE |
| INFORMATION\$1SCHEMA.CONSTRAINT\$1TABLE\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CONSTRAINT\$1TABLE\$1USAGE |
| INFORMATION\$1SCHEMA.CONSTRAINT\$1COLUMN\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CONSTRAINT\$1COLUMN\$1USAGE |
| INFORMATION\$1SCHEMA.ROUTINES | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1ROUTINES |
| SYS.SYSPROCESSES | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSPROCESSES |
| sys.system\$1objects | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSTEM\$1OBJECTS |
# Using an AWS SCT extension pack to emulate SQL Server Agent in PostgreSQL
SQL Server Agent is a Microsoft Windows service that runs SQL Server jobs. SQL Server Agent runs jobs on a schedule, in response to a specific event, or on demand. For more information about SQL Server Agent, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent?view=sql-server-ver15).
PostgreSQL doesn't have an equivalent for SQL Server Agent. To emulate the SQL Server Agent features, AWS SCT creates an extension pack. This extension pack uses AWS Lambda and Amazon CloudWatch. AWS Lambda implements the interface that you use to manage schedules and run jobs. Amazon CloudWatch maintains the schedule rules.
AWS Lambda and Amazon CloudWatch use a JSON parameter to interact. This JSON parameter has the following structure.
```
{
"mode": mode,
"parameters": {
list of parameters
},
"callback": procedure name
}
```
In the preceding example, *`mode`* is the type of the task and `list of parameters` is a set of parameters that depend on the type of the task. Also, `procedure name` is the name of the procedure that runs after the task is completed.
AWS SCT uses one Lambda function to control and run jobs. The CloudWatch rule starts the run of the job and provides the necessary information to start the job. When the CloudWatch rule triggers, it starts the Lambda function using the parameters from the rule.
To create a simple job that calls a procedure, use the following format.
```
{
"mode": "run_job",
"parameters": {
"vendor": "mysql",
"cmd": "lambda_db.nightly_job"
}
}
```
To create a job with several steps, use the following format.
```
{
"mode": "run_job",
"parameters": {
"job_name": "Job1",
"enabled": "true",
"start_step_id": 1,
"notify_level_email": [0|1|2|3],
"notify_email": email,
"delete_level": [0|1|2|3],
"job_callback": "ProcCallBackJob(job_name, code, message)",
"step_callback": "ProcCallBackStep(job_name, step_id, code, message)"
},
"steps": [
{
"id":1,
"cmd": "ProcStep1",
"cmdexec_success_code": 0,
"on_success_action": [|2|3|4],
"on_success_step_id": 1,
"on_fail_action": 0,
"on_fail_step_id": 0,
"retry_attempts": number,
"retry_interval": number
},
{
"id":2,
"cmd": "ProcStep2",
"cmdexec_success_code": 0,
"on_success_action": [1|2|3|4],
"on_success_step_id": 0,
"on_fail_action": 0,
"on_fail_step_id": 0,
"retry_attempts": number,
"retry_interval": number
},
...
]
}
```
To emulate the SQL Server Agent behavior in PostgreSQL, the AWS SCT extension pack also creates the following tables and procedures.
## Tables that emulate SQL Server Agent in PostgreSQL
To emulate SQL Server Agent, the extension pack uses the following tables:
**sysjobs**
Stores the information about the jobs.
**sysjobsteps**
Stores the information about the steps of a job.
**sysschedules**
Stores the information about the job schedules.
**sysjobschedules**
Stores the schedule information for individual jobs.
**sysjobhistory**
Stores the information about the runs of scheduled jobs.
## Procedures that emulate SQL Server Agent in PostgreSQL
To emulate SQL Server Agent, the extension pack uses the following procedures:
**sp\$1add\$1job**
Adds a new job.
**sp\$1add\$1jobstep**
Adds a step to a job.
**sp\$1add\$1schedule**
Creates a new schedule rule in Amazon CloudWatch. You can use this schedule with any number of jobs.
**sp\$1attach\$1schedule**
Sets a schedule for the selected job.
**sp\$1add\$1jobschedule**
Creates a schedule rule for a job in Amazon CloudWatch and sets the target for this rule.
**sp\$1update\$1job**
Updates the attributes of the previously created job.
**sp\$1update\$1jobstep**
Updates the attributes of the step in a job.
**sp\$1update\$1schedule**
Updates the attributes of a schedule rule in Amazon CloudWatch.
**sp\$1update\$1jobschedule**
Updates the attributes of the schedule for the specified job.
**sp\$1delete\$1job**
Deletes a job.
**sp\$1delete\$1jobstep**
Deletes a job step from a job.
**sp\$1delete\$1schedule**
Deletes a schedule.
**sp\$1delete\$1jobschedule**
Deletes the schedule rule for the specified job from Amazon CloudWatch.
**sp\$1detach\$1schedule**
Removes an association between a schedule and a job.
**get\$1jobs, update\$1job**
Internal procedures that interact with AWS Elastic Beanstalk.
**sp\$1verify\$1job\$1date, sp\$1verify\$1job\$1time, sp\$1verify\$1job, sp\$1verify\$1jobstep, sp\$1verify\$1schedule, sp\$1verify\$1job\$1identifiers, sp\$1verify\$1schedule\$1identifiers**
Internal procedures that check settings.
## Syntax for procedures that emulate SQL Server Agent in PostgreSQL
The `aws_sqlserver_ext.sp_add_job` procedure in the extension pack emulates the `msdb.dbo.sp_add_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-job-transact-sql?view=sql-server-ver15).
```
par_job_name varchar,
par_enabled smallint = 1,
par_description varchar = NULL::character varying,
par_start_step_id integer = 1,
par_category_name varchar = NULL::character varying,
par_category_id integer = NULL::integer,
par_owner_login_name varchar = NULL::character varying,
par_notify_level_eventlog integer = 2,
par_notify_level_email integer = 0,
par_notify_level_netsend integer = 0,
par_notify_level_page integer = 0,
par_notify_email_operator_name varchar = NULL::character varying,
par_notify_netsend_operator_name varchar = NULL::character varying,
par_notify_page_operator_name varchar = NULL::character varying,
par_delete_level integer = 0,
inout par_job_id integer = NULL::integer,
par_originating_server varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sp_add_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_add_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_step_id integer = NULL::integer,
par_step_name varchar = NULL::character varying,
par_subsystem varchar = 'TSQL'::bpchar,
par_command text = NULL::text,
par_additional_parameters text = NULL::text,
par_cmdexec_success_code integer = 0,
par_on_success_action smallint = 1,
par_on_success_step_id integer = 0,
par_on_fail_action smallint = 2,
par_on_fail_step_id integer = 0,
par_server varchar = NULL::character varying,
par_database_name varchar = NULL::character varying,
par_database_user_name varchar = NULL::character varying,
par_retry_attempts integer = 0,
par_retry_interval integer = 0,
par_os_run_priority integer = 0,
par_output_file_name varchar = NULL::character varying,
par_flags integer = 0,
par_proxy_id integer = NULL::integer,
par_proxy_name varchar = NULL::character varying,
inout par_step_uid char = NULL::bpchar,
out returncode integer
```
The `aws_sqlserver_ext.sp_add_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_add_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql?view=sql-server-ver15).
```
par_schedule_name varchar,
par_enabled smallint = 1,
par_freq_type integer = 0,
par_freq_interval integer = 0,
par_freq_subday_type integer = 0,
par_freq_subday_interval integer = 0,
par_freq_relative_interval integer = 0,
par_freq_recurrence_factor integer = 0,
par_active_start_date integer = NULL::integer,
par_active_end_date integer = 99991231,
par_active_start_time integer = 0,
par_active_end_time integer = 235959,
par_owner_login_name varchar = NULL::character varying,
*inout par_schedule_uid char = NULL::bpchar,*
inout par_schedule_id integer = NULL::integer,
par_originating_server varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sp_attach_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_attach_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-schedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_add_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_add_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_name varchar = NULL::character varying,
par_enabled smallint = 1,
par_freq_type integer = 1,
par_freq_interval integer = 0,
par_freq_subday_type integer = 0,
par_freq_subday_interval integer = 0,
par_freq_relative_interval integer = 0,
par_freq_recurrence_factor integer = 0,
par_active_start_date integer = NULL::integer,
par_active_end_date integer = 99991231,
par_active_start_time integer = 0,
par_active_end_time integer = 235959,
inout par_schedule_id integer = NULL::integer,
par_automatic_post smallint = 1,
inout par_schedule_uid char = NULL::bpchar,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_job` procedure in the extension pack emulates the `msdb.dbo.sp_delete_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-job-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_originating_server varchar = NULL::character varying,
par_delete_history smallint = 1,
par_delete_unused_schedule smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_delete_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-jobsteplog-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_step_id integer = NULL::integer,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_delete_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-jobschedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_name varchar = NULL::character varying,
par_keep_schedule integer = 0,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_delete_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-schedule-transact-sql?view=sql-server-ver15).
```
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_force_delete smallint = 0,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_detach_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_detach_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-schedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_delete_unused_schedule smallint = 0,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_update_job` procedure in the extension pack emulates the `msdb.dbo.sp_update_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_description varchar = NULL::character varying
par_start_step_id integer = NULL::integer
par_category_name varchar = NULL::character varying
par_owner_login_name varchar = NULL::character varying
par_notify_level_eventlog integer = NULL::integer
par_notify_level_email integer = NULL::integer
par_notify_level_netsend integer = NULL::integer
par_notify_level_page integer = NULL::integer
par_notify_email_operator_name varchar = NULL::character varying
par_notify_netsend_operator_name varchar = NULL::character varying
par_notify_page_operator_name varchar = NULL::character varying
par_delete_level integer = NULL::integer
par_automatic_post smallint = 1
out returncode integer
```
The `aws_sqlserver_ext.sp_update_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_update_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobschedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_freq_type integer = NULL::integer
par_freq_interval integer = NULL::integer
par_freq_subday_type integer = NULL::integer
par_freq_subday_interval integer = NULL::integer
par_freq_relative_interval integer = NULL::integer
par_freq_recurrence_factor integer = NULL::integer
par_active_start_date integer = NULL::integer
par_active_end_date integer = NULL::integer
par_active_start_time integer = NULL::integer
par_active_end_time integer = NULL::integer
par_automatic_post smallint = 1
out returncode integer
```
The `aws_sqlserver_ext.sp_update_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_update_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_step_id integer = NULL::integer
par_step_name varchar = NULL::character varying
par_subsystem varchar = NULL::character varying
par_command text = NULL::text
par_additional_parameters text = NULL::text
par_cmdexec_success_code integer = NULL::integer
par_on_success_action smallint = NULL::smallint
par_on_success_step_id integer = NULL::integer
par_on_fail_action smallint = NULL::smallint
par_on_fail_step_id integer = NULL::integer
par_server varchar = NULL::character varying
par_database_name varchar = NULL::character varying
par_database_user_name varchar = NULL::character varying
par_retry_attempts integer = NULL::integer
par_retry_interval integer = NULL::integer
par_os_run_priority integer = NULL::integer
par_output_file_name varchar = NULL::character varying
par_flags integer = NULL::integer
par_proxy_id integer = NULL::integer
par_proxy_name varchar = NULL::character varying
out returncode integer
```
The `aws_sqlserver_ext.sp_update_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_update_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-ver15).
```
par_schedule_id integer = NULL::integer
par_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_freq_type integer = NULL::integer
par_freq_interval integer = NULL::integer
par_freq_subday_type integer = NULL::integer
par_freq_subday_interval integer = NULL::integer
par_freq_relative_interval integer = NULL::integer
par_freq_recurrence_factor integer = NULL::integer
par_active_start_date integer = NULL::integer
par_active_end_date integer = NULL::integer
par_active_start_time integer = NULL::integer
par_active_end_time integer = NULL::integer
par_owner_login_name varchar = NULL::character varying
par_automatic_post smallint = 1
out returncode integer
```
## Examples for using procedures that emulate SQL Server Agent in PostgreSQL
To add a new job, use the `aws_sqlserver_ext.sp_add_job` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_job (
par_job_name := 'test_job',
par_enabled := 1::smallint,
par_start_step_id := 1::integer,
par_category_name := '[Uncategorized (Local)]',
par_owner_login_name := 'sa');
```
To add a new job step, use the `aws_sqlserver_ext.sp_add_jobstep` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_jobstep (
par_job_name := 'test_job',
par_step_id := 1::smallint,
par_step_name := 'test_job_step1',
par_subsystem := 'TSQL',
par_command := 'EXECUTE [dbo].[PROC_TEST_JOB_STEP1];',
par_server := NULL,
par_database_name := 'GOLD_TEST_SS');
```
To add a simple schedule, use the `aws_sqlserver_ext.sp_add_schedule` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_schedule(
par_schedule_name := 'RunOnce',
par_freq_type := 1,
par_active_start_time := 233000);
```
To set a schedule for a job, use the `aws_sqlserver_ext.sp_attach_schedule` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_attach_schedule (
par_job_name := 'test_job',
par_schedule_name := 'NightlyJobs');
```
To create a schedule for a job, use the `aws_sqlserver_ext.sp_add_jobschedule` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_jobschedule (
par_job_name := 'test_job2',
par_name := 'test_schedule2',
par_enabled := 1::smallint,
par_freq_type := 4,
par_freq_interval := 1,
par_freq_subday_type := 4,
par_freq_subday_interval := 1,
par_freq_relative_interval := 0,
par_freq_recurrence_factor := 0,
par_active_start_date := 20100801,
par_active_end_date := 99991231,
par_active_start_time := 0,
par_active_end_time := 0);
```
## Use case examples for emulating SQL Server Agent in PostgreSQL
If your source database code uses SQL Server Agent to run jobs, you can use the SQL Server to PostgreSQL extension pack for AWS SCT to convert this code to PostgreSQL. The extension pack uses AWS Lambda functions to emulate the behavior of SQL Server Agent.
You can create a new AWS Lambda function or register an existing function.
**To create a new AWS Lambda function**
1. In AWS SCT, in the target database tree, open the context (right-click) menu, choose **Apply extension pack for**, and then choose **PostgreSQL**.
The extension pack wizard appears.
1. On the **SQL Server Agent emulation service** tab, do the following:
+ Choose **Create an AWS Lambda function**.
+ For **Database login**, enter the name of the target database user.
+ For **Database password**, enter the password for the user name that you entered on the preceding step.
+ For **Python library folder**, enter the path to your Python library folder.
+ Choose **Create AWS Lambda function**, and then choose **Next**.
**To register an AWS Lambda function that you deployed earlier**
+ Run the following script on your target database.
```
SELECT
FROM aws_sqlserver_ext.set_service_setting(
p_service := 'JOB',
p_setting := 'LAMBDA_ARN',
p_value := ARN)
```
In the preceding example, *`ARN`* is the Amazon Resource Name (ARN) of the deployed AWS Lambda function.
The following example creates a simple task that consists of one step. Every five minutes, this task runs the previously created `job_example` function. This function inserts records into the `job_example_table` table.
**To create this simple task**
1. Create a job using the `aws_sqlserver_ext.sp_add_job` function as shown following.
```
SELECT
FROM aws_sqlserver_ext.sp_add_job (
par_job_name := 'test_simple_job');
```
1. Create a job step using the `aws_sqlserver_ext.sp_add_jobstep` function as shown following.
```
SELECT
FROM aws_sqlserver_ext.sp_add_jobstep (
par_job_name := 'test_simple_job',
par_step_name := 'test_simple_job_step1',
par_command := 'PERFORM job_simple_example;');
```
The job step specifies what the function does.
1. Create a scheduler for the job using the `aws_sqlserver_ext.sp_add_jobschedule` function as shown following.
```
SELECT
FROM aws_sqlserver_ext.sp_add_jobschedule (
par_job_name := 'test_simple_job',
par_name := 'test_schedule',
par_freq_type := 4, /* Daily */
par_freq_interval := 1, /* frequency_interval is unused */
par_freq_subday_type := 4, /* Minutes */
par_freq_subday_interval := 5 /* 5 minutes */);
```
The job step specifies what the function does.
To delete this job, use the `aws_sqlserver_ext.sp_delete_job` function as shown following.
```
PERFORM aws_sqlserver_ext.sp_delete_job(
par_job_name := 'PeriodicJob1'::character varying,
par_delete_history := 1::smallint,
par_delete_unused_schedule := 1::smallint);
```
# Using an AWS SCT extension pack to emulate SQL Server Database Mail in PostgreSQL
You can use SQL Server Database Mail to send email messages to users from the SQL Server Database Engine or Azure SQL Managed Instance. These email messages can contain query results or include files from any resource on your network. For more information about SQL Server Database Mail, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail?view=sql-server-ver15).
PostgreSQL doesn't have an equivalent for SQL Server Database Mail. To emulate the SQL Server Database Mail features, AWS SCT creates an extension pack. This extension pack uses AWS Lambda and Amazon Simple Email Service (Amazon SES). AWS Lambda provides users with an interface to interact with Amazon SES email sending service. To set up this interaction, add the Amazon Resource Name (ARN) of your Lambda function.
For a new email account, use the following command.
```
do
$$
begin
PERFORM sysmail_add_account_sp (
par_account_name :='your_account_name',
par_email_address := 'your_account_email',
par_display_name := 'your_account_display_name',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'ARN'
);
end;
$$ language plpgsql;
```
To add the ARN of your Lambda function to the existing email account, use the following command.
```
do
$$
begin
PERFORM sysmail_update_account_sp (
par_account_name :='existind_account_name',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'ARN'
);
end;
$$ language plpgsql;
```
In the preceding examples, *`ARN`* is the ARN of your Lambda function.
To emulate the SQL Server Database Mail behavior in PostgreSQL, the AWS SCT extension pack uses the following tables, views, and procedures.
## Tables that emulate SQL Server Database Mail in PostgreSQL
To emulate SQL Server Database Mail, the extension pack uses the following tables:
**sysmail\$1account**
Stores the information about the email accounts.
**sysmail\$1profile**
Stores the information about the user profiles.
**sysmail\$1server**
Stores the information about the email servers.
**sysmail\$1mailitems**
Stores the list of the email messages.
**sysmail\$1attachments**
Contains one row for each email attachment.
**sysmail\$1log**
Stores the service information about sending email messages.
**sysmail\$1profileaccount**
Stores the information about the user profiles and email accounts.
## Views that emulate SQL Server Database Mail in PostgreSQL
To emulate SQL Server Database Mail, AWS SCT creates the following views in the PostgreSQL database to ensure compatibility. The extension pack doesn't use them, but your converted code can query these views.
**sysmail\$1allitems**
Includes a list of all emails.
**sysmail\$1faileditems**
Includes a list of emails that couldn't be sent.
**sysmail\$1sentitems**
Includes a list of sent emails.
**sysmail\$1unsentitems**
Includes a list of emails that aren't sent yet.
**sysmail\$1mailattachments**
Includes a list of attached files.
## Procedures that emulate SQL Server Database Mail in PostgreSQL
To emulate SQL Server Database Mail, the extension pack uses the following procedures:
**sp\$1send\$1dbmail**
Sends an email to the specified recipients.
**sysmail\$1add\$1profile\$1sp**
Creates a new user profile.
**sysmail\$1add\$1account\$1sp**
Creates a new email account that stores such information as Simple Mail Transfer Protocol (SMTP) credentials, and so on.
**sysmail\$1add\$1profileaccount\$1sp**
Adds an email account to the specified user profile.
**sysmail\$1update\$1profile\$1sp**
Changes the attributes of the user profile such as description, name, and so on.
**sysmail\$1update\$1account\$1sp**
Changes the information in the existing email account.
**sysmail\$1update\$1profileaccount\$1sp**
Updates the email account information in the specified user profile.
**sysmail\$1delete\$1profileaccount\$1sp**
Removes an email account from the specified user profile.
**sysmail\$1delete\$1account\$1sp**
Deletes the email account.
**sysmail\$1delete\$1profile\$1sp**
Deletes the user profile.
**sysmail\$1delete\$1mailitems\$1sp**
Deletes emails from internal tables.
**sysmail\$1help\$1profile\$1sp**
Displays information about the user profile.
**sysmail\$1help\$1account\$1sp**
Displays information about the email account.
**sysmail\$1help\$1profileaccount\$1sp**
Displays information about email accounts associated with the user profile.
**sysmail\$1dbmail\$1json**
An internal procedure that generates JSON requests for AWS Lambda functions.
**sysmail\$1verify\$1profile\$1sp, sysmail\$1verify\$1account\$1sp, sysmail\$1verify\$1addressparams\$1sp**
Internal procedures that check settings.
**sp\$1get\$1dbmail, sp\$1set\$1dbmail, sysmail\$1dbmail\$1xml**
Deprecated internal procedures.
## Syntax for procedures that emulate SQL Server Database Mail in PostgreSQL
The `aws_sqlserver_ext.sp_send_dbmail` procedure in the extension pack emulates the `msdb.dbo.sp_send_dbmail` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15).
```
par_profile_name varchar = NULL::character varying,
par_recipients text = NULL::text,
par_copy_recipients text = NULL::text,
par_blind_copy_recipients text = NULL::text,
par_subject varchar = NULL::character varying,
par_body text = NULL::text,
par_body_format varchar = NULL::character varying,
par_importance varchar = 'NORMAL'::character varying,
par_sensitivity varchar = 'NORMAL'::character varying,
par_file_attachments text = NULL::text,
par_query text = NULL::text,
par_execute_query_database varchar = NULL::character varying,
par_attach_query_result_as_file smallint = 0,
par_query_attachment_filename varchar = NULL::character varying,
par_query_result_header smallint = 1,
par_query_result_width integer = 256,
par_query_result_separator VARCHAR = ' '::character varying,
par_exclude_query_output smallint = 0,
par_append_query_error smallint = 0,
par_query_no_truncate smallint = 0,
par_query_result_no_padding smallint = 0,
out par_mailitem_id integer,
par_from_address text = NULL::text,
par_reply_to text = NULL::text,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_mailitems_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_mailitems_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-mailitems-sp-transact-sql?view=sql-server-ver15).
```
par_sent_before timestamp = NULL::timestamp without time zone,
par_sent_status varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_add_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_name varchar,
par_description varchar = NULL::character varying,
out par_profile_id integer,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_add_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_name varchar
par_email_address varchar
par_display_name varchar = NULL::character varying
par_replyto_address varchar = NULL::character varying
par_description varchar = NULL::character varying
par_mailserver_name varchar = NULL::character varying
par_mailserver_type varchar = 'SMTP'::bpchar
par_port integer = 25
par_username varchar = NULL::character varying
par_password varchar = NULL::character varying
par_use_default_credentials smallint = 0
par_enable_ssl smallint = 0
out par_account_id integer
out returncode integer
```
The `aws_sqlserver_ext.sysmail_add_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_sequence_number integer = NULL::integer,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_help_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_update_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_description varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_force_delete smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_help_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_update_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_email_address varchar = NULL::character varying,
par_display_name varchar = NULL::character varying,
par_replyto_address varchar = NULL::character varying,
par_description varchar = NULL::character varying,
par_mailserver_name varchar = NULL::character varying,
par_mailserver_type varchar = NULL::character varying,
par_port integer = NULL::integer,
par_username varchar = NULL::character varying,
par_password varchar = NULL::character varying,
par_use_default_credentials smallint = NULL::smallint,
par_enable_ssl smallint = NULL::smallint,
par_timeout integer = NULL::integer,
par_no_credential_change smallint = NULL::smallint,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_help_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_update_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_sequence_number integer = NULL::integer,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
## Examples for using procedures that emulate SQL Server Database Mail in PostgreSQL
To send an email, use the `aws_sqlserver_ext.sp_send_dbmail` procedure as shown following.
```
PERFORM sp_send_dbmail (
par_profile_name := 'Administrator',
par_recipients := 'hello@rusgl.info',
par_subject := 'Automated Success Message',
par_body := 'The stored procedure finished'
);
```
The following example shows how to send an email with query results.
```
PERFORM sp_send_dbmail (
par_profile_name := 'Administrator',
par_recipients := 'hello@rusgl.info',
par_subject := 'Account with id = 1',
par_query := 'SELECT COUNT(*)FROM Account WHERE id = 1'
);
```
The following example shows how to send an email with HTML code.
```
DECLARE var_tableHTML TEXT;
SET var_tableHTML := CONCAT(
'Work Order Report
',
'',
'| Work Order ID | Product ID | ',
'Name | Order Qty | Due Date | ',
'Expected Revenue |
|---|
',
'
'
);
PERFORM sp_send_dbmail (
par_recipients := 'hello@rusgl.info',
par_subject := 'Work Order List',
par_body := var_tableHTML,
par_body_format := 'HTML'
);
```
To delete emails, use the `aws_sqlserver_ext.sysmail_delete_mailitems_sp` procedure as shown following.
```
DECLARE var_GETDATE datetime;
SET var_GETDATE = NOW();
PERFORM sysmail_delete_mailitems_sp (
par_sent_before := var_GETDATE
);
```
The following example shows how to delete the oldest emails.
```
PERFORM sysmail_delete_mailitems_sp (
par_sent_before := '31.12.2015'
);
```
The following example shows how to delete all emails that can't be sent.
```
PERFORM sysmail_delete_mailitems_sp (
par_sent_status := 'failed'
);
```
To create a new user profile, use the `aws_sqlserver_ext.sysmail_add_profile_sp` procedure as shown following.
```
PERFORM sysmail_add_profile_sp (
profile_name := 'Administrator',
par_description := 'administrative mail'
);
```
The following example shows how to create a new profile and save the unique profile identifier in a variable.
```
DECLARE var_profileId INT;
SELECT par_profile_id
FROM sysmail_add_profile_sp (
profile_name := 'Administrator',
par_description := ' Profile used for administrative mail.')
INTO var_profileId;
SELECT var_profileId;
```
To create a new email account, use the `aws_sqlserver_ext.sysmail_add_account_sp` procedure as shown following.
```
PERFORM sysmail_add_account_sp (
par_account_name :='Audit Account',
par_email_address := 'dba@rusgl.info',
par_display_name := 'Test Automated Mailer',
par_description := 'Account for administrative e-mail.',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'arn:aws:lambda:us-west-2:555555555555:function:pg_v3'
);
```
To add an email account to the user profile, use the `aws_sqlserver_ext.sysmail_add_profileaccount_sp` procedure as shown following.
```
PERFORM sysmail_add_profileaccount_sp (
par_account_name := 'Administrator',
par_account_name := 'Audit Account',
par_sequence_number := 1
);
```
## Use case examples for emulating SQL Server Database Mail in PostgreSQL
If your source database code uses SQL Server Database Mail to send emails, you can use the AWS SCT extension pack to convert this code to PostgreSQL.
**To send an email from your PostgreSQL database**
1. Create and configure your AWS Lambda function.
1. Apply the AWS SCT extension pack.
1. Create a user profile using the `sysmail_add_profile_sp` function as shown following.
1. Create an email account using the `sysmail_add_account_sp` function as shown following.
1. Add this email account to your user profile using the `sysmail_add_profileaccount_sp` function as shown following.
```
CREATE OR REPLACE FUNCTION aws_sqlserver_ext.
proc_dbmail_settings_msdb()
RETURNS void
AS
$BODY$
BEGIN
PERFORM aws_sqlserver_ext.sysmail_add_profile_sp(
par_profile_name := 'Administrator',
par_description := 'administrative mail'
);
PERFORM aws_sqlserver_ext.sysmail_add_account_sp(
par_account_name := 'Audit Account',
par_description := 'Account for administrative e-mail.',
par_email_address := 'dba@rusgl.info',
par_display_name := 'Test Automated Mailer',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'your_ARN'
);
PERFORM aws_sqlserver_ext.sysmail_add_profileaccount_sp(
par_profile_name := 'Administrator',
par_account_name := 'Audit Account',
par_sequence_number := 1
);
END;
$BODY$
LANGUAGE plpgsql;
```
1. Send an email using the `sp_send_dbmail` function as shown following.
```
CREATE OR REPLACE FUNCTION aws_sqlserver_ext.
proc_dbmail_send_msdb()
RETURNS void
AS
$BODY$
BEGIN
PERFORM aws_sqlserver_ext.sp_send_dbmail(
par_profile_name := 'Administrator',
par_recipients := 'hello@rusgl.info',
par_body := 'The stored procedure finished',
par_subject := 'Automated Success Message'
);
END;
$BODY$
LANGUAGE plpgsql;
```
To view the information about all user profiles, use the `sysmail_help_profile_sp` procedure as shown following.
```
SELECT FROM aws_sqlserver_ext.sysmail_help_profile_sp();
```
The following example displays the information about the specific user profile.
```
select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_id := 1);
select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_name := 'Administrator');
```
To view the information about all email accounts, use the `sysmail_help_account_sp` procedure as shown following.
```
select from aws_sqlserver_ext.sysmail_help_account_sp();
```
The following example displays the information about the specific email account.
```
select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_id := 1);
select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_name := 'Audit Account');
```
To view the information about all email accounts that are associated with the user profiles, use the `sysmail_help_profileaccount_sp` procedure as shown following.
```
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp();
```
The following example filters the records by identifier, profile name, or account name.
```
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1);
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1, par_account_id := 1);
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_name := 'Administrator');
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_account_name := 'Audit Account');
```
To change the user profile name or description, use the `sysmail_update_profile_sp` procedure as shown following.
```
select aws_sqlserver_ext.sysmail_update_profile_sp(
par_profile_id := 2,
par_profile_name := 'New profile name'
);
```
To change the email account settings, use the `ysmail_update_account_sp` procedure as shown following.
```
select from aws_sqlserver_ext.sysmail_update_account_sp (
par_account_name := 'Audit Account',
par_mailserver_name := 'arn:aws:lambda:region:XXXXXXXXXXXX:function:func_test',
par_mailserver_type := 'AWSLAMBDA'
);
```
# Migrating from SQL Server to Amazon RDS for SQL Server with AWS Schema Conversion Tool
Some things to consider when migrating SQL Server schema and code to Amazon RDS for SQL Server:
+ AWS SCT can convert SQL Server Agent to provide schedules, alerts, and jobs on an Amazon RDS for SQL Server DB instance. After conversion, you can use an Amazon RDS for SQL Server DB instance with SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), and SQL Server Integration Services (SSIS).
+ Amazon RDS currently doesn’t support SQL Server Service Broker or additional T-SQL endpoints that require you to run the CREATE ENDPOINT command.
+ Amazon RDS has limited support for linked servers. When converting SQL Server application code that uses linked servers, AWS SCT converts the application code. However, make sure to review the behavior of objects that use linked servers before you run the converted code.
+ Always on is used.
+ The AWS SCT assessment report provides server metrics for the conversion. These metrics about your SQL Server instance include the following:
+ Data mirroring is used.
+ SQL Server Log Shipping is configured.
+ Failover cluster is used.
+ Database Mail is configured.
+ Full Text Search Service is used. Amazon RDS for SQL Server has a limited full text search, and it does not support semantic search.
+ Data Quality Service (DQS) is installed. Amazon RDS doesn't support DQS so we recommend that you install SQL Server on an Amazon EC2 instance.
## Privileges for RDS for SQL Server as a target
To migrate to RDS for SQL Server, create a database user and then grant the required privileges for each database. You can use the following code example.
```
CREATE LOGIN user_name WITH PASSWORD 'your_password';
USE db_name
CREATE USER user_name FOR LOGIN user_name
GRANT VIEW DEFINITION TO user_name
GRANT VIEW DATABASE STATE TO user_name
GRANT CREATE SCHEMA TO user_name;
GRANT CREATE TABLE TO user_name;
GRANT CREATE VIEW TO user_name;
GRANT CREATE TYPE TO user_name;
GRANT CREATE DEFAULT TO user_name;
GRANT CREATE FUNCTION TO user_name;
GRANT CREATE PROCEDURE TO user_name;
GRANT CREATE ASSEMBLY TO user_name;
GRANT CREATE AGGREGATE TO user_name;
GRANT CREATE FULLTEXT CATALOG TO user_name;
GRANT CREATE SYNONYM TO user_name;
GRANT CREATE XML SCHEMA COLLECTION TO user_name;
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *db\$1name* with the name of your target database. Finally, replace *your\$1password* with a secure password.
# Data warehouse sources for AWS Schema Conversion Tool
AWS SCT can convert schemas for the following source data warehouses to a supported target. For information about permissions, connections, and what AWS SCT can convert for use with the target database or data warehouse, see details in the following.
**Topics**
+ [Connecting Amazon Redshift with the AWS Schema Conversion Tool](CHAP_Source.Redshift.md)
+ [Connecting Azure Synapse Analytics with AWS Schema Conversion Tool](CHAP_Source.AzureSynapse.md)
+ [Connecting to Google BigQuery with AWS Schema Conversion Tool](CHAP_Source.BigQuery.md)
+ [Connecting Greenplum Database with AWS Schema Conversion Tool](CHAP_Source.Greenplum.md)
+ [Connecting to Netezza with AWS Schema Conversion Tool](CHAP_Source.Netezza.md)
+ [Connecting Oracle Data Warehouse with AWS SCT](CHAP_Source.OracleDW.md)
+ [Connecting to a Snowflake data warehouse with AWS Schema Conversion Tool](CHAP_Source.Snowflake.md)
+ [Connecting to a SQL Server Data Warehouse with the AWS Schema Conversion Tool](CHAP_Source.SQLServerDW.md)
+ [Connecting to a Teradata Data Warehouse with the AWS Schema Conversion Tool](CHAP_Source.Teradata.md)
+ [Connecting the AWS Schema Conversion Tool to Vertica databases](CHAP_Source.Vertica.md)
# Connecting Amazon Redshift with the AWS Schema Conversion Tool
You can use AWS SCT to optimize your Amazon Redshift cluster. AWS SCT provides you with recommendations on the selection of distribution and sort keys for your Amazon Redshift cluster. You can consider the Amazon Redshift optimization project as an AWS SCT project with the source and target pointing to the different Amazon Redshift clusters.
## Privileges for Amazon Redshift as a source database
The following privileges are required for using Amazon Redshift as a source:
+ USAGE ON SCHEMA **
+ SELECT ON ALL TABLES IN SCHEMA **
+ SELECT ON PG\$1CATALOG.PG\$1STATISTIC
+ SELECT ON SVV\$1TABLE\$1INFO
+ SELECT ON TABLE STV\$1BLOCKLIST
+ SELECT ON TABLE STV\$1TBL\$1PERM
+ SELECT ON SYS\$1SERVERLESS\$1USAGE
+ SELECT ON PG\$1DATABASE\$1INFO
+ SELECT ON PG\$1STATISTIC
In the preceding examples, replace the ** placeholder with the name of the source schema.
For the privileges required for Amazon Redshift as a target, see [Permissions for Amazon Redshift as a target](CHAP_Converting.DW.md#CHAP_Converting.DW.ConfigureTarget).
## Connecting to Amazon Redshift as a source
Use the following procedure to connect to your Amazon Redshift source database with the AWS Schema Conversion Tool.
**To connect to an Amazon Redshift source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Amazon Redshift**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the connection information for the Amazon Redshift source database, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Redshift.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Amazon Redshift optimization settings
To edit Amazon Redshift optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Amazon Redshift**, and then choose **Amazon Redshift – Amazon Redshift**. AWS SCT displays all available settings for Amazon Redshift optimization.
Amazon Redshift optimization 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if the number of tables is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To choose the migration strategy.
AWS recommends using different clusters as a source and target for your optimization project. Before the start of the Amazon Redshift optimization process, you create a copy of your source Amazon Redshift cluster. You can include your source data into this copy or create an empty cluster.
For **Migration strategy**, choose **Migration to a copy** to include data from your source cluster in the target cluster.
For **Migration strategy**, choose **Migration to a clean slate** to review the optimization suggestions. After you accept these suggestions, migrate your source data to the target cluster.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you selected the **Use compression encoding** option.
+ To work with automatic table optimization.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To use only on the automatic table optimization, choose **Optimization strategies** in the left pane. Then select **Use Amazon Redshift automatic table tuning**, and choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with a skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user whose query statistics you want to analyze.
# Connecting Azure Synapse Analytics with AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from Azure Synapse Analytics to Amazon Redshift.
## Privileges for Azure Synapse Analytics as a source database
The following privileges are required for using an Azure Synapse Analytics data warehouse as a source:
+ VIEW DEFINITION
+ VIEW DATABASE STATE
Apply the privileges for each database whose schema you are converting.
## Connecting to Azure Synapse Analytics as a source
Use the following procedure to connect to your Azure Synapse Analytics data warehouse with the AWS Schema Conversion Tool.
**To connect to an Azure Synapse Analytics data warehouse as a source**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Azure Synapse Analytics**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the connection information for the Azure Synapse Analytics data warehouse manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.AzureSynapse.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Azure Synapse Analytics to Amazon Redshift conversion settings
To edit Azure Synapse Analytics to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Azure Synapse**, and then choose **Azure Synapse – Amazon Redshift**. AWS SCT displays all available settings for Azure Synapse Analytics to Amazon Redshift conversion.
Azure Synapse Analytics to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To migrate partitions of the source table to separate tables in Amazon Redshift. To do so, select **Use the UNION ALL view** and enter the maximum number of target tables that AWS SCT can create for a single source table.
Amazon Redshift doesn't support table partitioning. To emulate this behavior and make queries run faster, AWS SCT can migrate each partition of your source table to a separate table in Amazon Redshift. Then, AWS SCT creates a view that includes data from all these tables.
AWS SCT automatically determines the number of partitions in your source table. Depending on the type of source table partitioning, this number can exceed the quota for the tables that you can apply to your Amazon Redshift cluster. To avoid reaching this quota, enter the maximum number of target tables that AWS SCT can create for partitions of a single source table. The default option is 368 tables, which represents a partition for 366 days of a year and two tables for `NO RANGE` and `UNKNOWN` partitions.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## Azure Synapse Analytics to Amazon Redshift conversion optimization settings
To edit Azure Synapse Analytics to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Azure Synapse**, and then choose **Azure Synapse – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for Azure Synapse Analytics to Amazon Redshift conversion.
Azure Synapse Analytics to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting to Google BigQuery with AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from BigQuery to Amazon Redshift.
## Privileges for BigQuery as a source
To use a BigQuery data warehouse as a source in AWS SCT, create a service account. In Google Cloud, applications use service accounts to make authorized API calls. Service accounts differ from user accounts. For more information, see [Service accounts](https://cloud.google.com/iam/docs/service-accounts) in the Google Cloud Identity and Access Management documentation.
Make sure that you grant the following roles to your service account:
+ `BigQuery Admin`
+ `Storage Admin`
The `BigQuery Admin` role provides permissions to manage all resources within the project. AWS SCT uses this role to load your BigQuery metadata in the migration project.
The `Storage Admin` role grants full control of data objects and buckets. You can find this role under `Cloud Storage`. AWS SCT uses this role to extract your data from BigQuery and then load it into Amazon Redshift.
**To create a service account key file**
1. Sign in to the Google Cloud management console at [https://console.cloud.google.com/](https://console.cloud.google.com/).
1. On the [BigQuery API](https://console.cloud.google.com/apis/library/bigquery.googleapis.com) page, choose **Enable**. Skip this step if you see **API Enabled**.
1. On the [Service accounts](https://console.cloud.google.com/iam-admin/serviceaccounts) page, choose your project, and then choose **Create service account**.
1. On the **Service account details** page, enter a descriptive value for **Service account name**. Choose **Create and continue**. The **Grant this service account access to the project** page opens.
1. For **Select a role**, choose **BigQuery**, and then choose **BigQuery Admin**.
1. Choose **Add another role**. For **Select a role**, choose **Cloud Storage**, and then choose **Storage Admin**.
1. Choose **Continue**, and then choose **Done**.
1. On the [Service accounts](https://console.cloud.google.com/iam-admin/serviceaccounts) page, choose the service account that you created.
1. Choose **Keys**, and then choose **Create new key** for **Add key**.
1. Choose **JSON**, and then choose **Create**. Choose the folder to save your private key or select the default folder for downloads in your browser.
To extract data from a BigQuery data warehouse, AWS SCT uses the Google Cloud Storage bucket folder. Create this bucket before you start data migration. Enter the path to your Google Cloud Storage bucket folder in the **Create Local task** dialog box. For more information, see [Creating, running, and monitoring an AWS SCT task](agents.md#agents.Tasks).
## Connecting to BigQuery as a source
Use the following procedure to connect to your source BigQuery project with the AWS Schema Conversion Tool.
**To connect to a BigQuery source data warehouse**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **BigQuery**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your BigQuery project. AWS SCT displays this name in the tree in the left panel.
1. For **Key path**, enter the path to the service account key file. For more information about creating this file, see [Privileges for BigQuery as a source](#CHAP_Source.BigQuery.Permissions).
1. Choose **Test Connection** to verify that AWS SCT can connect to your source BigQuery project.
1. Choose **Connect** to connect to your source BigQuery project.
## Limitations on using BigQuery as a source for AWS SCT
The following limitations apply when using BigQuery as a source for AWS SCT:
+ AWS SCT doesn't support the conversion of subqueries in analytic functions.
+ You can't use AWS SCT to convert BigQuery `SELECT AS STRUCT` and `SELECT AS VALUE` statements.
+ AWS SCT doesn't support the conversion of the following types of functions:
+ Approximate aggregate
+ Bit
+ Debugging
+ Federated query
+ Geography
+ Hash
+ Mathematical
+ Net
+ Statistical aggregate
+ UUID
+ AWS SCT provides limited support for the conversion of string functions.
+ AWS SCT doesn't support the conversion of `UNNEST` operators.
+ You can't convert correlated join operations in AWS SCT.
+ AWS SCT doesn't support the conversion of `QUALIFY`, `WINDOW`, `LIMIT`, and `OFFSET` clauses.
+ You can't use AWS SCT to convert recursive common table expressions.
+ AWS SCT doesn't support the conversion of `INSERT` statements with subqueries inside `VALUES` clauses.
+ AWS SCT doesn't support the conversion of `UPDATE` statements for nested fields and repeated records.
+ You can't use AWS SCT to convert `STRUCT` and `ARRAY` data types.
## BigQuery to Amazon Redshift conversion settings
To edit BigQuery to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Google BigQuery**, and then choose **Google BigQuery – Amazon Redshift**. AWS SCT displays all available settings for BigQuery to Amazon Redshift conversion.
BigQuery to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## BigQuery to Amazon Redshift conversion optimization settings
To edit BigQuery to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Google BigQuery**, and then choose **Google BigQuery – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for BigQuery to Amazon Redshift conversion.
BigQuery to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting Greenplum Database with AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from Greenplum Database to Amazon Redshift.
## Privileges for Greenplum Database as a source
The following privileges are required for using Greenplum Database as a source:
+ CONNECT ON DATABASE **
+ USAGE ON SCHEMA **
+ SELECT ON *.*
+ SELECT ON SEQUENCE *.*
In the preceding example, replace placeholders as following:
+ Replace *database\$1name* with the name of the source database.
+ Replace *schema\$1name* with the name of the source schema.
+ Replace *table\$1name* with the name of the source table.
+ Replace *sequence\$1name* with the name of the sequence name.
## Connecting to Greenplum Database as a source
Use the following procedure to connect to your Greenplum source database with AWS SCT.
**To connect to a Greenplum source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **SAP ASE**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Greenplum source database credentials manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Greenplum.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Greenplum to Amazon Redshift conversion settings
To edit Greenplum to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Greenplum**, and then choose **Greenplum – Amazon Redshift**. AWS SCT displays all available settings for Greenplum to Amazon Redshift conversion.
Greenplum to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To migrate partitions of the source table to separate tables in Amazon Redshift. To do so, select **Use the UNION ALL view** and enter the maximum number of target tables that AWS SCT can create for a single source table.
Amazon Redshift doesn't support table partitioning. To emulate this behavior and make queries run faster, AWS SCT can migrate each partition of your source table to a separate table in Amazon Redshift. Then, AWS SCT creates a view that includes data from all these tables.
AWS SCT automatically determines the number of partitions in your source table. Depending on the type of source table partitioning, this number can exceed the quota for the tables that you can apply to your Amazon Redshift cluster. To avoid reaching this quota, enter the maximum number of target tables that AWS SCT can create for partitions of a single source table. The default option is 368 tables, which represents a partition for 366 days of a year and two tables for `NO RANGE` and `UNKNOWN` partitions.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## Greenplum to Amazon Redshift conversion optimization settings
To edit Greenplum to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Greenplum**, and then choose **Greenplum – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for Greenplum to Amazon Redshift conversion.
Greenplum to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting to Netezza with AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from Netezza to Amazon Redshift.
## Privileges for Netezza as a source
The following privileges are required for using Netezza as a source:
+ select on system.definition\$1schema.system view
+ select on system.definition\$1schema.system table
+ select on system.definition\$1schema.management table
+ list on **
+ list on **
+ list on **.all.table
+ list on **.all.external table
+ list on **.all.view
+ list on **.all.materialized view
+ list on **.all.procedure
+ list on **.all.sequence
+ list on **.all.function
+ list on **.all.aggregate
In the preceding example, replace placeholders as following:
+ Replace *database\$1name* with the name of the source database.
+ Replace *schema\$1name* with the name of the source schema.
AWS SCT requires access to the following system tables and views. You can grant access to these objects instead of granting access to `system.definition_schema.system view` and `system.definition_schema.system tables` in the preceding list.
+ select on system.definition\$1schema.\$1t\$1aggregate
+ select on system.definition\$1schema.\$1t\$1class
+ select on system.definition\$1schema.\$1t\$1constraint
+ select on system.definition\$1schema.\$1t\$1const\$1relattr
+ select on system.definition\$1schema.\$1t\$1database
+ select on system.definition\$1schema.\$1t\$1grpobj\$1priv
+ select on system.definition\$1schema.\$1t\$1grpusr
+ select on system.definition\$1schema.\$1t\$1hist\$1config
+ select on system.definition\$1schema.\$1t\$1object
+ select on system.definition\$1schema.\$1t\$1object\$1classes
+ select on system.definition\$1schema.\$1t\$1proc
+ select on system.definition\$1schema.\$1t\$1type
+ select on system.definition\$1schema.\$1t\$1user
+ select on system.definition\$1schema.\$1t\$1usrobj\$1priv
+ select on system.definition\$1schema.\$1vt\$1sequence
+ select on system.definition\$1schema.\$1v\$1aggregate
+ select on system.definition\$1schema.\$1v\$1constraint\$1depends
+ select on system.definition\$1schema.\$1v\$1database
+ select on system.definition\$1schema.\$1v\$1datatype
+ select on system.definition\$1schema.\$1v\$1dslice
+ select on system.definition\$1schema.\$1v\$1function
+ select on system.definition\$1schema.\$1v\$1group
+ select on system.definition\$1schema.\$1v\$1obj\$1relation
+ select on system.definition\$1schema.\$1v\$1obj\$1relation\$1xdb
+ select on system.definition\$1schema.\$1v\$1procedure
+ select on system.definition\$1schema.\$1v\$1relation\$1column
+ select on system.definition\$1schema.\$1v\$1relation\$1keydata
+ select on system.definition\$1schema.\$1v\$1relobjclasses
+ select on system.definition\$1schema.\$1v\$1schema\$1xdb
+ select on system.definition\$1schema.\$1v\$1sequence
+ select on system.definition\$1schema.\$1v\$1synonym
+ select on system.definition\$1schema.\$1v\$1system\$1info
+ select on system.definition\$1schema.\$1v\$1sys\$1constraint
+ select on system.definition\$1schema.\$1v\$1sys\$1object\$1dslice\$1info
+ select on system.definition\$1schema.\$1v\$1sys\$1user
+ select on system.definition\$1schema.\$1v\$1table
+ select on system.definition\$1schema.\$1v\$1table\$1constraint
+ select on system.definition\$1schema.\$1v\$1table\$1dist\$1map
+ select on system.definition\$1schema.\$1v\$1table\$1organize\$1column
+ select on system.definition\$1schema.\$1v\$1table\$1storage\$1stat
+ select on system.definition\$1schema.\$1v\$1user
+ select on system.definition\$1schema.\$1v\$1view
+ select on system.information\$1schema.\$1v\$1relation\$1column
+ select on system.information\$1schema.\$1v\$1table
+ select on \$1hist\$1column\$1access\$1\$1
## Connecting to Netezza as a source
Use the following procedure to connect to your Netezza source database with the AWS Schema Conversion Tool.
**To connect to a Netezza source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Netezza**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Netezza source database connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Netezza.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Configuring ongoing data replication
After you convert your Netezza database schemas and apply them to your Amazon Redshift database, you can migrate data with AWS SCT data extraction agents. The agent extracts your data and uploads it to your Amazon S3 bucket. You can then use AWS SCT to copy the data from Amazon S3 to Amazon Redshift.
If data in your source database changes during the migration process, you can capture ongoing changes with your AWS SCT data extraction agents. Then you can replicate these ongoing changes in your target database after you complete the initial data migration. This process is called ongoing data replication or *change data capture* (CDC).
**To configure ongoing data replication for migrations from Netezza to Amazon Redshift**
1. In your source database, create a history database. You can use the following code example in the Netezza command line interface (CLI).
```
nzhistcreatedb -d history_database_name -t query -v 1 -u load_user -o histdb_owner -p your_password
```
In the preceding example, replace *history\$1database\$1name* with the name of your history database. Next, replace *load\$1user* with the name of the user that you have defined to load history data to the database. Then, replace *histdb\$1owner* with the name of the user that you have defined as the owner of the history database. Make sure that you have already created this user and granted the `CREATE DATABASE` permission. Finally, replace *your\$1password* with a secure password.
1. Configure the history logging. To do so, use the following code example.
```
CREATE HISTORY CONFIGURATION history_configuration_name HISTTYPE QUERY
DATABASE history_database_name USER load_user PASSWORD your_password COLLECT PLAN, COLUMN
LOADINTERVAL 1 LOADMINTHRESHOLD 0 LOADMAXTHRESHOLD 0 STORAGELIMIT 25
LOADRETRY 2 VERSION 1;
```
In the preceding example, replace *history\$1configuration\$1name* and *history\$1database\$1name* with the names of your history configuration and your history database. Next, replace *load\$1user* with the name of the user that you have defined to load history data to the database. Then, replace *your\$1password* with a secure password.
1. Grant read permissions for all tables in the history database. You can use the following code example to grant the `SELECT` permission.
```
GRANT SELECT ON history_database_name.ALL.TABLE TO your_user;
```
In the preceding example, replace *history\$1database\$1name* with the name of your history database. Next, replace *your\$1user* with the name of the user with minimal permissions to work with your Netezza database. You use the credentials of this database user in AWS SCT.
1. Collect statistics for each table in your source schema to get the information about the cardinality of columns. You can use the following command to generate statistics in your history database.
```
GENERATE STATISTICS on "schema_name"."table_name";
```
In the preceding example, replace *schema\$1name* and *table\$1name* with the name of your database schema and table.
1. Make sure that you completed the prerequisites by running the following query:
```
SELECT COUNT(*) FROM history_database_name.history_schema_name."$hist_column_access_N";
```
In the preceding example, replace *history\$1database\$1name* and *history\$1schema\$1name* with the name of your history database and schema. Next, replace *N* with the the version number of your history database. For more information about history database versions, see the [IBM Netezza Documentation](https://www.ibm.com/docs/en/netezza?topic=history-database-versions).
1. Install your data extraction agents. For more information, see [Installing extraction agents](agents.md#agents.Installing).
Make sure that the `{working.folder}` parameter in the `settings.properties` file for all extractor instances points to the same folder. In this case, your extractors can coordinate the CDC session and use a single transaction point for all subtasks.
1. Register your data extraction agent. For more information, see [Registering extraction agents with the AWS Schema Conversion Tool](agents.md#agents.Using).
1. Create your CDC task. For more information, see [Creating, running, and monitoring an AWS SCT task](agents.md#agents.Tasks).
1. Open your project in AWS SCT. In the left pane, choose your source table. Open the context (right-click) menu, and choose **Create local task**.
1. For **Task name**, enter a descriptive name for your data migration task.
1. For **Migration mode**, choose **Extract, upload, and copy**.
1. Select **Enable CDC**.
1. Choose the **CDC settings** tab and define the scope and the schedule of CDC sessions.
1. Choose **Test task** to verify that you can connect to your working folder, Amazon S3 bucket, and Amazon Redshift data warehouse.
1. Choose **Create** to create your task.
1. Choose the **Tasks** tab, choose your task from the list, and choose **Start**.
1. The AWS SCT task maintains transactional consistency on the target database. The data extraction agent replicates transactions from the source in transaction ID order.
If you stop any of the migration sessions or if it fails, then the CDC processing also stops.
## Netezza to Amazon Redshift conversion settings
To edit Netezza to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Netezza**, and then choose **Netezza – Amazon Redshift**. AWS SCT displays all available settings for Netezza to Amazon Redshift conversion.
Netezza to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## Netezza to Amazon Redshift conversion optimization settings
To edit Netezza to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Netezza**, and then choose **Netezza – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for Netezza to Amazon Redshift conversion.
Netezza to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting Oracle Data Warehouse with AWS SCT
You can use AWS SCT to convert schemas, code objects, and application code from Oracle Data Warehouse to Amazon Redshift or Amazon Redshift and AWS Glue used in combination.
## Privileges for Oracle Data Warehouse as a source
The following privileges are required for using Oracle Data Warehouse as a source:
+ connect
+ select\$1catalog\$1role
+ select any dictionary
## Connecting to Oracle Data Warehouse as a source
Use the following procedure to connect to your Oracle data warehouse source database with the AWS Schema Conversion Tool.
**To connect to an Oracle Data Warehouse source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Oracle**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Oracle source data warehouse connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.OracleDW.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Oracle Data Warehouse to Amazon Redshift conversion settings
To edit Oracle Data Warehouse to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Oracle**, and then choose **Oracle – Amazon Redshift**. AWS SCT displays all available settings for Oracle Data Warehouse to Amazon Redshift conversion.
Oracle Data Warehouse to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To migrate partitions of the source table to separate tables in Amazon Redshift. To do so, select **Use the UNION ALL view** and enter the maximum number of target tables that AWS SCT can create for a single source table.
Amazon Redshift doesn't support table partitioning. To emulate this behavior and make queries run faster, AWS SCT can migrate each partition of your source table to a separate table in Amazon Redshift. Then, AWS SCT creates a view that includes data from all these tables.
AWS SCT automatically determines the number of partitions in your source table. Depending on the type of source table partitioning, this number can exceed the quota for the tables that you can apply to your Amazon Redshift cluster. To avoid reaching this quota, enter the maximum number of target tables that AWS SCT can create for partitions of a single source table. The default option is 368 tables, which represents a partition for 366 days of a year and two tables for `NO RANGE` and `UNKNOWN` partitions.
+ To convert the data type formatting functions such as `TO_CHAR`, `TO_DATE`, and `TO_NUMBER` with datetime format elements that Amazon Redshift doesn't support. By default, AWS SCT uses the extension pack functions to emulate the usage of these unsupported format elements in the converted code.
The datetime format model in Oracle includes more elements compared to datetime format strings in Amazon Redshift. When your source code includes only datetime format elements that Amazon Redshift supports, you don't need the extension pack functions in the converted code. To avoid using the extension pack functions in the converted code, select **Datetype format elements that you use in the Oracle code are similar to datetime format strings in Amazon Redshift**. In this case, the converted code works faster.
The numeric format model in Oracle includes more elements compared to numeric format strings in Amazon Redshift. When your source code includes only numeric format elements that Amazon Redshift supports, you don't need the extension pack functions in the converted code. To avoid using the extension pack functions in the converted code, select **Numeric format elements that you use in the Oracle code are similar to numeric format strings in Amazon Redshift**. In this case, the converted code works faster.
+ To convert Oracle `LEAD` and `LAG` analytic functions. By default, AWS SCT raises an action item for each `LEAD` and `LAG` function.
When your source code doesn't use the default values for offset in these functions, AWS SCT can emulate the usage of these functions with the `NVL` function. To do so, select **Use the NVL function to emulate the behavior of Oracle LEAD and LAG functions**.
+ To emulate the behavior of primary and unique keys in your Amazon Redshift cluster, select **Emulate the behavior of primary and unique keys**.
Amazon Redshift doesn't enforce unique and primary keys and uses them for informational purposes only. If you use these constraints in your code, then make sure that AWS SCT emulates their behavior in the converted code.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## Oracle Data Warehouse to Amazon Redshift conversion optimization settings
To edit Oracle Data Warehouse to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Oracle**, and then choose **Oracle – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for Oracle Data Warehouse to Amazon Redshift conversion.
Oracle Data Warehouse to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting to a Snowflake data warehouse with AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from Snowflake to Amazon Redshift.
## Privileges for Snowflake as a source database
You can create a role with privileges and grant this role the name of a user by using the `SECURITYADMIN` role and the `SECURITYADMIN` session context.
The example following creates minimal privileges and grants them to the `min_privs` user.
```
create role role_name;
grant role role_name to role sysadmin;
grant usage on database db_name to role role_name;
grant usage on schema db_name.schema_name to role role_name;
grant usage on warehouse datawarehouse_name to role role_name;
grant monitor on database db_name to role role_name;
grant monitor on warehouse datawarehouse_name to role role_name;
grant select on all tables in schema db_name.schema_name to role role_name;
grant select on future tables in schema db_name.schema_name to role role_name;
grant select on all views in schema db_name.schema_name to role role_name;
grant select on future views in schema db_name.schema_name to role role_name;
grant select on all external tables in schema db_name.schema_name to role role_name;
grant select on future external tables in schema db_name.schema_name to role role_name;
grant usage on all sequences in schema db_name.schema_name to role role_name;
grant usage on future sequences in schema db_name.schema_name to role role_name;
grant usage on all functions in schema db_name.schema_name to role role_name;
grant usage on future functions in schema db_name.schema_name to role role_name;
grant usage on all procedures in schema db_name.schema_name to role role_name;
grant usage on future procedures in schema db_name.schema_name to role role_name;
create user min_privs password='real_user_password'
DEFAULT_ROLE = role_name DEFAULT_WAREHOUSE = 'datawarehouse_name';
grant role role_name to user min_privs;
```
In the preceding example, replace placeholders as following:
+ Replace *`role_name`* with the name of a role with read-only privileges.
+ Replace `db_name` with the name of the source database.
+ Replace `schema_name` with the name of the source schema.
+ Replace *`datawarehousename`* with the name of a required data warehouse.
+ Replace `min_privs` with the name of a user that has minimal privileges.
The `DEFAULT_ROLE` and `DEFAULT_WAREHOUSE` parameters are key-sensitive.
## Configuring secure access to Amazon S3
Security and access management policies for an Amazon S3 bucket allow Snowflake to access, read data from, and write data to the S3 bucket. You can configure secure access to a private Amazon S3 bucket using the Snowflake `STORAGE INTEGRATION` object type. A Snowflake storage integration object delegates authentication responsibility to a Snowflake identity and access management entity.
For more information, see [Configuring a Snowflake Storage Integration to Access Amazon S3](https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration.html) in the Snowflake documentation.
## Connecting to Snowflake as a source
Use the following procedure to connect to your source database with the AWS Schema Conversion Tool.
**To connect to an Snowflake source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Snowflake**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Snowflake source data warehouse connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Snowflake.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Limitations for Snowflake as a source
The following are limitations when using Snowflake as a source for AWS SCT:
+ Object identifiers must be unique within the context of the object type and the parent object:
**Database**
Schema identifiers must be unique within a database.
**Schemas**
Objects identifiers such as for tables and views must be unique within a schema.
**Tables/Views**
Column identifiers must be unique within a table.
+ The maximum number of tables for large and xlarge cluster node types is 9,900. For 8xlarge cluster node types, the maximum number of tables is 100,000. The limit includes temporary tables, both user-defined and created by Amazon Redshift during query processing or system maintenance. For more information, see [Amazon Redshift quotas](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Cluster Management Guide*.
+ For stored procedures, the maximum number of input and output arguments is 32.
## Source data types for Snowflake
Following, you can find the Snowflake source data types that are supported when using AWS SCT and the default mapping to an Amazon Redshift target.
| Snowflake data types | Amazon Redshift data types |
| --- | --- |
| NUMBER | NUMERIC(38) |
| NUMBER(p) | If p is =< 4, then SMALLINT If p is => 5 and =< 9, then INTEGER If p is => 10 and =< 18, then BIGINT If p is => 19 then NUMERIC(p) |
| NUMBER(p, 0) | If p is =< 4, then SMALLINT If p is => 5 and =< 9, then INTEGER If p is => 10 and =< 18, then BIGINT If p is => 19 then: NUMERIC(p,0) |
| NUMBER(p, s) | If p is => 1 and =< 38, and if s is => 1 and =< 37, then NUMERIC(p,s) |
| FLOAT | FLOAT |
| TEXT Unicode characters up to 16,777,216 bytes; up to 4 bytes per character. | VARCHAR(MAX) |
| TEXT(p) Unicode characters up to 65,535 bytes; up to 4 bytes per character. | If p is =< 65,535 then, VARCHAR(p) |
| TEXT(p) Unicode characters up to 16,777,216 bytes; up to 4 bytes per character. | If p is => 65,535 and =< 16,777,216 then, VARCHAR(MAX) |
| BINARY Single-byte characters up to 8,388,608 bytes; 1 byte per character. | VARCHAR(MAX) |
| BINARY(p) Single-byte characters up to 65,535 bytes; 1 byte per character. | VARCHAR(p) |
| BINARY(p) Single-byte characters up to 8,388,608 bytes; 1 byte per character. | VARCHAR(MAX) |
| BOOLEAN | BOOLEAN |
| DATE | DATE |
| TIME Time values between 00:00:00 and 23:59:59.999999999. | VARCHAR(18) |
| TIME(f) Time values between 00:00:00 and 23:59:59.9(f). | VARCHAR(n) – 9 \$1 dt-attr-1 |
| TIMESTAMP\$1NTZ | TIMESTAMP |
| TIMESTAMP\$1TZ | TIMESTAMPTZ |
## Snowflake to Amazon Redshift conversion settings
To edit Snowflake to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Snowflake**, and then choose **Snowflake – Amazon Redshift**. AWS SCT displays all available settings for Snowflake to Amazon Redshift conversion.
Snowflake to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## Snowflake to Amazon Redshift conversion optimization settings
To edit Snowflake to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Snowflake**, and then choose **Snowflake – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for Snowflake to Amazon Redshift conversion.
Snowflake to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting to a SQL Server Data Warehouse with the AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from Microsoft SQL Server DW to Amazon Redshift or Amazon Redshift and AWS Glue used in combination.
## Privileges for Microsoft SQL Server Data Warehouse as a source
The following privileges are required for using Microsoft SQL Server data warehouse as a source:
+ VIEW DEFINITION
+ VIEW DATABASE STATE
+ SELECT ON SCHEMA :: **
In the preceding example, replace the ** placeholder with the name of the source source\$1schema.
Repeat the grant for each database whose schema you are converting.
In addition, grant the following, and run the grant on the master database:
+ VIEW SERVER STATE
## Limitations for SQL Server Data Warehouse as a source
Using Microsoft SQL Server Parallel Data Warehouse (PDW) as a source isn't currently supported.
## Connecting to SQL Server Data Warehouse as a source
Use the following procedure to connect to your SQL Server Data Warehouse source database with the AWS Schema Conversion Tool.
**To connect to a SQL Server Data Warehouse source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Microsoft SQL Server**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Microsoft SQL Server source data warehouse connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServerDW.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## SQL Server Data Warehouse to Amazon Redshift conversion settings
To edit SQL Server Data Warehouse to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Microsoft SQL Server**, and then choose **Microsoft SQL Server – Amazon Redshift**. AWS SCT displays all available settings for SQL Server Data Warehouse to Amazon Redshift conversion.
SQL Server Data Warehouse to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To migrate partitions of the source table to separate tables in Amazon Redshift. To do so, select **Use the UNION ALL view** and enter the maximum number of target tables that AWS SCT can create for a single source table.
Amazon Redshift doesn't support table partitioning. To emulate this behavior and make queries run faster, AWS SCT can migrate each partition of your source table to a separate table in Amazon Redshift. Then, AWS SCT creates a view that includes data from all these tables.
AWS SCT automatically determines the number of partitions in your source table. Depending on the type of source table partitioning, this number can exceed the quota for the tables that you can apply to your Amazon Redshift cluster. To avoid reaching this quota, enter the maximum number of target tables that AWS SCT can create for partitions of a single source table. The default option is 368 tables, which represents a partition for 366 days of a year and two tables for `NO RANGE` and `UNKNOWN` partitions.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## SQL Server Data Warehouse to Amazon Redshift conversion optimization settings
To edit SQL Server Data Warehouse to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Microsoft SQL Server**, and then choose **Microsoft SQL Server – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for SQL Server Data Warehouse to Amazon Redshift conversion.
SQL Server Data Warehouse to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting to a Teradata Data Warehouse with the AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, code objects, and application code from Teradata to Amazon Redshift or Amazon Redshift and AWS Glue used in combination.
## Privileges for Teradata as a source
The following privileges are required for using Teradata as a source:
+ SELECT ON DBC
+ SELECT ON SYSUDTLIB
+ SELECT ON SYSLIB
+ SELECT ON **
+ CREATE PROCEDURE ON **
In the preceding example, replace the ** placeholder with the name of the source database.
AWS SCT requires the CREATE PROCEDURE privilege to perform HELP PROCEDURE against all procedures in the source database. AWS SCT doesn't use this privilege to create any new objects in your source Teradata database.
## Connecting to Teradata as a source
Use the following procedure to connect to your Teradata source database with the AWS Schema Conversion Tool.
**To connect to a Teradata source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Teradata**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Teradata source database connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Teradata.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
### Using LDAP authentication with a Teradata source
To set up Lightweight Directory Access Protocol (LDAP) authentication for Teradata users who run Microsoft Active Directory in Windows, use the following procedure.
In the following procedure, the Active Directory domain is `test.local.com`. The Windows server is `DC`, and it's configured with default settings. The following script creates the `test_ldap` Active Directory account, and this account uses the `test_ldap` password.
**To set up LDAP authentication for Teradata users who run Microsoft Active Directory in Windows**
1. In the `/opt/teradata/tdat/tdgss/site` directory, edit the file `TdgssUserConfigFile.xml` . Change the LDAP section to the following.
```
AuthorizationSupported="no"
LdapServerName="DC.test.local.com"
LdapServerPort="389"
LdapServerRealm="test.local.com"
LdapSystemFQDN="dc= test, dc= local, dc=com"
LdapBaseFQDN="dc=test, dc=local, dc=com"
```
1. Apply the changes by running the configuration as follows.
```
#cd /opt/teradata/tdgss/bin
#./run_tdgssconfig
```
1. Test the configuration by running the following command.
```
# /opt/teradata/tdat/tdgss/14.10.03.01/bin/tdsbind -u test_ldap -w test_ldap
```
The output should be similar to the following.
```
LdapGroupBaseFQDN: dc=Test, dc=local, dc=com
LdapUserBaseFQDN: dc=Test, dc=local, dc=com
LdapSystemFQDN: dc= test, dc= local, dc=com
LdapServerName: DC.test.local.com
LdapServerPort: 389
LdapServerRealm: test.local.com
LdapClientUseTls: no
LdapClientTlsReqCert: never
LdapClientMechanism: SASL/DIGEST-MD5
LdapServiceBindRequired: no
LdapClientTlsCRLCheck: none
LdapAllowUnsafeServerConnect: yes
UseLdapConfig: no
AuthorizationSupported: no
FQDN: CN=test, CN=Users, DC=Anthem, DC=local, DC=com
AuthUser: ldap://DC.test.local.com:389/CN=test1,CN=Users,DC=test,DC=local,DC=com
DatabaseName: test
Service: tdsbind
```
1. Restart TPA using the following command.
```
#tpareset -f "use updated TDGSSCONFIG GDO"
```
1. Create the same user in the Teradata database as in Active Directory, as shown following.
```
CREATE USER test_ldap AS PERM=1000, PASSWORD=test_ldap;
GRANT LOGON ON ALL TO test WITH NULL PASSWORD;
```
If you change the user password in Active Directory for your LDAP user, specify this new password during connection to Teradata in LDAP mode. In DEFAULT mode, you connect to Teradata by using the LDAP user name and any password.
## Configuring statistics collection in your source Teradata data warehouse
To convert your source Teradata data warehouse, AWS SCT uses statistics to optimize your converted Amazon Redshift data warehouse. You can collect statistics in AWS SCT or upload the statistics file. For more information, see [Collecting or uploading statistics](CHAP_Converting.DW.md#CHAP_Converting.DW.Statistics).
To make sure that AWS SCT can collect statistics from your data warehouse, complete the following prerequisite tasks.
**To collect statistics from your Teradata data warehouse**
1. Run the following query to recollect statistics for all tables in your data warehouse.
```
collect summary statistics on table_name;
```
In the preceding example, replace *table\$1name* with the name of your source table. Repeat the query for each table that you convert.
1. Run the following query to determine the account string for the user, which you use to convert your data warehouse.
```
select * from dbc.accountinfo where username ='user_name'
```
1. Turn on query logging for a specific user using the account string from the previous example.
```
BEGIN QUERY LOGGING WITH OBJECTS, SQL ON ALL ACCOUNT=('$M$BUSI$S$D$H');
```
Alternatively, turn on query logging for all database users.
```
BEGIN QUERY LOGGING WITH SQL, OBJECTS LIMIT SQLTEXT=0 ON ALL;
```
After you complete collecting data warehouse statistics, turn off query logging. To do so, you can use the following code example.
```
end query logging with explain, objects, sql on all account=(' $M$BUSI$S$D$H');
```
## Collecting statistics in an offline mode from your source Teradata data warehouse
After you configure the statistics collection in your Teradata data warehouse, you can collect statistics in your AWS SCT project. Alternatively, you can use Basic Teradata Query (BTEQ) scripts to collect statistics in an offline mode. Then, you can upload the files with collected statistics to your AWS SCT project. For more information, see [Collecting or uploading statistics](CHAP_Converting.DW.md#CHAP_Converting.DW.Statistics).
**To collect statistics from your Teradata data warehouse in an offline mode**
1. Create the `off-line_stats.bteq` script with the following content.
```
.OS IF EXIST column-stats-tera.csv del /F column-stats-tera.csv
.OS IF EXIST table-stats-tera.csv del /F table-stats-tera.csv
.OS IF EXIST column-skew-script-tera.csv del /F column-skew-script-tera.csv
.OS IF EXIST column-skew-stats-tera.csv del /F column-skew-stats-tera.csv
.OS IF EXIST query-stats-tera.csv del /F query-stats-tera.csv
.LOGON your_teradata_server/your_login, your_password
.EXPORT REPORT FILE = table-stats-tera.csv
.SET TITLEDASHES OFF
.SET WIDTH 10000
SELECT
'"' || OREPLACE(COALESCE(c.DatabaseName, ''), '"', '""') || '";' ||
'"' || OREPLACE(COALESCE(c.TableName, ''), '"', '""') || '";' ||
'"' || TRIM(COALESCE(s.reference_count, '0')) || '";' ||
'"' || TRIM(COALESCE(CAST(p.RowCount AS BIGINT), '0')) || '";' ||
'"' || CAST(CAST(w.size_in_mb AS DECIMAL (38,1) FORMAT 'Z9.9') AS VARCHAR(38)) || '";' ||
'"' || TRIM(COALESCE(r.stat_fk_dep_count, '0')) || '";' ||
'"' || CAST(CAST(current_timestamp(0) as timestamp(0) format 'YYYY-MM-DDBHH:MI:SS') as VARCHAR(19)) || '"'
(TITLE '"database_name";"table_name";"reference_count";"row_count";"size_in_mb";"stat_fk_dep_count";"current_ts"')
FROM (select databasename, tablename
from DBC.tablesv
where tablekind IN ('T','O')
and databasename = 'your_database_name'
) c
left join
(select DatabaseName, TableName, max(RowCount) RowCount
from dbc.tableStatsv
group by 1,2)p
on p.databasename = c.databasename
and p.tablename = c.tablename
left join
(SELECT r.ChildDB as DatabaseName,
r.ChildTable as TableName,
COUNT(DISTINCT r.ParentTable) reference_count
FROM DBC.All_RI_ChildrenV r
GROUP BY r.ChildDB, r.ChildTable) s
on s.databasename = c.databasename
and s.tablename = c.tablename
left join
(SELECT r.ParentDB as DatabaseName,
r.ParentTable as TableName,
COUNT(DISTINCT r.ChildTable) stat_fk_dep_count
FROM DBC.All_RI_ParentsV r
GROUP BY r.ParentDB, r.ParentTable) r
on r.databasename = c.databasename
and r.tablename = c.tablename
left join
(select databasename, tablename,
sum(currentperm)/1024/1024 as size_in_mb
from dbc.TableSizeV
group by 1,2) w
on w.databasename = c.databasename
and w.tablename = c.tablename
WHERE COALESCE(r.stat_fk_dep_count,0) + COALESCE(CAST(p.RowCount AS BIGINT),0) + COALESCE(s.reference_count,0) > 0;
.EXPORT RESET
.EXPORT REPORT FILE = column-stats-tera.csv
.SET TITLEDASHES OFF
.SET WIDTH 10000
'"' || TRIM(COALESCE(CAST(t2.card AS BIGINT), '0')) || '";' ||
SELECT
'"' || OREPLACE(COALESCE(trim(tv.DatabaseName), ''), '"', '""') || '";' ||
'"' || OREPLACE(COALESCE(trim(tv.TableName), ''), '"', '""') || '";' ||
'"' || OREPLACE(COALESCE(trim(tv.columnname), ''), '"', '""') || '";' ||
'"' || TRIM(COALESCE(CAST(t2.card AS BIGINT), '0')) || '";' ||
'"' || CAST(current_timestamp AS VARCHAR(19)) || '"' (TITLE '"database_name";"table_name";"column_name";"cardinality";"current_ts"')
FROM dbc.columnsv tv
LEFT JOIN
(
SELECT
c.DatabaseName AS DATABASE_NAME,
c.TABLENAME AS TABLE_NAME,
c.ColumnName AS COLUMN_NAME,
c.UniqueValueCount AS CARD
FROM dbc.tablestatsv c
WHERE c.DatabaseName = 'your_database_name'
AND c.RowCount <> 0
) t2
ON tv.DATABASENAME = t2.DATABASE_NAME
AND tv.TABLENAME = t2.TABLE_NAME
AND tv.COLUMNNAME = t2.COLUMN_NAME
WHERE t2.card > 0;
.EXPORT RESET
.EXPORT REPORT FILE = column-skew-script-tera.csv
.SET TITLEDASHES OFF
.SET WIDTH 10000
SELECT
'SELECT CAST(''"' || TRIM(c.DatabaseName) || '";"' || TRIM(c.TABLENAME) || '";"' || TRIM(c.COLUMNNAME) || '";"'' ||
TRIM(CAST(COALESCE(MAX(cnt) * 1.0 / SUM(cnt), 0) AS NUMBER FORMAT ''9.9999'')) || ''";"'' ||
CAST(CURRENT_TIMESTAMP(0) AS VARCHAR(19)) || ''"'' AS VARCHAR(512))
AS """DATABASE_NAME"";""TABLE_NAME"";""COLUMN_NAME"";""SKEWED"";""CURRENT_TS"""
FROM(
SELECT COUNT(*) AS cnt
FROM "' || c.DATABASENAME || '"."' || c.TABLENAME ||
'" GROUP BY "' || c.COLUMNNAME || '") t' ||
CASE WHEN ROW_NUMBER() OVER(PARTITION BY c.DATABASENAME
ORDER BY c.TABLENAME DESC, c.COLUMNNAME DESC) <> 1
THEN ' UNION ALL'
ELSE ';' END (TITLE '--SKEWED--')
FROM dbc.columnsv c
INNER JOIN
(SELECT databasename, TABLENAME
FROM dbc.tablesv WHERE tablekind = 'T'
AND databasename = 'your_database_name') t
ON t.databasename = c.databasename
AND t.TABLENAME = c.TABLENAME
INNER JOIN
(SELECT databasename, TABLENAME, columnname FROM dbc.indices GROUP BY 1,2,3
WHERE TRANSLATE_CHK (databasename USING LATIN_TO_UNICODE) + TRANSLATE_CHK (TABLENAME USING LATIN_TO_UNICODE) + TRANSLATE_CHK (columnname USING LATIN_TO_UNICODE) = 0
) i
ON i.databasename = c.databasename
AND i.TABLENAME = c.TABLENAME
AND i.columnname = c.columnname
WHERE c.ColumnType NOT IN ('CO','JN','N','++','VA','UT','AN','XM','A1','BO')
ORDER BY c.TABLENAME, c.COLUMNNAME;
.EXPORT RESET
.EXPORT REPORT FILE = column-skew-stats-tera.csv
.SET TITLEDASHES OFF
.SET WIDTH 10000
.RUN FILE = column-skew-script-tera.csv
.EXPORT RESET
.EXPORT REPORT FILE = query-stats-tera.csv
.SET TITLEDASHES OFF
.SET WIDTH 32000
SELECT
'"' || RTRIM(CAST(SqlTextInfo AS VARCHAR(31900)), ';') || '";"' ||
TRIM(QueryCount) || '";"' ||
TRIM(QueryId) || '";"' ||
TRIM(SqlRowNo) || '";"' ||
TRIM(QueryParts) || '";"' ||
CAST(CURRENT_TIMESTAMP(0) AS VARCHAR(19)) || '"'
(TITLE '"query_text";"query_count";"query_id";"sql_row_no";"query_parts";"current_ts"')
FROM
(
SELECT QueryId, SqlTextInfo, SqlRowNo, QueryParts, QueryCount,
SUM(QueryFirstRow) OVER (ORDER BY QueryCount DESC, QueryId ASC, SqlRowNo ASC
ROWS UNBOUNDED PRECEDING) AS topN
FROM
(SELECT QueryId, SqlTextInfo, SqlRowNo, QueryParts, QueryCount,
CASE WHEN
ROW_NUMBER() OVER (PARTITION BY QueryCount, SqlTextInfo ORDER BY QueryId, SqlRowNo) = 1 AND SqlRowNo = 1
THEN 1 ELSE 0 END AS QueryFirstRow
FROM (
SELECT q.QueryId, q.SqlTextInfo, q.SqlRowNo,
MAX(q.SqlRowNo) OVER (PARTITION BY q.QueryId) QueryParts,
COUNT(q.SqlTextInfo) OVER (PARTITION BY q.SqlTextInfo) QueryCount
FROM DBC.dbqlsqltbl q
INNER JOIN
(
SELECT QueryId
FROM DBC.DBQLogTbl t
WHERE TRIM(t.StatementType) IN ('SELECT')
AND TRIM(t.AbortFlag) = '' AND t.ERRORCODE = 0
AND (CASE WHEN 'All users' IN ('All users') THEN 'All users' ELSE TRIM(t.USERNAME) END) IN ('All users') --user_name list
AND t.StartTime > CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY 1
) t
ON q.QueryId = t.QueryId
INNER JOIN
(
SELECT QueryId
FROM DBC.QryLogObjectsV
WHERE ObjectDatabaseName = 'your_database_name'
AND ObjectType = 'Tab'
AND CollectTimeStamp > CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY 1
) r
ON r.QueryId = t.QueryId
WHERE q.CollectTimeStamp > CURRENT_TIMESTAMP - INTERVAL '30' DAY
) t
) t
WHERE SqlTextInfo NOT LIKE '%";"%'
) q
WHERE
QueryParts >=1
AND topN <= 50
ORDER BY QueryCount DESC, QueryId, SqlRowNo
QUALIFY COUNT(QueryId) OVER (PARTITION BY QueryId) = QueryParts;
.EXPORT RESET
.LOGOFF
.QUIT
```
1. Create the `td_run_bteq.bat` file that runs the BTEQ script that you created in the previous step. Use the following content for this file.
```
@echo off > off-line_stats1.bteq & setLocal enableDELAYedexpansion
@echo off > off-line_stats2.bteq & setLocal enableDELAYedexpansion
set old1=your_teradata_server
set new1=%1
set old2=your_login
set new2=%2
set old3=your_database_name
set new3=%3
set old4=your_password
set /p new4=Input %2 pass?
for /f "tokens=* delims= " %%a in (off-line_stats.bteq) do (
set str1=%%a
set str1=!str1:%old1%=%new1%!
>> off-line_stats1.bteq echo !str1!
)
for /f "tokens=* delims= " %%a in (off-line_stats1.bteq) do (
set str2=%%a
set str2=!str2:%old2%=%new2%!
>> off-line_stats2.bteq echo !str2!
)
type nul > off-line_stats1.bteq
for /f "tokens=* delims= " %%a in (off-line_stats2.bteq) do (
set str3=%%a
set str3=!str3:%old3%=%new3%!
>> off-line_stats1.bteq echo !str3!
)
type nul > off-line_stats2.bteq
for /f "tokens=* delims= " %%a in (off-line_stats1.bteq) do (
set str4=%%a
set str4=!str4:%old4%=%new4%!
>> off-line_stats2.bteq echo !str4!
)
del .\off-line_stats1.bteq
echo export starting...
bteq -c UTF8 < off-line_stats.bteq > metadata_export.log
pause
```
1. Create the `runme.bat` file that runs the batch file that you created in the previous step. Use the following content for this file.
```
.\td_run_bteq.bat ServerName UserName DatabaseName
```
In the `runme.bat` file, replace *ServerName*, *UserName*, and *DatabaseName* with your applicable values.
Then, run the `runme.bat` file. Repeat this step for each data warehouse that you convert to Amazon Redshift.
After you run this script, you receive three files with statistics for each database. You can upload these files to your AWS SCT project. To do so, choose your data warehouse from the left panel of your project, and open the context (right-click) menu. Choose **Upload Statistics**.
## Teradata to Amazon Redshift conversion settings
To edit Teradata to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Teradata**, and then choose **Teradata – Amazon Redshift**. AWS SCT displays all available settings for Teradata to Amazon Redshift conversion.
Teradata to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To migrate partitions of the source table to separate tables in Amazon Redshift. To do so, select **Use the UNION ALL view** and enter the maximum number of target tables that AWS SCT can create for a single source table.
Amazon Redshift doesn't support table partitioning. To emulate this behavior and make queries run faster, AWS SCT can migrate each partition of your source table to a separate table in Amazon Redshift. Then, AWS SCT creates a view that includes data from all these tables.
AWS SCT automatically determines the number of partitions in your source table. Depending on the type of source table partitioning, this number can exceed the quota for the tables that you can apply to your Amazon Redshift cluster. To avoid reaching this quota, enter the maximum number of target tables that AWS SCT can create for partitions of a single source table. The default option is 368 tables, which represents a partition for 366 days of a year and two tables for `NO RANGE` and `UNKNOWN` partitions.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
+ To use an explicit list of columns in converted code for `SELECT *` statements, select **Use explicit column declaration**.
+ To emulate the behavior of primary and unique keys in your Amazon Redshift cluster, select **Emulate the behavior of primary and unique keys**.
Amazon Redshift doesn't enforce unique and primary keys and uses them for informational purposes only. If you use these constraints in your code, then make sure that AWS SCT emulates their behavior in the converted code.
+ To ensure data uniqueness in the target Amazon Redshift tables. To do so, select **Emulate the behavior of SET tables**.
Teradata creates tables using the `SET` syntax element as a default option. You can't add duplicate rows in a `SET` table. If your source code doesn't use this uniqueness constraint, then turn off this option. In this case, the converted code works faster.
If your source code uses the `SET` option in tables as a uniqueness constraint, turn on this option. In this case, AWS SCT rewrites `INSERT..SELECT` statements in the converted code to emulate the behavior of your source database.
## Teradata to Amazon Redshift conversion optimization settings
To edit Teradata to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Teradata**, and then choose **Teradata – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for Teradata to Amazon Redshift conversion.
Teradata to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
# Connecting the AWS Schema Conversion Tool to Vertica databases
You can use AWS SCT to convert schemas, code objects, and application code from Vertica to Amazon Redshift.
## Privileges for Vertica as a source
The following privileges are required for using Vertica as a source:
+ USAGE ON SCHEMA **
+ USAGE ON SCHEMA PUBLIC
+ SELECT ON ALL TABLES IN SCHEMA **
+ SELECT ON ALL SEQUENCES IN SCHEMA **
+ EXECUTE ON ALL FUNCTIONS IN SCHEMA **
+ EXECUTE ON PROCEDURE **
In the preceding example, replace placeholders as following:
+ Replace *schema\$1name* with the name of the source schema.
+ Replace *procedure\$1name* with the name of a source procedure. Repeat the grant for each procedure that you are converting.
+ Replace *procedure\$1signature* with the comma-delimited list of procedure argument types.
## Connecting to Vertica as a source
Use the following procedure to connect to your Vertica source database with the AWS Schema Conversion Tool.
**To connect to a Vertica source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Vertica**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. 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](CHAP_UserInterface.SecretsManager.md).
+ To enter the Vertica source database connection information manually, use the following instructions:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Vertica.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
## Vertica to Amazon Redshift conversion settings
To edit Vertica to Amazon Redshift conversion settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Vertica**, and then choose **Vertica – Amazon Redshift**. AWS SCT displays all available settings for Vertica to Amazon Redshift conversion.
Vertica to Amazon Redshift 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 set the maximum number of tables that AWS SCT can apply to your target Amazon Redshift cluster.
For **The maximum number of tables for the target Amazon Redshift cluster**, choose the number of tables that AWS SCT can apply to your Amazon Redshift cluster.
Amazon Redshift has quotas that limit the use tables for different cluster node types. If you choose **Auto**, AWS SCT determines the number of tables to apply to your target Amazon Redshift cluster depending on the node type. Optionally, choose the value manually. For more information, see [Quotas and limits in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html) in the *Amazon Redshift Management Guide*.
AWS SCT converts all your source tables, even if this is more than your Amazon Redshift cluster can store. AWS SCT stores the converted code in your project and doesn't apply it to the target database. If you reach the Amazon Redshift cluster quota for the tables when you apply the converted code, then AWS SCT displays a warning message. Also, AWS SCT applies tables to your target Amazon Redshift cluster until the number of tables reaches the limit.
+ To migrate partitions of the source table to separate tables in Amazon Redshift. To do so, select **Use the UNION ALL view** and enter the maximum number of target tables that AWS SCT can create for a single source table.
Amazon Redshift doesn't support table partitioning. To emulate this behavior and make queries run faster, AWS SCT can migrate each partition of your source table to a separate table in Amazon Redshift. Then, AWS SCT creates a view that includes data from all these tables.
AWS SCT automatically determines the number of partitions in your source table. Depending on the type of source table partitioning, this number can exceed the quota for the tables that you can apply to your Amazon Redshift cluster. To avoid reaching this quota, enter the maximum number of target tables that AWS SCT can create for partitions of a single source table. The default option is 368 tables, which represents a partition for 366 days of a year and two tables for `NO RANGE` and `UNKNOWN` partitions.
+ To apply compression to Amazon Redshift table columns. To do so, select **Use compression encoding**.
AWS SCT assigns compression encoding to columns automatically using the default Amazon Redshift algorithm. For more information, see [Compression encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html) in the *Amazon Redshift Database Developer Guide*.
By default, Amazon Redshift doesn't apply compression to columns that are defined as sort and distribution keys. You can change this behavior and apply compression to these columns. To do so, select **Use compression encoding for KEY columns**. You can select this option only when you select the **Use compression encoding** option.
## Vertica to Amazon Redshift conversion optimization settings
To edit Vertica to Amazon Redshift conversion optimization settings, choose **Settings** in AWS SCT, and then choose **Conversion settings**. From the upper list, choose **Vertica**, and then choose **Vertica – Amazon Redshift**. In the left pane, choose **Optimization strategies**. AWS SCT displays conversion optimization settings for Vertica to Amazon Redshift conversion.
Vertica to Amazon Redshift conversion optimization settings in AWS SCT include options for the following:
+ To work with automatic table optimization. To do so, select **Use Amazon Redshift automatic table tuning**.
Automatic table optimization is a self-tuning process in Amazon Redshift that automatically optimizes the design of tables. For more information, see [Working with automatic table optimization](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) in the *Amazon Redshift Database Developer Guide*.
To rely only on the automatic table optimization, choose **None** for **Initial key selection strategy**.
+ To choose sort and distribution keys using your strategy.
You can choose sort and distribution keys using Amazon Redshift metadata, statistical information, or both these options. For **Initial key selection strategy** on the **Optimization strategies** tab, choose one of the following options:
+ Use metadata, ignore statistical information
+ Ignore metadata, use statistical information
+ Use metadata and statistical information
Depending on the option that you choose, you can select optimization strategies. Then, for each strategy, enter the value (0–100). These values define the weight of each strategy. Using these weight values, AWS SCT defines how each rule influences on the choice of distribution and sort keys. The default values are based on the AWS migration best practices.
You can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to define it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.
+ To configure strategy details.
In addition to defining the weight for each optimization strategy, you can configure the optimization settings. To do so, choose **Conversion optimization**.
+ For **Sort key columns limit**, enter the maximum number of columns in the sort key.
+ For **Skewed threshold value**, enter the percentage (0–100) of a skewed value for a column. AWS SCT excludes columns with the skew value greater than the threshold from the list of candidates for the distribution key. AWS SCT defines the skewed value for a column as the percentage ratio of the number of occurrences of the most common value to the total number of records.
+ For **Top N queries from the query history table**, enter the number (1–100) of the most frequently used queries to analyze.
+ For **Select statistics user**, choose the database user for which you want to analyze the query statistics.
Also, on the **Optimization strategies** tab, you can define the size of small tables for the **Find small tables** strategy. For **Min table row count** and **Max table row count**, enter the minimum and maximum number of rows in a table to consider it as a small table. AWS SCT applies the `ALL` distribution style to small tables. In this case, a copy of the entire table is distributed to every node.