Oracle Transparent Gateway for Sybase (sous Solaris)

Introduction

Cette documentation technique montre comment utiliser et mettre en œuvre Oracle Transparent Gateway for Sybase, composant qui permet d’établir un lien de communication entre une instance Oracle et un serveur Sybase. L’environnement pour l’instance Oracle et le serveur Sybase est une plateforme SunOS Solaris SPARC. Par la même occasion, une normalisation est mise en œuvre.

Dans la suite de cette documentation, le composant Oracle Transparent Gateway for Sybase sera souvent appelé Oracle Agent TG (TG pour Transparent Gateway).

Installation de Transparent Gateway for Sybase

À la création d’une distribution Oracle 9i, le composant Transparent Gateway for Sybase doit être sélectionné au cours d’une installation personnalisée. Par défaut, le composant Transparent Gateway for Sybase n’est pas inclus dans une installation classique.

Le chemin d’accès à la distribution Sybase est demandé au cours de la création de la distribution Oracle 9i pour réaliser la compilation. Dans l’environnement normalisé de cette documentation, la compilation a été réalisée en donnant comme distribution cliente Sybase le chemin /Software/sybase/sybase-12.5, sybase-12.5 étant un lien qui pointe vers une distribution Sybase 12.5.3 ESD#4.

À l’issue de la création de la distribution Oracle 9i, le répertoire $ORACLE_HOME/tg4sybs contient tous les binaires et toutes les librairies du composant Oracle Transparent Gateway for Sybase. Dans l’environnement normalisé de cet article, $ORACLE_HOME est le répertoire /Software/oracle/app/product/9.2.0.

Configuration de Transparent gateway for Sybase

Fichiers tnsnames.ora et listener.ora pour l’agent Oracle TG for Sybase

Une normalisation a été créée pour l’ajout d’agent TG Oracle for Sybase dans les fichiers tnsnames.ora et listener.ora : TGSYB<Trigramme applicatif du serveur Sybase (3 lettres)>.

Dans le contexte de ce cas pratique, le serveur Sybase a pour trigramme FIA, les services Net Oracle pour cet agent Oracle TG for Sybase seront par conséquent nomenclaturés TGSYBFIA dans les fichiers listener.ora et tnsnames.ora.

Après avoir pris de soin de réserver un port pour l’agent Oracle TG for Sybase sur la machine de l’instance Oracle qui va utiliser les services de l’agent, le fichier $TNS_ADMIN/tnsnames.ora doit comporter alors l’entrée pour TGSYBFIA avec la clause hs=ok :

tnsnanmes.ora

# HETEROGENEOUS SERVICES ###############################################

TGSYBFIA =
       (DESCRIPTION =
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = SRVUNXFR1)(PORT = 5560 ))
         )
         (CONNECT_DATA =(SID = TGSYBFIA))
         (HS=OK)
      )

La clause hs=ok dans le fichier tnsnames.ora indique qu’il s’agit d’un agent Oracle pour les services hétérogènes (HS, Transparent Gateway…)

Le listener pour l’agent Oracle Transparent Gateway for Sybase doit comporter deux clauses très importantes :

• PROGRAM=tg4sybs, tg4sybs étant l’exécutable de Transparent Gateway for Sybase.
• ENVS=LD_LIBRARY_PATH=sybase_dir/lib:oracle_home_directory/lib

Dans le listener de l’Agent TG , la clause ENVS doit être implémentée afin de mettre à jour la variable d’environnement LD_LIBRARY_PATH et ceci afin d’indiquer le chemin d’accès aux librairies Open Client de la distribution cliente de Sybase ($SYBASE/$SYBASE_OCS/lib) ainsi que le chemin d’accès aux librairies Oracle ($ORACLE_HOME/lib).

listener.ora

# HETEROGENEOUS SERVICES -----------------------------------------------

LISTENER_TGSYBFIA =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SRVUNXFR1)(PORT = 5560))
)
)
)

