Pour ce qui concernent les bases de données oracle, nous avons utilise plusieurs technologies: IBM FLASHCOPY, Oracle Import/export…(cela fera l’objet d’un prochain article).

Dans le présent article, nous allons nous intéresser au cas des bases de données SQL Server. Il est d’usage connu que pour transférer des données d’une TABLE SQL server vers Oracle, l’on utilise les DTS pour les versions jusqu’à SQL Server 2000 ou SSIS à partir de SQL Server 2005.

Quelles étaient les difficultés dans notre cas ?

• Les données à extraire concernaient plusieurs tables dans plusieurs bases données SQL server • Les données devraient résider dans différents schémas dans la base de datawarehouse oracle. • Les actions d’acquisition et de chargement doivent se faire de façon automatique dans un délai de temps limité. • La liaison réseau entre les bases SQL Server et le serveur datawarehoure avait un débit de 100MB/S.

Solution choisie

Je ne vais pas entrer dans les détails de solutions que nous avons due envisager avant de choisir, je me contenterai simplement de vous expliquer celle que nous retenue. Nous avons donc décidé d’utiliser SQL Server Integration Services (SSIS). Il faut noter que tous les documents que nous avons trouvé sur le sujet, traitent toujours dans le sens inverse (i.e: de Oracle vers SQL server) ; tout à fait normal puisque SSIS est un outil Microsoft. D’ailleurs si vous être confronté au problème dans le sens inverse, j’ai un excellent document de Microsoft intitulé “SQL Server Integration Services with Oracle Database 10g “ que je pourrais vous transmettre.

Choix de la configuration SSIS

Nous avons fait deux tests pour choisir la configuration:

1er test: Un premier test qui consistait à extraire les données et les charger directement dans la base de données distant oracle : - choix d’une table de test de l’application sous SQL server ayant environ 200.000 lignes - Création d’une table sur le serveur oracle avec la même structure. - Création d’un package SSIS qui extrait les données de la table SQL server et les charge dans la table oracle sur le serveur distant.

Résultat: environ 47mn pour terminer l’opération

2e test: Dans ce deuxième test nous avons fait les étapes suivantes: - choix d’une table de test de l’application sous SQL server ayant environ 200.000 lignes - Création d’une table sur le serveur oracle avec la même structure. - Création d’un package SSIS qui extrait les données de la table SQL server dans un fichier plat et le transfert vers le serveur Oracle par FTP. - Utilisation de Oracle SQL Loader pour charger les données dans la table oracle

Résultat: moins de 5 mn pour terminer l’opération

Vous comprenez donc que le choix s’est fait de lui-même.

Problème de création du package SSIS

Nous avons été confronté au problème de création de fichier en fonction de la table sélectionnée pour le « upload » des données. Il s’agissait de parcourir chaque schéma dans SQL server, de sélectionner une table et en fonction de son nom, créer un fichier au niveau OS pour accueillir les données. N’ayant pas trouvé de paramètres adéquats dans la configuration des composants des packages SSIS, nous avons du faire appel à un petit script VB.NET pour cela (merci à Gilles pour le script, car je suis très nul en VB). N’étant pas l’auteur de ce scripts, je me permet de respecter le copyright en m’abstenant de le mettre ici.

Problème de création des tables dans la base oracle

Etant donné que nous avions environ 80 à 150 tables par application à charger dans oracle, il fallait trouver un moyen plus au moins automatique pour obtenir la structure des tables de SQL server et les créer dans oracle avec les conversions de types nécessaires. Si vous connaissez un moyen avec SSIS de faire cela, je serais très heureux de l’avoir (j’avoue que je ne suis pas assez costaux et même novice dans ce domaine). Pour ma part, j’ai du faire appel à Oracle SQL Developper. Cet outil, dans sa version actuelle permet de se connecter à une base SQL Server et de migrer un ou plusieurs schémas vers une base oracle. Si vous vous demandez pourquoi je n’ai pas utilisé cet outil pour résoudre mon problème de bout en bout, rappelez-vous que j’ai dit que le processus devrait être automatique (l’operateur choisir dans un menu, et tout doit se faire).

Problème de création des control file pour SQL loader

Etant donné le nombre de tables, il était impossible d’envisager une création manuelle des fichiers de control pour le chargement SQL Loader. Nous avons écrit les scripts suivants pour faire cela.

