CONNECT TO SYBASE/SAP ASE FROM ORACLE

Download Oracle Transparent Gateway Software

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

scroll down and save this file: LINUX.X64_193000_gateways.zip

Copy LINUX.X64_193000_gateways.zip to target server

unzip LINUX.X64_193000_gateways.zip into a temporary location

2. Create a Installation response file

vi /var/tmp/sybase_gateway.rsp

Ensure you update parameters below as needed

Main parameters

ORACLE_HOME location
oracle.install.tg.sybsConStr which is the Sybase connection details <host>,<port>,<database>

oracle.install.responseFileVersion=/oracle/install/rspfmt_tginstall_response_schema_v19.0.0
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.tg.customComponents=oracle.rdbms.tg4sybs:19.0.0.0.0
oracle.install.tg.ifmxConStr=
oracle.install.tg.msqlConStr=
oracle.install.tg.sybsConStr=restart01.localdomain,5000,sybtestdb1
oracle.install.tg.teraConStr==
oracle.install.tg.db2ConStr=
oracle.install.tg.appc.protocol=SNA
oracle.install.tg.wbsp.remote.str=
oracle.install.tg.wbsp.location=
oracle.install.tg.wbsp.local.queuemgr=

3. Install Oracle Transparent Gateway Software

cd <Oracle Gateway Software Temp location>/gateways
./runInstaller -responseFile /var/tmp/sybase_gateway.rsp -silent

4. Run root script as root user

/u01/app/oracle/product/19.0.0/tghome_1/root.sh

5. Add oratab entry

(back as oracle user)

echo "GATEWAY:/u01/app/oracle/product/19.0.0/tghome_1:N" >> /etc/oratab

6. Customize/Review the Initialization Parameter File

This will have inherited the settings from the response file and can be edited if needed to update server, port or database in line : HS_FDS_CONNECT_INFO

cat /u01/app/oracle/product/19.0.0/tghome_1/dg4sybs/admin/initdg4sybs.ora

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Sybase

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[restart01.localdomain]:5000/sybtestdb1
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

7. Configure the Oracle Transparent Gateway Listener

Set env

. oraenv <<< GATEWAY

Copy the example listener.ora to edit

cp ${ORACLE_HOME}/dg4sybs/admin/listener.ora.sample $ORACLE_HOME/network/admin

cd $ORACLE_HOME/network/admin

ls listener.ora # this should not exist
cp listener.ora.sample listener.ora

decide on port to use for the gateway listener:
check ports in use as root

lsof -i -P -n | grep LISTEN|

Update listener.ora example below:

# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER_SYBASE_GATEWAY =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
 )

SID_LIST_LISTENER_SYBASE_GATEWAY=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4sybs)
         (ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1)
         (PROGRAM=dg4sybs)
         (ENV = /u01/app/oracle/product/19.0.0/tghome_1/dg4sybs/driver/lib:/u01/app/oracle/product/19.0.0/tghome_1/lib)
      )
  )

#CONNECT_TIMEOUT_LISTENER = 0

Start the listener

lsnrctl start LISTENER_SYBASE_GATEWAY

8. Determine the TNS Connect string

There will be an example connect string in:

${ORACLE_HOME}/dg4sybs/admin/tnsnames.ora.sample

This is ok but you may need to update as I have the port to reflect port number used for listener we created above, we won’t use this tnsnames file as will create the database link below with tns details inline, but its got to review the settings to use:

# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

dg4sybs  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    (CONNECT_DATA=(SID=dg4sybs))
    (HS=OK)
  )

9. Sybase ASE pre-requisites for Test

A user who is dbo with default database set which contains tables we will query

For purpose of this test I created a sybase database, user, and table as below on ASE 16


disk init name='data01', physname='/opt/sap/data/data01.dat', size='100M'
go

disk init name='log01', physname='/opt/sap/data/log01.dat', size='50M'
go

create database sybtestdb1 on data01='50M' log on log01='25M'
go

create login psammy with password sybase@456
go

use sybtestdb1
go


sp_changedbowner psammy
go

sp_modifylogin psammy,defdb,'sybtestdb1'
go

-- logged back in as psammy and ran

select db_name()
go

create table sybtab1(name char(64))
go

insert into sybtab1 values ('Paul')
go

select * from sybtab1
go

10. Create Database link to connect Sybase database

Connect to the Oracle database as the user you wish to query Sybase from, this use will need create database privilege.

Then Create the Database Link, note we are using the TNS details from above and Sybase ASE user name password

Also note generally for Oracle to Syabase querys or SQL we need to double quote here the username and password, but also for queries as below.

CREATE DATABASE LINK SAP01_sybtestdb1_LINK
CONNECT TO "psammy" IDENTIFIED BY "sybase@456"
USING '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=dg4sybs)) (HS=OK))';

Lets Query that table we created again note the double quotes for table columns and table name.

select "name" from "dbo"."sybtab1"@SAP01_sybtestdb1_LINK;

name
----------------------------------------------------------------
Paul

11. Add the Transparent Gateway listener to host stop and start scripts

as root:

touch /usr/local/bin/gateway_listener_stop_start.sh
chmod 755 /usr/local/bin/gateway_listener_stop_start.sh

vi /usr/local/bin/gateway_listener_stop_start.sh

Copy below to script: gateway_listener_stop_start.sh

#!/bin/bash

#name : /usr/local/bin/gateway_listener_stop_start.sh
#V1.0
#PSAMMY 

export PATH=/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/19.0.0/tghome_1/bin
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/tghome_1

case $1 in

start)
echo “STARTING LISTENER_SYBASE_GATEWAY”
ps -ef|grep "tns"|grep LISTENER_SYBASE_GATEWAY|grep -v grep

if [ $? -eq 0 ]
then
echo “LISTENER_SYBASE_GATEWAY is already running”
exit 1;
fi

lsnrctl start LISTENER_SYBASE_GATEWAY
;;

stop)
ps -ef|grep "tns"|grep LISTENER_SYBASE_GATEWAY|grep -v grep

if [ $? -gt 0 ]
then
echo “LISTENER_SYBASE_GATEWAY is not running”
exit 1;
fi

 
lsnrctl stop LISTENER_SYBASE_GATEWAY
;;

*)
echo “only start or stop can be used as arguments”
exit 1
;;

esac

As root Create a service to call this script and enable the service so LISTENER auto starts and stops:

vi  /usr/lib/systemd/system/gateway_listener.service

copy below to gateway_listener.service:

[Unit]
Description=Gateway Listener for Oracle
After=network.target

[Service]
RemainAfterExit=yes
User=oracle
Type=simple
ExecStart=/bin/bash /usr/local/bin/gateway_listener_stop_start.sh start
ExecStop=/bin/bash /usr/local/bin/gateway_listener_stop_start.sh stop

[Install]
WantedBy=multi-user.target

As root reload systemd service files

systemctl daemon-reload
systemctl disable gateway_listener
systemctl enable gateway_listener
systemctl stop gateway_listener
systemctl start gateway_listener

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.