Réplication vers IQ 12.7 via une base tampon ASE (staging)

Introduction

Dans le contexte d’une utilisation d’IQ pour gérer un datawarehouse et d’un serveur de réplication RepServer pour répliquer les données d’une base de données OLTP (ASE ou autre) vers Sybase IQ, il est préférable d’utiliser une base tampon car Sybase IQ n’est pas optimisé pour les insertions, mises à jour et suppressions en ligne à ligne.

Il existe deux approches pour l’implémentation d’une base dedonnées tampon :

Utiliser une base tampon ASE (staging ASE). Cette approche est adaptée à de larges volumes de données et une période de transfert de données longue.
Utiliser les tables systèmes IQ. Pour cette approche, le volume de données en incrémental est faible ou bien un script automatique de transfert de données est mis en œuvre.

Base de données tampon Sybase ASE

L’implémentation de la base de données tampon avec ASE demande beaucoup de temps car il faut :

Créer la base de données tampon ASE avec la même structure que la base IQ.
Créer les procédures stockées utilisées par les function strings de RepServer dans la base de données tampon.
Changer la connection de Repserver vers la base de données tampon ASE.
Créer ou modifier les function strings de RepServer pour invoquer les procédures stockées.
Créer les tables tampon dans IQ pour bouger les données de la base de données tampon ASE dans les tables temporaires de Sybase IQ, tables temporaires utilisées avant de déplacer les données dans les tables finales IQ.
Créer les procédures stockées dans IQ pour charger les données depuis la base de données tampon vers Sybase IQ
Créer une procédure stockée dans la base de données tampon pour nettoyer les données transférées.

Tables systèmes Sybase IQ

Pour utiliser les tables systèmes IQ, il faut :

Créer les procédures stockées utilisées par les function strings de RepServer dans la base IQ.
Créer ou modifier les function strings de RepServer pour invoquer les procédures stockées.
Créer les tables tampon dans IQ en utlisant les tables systèmes IQ.
Créer une procédure stockée dans IQ pour charger les données de la base tampon vers Sybase IQ.

PowerDesigner 15.0 avec la modélisation de fluidité de l’information - MFI (Information Liquidity Modeling : ILM) simplifie l’automatisation de la mise en œuvre des bases de données tampon : pour cela une réplication est définie, IQ étant défini comme la cible de la réplication. Une fois la définition complétée, une simple commande permet de créér tous les artefacts requis pour implémenter la base de données tampon.

Pas à pas, cet article se propose de décortiquer à travers un cas concret la mise en œuvre automatique de la base de données tampon via PowerDesigner 15.0.

Définition des modèles physiques ASE et IQ 12.7

Le schéma global du cas pratique consiste à répliquer la table INVESTMENT_POSITION via une base de données tampon ASE d’un serveur ASE 15.0.2 (DAL_P1_ASE) vers un serveur IQ 12.7 (DAL_P1_ASQ). Dans chacun des serveurs, la base de données s’appelle ASSETS.

En préambule, les deux modèles physiques de DAL_P1_ASE.ASSETS (ASE 15.0.2) et DAL_P1_ASQ.ASSETS (IQ 12.7) sont créés avec PowerDesigner 15. Dans chacun des modèles physiques, la table INVESTMENT_POSITION est modélisée.

Définition du modèle physique ASE

Le modèle physique de DAL_P1_ASE.ASSETS appelé MPD_DAL_P1_ASQ_ASSETS est créé avec l’option FileNew ModelPhysical ModelSybase AS Enterprise 15.0.2 :

Définition du modèle physique IQ

Le modèle physique de DAL_P1_ASQ.ASSETS appelé MPD_DAL_P1_ASQ_ASSETS est créé avec l’option FileNew ModelPhysical ModelSybase AS IQ 12.7 :

Définition de la réplication vers Sybase IQ

Création du modèle de fluidité de l’information IQ Staging + Replication Server

Une réplication pour Sybase IQ est définie en utilisant le modèle de fluidité de l’information (MLI ou ILM). Pour créer un modèle de fluidité de l’information :

FileNew model. Sélectionner Information Liquidity Model dans la boîte de dialogues.
Cliquer sur l’onglet "Extended Model Definitions" et sélectionner une définition étendue Replication Server + IQ Staging.

Le modèle de fluidité de l’information est appelé InformationLiquidityModelAssets.

Assitant Réplication (Replication Wizard)

PowerDesigner 15 propose un assistant pour mettre en œuvre le schéma sommaire de la réplication ASE vers IQ. Durant cet assistant, les modèles physiques MPD_DAL_P1_ASE_ASSETS et MPD_DAL_P1_ASQ_ASSETS sont utilisés. Pour appeler l’assistant de réplication : ToolsReplication Wizard.

