9th Feb

ORACLE - MS-SQL DBLink fejlesztési tapasztalatok

Monday, February 9, 2015 - 10:54
0
Author: Fegyver Gyula

Egyik partnerünk számára új ORACLE adatbázis alapú C4 megvalósítása volt a feladat. A projekt egyik kisebb részfeladata volt a meglévő MSSQL alapú Jogosultságkezelő Rendszerrel (továbbiakban JKR) történő kapcsolattartás folyamat kialakítása. A szükséges két rendszer közötti szinkronizálás, egy Oracle heterogeneous services alapú DB-LINK –re lett alapozva. 

A BLOG célja, hogy bemutassa milyen előre nem várt problémákkal kellett szembesülnünk az implementáció és a tesztelések során.

Megvalósítás - ELSŐ rész

Az eredeti koncepció tehát az volt, hogy az ORACLE adatbázisban elkészített DB-LINK segítségével a szinkronizáláshoz szükséges SQL STATEMENT –ket átadjuk az MS-SQL –nek, és a napi folyamat végén a táblák egyformák lesznek.

DB-LINK készítése:

Erről nem szükséges részletesen szólni, hiszen már készült erről egy bejegyzés. Inkább csak a „speciális” körülményeket szeretném ismertetni.

Dorsum –ban korábban már többször is készítettünk, és használtunk DB-LINK –t ORACLE és MS-SQL között. Jelen esetben nehézséget az okozott, hogy a szokásos OS helyett (UNIX, LINUX) üzemeltetőnél AIX operációs rendszer volt telepítve a HOST –n az ORACLE alatt. A szokásos DB-LINK építési módszer megdőlt, és kénytelenek voltunk egy közbenső WIN SERVER –s gépet beiktatni az architektúrába. Ezen a „GATEWAY” gépen gyakorlatilag csak egy ORACLE LISTENER és egy MS ODBC volt telepítve. Ez utóbbi mutatott az távoli (nem belső LAN –n található) MSSQL adatbázisra. (Természetesen a további szükséges paraméterfájlok is beállításra kerültek: HS fájl, LISTENER paraméterfájl, TNS rekord, stb.)

Számtalan problémánk volt a DB-LINK –n keresztüli magyar ékezetes karakterek, valamint a lebegőpontos számok helyes átvitelével. Mindegyik problémát sikerült megoldani, de némely „megoldás” nem volt nevezhető valódi megoldásnak. Kiemelhető pl., hogy a numerikus adatok átvitelének problémája úgy lett áthidalva, hogy forrás oldalon VIEW –kat készítettünk ezen táblákra, ahol FLOAT típusra kerültek konvertálásra az érintett oszlopok. (Megjegyzem ez utóbbi megoldás, és probléma nem a JKR –s DB-LINK esetében, hanem egy másik, de teljesen hasonló DB-LINK alkalmazásánál jelentkezett ugyanezen projektben.)

Fejlesztés:

A szinkronizáció implementációja egy ORACLE PACKAGE –ben lett megvalósítva. Már az első verzió készítésekor nyilvánvaló volt, hogy a kb. 15 db. adott esetben nagyméretű tábla napi szinkronizációja nem lesz technikailag gyorsan végrehajtható.

Ebből kiindulva úgy terveztem, hogy ORACLE oldalon létrehozok TMP jellegű táblákat, abból a célból, hogy egyáltalán NE legyen szükség a DB-LINK oldali kommunikációra a szinkronizálandó rekordok detektálása céljából. Így a lassú DB-LINK kommunikációt csak a legszükségesebb feladatra használom – az adatok áttöltésére. Nyilvánvaló volt, hogy ORA-MSS közötti DB-LINK használatában erős korlát lesz, hogy csak 1 sorokat lehet futtatni. BULK jellegű feldolgozásra nincs lehetőség.

A fejlesztéssel szemben támasztott igény volt, hogy természetesen konzisztens szinkronizáció valósuljon meg. A rekordok mellett található ORA_ROWSCN PSEUDO változó segítségével tökéletesen megvalósítható ez a fajta konzisztencia. (Később lehet fokozni az itt jelentkező teljesítménygondot a szinkronizálandó táblák ROWDEPENDENCIES beállításával.)

Tehát a szinkronizálás első pillanatában V$DATABASE –ből kinyert CURRENT_SCN tranzakció azonosító és az ennél kisebb rekordok valamint az előző szinkronizáció elmentett SCN –től nagyobb rekordokra kell szinkronizálni.

 

Így biztosított, hogy a szinkronizálás kezdetének pillanata után keletkezett rekordok nem kerülnek átadásra (azok majd másnap szinkronizálódnak). Optimalizálási célból UPDATE utasítást nem szinkronizálók, csak INSERT, és DELETE –ket. Ez olcsóbb utasításpár, mint a „széles” táblákra a sok oszlopot módosítani. 

Az alábbi módon kerülnek „átadásra” pl. az „UPDATE” –k:

