Migration de Sybase Adaptive Server Enterprise vers IQ 12.7, guide pratique

Introduction

Cet article propose un guide de migration de Sybase Adaptive Server Enterprise (ASE) vers une base de données IQ 12.7.

Une revue de l’architecture IQ 12.7 est proposée en préambule, puis sont abordées les différences entre ASE et IQ pour tout ce qui concerne les types de données, la gestion des contraintes, etc. Connaître ces différences permet d’appréhender efficacement les migrations d’ASE vers IQ. Cette documentation n’aborde pas les différences entre ASE et IQ sur l’administration des serveurs.

Un cas pratique de migration est ensuite proposé (ASE 12.5.3 > IQ 12.7), cas pratique qui inclut les migrations des tables et les chargements avec le binaire bcp et les commandes IQ LOAD TABLE et INSERT LOCATION.

Généralités sur l’architecture IQ

IQ diffère d’un serveur Sybase ASE sur plusieurs points importants. IQ ne contient qu’une seule base de données, ce qui est très différent d’un serveur ASE qui peut contenir plusieurs bases de données. IQ a une philosophie serveur unique/base de données unique.

Architecture d’une instance IQ

Une instance IQ est une collection de fichiers et de supports de type "raw device". Chaque fichier a un rôle spécifique.

Fichiers IQ d’une base de données

Fichiers IQ de sortie

Fichiers IQ spécialisés

Version Multiplex d’IQ 12.7

L’architecture Sybase IQ Multiplex permet une implémentation plus évolutive d’une base de données IQ. Dans cette architecture, plusieurs instances Sybase IQ peuvent partager les mêmes données, ce qui permet de répartir les exécutions des requêtes sur plusieurs instances. Les dégradations de performances sur cette architecture distribuée multiplex sont quasi négligeables.

L’installation Multiplex se compose de plusieurs machines connectées à un disque partagé.

Les instances IQ sur les machines sont désignées comme des serveurs de requêtes (query servers) ou comme un serveur d’écriture (write server). Il ne peut y avoir qu’un seul serveur IQ d’écriture dans une installation multiplex, mais plusieurs serveurs IQ de requête peuvent être présents. La création de ces serveurs est malheureusement accompli uniquement à travers Sybase Central, il n’existe pas de lignes de commandes pour la mise en œuvre des serveurs IQ de requête.

Les serveurs IQ dans une architecture multiplex ont leurs propres fichiers ( .db, .log, .iqtmp, .iqmsg, etc.) à l’exception des fichiers de données de la base de données (IQ Main Store) qui sont partagés par tous les serveurs.

Un serveur de requêtes IQ dans une architecture multiplex peut disposer d’un espace de données local (dbspace local ou local store). Cet espace local n’est pas représenté dans le schéma de l’architecture multiplex plus haut. Ainsi les utilisateurs connectés à un serveur de requête peuvent créer, stocker et maintenir des tables ou objets persistents au serveur de requête.

Schémas (simulation de bases de données multiples)

Comme il n’y a qu’une seule base de données par serveur, l’instance IQ ne peut se présenter comme un serveur ASE contenant de multiples bases de données. Toutefois, il existe des méthodes qui permettent de ségréguer logiquement une base de données IQ en une multitude de schémas (comme Oracle) afin de simuler les bases de données multiples dans ASE.

En manipulant l’appartenance des objets à des utilisateurs spécifiques, des schémas séparés peuvent être créés dans des partitions de données et ainsi des bases de données logiques peuvent être formées.

Par exemple, un schéma créé par l’utilisateur MKTG (table MKTG.customer), est différent d’un schéma créé par l’utilisateur ACCT (table ACCT.customer).

Différences entre ASE 12.x/15.0.x et IQ 12.7

Différences sur le design des tables, caractère NULL ou NOT NULL

Les colonnes dans IQ doivent être explicitement définies avec l’option NULL ou NOT NULL. Les comportements par défaut d’ASE et d’IQ sont en effet radicalement différent à ce sujet. IQ applique l’option NULL par défaut à une colonne à moins que l’option NOT NULL ne soit explicitement donnée. ASE applique l’inverse : par défaut la colonne a la caractère NOT NULL si rien n’est spécifié explicitement pour la colonne.

Différences sur les types de données

Le choix des types de données est critique pour l’optimisation des performances et un stockage efficace. Le tableau ci-dessous illustre comment les types de données sont gérées par ASE et IQ.