1. L’assistant propose en première étape de spécifier la source <server>.<database> : DAL_P1_ASE.ASSETS.

2. Dans le seconde étape, l’assistant propose de sélectionner le modèle physique de la source. Dans le cas pratique, il s’agit du modèle physique MPD_DAL_P1_ASE_ASSETS.

3. L’assistant propose ensuite de saisir le nom du processus de réplication dans Replication Server 12.6. Le format du process de réplication est <servername>.<replication process name>. <servername> est optionnel.

4. L’étape suivante permet de sélectionner le type de publication au sein de Replication Server : publication par table, publication par bases de données (nouveauté RS 12.6), etc. Dans le cas pratique une publication sera créée par table (INVESTMENT_POSITION dans ce contexte).

L’option "Create a publication for each replicated table" est choisie.

5. Comme l’option "une publication par table" a été sélectionnée précédemment, l’étape propose de sélectionner les tables pour lesquelles une publication doit être créée à partir du modèle MPD_DAL_P1_ASE_ASSETS.

6. La connexion à la base de données IQ est donnée à l’étape "Remote Database Connection Selection". Cette connexion est donnée sous la forme <IQServerName>.<DatabaseName>

7. L’étape suivante permet de sélectionner le modèle physique de la base IQ cible de la réplication.

8. L’assistant est alors terminé, en cliquant sur "Terminer", cet assistant créé les publications des tables, vues et procédures stockées répliquées dans le process de réplication.

À la fin de cet assistant, le schéma primaire (sans la base tampon pour le moment) apparaît dans le modèle de fluidité de l’information :

Voici la définition de réplication (repdef) et la publication créées automatiquement par PowerDesigner pour la table INVESTMENT_POSITION (source que l’on retrouve dans l’onglet "Preview" pour InformationLiquidityModelAssetsReplication processesRS126PublicationsINVESTMENT_POSITION) :

/* Publication: INVESTMENT_POSITION */
create publication "INVESTMENT_POSITION"
   with primary at "DAL_P1_ASE"."ASSETS"
go

/* Replication Definition: "INVESTMENT_POSITION" */
create replication definition "INVESTMENT_POSITION"
   with primary at "DAL_P1_ASE"."ASSETS"
   with all tables named "INVESTMENT_POSITION"         
   (
      "PORTFOLIO_ID"                 int,
      "INSTRUMENT_ID"                int,
      "DATE"                         datetime,
      "POS"                          float
   )
   primary key ("PORTFOLIO_ID","INSTRUMENT_ID","DATE")
go

/* Article: "INVESTMENT_POSITION" */
create article "INVESTMENT_POSITION" for "INVESTMENT_POSITION"
   with primary at "DAL_P1_ASE"."ASSETS"
   with replication definition "INVESTMENT_POSITION"
go

/* Validate Publication: INVESTMENT_POSITION */
validate publication "INVESTMENT_POSITION"
   with primary at "DAL_P1_ASE"."ASSETS"
go

Définition des options de la base tampon ASE (staging database)

Pour définir les options de la base tampon ASE, double cliquer sur la base de données IQ dans le modèle de fluidité de l’information : l’onglet "Staging Database" permet de définir les options futures de la base tampon :

Voici les options importantes sur lesquelles il faut se focaliser lors de la définition des options :

Option de la base tampon	Description
`Use insert table in Sybase IQ`	Indique qu’une table tampon dédiée aux insertions sera également créée dans Sybase IQ pour copier les lignes insérées dans la base tampon, cette méthode a pour but de pouvoir effectuer des transformations au sein de IQ avant d’insérer dans les tables finales.
`Support update in Sybase IQ`	Indique qu’une commande update sera transformée en commande update dans Sybase IQ. Si cette option n’est pas sélectionnée, les ordres update seront remplacés par des commandes delete et insert.
`Insert table code`	Modèle de nomenclature des tables dédiées aux insertions.
`Update table code`	Modèle de nomenclature des tables dédiées aux mises à jour.
`Delete table code`	Modèle de nomenclature des tables dédiées aux suppressions.
`Use stored procedure for function strings`	Créé des procédures stockées dans la base de données tampon, procédures stockées qui sont utilisées dans les function strings de Replication Server.
`Insert procedure code`	Modèle de nomenclature des procédures stockées d’insertion.
`Update procedure code`	Modèle de nomenclature des procédures stockées de mise à jour.
`Delete procedure code`	Modèle de nomenclature des procédures stockées de suppression.