vv_statement := '
         begin
           for r in (select * from ' || av_table_name || ' where ora_rowscn between '
             || grec_tbl_rec_sync.last_max_scn || ' and ' || grec_tbl_rec_sync.start_scn || ')
           loop
             delete from ' || av_table_name || '@' || GCD_LINK_NAME || ' where ' || vv_where1 || ';
             insert into ' || av_table_name || '@' || GCD_LINK_NAME || ' values r;
           end loop;
         end;';
 
execute immediate vv_statement;
 
Ha az összes INSERT, DELETE, és „UPDATE” átment, akkor szükséges utána az ORACLE oldali TMP táblákat is szinkronizálni. Hiszen ezen táblák lesznek alapjai a köv. szinkronizációnak. Ez alapján fog kiderülni, hogy mely rekordokat kell majd akkor áttölteni. Az említett TMP táblákban, ezen lépések szükségesek:
 
vv_statement := 'merge into ' || vv_cpy_tbl_name || ' trg
                 using (
                   select *
                     from ' || av_table_name || '
                    where ora_rowscn <= ' || grec_tbl_rec_sync.start_scn || ') 
                      src on (' || vv_where || ')
                 when     matched then
                   update set ' || get_cols(av_table_name, 2) || '
                 when not matched then
                   insert (' || get_cols(av_table_name, 0) || ')
                   values (' || get_cols(av_table_name, 1) || ')';
 
execute immediate vv_statement;
 
vv_statement := 'delete ' || vv_cpy_tbl_name || ' trg
                  where not exists (select 1
                                      from ' || av_table_name || ' src
                                     where ' || vv_where || ')';
execute immediate vv_statement;
 
Mint látható, szükséges egy MERGE utasítás az eredeti táblával, valamint ki kell törölni azokat a rekordokat, amelyek a szinkronizáció során is kitörlődtek. Megjegyzem, hogy a DB-LINK –n keresztüli MERGE sajnos ugyancsak nem működik.

Tesztelés során tapasztalt hibák:

A korábban jelzett DB-LINK hibák kiküszöbölése után jelentkezett a legfőbb probléma, amit nem is sikerült megoldani. Az pedig a DB-LINK –n keresztüli egysoros STATEMENT –k teljesítménygondja.

A Bank, és az üzemeltető cég nem egy telephelyen található. A kommunikációt köztük egy 100MB –s bérelt vonal biztosítja. Ez a sebesség nem lenne szűk keresztmetszet, de az egyenkénti DB-LINK kommunikáció felépülése, és lebontása olyan jelentős időbe kerül, ami miatt egy kisebb szinkronizáció is a tervezettnél jóval tovább tartott. Hálózatos szakemberek nyomozása indult, de nem nagyon bíztunk a sikerben, ezért párhuzamosan elindult az átírás…

 

Megvalósítás - MÁSODIK rész

Az új terv szerint a teljes keretrendszer megmarad, de a soronként DB-LINK áttöltést meg kell szüntetni. Alternatívaként a teljes konzisztens szinkronizáció megtartása mellett az SQL STATEMENT –ket egy ORACLE DIRECTORY –ba írja a kód egy fájlba. Végeredményként létrejön egy PATCH.SQL fájl, ami tartalmazza az összes INSERT, és DELETE utasítást. Mindezt olyan formában, hogy az MS-SQL számára értelmezhető legyen. A feladat legjelentősebb része tehát itt az volt, hogy olyan SQL STATEMENT –ket generáljon a kód, ami képes minden további konverzió nélkül arra, hogy JKR adatbázisban hibamentesen lefusson. 
 
A hatékonyságot figyelembe véve itt elsősorban olyan SQL STATEMENT –t kellett generálni, ami képes INSERT utasításokat "generálni":

    function "_get_INS_statement"(

      av_table_name varchar2,
      av_where      varchar2) 
      return varchar2 as
 
      vv_cols_values  varchar2(32000);
      vv_cols         varchar2(32000);
      vv_ret          varchar2(32000);
 
      cursor vcur_columns(av_table_name varchar2) is
        select column_name, data_type, column_id
          from user_tab_cols
         where table_name     = av_table_name
           and hidden_column  = 'NO'
         order by column_id;
 
    begin
      for r in vcur_columns(upper(av_table_name)) 
      loop
        if instr(r.data_type, 'CHAR') > 0 then
          vv_cols := '''''''''||replace(' || r.column_name || ',chr(39), chr(39)||chr(39))||''''''''';
        elsif instr(r.data_type, 'DATE') > 0 then
          vv_cols := '''''''''||to_char(' || r.column_name || ',''yyyy.mm.dd hh24:mi:ss'')||''''''''';
        else
          vv_cols := r.column_name;
        end if;
        vv_cols_values := vv_cols_values || case r.column_id
                                              when 1 then ''
                                              else        ','
                                            end || '''||decode(' || r.column_name || ',null,''null'',' || vv_cols || ')||''';
      end loop;
 
      vv_ret := 'select ''insert into ' || av_table_name || ' values (' || vv_cols_values || ');'' from ' || av_table_name || ' where ' || av_where;
      return vv_ret;
    end;
 
Természetesen itt még további feladat a PATCH fájl átmozgatása az MS gépre, és ott megfuttatni, stb..
 

Konklúzió

Tisztában voltunk a DB-LINK használatában jó néhány korlátra, de ez az említett teljesítményprobléma minket is meglepett. A DB-LINK maga kevesebb korlátot jelentett volna, és sokkal kevésbé lett volna szűk keresztmetszet, ha a másik oldalán is ORACLE adatbázis található.. 

Mondhatjuk, hogy a továbbiakban is fogunk használni DB-LINK –t, akár nem ORACLE adatbázishoz is, de ahol olyan funkciót kell megvalósítani, ami sok SQL utasítás továbbítását jelenti, ott már nem lehet alternatíva a DB-LINK.

Comments (0)
0