SID_LIST_LISTENER_TGSYBFIA =
(SID_LIST =
(SID_DESC =
(SID_NAME = TGSYBFIA)
(ORACLE_HOME = /Software/oracle/app/product/9.2.0)
(PROGRAM = tg4sybs)
(ENVS = LD_LIBRARY_PATH=/Software/sybase/sybase-12.5/OCS-12_5/lib:/Software/oracle/app/product/9.2.0/lib)

)
)

LOG_DIRECTORY_LISTENER_TGSYBFIA=/Software/oracle/Instances/TGSYBFIA/bdump
LOG_FILE_LISTENER_TGSYBFIA=listener_TGSYBFIA.log

Fichier d’initialisation init pour l’agent Transparent Gateway for Sybase

Vient ensuite l’étape de la création du fichier d’initialisation pour l’agent Oracle Transparent Gateway for Sybase. Oracle fournit un fichier d’initialisation nommé inittg4sybs.ora pour exemple et localisé dans le répertoire $ORACLE_HOME/tg4sybs/admin. Le fichier d’initialisation de l’agent TG for Sybase doit exister avant le démarrage du listener.

Pour créer le fichier d’initialisation de l’agent Oracle Transparent Gateway for Sybase, copier le fichier exemple inittg4sybs.ora et renommer ce dernier en init<SID de l’Agent TG for Sybase>.ora.

Dans notre cas pratique, le fichier d’initialisation est donc nomenclaturé initTGSYBFIA.ora et ce dernier est installé dans le répertoire normalisé /Software/oracle/Instances/<SID Oracle Agent TG>/pfile (/Software/oracle/Instances/TGSYBFIA/pfile).

Dans ce fichier d’initialisation de l’agent TG Oracle sont indiqués

• la connexion au serveur Sybase avec la variable HS_FDS_CONNECT_INFO de la façon suivante :

  HS_FDS_CONNECT_INFO=server_name.database_name[,INTERFACE=interface_file]

  Les entrées server_name et database_name sont bien entendues sensibles à la casse. La localisation du fichier interfaces peut être optionnellement donnée.
• la variable d’environnement $SYBASE est également donnée dans le fichier d’initialisation de l’agent avec la commande SET :

  SET SYBASE=sybase_dir

Ci-dessous l’extrait du fichier TGSYBFIA.ora

TGSYBFIA.ora

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = IDB_T1_ASE.FDB,/Software/sybase/interfaces

#
# SYBASE Environment variable
#
set SYBASE=/Software/sybase/sybase-12.5

Démarrage du listener pour l’agent Transparent Gateway for Sybase

Le listener TGSYBFIA peut alors être démarré avec le binaire lsnrctl et en lançant la commande start LISTENER_TGSYBFIA. Bien entendu, il faut que les variables d’environnement $PATH et $LD_LIBRARY_PATH soient bien initialisées respectivement vers $ORACLE_HOME/bin:$PATH et $ORACLE_HOME/lib:$LD_LIBRARY_PATH pour utiliser le binaire du listener lsnrctl.

oracle@RISKD > lsnrctl
LSNRCTL for Solaris: Version 9.2.0.7.0 - Production on 28-JAN-2007 17:16:18 Copyright (c)
1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start LISTENER_TGSYBFIA
Starting /Software/oracle/app/product/9.2.0/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 9.2.0.7.0 - Production
System parameter file is /Software/oracle/Network/listener.ora
Log messages written to /Software/oracle/Instances/TGSYBFIA/bdump/listener_tgsybfia.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVUNXFR1)(PORT=5560)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SRVUNXFR1)(PORT=5560)))
STATUS of the LISTENER
------------------------
Alias LISTENER_TGSYBFIA
Version TNSLSNR for Solaris: Version 9.2.0.7.0 - Production
Start Date 31-JAN-2007 17:30:24
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /Software/oracle/Network/listener.ora
Listener Log File /Software/oracle/Instances/TGSYBFIA/bdump/listener_tgsybfia.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVUNXFR1)(PORT=5560)))
Services Summary...
Service "TGSYBFIA" has 1 instance(s).
Instance "TGSYBFIA", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Modes transactionnels, configuration du 2-phase commit

