Sybase Component Integration Services CIS, tables proxy attachées à des jeux de résultats de procédures stockées

Introduction

Certaines philosophies ou normes de développement interdisent aux applications clientes de récupérer directement les jeux de résultats retournés par une procédure stockée. C’est souvent le cas dans les normes de développement autour des outils de reporting comme Business Objects, etc. Les données doivent être récupérées depuis des vues ou des tables.

Dans d’autres cas, les données doivent être exportées dans un fichier plat avec le binaire bcp de Sybase mais le formatage complexe des données ou une dénormalisation nécessaire implique obligatoirement l’utilisation d’une procédure stockée.

Sybase Component Integration Services ou CIS avec les tables proxy offre la possibilité de faire apparaître les jeux de résultats d’une procédure sous forme de table pour répondre à ces problématiques.

Un article était déjà paru sur ce sujet en juillet 2002 : Mise en œuvre de CIS (Component Integration Services). Cet article approfondit ce sujet technique en évoquant à travers un exemple les restrictions, l’avantage du nouveau serveur local loopback, comment récupérer les tables proxy depuis les tables systèmes, etc.

Contexte

L’objectif est de créer une table pt_bo_getnotif (pt pour proxy table) qui correspond au jeu de résultats (result sets) retournés par la procédure sp_bo_getnotif effectuant tous les calculs et toutes les dénormalisations nécessaires. Cette table est mise à disposition pour un univers BO (Business Objects).

La structure de la table pt_bo_getnotif est donnée ci-dessous et elle repose sur la procédure sp_bo_getnotif prenant en paramètre deux variables optionnelles de type date @DATE_FROM et @DATE_TO pour délimiter un intervalle de temps:

Table pt_bo_getnotif

create table pt_bo_getnotif (
 THRE_TYPE_CODE          varchar(5)      not null
 INDUS_CODE              int             not null
 ISS_ID                  numeric(18,0)   not null
 CTR_ID                  numeric(18,0)   not null
 USR_ID                  int             not null
 HOLDING_ID_DATE_1       date            null
 LAST_REPORT_DT_1        numeric(8,0)    null
 LAST_EXPOSURE_1         numeric(18,2)   null
 LAST_NOTIF_ENTITY_ID_1  numeric(18,0)   null
 HOLDING_ID_DATE_2       date            null
 LAST_REPORT_DT_2        numeric(8,0)    null
 LAST_EXPOSURE_2         numeric(18,2)   null
 LAST_NOTIF_ENTITY_ID_2  numeric(18,0)   null
 HOLDING_ID_DATE_3       date            null
 LAST_REPORT_DT_3        numeric(8,0)    null
 LAST_EXPOSURE_3         numeric(18,2)   null
 LAST_NOTIF_ENTITY_ID_3  numeric(18,0)   null
 _DATE_FROM              date            null
 _DATE_TO                date            null)
go

Procédure sp_bo_getnotif

create procedure sp_bo_getnotif
  @DATE_FROM date = null,
  @DATE_TO date = null 
as  
begin  
   .......
select
 THRE_TYPE_CODE, 
 INDUS_CODE, 
 ISS_ID , 
 CTR_ID,  
 USR_ID, 
 max(case when rownum=minrownum then HOLDING_ID_DATE
           else null end ) HOLDING_ID_DATE_1, 
 max(case when rownum=minrownum then DT_NOTIFICATION
           else null end ) LAST_REPORT_DT_1, 
 max(case when rownum=minrownum then EXPOSURE
           else null end ) LAST_EXPOSURE_1, 
 max(case when rownum=minrownum then NOTIFYING_ENTITY_ID
           else null end ) LAST_NOTIF_ENTITY_ID_1, 
 max(case when rownum=minrownum +1 then HOLDING_ID_DATE
           else null end ) HOLDING_ID_DATE_2, 
 max(case when rownum=minrownum +1 then DT_NOTIFICATION
           else null end ) LAST_REPORT_DT_2, 
 max(case when rownum=minrownum +1 then EXPOSURE
           else null end ) LAST_EXPOSURE_2, 
 max(case when rownum=minrownum +1 then NOTIFYING_ENTITY_ID
           else null end ) LAST_NOTIF_ENTITY_ID_2, 
 max(case when rownum=minrownum +2 then HOLDING_ID_DATE
           else null end ) HOLDING_ID_DATE_3, 
 max(case when rownum=minrownum +2 then DT_NOTIFICATION
           else null end ) LAST_REPORT_DT_3, 
 max(case when rownum=minrownum +2 then EXPOSURE
           else null end ) LAST_EXPOSURE_3, 
 max(case when rownum=minrownum +2 then NOTIFYING_ENTITY_ID
           else null end ) LAST_NOTIF_ENTITY_ID_3

from    #tmp2 
where   rownum< minrownum+3 
group by THRE_TYPE_CODE, 
        INDUS_CODE,
        ISS_ID,
        CTR_ID 
return 0
end

go

