Sqlways.ini File - Section [DDL]

This article describes sqlways.ini file [DDL] section and the options it contains.

Option name	Description
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. This option is only available if the target database is Oracle and 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 scripts for indexes are re-executed without recreating the table.
OUTSCHEMA	Output schema name.
EMPTY_SCHEMA	for /EMPS option. Possible values - Yes, No.
COLUMN_NAME_CASE	This option specifies the case of column names in 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 in the 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 is used to set the 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 to not specify the NOT NULL constraints for primary key columns explicitly when creating a 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 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 constraints 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 a unique row identifier for a table.
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 is generated. The default value is 0 (no commits are generated).
STATEMENT_DELIMITER	Statement termination character.
STATISTICS_STATEMENTS	This option specifies whether statements to calculate statistics on tables in DDL scripts for table indexes should be generated.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 indexes on primary keys and unique constraints should be generated. 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, index scripts for primary and unique constraints are always created. This allows to create 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.) which 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, but 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. The default value is Yes. Possible values - Yes, No.
REPLACE_NOT_ALLOWED_CHARS_FPOS	This option specifies a character or a string that replaces not allowed characters in the first position of identifiers in the target database. Characters that are allowed in 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 in 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, but it allows users to remove not allowed characters in the first position of identifiers in the target database. When Yes is specified, all not allowed characters are removed in the first position of identifiers in the target database. The default value is Yes. Possible values - Yes, No.
TRIM_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 a 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 the first identifier) with 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. The default value is Yes. Possible values - Yes, No.
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). The default value is Yes. Possible values - Yes, No.
REPLACE_RESERVED_WORDS	This option is used to change identifiers of the source database which 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 a database.
CONVERT_DATABASE_TO_SCHEMA	This option is used to convert fully qualified names with the database specification. It is required to set this option to Yes to get it work.
CONVERT_PACKAGE_TO_SCHEMA	This option is used to convert the Package name as the schema from an Oracle database. Currently this feature works for MSSQL as a target database only. It is required to set this option to Yes to get it work.
BUILTIN_FUNCTIONS_NULLIF_CHECK	This option is used to generate NULLIF check when converting LTRIM, RTRIM, SUBSTRING, RIGHT, REPLICATE functions and COALESCE(@some_value,'') for the second parameter of the CHARINDEX function from Sybase ASE to Microsoft SQL Server. This option is required for compatibility of these functions when empty strings, NULL values or zero lengths are processed. It is required to set this option to Yes to get it work.
FULLY_QUALIFY_IDENTIFIERS	This option is used to generate schema for each referenced object without schema inside a View, Stored Procedure or Function. This option works only if the EMPTY_SCHEMA option is set to No. The default value is No. Possible values - Yes, No.
CONVERT_ROUTINE_TO_SP_RESULTSET	If it's Yes, then all the functions and procedures that return values must be converted to stored procedures that return resultset via the SELECT statement. The default value is No or Empty. Possible values - Yes, No.
OUT_REFCUR_FIRST_POSITION	This option works together with CONVERT_ROUTINE_TO_SP_RESULTSET and puts OUT parameter to the first position in the parameter list. Also please note, that this option will work only for Oracle version 11 and lower. For Oracle version 12 and higher OUT parameter will not be generated, and will be used PIPELINE functionality. Possible values - “Yes”, “No” or Empty. Default values - “No” or Empty.
MIGRATE_MEMBERSHIP	This option controls the way the Groups are converted. When MIGRATE_MEMBERSHIP=No then groups are migrated as simple users and default permissions are used for them, E.G.: For SAP ASA to Microsoft SQL Server db_datareader and db_datawriter are specified. The default value is Yes. Possible values - Yes, No.
TARGET_FILES_GROUPING	This option controls the way the result SQL files are generated. When data export is switched off and the TARGET_FILES_GROUPING = YES, then separate SQL files are generated each for each object extracted from the source database. If the TARGET_FILES_GROUPING=ALL, then all the generated SQL is stored in one large file. The default value is empty. Possible values - ALL.
CONVERT_GLOBAL_VARIABLES_TO_LOCAL	This option controls the way global or user variables are converted by SQLWays. When the option is set to Yes, all global variables are converted to local variables declared and used inside the SP or FN only. If the option is set to No, then global variables are converted to their equivalent, automatically generated by SQLWays. The default value is No. Possible values - Yes, No.
PKG_VARIABLES	This option controls the way global variables are stored in the target database. When the option is set to UNION_BY_PACKAGE, all package variables, that belong to one package, are stored in one table per package, where column names correspond to variable names and values are stored each in its own column. If the option is set to UNION_BY_TYPE, then all global variables are stored in tables, each for its own type group, where variable name is stored as the value of the column with the corresponding variable value in the corresponding row. If the option is set to LOCAL, then all the global variables are converted to local variables. Please use this option carefully to avoid import issues, as not all the source cases can be converted using this option. The default value is UNION_BY_TYPE. Possible values - UNION_BY_TYPE, UNION_BY_PACKAGE and LOCAL.
CONVERT_PK_INDEX	This option controls the way the indexes, created on primary column in DB2, are converted by SQLWays. When the option is set to Yes, the indexes on primary columns are converted to their equivalent. If the option is set to No, then indexes are commented by SQLWays. The default value is Yes. Possible values - Yes, No. Currently this option works only for DB2 to Oracle migration direction.
FN_RET_MULTIPLE_VALUES_TO_TABLE_FN	This option controls the way informix functions, which return multiple values, are converted to MSSQL Server. If this option is set as Yes, SQLWays will convert it as Table Function in MSSQL, if NO - procedure with out parameters. The default value is No. Possible values - Yes, No.
SW_SEQ_SUFFIX	This option adds suffix to the sequences generated automatically by Ispirer SQLWays Wizard. Default value is “SWS_”. If both options SW_SEQ_SUFFIX and SW_SEQ_PREFIX are empty default suffix(“SWS_”) and prefix(“_ID”) for sequences will be used.
SW_SEQ_PREFIX	This option adds prefix to the sequences generated automatically by Ispirer SQLWays Wizard. Default value is “_ID”. If both options SW_SEQ_SUFFIX and SW_SEQ_PREFIX are empty suffix(“SWS_”) and prefix(“_ID”) for sequences will be used default.
SW_TRIG_SUFFIX	This option adds suffix to the triggers generated automatically by Ispirer SQLWays Wizard. Default value is “SWT_”. If both options SW_TRIG_SUFFIX and SW_TRIG_PREFIX are empty default suffix(“SWT_”) and prefix(“_ID”) for triggers will be used.
SW_TRIG_PREFIX	This option adds suffix to the triggers generated automatically by Ispirer SQLWays Wizard. Default value is “_ID”. If both options SW_TRIG_SUFFIX and SW_TRIG_PREFIX are empty suffix(“SWT_”) and prefix(“_ID”) for triggers will be used default.
ANSI_QUOTED_IDENTIFIERS	This option controls how to handle text inside the double quotes. If this option is empty or set to “No”, text in double quotes will be identified as a simple string. If this option is set to “Yes” SQLWays will handle text in double quotes as identifier names. Possible values - “Yes”, “No” or empty(the same as “No”). Default value - “No” or empty.
TARGET_BL	This option controls how business logic objects (procedures, functions, triggers, etc.) should be converted - to SQL objects or to Java classes. By default this option is set to “Default” and business logic objects will be converted to SQL objects. If this option is set to “Java” java classes will be generated as a result of conversion. Possible values - “Default” and “Java”.
CASCADE_CONSTRAINTS_NO_ACTION	If there is a referential relationship between a table and the parent row is altered in the parent table, an action, which should be done on child table, can be specified in constraint definition. If you set this option as “Yes”, this action will be set to “NO ACTION” in constraint. Default value - “No”. Possible values - “Yes”, “No”.
QUOTE_IDENTIFIERS	This option puts identifier names in quotes in target scripts. Possible values - “Always”, empty. Default - empty.
SYNONYM_TO_VIEW	This option controls how Oracle synonyms should be converted to PostgreSQL database. By default CREATE SYNONYM statement will be commented and all the synonyms identifiers will be changed to the original object names. If this option is set to YES, all the synonyms will be converted to views in PostgreSQL. Possible values - “Yes”, “No” or empty. Default - “No” or empty.
CONVERT_SP_RETSTATUS_OUTPARAM	If this option is set to “Yes” the source procedure will be converted to the procedure in the target with additional OUT parameter, that will return status of procedure execution. If this option is set to “No”, source procedure will be converted to the function with return statement. Possible values - “Yes”, “No”.
CONVERT_CHECK_CONSTRAINTS	This option defines whether check constraints should be converted. If this option is set to “Yes”, check constraints will be converted. If it is set to “No” - check constraints will not be converted. Possible values - Yes, No. Default value - Yes.
CONVERT_UNIQUE_CONSTRAINTS	This option defines whether unique constraints should be converted. If this option is set to “Yes”, unique constraints will be converted. If it is set to “No” - unique constraints will not be converted. Possible values - Yes, No. Default value - Yes.
SCHEMA_TO_DATABASE	This option controls schema names conversion for all specified objects for the migration process. If this option is set to “Yes”, converted objects will be created under the database with the same name as the source schema has. In that case, the schema name will be obtained from OUTSCHEMA option. If it is set to “No” - the source database structure will be saved. Possible values - Yes, No. Default value - No.
PARTITION_TABLESPACE	This option controls whether source partition table tablespaces should be converted or no. In case when the option is set into “Yes”, source tablespaces for table partitions will be included in migration solution and target definitions of table partitions will contain tablespaces equal to source tablespaces. If the option = “No”, tablespaces for table partitions will be ignored. Possible values - Yes, No. Default value - No.
REMOVE_VAR_PREFIX	This option controls the way the source variable prefix like “@” will be replaced from MS SQL Server, Sybase ASE databases inTO other target databases. In case the option is set to “Yes”, the SQLWays will just remove the variable prefix and variable name will be without any additional prefixes. If the option = “No”, the SQLWays will remove source prefix with the prefix defined in the VAR_PREFIX option. Default prefix that will be used is “v_”. User can specify its own prefix that will be used in conversion. Possible values - Yes, No. Default value - Yes.
VAR_PREFIX	This option defines what variable prefix will be used in variables when migrating from such databases as MS SQL Server and Sybase ASE. In this option a user can specify its own prefix. Default value - “v_”.
USE_CHAR_LENGTH	When migrating tables from Oracle to PostgreSQL, Greenplum or Redshift direction, information about the column length is taken from DATA_LENGTH column from ALL_TAB_COLUMNS system table. This column stores information about the number of bytes of CHAR\VARCHAR2 and NCHAR\NVARCHAR2 columns. And if you need to use the number of characters as a length of CHAR\VARCHAR2 and NCHAR\NVARCHAR2 columns, then you need to set this option to “Yes” and our tool will use information from ALL_TAB_COLUMNS from “CHAR_LENGTH” column. Possible values - “Yes”, “No” or Empty. Default value - “No” or Empty.

Example:

[DDL]
ANSI_QUOTED_IDENTIFIERS=Yes

If you have any questions about the supported options or some features that you may need during your migration project, please contact our technical team: support@ispirer.com

Ispirer Home Page Ispirer SQLWays Product Page Request SQLWays