Sybase - Export et import de données avec l’utilitaire bcp sous Windows, guide pratique

Introduction

L’utilitaire bcp dans le client Sybase Adaptive Server Enterprise permet d’exporter et importer des données en mode natif ou en mode caractères. Ce guide pratique s’adresse à tous les administrateurs et concepteurs qui abordent pour la première fois cet outil natif d’export/import de données, outil natif qui est de loin le plus efficace.

Dans le jargon classique :

• "bcp out" désigne l’export des données dans un fichier avec bcp.
• "bcp in" correspond à l’import des données dans une base depuis un fichier avec bcp.

Dans ce guide pratique :

• La variable %SYBASE% est le répertoire d’installation du client Sybase Adaptive Server Enterprise, par exemple : C:\Logiciel\sybase.
• La variable %SYBASE_OCS% est le répertoire de l’Open Client Sybase Adaptive Server Enterprise. Il s’agit d’un sous répertoire dans %SYBASE% qui dépend de la version du client.
  • version 12.0 : OCS-12_0
  • version 12.5 : OCS-12_5
  • version 15.0 : OCS-15_0
  • version 15.5 : OCS-15_0

L’utilitaire bcp est disponible dans le répertoire %SYBASE%\%SYBASE_OCS%\bin.

DOS> %SYBASE%\%SYBASE_OCS%\bin\bcp -?

DOS> cd %SYBASE%\%SYBASE_OCS%\bin

DOS> bcp -?

Export de données

bcp out

La syntaxe pour exporter une table ou une vue dans un fichier fichier.txt est la suivante :

DOS> bcp basededonnees.proprietaire.matable out fichier.txt -Sserveur
     [-Uutilisateur] [-Pmotdepasse]
     [-t separateur de champs] [-r separateur de ligne] [-c] [-n]

Le mot clé "out" indique le mode export de données.

Les options -c (mode caractères) et -n (mode natif) sont exclusives l’une de l’autre.

Dans les exemples qui suivent :

• base de données : opcenterv3
• table : PMS_APPLICATION
• propriétaire : dbo
• serveur : DBA_T1_ASE
• utilisateur : sa

bcp out en mode natif (option -n)

L’option -n exporte les données d’une table ou d’une vue en mode natif. Pour exporter la table PMS_APPLICATION (propriétaire dbo) en mode natif :

DOS> bcp opcenterv3.dbo.PMS_APPLICATION out fichier.bcpn -SDBA_T1_ASE -Usa -P****** -n

Starting copy...

29 rows copied.
Clock Time (ms.): total = 230  Avg = 7 (126.09 rows per sec.)

Le fichier fichier.bcpn produit est dans un format propriétaire qui n’est pas humainement lisible. Le format en mode natif est adapté pour les transferts de données d’un serveur à un autre serveur ou si la table contient des colonnes de type text (données non structurées) contenant des retours chariot, etc.

bcp out en mode caractères (option -c)

L’option -c exporte les données en mode caractères, le fichier produit est lisible et exploitable (awk, etc.). Avec l’option -c, les options -t (séparateur de champs) et -r (séparateur de lignes) sont utilisables. Pour exporter la table PMS_APPLICATION (propriétaire dbo) en mode caractères :

DOS> bcp opcenterv3.dbo.PMS_APPLICATION out fichier.bcpc -SDBA_T1_ASE -Usa -P****** -c

fichier.bcpc

1         MQ           1
9         IPS          1

Par défaut, le séparateur de champs est la tabulation et le séparateur de lignes est \r\n.

Pour spécifier des séparateurs non standards, par exemple le point virgule comme séparateur de champs et #\r\n comme séparateur de lignes : utiliser les arguments -t et -r.

DOS> bcp opcenterv3.dbo.PMS_APPLICATION out fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -r"#\r\n" -c

fichier.bcpc

1;MQ;1#
9;IPS;1#

Exporter les résultats d’une procédure stockée avec bcp out