Les deux colonnes subsidiaires _DATE_FROM et _DATE_TO dans la table pt_bo_getnotif ne sont pas retournées par le jeu de résultats de la procédure sp_bo_getnotif mais sont nécessaires si l’on souhaite donner des valeurs aux paramètres @DATE_FROM et @DATE_TO de la procédudre stockée sp_bo_getnotif.

Pré-requis, le serveur local loopback

Si les tables MDA ont déjà été installées sur le serveur ASE, un serveur local appelé loopback est déjà défini dans sysservers. Ce serveur local loopback est réutilisé dans la définition de la table proxy.

sp_helpserver
goname       network_name   class          ....
---------- -------------- -------------  ....
...        ...            ...            ....
loopback   BOR_P1_ASE     ASEnterprise  ....

Dans le cas contraire, si le serveur loopback n’existe pas, utiliser la commande sp_addserver pour le créer.

sp_addserver loopback,null,@@servername
go

Création d’une table proxy ayant pour source une procédure stockée avec la commande create existing table

La table proxy pt_bo_getnotif est créée avec la syntaxe create existing table ... external procedure at "loopback.dbname.owner.procedure_name".

La syntaxe "external procedure at" spécifie le nom de la procédure stockée attachée à la table proxy.

create existing table (...)
external procedure 
at "server_name.database_name.owner.procedure_name"

Lorsque les procédures stockées prennent en entrée des paramètres @var1, @var2… optionnels ou non, la table proxy doit contenir les colonnes spéciales additionnelles _var1, _var2… correspondant aux paramètres d’entrée de la procédure stockée.

Ces colonnes additionnelles doivent être de même type que les paramètres d’entrée de la procédure, ne doivent pas être définies à NOT NULL et ont une nomenclature spéciale : elles ont la nomenclature des variables en entrée de la procédure et sont préfixées par un caractère _ underscore au lieu d’un caractère @.

La procédure stockée sp_bo_getnotif ayant deux paramètres optionnels en entrée @DATE_FROM et @DATE_TO de type date null, la table pt_bo_getnotif est créée avec deux colonnes supplémentaires _DATE_FROM et _DATE_TO de type date null :

create existing table pt_bo_getnotif (
 THRE_TYPE_CODE           varchar(5)      not null  ,
 INDUS_CODE               int             not null  ,
 ISS_ID                   numeric(18,0)   not null  ,
 CTR_ID                   numeric(18,0)   not null  ,
 USR_ID                   int             not null  ,
 HOLDING_ID_DATE_1        date            null  ,
 LAST_REPORT_DT_1         numeric(8,0)    null  ,
 LAST_EXPOSURE_1          numeric(18,2)   null  ,
 LAST_NOTIF_ENTITY_ID_1   numeric(18,0)   null  ,
 HOLDING_ID_DATE_2        date            null  ,
 LAST_REPORT_DT_2         numeric(8,0)    null  ,
 LAST_EXPOSURE_2          numeric(18,2)   null  ,
 LAST_NOTIF_ENTITY_ID_2   numeric(18,0)   null  ,
 HOLDING_ID_DATE_3        date            null  ,
 LAST_REPORT_DT_3         numeric(8,0)    null  ,
 LAST_EXPOSURE_3          numeric(18,2)   null  ,
 LAST_NOTIF_ENTITY_ID_3   numeric(18,0)   null  ,
 _DATE_FROM               date            null  ,
 _DATE_TO                 date            null  
) on 'default'
  external procedure
  at 'loopback.BOR.dbo.sp_bo_getnotif'
go

Si la colonne _DATE_FROM ou _DATE_TO est définie à NOT NULL, une erreur est retournée à la création de la table :

create existing table (...  _DATE_TO date not null) 
on 'default'
external procedure at 'loopback.BOR.dbo.sp_bo_getnotif'
          Msg 11270, Level 16, State 2:
Server 'BOR_P1_ASE', Line 16:
Column '_DATE_TO' does not allow null. Any column defined as a parameter column for RPC tables
must allow null.
A parameter column is a column whose name begins with an underscore.

Usage : clauses where et paramètres de la procédure stockée

La table pt_proxy est interrogée comme une table normale :

select THRE_TYPE_CODE, INDUS_CODE from pt_bo_notif
          
THRE_TYPE_CODE    INDUS_CODE
--------------    ----------
ABCDE                      1

Les valeurs des clauses where sur les colonnes _DATE_FROM et _DATE_TO lors de l’interrogation de la table proxy sont automatiquement données aux paramètres @DATE_FROM et @DATE_TO lors de l’appel de la procédure stockée sp_bo_getnotif.

select * from pt_bo_getnotif
where
 _DATE_FROM='2009 SEP 01'
 and _DATE_TO='2009 SEP 30'

execute sp_bo_getnotif @DATE_FROM='2009 SEP 01',
                       @DATE_TO='2009 SEP 30'

