Customizing conversion |

Generating Triggers when migrating DB2 IDENTITY column to Oracle Converting CASE statement to DECODE function in Oracle Migrating Tables with NOT NULL constraint from Sybase Migrating ANSI joins as ANSI joins in Oracle Migrating of THETA joins from Oracle to SQL Server Indenting converted sql Renaming of Oracle's qualified object names Changing the case of the quoted identifiers Setting the default target database Remove ORDER BY column when column not in SELECT list Migrate Oracle DELETE SET NULL Clause To SQL Server Triggers Convert Oracle TO_DATE function to CAST AS TIMESTAMP in Teradata Add CASESPECIFIC clause to CAST AS CHAR function in Teradata Convert Oracle TRUNC (DATE) function to CAST AS DATE function in Teradata Migrating Oracle temporary table to Teradata Datatype mapping "SwisSQL Console" provides various configuration options to customize the way SQL query conversion is done. This provides users very good flexibility in customizing the query conversion. In this section , you can learn about the various configuration options provided by SwisSQL and how it could be used for customizing the query conversion. All of the listed customizable option can be modified from the SwisSQL Console GUI. All of the configurable options listed below except the option "Setting the default target database" can be modified in the "Conversion settings" dialog. The "Conversion settings dialog" can be accessed through the menu "Settings -> Conversion settings" in the SwisSQL Console GUI. The configurable option "Setting the default target database" can be accessed as described here. Conversion settings dialog These configurations are also available through configuration file SQLOneConsoleOptions.conf present under <InstallationDir>\SwisSQL\Console5.5\conf directory. ### 1. Generating Triggers when migrating DB2 IDENTITY column to Oracle SwisSQL Console by default creates a new sequence and trigger in Oracle when converting DB2 IDENTITY columns. This default value is given by following option GenerateTriggerForIdentity. When the option is set to true (default value) a trigger is generated along with the sequence. When the option is set to false only the sequence is generated for IDENTITY columns. In the "Conversion settings" dialog select the check box "Generate trigger when migrating DB2 identity column to Oracle" to set the value to true. To set the value to false unselect the checkbox.
Description: The sample DB2 SQL creates an employee table with empno column as IDENTITY and a name column.
Case 1 – Generating sequence and trigger : The option GenerateTriggerForIdentity is set to true, which is the default value. In this case the trigger is generated along with sequence.
SOURCE SQL | CONVERTED SQL
CREATE TABLE employee (empno int not null generated always as identity (start with 1 increment by 2), name varchar(20) not null) | CREATE SEQUENCE employee_empno_SEQ START WITH 1 INCREMENT BY 2 / /* SwisSQL Message : Query split into multiple Queries. Manual Intervention required */ CREATE TABLE employee ( empno int NOT NULL , name VARCHAR2 (20) NOT NULL ) / CREATE OR REPLACE TRIGGER TR_employee_empno BEFORE INSERT ON employee FOR EACH ROW BEGIN SELECT employee_empno_SEQ.nextval INTO :new.empno FROM dual; END;
Case 2 - Generating sequence alone: The option GenerateTriggerForIdentity is set to false. In this case the trigger is not generated.
CREATE TABLE employee (empno int not null generated always as identity (start with 1 increment by 2), name varchar(20) not null) | CREATE SEQUENCE employee_empno_SEQ START WITH 1 INCREMENT BY 2 / /* SwisSQL Message : Query split into multiple Queries. Manual Intervention required */ CREATE TABLE employee ( empno int NOT NULL , name VARCHAR2 (20) NOT NULL )
### 2. Converting CASE statement to DECODE function in Oracle SwisSQL Console by default converts a CASE statement of any SQL dialect as DECODE function in Oracle. This default value is given by following option ConvertCaseToDecode. When the option is set to true the CASE statement is converted as DECODE function in Oracle . When the option is set to false the CASE statement is converted as CASE statement in Oracle. In the "Conversion settings" dialog select the check box "Convert CASE statement to DECODE function in Oracle" to set the value to true. To set the value to false unselect the checkbox.
Description: The sample SQL fetches the column id from emp table and based on the value returns the appropriate number.
Case 1 – convert to DECODE: The option convertCaseToDecode is set to true, which is the default value. In this case the CASE statement is converted to DECODE function in Oracle .
SOURCE SQL | CONVERTED SQL
SELECT case id when 1 then 'ONE' when 2 then 'TWO' when 3 then 'THREE' when 4 then 'FOUR' when 5 then 'FIVE' else 'SIX' end FROM emp | SELECT DECODE(id ,1 ,'ONE',2 ,'TWO',3 ,'THREE',4 ,'FOUR',5 ,'FIVE','SIX') FROM emp
Case 2 - convert to CASE: The option convertCaseToDecode is set to false. In this case the CASE statement is converted to CASE statement in Oracle .
SELECT case id when 1 then 'ONE' when 2 then 'TWO' when 3 then 'THREE' when 4 then 'FOUR' when 5 then 'FIVE' else 'SIX' end FROM emp | SELECT case id when 1 then 'ONE' when 2 then 'TWO' when 3 then 'THREE' when 4 then 'FOUR' when 5 then 'FIVE' else 'SIX' end FROM emp
### 3. Migrating Tables with NOT NULL constraint from Sybase. SwisSQL Console by default converts all table columns which do not specify either 'NULL' or 'NOT NULL' constraint as having NULL constraint, which is the default constraint in all databases. In Sybase alone when 'NULL' or 'NOT NULL' constraint is not specified the constraint is by default taken as 'NOT NULL'. For this case Console allows user to specify whether the table should be converted with NOT NULL constraint or with NULL constraint. When the option SybaseNotNullConstraint is set to true columns are converted with NOT NULL constraint in Oracle and SQL Server. When the option SybaseNotNullConstraint is set to false columns are converted with NULL constraint in Oracle and SQL Server. In the "Conversion settings" dialog select the check box "Set NOT NULL constraint for table columns" to set the value to true. To set the value to false unselect the checkbox.
Description: The sample SQL creates employee table with empno and name as columns without specifying the NULL or NOT NULL constraint in Sybase
Case 1 - column has NOT NULL constraint: The option SybaseNotNullConstraint is set to true. In this case the table is converted with NOT NULL constraint when constraint is not specified.
SOURCE SQL | CONVERTED SQL
CREATE TABLE employee ( empno int, name varchar(20) null ) | CREATE TABLE employee ( empno int NOT NULL , name VARCHAR2 (20) null )
Case 2 - column has NULL constraint: The option SybaseNotNullConstraint is set to false which is the default value. In this case the table is converted without NOT NULL constraint when constraint is not specified.
CREATE TABLE employee ( empno int, name varchar(20) null ) | CREATE TABLE employee ( empno int , name VARCHAR2 (20) null )
### 4. Migrating ANSI joins as ANSI joins in Oracle. SwisSQL Console by default converts all the ANSI Join SQL queries to THETA joins in Oracle. It is possible to customize the conversion to generate ANSI Join queries through configuration. For this the configuration option ConvertToANSIJoinInOracle present in the file SQLOneConsoleOptions.conf under the directory <InstallDir>/SwisSQL/Console5.5/conf is to be used. The option is by default set to false, in which case the ANSI join SQL queries will be converted to THETA convention. If the option is set to true then the ANSI join SQL queries will be converted to Oracle ANSI Joins. In the "Conversion settings" dialog select the check box "Convert ANSI join to Oracle theta join" to convert the ANSI join to Oracle theta join. To retain the ANSI join in Oracle unselect the checkbox.
Description: The sample SQL fetches the employee number, name, salary and department name whose salary is greater than the value in salarybound column of employee table and also does a left outer join to list all the employees who are either associated to a Department or not.
Case 1 - Convert to THETA Join: The option ConvertToANSIJoinInOracle is set to false, which is the default value. In this case the SQL queries are converted to THETA Joins
SOURCE SQL | CONVERTED SQL
SELECT e.empno, e.name, e.deptno, e.deptname FROM employee e LEFT OUTER JOIN Dept d ON e.deptno = d.deptno WHERE e.salary > e.salaryBound ORDER BY empno; | SELECT e.empno, e.name, e.deptno, e.deptname FROM employee e, Dept d WHERE e.deptno = d.deptno (+) AND (e.salary > e.salaryBound) ORDER BY empno
Case 2 - Convert to ANSI Join:The option ConvertToANSIJoinInOracle is set to true. In this case the SQL queries are converted to ANSI Joins.
SELECT e.empno, e.name, e.deptno, e.deptname FROM employee e LEFT OUTER JOIN Dept d ON e.deptno = d.deptno WHERE e.salary > e.salaryBound ORDER BY empno; | SELECT e.empno, e.name, e.deptno, e.deptname FROM employee e LEFT OUTER JOIN Dept d ON e.deptno = d.deptno WHERE e.salary > e.salaryBound ORDER BY empno;
### 5. Migrating THETA joins from Oracle to SQL Server SwisSQL Console by default converts all the THETA join SQL queries in Oracle to ANSI join in SQL Server. It is possible to customize the conversion to generate THETA Join queries through configuration. The configuration option ConvertToThetaJoinInTSQL present in the file SQLOneConsoleOptions.conf under the directory <InstallDir>/SwisSQL/Console5.5/conf is to be used. The option is by default set to false, in which case the SQL queries will be converted to ANSI convention. If the option is set to true then the SQL queries will be converted to SQL Server THETA Joins. In the "Conversion settings" dialog select the check box "Convert Oracle theta join to SQL Server theta join" to convert the Oracle theta join to SQL Server theta join. To convert the Oracle theta join to ANSI join unselect the checkbox.
Description: The sample SQL fetches the employee number, name, salary and department name whose salary is greater than the value in salarybound column of employee table and also does a left outer join to list all the employees who are either associated to a Department or not.
Case 1 - Convert to ANSI Join: The option ConvertToThetaJoinInTSQL is by default set to false, in which case the SQL queries will be converted to ANSI convention in SQL Server.
SOURCE SQL | CONVERTED SQL
SELECT e.empno, e.name, e.deptno, e.deptname FROM employee e, dept d WHERE e.salary > e.salaryBound AND e.deptno = d.deptno (+) ORDER BY empno | SELECT e.empno, e.name,e.deptno, e.deptname FROM employee e LEFT OUTER JOIN Dept d ON e.deptno = d.deptno WHERE e.salary > e.salaryBound ORDER BY empno;
Case 2 - Convert to THETA Join:When the option ConvertToThetaJoinInTSQL is set to true, then the SQL queries are converted to Microsoft THETA Joins
SELECT e.empno, e.name, e.deptno, e.deptname FROM employee e, dept d WHERE e.salary > e.salaryBound AND e.deptno = d.deptno (+) ORDER BY empno | SELECT e.empno, e.name, e.deptno, e.deptname FROM employee e, dept d WHERE e.salary > e.salaryBound AND e.deptno *= d.deptno ORDER BY empno;
### 6. Indenting the converted SQL. SwisSQL Console by default indents all the converted SQL. This option can be customized by setting IndentConvertedSQL option in the file SQLOneConsoleOptions.conf under the directory <InstallDir>/SwisSQL/Console5.5/conf as true to indent the converted SQL and false to convert the SQL without indentation. In the "Conversion settings" dialog select the check box "Indent the converted SQL statement" to set the value to true. To set the value to false unselect the checkbox. ### 7. Renaming of Oracle's qualified object names SwisSQL Console by default converts the qualified object names of Oracle as is i.e. the object names of the format schema_name.object_name is left as is. It is possible to customize the conversion to the format schema_name.schema_name_object_name where the object name modified to the form schema_name_object_name. This option can be customized by setting RenameTableNameAsSchemaName_TableName option in the file SQLOneConsoleOptions.conf under the directory <InstallDir>/SwisSQL/Console5.5/conf as true to modify the object name and false to leave the object name as is. This option is specific to Oracle to Netezza SQL conversion. In the "Conversion settings" dialog select the check box "Rename table name to schemaname_tablename" to set the value to true. To set the value to false unselect the checkbox. ### 8. Changing the case of the quoted identifiers SwisSQL Console by default changes the case of the quoted identifiers to lowercase. It is possible to customize this conversion to change the case to uppercase. This option can be customized by setting ConvertQuotedIdentifiersToUppercase option in the file SQLOneConsoleOptions.conf under the directory <InstallDir>/SwisSQL/Console5.5/conf as true to change the case to uppercase and false to change the case to lowercase. In the "Conversion settings" dialog select the check box "Convert the quoted identifiers to upper case" to set the value to true. To set the value to false unselect the checkbox. ### 9. Setting the default target database The default target database to which the SQLs are to be converted can be configured in the SwisSQL Console GUI. To change the default target database access the menu item "Settings -> Select target database" and select the check box of the target database you wish to set as the default target database. ### 10. Remove ORDER BY column when column not in SELECT list In Sybase, for SELECT queries with DISTINCT qualifier ORDER BY clause can have column names which are not present in the column list of the SELECT clause. However, this is invalid in SQL Server. In such cases, the column has to be removed from the ORDER BY clause or the query has to moved to a subquery. The checkbox "Remove ORDER BY column when column not in SELECT list" can be used to achieve the above conversions. When the property is set to true by selecting the checkbox, the ORDER BY column is removed which is the default behaviour. When the property is set to false by unchecking the checkbox the query is moved to a subquery. ### 11. Convert Oracle TO_DATE function to CAST AS TIMESTAMP in Teradata SwisSQL Console provides an option to convert Oracle TO_DATE without the format argument as CAST AS TIMESTAMP or CAST AS DATE function to Teradata. For this the checkbox "Convert Oracle TO_DATE function to CAST AS TIMESTAMP function" is to be used. When this option is set to true by selecting the checkbox, the TO_DATE function is converted to CAST function with TIMESTAMP(0) format in Teradata. When the option is set to false, the TO_DATE function is converted to the default CAST function with DATE format.
Case 1 :- Checkbox "Convert Oracle TO_DATE function to CAST AS TIMESTAMP function" is checked
SOURCE SQL | CONVERTED SQL
SELECT TO_DATE('2003/07/09') FROM dual | SELECT CAST('2003/07/09' AS TIMESTAMP)
Case 2 :- Checkbox "Convert Oracle TO_DATE function to CAST AS TIMESTAMP function" is unchecked
SOURCE SQL | CONVERTED SQL
SELECT TO_DATE('2003/07/09') FROM dual | SELECT CAST('2003/07/09' AS DATE)
### 12. Add CASESPECIFIC clause to CAST AS CHAR function in Teradata SwisSQL Console provides the option to add CASESPECIFIC clause / attribute to CAST AS CHAR function while converting to Teradata. For this the checkbox "Add CASESPECIFIC clause to CAST AS CHAR function" can be used. When this option is set to true by selecting the checkbox the attribute CASESPECIFIC is added to the converted CAST function.
Case 1 :- Checkbox "Add CASESPECIFIC clause to CAST AS CHAR function" is checked.
SOURCE SQL | CONVERTED SQL
SELECT TO_CHAR('2003') FROM dual | SELECT CAST('2003' AS VARCHAR (50) CASESPECIFIC)
Case 2 :- Checkbox "Add CASESPECIFIC clause to CAST AS CHAR function" is unchecked.
SOURCE SQL | CONVERTED SQL
SELECT TO_CHAR('2003') FROM dual | SELECT CAST('2003' AS VARCHAR (50))
### 13. Convert Oracle TRUNC (DATE) function to CAST AS DATE function in Teradata SwisSQL Console provides the option to convert Oracle's TRUNC(DATE) function to CAST AS DATE function in Teradata. This enables the Teradata optimizer to use Partition Elimination. For this the checkbox "Convert Oracle TRUNC (DATE) function to CAST AS DATE function" can be used. When this option is set to true by selecing the checkbox the function is converted to CAST AS DATE. When this option is set to false the function is converted as Teradata's TRUNC UDF.
Case 1 :- Checkbox "Convert Oracle TRUNC (DATE) function to CAST AS DATE function" is checked
SOURCE SQL | CONVERTED SQL
SELECT TRUNC(TO_DATE('22-AUG-03'), 'DD') FROM dual | SELECT CAST(CAST('22-AUG-03' AS TIMESTAMP) AS DATE)
Case 2 :- Checkbox "Convert Oracle TRUNC (DATE) function to CAST AS DATE function" is unchecked
SOURCE SQL | CONVERTED SQL
SELECT TRUNC(TO_DATE('22-AUG-03'), 'DD') FROM dual | SELECT TRUNC(CAST('22-AUG-03' AS TIMESTAMP), 'DD')
1. ### 14. Migrating Oracle temporary table to Teradata SwisSQL Console provides the following options to convert Oracle temporary table to Teradata. Any one of these options can be selected from the Combobox "Convert Oracle Temporary table to :" Convert to Teradata GLOBAL TEMPORARY table This option can be selected by selecting the option "Global Temporary Table" . When this option is selected, Oracle temporary table is converted to GLOBAL TEMPORARY table in Teradata.
   Oracle temporary table is converted to global temporary table in Teradata
   SOURCE SQL | CONVERTED SQL
   CREATE GLOBAL TEMPORARY TABLE T_V_Table1 (department_number ) ON COMMIT PRESERVE ROWS PARALLEL AS SELECT deptno dep artment_number FROM emp | CREATE GLOBAL TEMPORARY TABLE T_V_Table1 , NO LOG (department_number ) AS (SELECT deptno department_number FROM emp )< span style="color: blue;">WITH NO DATA ON COMMIT PRESERVE ROWS INSERT INTO T_V_Table1 ( SELECT deptno department_number FROM emp )
