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


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

2- Prérequis

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

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

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

sp_configure 'enable file access',1
go

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

3- Tables proxy reposant sur des fichiers système

3-1- Généralités

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

3-2- 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 commande 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.

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

3-2-1- 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.

3-2-2- 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

3-3- 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
etc. etc.
) 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 :

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

3-3-2- 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

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

3-3-4- Troncature d'un fichier

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

truncate table <proxy_table>

4- Trace flag 11206

Le trace flag 11206 permet de traquer 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

Annexe

Historique

Version Date Commentaires
1.0 11/2004 Version initiale

Liens

Adaptive Server Enterprise 12.5 - Component Integration Services User's Guide, File system access