Accès aux fichiers avec Sybase C.I.S 12.5

Logo

Introduction

Avec la version 12.5, l’accès aux fichiers est implémenté avec les CIS (Component Integration Services) de Sybase. Cette technologie simplifie grandement les opérations de transvasement de données entre des fichiers et des tables (opérations bulk copy bcp).

Paramètres de configuration

Deux paramètres de configuration au sein du serveur doivent être appliqués pour pouvoir mettre en œuvre l’accès aux fichiers à partir du moteur Sybase.

  • enable cis (paramètre dynamique : 1 par défaut).
  • enable file access (paramètre dynamique : 0 par défaut).

Les services CIS (Component Integration Services) doivent être activés, ces derniers l’étant par défaut avec Sybase 12.5. Si ce n’est pas le cas

execute sp_configure 'enable cis',1
go

Pour accéder aux fichiers, le paramètre 'enable file access' doit être activé. Par défaut ce paramètre n’est pas activé :

execute sp_configure 'enable file access',1
go

Sécurité

Seuls les utilisateurs disposant du rôle sa_role ou sso_role sont autorisés à créer des tables proxy reposant sur des fichiers. Le moteur Sybase doit être autorisé à créer des fichiers dans le répertoire de destination.

Tables proxy reposant sur des fichiers système - Généralités

Lorsqu’une table proxy fait référence à un fichier :

  • Le fichier ne doit pas être un répertoire, un bloc spécial ou un fichier contenant des caractères spéciaux.
  • Le moteur Adaptive Server doit au moins avoir un droit de lecture sur le fichier. Si le fichier doit être créé, le droit d’écriture doit également être donné.
  • Le contenu d’un fichier existant doit être lisible.
  • Les enregistrements dans le fichier doivent être délimités par un caractère de nouvelle ligne \n.
  • La taille maximale d’un enregistrement ne peut excéder 32767 octets.
  • Les colonnes individuelles, sauf pour la dernière, sont délimitées par une chaîne qui ne peut excéder 16 octets : la chaîne de séparation est par défaut la tabulation.
  • Il doit exister une correspondance pour chaque enregistrement entre les valeurs délimitées dans le fichier et les colonnes dans la table proxy.

Tables proxy créées avec la commande create proxy_table

Avec la commande create proxy_table, une table proxy avec une colonne nommée record de type varchar(255) est créée. Dans ce cas, le fichier doit contenir des caractères lisibles et les lignes sont séparées par le caractère de retour chariot \n. Si le fichier n’existe pas, le moteur ASE tente de le créer. Syntaxe : 

create proxy_table <table_name>
external file at 'pathname'

La commande drop table sur une table proxy faisant référence à un fichier ne supprime pas le fichier associé.

Exemple :

create proxy_table recordlogs
external file at '/sdata/sybase/v12.5/TST_T1_ASE/files/recordlogs.txt'

La procédure sp_help indique bien que cette table ne contient qu’une seule colonne ayant le nom record de type varchar(255) et précise bien la localisation du fichier externe correspondant.

exec sp_help recordlogs
go

Object is Remote/External
-------------------------
/sdata/sybase/v12.5/TST_T1_ASE/files/recordlogs.txt


Name                 Owner         Object_type
----                 -----         ----------- 
recordlogs         dbo         user table 

Data_located_on_segment When_created 
----------------------- ------------ 
default                 Nov 26 2004 5:25PM 

Column_name     Type       Length      ... 
-----------     ----       ----------- ...
record          varchar    255         ...

Insertions et troncatures

La commande insert peut alors être utilisée pour insérer des lignes à la fin du fichier : 

declare @datestring varchar(20)
select @datestring = convert(varchar(20),getdate())
insert into recordlogs values (@datestring + ' : query processing')

Pour vider le fichier, utiliser la commande truncate table :

truncate table recordlogs

Les commandes update et delete ne sont pas autorisées sur les tables proxy.

Interrogation

De simples interrogations peuvent être alors réalisées sur le fichier avec la commande SELECT :

select record from recordlogs where record like '%query%'

record
------
Nov 26 2004 5:37PM : query processing

Tables proxy créées avec la commande create existing table

Il est possible de spécifier des colonnes avec des types de données pour une table proxy faisant référence à un fichier contenant des enregistrements avec des valeurs délimitées. Syntaxe : 

create existing table <table_name> (
        column1     int            null,
        column2     datetime       null,
        column3     varchar(1024)  null,
								…
								…
) external file at 'pathname' [column delimiter '<string>']

Par défaut le délimiteur de colonnes est une tabulation.

