Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
|
guides:migration-toolkit:command-line:sqlways-ini:postgresql-section [2019/11/16 09:51] darya.prikhodkina |
guides:migration-toolkit:command-line:sqlways-ini:postgresql-section [2024/04/26 14:06] uliana.philippova |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ** About Ispirer Systems ** | ||
| + | |||
| + | [[https://www.ispirer.com|Ispirer Home Page]] | ||
| + | [[https://www.ispirer.com/products/database-migration|Database Migration]] | ||
| + | [[https://www.ispirer.com/application-conversion|Application Conversion]] | ||
| + | [[https://www.ispirer.com/download|Downloads]] | ||
| + | ---- | ||
| ====== Sqlways.ini File - Section [Postgre] ====== | ====== Sqlways.ini File - Section [Postgre] ====== | ||
| Line 4: | Line 11: | ||
| This article describes sqlways.ini file's [Postgre] section and the options it contains. | This article describes sqlways.ini file's [Postgre] section and the options it contains. | ||
| ^ Option name ^ Description ^ | ^ Option name ^ Description ^ | ||
| - | | **HOST** | This option sets the host name where import will be done. | | + | | **HOST** | This option sets the host name where the import will be done. | |
| - | | **DATABASE** | This option sets the database name where import will be done. | | + | | **DATABASE** | This option sets the database name where the import will be done. | |
| - | | **USER** | This option sets user account that will be used to connect to PostgreSQL database. | | + | | **USER** | This option sets the user account that will be used to connect to the PostgreSQL database. | |
| - | | **PWD** | This option sets password that will be used together with the user account to connect to PostgreSQL database. | | + | | **PWD** | This option sets the password that will be used together with the user account to connect to the PostgreSQL database. | |
| - | | **PORT** | This option sets port number that will be used to connect to PostgreSQL database. | | + | | **PORT** | This option sets the port number that will be used to connect to the PostgreSQL database. | |
| - | | **TARGET_VERSION** | This option sets the version number of the database where import will be done. | | + | | **TARGET_VERSION** | This option sets the version number of the database where the import will be done. | |
| - | | **BIN** | Specifies the directory where PSQL.exe utility is located. | | + | | **BIN** | This option sets the directory where the PSQL.exe utility is located. | |
| - | | **USE_EXPLICIT_CURSORS** | This option controls the way cursors will be converted in PostgreSQL database. If this option is set to "Yes", explicit cursor declaration will be used. If set to "No", implicit cursor declaration will be used. \\ Possible values: Yes, No. | | + | | **USE_EXPLICIT_CURSORS** | This option controls the way cursors will be converted to the PostgreSQL database. If this option is set to "Yes", an explicit cursor declaration will be used. If set to "No", an implicit cursor declaration will be used. \\ Possible values: "Yes", "No". | |
| - | | **REPLACE_NULL_BYTE_WITH** | In PostgreSQL it is not allowed to insert null byte in not BYTEA data type column. So this option controls how the null byte should be changed to make correct import. \\ If the data type of the inserted column is BYTEA, this option will not work for that case. | | + | | **REPLACE_NULL_BYTE_WITH** | In PostgreSQL it is not allowed to insert a null byte into a non-BYTEA data type column. Therefore, this option controls how the null byte should be changed to make correct import. \\ If the data type of the inserted column is BYTEA, this option will not work for that case. | |
| - | | **SET_SEARCH_PATH_TO** | This option will add "SET search_path TO" statement into each sql file generated by SQLWays Wizard tool. To use this option you just need to specify a schema name that will be used in SET search_path TO statement. \\ By default this option is empty. | | + | | **SET_SEARCH_PATH_TO** | This option will add a "SET search_path TO" statement into each sql file generated by SQLWays Wizard. To use this option, you just need to specify a schema name that will be used in the "SET search_path TO" statement. \\ By default this option is empty. | |
| - | | **DATA_LOAD_FORMAT** | This option defines the format the data from MSSQL database will be extracted and loaded into PostgreSQL database. Possible values - "HEX" and "OCT". | | + | | **DATA_LOAD_FORMAT** | This option defines the format in which data from the SQL Server database will be extracted and loaded into the PostgreSQL database. Possible values: "HEX" and "OCT". | |
| - | | **DOLLAR_QUOTED_PGSQL_MASK** | This option controls dollar quotation in the pgsql. The value specified here will be used instead of "$$" in Pl/PgSQL, which is set by default. | | + | | **DOLLAR_QUOTED_PGSQL_MASK** | This option controls the dollar quotation in pgsql. The value specified here will be used instead of "$$" in Pl/PgSQL, which is set by default. | |
| - | | **MULTIPLE_OUT_REFCURSOR_PARAM** | This option controls the conversion of Oracle multiple OUT refcursor parameters in PostgreSQL. If this option is set to "Yes", OUT REFCURSOR parameters will be converted into OUT REFCURSOR parameters in PostgreSQL. If "No", Oracle procedure with OUT REFCURSORs parameters will be converted into a function which returns setof refcursors. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "Yes".| | + | | **MULTIPLE_OUT_REFCURSOR_PARAM** | This option controls the conversion of Oracle multiple OUT REFCURSOR parameters to PostgreSQL. If this option is set to "Yes", the OUT REFCURSOR parameters will be converted to OUT REFCURSOR parameters in PostgreSQL. If "No", the Oracle procedure with OUT REFCURSOR parameters will be converted to a function which returns setof refcursors. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "Yes".| |
| - | | **AUTOUPDATE_TIMESTAMP_COLUMN** | This option controls the way how TIMESTAMP column is converted from Sybase ASE to PostgreSQL database. If this option is set to "Yes", then 2 triggers will be generated in PostgreSQL and they will update information in a column. If this option is set to "No", then it will be a common column of BYTEA data type. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "No" or Empty. | USE_TABLE_FUNCTIONS | + | | **AUTOUPDATE_TIMESTAMP_COLUMN** | This option controls the way how the TIMESTAMP column is converted from the Sybase ASE to the PostgreSQL database. If this option is set to "Yes", then 2 triggers will be generated in PostgreSQL and they will update the information in the column. If this option is set to "No", then it will be a common column of the BYTEA data type. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | |
| - | | **USE_TABLE_FUNCTIONS** | This option controls how Sybase ASE procedures that return result sets will be converted to PostgreSQL. If this option is set to "Yes", then procedures will be converted to table functions. If it is set to "No" then such procedures will be converted into functions that return result sets of defined data type. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "No" or Empty. | | + | | **USE_TABLE_FUNCTIONS** | This option controls how Sybase ASE procedures that return result sets will be converted to PostgreSQL. If this option is set to "Yes", then the procedures will be converted to the table functions. If it is set to "No", then such procedures will be converted into the functions that return result sets of a defined data type. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | |
| - | | **VIRT_COL_SAVE_TBL_NAME** | This option works only for Oracle tables with virtual columns that should be converted to PostgreSQL. And it controls naming of the generated tables and views in PostgreSQL. If this option is set to "Yes" then generated tables will have the same names as in Oracle database and SQLWays will add suffix "_VW" to the generated views. If this option is set to "No", then the suffix "_TBL" will be added to the names of converted tables and generated views will have the same names as Oracle tables with virtual columns. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "No" or Empty. | | + | | **VIRT_COL_SAVE_TBL_NAME** | This option works only for Oracle tables with virtual columns that should be converted to PostgreSQL. And it controls naming of the generated tables and views in PostgreSQL. If this option is set to "Yes", then the generated tables will have the same names as in the Oracle database and SQLWays will add suffix "_VW" to the generated views. If this option is set to "No", then the suffix "_TBL" will be added to the names of converted tables and the generated views will have the same names as the Oracle tables with virtual columns. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | |
| - | | **ROWNUM_TO_GENERATE_SERIES** | This option controls how Oracle Rownum function will be converted to PostgreSQL database. If this option is set to "Yes", it will be converted to GENERATE_SERIES. If this option is set to "No", then Oracle Rownum function will be converted to ROW_NUMBER() with OVER clause in PostgreSQL. \\ Possible values - "Yes" or Empty, "No". \\ Default value - "Yes" or Empty. | | + | | **ROWNUM_TO_GENERATE_SERIES** | This option controls how the Oracle Rownum function will be converted to the PostgreSQL database. If this option is set to "Yes", it will be converted to GENERATE_SERIES. If this option is set to "No", then the Oracle Rownum function will be converted to ROW_NUMBER() with OVER clause in PostgreSQL. \\ Possible values: "Yes" or Empty, "No". \\ Default value: "Yes" or Empty. | |
| - | | **CAST_PARAMETERS_IN_CALL** | This option controls whether the parameters in call routine statements should be casted to the appropriate data types in accordance to the routine signature. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "No" or Empty. | | + | | **CAST_PARAMETERS_IN_CALL** | This option controls whether the parameters in call routine statements should be casted to the appropriate data types in accordance to the routine signature. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | |
| - | | **PARTITION_ENABLE** | This option is available for PostgreSQL version 10 and later and controls whether table partitioning should be migrated or no. To allow table partitions conversion, set this option to Yes. If table partitions conversion is not required, "No" value should be specified. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "Yes" or Empty. | | + | | **PARTITION_ENABLE** | This option is available for PostgreSQL version 10 and later and controls whether table partitioning should be migrated or not. To allow table partitions conversion, set this option to "Yes". If table partitions conversion is not required, the value should be set to "No". \\ Possible values: "Yes", "No" or Empty. \\ Default value: "Yes" or Empty. | |
| - | | **TABLES_WITH_OIDS** | This option defines whether the tables will be created with OID. OID column is a system column created for each table. It contains the unique identifier for the row of data in the database and can be used as a unique pointer to the exact record in the database. By default this option is set to "Yes". This means that all the CREATE TABLE statements will be created with the WITH OIDS option. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "Yes". | | + | | **TABLES_WITH_OIDS** | This option defines whether the tables will be created with OID. The OID column is a system column created for each table. It contains the unique identifier for the row of data in the database and can be used as a unique pointer to an exact record in the database. By default this option is set to "No". This means that all the CREATE TABLE statements will be created with the WITH OIDS option. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No". | |
| - | | **TABLESPACE** | In this option you can specify the tablespace name that will be used in CREATE TABLE statements in PostgreSQL. \\ Default value - Empty. | | + | | **TABLESPACE** | With this option, you can specify the tablespace name that will be used in CREATE TABLE statements in PostgreSQL. \\ Default value: Empty. | |
| - | | **INDEX_TABLESPACE** | In this option you can specify the tablespace name that will be used in CREATE INDEX statements in PostgreSQL. \\ Default value - Empty. | | + | | **INDEX_TABLESPACE** | With this option, you can specify the tablespace name that will be used in CREATE INDEX statements in PostgreSQL. \\ Default value: Empty. | |
| - | | **USE_SOURCE_TABLESPACE_NAMES** | This option defines whether the original tablespace names will be used in the converted object definitions. \\ Possible values - "Yes", "No" or Empty. \\ Default value - "No" or Empty. | | + | | **USE_SOURCE_TABLESPACE_NAMES** | This option defines whether the original tablespace names will be used in the converted object definitions. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | |
| - | | **DATA_MIGRATOR_BIN_DIRECTORY** | This option allows you to specify the path to the folder which contains the ISOFDM.exe tool. Usually the path specification is not required as SQLWays automatically searches for the BIN directory. If the text-box is empty, first it will search for the PATH variable, then it will search the registry. If it fails to find the path to the ISOFDM.exe, no path will be added to the command and system may return the error on the command execution. \\ Default value - Empty. | | + | | **DATA_MIGRATOR_BIN_DIRECTORY** | This option allows to specify the path to the folder which contains the ISOFDM.exe tool. Usually the path specification is not required as SQLWays automatically searches for the BIN directory. If the text-box is empty, first it will search the PATH variable, then it will search the registry. If the path to ISOFDM.exe cannot be found, no path will be added to the command and system may return an error when executing the command. \\ Default value: Empty. | |
| + | | **RAISERROR_TO_SWP** | This option defines the way of the RAISERROR statements conversion when migrating from Sybase ASA to PostgreSQL. If this option is set to “Yes”, then the RAISERROR statements will be converted to the user defined SWP_RAISERROR procedures in PostgreSQL. If this option is set to “No”, then it will be converted to the RAISE EXCEPTION statements. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. \\ NOTE: The procedure defined SWP_RAISERROR will be generated by Ispirer Toolkit automatically. | | ||
| + | | **USE_SSL** | This option defines whether SSL will be used to connect to the target database. If this option is set to “Yes”, then the tool will generate a command line that will use the information from the SSL options (SSL_MODE, CLIENT_CERTIFICATE, CLIENT_KEY, ROOT_CERTIFICATE) in order to connect to the target database using the SSL connection. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | | ||
| + | | **SSL_MODE** | This option defines the SSL connection mode to the target database. This option is only taken into account when the USE_SSL option is set to “Yes”. \\ Possible values: allow, disable, prefer, requirer, verify-ca, verify-full. | | ||
| + | | **CLIENT_CERTIFICATE** | This option defines the path to the Client SSL Certificate. This option is only taken into account when the USE_SSL option | ||
| + | is set to “Yes”. | | ||
| + | | **CLIENT_KEY** | This option defines the path to the Client SSL Key. This option is only taken into account when the USE_SSL option is set to “Yes”. | | ||
| + | | **ROOT_CERTIFICATE** | This option defines the path to the Root Certificate. This option is only taken into account when the USE_SSL option is set to “Yes”. | | ||
| + | | **RETURN_RESULT_FROM_SP_AND_FN** | This option determines the type of returned parameter of functions and procedures. By default, table functions will be converted to a function with RETURN TABLE. Other functions and procedures will be converted to functions with SETOF. If the conversion tool was not able to define the data types for the result set, REFCURSOR will be used as a returned result. If this option is set to "Table", "Setof" or "Refcursor", then the return type will be TABLE, Setof or Refcursor respectively. Possible values: "Table", "Setof", "REFCURSOR" and Empty. Default value: Empty. \\ [[samples:options-examples #[Usage example]:RETURN_RESULT_FROM_SP_AND_FN|Usage example]].| | ||
| + | | **CONVERT_TBL_PAR** | This option determines how the table type parameters will be converted to PostgreSQL. If this option is empty, then the table type parameters will be converted to a VARCHAR parameter that will store the table name. The table name will be used instead of the parameter inside the body as well. If this option is set to JSON or JSONB, then the table type parameters will be converted to JSON\JSONB data type. The functions that work with JSON\JSONB will be used inside the object as well. This option works only for the Microsoft SQL Server to PostgreSQL direction. \\ Possible values: "JSON", "JSONB", Empty. \\ Default value: Empty. | | ||
| + | | **SELECT_WITHOUT_FROM_TO_RAISE_NOTICE** | This option determines how to convert SELECT statements without a FROM clause. If this option is set to “Yes”, then SELECT statements without FROM will be converted to RAISE NOTICE in PostgreSQL. If it is set to “No”, then will be converted as it is. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | | ||
| + | | **USER_TYPE_TO_BASE_TYPE** | This option determines how to convert user defined types. If this options is set to “Yes”, then the user defined types will be converted to the base data types. If it is set to “No”, then the user defined types will be left as it is. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | | ||
| + | | **RETURN_WITH_SEL_TO_RASIE_NOTICE** | This option defines the conversion of objects that returns both result set from a SELECT and RETURN statement. If this option is set to “Yes” in PostgreSQL, an object will be generated that return the result set from a query, and the RETURN statement will be converted to a RAISE NOTICE. If this option is set to “No” or empty, then the parameter returning the value from the RETURN statement will be added to OUT. \\ Possible values: "Yes", "No" or Empty. \\ Default value: "No" or Empty. | | ||
| + | | **IDENTITY_OPTIONS** | This option defines the conversion of Identity options when migrating from Sybase ASE to PostgreSQL. If this option is set to Remove, then the identity options START and INCREMENT will be ommited in the target. If this option is set to empty or “All”, then the default conversion will be used, and all options will be saved. \\ Possible values: "Remove", "All" or Empty. \\ Default value: "All" or Empty. | | ||
| + | | **TABLE_TYPE_CONVERSION** | This option controls the way how the Oracle table data types like TYPE IS TABLE, VARRAY are converted to PostgreSQL. If the option is set to "Array", then the arrays will be used instead of the types, and the types will not be created in PostgreSQL. \\ If the option is set to "Tables", then the temporary tables will be used. \\ Possible values: “Arrays”, "Tables" or Empty. Default value: "Arrays" or Empty. \\ [[samples:options-examples #[Usage Example]:TABLE_TYPE_CONVERSION|Usage example]] | | ||
| + | | **IDENTITY_TO_SERIAL** | This option controls the way how the column with the IDENTITY property may be converted. \\ If the option is set to "No" or Empty, the IDENTITY property will be moved for PostgreSQL. \\ If the option is set to "Yes", the IDENTITY property specified for the column will be converted as SERIAL for PostgreSQL. \\ Possible values: “Yes”, "No" or Empty. Default value: "No" or Empty. \\ [[samples:options-examples #[Usage Example]:IDENTITY_TO_SERIAL|Usage example]]| | ||
| + | | **IDENTITY_COLUMN_TYPE** | This option changes the definition of the IDENTITY column. If the option set to "Always" or left empty, it is generated as GENERATED ALWAYS AS IDENTITY. Otherwise, it will be generated as GENERATED BY DEFAULT AS IDENTITY. \\ Possible values: "Default", "Always" or Empty. \\ Default value: "Always" or Empty. \\ [[samples:options-examples #[[Usage example]: IDENTITY_COLUMN_TYPE|Usage example]] | | ||
| + | | **TRIGGER_RECURSION_LVL** | This option adds the check of the trigger nesting level to the triggers with reсursion. By default, the option is empty and no check will be added. If the option has the value n, where n - is an integer number, then the WHEN statement (pg_trigger_depth() < n+1) will be added before the trigger function is called. This check will let the trigger function to be executed n times. Possible values: any positive integer value. \\ [[samples:options-examples #[[Usage example]: TRIGGER_RECURSION_LVL |Usage example]] | | ||
| + | | **AUTONOMOUS_TRANSACTION_TO_DBLINK** | This option controls how to convert procedures and functions with the AUTONOMOUS TRANSACTION pragma to the PostgreSQL database. If this option is set to “Yes”, these procedures will be converted to the recursive procedures with dblink to the remote database. An additional script will be generated in which FOREIGN SERVER and USER MAPPING will be created for the current user. This script should be executed as the superuser. \\ Possible values: "Yes", "No". \\ Default value: "No". \\ [[samples:options-examples #[[Usage example]:AUTONOMOUS_TRANSACTION_TO_DBLINK |Usage example]] | | ||
| + | | **SECURITY_DEFINER** | This option allows to convert the PostgreSQL routines to SECURITY DEFINER. Please note that if any transaction control statements are mentioned in the source code, they will be commented out since they are not allowed in such functions/procedures.\\ Possible values: "Yes", "No" or Empty.\\ Default value: "No" or Empty. \\ [[samples:options-examples #[[Usage example]:SECURITY_DEFINER|Usage example]] | | ||
| + | | **TRIG_PROC_SCHEMA_PREFIX ** | These options allow to add a prefix to the names of the schemas in which trigger functions will be created. A new schema will be created automatically. It may be useful if there is a need to separate them from user functions. \\ [[samples:options-examples #[[Usage example]: TRIG_PROC_SCHEMA_PREFIX and TRIG_PROC_SCHEMA_SUFFIX |Usage example]] | | ||
| + | | **TRIG_PROC_SCHEMA_SUFFIX ** | These options allow to add a suffix to the names of the schemas in which trigger functions will be created. A new schema will be created automatically. It may be useful if there is a need to separate them from user functions. \\ [[samples:options-examples #[[Usage example]: TRIG_PROC_SCHEMA_PREFIX and TRIG_PROC_SCHEMA_SUFFIX |Usage example]] | | ||
| **Example**: | **Example**: | ||
| Line 40: | Line 67: | ||
| | | ||
| \\ | \\ | ||
| - | ---- | + | |
| - | === More about Ispirer Systems === | + | |
| - | [[https://www.ispirer.com|Ispirer Home Page]] | + | |
| - | [[https://www.ispirer.com/products/database-migration|Database Migration]] | + | |
| - | [[https://www.ispirer.com/application-conversion|Application Conversion]] | + | |
| - | [[https://www.ispirer.com/download|Downloads]] | + | |