Extraction et exploitation des exceptions Replication Server

1- Introduction
2- Pourquoi extraire les commandes SQL d'une exception dans un fichier exploitable ?
3- Extractions des commandes SQL d'une exception
- 3-1- Extractions depuis les tables rs_systext et rs_exceptscmd dans la base RSSD
- 3-2- Méthodologie d'extraction d'une exception
4- Script rsdumpexception.ksh
Annexe
- Historique
- Liens

1- Introduction

Cet article propose une méthode (via un script shell) pour extraire dans un fichier plat lisible et exploitable les commandes SQL d'une exception rencontrée par un moteur Replication Server vers une cible.

Le contexte du schéma de réplication de l'article est le suivant :

contexte réplication image

Les exceptions sont enregistrées dans la base RSSD (Replication Server System Database) du moteur de réplication.

L'extraction des commandes SQL dans une exception est disponible dans le guide pratique Sybase Replication Server - §3 : Guide pratique et astuces Sybase Replication Server »

2- Pourquoi extraire les commandes SQL d'une exception dans un fichier exploitable ?

Le cas ne doit jamais arriver si le système de réplication est bien construit mais une transaction traitée par un moteur Replication Server vers une cible et contenant un nombre très important de commandes SQL peut être en échec à cause de quelques ordres SQL (violation de contraintes d'unicité, de contraintes étrangères etc...).

Dans l'exemple qui suit, la transaction en échec vers la cible DEC_T5_ASE.idee contient 314 420 ordres SQL.

RSD_D1_ASE.DEC_D1_REP_RSSD.1> rs_helpexception 113
RSD_D1_ASE.DEC_D1_REP_RSSD.2> go

        Detailed Summary of Logged Transaction # 113      on 'DEC_D1_REP'
 
 Origin Site                    Origin User     Org. Commit Date  #Cmds in Xact
 ------------------------------ --------------- ----------------- -------------
 DEC_T1_ASE.idee                ideemgr         Sep 10 2009  3:56        314420

 Dest. Site                     Dest. User      Date Logged      
 ------------------------------ --------------- -----------------
 DEC_T5_ASE.idee                idee_maint      Sep 11 2009  4:47
 
This transaction was logged by the 'sysadmin log_first_tran' command.

En supposant que l'erreur "Attempt to insert duplicate key" soit rencontrée par le moteur Replication Server dans cette transaction XActID=113, pour retrouver la ou les commandes SQL qui génèrent cette violation de contrainte d'unicité parmi ces 314 420 ordres SQL, il est préférable de disposer des ordres SQL contenus dans la transaction dans un format lisible et exploitable, exploitable notamment par awk, sed et tous les utilitaires Unix qui sont d'un immense secours dans ce contexte.

L'exploitation de ce fichier des ordres SQL dans la transaction va permettre notamment de déterminer très rapidement le(s) ordre(s) SQL problématique(s) en réalisant des croisements de données entre les données cible et les ordres SQL. À la fin de l'analyse, deux options possibles :

Les données dans la base cible sont modifiées pour relancer la transaction depuis Replication Server.
```
resume connection to DEC_T5_ASE.idee exec tran
```
Les ordres SQL sont modifiés (suppression, transformation d'ordre INSERT en ordres UPDATE etc...) dans la transaction, transaction ensuite exécutée dans la base cible. Ainsi la transaction est ensuite écartée en toute sécurité dans Replication Server avec l'option skip tran de la commande resume connection.
```
resume connection to DEC_T5_ASE.idee skip tran
```

3- Extractions des commandes SQL d'une exception

3-1- Extractions depuis les tables rs_systext et rs_exceptscmd dans la base RSSD

Pour extraire les requêtes SQL d'une exception, il suffit d'interroger les tables rs_systext et rs_exceptscmd dans la base RSSD du moteur Replication Server pour la transaction XActId associée à l'exception :

declare @xactid int
select @xactid = 113 
select
 textval
from rs_systext, rs_exceptscmd
where cmd_id = parentid
and texttype='C'
and cmd_type='L'
and (case (select convert(int, 0x0000100))
  when 65536 then convert(int, reverse(substring(sys_trans_id, 5, 8)))
  else convert(int, substring(sys_trans_id, 5, 8))
  end
 ) = @xactid
order by src_cmd_line,sequence

La requête ci-dessus fonctionne très bien lorsqu'aucune commande SQL dans la transaction ne dépasse la longueur de la colonne textval qui est taillée à varchar(255) dans la table rs_systext.

Dans le cas d'une commande SQL dépassant les 255 caractères, un saut de ligne au moins coupe la commande SQL qui est répartie sur plusieurs lignes ordonnées grâce à la colonne sequence dans la table rs_systext. Les coupures de ces commandes SQL sont peu pratiques pour disposer d'un fichier exploitable.

3-2- Méthodologie d'extraction d'une exception

Dans ce contexte, une solution simple peut consister à décharger les commandes SQL de l'exception dans un fichier plat par bcp puis traiter ce fichier avec awk pour concatèner les commandes SQL éventuellement réparties sur plusieurs lignes.

Pour cette opération une table proxy v_dba_dumpexception est créée. La table proxy v_dba_dumpexception affiche en mode RPC (Remote Procedure Call) les résultats de la procédure stockée sp_dba_dumpexception, procédure stockée qui retourne 3 colonnes pour une exception XActID : XActID, sequence et textval.

create procedure sp_dba_dumpexception @xactid int=NULL
as
select
 @xactid, sequence,textval
from rs_systext, rs_exceptscmd
where cmd_id = parentid
and texttype='C'
and cmd_type='L'
and (case (select convert(int, 0x0000100))
  when 65536 then convert(int, reverse(substring(sys_trans_id, 5, 8)))
  else convert(int, substring(sys_trans_id, 5, 8))
  end
 ) = @xactid
order by src_cmd_line,sequence
go

create existing table v_dba_dumpexception (
 xactid int not null,
 sequence int not null,
 textval varchar(255) not null)
external procedure at 'loopback.DEC_D1_REP_RSSD.dbo.sp_dba_dumpexception'
go

Exemple :

select * from v_dba_dumpexception where xactid=113
go
xactid   sequence   textval
------   --------   -------------------------------------------------------------
 ...
   113          1   update INSTRUMENT set INST_EXT_COD=NULL  where INST_NUM=623796
   113          1   execute rs_update_lastcommit @origin = 109, @origin_qid = 0x0004000b84f9164700f0f7de000400eaef9d001100009c800106c9b00000000000000001, @secondary_qid = 0x000000000000000000000000000000000000000000000000000000000000000000000000, @origin_time = '20090910 15:
   113          2   56:46:880'
 ...

Le tri sur les colonnes src_cmd_line et sequence est très important pour respecter l'ordre des commandes SQL dans la transaction.

La vue v_113 correspondant à select * from v_dba_dumpexception where xactid=113 est alors utilisée pour décharger les commandes SQL de l'exception XActID=113 dans un fichier par bcp en mode caractère. Pour cette opération, le séparateur utilisé ne doit pas être présent dans les commandes SQL :

create view v_113 as select * from v_dba_exception where xactid=113

%> bcp DEC_D1_REP_RSSD..v_113 out 113.sql -Usa -P<password> -SRSD_D1_ASE -t'####' -c

L'exécution du programme awk ci-dessous appliqué sur le fichier plat affiche les commandes SQL en prenant en charge la concaténation des commandes SQL réparties sur plusieurs lignes en s'appuyant sur la valeur de la colonne sequence (col #2) :

cat 113.sql | awk -F"####" 'BEGIN { vTextVal="" }
{ if ($2==1) { print vTextVal; vTextVal=$3; } else { vTextVal = vTextVal$3 } }
END {  print vTextVal; }'

update INSTRUMENT set INST_EXT_COD=NULL  where INST_NUM=623823
update INSTRUMENT set INST_EXT_COD=NULL  where INST_NUM=623824
update INSTRUMENT set INST_EXT_COD=NULL  where INST_NUM=623796
 commit transaction
execute rs_update_lastcommit @origin = 109, @origin_qid = 0x0004000b84f9164700f0f7de000400eaef9d001100009c800106c9b00000000000000001, @secondary_qid = 0x000000000000000000000000000000000000000000000000000000000000000000000000, @origin_time = '20090910 15:56:46:880'

La dernière commande SQL coupée sur 2 séquences textval est ainsi concaténée.

4- Script rsdumpexception.ksh

Le script rsdumpexception.ksh extrait les commandes SQL d'une exception dans un fichier plat et prend en charge la concaténation des ordres SQL scindés :

%> rsdumpexception.ksh <RepServer> <XactID> <Fichier> [<Separateur>]

Par défaut le séparateur est ####, le quatrième paramètre permet de spécifier un autre séparateur.

Voici un exemple d'appel avec spécification d'un séparateur différent :

%> rsdumpexception.ksh DEC_D1_REP 113 /tmp/test.tmp "@@"

Les codes retour suivants sont retournés pour une éventuelle utilisation au sein d'un autre script :

0	Export de l'exception réussi. %> rsdumpexception.ksh DEC_D1_REP 113 /tmp/test.tmp System object creation Exception extraction ... 314422 rows copied. Clock Time (ms.): total = 9126 Avg = 0 (34453.43 rows per sec.) Applying final awk program, formatting file End, return code is 0
1	L'extraction bcp est en échec. %> rsdumpexception.ksh DEC_D1_REP 113 /tmp2/test.tmp System object creation Exception extraction Unable to open host file '/tmp2/test.tmp' An error ocurred during exception extraction, bcp return code is 2 End, return code is 1
2	L'application du programme awk est en échec. %> rsdumpexception.ksh DEC_D1_REP 113 /tmp/test.tmp System object creation Exception extraction ... 314422 rows copied. Clock Time (ms.): total = 5792 Avg = 0 (54285.57 rows per sec.) Applying final awk program, formatting file An error ocurred while applying awk program End, return code is 2
3	Erreur de syntaxe dans l'appel du programme. %> rsdumpexception.ksh DEC_D1_REP 113 Usage : rs_dumpexception.ksh REPSERVER XActID FileName [Separator]

Ce script s'appuie sur une normalisation : l'environnement est pris à partir du fichier $DBA/etc/<RepServer>.rep, $DBA étant le répertoire /Software/sybase/dba.

Le serveur ASE hébergeant la base RSSD et le nom de la base RSSD sont récupérés depuis le fichier de configuration du moteur de réplication (paramètres RSSD_server et RSSD_database), fichier localisé dans la norme dans le répertoire $CFG (/Software/sybase/dba/<RepServer>/cfg) et ayant pour nomenclature <RepServer>.cfg.

2 pré-requis pour ce script

Le serveur local loopback est utilisé dans la définition de la table proxy v_dba_dump_exception ('loopback.DEC_D1_REP_RSSD.dbo.sp_dba_dumpexception'). En fonction de la version de Sybase ASE utilisée, si le serveur loopback n'existe pas encore dans la table sysservers, ajouter cette entrée avec la commande sp_addserver :
```
sp_addserver 'loopback',null,@@servername
go
```
Le serveur ASE hébergeant la base RSSD est sur la même machine que le moteur de réplication. La connexion à la base RSSD est alors réalisée avec le login sa, le mot de passe de ce login étant extrait depuis le fichier $DBA/RSSD_server/cfg/script/mdp/mdp_sa dans la norme adoptée.

#!/bin/ksh
#

PROG=$0

Usage() {
   echo "Usage : ${PROG} REPSERVER XActID FileName [Separator]"
   exit 3
}

rssd_createtmpview() {
        isql -U${SYB_RSSD_USER} -S${SYB_RSSD_ASESRV} -D${SYB_RSSD_DB}<<-EOF | grep -v '^Password:'
        ${SYB_RSSD_PASSWD}
        if exists (select 1 from sysobjects where type='V' and name='v_dumpxact')
        begin 
                drop view v_dumpxact
        end
        go
        create view v_dumpxact as select * from v_dba_dumpexception where xactid=${XACTID}
        go
        EOF

}


rssd_createsysobjects() {
        isql -U${SYB_RSSD_USER} -S${SYB_RSSD_ASESRV} -D${SYB_RSSD_DB}<<EOF | grep -v '^Password:'
        ${SYB_RSSD_PASSWD}
        if exists (select 1 from sysobjects where type='P' and name='sp_dba_dumpexception')
        begin 
                drop procedure sp_dba_dumpexception
        end
        go
                create procedure sp_dba_dumpexception @xactid int=NULL
                as
                select
                        @xactid, sequence,textval
                from rs_systext, rs_exceptscmd
                where cmd_id = parentid
                and texttype='C'
                and cmd_type='L'
                and (case (select convert(int, 0x0000100))
                when 65536 then convert(int,reverse(substring(sys_trans_id, 5, 8)))
                else convert(int, substring(sys_trans_id, 5, 8))
                end
                ) = @xactid
                order by src_cmd_line,sequence
        go

        if exists (select 1 from sysobjects where type='U'and name='v_dba_dumpexception')
        begin 
                drop table v_dba_dumpexception
        end
        go
                create existing table v_dba_dumpexception (
                        xactid int not null,
                        sequence int not null,
                        textval varchar(255) not null)
                external procedure at 'loopback.DEC_D1_REP_RSSD.dbo.sp_dba_dumpexception'
        go
        EOF
}


if [ $# -lt 3 ] 
then
  Usage
fi

REPSERVER=$1
XACTID=$2
FILENAME=$3

if [ $# -eq 4 ]
then 
        SEPARATOR=$4
else
        SEPARATOR="####"        
fi

export REPSERVER
export XACTID
export FILENAME
export SEPARATOR

REPSERVERNAME=$REPSERVER
export REPSERVERNAME

LOG_FILE=/tmp/tmp_dumpexception.${REPSERVER}.$$.log


# Verification de l'existence du fichier d'initialisation pour REPSERVER
if [ ! -f $DBA/etc/${REPSERVER}.rep ] 
then
   echo "The file $DBA/etc/${REPSERVER}.rep does not exist"
   Usage
fi

# Prise de l'environnement
 . ~sybase/.profile
 . $DBA/etc/${REPSERVER}.rep

if [ ! -f ${CFG}/${REPSERVER}.cfg ]
then
   echo "The replication server configuration file $CFG/${REPSERVER}.cfg does not exist"
   Usage
fi

SYB_RSSD_ASESRV=`cat $CFG/${REPSERVER}.cfg | grep "RSSD_server" | awk -F'=' '{print $2}'`
SYB_RSSD_DB=`cat $CFG/${REPSERVER}.cfg | grep "RSSD_database" | awk -F'=' '{print $2}'`
SYB_RSSD_USER="sa"
SYB_RSSD_PASSWD=`cat $DBA/${SYB_RSSD_ASESRV}/cfg/script/mdp/mdp_sa`

export SYB_RSSD_ASESRV
export SYB_RSSD_DB
export SYB_RSSD_USER
export SYB_RSSD_PASSWD

# Creation des objets necessaires : sp_dba_dumpexception et v_dba_dumpexception
echo "System object creation"
rssd_createsysobjects
rssd_createtmpview

echo "Exception extraction"
bcp ${SYB_RSSD_DB}..v_dumpxact out ${FILENAME}.$$ -U${SYB_RSSD_USER} -S${SYB_RSSD_ASESRV}
 -P${SYB_RSSD_PASSWD} -t"${SEPARATOR}" -c

RC=0
RCBCP=$?

if [[ $RCBCP -ne 0 ]]
then
        echo "An error ocurred during exception extraction, bcp return code is $RCBCP"
        RC=1
else
        echo "Applying final awk program, formatting file"
        cat ${FILENAME}.$$ | awk -F"${SEPARATOR}" 'BEGIN { vTextVal="" } \
{ if ($2==1) { print vTextVal; vTextVal=$3; } else { vTextVal = vTextVal$3 } } \
END {  print vTextVal; }' > ${FILENAME}
        RCAWK=$?
        
        if [[ $RCAWK -ne 0 ]]
        then
                echo "An error ocurred while performing awk program"
                RC=2
        fi

fi

# Suppression du fichier temporaire
if [ -f ${FILENAME}.$$ ]
then
        rm -f ${FILENAME}.$$
fi

echo "End, return code is $RC"

exit $RC

Annexe

Historique

Version	Date	Commentaires
1.0	12/2009	Version initiale

Liens

SQLPAC : Modèle des exceptions dans la base RSSD et suppression efficace des exceptions Sybase Replication Server
SQLPAC : Guide pratique et astuces Sybase Replication Server
RS 15 BOL : rs_helpexception
RS 15 BOL : resume connection
RS 15 BOL : rs_systext
RS 15 BOL : rs_exceptscmd