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 :
L'implémentation de la base de données tampon avec ASE demande beaucoup de temps car il faut :
Pour utiliser les tables systèmes IQ, il faut :
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éfinie 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.
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.
Le modèle physique de DAL_P1_ASE.ASSETS appelé MPD_DAL_P1_ASQ_ASSETS est créé avec l'option File > New Model > Physical Model > Sybase AS Enterprise 15.0.2 :
Le modèle physique de DAL_P1_ASQ.ASSETS appelé MPD_DAL_P1_ASQ_ASSETS est créé avec l'option File > New Model > Physical Model > Sybase AS IQ 12.7 :
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 :
Le modèle de fluidité de l'information est appelé InformationLiquidityModelAssets.
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 : Tools > Replication Wizard.
1. |
|
2. |
|
3 |
|
4 L'option "Create a publication for each replicated table" est choisie. |
|
5 |
|
6 |
|
7 |
|
8 |
|
À 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 InformationLiquidityModelAssets > Replication processes > RS126 > Publications > INVESTMENT_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
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 Tools > Check Model.
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. |
|
2. |
|
3 |
|
Un nouveau modèle ILM pour la base tampon est créé, nouveau schéma qui se présente alors comme ci-dessous :
Dans le modèle physique de la base tampon IQStagingAssets, les nouveaux objets ci-dessous sont créés :
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
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
Dans le modèle physique de la base IQ, les nouveaux objets ci-dessous sont créés :
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 :
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';
A 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. |
DAL_P1_REP > suspend connection to DAL_P1_ASE.IQAssetStagings |
2. |
DAL_P1_ASQ > call IQ_LOAD_STAGING |
3. |
DAL_P1_ASE.IQStagingAssets > execute IQ_CLEAN_STAGING |
4. |
DAL_P1_REP > resume connection to DAL_P1_ASE.IQAssetStagings |
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 11/2008 | Version initiale |
Sybase Books
Online IQ 12.7
Sybase Books
Online Adaptive Server Enterprise 15.0.2
Sybase Books
Online Replication Server 15.0
Sybase Books
Online PowerDesigner 15.0, modèles de fluidité de l'information, working with
replication server, creating a staging database for Sybase IQ