Après avoir défini les options : vérifier le modèle et détecter tous les avertissements et toutes les erreurs éventuelles en utilisant le menu ToolsCheck Model.

Création de la base tampon ASE (staging database)

Une fois le modèle de fluidité de l’information validé, la base de données tampon ASE peut être créée dans le modèle de fluidité de l’information:

1. Sélectionner dans le menu : ToolsGenerate Information Liquidity Model

2. Dans l’onglet "Detail", cliquer sur le bouton "Enable Transformations" Model

3. Dans l’onglet "Extended Model Definitions Tab", sélectionner Sybase IQ Staging et cliquer sur OK

Un nouveau modèle ILM pour la base tampon est créé, nouveau schéma qui se présente alors comme ci-dessous :

Détails des objets et procédures créés

Objets créés dans la base tampon IQStagingAssets

Dans le modèle physique de la base tampon IQStagingAssets, les nouveaux objets ci-dessous sont créés :

Les tables tampon pour les insertions.
Les tables tampon pour les mises à jour si l’option "Support update in Sybase IQ" est choisie.
Les tables tampon pour les suppressions.
Les procédures stockées déclenchées par les function strings de Replication Server.
La procédure stockée IQ_CLEAN_STAGING qui nettoie les tables tampon lorsque les données sont transférées de la base tampon vers IQ.

Dans le cas pratique (option "Support update in Sybase IQ" non choisie) , 2 tables sont créées dans la base tampon : INVESTMENT_POSITION_INSERTED (insertions) et INVESTMENT_POSITION_DELETED (suppressions). A titre d’exemple voici la structure pour la table INVESTMENT_POSITION_INSERTED.

create table INVESTMENT_POSITION_INSERTED (
   PORTFOLIO_ID         integer                        not null,
   INSTRUMENT_ID        int                            not null,
   DATE                 date                           not null,
   POS                  float                          null,
   constraint PK_INVESTMENT_POSITION_INSERTE primary key (PORTFOLIO_ID, INSTRUMENT_ID, DATE)
)
go

Ces 2 tables tampon sont alimentées par des procédures stockées déclenchées par les function strings de Replication Server. La nouvelle procédure stockée SP_INS_INVESTMENT_POSITION dans la base tampon IQStagingAssets est par exemple dédiée à l’alimentation de la table INVESTMENT_POSITION_INSERTED,

create procedure SP_INS_INVESTMENT_POSITION
   @portfolio_id_new int,
   @instrument_id_new int,
   @date_new datetime,
   @pos_new float
as
begin  
   if @@trancount = 0
   begin
      raiserror 30000 "SP_INS_INVESTMENT_POSITION: procedure must be called from within a transaction."
      return -1
   end
   
   -- Insert into the insert table
   insert into INVESTMENT_POSITION_INSERTED (PORTFOLIO_ID, INSTRUMENT_ID, DATE, POS)
   values (@portfolio_id_new, @instrument_id_new, @date_new, @pos_new)
   
   return 0
end
go

La procédure stockée IQ_CLEAN_STAGING est très basique et tronque les tables tampon, la troncature est réalisée lorsqu’une phase de transfert des données de la base de données tampon vers IQ vient de se produire :

create procedure IQ_CLEAN_STAGING as
begin
   -- Clean staging tables
    -- Clean the staging tables of 'INVESTMENT_POSITION'
   truncate table INVESTMENT_POSITION_INSERTED
   truncate table INVESTMENT_POSITION_DELETED
end
go

Objets créés dans Replication Server

PowerDesigner 15 a tout automatisé, de la création des définitions de réplication, des connexions, des souscriptions aux function strings mises en œuvre dans Replication Server pour alimenter les tables tampon dans la base tampon IQStagingAssets via des procédures stockées. Tout le script peut être retrouvé dans l’onglet "Preview" en double cliquant sur l’icône RepServer.

Voici par exemple le code de la function string mise en œuvre pour déclencher via la réplication la procédure SP_INS_INVESTMENT_POSITION qui insère dans la table INVESTMENT_POSITION_INSERTED dans la base tampon IQStagingAssets lorsqu’une insertion dans INVESTMENT_POSITION est détectée par le moteur de réplication.

/* Function String: "rs_insert" */
create function string "INVESTMENT_POSITION"."rs_insert"
   for rs_sqlserver_function_class
   with overwrite
   output language
   'exec SP_INS_INVESTMENT_POSITION ?PORTFOLIO_ID!new?,
                          ?INSTRUMENT_ID!new?,
                          ?DATE!new?,
                          ?POS!new?'
go