Lorsqu’il s’agit d’un jeu de résultats retourné par une procédure stockée, pour exporter ce jeu de résultats dans un fichier avec bcp, la technologie CIS (Component Integration Services) doit être mise en œuvre. La méthode est relativement simple : une table proxy est créée avec la clause "create existing table", table pour laquelle la source est la procédure stockée :

create existing table (...)
external procedure 
at "server_name.database_name.owner.procedure_name"

Exemple

create existing table V_OPC_APPLICATIONS (
  app_id                          numeric(4,0)                     not null  ,
  app_trig                        varchar(4)                           null  ,
  ...
)
external procedure 
at "loopback.opcenterv3.dbo.sp_opc_getapplist"

Pour plus d’informations sur les tables proxy attachées à des jeux de résultats de procédures stockées : Sybase Component Integration Services CIS, tables proxy attachées à des jeux de résultats de procédures stockées

Les résultats de la procédure sp_opc_getapplist sont alors exportés avec bcp via la table proxy

DOS> bcp opcenterv3.dbo.V_OPC_APPLICATIONS out fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -c
          1760;;COMMS Firewall; ; ; ;0;;1;
...

Import de données

bcp in

La syntaxe pour importer dans une table les données provenant d’un fichier fichier.txt est la suivante :

DOS> bcp basededonnees.proprietaire.matable in fichier.txt -Sserveur -Uutilisateur -Pmotdepasse
     [-t separateur de champs] [-r separateur de ligne] [-b] [-E] [-c] [-n] 

Le mot clé "in" indique le mode import de données.

Les options -c (mode caractères) et -n (mode natif) sont exclusives l’une de l’autre.

Comme pour l’export de données avec bcp out, les séparateurs de colonnes et de lignes sont respectivement indiqués avec les options -t et -r si ceux ci ne sont pas les séparateurs par défaut (tabulation pour le séparateur de colonnes et \r\n pour le séparateur de lignes).

Les options -b et -E sont importantes lors des imports et sont abordées dans les paragraphes qui suivent.

Pour importer dans la table PMS_APPLICATION les données provenant d’un fichier texte pour lequel le séparateur de champs est le point virgule et le séparateur de lignes #\r\n :

fichier.bcpc

1;MQ;1#
9;IPS;1#

DOS> bcp opcenterv3.dbo.PMS_APPLICATION in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -r"#\r\n" -c

bcp in et l’option -b (batch size) : éviter la saturation du journal (log full)

L’import avec bcp dans une table comportant des indexes, des colonnes de type text ou des déclencheurs (triggers) est journalisé, ce point est vu un peu plus loin dans ce guide pratique (fast/slow bcp). En fonction de la volumétrie à importer, la taille du journal de transactions de la base de données peut être insuffisante.

DOS> bcp opcenterv3.dbo.RLV_BASE in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";"  -c
          188000 rows sent to SQL Server.
189000 rows sent to SQL Server.

La saturation du journal est notifiée dans le fichier de log du serveur DBA_T1_ASE (DBA_T1_ASE.log) avec l’erreur 1105.

DBA_T1_ASE.log

00:00000:00006:2011/03/04 18:32:19.25 server  Error: 1105, Severity: 17, State:
4
00:00000:00006:2011/03/04 18:32:19.25 server  Can't allocate space for object 's
yslogs' in database 'opcenterv3' because 'logsegment' segment is full/has no fre
e extents. If you ran out of space in syslogs, dump the transaction log. Otherwi
se, use ALTER DATABASE to increase the size of the segment.
00:00000:00006:2011/03/04 18:33:19.20 server  1 task(s) are sleeping waiting for
 space to become available in the log segment for database opcenterv3.

L’option -b (batch size) de la commande bcp in permet d’éviter ces écueils et ordonne de lancer la commande commit toutes les n lignes importées. Pour lancer un commit toutes les 10000 lignes : spécifier -b10000.

DOS> bcp opcenterv3.dbo.RLV_BASE in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";"  -b10000 -c

Le dimensionnement du paramètre "batch size" -b<nb lignes> va dépendre de la taille du journal de la base de données et de la structure de la table (indexes, colonnes text, etc.).

bcp in et l’option -E (colonnes identity)