• Dans IQ les coûts de stockage augmentent de manière très importante lorsque la précision dépasse 18. IQ et ASE utilise des tailles différentes pour la précision et l’échelle par défaut. La précision et l’échelle par défaut sont respectivement 18 et 0 pour ASE. IQ a une précision de 136 et une échelle de 38 par défaut.
• Si les requêtes n’utilisent jamais la composante temps dans une colonne de type datetime, utiliser plutôt le type date. Le type date utilise 4 bytes alors que le type datetime utilise 8 bytes. Dans IQ il n’y a aucune différence de stockage entre le type datetime et le type smalldatetime, les deux consomment 8 bytes.
• Il faut choisir le type char plutôt que le type varchar lorsque la colonne a une taille inférieure à 256 bytes. Un champ varchar requiert un byte additionnel pour stocker la longueur. L’exception à cette règle concerne les champs caractère qui sont concaténés dans les requêtes. Pour ces colonnes, le type varchar doit être utilisé pour éviter les espaces additionnels.
• Les types de données pour les colonnes impliquées dans les jointures doivent être les plus petits possibles and les types de données doivent correspondre exactement. Les types de données numériques sont préférés aux types caractères, les entiers non signés sont préférés aux entiers signés (l’optimiseur n’a pas à vérifier le signe de la valeur).

Différences sur la gestion des contraintes (RI, default)

Les contraintes d’intégrité référentielle (RI) peuvent être implémentés dans IQ. Les contraintes RI sont bénéfiques pour les performances des requêtes et plus particulièrement lorsqu’une table de fait partitionnée est employée. L’optimiseur utilise les contraintes RI pour déterminer la cardinalité entre les clés d’un parent et celles des tables filles. Les contraintes RI dégradent les performances d’environ 5% (max), mais ces contraintes peuvent être désactivées temporairement lors du chargement en utilisant l’option de base de données Disable_RI_Check.

Différences sur les déclencheurs (triggers)

Les seuls types d’évènements autorisés sont :

BackupEnd | "Connect" | ConnectFailed | DatabaseStart | DBDiskSpace |
"Disconnect" | GlobalAutoincrement | GrowDB | GrowLog | GrowTemp|
LogDiskSpace | "RAISERROR" | ServerIdle | TempDiskSpace 

Différences SQL, options systèmes IQ pour rapprocher IQ des comportements par défaut de Sybase ASE

Sybase IQ devient de plus en plus mature et les différences entre ASE et IQ se réduisent petit à petit avec les nouvelles versions. Beaucoup d’options systèmes IQ permettent de forcer IQ à se comporter comme ASE. Les options de compatibilité sont automatiquement appliquées lorsqu’une connection à un serveur IQ est réalisée à travers la couche TDS grâce à la procédure système sp_tsql_environment.

• ALLOW_NULLS_BY_DEFAULT : par défaut, sans spécification lors d’une création d’une table, IQ créé des colonnes avec la valeur NULL autorisée. ASE a le comportement contraire, les colonnes sont définies à NOT NULL par défaut. L’option système IQ allow_nulls_by_default positionnée à "off" permet de forcer IQ à se comporter comme ASE lors de la création des tables :

   set option public.allow_nulls_by_default = 'off';

• QUOTED_IDENTIFIER : le comportement par défaut d’IQ est de traiter les simple et double quotes comme deux caractères différents. Une simple quote correspond à une chaîne, une double quote à un nom d’objet. ASE ne fait aucune différence entre la simple et double quote. L’option système IQ quoted_identifier positionnée à "off" permet de forcer IQ à se comporter comme ASE dans le traitement des quotes.

   set option public.quoted_identifier = 'off';

• STRING_RTRUNCATION : IQ et ASE se comportent différemment sur les espaces résiduels à la fin des valeurs string et binary lors de l’écriture dans les tables. Sybase ASE élimine les espaces résiduels à la fin des valeurs avant d’écrire dans la table alors qu’IQ laisse les espaces résiduels. L’option système IQ string_rtruncation positionnée à "Off" permet d’avoir un iso-comportement entre IQ et ASE dans ce cas de figure.

   set option public.string_rtruncation = 'off';