Les modes transactionnels (HS_FDS_TRANSACTION_MODEL)

L’agent Gateway supporte les fonctionnalités ci-dessous pour ce qui concerne les transactions :

• COMMIT_CONFIRM
• READ_ONLY
• SINGLE_SITE

Par défaut l’agent Gateway utilise le mode de transaction COMMIT_CONFIRM. Lorsqu’une base de données Sybase est mise à jour par une transaction, le gateway devient le point de validation (commit point site). La base de données Oracle valide la transaction dans la base de données Sybase après avoir vérifié que tout s’est bien passé transactionnellement au niveau Oracle.

Dans le cas où il n’est pas nécessaire de coordonner la transaction Oracle avec la transaction Sybase, le mode SINGLE_SITE suffit amplement. Pour modifier le mode transactionnel de l’agent Gateway, modifier le paramètre d’initialisation HS_FDS_TRANSACTION_MODEL dans le fichier d’initialisation de l’agent Gateway :

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = IDB_T1_ASE.FDB,/Software/sybase/interfaces
HS_FDS_TRANSACTION_MODEL = < COMMIT_CONFIRM | READ_ONLY | SINGLE_SITE >

#
# SYBASE Environment variable
#
set SYBASE=/Software/sybase/sybase-12.5

Le mode READ_ONLY ne fournit qu’un mode en lecture seule, les transactions de mise à jour ne sont pas autorisées.

Configuration du mode 2-phase commit (COMMIT_CONFIRM, HS_FDS_RECOVERY_ACCOUNT)

Pour activer le mode COMMIT_CONFIRM (transaction distribuée entre Oracle et Sybase), il faut créer un compte de recovery (avec mot de passe) et une table de log dans le serveur Sybase. La table de LOG qui s’appelle HS_TRANSACTION_LOG est la table dans laquelle sont stockées les transactions 2-phase commit et leurs descriptions.

La table HS_TRANSACTION_LOG a la structure ci-dessous :

GLOBAL_TRAN_ID char(64) NOT NULL
TRAN_COMMENT char(255)

Pour que l’agent Gateway puisse gérer les transactions distribuées, un compte et un mot de passe doit donc être implémenté au sein du serveur Sybase. Par défaut, le compte et le mot de passe sont RECOVER/RECOVER. Le nom du compte et son mot de passe peuvent être respectivement modifiés dans le fichier d’initialisation de l’agent TG for Sybase avec les paramètres HS_FDS_RECOVERY_ACCOUNT et HS_FDS_RECOVERY_PWD. Oracle recommande de ne pas modifier le compte par défaut (RECOVER / RECOVER), pourquoi ?

Le mode 2-phase commit impliquant un compte de recovery à créer dans le serveur Sybase, cela indique clairement que pour gérer les transactions distribuées par l’agent TG for Sybase, une deuxième connexion est ouverte avec ce compte de recovery.

La table HS_TRANSACTION_LOG doit être bien entendu créée dans la base de données Sybase qui sera mise à jour par Oracle et cette dernière table doit appartenir au compte de recovery.

Le fichier tg4sybs_tx.sql dans le répertoire $ORACLE_HOME/tg4sybs/admin contient le script de création de la table HS_TRANSACTION_LOG.

Configuration du owner (HS_FDS_DEFAULT_OWNER)

Au lieu d’utiliser le nom du propriétaire par défaut des tables définies dans Sybase (dbo), ou bien de spécifier explicitement un propriétaire différent dans les commandes SQL, il est possible de définir un propriétaire par défaut qui est utilisé sans à avoir à spécifier ce dernier dans les commandes SQL.

Le owner des tables ou vues peut être donné avec le paramètre d’intialisation HS_FDS_DEFAULT_OWNER dans le fichier d’initialisation de l’agent TG.

Création du lien vers Sybase depuis Oracle (CREATE PUBLIC DATABASE LINK)

Pour créer un lien vers la base de données Sybase à travers l’agent TG :

