Utiliser ddlgen et bcp pour réduire une enveloppe Sybase Adaptive Server Enterprise


1- Introduction

La réduction d'une enveloppe d'une base Sybase Adaptive Server Enterprise n'est pas encore une opération simple contrairement à Oracle ou MS SQL. Les commandes de réduction des bases de données et devices sont attendues avec impatience mais ne sont pas encore disponibles même avec la version ASE 15.5.

Les objets et données doivent être exportés, la base recréée avec une enveloppe plus petite et les objets et données sont ensuite importés. L'opération peut paraître lourde et complexe, mais en réalité elle est assez simple avec ddlgen (nouveauté ASE 12.5) et bcp.

En dehors de la réduction d'enveloppe, cette méthode peut être utilisée également dans le cadre du changement de la taille de pages d'un serveur (2K, 4K, 8K, 16K) si on ne souhaite pas utiliser l'utilitaire sybmigrate.

2- Cas pratique

Dans cet article, l'estimation de la volumétrie des données de la base DALI_COMP à la naissance du projet a été surestimée à 6 Gb.

ddlgen bcp

La base DALI_COMP va être réduite à 2,5 Gb de données pour être déplacée vers un autre serveur ne disposant pas des 6 Gb nécessaires.

schema réduction

3- ddlgen, reverse des objets

3-1- Syntaxe et utilisation des ddlgen

L'utilitaire ddlgen est très simple à utiliser pour déverser le reverse DDL (Data Definition Language) des objets dans un fichier plat.

sybase@DCP_P1_ASE> ddlgen -U<username> -P<password> -S<servername> -D<databasename> -O<fichier_de_sortie>

Les paramètres optionnels -T et -N permettent respectivement de filtrer les types d'objet et les noms d'objets pour lesquels le reverse doit être fait. Par exemple pour ne réaliser que le reverse des tables utilisateur commençant par A : -TU -NA%

sybase@DCP_P1_ASE> ddlgen -U<username> -P<password> \
                      -S<servername> -D<databasename> \
                      -O<fichier_de_sortie> -TU -NA%

Voici la liste exhaustive des options disponibles avec l'option -T de ddlgen pour la version 15.0.2 :

Option Description Option Description Option Description
C Cache I Indexes RS Serveurs distants
DB Base de données KC Contraintes PK,uniques SG Segments
D Valeurs par défaut (default) TR Déclencheurs (triggers)
DBD Devices de bases de données L Login U Tables
DPD Devices de dumps P Procédures stockées UDD Types de données utilisateur
EC Classes d'exécution R Règles (Rules) USR Users
EG Groupes d'engines R Contraintes RI (foreign keys...) V Vues
EK Encrypted keys RO Rôles WS Web service utilisateur
GRP Groupes WSC Web Service consumer
XP Procédures stockées étendues

-TU -XOU effectue le reverse pour les tables utilisateur uniquement.

-TU -XOD effectue le reverse pour les tables proxy uniquement.

L'option -F{TR | I | KC | RI |%} permet d'exclure les déclencheurs (TR), les indexes (I), les contraintes uniques (KC), les contraintes d'intégrité (RI) dans la génération des DDL pour les tables utilisateurs. % exclut les 4 types ( TR, I, KC, RI ).

Restrictions : ddlgen n'effectue pas le "reverse"

3-2- Lancement du reverse DDL de la base de données

Le reverse de la base DALI_COMP sur le serveur DCP_P1_ASE est exécuté avec ddlgen :

sybase@DCP_P1_ASE> ddlgen -Usa -SDCP_P1_ASE -DDALI_COMP -Odali_comp_reverse.sql -P******

Attention, le fichier généré contient la commande DROP DATABASE

La base DALI_COMP est d'ores et déjà créée dans le serveur cible DTH_P1_ASE avec une enveloppe réduite, aussi le fichier généré par ddlgen est édité pour retirer les commandes de suppression et de création de la base de données DALI_COMP.