2. Convert to Teradata VOLATILE table This option can be selected by selecting the option "Volatile Table" . When this option is selected, Oracle temporary table is converted to VOLATILE table in Teradata.
   Oracle temporary table is converted to volatile table in Teradata
   SOURCE SQL | CONVERTED SQL
   CREATE GLOBAL TEMPORARY TABLE T_V_Table1 (department_n umber ) ON COMMIT PRESERVE ROWS PARALLEL AS SELECT deptno dep artment_number FROM emp | CREATE VOLATILE TABLE T_V_Table1 , NO LOG (department_number ) AS (SELECT deptno department_number FROM emp )WITH DATA ON COMMIT PRESERVE ROWS
3. Convert to Teradata permanent table This option can be selected by selecting the option "Permanent Table" . When this option is selected, Oracle temporary table is converted to a permanent table in Teradata.
   Oracle temporary table is converted to permanent table in Teradata
   SOURCE SQL | CONVERTED SQL
   CREATE GLOBAL TEMPORARY TABLE T_V_Table1 (department_n umber ) ON COMMIT PRESERVE ROWS PARALLEL AS SELECT deptno dep artment_number FROM emp | CREATE TABLE T_V_Table1 (department_number ) AS (SELECT deptno department_number FROM emp )WITH DATA