CREATE PUBLIC DATABASE LINK <db_link_name> CONNECT TO "user"
        IDENTIFIED BY "password"
        USING '<SID_TG4SYB>';

En voici un exemple :

CREATE PUBLIC DATABASE LINK SRV_FIA CONNECT TO "user"
        IDENTIFIED BY "password" USING 'TGSYBFIA';

Pour ensuite tester, il suffit d’interroger une table de la base Sybase cible :

select count(*) from "sysobjects"@SRV_FIA;

28

Les vues et les synonymes améliorent grandement la syntaxe un peu « barbare » d’accès aux objets distants.

Lorsque le fichier init de Transparent Gateway n’est pas implémenté correctement, l’erreur ORA-28500 est levée :

select count(*) from "CDS_DATA"@SRV_FIA;
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Transparent gateway for SYBASE][H006] The init parameter <HS_FDS_CONNECT_INFO>
is not set. Please set it in init<orasid>.ora file.
ORA-02063: preceding 2 lines from SRV_FIA

Fermeture du lien vers Sybase dans une session (OPEN_LINKS, ALTER SESSION CLOSE DATABASE LINK)

Lorsqu’une commande vers une base sybase proxy à travers l’agent TG for Sybase est lancée, Oracle crée une session dans la base de données distante.

La connexion demeure active jusqu’à la fin de la session ou bien jusqu’à ce que le nombre maximal de liens vers des bases de données distantes pour la session excède le paramètre d’initialisation OPEN_LINKS (maximum number of concurrent open connections to remote databases in one session).

Pour réduire le nombre de sessions ouvertes vers des bases Sybase distantes, la commande ALTER DATABASE CLOSE DATABASE LINK peut être utilisée si besoin :

Exemple :

ALTER SESSION CLOSE DATABASE LINK SRV_FIA;

select * from "CDS_SPREADS_CURVES"@SRV_FIA;

no rows selected

alter session close database link SRV_FIA;

ERROR:
ORA-02080: database link is in use

commit;

Commit complete.

alter session close database link SRV_FIA;

Session altered.

Transparent Gateway for Sybase : fonctionnalités et restrictions

Exécutions des procédures stockées sous Sybase

Valeurs en retour des procédures stockées Sybase (HS_FDS_PROC_IS_FUNC)

Par défaut les procédures stockées ne renvoient pas de valeur de retour avec l’agent Gateway TG for Sybase. Pour activer les valeurs de retour, le paramètre HS_FDS_PROC_IS_FUNC doit être à TRUE dans le fichier d’initialisation de l’agent TG for Sybase.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = IDB_T1_ASE.FDB,/Software/sybase/interfaces
HS_FDS_PROC_IS_FUNC = TRUE

#
# SYBASE Environment variable
#
set SYBASE=/Software/sybase/sybase-12.5

Ci-dessous un exemple, appel de la procédure stockée REVISE_SALARY prenant en paramètre le nom d’un employé et retournant le nouveau salaire :

DECLARE
INPUT VARCHAR2(15);
RESULT NUMBER(8,2);
BEGIN
  INPUT := 'JOHN SMYTHE';
  
  RESULT := REVISE_SALARY@SYBS(INPUT);
  
  UPDATE EMP SET SAL = RESULT WHERE ENAME =: INPUT;
END;

Result sets des procédures stockées Sybase (HS_FDS_RESULTSET_SUPPORT)

Oracle Transparent Gateway for Sybase supporte les jeux de résultats retournés par des procédures stockées Sybase. Pour activer avec l’agent TG for Sybase les jeux de résultats retournés par procédures stockées, le paramètre d’initialisation de l’agent TG for Sybase HS_FDS_RESULTSET_SUPPORT doit être positionné à TRUE :

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = IDB_T1_ASE.FDB,/Software/sybase/interfaces
HS_FDS_RESULTSET_SUPPORT = TRUE

#
# SYBASE Environment variable
#
set SYBASE=/Software/sybase/sybase-12.5

