En attendant Replication Server 15.5 : Synchronisation de données vers Sybase IQ

Logo

Introduction

Ce document présente une solution de copie de données asynchrone d’une base Sybase ASE vers une instance IQ.

La mise en place de cette méthode est simple et totalement fonctionnelle, mais tactique, en attendant l’arrivée de Sybase Replication Server 15.5 - Real Time Loading Option for IQ.

Architecture

Présentation

Une commande IQ (insert location), une table, une procédure stockée, un script shell… et quelques minutes de préparation.

La solution ici présentée est le fruit du compromis suivant : standard ou exploitable ? Elle s’appuie en effet sur de la génération et de l’exécution dynamique de code SQL, méthodes habituellement à éviter. Le dilemme était : où stocker les informations de configuration et les paramètres d’exécution ?

  • Sur le système, la solution est simple à implémenter - quelques lignes dans un script shell pour lire un ou deux fichiers de configuration, mais certains aspects fonctionnels comme 'tout depuis la dernière fois' ne sont pas si simples à gérer (stockage de la date et calculs éventuels), sans compter les aspects de portage en environnement hétérogène (Unix, Windows).
  • En base, la solution est extrêmement pratique à gérer mais impose une génération dynamique du code.

Cette dernière solution a été retenue, la dégradation de performances implicite de cette méthode étant négligeable au regard des services rendus (écriture massive de données et configuration simplifiée).

Pour la solution : une table de configuration, une procédure stockée qui l’interprète et un script shell qui lance le tout, ce dernier pouvant très simplement être adapté sous forme de procédure.

Cette méthode gère très bien les ajouts et modifications. En revanche, la prise en charge des suppressions est plus problématique puisqu’elle est fondée sur des données sélectionnées, leur absence suite à une suppression ne peut pas être detectée. Pour cette raison la procédure intègre la possibilité d’opérer différement, séquentiellement sur un même objet.

La table REP_CONFIG : paramètres de configuration

select column_name, domain_name, width
from sp_iqcolumn('DBA_REPCONFIG','sa');

 column_name           domain_name    width
 --------------------- -------------  --------
 RC_SCHEMA             char           10
 RC_TABLE              char           50
 RC_TABLE_NUM          tinyint        1
 RC_FREQ               char           10
 RC_TYPE               char           10
 RC_SOURCE             char           50
 RC_SOURCE_TABLE       char           50
 RC_REMOTESELECT       char           1000
 RC_LOCALDELETE        char           1000
 RC_DATELAST           timestamp      8

La clé unique est composée des 3 premières colonnes : SCHEMA, TABLE et TABLE_NUM.

L’unité de base est la table dans un schéma. La colonne TABLE_NUM est néanmoins nécessaire si l’on veut intégrer plusieurs logiques différentes pour un même objet.

FREQ permet d’indiquer la fréquence de mise à jour. C’est un indicateur qui identifie plusieurs catégories de mises à jour : en l’occurrence la mise à jour journalière (DAILY) et la mise à jour différentielle (DELTA). Il influe sur le périmètre à mettre à jour.

TYPE détaille la méthode de synchronisation : complète (FULL), partielle (PARTIAL), différentielle (DELTA). Cela va influer sur la méthode de copie des informations.

SOURCE précise l’instance et la base source hébergeant la table à utiliser.

SOURCE_TABLE indique la table (ou la vue) de la base source contenant les données.

REMOTESELECT : ordre SQL utilisé lors de la sélection des données dans la base source.

LOCALDELETE: ordre SQL utilisé lors de la suppression des données locales.

DATELAST : stocke la date de dernier lancement. Cette colonne a un usage double : visualiser la date de l’exécution la plus récente mais peut également être utilisée comme référence dans une synchronisation par delta sur date.

Procédure stockée IQ sp_refresh

Code source de la procédure stockée IQ sp_refresh

