[DDL] Subsection

GENERATE_DROP_TABLE - If Yes is specified, the DROP TABLE statement is generated before each CREATE TABLE statement. The default value is No. Possible values - Yes, No.

DROP_TABLE_CASCADE_CONSTRAINTS - If Yes is specified, the CASCADE CONSTRAINTS option is generated in the DROP TABLE statement. Currently this option is supported by Oracle only. For more information, see Dropping Tables.

This option is only available if the target database is Oracle and the GENERATE_DROP_TABLE is set to Yes. The default value is No.

GENERATE_DROP_INDEX - If Yes is specified, the DROP INDEX statement is generated before each CREATE INDEX statement. The default value is No. Possible values - Yes, No.

This option can be helpful when the scripts for indexes are re-executed without recreating the table.

OUTSCHEMA - for the /OSN option (Output schema name).

EMPTY_SCHEMA - for the /EMPS option. Possible values - Yes, No.

COLUMN_NAME_CASE - This option specifies the case of column names in the SQL statements. Possible values - Upper, Lower. If no value is specified, the case of the column names is not changed and column names are used as they are provided by source database.

USE_CONSTRAINT_NAMES - If Yes is specified, constraint names of the source database will be used in generated DDL scripts. Otherwise constraint names will be skipped. The default value is No. Possible values - Yes, No.

PK_COLS_NOTNULL - This option specifies to force NOT NULL constraints in the CREATE TABLE statement for columns making up a primary key. The default value is Yes. Possible values - Yes, No.

Some databases (Oracle, Sybase, Access e.g.) allow not specifying NOT NULL constraints for primary key columns explicitly when creating the table, and they change columns to NOT NULL when adding a primary key. Other databases (IBM DB2, Microsoft SQL Server, MySQL e.g.) require primary key columns to be created with NOT NULL constraints before adding a primary key.

The default value is Yes (all primary key columns will be created with NOT NULL constraints). Possible values - Yes, No.

CONVERT_IDENTITY_COLUMNS - If Yes is specified, SQLWays converts the identity properties of columns. Otherwise, SQLWays does not extract the identity properties. The default value is Yes. Possible values - Yes, No.

REMOVE_NOTNULL_EXCEPT_PK - If Yes is specified, the NOT NULL constraint for all columns except primary key columns in the target database are removed to avoid their conversion between databases by SQLWays. The default value is No. Possible values - Yes, No.

Identity columns are commonly used in conjunction with primary key constraints to serve as the unique row identifier for the table.

INSERT_COMMIT_COUNT - This option is only valid for INS output format and is used to generate a COMMIT statement after the specified number of INSERT statements are generated. The default value is 0 (no commits are generated).

STATEMENT_DELIMITER - for the /STDEL option (Statement termination character).

STATISTICS_STATEMENTS - This option specifies whether or not to generate statements to calculate statistics on tables in the DDL scripts for table indexes.The default value is Yes. Possible values - Yes, No. Currently SQLWays generates statistics statements for IBM DB2 only.

PK_UNIQUE_INDEXES - This option specifies whether to generate indexes on primary keys and unique constraints. The default value is No. This means no index scripts are generated for primary keys and unique constraints. In this case the database creates unique indexes for primary keys and unique constraints implicitly.

If the unique indexes with the same definition as required for primary keys and unique constraints were created before creating primary keys and unique constraints, the database uses these indexes for primary keys and unique constraints.

When PK_UNIQUE_INDEXES=ALWAYS is specified, the index scripts for primary and unique constraints are always created. This allows creating indexes before creating primary keys and unique constraints.

REPLACE_NOT_ALLOWED_CHARS - This option specifies a character or a string that replaces not allowed characters in identifiers in the target database.

The source database may allow users to use characters in identifiers (table and column names etc.) that are not allowed in the target database.

For example, users can use @ in identifiers in Microsoft SQL Server, but this character is not allowed in identifiers in Oracle.

If no value is specified in this option and "remove_not_allowed_chars=no", then all not allowed characters are left in identifiers for the target database.

REMOVE_NOT_ALLOWED_CHARS - This option is similar to the replace_not_allowed_chars option, except that it allows you to remove not allowed characters in identifiers in the target database.

When Yes is specified, all not allowed characters are removed in identifiers in the target database.

REPLACE_NOT_ALLOWED_CHARS_FPOS - This option specifies a character or a string that replaces not allowed characters at the first position of identifiers in the target database.

Characters that are allowed at the first position of identifiers in the source database may not be allowed in the target database.

For example, Microsoft SQL Server identifiers can begin with _ (underscore) while Oracle identifiers cannot.

If no value is specified in this option and "remove_not_allowed_chars_fpos=no", then all not allowed characters are left at the first position of identifiers for the target database.

REMOVE_NOT_ALLOWED_CHARS_FPOS - This option is similar to the replace_not_allowed_chars_fpos option, except that it allows users to remove not allowed characters at the first position of identifiers in the target database.