La mise en œuvre est toutefois complexe, car on doit appeler séquentiellement DBMS_HS_RESULT_SET.GET_NEXT_RESULT, peu recommandé pour sa lourdeur.

Mode chaîné (chained mode)

L’agent Gateway supporte le mode ANSI de chaînage. Les procédures stockées Sybase doivent être écrites dans ce mode. Fonctionner en mode chaîné autorise l’agent Gateway à assurer la protection des transactions dans le 2-phase commit Oracle (COMMIT_CONFIRM).

Pour rappel, pour appliquer le mode chaîné pour une procédure stockée Sybase :

execute sp_procxmode <proc_name>, chained
go

Définitions des colonnes (NULL)

Par défaut, la colonne d’une table Sybase ne peut pas contenir des valeurs NULL à moins que la clause NULL soit spécifiée dans la définition de la colonne. En confirmité avec la norme ANSI, l’option de base de données allow nulls by default permet de modifier ce comportement afin d’autoriser les valeurs NULL par défaut dans les colonnes.

Conversion des données de type date

Sybase ne supporte pas les conversions de date implicites. De telles conversions doivent être explicites.

Par exemple, une erreur est soulevée pour la requête ci-dessous :

SELECT DATE_COL FROM TEST@SYBS WHERE DATE_COL = "1-JAN-2001";

Pour éviter ces problèmes de conversions implicites, ajouter les conversions explicites avec par exemple la fonction to_date :

SELECT DATE_COL FROM TEST@SYBS WHERE DATE_COL = TO_DATE("1-JAN-2001");

Type de données FLOAT

La précision pour le type de données FLOAT avec l’agent Gateway for Sybase est 7.

Chaînes vides dans les requêtes

Oracle traite les chaînes vides comme des valeurs NULL alors que Sybase traite une chaîne vide comme un espace. Par exemple, la commande ci-dessous :

SELECT "ename", "empno", "job" FROM "emp"@SYBS WHERE "ename" = '';

est traitée par Sybase de la façon suivante :

SELECT ename, empno, job FROM emp WHERE ename = ' '

L’agent Gateway passe la chaîne vide à Sybase sans la moindre conversion. Si on passe une chaîne vide en estimant en fait récupérer les valeurs NULL, Sybase ne procéde pas de cette manière et transformera cette chaîne vide en un espace blanc. Pour éviter ce problème, utiliser plutôt NULL ou IS NULL dans la commande SQL à la place d’une chaîne vide :

SELECT "ename", "empno", "job" FROM "emp"@SYBS
WHERE "ename" IS NULL;

Sybase et les types de données NCHAR et NVARCHAR

Les types de données Sybase NCHAR et NVARCHAR ne sont pas supportés par l’agent Transparent Gateway 9i.

Fonctions d’aggrégats dans les commandes CREATE TABLE et CREATE VIEW

Le serveur Oracle n’envoie pas à l’agent Gateway une commande SELECT contenant une fonction d’aggrégats (sum, avg…) dans une création de table ou de vue. Par exemple la commande ci-dessous n’est pas traitée et envoyée vers l’agent Gateway :

CREATE TABLE sum_calls_table AS
SELECT SUM(calls_abandoned),
       SUM(calls_completed),
       SUM(calls_failed)
FROM monthly_calls@SYBS;

À la place, Oracle rapatrie les données nécessaires en reformatant la commande SQL vers l’agent Gateway et effectue les fonctions d’aggrégats lui-même : cela peut être très pénalisant si les tables rapatriées sont très volumineuses.

Pour pallier à ce problème de comportement :

DROP TABLE sum_calls_table;
          
CREATE TABLE sum_calls_table (x1sum NUMBER, x2sum NUMBER, x3sum NUMBER);

DECLARE x1 NUMBER;
DECLARE x2 NUMBER;
DECLARE x3 NUMBER;

BEGIN

SELECT SUM(calls_abandoned), SUM(calls_completed),
       SUM(calls_failed) INTO x1, x2, x3 FROM monthly_calls@SYBS;

INSERT INTO sum_calls_table VALUES (x1, x2, x3);

END;