Ispirer Website Ispirer Capabilities - PostgreSQL Migration Free Trial

Sqlways.ini File - Section [Postgre]

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

Option name	Description
HOST	This option sets the host name where import will be done.
DATABASE	This option sets the database name where import will be done.
USER	This option sets user account that will be used to connect to PostgreSQL database.
PWD	This option sets password that will be used together with the user account to connect to PostgreSQL database.
PORT	This option sets port number that will be used to connect to PostgreSQL database.
TARGET_VERSION	This option sets the version number of the database where import will be done.
BIN	Specifies the directory where 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.
REPLACE_NULL_BYTE_WITH	In PostgreSQL it is not allowed to insert null byte, except for BYTEA data type column. So this option controls how the null byte should be changed to make a correct import. If the data type of the inserted column is BYTEA, this option will not work for that case. For example: REPLACE_NULL_BYTE_WITH=N
SET_SEARCH_PATH_TO	This option will add “SET search_path TO” statement into each sql file generated by the SQLWays Wizard tool. To use this option you need just to specify a scheme name that will be used in SET search_path TO statement. By default this option is empty.
DATA_LOAD_FORMAT	This option defines the format of the data from MSSQL database that will be extracted and loaded into 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.
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”.
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	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.
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.
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.
CAST_PARAMETERS_IN_CALL	This option controls whether the parameters in call routine statements should be changed 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.
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 a 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”.
TABLESPACE	In 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.
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 searches for the BIN directory automatically. If the text-box is empty, it will search for the PATH variable first and 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.
OPT_DATA_LOAD_FORMAT	This option defines the format of data extracted from source database, that will be converted into BYTEA data type and loaded into PostgreSQL database. Possible values - Empty, “HEX”, “ESCAPE”. Default value - Empty.
RAISERROR_TO_SWP	This option defines the way of conversion of RAISERROR statements when migrating from Sybase ASA to PostgreSQL. If this option is set to “Yes”, then RAISERROR statements will be converted into user defined SWP_RAISERROR procedures in PostgreSQL. If this option is set to “No”, then it will be converted to RAISE EXCEPTION statements. Possible values - “Yes”, “No” or Empty. Default value - “No” or Empty. NOTE: procedure defined SWP_RAISERROR will be generated by Ispirer SQLWays tool 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 command line that will use 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 mode of SSL connection to the target database. This option is taken into account only when option USE_SSL is set to “Yes”. Possible values - allow, disable, prefer, requirer, verify-ca, verify-full.
CLIENT_CERTIFICATE	This option defines a path to the Client SSL Certificate. This option is taken into account only when option USE_SSL is set to “Yes”.
CLIENT_KEY	This option defines a path to the Client SSL Key. This option is taken into account only when option USE_SSL is set to “Yes”.
ROOT_CERTIFICATE	This option defines a path to the Root Certificate. This option is taken into account only when option USE_SSL 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 conversion tool was not able to define the data types for result set, REFCURSOR will be used as a returned result. If this option is set to “Table”, “Setof” or “Refcursor” then will be used TABLE, Setof or Refcursor as returned type accordingly. Possible values - “Table” , “Setof”, “REFCURSOR” and Empty. Default value - Empty.
CONVERT_TBL_PAR	This option determines how the parameters of table type will be converted in the PostgreSQL. If this option is empty, then 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 table type parameter 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 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 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 user defined types will be converted to base data types. If it is set to “No”, then 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 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 result set from a query, and RETURN statement will be converted to a RAISE NOTICE. If this option is set to “No” or empty, then the parameter to return value from RETURN statement will be added OUT . Possible values - “Yes” , “No” or Empty. Default value - “No” or Empty.
IDENTITY_OPTIONS	This option defines conversion of Identity optoins when migrating from Sybase ASE to PostgreSQL. If this option is set to Remove, then identity options START and INCREMENT will be ommited in the target. If this option is set to empty or “All”, then will be used default conversion, and all options will be saved. Possible values - “Remove” , “All” or Empty. Default value - “All” or Empty.

Example:

[Postgre]
HOST=hostname
DATABASE=test
USER=postgres
USE_EXPLICIT_CURSORS=Yes
SET_SEARCH_PATH_TO=schema_name