• ANSINULL : par défaut IQ gère les valeurs NULL uniquement avec l’opérateur "is null". Les résultats des comparaisons de la valeur NULL en utilisant les opérateurs "=" ou "!=" sont inconnus. Avec ASE, les valeurs NULL peuvent être traitées avec les opérateurs "is null","=","!=" et "<>". Pour forcer IQ à se comporter comme ASE dans les comparaisons sur les valeurs NULL, positionner l’option système IQ ansinull à "off".

   set option public.ansinull = 'off';

• CHAINED : par défaut IQ traite toutes les opérations dans une transaction unique jusqu’à la commande COMMIT/ROLLBACK lancée par le client. ASE traite en revanche par défaut chaque commande/batch dans une transaction séparée. L’option publique chained positionnée à "on" de Sybase IQ permet de forcer IQ à se comporter comme ASE en mode chaîné.

   set option public.chained = 'on';

• FLOAT_AS_DOUBLE : Par défaut IQ traite les types float comme des types double, occasionnant ainsi des arrondis. Sybase ASE traite quant à lui de manière différente les types float des types double. Pour forcer IQ à traiter les types FLOAT différemment des types DOUBLE comme Sybase ASE, positionner l’option système IQ float_as_double à "Off".

   set option public.float_as_double = 'off';

Cas pratique

Pour transférer les données d’un serveur Sybase Adaptive Server Enterprise vers un serveur IQ, deux méthodes sont offertes : la méthode "INSERT LOCATION" et la méthode "LOAD TABLE". La méthode INSERT LOCATION est réservée aux faibles volumes de données, alors que la méthode LOAD TABLE est plutôt réservée aux volumes importants.

Dans les cas pratiques de cette documentation, les serveurs IQ sont installés avec la norme décrite dans l’article "Installation et normalisation de Sybase IQ 12.7 sous Solaris".

Méthode INSERT LOCATION

La commande INSERT LOCATION permet de charger dans une table IQ depuis une session dbisql des données contenues dans un serveur Sybase ASE par un ordre INSERT/SELECT.

Cinématique INSERT/LOCATION

La cinématique est la suivante :

Syntaxe INSERT LOCATION

INSERT [INTO] [owner.]tablename[
   (column_name [, ... ] ) ]
   ... insert-load-options
   [ LOCATION 'servername.dbname'
   [ location-options ] ]
   ... { select-statement }
            

Paramètres :
insert-load-options:
        [ LIMIT number-of-rows ]
        [ NOTIFY number-of-rows ]
        [ SKIP number-of-rows ]
        [ START ROW ID number ]

location-options:
        [ ENCRYPTED PASSWORD ]
        [ PACKETSIZE packet-size ]
        [ QUOTED_IDENTIFIER { ON | OFF } ]
            

L’ordre SELECT {select-statement} donné au serveur ASE peut contenir des jointures, des conversions, des clauses CASE et du casting du type de données.

Exemple :

INSERT INTO dbo.authors ( au_id )
NOTIFY 10000
LOCATION 'ASESERVER.pubs2'
{ select au_id from authors }

Authentifications avec la méthode INSERT LOCATION

Lorsque le serveur IQ se connecte au serveur distant ASE, INSERT..LOCATION utilise le login distant défini pour l’utilisateur de la connexion courante si un login distant a été créé avec CREATE EXTERNLOGIN et un serveur distant a été défini avec la commande CREATE SERVER.

Pour définir un serveur distant ASE :

CREATE SERVER server-name 
CLASS 'ASEJDBC|ASEODBC' 
USING '{ machine-name:port-number [ /dbname ] | data-source-name }' 
[ READ ONLY ]

Pour définir un login externe vers un serveur distant ASE :

CREATE EXTERNLOGIN login-name
TO remote_server
REMOTE LOGIN remote_user
[ IDENTIFIED BY remote-password ]

remote_server est le serveur distant défini dans l’étape précédente avec la commande CREATE SERVER.

Lorsque le serveur distant ou le login distant n’est pas défini, IQ effectue une connexion vers le serveur ASE en utilisant les coordonnées disponibles dans le fichier interfaces ou le fichier sql.ini et en utilisant l’utilisateur et le mot de passe de la connexion courante.

Cas pratique INSERT LOCATION

Dans le cas pratique, l’authentification est réalisée avec le contexte de l’utilisateur : le compte et le mot de passe de l’utilisateur sont envoyés au serveur ASE. Cette méthode est adaptée pour les faibles volumes à transférer.