### 15. Migrate Oracle DELETE SET NULL Clause To SQL Server Triggers The DELETE SET NULL clause of Oracle will be simulated in SQL Server by creating INSERT, DELETE and UPDATE triggers on each of the FOREIGN KEY constraint. Object name customization: Based on the Oracle ALTER statement the trigger name, the table on which the trigger is applied and the column names used in the trigger will be changed during migration. The trigger name will also be based on the foreign key constraint name used in the Oracle ALTER statement. Example : Source Oracle Query : ALTER TABLE TABLE1 ADD CONSTRAINT CONSTRAINT1 FOREIGN KEY (COLUMN1,COLUMN2) REFERENCES PRIMARY_TABLE ON DELETE SET NULL Converted query to SQL Server by SwisSQL : ALTER TABLE TABLE1 ADD CONSTRAINT CONSTRAINT1 FOREIGN KEY (COLUMN1, COLUMN2) REFERENCES PRIMARY_TABLE /* INSERT TRIGGER FOR: TABLE1*/ CREATE TRIGGER TABLE1_INSTRIG ON TABLE1 FOR INSERT AS declare @num_rows int select @num_rows = @@rowcount If @num_rows = 0 return /* CONSTRAINT1 insert restrict */ IF (SELECT COUNT(*) FROM PRIMARY_TABLE , inserted WHERE PRIMARY_TABLE.COLUMN1 = INSERTED.COLUMN1 AND PRIMARY_TABLE.COLUMN2 = INSERTED.COLUMN2) != @num_rows BEGIN RAISERROR 40002 'NOT IN PRIMARY_TABLE for child TABLE1' ROLLBACK TRAN RETURN END go /* DELETE TRIGGER FOR: PRIMARY_TABLE */ CREATE TRIGGER PRIMARY_TABLE_DELTRIG ON PRIMARY_TABLE FOR DELETE AS declare @num_rows int select @num_rows = @@rowcount If @num_rows = 0 return /* CONSTRAINT1 Delete Set Null */ UPDATE TABLE1 SET TABLE1.COLUMN1 = null , TABLE1.COLUMN2 = null from TABLE1 , deleted where TABLE1.COLUMN1 = DELETED.COLUMN1 AND TABLE1.COLUMN2 = DELETED.COLUMN2 go /* UPDATE TRIGGER for: PRIMARY_TABLE */ CREATE TRIGGER PRIMARY_TABLE_UPDTRIG ON PRIMARY_TABLE FOR UPDATE AS declare @num_rows int select @num_rows = @@rowcount If @num_rows = 0 return if update(COLUMN1) OR update(COLUMN2) BEGIN /* Update Primary Key restrict if no dependants found forPRIMARY_TABLE */ IF (SELECT COUNT(*) FROM TABLE1,deleted,inserted where TABLE1.COLUMN1 = DELETED.COLUMN1 AND DELETED.COLUMN1 != INSERTED.COLUMN1 AND TABLE1.COLUMN2 = DELETED.COLUMN2 AND DELETED.COLUMN2 != INSERTED.COLUMN2) != 0 BEGIN RAISERROR 40001 'Parent table PRIMARY_TABLE cannot change Primary Key with dependant rows in TABLE1' ROLLBACK TRAN RETURN END END go /* UPDATE TRIGGER for: TABLE1 */ CREATE TRIGGER TABLE1_UPDTRIG ON TABLE1 FOR UPDATE AS declare @num_rows int select @num_rows = @@rowcount If @num_rows = 0 return if update(COLUMN1) OR update(COLUMN2) BEGIN /* CONSTRAINT1 update change restrict */ IF (SELECT COUNT(*) FROM PRIMARY_TABLE, inserted where PRIMARY_TABLE.COLUMN1 = INSERTED.COLUMN1 AND PRIMARY_TABLE.COLUMN2 = INSERTED.COLUMN2) != @num_rows BEGIN RAISERROR 40005 'not in PRIMARY_TABLE when changing child TABLE1' ROLLBACK TRAN RETURN END END go ### 13. Datatype mapping Usage : To configure the desired data type mapping to be used for conversion of SQLs from one database to another overriding the default data type mapping provided by the SwisSQL Console tool . Two types of data type mapping configuration: a.Global Datatype mapping Global datatype mapping, where in, any number of source data type and the mapped data type can be added by the user to the Global Settings table as shown below . The source data type will be overriden by the corresponding mapped data type in all the subsequent conversions for all the tables. Example :: Source Datatype = BLOB Target Datatype = BLOB(100M) In this case the Datatype BLOB will be overridden by BLOB(100M) during the conversion . b. Table Level Mapping Mapping at the Table column level, can be used to specify the mapped datatype for a particular table column. During conversion when the specified column name is encountered (with that specifed table), then the original data type is overriden by the corresponding mapped datatype specified by the user. You can provide the Table level mapping by selecting 'Table Level' in the combo of the Dialog shown below . In this Dialog we need to provide the Table Name, Column Name and the Target Datatype in the 1st,2nd and 3rd columns respectively . Example :: Source Table Name = TABLE1 Source Column Name = COLUMN1 Target Datatype = BLOB(2G) c. Identity Mapping A column can be specified as a identity column during conversion to SQL Server. The identity value specified will be added to the specified column when the CREATE TABLE statement is converted to SQL Server. In the Table shown below, we need to provide the Table Name, Column Name and the Identity in the 1st,2nd and 3rd columns respectively . Example :: Source Table Name = TABLE1 Source Column Name = COLUMN1 Identity = IDENTITY(1,4)

© 2011 ZOHO Corporation