Les colonnes peuvent avoir n’importe quel type de données excepté les types image, text et Java ADT.

Si le fichier n’existe pas, ce dernier est créé par le moteur. Si le fichier existe, les données dans le fichier sont écrasées.

Exemple :

create existing table authors_bcp(
        au_id       id            not null ,
        au_lname    varchar(40)   not null ,
        au_fname    varchar(20)   not null ,
        phone       char(12)      not null ,
        address     varchar(40)       null ,
        city        varchar(20)       null ,
        state       char(2)           null ,
        country     varchar(12)       null ,
        postalcode  char(10)          null 
)
external file at '/sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt' column delimiter ';'

Avec les tables proxy reposant sur des fichiers :

  • Les tables peuvent être sauvegardées dans des fichiers texte en utilisant les commandes select/into ou insert/select. Lorsqu’une commande insert est exécutée, chaque colonne est transformée en caractères dans le jeu de caractères par défaut du serveur. Les résultats sont mis en buffer et toutes les colonnes exceptées la dernière sont séparées par le délimiteur. La dernière colonne est terminée par le caractère de fin de ligne \n. Le buffer est ensuite écrit dans le fichier, représentant ainsi une ligne de données dans le fichier.
  • Ce procédé fournit une alternative SQL aux procédés bcp in, bcp out pour décharger le contenu d’une table dans un fichier ou transvaser le contenu d’un fichier dans une table.

Déchargement des données d’une table dans un fichier

Les tables proxy facilitent le déchargement du contenu des données d’une table dans un fichier : 

insert into authors_bcp
select * from authors

-----
Contenu du fichier : /sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt
172-32-1176;White;Johnson;408 496-7223;10932 Bigge Rd.;MenloPark;CA;USA;94025
213-46-8915;Green;Marjorie;415 986-7020;309 63rd St.#411;Oakland;CA;USA;94618
238-95-7766;Carson;Cheryl;415 548-7723;589 Darwin Ln.;Berkeley;CA;USA;94705
 ...

Insertion des données d’un fichier dans une table

Les tables proxy permettent d’insérer simplement les données d’un fichier texte dans une table :

select * into authors2 from authors where 1=2
go
insert into authors2
select * from authors_bcp
go

Interrogation

De simples interrogations peuvent être alors réalisées sur le fichier avec la commande SELECT grâce à la table proxy : 

select au_id from authors_bcp where au_lname like 'Wh%'

172-32-1176

Troncature d’un fichier

La commande truncate table sur une table proxy faisant référence à un fichier système vide le contenu du fichier

truncate table <proxy_table>

Trace flag 11206

Le trace flag 11206 consigne dans le fichier de log du serveur les opérations liées aux tables proxy reposant sur des fichiers externes : 

dbcc traceon(11206)
go

Le traceflag 11206 montre notamment que lors des interrogations, tous les enregistrements dans le fichier sont lus, donc il est nécessaire de prendre garde à la volumétrie du fichier.

Exemple de lecture : 

select au_id from authors_bcp where au_lname like 'Wh%'

00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
startscan (/sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt), fdes 0,
sdes 0x206a2a70
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
getnext (/sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt), fdes 0,
offset 0
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
converting '172-32-1176' to type 'VARCHAR'; src len 11
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
converting 'White' to type 'VARCHAR'; src len 5
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
getnext (/sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt), fdes 0,
offset 79
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
converting '213-46-8915' to type 'VARCHAR'; src len 11
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
converting 'Green' to type 'VARCHAR'; src len 5
 ...
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
fil_getnext: ufread() returned no data.
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
endscan (/sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt), fdes 0
00:00000:00019:2004/11/26 18:21:45.77 server FILEPRXY, spid 19:
fil_closetable(/sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt), fdes
-1

Exemple d’écriture: 

insert into recordlogs values ('ecriture')

00:00000:00020:2004/11/26 18:30:10.77 server FILEPRXY, spid 20:
fil_insert: opened
'/sdata/sybase/v12.5/TST_T1_ASE/files/recordlogs.txt', positioned at
offset 1839
00:00000:00020:2004/11/26 18:30:10.80 server FILEPRXY, spid 20:
inserted record of 9 bytes, beginning at offset 1839
00:00000:00020:2004/11/26 18:30:10.80 server FILEPRXY, spid 20:
fil_closetable(/sdata/sybase/v12.5/TST_T1_ASE/files/recordlogs.txt),
fdes 0

Exemple de troncature :

truncate table authors_bc

00:00000:00020:2004/11/26 18:27:52.95 server FILEPRXY, spid 20:
truncating file /sdata/sybase/v12.5/TST_T1_ASE/files/authors.txt