ALTER PROCEDURE "sa"."sp_refresh"( IN p_table varchar(50), IN p_table_num varchar(50) default 1 )
on exception resume
begin
    declare l_sql varchar(2000); 
    declare l_sqlcode int; 
    declare l_sqlstate char(5); 

    declare l_table varchar(50);
    declare l_schema varchar(50);
    declare l_source varchar(30);
    declare l_source_table varchar(30);
    declare l_datelast varchar(8);
    declare l_type varchar(10);
    declare l_remoteselect varchar(1000);
    declare l_localdelete varchar(1000);
    declare l_method varchar(100);

    declare l_daterun datetime;
    declare l_step char(20);

    set l_table=ltrim(rtrim(p_table));
    set l_sqlcode=0;
    set l_daterun=getdate();

    /*
    Collecte de la configuration
    */
    set l_step=' / 1 - get config / ';
    message now(), l_step to client;
    select ltrim(rtrim(RC_SOURCE)), 
            ltrim(rtrim(RC_SOURCE_TABLE)), 
            convert(varchar(8),RC_DATELAST,112), 
            ltrim(rtrim(RC_TYPE)), 
            ltrim(rtrim(RC_SCHEMA)), 
            ltrim(rtrim(RC_REMOTESELECT)), 
            ltrim(rtrim(RC_LOCALDELETE))
    into    l_source, l_source_table,l_datelast, l_type, l_schema ,l_remoteselect,l_localdelete
    from    sa.DBA_REPCONFIG 
    where   RC_TABLE=l_table
    and     RC_TABLE_NUM=p_table_num;

    set l_sqlcode = SQLCODE; set l_sqlstate=SQLSTATE;
    if l_sqlcode != 0 then 
        message now(), l_step , 'SQLCODE = ' || l_sqlcode || ' / ' || 'SQLSTATE = ' || l_sqlstate || ' / ' || ERRORMSG(l_sqlcode) to client; 
        if l_sqlcode = 100 then
            message now(), l_step , 'Table ' || l_table || ' not in scope' to client;
        end if ;
        return l_sqlcode; 
    end if;


        /*
        Préparation de la table temporaire d’accueil
        */
        set l_step=' / 2 - temp table / ';
        set l_sql = 'select * into #tmp from ' || l_table || ' where 1=2';
        message now(),  l_step , l_sql to client;
        execute immediate l_sql; 
        
        /*
        Récupération des données distantes
        */
        -- determination du SQL de collecte
        if l_remoteselect is null then
            set l_remoteselect = 'select * from ' || l_source_table;
        end if;

        -- application des filtres de date éventuels
        if l_datelast is not null then
            set l_remoteselect = replace(l_remoteselect,'RC_DATELAST',l_datelast);
        end if;

        set l_step=' / 3 - get data / ';
        set l_sql = 'insert into #tmp location ' || '''' || l_source || '''' || ' { ' || l_remoteselect || ' } ';
        message now(),  l_step , l_sql to client;
        execute immediate l_sql; 
        set l_sqlcode = SQLCODE; set l_sqlstate=SQLSTATE;
        if l_sqlcode not in ( 0, 100 )  then
            message now(), l_step , 'SQLCODE = ' || l_sqlcode || ' / ' || 'SQLSTATE = ' || l_sqlstate || ' / ' || ERRORMSG(l_sqlcode) to client; 
            return l_sqlcode; 
        end if;

        
        /*
        Suppression des données locales
        */

        -- determination du SQL de suppression
        if l_localdelete is null then
            set l_localdelete = 'delete ' || l_table;
        end if;

        -- application des filtres de date éventuels
        if l_datelast is not null then
            set l_localdelete = replace(l_localdelete,'RC_DATELAST',l_datelast);
        end if;

        set l_step=' / 4 - delete data / ';
        set l_sql = l_localdelete;
        message now(),  l_step , l_sql to client;
        execute immediate l_sql; 
        set l_sqlcode = SQLCODE; set l_sqlstate=SQLSTATE;
        if l_sqlcode not in ( 0, 100 )  then
            message now(), l_step , 'SQLCODE = ' || l_sqlcode || ' / ' || 'SQLSTATE = ' || l_sqlstate || ' / ' || ERRORMSG(l_sqlcode) to client; 
            return l_sqlcode; 
        end if;

        /*
        Insertion des données
        */
        set l_step=' / 5 - insert data / ';
        set l_sql = 'insert into ' || l_table || ' select * from #tmp';
        message now(),  l_step , l_sql to client;
        execute immediate l_sql; 
        set l_sqlcode = SQLCODE; set l_sqlstate=SQLSTATE;
        if l_sqlcode not in ( 0, 100 )  then
            message now(), l_step , 'SQLCODE = ' || l_sqlcode || ' / ' || 'SQLSTATE = ' || l_sqlstate || ' / ' || ERRORMSG(l_sqlcode) to client; 
            return l_sqlcode; 
        end if;
    

    /*
    Fin de traitement
    */
    update sa.DBA_REPCONFIG 
    set     RC_DATELAST=l_daterun
    where   RC_TABLE=l_table
    and     RC_TABLE_NUM=p_table_num;

    set l_step=' / 6 - END / ';
    message now(), l_step , 'Completed successfully' to client;
    return l_sqlcode; 
 