INSERT INTO IDW.PORTFOLIO
 ( 
     PORTFOLIO_ID,
     PORTFOLIO_NAME,
 ...
 ) 

LOCATION 'IDB_T1_ASE.investment'
'
SELECT PORTFOLIO_ID,
       PORTFOLIO_NAME,
       ....
FROM PORTFOLIO
'
go
commit
go

Méthode LOAD TABLE

La commande LOAD TABLE permet de charger massivement dans une table IQ les données contenues dans un fichier plat ASCII depuis une session dbisql.

Les données contenues dans les fichiers plats sont exportées en mode caractère depuis un serveur Sybase Adaptive Server Enterprise avec le binaire bcp : les données peuvent provenir de la table directement ou d’une vue. L’avantage des vues est multiple, elles permettent de formater les données, notamment les colonnes date/datetime, etc., ou encore de réaliser des exports par plages (plages de dates par exemple) si il s’agit d’une table très volumineuse à décharger.

Commande LOAD TABLE

La syntaxe de la commande LOAD TABLE est très touffue, toutes les options ne seront pas décrites dans cet article.

LOAD [ INTO ] TABLE [ owner ].tablename
 ... ( load-specification [, ...] )
 ... FROM { 'filename-string' | filename-variable } [, ...]
 ... [ CHECK CONSTRAINTS { ON | OFF } ]
 ... [ DEFAULTS { ON | OFF } ]
 ... QUOTES OFF
 ... ESCAPES OFF
 ... [ FORMAT { 'ascii' | 'binary' } ]
 ... [ DELIMITED BY 'string' ]
 ... [ STRIP { ON | OFF } ]
 ... [ WITH CHECKPOINT { ON | OFF } ]
 ... [ { BLOCK FACTOR number | BLOCK SIZE number } ]
 ... [ BYTE ORDER { NATIVE | HIGH | LOW } ]
 ... [ LIMIT number-of-rows ]
 ... [ NOTIFY number-of-rows ]
 ... [ ON FILE ERROR { ROLLBACK | FINISH | CONTINUE} ]
 ... [ PREVIEW { ON | OFF } ]
 ... [ ROW DELIMITED BY 'delimiter-string' ]
 ... [ SKIP number-of-rows ]
 ... [ WORD SKIP  number ]
 ... [ START ROW ID number ]
 ... [ UNLOAD FORMAT ]
 ... [ IGNORE CONSTRAINT constrainttype [, ...] ]
 ... [ MESSAGE LOG 'string' ROW LOG 'string' [ ONLY LOG logwhat [, ...] ]
 ... [ LOG DELIMITED BY 'string' ]

Permissions et droit ALTER TABLE

Les permissions requises pour exécuter la commande LOAD TABLE dépendent initialement de l’option -gl donnée à la ligne de commande du démarrage du serveur IQ.

Par défaut, lorsque l’option -gl n’est pas donnée au démarrage du serveur IQ, -gl est à ALL pour les serveurs démarrés avec start_asiq et à DBA pour les autres modes de démarrage.

Dans la normalisation adoptée, le serveur IQ est démarré avec l’option -gl DBA et un utilisateur IQ appelé idwloader dédié aux chargements est créé. Le droit ALTER TABLE sur une table autorise les chargements massifs pour un utilisateur donné.

L’utilisateur idwloader est créé avec la commande GRANT CONNECT et le droit ALTER TABLE est donné à cet utilisateur pour les tables chargées avec ce compte. Les syntaxes GRANT nécessaires sont rappelées ci-dessous

GRANT CONNECT TO userid IDENTIFIED BY password;
GRANT ALTER ON [owner.]tablename TO userid;

Exemple :

GRANT CONNECT TO idwloader IDENTIFIED BY ********;
GRANT ALTER ON IDW.ADJUSTEMENT_FACTOR TO idwloader;

Cas pratique LOAD TABLE

Dans le cas pratique, la table ADJUSTMENT_FACTOR est exportée du serveur ASE IDB_T1_ASE pour chargement dans la table IDW.ADJUSTMENT_FACTOR dans le serveur IQ IDB_T1_ASQ.

Export des données (bcp)

Une vue vIQ_ADJUSTMENT_FACTOR est créée dans le serveur ASE IDB_T1_ASE pour préparer les données et notamment le formatage des dates.

use investment
go
if exists (select 1 from sysobjects where type='V' and name='vIQ_ADJUSTMENT_FACTOR')
begin
    drop view vIQ_ADJUSTMENT_FACTOR
end
go
CREATE VIEW vIQ_ADJUSTMENT_FACTOR
as
select
  INSTRUMENT_ID,
  ( case ADJUSTMENT_DATE
      when NULL then '0000/00/00' else convert(varchar(10),ADJUSTMENT_DATE,117)
   end)  as ADJUSTMENT_DATE,
  SOURCE_ID,
  ADJ_FACTOR_P,
  ADJ_FACTOR_GR,
  ADJ_FACTOR_NR,
  CREATED_BY,
  convert(varchar(10),CREATED_ON,117) +' '+ convert(varchar(8),CREATED_ON,108) as CREATED_ON,
  UPDATED_BY,
  ( case UPDATED_ON
      when NULL then '0000/00/00 00:00:00' else convert(varchar(10),UPDATED_ON,117) 
      +' '+ convert(varchar(8),UPDATED_ON,108)
   end ) as UPDATED_ON
from ADJUSTMENT_FACTOR
go

Les données de la vue sont exportées sur la machine hébergeant le serveur IQ IDB_T1_ASQ, export réalisé en mode caractère avec le binaire bcp en spécifiant comme délimiteur de colonnes le point-virgule (;) :

sybase@IDB_T1_ASQ > bcp investment..vIQ_ADJUSTMENT_FACTOR out ADJUSTMENT_FACTOR.bcpc -Usa -t";" -SIDB_T1_ASE -P******** -c

Création de la table IDW.ADJUSTMENT_FACTOR, clauses UNIQUE(x)

La table IDW.ADJUSTMENT_FACTOR est créée avec dbisql :

iq_c_ADJUSTMENT_FACTOR.sql

DROP TABLE IDW.ADJUSTMENT_FACTOR;

CREATE TABLE IDW.ADJUSTMENT_FACTOR                      
 (                                                                     
  INSTRUMENT_ID            int       NOT NULL IQ UNIQUE(500000),        
  ADJUSTMENT_DATE   date  NOT NULL,
  SOURCE_ID   varchar(12) NOT NULL IQ UNIQUE(500),          
  ADJ_FACTOR_P   float NULL,
  ADJ_FACTOR_GR   float NULL,
  ADJ_FACTOR_NR   float NULL,
  CREATED_BY               varchar(20)                  NOT NULL,      
  CREATED_ON               datetime                     NOT NULL,      
  UPDATED_BY               varchar(20)                  NULL,          
  UPDATED_ON               datetime                     NULL,
  PRIMARY KEY(INSTRUMENT_ID, ADJUSTMENT_DATE, SOURCE_ID)
 ); 

GRANT ALTER ON IDW.ADJUSTEMENT_FACTOR TO idwloader;

sybase@IDB_T1_ASQ > dbisql iq_c_ADJUSTMENT_FACTOR.sql

Import des données (LOAD TABLE et dbisql)

Un fichier SQL iq_l_ADJUSTMENT_FACTOR.sql contenant la commande LOAD TABLE pour la table IDW.ADJUSTMENT_FACTOR est alors créé puis exécuté avec dbisql.

iq_l_ADJUSTMENT_FACTOR.sql

LOAD TABLE IDW.ADJUSTMENT_FACTOR
 ( 
    INSTRUMENT_ID,
    ADJUSTMENT_DATE date('YYYY/MM/DD') null('0000/00/00'),
    FILLER(1),
    SOURCE_ID,
    ADJ_FACTOR_P,
    ADJ_FACTOR_GR,
    ADJ_FACTOR_NR,
    CREATED_BY ,
    CREATED_ON datetime('YYYY/MM/DD hh:nn:ss') null('0000/00/00 00:00:00'),
    FILLER(1),
    UPDATED_BY ,
    UPDATED_ON datetime('YYYY/MM/DD hh:nn:ss') null('0000/00/00 00:00:00'),
    FILLER(1)
 ) 
FROM '/Software/sybase/dba/IDB_T1_ASQ/dmp/ADJUSTMENT_FACTOR.bcpc'
DELIMITED BY ';'
ROW DELIMITED BY '\x0a'
QUOTES off
ESCAPES off
FORMAT ascii
WITH CHECKPOINT ON
PREVIEW ON
;
commit;

sybase@IDB_T1_ASQ > dbisql iq_l_ADJUSTMENT_FACTOR.sql