Lorsque les paramètres de la procédure stockée sont obligatoires et ne sont pas spécifiés dans les clauses where correspondantes de la table proxy, le message classique "Procedure %1 expects parameter..." est renvoyé au client :

Procedure sp_bo_getnotif expects parameter @DATE_FROM, which was not supplied.

Applications : bcp, insert into, etc.

Outre le fait de pouvoir extraire les résultats de procédures stockées en interrogeant des tables de manière classique, etc. les autres intérêts et champs d’application sont multiples :

les DBA peuvent prendre la main plus facilement pour résoudre d’éventuels problèmes de performance en retouchant le code de la procédure stockée.
export de résultats de procédures stockées avec le binaire bcp :
```
bcp BOR..pt_bo_getnotif out 1.txt -Usa -SBOR_P1_ASE -t";" -c
```
matérialisation de résultats de procédures stockées dans des tables physiques et temporaires :
```
select * into #tmp1 from pt_bo_getnotif
```

Restrictions et précautions

Une restriction évidente : les colonnes du jeu de résultats de la procédure stockée doivent être définies dans la table proxy.

Troncature des données

Lorsque les conversions automatiques sont possibles et la précision de la colonne dans la table proxy plus courte que celle du jeu de résultats de la procédure stockée, les données sont alors tronquées.

Dans l’exemple ci-dessous la colonne THRE_TYPE_CODE est définie avec un typevarchar(2) dans la table proxy pt_bo_getnotif alors que le jeu de résultats de la procédure sp_bo_getnotif retourne des données de type varchar(5) pour cette colonne, la conversion étant possible, les données sont tronquées :

create existing table pt_bo_getnotif (
 THRE_TYPE_CODE varchar(5) not null,
 ...
) 
on 'default' 
external procedure at
'loopback.BOR.dbo.sp_bo_getnotif'
go

select THRE_TYPE_CODE from pt_bo_getnotif
go

THRE_TYPE_CODE
--------------
ABCDE

create existing table pt_bo_getnotif (
 THRE_TYPE_CODE varchar(2) not null,
 ...
) 
on 'default'
external procedure at
'loopback.BOR.dbo.sp_bo_getnotif'
go

select THRE_TYPE_CODE from pt_bo_getnotif
go

THRE_TYPE_CODE
--------------
AB

Respect des types de données

Il faut s’assurer que les types de données des jeux de résultats de la procédure stockée sont conformes aux types de données de la table proxy, ou au moins qu’ils soient automatiquement convertibles. Une erreur de conversion se produit à l’interrogation de la table proxy lorsque les types de données sont incompatibles.

Dans l’exemple ci-dessous la colonne THRE_TYPE_CODE est définie avec un type int dans la table proxy pt_bo_getnotif alors que le jeu de résultats de la procédure sp_bo_getnotif retourne des données de type varchar(5) pour cette colonne :

create existing table pt_bo_getnotif (
 THRE_TYPE_CODE          int         null  ,
 ...) 
on 'default' external procedure at 'loopback.BOR.dbo.sp_bo_getnotif'
go

select * from pt_bo_getnotif
go

Msg 11216, Level 16, State 2:
Server 'BOR_P1_ASE', Line 1:
Internal Ct-Lib/Cs-Lib error 33816856: 'cs_convert: cslib user api layer: common library error: 
The conversion/operation was stopped due to a syntax error in the source field.'.
Command has been aborted.
(0 rows affected)

Non correspondance du nombre de colonnes

Lorsque les colonnes de la table proxy ne correspondent pas exactement aux colonnes du jeu de résultats de la procédure stockée, une erreur interne se produit à l’interrogation de la table proxy.

Dans l’exemple ci-dessous une colonne supplémentaire crdate de type datetime est définie dans la table proxy pt_get_usertables par rapport au jeu de résultats de la procédure sp_get_usertables :

create procedure sp_get_usertables as
begin
   select name, type from sysobjects where type='U'
end
go

create existing table pt_get_usertables (
 ...                      ...             ...
 name   longsysname   not null  ,
 type   char(2)       not null  ,
 crdate datetime      not null  ) 
on 'default' external procedure at 'loopback.BOR.dbo.sp_get_usertables'
go

select * from pt_get_usertables
go

Internal Ct-Lib/Cs-Lib error 33620242: 'cs_convert: cslib user api layer: external error:
An illegal value of -16232 was placed in the maxlength field of the CS_DATAFMT structure.'.

Jeux de résultats multiples dans la procédure stockée

Lorsqu’une procédure stockée retourne plusieurs jeux de résultats, seul le premier jeu de résultats est retourné à la table proxy. Tous les jeux de résultats suivants sont écartés.

Retrouver les tables proxy dans les tables systèmes

La table système sysattributes affiche les définitions des tables proxy (object_type='OD') :

select object_cinfo, char_value from sysattributes where object_type='OD'

object_cinfo        char_value
------------------- --------------------------------------------------------
pt_bo_getnotif      loopback.BOR.dbo.sp_bo_getnotif