On retrouve bien sur les créations de souscriptions, etc.

create subscription "INVESTMENT_POSITION_ASSETS"
   for publication "INVESTMENT_POSITION"
   with primary at "DAL_P1_ASE"."ASSETS"
   with replicate at "DAL_P1_ASE"."IQStagingAssets"
   without materialization
go

Objets créés dans la base IQ ASSETS

Dans le modèle physique de la base IQ, les nouveaux objets ci-dessous sont créés :

Les tables tampon pour les insertions si l’option "Use insert table in Sybase IQ" est choisie.
Les tables tampon pour les mises à jour si l’option "Support update in Sybase IQ" est choisie.
Les tables tampon pour les suppressions
La procédure stockée IQ_LOAD_STAGING pour transférer les données de la base tampon ASE vers Sybase IQ

Dans le cas pratique, l’option "Use insert table in Sybase IQ" n’a pas été choisie, aussi, une seule table tampon dédiée aux suppressions est créée : INVESTMENT_POSITION_DELETED

create table INVESTMENT_POSITION_DELETED (
   PORTFOLIO_ID         integer                        not null,
   INSTRUMENT_ID        int                            not null,
   "DATE"               date                           not null,
   constraint PK_INVESTMENT_POSITION_DELETED primary key (PORTFOLIO_ID, INSTRUMENT_ID, "DATE")
);

La procédure stockée IQ_LOAD_STAGING :

récupère dans la table INVESTMENT_POSITION_DELETED par la méthode INSERT LOCATION les données supprimées depuis la table INVESTMENT_POSITION_DELETED de la base de données tampon.
supprime physiquement les données dans la table INVESTMENT_POSITION à partir des données dans la table INVESTMENT_POSITION_DELETED
insère les nouvelles données dans la table INVESTMENT_POSITION par la méthode INSERT LOCATION depuis la table INVESTMENT_POSITION_INSERTED de la base de données tampon
vide la table INVESTMENT_POSITION_DELETED.

create procedure IQ_LOAD_STAGING ()
begin
   ------------------------------------------------------------
   -- Loading Table: INVESTMENT_POSITION
   ------------------------------------------------------------
   -- Load deleted rows into delete staging table
   insert into INVESTMENT_POSITION_DELETED (PORTFOLIO_ID, INSTRUMENT_ID, DATE)
   location 'DAL_P1_ASE.IQStagingAssets'
   ' select PORTFOLIO_ID, INSTRUMENT_ID, DATE
      from  INVESTMENT_POSITION_DELETED ';
   
   -- Remove deleted rows in IQ table
   delete INVESTMENT_POSITION
    from  INVESTMENT_POSITION tab, INVESTMENT_POSITION_DELETED stg
   where  tab.PORTFOLIO_ID = stg.PORTFOLIO_ID and tab.INSTRUMENT_ID = stg.INSTRUMENT_ID and tab.DATE = stg.DATE;
   
   -- Load inserted rows into IQ table
   insert into INVESTMENT_POSITION (PORTFOLIO_ID, INSTRUMENT_ID, DATE, POS)
   location 'DAL_P1_ASE.IQStagingAssets'
   ' select PORTFOLIO_ID, INSTRUMENT_ID, DATE, POS
      from  INVESTMENT_POSITION_INSERTED ';
   
   -- Clean staging data
   truncate table INVESTMENT_POSITION_DELETED;
  
   -- Commit
   commit;
end;
comment on procedure IQ_LOAD_STAGING is 
'This stored procedure loads data from the staging database into the staging tables';

Transfert des données de la base tampon vers Sybase IQ

À intervalles réguliers, les données doivent être transférées de la base tampon IQStagingAssets vers la base IQ ASSETS. Pour transférer les données de la base tampon vers IQ :

1. Suspendre la réplication pour s’assurer qu’aucune donnée ne sera modifiée durant le transfert de la base tampon vers IQ :

DAL_P1_REP > suspend connection to DAL_P1_ASE.IQAssetStagings

2. Lancer la procédure stockée IQ_LOAD_STAGING dans Sybase IQ pour déplacer les données dans la base IQ ASSETS :

DAL_P1_ASQ > call IQ_LOAD_STAGING

3. Lancer la procédure stockée IQ_CLEAN_STAGING dans Sybase IQ pour vider les tables de travail :

DAL_P1_ASE.IQStagingAssets > execute IQ_CLEAN_STAGING

4. Relancer la réplication de DAL_P1_ASE.ASSETS vers DAL_P1_ASE.IQStagingAssets avec la commande resume :

DAL_P1_REP > resume connection to DAL_P1_ASE.IQAssetStagings