end

Commentaires

8 lignes hors gestion d’erreur et déclaration, l’algorithme est basique :

Il y a une table tampon entre la source et la cible. Cela permet de rendre le transfert rapide décorrélé de tout lien logique entre les 2 environnements. Sa structure est déterminée de manière implicite par la commande SELECT INTO from RC_TABLE where 1=2

La requête exécutée sur l’instance source pour récupérer les informations est contenue dans RC_REMOTESELECT. Si cette colonne est NULL, alors un SELECT * from RC_TABLE est mis en œuvre.

La même stratégie est appliquée pour la suppression de données locales, si LOCAL_DELETE est NULL alors DELETE * FROM RC_TABLE appliqué.

On prévoit dans les ordres sql générés pour la sélection distante comme la suppression locale de remplacer la valeur de chaine RC_DATELAST par le contenu de cette même valeur. Cette méthode permet la gestion de delta par date. Elle peut être adaptée pour tout autre type de synchronisation logique.

Le transfert de données est réalisé par INSERT INTO #tmp LOCATION ...

L’insertion en base est un 'INSERT ... SELECT'.

Exemples

Copie simple, table à table

RC_TABLE        : TEST1
RC_TABLE_NUM    : 1
RC_REMOTESELECT : 
RC_LOCALDELETE  : 
RC_DATELAST     : 20100121 10:00:00

Cette configuration va permettre une copie complète d’une table de référence. les ordres SQL générés vont être :

INSERT #tmp LOCATION .... { SELECT * FROM TEST1 }
DELETE TEST1
INSERT TEST1 select * from #tmp

Copie partielle, 'tout depuis la dernière fois '

RC_TABLE        : TEST2
RC_TABLE_NUM    : 1
RC_REMOTESELECT : select * from TEST2 where [DATE] > 'RC_DATELAST'
RC_LOCALDELETE  : delete TEST2 where [DATE] > 'RC_DATELAST'
RC_DATELAST     : 20100121 10:00:00

Cette configuration va permettre une copie complète d’une table selon des critères (critères de date le plus souvent). les ordres SQL générés vont être :

INSERT #tmp LOCATION .... { SELECT * from TEST1 where [DATE] > '20100121' }
DELETE TEST1 where [DATE] > '20100121'
INSERT TEST1 select * from #tmp

Copie par lots

RC_TABLE        : TEST3
RC_TABLE_NUM    : 1
RC_REMOTESELECT : select * from TEST2 where [VAL] > 0
RC_LOCALDELETE  : delete TEST2 where where [VAL] > 0
RC_DATELAST     : 20100121 10:00:00
RC_TABLE        : TEST3
RC_TABLE_NUM    : 2
RC_REMOTESELECT : select * from TEST3 where [VAL] < = 0
RC_LOCALDELETE  : delete TEST3 where where [VAL]  < = 0
RC_DATELAST     : 20100121 10:00:00

Cette configuration découpe fonctionnellement les flux en éléments plus petits.

Copie / update

RC_TABLE        : TEST
RC_TABLE_NUM    : 1
RC_REMOTESELECT : select * from TEST where [DATE] > 0
RC_LOCALDELETE  : delete TEST from TEST local inner join #tmp tmp on local.KEY = tmp.KEY
RC_DATELAST     : 20100121 10:00:00

Cette configuration ajoute les données récentes, supprime les données correspondantes éventuellement présentes tout en conservant les anciennes. Notons l’usage possible de la table temporaire #tmp dans le contexte d’exécution.

Ajout systématique

RC_TABLE        : TEST
RC_TABLE_NUM    : 1
RC_REMOTESELECT : select * from TEST where [DATE] > 'RC_DATELAST'
RC_LOCALDELETE  : select getdate() from dummy
RC_DATELAST     : 20100121 10:00:00

L’ordre 'SELECT' défini dans la colonne RC_LOCALDELETE est exécuté, conséquence : le mode ajout systématique est activé. Quel que soit le contenu original de la table.