Main parameters to setup a Transparent Gateway connection to SQL Server

Hi,

I would like to share with you the parameters that worked out for me when connecting to a SQL Server database.

Please consider that I am not explaining in detail how to set up a transparent gateway connection to SQL Server.

I was asked to setup a connection from Oracle 10.2.0.4 to SQL Server 2005 using transparent gateway, When I was checking up the SQL Server there was already a transparent gateway installed on it (10.1) but it was not configured yet. so I decided to work with that one and then I downloaded thet same version and installed it on a development server.

After starting it I performed some basic tests and then pass on the testing environment to the developers.

Later on the developers reported the following errors when uploading a CSV file from APEX to one table on SQLServer.

ORA-02054: transaction 18.154.59645 in-doubt
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
trying all tdps, ignoring failure
ORA-02063: preceding 2 lines from SQLDEV_WEB_USER
ORA-06512: at line 6

AND

ORA-28551: pass-through SQL: SQL parse error
[Transparent gateway for MSSQL][Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite (send()).[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation. (SQL State: 00000; SQL Code: 11)
ORA-06512: at "DEVE.SP_INS_LEAD", line 20
ORA-06512: at "DEV.INS_SQL", line 7
ORA-06512: at line 3


After cleaning some pending transactions (see how here) I realized that the default configuration was not enough and then I did some research on this, after that I came up with what I consider the main parameters to set up to connect to SQL Server.

Oracle Transparent gateway reads the parameters from the following file:

$ORACLE_HOME\tg4\admin\init.ora

in my case:
D:\oracle\product\10.2.0\tg_1\tg4msql\admin\initsqldev.ora

These are the parameters that best fitted my situation and I am going to explain them one by one:

HS_FDS_CONNECT_INFO=sqldev01.sqldev
HS_RPC_FETCH_REBLOCKING=OFF
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRANSACTION_LOG= HS_TRANSACTION_LOG
HS_FDS_RECOVERY_ACCOUNT=web_usr
HS_FDS_RECOVERY_PWD=xxxxxx
HS_FDS_PROC_IS_FUNC=TRUE
HS_FDS_RESULTSET_SUPPORT=TRUE

As you can see these are Heterogeneous Services parameters and are different from those we are used to see on Oracle database servers.

Now lets review them one by one:

HS_FDS_CONNECT_INFO=sqldev01.sqldev
This is the name of the target database instance: servername.dbname

HS_RPC_FETCH_REBLOCKING=OFF
This is a very important parameter, specially for performance, in simple words, when this parameter is set to YES (the default value) the agent will not send data to the Oracle system until it has fulfill the size specified in the HS_RPC_FETCH_SIZE parameter, when we set this parameter to OFF it will send the data immediately it is ready. This parameter can be OFF only when the non-Oracle system supports array fetches.

HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
This parameter enables Read/Write transactions on the remote database, in order for it to work it needs a transaction table, specified in the next 3 parameters. Other values for this parameter are: READ_ONLY and SINGLE_SITE

HS_FDS_TRANSACTION_LOG= HS_TRANSACTION_LOG
This parameter specify the name of the transaction log table, we have to grant it SELECT, DELETE, and INSERT privileges to public.

HS_FDS_RECOVERY_ACCOUNT=web_usr
We have to provide an SQLServer account to work with the HS_FDS_TRANSACTION_MODEL commit_confirm model, that will help us overcome the pending transactions, I used the same account set up in the DB LINK from Oracle.

HS_FDS_RECOVERY_PWD=xxxxx
The password for this account.

HS_OPEN_CURSORS=100
This parameter is not really necessary to avoid pending or in-doubt transactions, but the developers were using PL/SQLs with dinamic SQL and I had to increase this value on the agent, the maximun value is the value for the OPEN_CURSORS parameter on the Oracle database and a default value of 50.

HS_FDS_PROC_IS_FUNC=TRUE
By default, all stored procedures and functions do not return a return value to the user. To enable return values we need to set it to TRUE. This parameter does not work if you have
HS_FDS_RESULTSET_SUPPORT=TRUE.

Note: If you set this gateway initialization parameter, you must change the syntax of the procedure execute statement for all existing stored procedures, visit the official documentation below

HS_FDS_RESULTSET_SUPPORT=TRUE
The same as before, but returns a whole RESULTSET instead of a value. This parameter will leave ineffective the previous one.

Note: If you set this gateway initialization parameter, you must change the syntax of the procedure execute statement for all existing stored procedures, visit the official documentation below

Here is the official documentation for the Transparent Gateway regarding MS SQL Server

Advertisements
This entry was posted in Oracle Server, Performance & Tuning and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s