Lorsque la table contient une colonne de type identity, l’option -E doit être indiquée dans la commande bcp in si l’on souhaite que les identifiants dans le fichier source soient conservés lors de l’import. Sans l’option -E, de nouvelles valeurs pour la colonne identity sont attribuées.

DOS> bcp opcenterv3.dbo.BASE in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -b10000 -E -c

L’option -E équivaut à la commande Transact SQL set identity_insert <table> on | off.

Si l’option -E est indiquée dans la commande bcp in alors que la table ne contient pas de colonne de type identity, l’erreur 7756 est générée :

DOS> bcp opcenterv3.dbo.BASE2 in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -b10000 -E -c
          Server Message: DBA_T1_ASE - Msg 7756, Level 16, State 1:
Cannot use 'SET IDENTITY_INSERT' for table 'opcenterv3.dbo.BASE2' because 
the table does not have the identity property.

Gestion des erreurs avec bcp : %ERRORLEVEL%, options -m (maxerrors) et -e (errfile)

Comme tout binaire qui se respecte, bcp retourne un code erreur en cas d’échec, code erreur récupéré par la variable DOS %ERRORLEVEL% à exploiter.

DOS> bcp opcenterv3.dbo.BASE in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -b10000 -E -c

Starting copy...
CSLIB Message:  - L0/O0/S0/N24/1/0:
cs_convert: cslib user api layer: common library error: The conversion/operation
 was stopped due to a syntax error in the source field.

DOS> echo %ERRORLEVEL%

-1

Par défaut, le nombre maximal d’erreurs autorisé lors de l’import avec bcp in est fixé à 10.

The total number of errors in this BCP operation is greater than the 
maximum number of errors (10) allowed. BCP has stopped.
bcp copy in failed

L’option -m (maxerrors) permet d’étendre cette limite à des valeurs personnalisées et plus élevées : par exemple 1000 erreurs maximum rencontrées

DOS> bcp opcenterv3.dbo.BASE in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -b10000 -E -m 1000 -c

L’option -e indique le fichier d’erreurs dans lequel doivent être consignées les lignes rejetées lors de l’import : pour notifier les erreurs dans le fichier errfile.txt

DOS> bcp opcenterv3.dbo.BASE in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -b10000 -E -e errfile.txt -c

errfile.txt

#@ Row 3, Column 16: CSLIB Message:  - L0/O0/S0/N24/1/0:
cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field. @#
#@ Row 3: Not transferred @#
22;model;1;0;5;SYBASE_system;0;Oct 22 2001 12:00:00:000AM;4;0;0;0;0;0;0;0;1

fast bcp/slow bcp

Les termes "fast bcp" et "slow bcp" sont souvent utilisés dans le jargon informatique lors de l’import de données avec bcp in.

• Le terme "fast bcp" désigne l’import de données avec bcp in dans une table ne comportant ni indexes, ni colonnes de type text et ni déclencheurs (triggers) : dans ce cas de figure, une journalisation très minimale est réalisée, seule l’allocation des extents est journalisée. Les performances sont dans ce cas de figure excellentes.
• Le terme "slow bcp" correspond à l’opération inverse : l’import des données avec bcp in est journalisé car la table comporte des indexes, des colonnes de type text ou des déclencheurs (triggers). Les performances d’alimentation sont dès lors dégradées, chaque insertion de ligne est en effet journalisée.

Un cas concret : insertion de 3 038 439 dans la table RLV_BASE par paquets de 10000 lignes en mode fast et slow bcp.

DOS> bcp opcenterv3.dbo.RLV_BASE in fichier.bcpc -SDBA_T1_ASE -Usa -P****** -t";" -b10000 -c

Le graphique ci-dessous résume bien la différence entre fast bcp et slow bcp. Le temps nécessaire à l’import en fast bcp plus la recréation des 2 indexes est nettement inférieur à l’import avec bcp dans la table comportant les 2 indexes.

Le choix de détruire les indexes et la désactivation des déclencheurs (triggers) avant un import dans une table ou une partition va dépendre du comportement fonctionnel et de la volatilité de la table.