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;
endCommentaires
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:00Cette 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 #tmpCopie 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:00Cette 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 #tmpCopie 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:00RC_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:00Cette 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:00Cette 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:00L’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.