Un script SQL pour générer le fichier de control selon le schéma et la table:

__SET verify off
SET heading off
SET embedded ON
SET recsep off
SET pages 0
SET LINES 80
SET feedback off

define owner=&1
define tab_name=&2
spool /appli/oracle/dwhworkspace/sqlldr/&owner/&tab_name..ctl
 
SELECT 
'load data 
infile ' || '/appli/oracle/dwhworkspace/windata/&owner/' || upper('&tab_name') || '.CSV'|| ' 
INTO TABLE ' || upper('&owner') || '.' || upper('&tab_name') || '
REPLACE 
FIELDS terminated BY |' || '
TRAILING nullcols' 
from all_tables where owner = upper('&owner') and table_name = upper('&tab_name')
/
 
select decode(a.column_id, '1', '(') || a.column_name || 
(case when a.column_id < max(b.column_id) then ',' else ')' END)
from all_tab_columns a, all_tab_columns b
where a.owner = upper('&owner') and a.table_name = upper('&tab_name') and
b.owner = upper('&owner') and b.table_name = upper('&tab_name')
group by a.column_name, a.column_id
order by a.column_id
/
 
spool off
set verify on
set heading on
set embedded off
set recsep wrapped
set pages 64
set lines 80
set feedback 6
undefine 1
undefine 2
exit
__

Et le script Unix qui appelle le script SQL:

__#!/usr/bin/sh
export DWH_WK_DIR=/appli/oracle/dwhworkspace
DATE=`date +"%Y%m%d:%T`
DAT=`date +"%Y%m%d`
LOG=$DWH_WK_DIR/log/create_sqlldr_ctl_$DAT.log
APPS=" " #list des schemas oracle 
ORACLE_HOME=/appli/oracle/product/10.2.0
SQLPLUS="$ORACLE_HOME/bin/sqlplus" 
export ORACLE_SID=

echo "Start create_sqlldr_ctl.sh at $DATE........." >>$LOG
 
for app in `echo $APPS`
do
 echo "start generating SQL Loader control files for $app..." >>$LOG 
 #Get application tables list
 $SQLPLUS '/ as sysdba'  @create_tab_list.sql $app 
 for tab in `cat $DWH_WK_DIR/windata/${app}_tables.lst`
 do
   $SQLPLUS '/ as sysdba' @generate_sqlldr_ctl.sql $app $tab
 done
 echo "End generating SQL Loader control files for $app..." >>$LOG
done
 
DATE=`date +"%Y%m%d:%T`
echo "End create_sqlldr_ctl.sh at $DATE........." >>$LOG
exit 0 __

Finalement…

Après ces configuration et scripts, il ne restait qu’à écrire des scripts pour exécuter SQL Loader pour chaque table de chaque application:

__#!/usr/bin/sh
ORACLE_BASE=/appli/oracle 
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
ORACLE_SID=
ORACLE_OWNER=oracle
CONUSER=`whoami`
SQLLDR="$ORACLE_HOME/bin/sqlldr"
SYSPASS=
DATE=`date +"%Y%m%d:%T`
DAT=`date +"%Y%m%d`
DWH_WK_DIR=/appli/oracle/dwhworkspace
LOG=$DWH_WK_DIR/log/load_win_data_$DAT.log
APPS=""

echo "Start Load_win_data.sh at $DATE........." >>$LOG

#verify that the user executing the scipt is the oracle soft owner
if $CONUSER != $ORACLE_OWNER; then
   echo "ERR: This script must be runned as user: $ORACLE_OWNER" >>$LOG
   exit 1
fi

#Loading data from flat  files;
for app in `echo $APPS`
do
  echo "Start loading $app data..." >>$LOG
  for tab in `cat $DWH_WK_DIR/windata/${app}_tables.lst`
  do
    $SQLLDR userid=system/$SYSPASS@$ORACLE_SID  control=$DWH_WK_DIR/sqlldr/${app}/${tab}.ctl log=load_$app_$DAT.log
  done
  echo "End loading $app data...see load_$app_$DAT.log for errors." >>$LOG
done

DATE=`date +"%Y%m%d:%T`
echo "End load_win_data.sh at $DATE........." >>$LOG
exit 0
__

ET VOILA LE TRAVAIL…