When Yes is specified, all not allowed characters are removed at the first position of identifiers in the target database.

_NAMES_EXCEEDING_MAX_LEN - Databases may have different identifier length limits. For example, the maximum identifier length in SQL Server is 128 characters while in Oracle - 30. An error occurs when a table with the name exceeding 30 characters is migrated from SQL Server to Oracle.

If Yes is specified, SQLWays trims off identifiers exceeding the maximum length for the target database. If during the trimming SQLWays gets identical identifiers, it replaces the last characters of these identifiers (except for the first identifier) with the numbers in the ascending order beginning with "2".

For example, if after trimming SQLWays gets two identical identifiers like "home_phone_numbers" etc., they are further converted to: "home_phone_numbers" and "home_phone_number2".

If No is specified, SQLWays does not trim identifiers exceeding the maximum length for the target database.

TRIM_RULE_ALPHANUM_ONLY - This option is used in conjunction with the "trim_names_exceeding_max_len=yes" option.

If Yes is specified and an identifier exceeds the maximum length for the target database, SQLWays first of all deletes all non-alphanumeric characters and then, if the identifier's length is still more than the maximum length, it is trimmed. If No is specified, SQLWays immediately trims the identifiers without deleting the non-alphanumeric characters.

For example, if Yes is specified, SQL Server identifier like "regional_customer_account_number#" (33 characters) is converted to "regionalcustomeraccountnumber" (29 characters).

REPLACE_RESERVED_WORDS - This option is used to change identifiers of the source database that served as reserved words in the target database. The option specifies a template for reserved words replacement.

For example, if %RWORD%_ is specified, underscore character is added to the right of all reserved words.

The default value of the template is %RWORD% which means that the reserved words are not changed and are delimited in SQL statements for the target database. The delimeter depends on the database, see Delimeted Identifies for more information.

ANSI_QUOTED_IDENTIFIERS - When ANSI_QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks.

When ANSI_QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

The default value for ANSI_QUOTED_IDENTIFIER is ON. This option is applicable for MSSQL and Sybase ASE.

CONVERT_INDEXES - Specifies whether to convert indexes or not. The default value is All, in this case all indexes will be converted. Possible values are unique and none.

VAR_PREFIX - For variables, when some special characters are used to identify them in the source database, like in SQL Server (@), this option identifies symbols that substitute special symbol. Default value is v_

REMOVE_VAR_PREFIX - Specifies whether to remove or not variable prefix (like '@' in SQL Server and Sybase) during the conversion. Default value is 'no'.

TARGET_FILES_GROUPING - If the conversion of the SCHEMA only is made this option can control how to store the SQL converted, As the default sql for each object is stored in its own file. ALL - means that all the objects will be grouped into one file. OBJECT is the default.

REMOVE_SPECIAL_COLUMNS - This option was created for the direction from MSSQL to Oracle. When this option is set to "yes" (default value is "no") while converting table with TIMESTAMP column, this column is removed from target table (not converted).

START_VALUE_EXTRACTION_WITH_SEQUENCE_INCREMENT - This option was created for the direction from DB2 to Oracle. If this option is set to "yes" starting value for converted sequence would be the current value of source sequense plus the value specified in INCREMENT BY clause. Source: CREATE SEQUENCE ORDER_SEQ START WITH 1 INCREMENT BY 2 NO MAXVALUE NO CYCLE CACHE 24# (current sequence value is 3) Equivalent: CREATE SEQUENCE ORDER_SEQ INCREMENT BY 2 START WITH 5 MAXVALUE 2147483647 MINVALUE 1 NOCYCLE CACHE 24 NOORDER; When this option is set to "no" (default) Equivalent: CREATE SEQUENCE ORDER_SEQ INCREMENT BY 2 START WITH 1 MAXVALUE 2147483647 MINVALUE 1 NOCYCLE CACHE 24 NOORDER;

EXPORT_CONSTRAINTS_FOR_QUERIES - If this option is set to "yes" (default) and objects (tables) are selected for conversion using query, along with the export of the object (table) itself it's constraints (primary, foreign keys, etc.) will be also exported. But if you set this option to "no" object (table) will be converted without it's constraints.

CONVERT_DATABASE_TO_SCHEMA - This option was created for the direction from Sybase ASE to Oracle. The default value is "no". When this option is set to "yes" source DB name will be used as schema name in the target. Example: source: schema1.table1 target: db1.table1.

cd2s_sch_to_obj_name - This option was created for direction from Sybase ASE to DB2. This option is used only when option CONVERT_DATABASE_TO_SCHEMA is set to "yes". Option cd2s_sch_to_obj_name has "no" as a default value. When cd2s_sch_to_obj_name=yes schema name would be padded to object name like in the example below Source; db1.sch1.tab2 Equivalent: db1.sch1_tab2 When cd2s_sch_to_obj_name=no schema name would be omitted: Source; db1.sch1.tab2 Equivalent: db1.tab2

Ispirer SQLWays Database Migration Software

[DDL] Subsection