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

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.

• Le binaire ddlgen prend en charge le "reverse engineering" des objets. ddlgen est un utilitaire java.
• Le binaire bcp prend en charge l’export/import des données.

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.

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.

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.

ddlgen, reverse des objets

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 :

-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 |%} exclut 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"

• des logins internes ou externes (remote), des rôles et des mots de passe.
• des routines (procédures, fonctions) dont le code est masqué avec sp_hidetext.

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******

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é.

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.

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.

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.

<<<<< 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, notamment dans un contexte de revue d’impacts et de code.

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.

set deferred_name_resolution on
go
create procedure p1 as exec p2
go
exec sp_depends p1
go

The dependencies of the stored procedure cannot be determined until the first
successful execution.

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…

<<<<< 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.

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.

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

• Les fonctions UDF, nouveauté 15.0.2, ne peuvent pas être appliquées sur une version 15.0.1
• Le type bigint, nouveauté 15.0, ne peut pas être appliqué sur une version 12.5.x, etc.

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.

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 :

• SQLPAC - Mise en œuvre de CIS (Component Integration Services)
• SQLPAC : Sybase Component Integration Services CIS, tables proxy attachées à des jeux de résultats de procédures stockées

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.

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) avec 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

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_out.ksh

…
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.

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).

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

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_in.ksh

…
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. Éventuellement, 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.