La duplication des logins du serveur DCP_P1_ASE utilisés dans la base DALI_COMP est ensuite appliquée dans DTH_P1_ASE (pour plus d'informations sur la duplication de comptes : Sybase ASE, reverse et duplication des logins »). Le cas des tables proxy vers des tables distantes impliquant des logins distants (remote logins) doit être également géré.

3-3- Exécution du fichier DDL généré dans la cible

Le fichier généré dali_comp_reverse.sql est alors simplement exécuté dans la base cible DTH_P1_ASE/DALI_COMP avec isql :

sybase@DTH_P1_ASE> isql -Usa -idali_comp_reverse.sql -odali_comp_reverse.log -DDALI_COMP -P*******

Voici quelques cas éventuels d'erreur qu'il est possible de rencontrer.

3-3-1- Cas des déclencheurs (triggers)

Malheureusement, ddlgen effectue le reverse des déclencheurs sur une table (CREATE TRIGGER) peu après la commande CREATE TABLE de cette table. Or bien souvent, les déclencheurs peuvent dans leur code source solliciter des tables qui ne sont pas encore créées. Exemple :

<<<<< CREATING Trigger - "ESBLOGT02.dbo.DeleteTransaction" >>>>>
Msg 208, Level 16, State 1:
Server 'BMQ_T1_ASE', Procedure 'dbo.DeleteTransaction', Line 5:
dbo.LogTransaction not found. Specify owner.objectname or use sp_help to check
whether the object exists (sp_help may produce lots of output).

Pour retrouver rapidement les créations de déclencheurs en échec avec l'erreur 208 :

% cat dali_comp_reverse.log | awk -v RS="<<<<<" '$1=="CREATING" && $2=="Trigger" && $7="Msg" && $8=208 {print $1" "$2" : "$4 ", Msg "$8}'
...
CREATING Trigger : "DALI_COMP.dbo.DeleteTransaction", Msg 208
CREATING Trigger : "DALI_COMP.dbo.DeleteLog", Msg 208
 ...

Dans ce contexte, il n'existe pas d'autres alternatives que de recréér ces déclencheurs soit à partir du fichier DDL complet, soit en utilisant à nouveau ddlgen avec l'option -TTR -N<triggername> pour exporter uniquement les triggers en échec.

3-3-2- Cas des procédures imbriquées (nested procedures) : sysdepends

Il y a le cas épineux des procédures stockées imbriquées. ddlgen ne détecte pas les dépendances entre procédures stockées afin de les créér dans le bon ordre.

Procédures stockées imbriquées (nested)
<<<<< CREATING Stored Procedure -"DALI_COMP.dbo.p_Exception_Recon_Report2" >>>>>
Msg 2007, Level 11, State 1:
Server 'DTH_P1_ASE', Procedure 'p_Exception_Recon_Report2', Line 89:
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 
'p_Exception_Recon_Report2_np'.
 The stored procedure will still be created. 

La procédure stockée est créée mais malheureusement la table système sysdepends des dépendances entre objets n'est plus à jour. Il ne s'agit pas d'une erreur critique, les incohérences dans sysdepends n'empêchent pas la bonne marche du serveur, mais c'est dommage.

Si on souhaite que les dépendances soient à jour dans sysdepends : pas le choix, l'ordre de création des procédures stockées doit être retouché à la main !

La version ASE 15.5 offre enfin la souplesse sur ce sujet grâce au nouveau paramètre session "deferred_name_resolution" ou paramètre serveur "deferred name resolution". Lorsque ce paramètre est actif, la dépendance ne sera mise à jour qu'à la première exécution réussie de l'objet, ce qui garantit une cohérence totale dans la table sysdepends.

%> isql -Usa

set deferred_name_resolution on
go
create procedure p1 as exec p2
go
sp_depends p1
go
The dependencies of the stored procedure cannot be determined until the first
successful execution.

3-3-3- Cas des dépendances avec des tables temporaires pour les procédures stockées

Pour les procédures stockées ayant besoin de tables temporaires créées en amont de la compilation, pas d'autres choix que de créér et supprimer ces tables temporaires respectivement avant et après la compilation...

Dépendances avec des tables temporaires
<<<<< CREATING Stored Procedure - "DALI_COMP.dbo.p_TFM_purge_Records_np" >>>>>
Msg 208, Level 16, State 1:
Server 'DTH_P1_ASE', Procedure 'p_TFM_purge_Records_np', Line 63:
#IRNs not found. Specify owner.objectname or use sp_help to check whether the
object exists (sp_help may produce lots of output).

Si il n'y a aucune gestion de sources (ce qui est bien souvent le cas...), il faut rechercher la création des tables temporaires dans le fichier généré par ddlgen pour récupérer les structures exactes des tables temporaires nécessaires. Travail de fourmi si il existe un nombre important de procédures stockées.

% isql -Usa

create table #IRNs (IRN numeric(18) not null)
go
create procedure p_TFM_purge_Records_np
as 
 ....
go
drop table #IRNs
go

Comme pour les procédures stockées imbriquées, la version ASE 15.5 sauve la vie une fois de plus avec le nouveau paramètre session "deferred_name_resolution" ou paramètre serveur "deferred name resolution". Lorsque ce paramètre est actif, la dépendance avec les tables temporaires ne sera vérifiée qu'à la première exécution réussie de la procédure stockée.

3-3-4- Cas des serveurs ASE avec des versions différentes (partitionnement aléatoire roundrobin)

Si la version de la cible ASE est inférieure à la version de la source sur laquelle a été réalisée le "reverse", les nouveautés ASE de la version source éventuellement utilisées sont refusées dans la version cible. Par exemple

Toutefois, même après s'être assuré de ne pas utiliser des nouveautés inapplicables sur la cible, il existe un cas bien particulier à connaître si un script DDL 15.x est appliqué sur une version 12.5.x : le cas du partitionnement.

Avec la version 15.x, la commande CREATE TABLE peut contenir la clause "partition by roundrobin"

create table Extended_Info_Properties (
 .....
)
lock allpages
 on 'default'
partition by roundrobin 1

cependant la clause "partition by roundrobin" est une syntaxe de la version 15.0, les versions 12.5 ne supportent que les commandes ALTER TABLE ... PARTITION <n> | UNPARTITION pour le partitionnement aléatoire roundrobin. L'erreur 156 survient avec la clause 15.x partition by roundrobin <n> dans un version 12.x.

Msg 156, Level 15, State 2
Server 'BMQ_T1_ASE', Line 2
Incorrect syntax near the keyword 'partition'.

Si le script est exécuté sur une version 12.5, éliminer les clauses "partition by roundrobin 1" et formater le script pour ajouter les commandes ALTER TABLE .. PARTITION <n> si du partitionnement aléatoire est effectivement implémenté pour une table.

3-3-5- Cas des tables proxy avec référence vers des serveurs distants

Lorsqu'il existe une table proxy dans le reverse DDL, le serveur distant et les éventuels logins distants associés (remote logins) doivent être préparés dans le serveur cible avec sp_addserver et sp_addremotelogin, sauf si il s'agit du serveur loopback qui est automatiquement créé pour les versions supérieures à la version 12.5.3, serveur pseudo-distant qui pointe en réalité vers le serveur ASE lui même.

create existing table V_APPLICATIONS_LIST (
    app_id               numeric(4,0)                     not null  ,
 ... 
) on 'default'
  external procedure
  at 'loopback.DALI_COMP.dbo.SP_APPLICATIONS_LIST'

Pour plus d'informations sur les tables proxy :

4- Génération des commandes bcp pour l'export / import des données (colonnes identity, text...)

Dans les sections qui suivent, le cas des colonnes encryptées n'est pas abordé.

Les commandes bcp out et bcp in sont générées dynamiquement en interrogeant les tables systèmes sysobjects et syscolumns de la base source ou cible DALI_COMP.

4-1- Génération des commandes bcp out (export des données)

Les commandes "bcp out" d'export des tables peuvent être générées très simplement en interrogeant dynamiquement la table système sysobjects pour la base concernée avec isql. Les résultats sont consignés dans un fichier (bcp_out.ksh) grâce à l'option -o du binaire isql.

Les exports des données des tables proxy (existing table, proxy_table) sont écartés en vérifiant qu'il n'existe pas d'entrée dans sysattributes pour object_type='OD' (OD : Object Definition - OMNI).

Le choix de l'option d'export ( mode caractères -c ou mode binaire -n ) va dépendre des versions source et cible des serveurs ASE.

%> isql -Usa -SDCP_P1_ASE -w2000 -DDALI_COMP -b -obcp_out.ksh
password :

set nocount on
go

select 'bcp  ' + db_name() + '.' + user_name(uid) + '.' + name + ' out ${DIRBCP}/' + name + '.bcpn -Usa -S' 
+ @@servername + ' -P${PWD} -n'  from sysobjects 
where type='U'
       and name not in (select object_cinfo from sysattributes where object_type='OD')
go
...
bcp  DALI_COMP.dbo.UK_VENUS_AVG_RANK out ${DIRBCP}/UK_VENUS_AVG_RANK.bcpn -Usa -SDCP_P1_ASE -P${PWD} -n
 ...

Le fichier bcp_out.ksh généré est ensuite transformé en script Korn Shell exécutable et adapté pour renseigner les variables ${DIRBCP} et ${PWD}.

Dans le cas de figure ici, les tables ont une volumétrie relativement faible. Bien entendu, pour les grosses tables, il est souhaitable d'exporter celles-ci par périmètre (par exemple par date) grâce à des vues ou bien d'utiliser le partitionnement disponible avec bcp.

L'option -c est malheureusement incontournable pour exporter d'une version 15.0 vers une version 12.x avec les difficultés bien connues pour bien choisir le séparateur de colonnes ( -t ) et/ou de lignes ( -r ), surtout si on a le malheur d'avoir des tables avec des champs de type text qui stockent du format XML par exemple.

4-2- Génération des commandes bcp in (import des données)

Pour la génération dynamique des commandes "bcp in" via isql, la table système syscolumns va intervenir dans la requête afin de gérer le cas des tables qui possèdent une colonne de type identity. L'option -E (conservation des valeurs identity) sera ajoutée dans la commande bcp in pour ces tables.

Les imports des données des tables proxy (existing table, proxy_table) sont écartés en vérifiant qu'il n'existe pas d'entrée dans sysattributes pour object_type='OD' (OD : Object Definition - OMNI).

Vérifier que les imports des tables proxy sont effectivement écartés pour éviter de générer des incohérences de données réelles dans les serveurs distants.

La génération est réalisée depuis le serveur cible et les résultats sont consignés dans un fichier bcp_in.ksh.

%> isql -Usa -SDTH_P1_ASE -w2000 -DDALI_COMP -b -obcp_in.ksh
password :

set nocount on
go

select 'bcp  ' + db_name() + '.' + user_name(uid) + '.' + name + ' in ${DIRBCP}/' + name + '.bcpn -Usa -S' 
 + @@servername + ' -P${PWD} -b10000 -n'
from sysobjects 
   where type='U' 
      and name not in (select object_cinfo from sysattributes where object_type='OD')
      and name not in (select distinct(object_name(id)) from syscolumns where status=128)

union

select 'bcp  ' + db_name() + '.' + user_name(uid) + '.' + name + ' in ${DIRBCP}/' + name + '.bcpn -Usa -S'
 + @@servername + ' -P${PWD} -E -b10000 -n'

from sysobjects 
  where type='U'
     and name not in (select object_cinfo from sysattributes where object_type='OD') 
     and name in (select distinct(object_name(id)) from syscolumns where status=128)
go
...
bcp  DALI_COMP.dbo.UK_VENUS_AVG_RANK in ${DIRBCP}/UK_VENUS_AVG_RANK.bcpn -Usa -SDTH_P1_ASE -P${PWD} -n
bcp  DALI_COMP.dbo.I_CK_INT_CMP_CRIPS_MS in ${DIRBCP}/I_CK_INT_CMP_CRIPS_MS.bcpn -Usa -SDTH_P1_ASE -P${PWD} -E -n
 ... 

Pour retrouver une table possédant une colonne de type identity, le champ status dans syscolumns pour la colonne de type identity est à 128 : select object_name(id) from syscolumns where status=128.

Comme pour le fichier bcp_out.ksh, le fichier bcp_in.ksh généré est ensuite transformé en script Korn Shell exécutable et adapté pour renseigner les variables ${DIRBCP} et ${PWD}.

Les insertions sont réalisées avec un pas de 10 000 lignes : évidemment, cette valeur doit être ajustée en fonction de la volumétrie des tables, de la taille du journal etc... Eventuellement, le paramètre -A peut être également ajouté pour spécifier une taille de paquets plus élevée.

Comme pour l'export, l'option -c (mode caractères) est malheureusement incontournable si l'export est réalisé d'une version 15.0 vers une version 12.x.


Annexe

Historique

Version Date Commentaires
1.0 09/2010 Version initiale
1.1 10/2010 Prise en compte des tables proxy dans la génération des commandes bcp

Liens

Sybase ASE 15.5 BOL : Utility guide, ddlgen
Sybase ASE 15.5 BOL : Utility guide, bcp
SQLPAC - Sybase ASE, reverse et duplication des logins