Vysoká škola ekonomická v Praze Fakulta informatiky a statistiky Vyšší odborná škola informačních služeb v Praze
Jakub Laušman
Optimalizace postloadových procesů datového skladu Raiffeisenbank Bakalářská práce
2007/2008
1. Prohlášení Prohlašuji, že jsem diplomovou (absolventskou, bakalářskou) práci na téma ‘Optimalizace postloadových procesů datového skladu Raiffeisenbank’ zpracoval samostatně a použil pouze zdrojů, které cituji a uvádím v seznamu použité literatury.
V Praze dne: ........................
Podpis:....................................
-1-
2. Obsah:
1. Prohlášení.................................................................................................... 1 2. Obsah: ......................................................................................................... 2 3. Anotace........................................................................................................ 5 4. Úvod............................................................................................................. 6 4.1. Úvodní slovo.......................................................................................... 6 4.2. Prostředí................................................................................................ 7 4.2.1. Datový sklad ................................................................................... 8 4.2.2. Oracle ........................................................................................... 11 4.2.3. Informatica .................................................................................... 12 4.2.3.1. Mapping designer................................................................... 12 4.2.3.2. WorkflowDesigner .................................................................. 16 4.2.3.3. Worfkflow Monitor................................................................... 19 5. Vymezení problému ................................................................................... 20 6. Co vše ještě potřebujeme vědět:................................................................ 22 6.1. Průběh loadu Interfaces ...................................................................... 22 6.1.1. Způsob pojmenováni Interfaces .................................................... 22 6.1.2. STL_CONTROL_RECORDS resp. ETL_CONTROL_RECORDS 23 6.1.3. Jak probíhá load Interfaces........................................................... 24 6.2. Vytvoření nového Interface.................................................................. 26 7. Vývoj Procedury stl_postload_counting ..................................................... 29 7.1. Tabulky................................................................................................ 29 7.1.1. STL_CONTROL_RECORDS ........................................................ 29 7.1.2. ETL_ERROR_MESSAGES .......................................................... 29 7.2. Proměnné ............................................................................................ 30 7.2.1. Vstupní proměnné......................................................................... 30 7.2.2. Výstupní proměnné....................................................................... 30 -2-
7.2.3. Ostatní proměnné ......................................................................... 31 7.2.4. Proměnné pro EXCEPTION.......................................................... 32 7.3. Cursory ................................................................................................ 33 7.3.1. CURSOR stl_cur........................................................................... 33 7.3.2. CURSOR systemsCheck_cur ....................................................... 34 7.3.3. CURSOR midCheck_cur............................................................... 35 7.4. Soustava IF podmínek a EXECUTE IMMEDIATE ............................... 37 7.4.1. Naplnění cursorů a EXECUTE IMMEDIATE ................................. 38 7.4.2. Vysvětlení soustavy IF podmínek ................................................. 39 7.4.2.1. IF00 ........................................................................................ 39 7.4.2.2. IF01 ........................................................................................ 39 7.4.2.3. IF02 ........................................................................................ 39 7.4.2.4. IF03 ........................................................................................ 40 7.5. LOOP cyklus........................................................................................ 42 7.6. Průběh procedury ................................................................................ 43 7.7. Testování procedury............................................................................ 45 7.7.1. Testování standardní funkčnosti ................................................... 45 7.7.2. Testování procedury při chybových situacích ............................... 45 7.8. Nasazení procedury ............................................................................ 47 8. Aplikace a ladění procedury....................................................................... 49 8.1. Oprava procedury – přidání kontroly na status .................................... 49 8.2. Nasazení na MIDAS ............................................................................ 51 8.3. Problém s kontrolou VSTUP = VÝSTUP ............................................. 52 8.3.1. Řešení problému kontroly ............................................................. 52 9. Závěr.......................................................................................................... 54 10. Seznam použitých zdrojů: ........................................................................ 55 10.1. Zdroje s prací úzce spojené............................................................... 55 10.2. Konzultace s zkušenějšími zaměstnanci ........................................... 55 10.3. Ostatní zdroje spjaté s tématem ........................................................ 55
-3-
11. Přílohy...................................................................................................... 57 11.1. STL_POSTLOAD_COUNTING_v1.0.doc .......................................... 57 11.2. STL_POSTLOAD_COUNTING_v1.1.doc .......................................... 62 11.3. STL_POSTLOAD_COUNTING_v1.2.doc .......................................... 68
-4-
3. Anotace Tato práce pojednává o tom, jak na základě zakázky vedení datového skladu, provést optimalizaci postloadových procesů. Nejdříve je představeno prostředí, ve kterém se bude čtenář pohybovat, poté následuje seznámení s důležitými procesy, které s danou tématikou úzce souvisí. Jakmile je čtenář seznámen se vším důležitým, začne se popisovat vývoj procedury a s tím spojené procesy. Následuje shrnutí nasazení vyvinuté procedury do živého datového skladu, resp. jestli vše dopadlo úspěšně či neúspěšně, na co nového se při běhu procedury v živém skladu přišlo a nastínění, jak se budou zjištěné skutečnosti řešit.
-5-
4. Úvod 4.1. Úvodní slovo Tato práce se bude zabývat řešením optimalizace jednoho z mnoha procesů hlavního „loadu“ v datovém skladu Raiffeisen bance (dále jen RBCZ) konkrétně „loadu Interfaces“. Nejedná se o jednoduchý proces, a proto budu nucen vysvětlit mnoho výrazů, procesů, a alespoň jemně nastínit, v jakém prostředí se budeme v této práci pohybovat. To vše bych rád udělal před tím, než vám popíši jakým způsobem budeme optimalizaci řešit, a čeho přesně se optimalizace týká. Doufám, že budou všechny termíny jasně popsány a tato práce bude mít pro vás jistý přínos.
-6-
4.2. Prostředí V této podkapitole bych rád nastínil, v jakém prostředí se budeme pohybovat. Jedná se o databázové prostředí na platformě Oracle a o nadstavbový nástroj Informatica, který spolupracuje s databází Oracle. Dohromady jsou tyto dva nástroje to hlavní, co tvoří náš datový sklad, k němuž samozřejmě patří i velmi výkonný hardware. Databázi oracle popíši velmi stručně, protože očekávám, že ten kdo bude číst tuto práci se již s nějakou databází setkal (ať již Oracle či MS SQL Server nebo další…). Nástroj Informatica již popíši trošku podrobněji, protože se v něm při vývoji procedury budeme pohybovat a tak bych rád, aby čtenář věděl co se právě děje. Nejprve bych vám však rád stručně popsal architekturu našeho datového skladu a ukázal část, ve které se budeme při vývoji naší postloadové procedury pohybovat.
-7-
4.2.1. Datový sklad Začněme tedy tím, co je to datový sklad (dále jen DS). DS je rozsáhlá databáze, ve které se soustředí data z mnoha různých systémů. Protože tyto source systémy, ze kterých se data do DS získávají, pracují na různých OS a s různými typy databází, musí data projít určitými transformacemi, čištěním, tříděním, apod. aby byla pro vlastníka DS vůbec použitelná. Nyní se vám pokusím na obrázku1, který vidíte níže, vysvětlit jak DS RBCZ funguje.
Jak na obrázku vidíte, rozděluje se do dvou hlavních částí. První část ukazuje source systémy jako např. APS, MIX, či MIDAS (MID) jenž jsou na straně poboček RBCZ nebo například ČNB či Tatrabanky (banka, která se stará o bezhotovostní platební styky za pomocí platebních karet [sídlo na Slovensku]). Druhá část obrázku znázorňuje centrálu RBCZ v Praze, kde se informace z těchto source databází soustředí na jednom místě, neboli DS RBCZ.
1
Obrázek vytvořen v PowerDesigneru12. Jedná se o upravený obrázek z knihy (zdroj č. 1)
Chapter 1:Data Warehousing -> A historical pespercive -> Figure 1.1 Obrázek z této knihy jsem přizpůsobil stavu DS RBCZ, kde pracuji.
-8-
V první části se ze source systémů stahují data přes ODBC connections do Staging area (dále jen STGA – místo pro ukládání ještě nezpracovaných dat) v RBCZ. V druhé části je toho už trošku více. Row data z source systémů se stáhnou přes ODBC do tzv. STGA. Zde se data ukládají a jsou využívána dále. Tato data z STGA, která nebyla ještě žádným způsobem upravena, se používají jako zdrojová data pro „Interfaces“. „Interfaces“ jsou tabulky, do kterých se nahrávají data z STGA. Než se však data z STGA dostanou do „Interfaces“ musí projít řadou transformací2 jako například, čištění od mezer (otrimování), přetypování, upravení velikých a malých písmen, přidání komentářů k jednotlivým sloupcům apod. Nahrávání „Interfaces“ se řídí tabulkou STL_CONTROL_RECORDS, k této tabulce se později vrátím a vysvětlím její přesnou funkcionalitu. Takto zpracovaná data, která již obsahují „Interfaces“, se pak používají k plnění faktovek a dimenzí. Tyto tabulky jsou už logicky sdružená a propojená data, nad kterými už se dají dělat analýzy a dají se na nich stavět datamárty3 (dále DM). Ne každému musí být všechny výše zmíněné výrazy naprosto jasné, proto se je nyní stručně popíši. „Dimenze“ jsou tabulky, ve kterých nalezneme data jenž se příliš často nemění např. Jméno, číslo účtu, rodné číslo, IČO apod. Tyto tabulky jsou samozřejmě historizovány a updatovány v případě jakékoli změny. Změny v nich však, jak jsem již zmiňoval, probíhají velmi zřídka. Naopak, „Faktovky“ jsou tabulky, kde se každý den něco mění. Jsou to tabulky, které například zaznamenávají denní stavy na účtech klientů banky. Tyto tabulky se samozřejmě také historizují a jsou daleko větší než-li „Dimenzové“ tabulky. Pro představu, jedna „Dimenzová“ tabulka má průměrně kolem 1 GB a „Faktovka“ např. 20 GB. Velikost „Faktovek“ může být samozřejmě i daleko větší a tak se „Faktovky“ rozdělují do partitions4, což nám umožňuje Oracle.
2
Tyto transformace probíhají na úrovni programu Informatica. Vysvětlení je dále v kapitole
Informatica. 3
Pojem „Datamart“ bude vysvětlen dále v této kapitole.
4
Viz. kapitola o databázi Oracle
-9-
„Dimenze“ a „Faktovky“ jsou již tabulky, které mohou využívat pracovníci banky. „Faktovky“ obsahují cizí klíče, kterými se mohou propojit s „Dimenzemi“ a díky tomu nám vlastně „Faktovky“ podávají detailní report o datech jenž obsahují „Dimenze“. Například máme „Dimenzi“ klientů banky a „Faktovku“ jejich účtů. V „Dimenzi“ můžeme získat osobní data klientů (statická – rodné číslo, jméno, IČO, …atd) a v „Faktovce“ se pak můžeme podívat na denní stavy účtu (dynamická data) daného klienta. Když mají uživatelé speciální požadavky na další transformace nad již hotovými „Dimenzemi“, „Faktovkami“ či „Interfaces“ tak se pro tyto účely vytváří DM. Datamart (DM) je v překladu datový trh. Vychází z dostupnosti dat v datovém skladu, který v sobě soustředí již transformovaná data z source systémů a tak je daleko jednodušší dostat námi potřebná data. Jedná se o jakýsi malý datový sklad postavený na již transformovaných datech hlavního datového skladu. Část, ve které se během vývoje budeme pohybovat je část DS na úrovni mezi STGA a „Interfaces“. Dále pokládám za důležité zmínit, že každý večer probíhá load nových dat do DS RBCZ z všech source systémů a každý z těchto večerních loadu hlídá menší skupina lidí, která má na starost bezproblémový průběh tohoto loadu, popřípadě vyřešení a následné reportování chyb, které během loadu nastaly. Po tomto rychlém seznámení se s DS RBCZ můžeme přikročit k tomu abychom si řekli něco více o dvou hlavních nástrojích jenž zajišťují jeho funkčnost, a to sice Oracle a Informatica.
- 10 -
4.2.2. Oracle Platforma Oracle je velmi spolehlivá a díky svému rozšíření PL-SQL ,což je programovací jazyk na úrovni této databáze podobný jazyku pascal, nabízí mnohem širší možnosti využití než běžné databáze. PL-SQL budeme během naší optimalizace postloadových procesů velmi hojně využívat. Další velikou výhodou platformy Oracle je možnost velké tabulky rozdělovat na libovolný počet partition, což velmi urychluje vykonávání SELECT příkazů např. nad tabulkami o velikosti 7 GB. Na každé partition není problém vytvořit lokální indexy, což vyhledávací příkazy opět urychluje, samozřejmě za předpokladu jsou indexy vytvořeny nad správnými sloupci. Oracle je databáze jako například Microsoft SQL Server, se kterou jsme se všichni na této škole (minimálně v dvou semestrech) setkali a tak nepokládám za důležité zabývat se jejím bližším popisem .
- 11 -
4.2.3. Informatica Informatica je nástroj, který by se dal popsat jako nadstavba nad databázi, která dokáže provádět mnohé transformace s daty, které by se jinak musely složitě programovat v PL-SQL. Tento program má v celku příjemné uživatelské prostředí a dovoluje nejrůznější operace s daty. Nejčastějšími operacemi jsou čištění dat, filtrování, přetypování datových typů, získávání údajů z jiných tabulek na bázi join operací, a další. Rámcově by se dalo říci že se jedná vesměs o operace, které vám nabídne Microsoft Excel, s tím že jsou zde samozřejmě i další operace, jenž v excelu nenajdete a jsou velmi užitečné při práci s daty nad databázovým prostředím. Další velmi využívané vlastnosti tohoto programu jsou například posílání e-mailů či SMS zpráv o úspěchu či neúspěchu naprogramovaných operací, tvoření složitých workflow 5. Nejoceňovanější je na Informatice to, že pro ní není problém připojit se k jakékoli vzdálené databázi a získávat z ní přes ODBC data. Nejpoužívanějšími nástroji Informatiky, jsou PowerDesigner, WorkflowDesigner a WorkflowMonitor. Tyto Tři nástroje budeme také používat při řešení našeho problému.
4.2.3.1. Mapping designer Tímto nástrojem programu Informatica začínám záměrně, protože je v hierarchii všech 3 komponent na nejnižším místě. To však neznamená, že je méně užitečný než-li ostatní 2 komponenty. Opak je pravdou, nejdůležitější věci se odehrávají právě na úrovni této komponenty. PowerDesigner nám umožňuje tvorbu tzv. mappingů, kterými můžeme docílit nejrůznějších transformací dat z source systémů do našeho datového skladu. Když je zapotřebí vytvořit nové tabulky obsahující upravená data z source systémů nebo se nám dostane zakázka, která požaduje vytvoření nové tabulky, upravení stávající tabulky, či vytvoření DM, téměř vždy je zapotřebí upravovat mappingy či vytvářet nové v tomto nástroji.
5
Tento a další termíny z prostředí programu Informatica budou vysvětleny podkapitolách této
kapitoly.
- 12 -
Mapping se vždy skládá z několika ‘krabic‘ (box) . Každá „krabice“ má určitou funkcionalitu. Níže vám nabídnu přehled jednotlivých „krabic“, jenž budeme pro potřeby této práce potřebovat. Nebudu zacházet do zbytečných detailů. Mým cílem je pouze to, aby měl čtenář představu o tom co se právě děje. Nechci čtenáře zatěžovat zbytečnými detaily, které by pouze mátly. Typy „krabic“ a jejich funkcionalita: •
SOURCE o Jedná se o zástupce tabulky, který ukazuje strukturu a datové typy všech sloupců tabulky.
•
SOURCE QUALIFIER o Tato entita se objeví současně s vytvořením source. Vybere všechny sloupce z dané tabulky a všechny data, která tabulka obsahuje. Nabízí možnost tento výběr značně omezit tím, že jí vepíšete do záložky ‘SQLOVERRIDE‘ SQL kód, kterým své požadavky na vybraná data upřesníte.
•
TARGET o Jedná se o zástupce cílové tabulky se všemi sloupci, které cílová tabulka obsahuje.
•
TRANSFORMATION o V této krabici se děje nejvíce transformací. Čistí se zde data, provádí výpočty, můžete zde zavádět proměnné, určovat která data jsou pouze vstupní a která výstupní, můžete zde přetypovávat jednotlivé proměnné. Výpočetní operace nabízejí v podstatě to samé co Microsoft Excel, avšak jsou zde samozřejmě i další jiné, pokročilejší fce.
- 13 -
•
LOOKUP o
Funkcionalita této krabice spočívá v tom, že je schopna získat data z jiných tabulek a přidat je do krabice TRANSFORMATION na základě join operací. Přidává se zde podmínka která je v SQL známá např. jako from xxx join yyy on (x.a = y.b)
•
FILTER o Tato krabice na základě zadaných údajů (popř. podmínek) pouští či nepouští příslušná data k dalšímu zpracování.
- 14 -
•
UPDATE STRATEGY o Tuto krabici jsem si nechal nakonec, protože bez ní se mapping obejde, avšak všichni, kdo mappingy u nás v RB tvoří, tuto krabici používají už jen z hlediska přehlednosti. Jediné co se v této krabici odehrává je to, zda jí zadáte ,aby byla data do TARGETU insertována či updatována.
Pro názornost níže nabízím ukázku složitějšího mappingu, který obsahuje všechny výše zmíněné ‘krabice’.
Source
SourceQalifier
LookUp
Transformation UpdateStrategy
- 15 -
Target
4.2.3.2. WorkflowDesigner Tento nástroj programu Informatica skýtá možnost tvoření složitých „Workflow“, jenž jsou složena z jednotlivých „Sessions“, „Commandů“ apod. V následujících odstavcích vysvětlím výše zmíněné termíny. Začnu s komponentou nazývanou „Session“. „Session“ je entita, ke které je přiřazen pouze jeden mapping. „Session“ je s tímto mappingem spojena a v případě, kdy na ní v „Workflow“ přijde řada, spustí daný „Mapping“, který už vykoná své transformace. V „Session“ se v základu nastavují 3 hlavní věci. Zaprvé se zde nastavuje, co se má stát, když se v průběhu vykonávání „Mappingu“ se kterým je „Session“ spojena, stane chyba. V zásadě mohou nastat dvě možnosti, a to že „Session“ nebude brát na error ohledy a spustí následující „Session“, a nebo se na tomto erroru zastaví a zastaví tím celé „Workflow“ (dále jen WF). Druhou věcí, která se v „Session“ nastavuje jsou connections. Tato část je velmi důležitá, protože se zde nastavují cesty k zdrojovým a cílovým tabulkám které jsou obsaženy v mappingu jenž je k dané session připojen. A konečně zatřetí se zde vyplňuje způsob, kterým se bude tabulka plnit. Jsou zde v nabídce dvě možnosti ‘NORMAL’ a ‘BULK’ . Bohužel druhá z možností způsobuje v součinnosti s platformou Oracle chyby, takže jediný způsob plnění který je pro nás použitelný je ‘NORMAL’. Pro zvědavé, způsob plnění ‘BULK’ by měl být výkonnější a rychlejší, ale jak jsem zmiňoval s platformou Oracle působí problémy. To by bylo asi tak vše co je důležité znát o „Sessions“. Teď se blíže podíváme na komponentu „COMMAND“. Jedná se o komponentu, která dovoluje vykonávat příkazy v příkazovém řádku tzn. zašlete jí parametry a „Command“ se vykoná. Tato komponenta se používá například k zasílání e-mailů6 , SMS zpráv při errorech, či succeedování jednotlivých „Sessions“ a nebo celých WF. Další velmi hojné využití „Commandů“ spočívá v vytváření speciálních souborů tzv. FLAGů7 (*.flag), které značí ukončení jistého WF. Na takovýto flag pak čeká jiné WF. Jakmile se flag objeví na určitém místě, tak se spustí WF, jenž na tento flag na daném místě čekalo.
- 16 -
Tyto dvě komponenty WorkflowDesigneru jsou asi nejpoužívanější a sdružují se do velikých celků, které se nazývají „WORKFLOW“. Takovéto WF v sobě sdružují spoustu „Sessions“ a „Commandů“, které jsou na sebe navázány a často obsahují například podmínky na pokračování pouze v případě, že „Session“ před nimi má např. status succeeded apod. Samozřejmostí je komponenta START, která , jak již její název napovídá, startuje celé WF. Tato komponenta se vkládá sama automaticky při vytvoření nového WF. Níže můžete vidět ukázku jednoho z mnoha WF, která zajišťují přísun dat do našeho datového skladu.
Start
Command
Session
Worklet
6
Zasílání e-mailů probíhá přes SMTPsender.exe
7
‘prázdný’ soubor, jehož funkce spočívá pouze v tom, že označuje ukončení určité operace
- 17 -
Kromě výše zmiňovaných a vysvětlovaných komponent, můžeme v tomto WF vidět ještě jednu velmi využívanou komponentu, která se jmenuje WORKLET. Worklet je komponenta jenž umožňuje skrýt do sebe další WF, můžeme jej nazývat jako podworkflow, které je většinou jen jakýsi řetěz tvořený z mnoha „Sessions“. Worklet se využívá z jednoho praktického důvodu a to sice proto, aby byla WF přehledná. Worklet Interfaces_MIX ,který je znázorněn na obrázku výše, v sobě skrývá cca. 30 „Sessions“, které plní jednotlivé „Interfaces“ spadající pod systém MIX viz. obrázek níže.
- 18 -
4.2.3.3. Worfkflow Monitor Jako poslední vám představím třetí mnou zmiňovaný nástroj programu Informatica a to sice Workflow Monitor (dále WFM). Tento nástroj se využívá pro aktivní sledování právě spuštěných WF. Je zde možné sledovat jednotlivé komponenty, popřípadě je disablovat, stopnout, nebo například restartovat. Samozřejmostí je, že když nějaká session zfailuje tak máte se možnost podívat na log událostí a zjistit jaká chyba zavinila pád dané „Session“.
WFM má podobu Gantova diagramu, tudíž je zde krásně graficky znázorněno jak co na sebe navazuje a čas, po který jaká „Session“ či „Command“ běžely viz. screenshot výše.
- 19 -
5. Vymezení problému Nyní se konečně dostáváme k jádru věci. V této práci bych se chtěl zabývat jednou ze zakázek na které jsem v RBCZ pracoval. Jedná se o optimalizaci procesů8, které jsou nedílně spojeny s vývojem nových „Interfaces“ a jejich nasazování do DS RBCZ. Optimalizace, kterou budeme v této práci rozebírat se týká vývoje procedury, jenž má za úkol nahradit složité a čas utrácející procesy, které jsou vždy nedílnou součástí nasazení nového „Interface“ do DS. Hlavním úkolem této procedury je kontrolní přepočet řádků jednotlivých tabulek, které již prošly přes STGA a preload counting DS a jsou již po určitém stavu zpracování. Kontrolní přepočet řádků je ve většině WF finální proces, po kterém je tabulka („Interface“) schopna provozu v DS (pakli-že počet řádků na vstupu a výstupu je shodný, ovšem tento constraint není momentálně v DS RBCZ zapnut, protože existuje v DS několik tabulek, u kterých tento stav nenastane). Procedura musí být schopna po zadání vstupních údajů pracovat pro jednotlivé systémy (i pro 2 a více paralelně) s tím že bude mít dva druhy vstupů, protože se zde vyskytuje jeden source systém, který se od ostatních liší svou větší paralelizací. Z tohoto důvodu bude vždy jeden ze vstupů hodnota ‘NULL’ a druhý ponese název source systému (např. ‘APS‘, nebo ‘MIX‘ a nebo ‘MID_144_199‘) pro který mají být zpracovány řádky na výstupu. Co se výstupů z procedury týče, jedná se o err_check9, err_msg10, err_job_id11.
Tyto výstupy jsou požadovány jak z důvodu historizace chyb, tak
z důvodu urychlení zjišťování příčin chyb pro večerní službu, která dohlíží přes VPN na běh večerního loadu.
8
Tyto procesy budou detailněji popsány v kapitole o vytvoření interface
9
Číslo 0 či 1. 1 = chyba a 0 = vše je v pořádku
10
VARCHAR2 -> Chybová hláška = číselná hláška Oracle + popis chyby
11
JOB_ID loadu ve kterém se chyba stala. Logováno v tabulce ETL_ERROR_MESSAGES
- 20 -
Samozřejmostí je ochrana proti nesprávnému zadání údajů, či neexistujícím systémům. Proto musí být procedura vybavena dostatečným množstvím ‘EXCEPTIONS’12,
podmínek a check kurzorů, které tyto stavy dostatečně ošetří,
abychom se vyhnuli zbytečným kolizím při běhu loadu resp. postloadu. V případě chyby by se pak údaje z ‘EXCEPTIONS’ měly plnit do výše zmíněných outputů procedury stl_postload_counting a ty by následně měly být poslány do tabulky ETL_ERROR_MESSAGES.
12
Vyjímky při kterých se do tabulky ETL_ERROR_MESSAGES (logovací tabulka) zapíše
záznam o výskytu chyby a jejím charakteru. Zapisování chyb do výše zmíněné tabulky se děje již na úrovni programu Informatica.
- 21 -
6. Co vše ještě potřebujeme vědět: Jak název této kapitoly napovídá, není to ještě zdaleka vše co potřebujeme vědět k tomu, abychom mohli začít tvořit proceduru, která vyřeší výše zmíněný problém. Již jste se dozvěděli, že optimalizace se bude týkat nasazování a funkčnosti „Interfaces“. Co k tomuto potřebujeme vědět? Budu vám muset ukázat, jak vlastně probíhá ta část loadu, ve které se budeme pohybovat při tvoření naší procedury. Také vám budu muset vysvětlit, jak se vlastně nový „Interface“ tvoří, aby jste si byli schopni představit, jak hodně naše nová procedura pomůže urychlit nasazování nových „Interfaces“ a běh samotného postloadu. Přejděme teď k prvnímu zmiňovanému bodu a vysvětleme si jej.
6.1. Průběh loadu Interfaces Ještě než začnu popisovat průběh vlastního loadu „Interfaces“ je tu ještě několik maličkostí, které je nutné znát pro to, aby vám výklad průběhu loadu byl zcela jasný.
6.1.1. Způsob pojmenováni Interfaces Jak již víte z kapitoly Datový sklad tak jsou „Interfaces“ zdrojem pro plnění faktovek a dimenzí v DS RBCZ. Všechny „Interfaces“ podléhají určité jmenné konvenci. To jakým způsobem jsou jednotlivé „Interfaces“ pojmenovávány vám ukáži na následujících ukázkových jménech: •
‘S091_IBL_ IBLALPINS ‘ o S .................. značí, že je tabulka ještě v STGA o 091............... pořadové číslo tabulky o IBL ............... je zkratka zdrojového systému o IBLALPINS .. je již vlastní jméno tabulky udávající k čemu je tabulka vlastně využívána
- 22 -
o Takovýto název nesou tabulky při zpracování v loadu „Interfaces“ dokud všechny tabulky z daného systému (zde ‘IBL’) tímto zpracováním neprojdou.
•
‘E091_IBL_ IBLALPINS’. o E .................. značí že tabulka je již zpracována a je možno z ní plnit na ní závislé tabulky (faktovky či dimenze)
6.1.2. STL_CONTROL_RECORDS resp. ETL_CONTROL_RECORDS Tyto dvě tabulky, které jsou v názvu této podkapitoly, jsou vlastně jedna a tatáž tabulka. Jedná se o to, že při začátku loadu „Interfaces“ nese tabulka jméno s předponou STL_* a po loadu „Interfaces“, když je již vše hotovo, se tabulka pouze přejmenovává (předpona STL_* se zamění za ETL_*) ,ale struktura zůstává stále stejná a data obsažená v tabulce se také nemění. Toto vše vysvětluji ,protože za pomoci tabulky STL_CONTROL_RECORDS se řídí load „Interfaces“. Tato tabulka obsahuje jména všech tabulek které mají být zpracovány, a další potřebné údaje, které slouží například i noční službě, která kontroluje průběh večerního loadu (nejen loadu „Interfaces“…). Pro vaši představu máte níže k dispozici ukázku tabulky STL_CONTROL_RECORDS, ke které samozřejmě dodám i vysvětlení co který sloupec znamená.
Co se popisu této tabulky týče, budu ji vysvětlovat po jednotlivých sloupcích. • JOB_ID:
Jedná se o číslo loadu, toto číslo je každý den jiné a pro každý load jedinečné.
- 23 -
• EXTRACT_NAME:
Zde jde o jméno tabulky s předponou E* neboli jméno které bude mít tabulka poté co projde loadem.
• VALIDITY_DATE:
Datum, které je aktuální pro daný load.
• CREATED_DATETIME: Datum vytvoření záznamu o tabulce do tabulky STL_CONTROL_RECORDS • NRECS_EXPECTED:
Počet řádků v source systému před zpracováním
• NRECS_ACTUAL:
Počet řádků po zpracování neboli po různých transformacích
• STATUS:
Aktuální status dané tabulky. Díky statusu poznáme v jaké fázi se tabulka právě nachází (možné statusy: S, D, T, E)
6.1.3. Jak probíhá load Interfaces Vzhledem k tomu, že jsme si již vysvětlili důležité termíny, funkce jednotlivých objektů, apod. můžeme se nyní věnovat vysvětlení průběhu loadu „Interfaces“, které budeme potřebovat k tomu ,abychom mohli vyvíjet optimalizační proceduru. Load „Interfaces“ začíná něco kolem 22:30 hod. tím, že se vytvoří tabulka STL_CONTROL_RECORDS a hned poté co se tato tabulka vytvoří se začnou na základě preloadu vytvářet Skové tabulky jednotlivých „Interfaces“ pro všechny systémy, aby se data ze source systémů měla do čeho plnit. V preloadu každého „Interface“ se děje to, že se napočítá počet řádků tabulky v daném source systému pro určitý „Interface“ a zapíše se spolu s názvem tabulky do STL_CONTROL_RECORDS (NRECS_EXPECTED) a přidělí se mu status ‘S’. Status ‘S’ se danému „Interface“ přiděluje proto, aby bylo jasné, že daný „Interface“ již prošel preloadem a je připraven na hlavní load… neboli naplnění Skové tabulky.
- 24 -
Jakmile tabulky projdou preloadem, dostávají se do druhé fáze a to sice do hlavního loadu. Zde se plní Skové tabulky daty z source systémů. Neplní se samozřejmě 1:1, ale data ze source systémů prochází přes všelijaké možné transformace, čištění apod. Jakmile tabulky projdou hlavním loadem, jsou již naplněny daty a v tabulce STL_CONTROL_RECORD je jim přidělen status ‘T’, který znamená že tabulky (resp. jejich data) již prošly transformacemi. Třetí fáze loadu „Interfaces“ spočívá v tom, že tabulky projdou postloadem, což je jakási kontrola. Tato kontrola spočívá v tom, že se jim napočítá počet řádků na výstupu (postload_counting) a výsledek tohoto přepočtu se zaznamenává opět do tabulky STL_CONTROL_RECORDS (NRECS_ACTUAL). Jakmile „Interfaces“ mají napočítány výstupní počet řádků, přiděluje se jim status ‘E’ a jsou připraveny na poslední fázi. Nyní máme tabulky již naplněné a mají napočítány jak řádky na vstupu, tak i na výstupu. V poslední fázi se děje v podstatě pouze to, že se tabulky „Interfaces“ přejmenovávají z Skových na Ečkové. Protože se na vlastním začátku loadu „Interfaces“ nedropovali Ečkové tabulky a při přejmenovávání Skových na Ečkové by docházelo k kolizím (vyskytovaly by se tabulky s stejnými jmény), tak se staré Ečkové tabulky dropují a hned potom může proběhnout přejmenování Skových na Ečkové. To samé se děje i s tabulkou STL_CONTROL_RECORDS, které se změní jméno na ETL_CONTROL_RECORDS. Jakmile toto přejmenování proběhne mohou se již „Interfaces“ využívat pro další část loadu, která již však nemá s loadem „Interfaces“ nic společného. Jedná se totiž již o plnění tabulek zcela jiných a to sice faktovek a dimenzí.
- 25 -
6.2. Vytvoření nového Interface Tato kapitola je poslední, ve které budu vysvětlovat něco k funkčnosti DS RBCZ. Považuji ovšem za nutné vám ještě vysvětlit tvorbu nového „Interface“, resp. nové tabulky, která bude například plnit nějaká nová pole v faktovce či dimenzi. Jak se při vývoji procedury jistě sami přesvědčíte, všechny informace zde uvedené budeme potřebovat. Pakli-že se chystáme nasadit nový „Interface“, musíme postupovat následovně. Nejdříve si musíme zjistit, kde se vlastně zdroj dat, která chceme do „Interface“ dostat, nachází. To znamená nalezení zdroje, což může být flatfile, či tabulka v cizí databázi, ze které budeme data downloadovat. Tento proces tedy vyžaduje znalost příslušné cesty k datum na síťovém disku (případ flatfile), či v případě databáze znalost ODBC connection + zajištění oprávnění přístupu do dané databáze. Dalším krokem k založení nového „Interface“ je vytvoření příslušných tabulek, do kterých se budou data získaná z flatfile či jiné databáze nahrávat. Je dobré vytvořit si Skovou tabulku pro testovací účely a na živém skladu Ečkovou tabulku, která tam bude připravena pro dobu nasazení „Interface“. Dále je vhodné založit si testovací tabulku STL_CONTROL_RECORDS pro účely testování celého loadu našeho nového „Interface“. Vytvoření mapingu pro preload counting. Jedná se v podstatě pouze o to, že do již existujícího mapingu preload countingu daného systému, pro který děláme nový „Interface“ přidáme další větev, která bude obsahovat údaje o našem novém „Interface“. Námi nově přidaná větev založí v tabulce STL_CONTROL_RECORDS záznam pro naší tabulku. Tento záznam ponese jméno našeho „Interface“ se statusem S (stage), jenž označuje, že je tabulka připravena na load dat + napočítá počet řádků, při vstupu dat do naší databáze, nebo-li před veškerými transformacemi, které se dějí v hlavním loadu.
- 26 -
Hlavní load našeho „Interface“. Pro tuto činnost musíme vytvořit maping obsahující dvě větve. První větev mapingu bude plnit data ze source systému do naší „Interfacové“ tabulky s případnými transformacemi, jako například: čištění dat od mezer, úprava malých a velikých písmen, doplňování ‘NULL’ hodnot na hodnoty ’#NA’
či v případě datového typu DECIMAL na hodnotu 0. Tato větev také přidá do
našeho „Interface“ hodnotu JOB_ID, což je aktuální číslo loadu. Každý den je pro load nové JOB_ID. Druhá větev mapingu hlavního loadu našeho „Interface“ se stará o to, aby se do tabulky STL_CONTROL_RECORDS udělal update statusu naší „Interfacová“ tabulky. Protože je teď naplněna, může jí tato větev změnit status z ‘S‘ (stage) na ‘T’ (transformed). Nyní se dostáváme ke konečné fázi vytvoření nového „Interface“. Jako poslední musíme přidat větev do opět již existujícího mapingu postload countingu. Stejně jako u první části preload counting, tato část postload countingu dělá dvě věci. Nejdříve napočítá počet řádků „Interfacová“ tabulky po transformacích a pak jí přidělí v tabulce STL_CONTROL_RECORDS status E (executed) a vloží počet napočítaných řádků na výstupu.
- 27 -
Níže můžete vidět ukázku mapingu postload countingu (pro systém Midas MID). Zde je pro každý „Interface“ Midasu jedna větev, která zajišťuje výše zmíněné funkce a podobně vypadá i maping preload countingu s tím rozdílem, že pro každý „Interface“ je zde samozřejmě jiný zdroj.
- 28 -
7. Vývoj Procedury stl_postload_counting Nyní se konečně dostáváme k vývoji naší procedury, která by měla urychlit vývoj nových „Interface“ a průběh celého postloadu. Na konci našeho vývoje by měl mít každý systém vlastní postload (větší paralelizace postloadových procesů bude mít za důsledek urychlení loadu) plus při vývoji jakéhokoliv nového „Interface“ by odpadlo přidávání nové větve do mapingu postloadu, protože o postload se bude starat naše procedura, jenž se bude nacházet v novém mapingu, který se již nebude při vývoji nového „Interface“ muset updatovat o nový řádek.
7.1. Tabulky V této kapitole si řekneme, které tabulky při vývoji procedury budeme potřebovat a co do nich budeme zapisovat.
7.1.1. STL_CONTROL_RECORDS Tuto tabulku budeme využívat jako naváděcí pro kurzory, jenž podle záznamů v této tabulce budou postupně vybírat jednotlivé tabulky určitého systému a počítat pro ně řádky na výstupu. Do této tabulky se poté zapíše stav řádků na výstupu do sloupce NRECS_ACTUAL (neboli po transformacích) a dále se zde updatuje sloupec STATUS. Status tabulky pro kterou již byly napočítány řádky na výstupu se změní z ‘T’ na ‘E’.
7.1.2. ETL_ERROR_MESSAGES Do této tabulky, která má pouze dva sloupce JOB_ID a ERROR_MESSAGE, se zapisují chyby, které nastaly během loadu, nejen loadu i “Interfaces“. Naše procedura bude mít výstupní proměnné, které ponesou, v případě chyby informace, jenž se zapíší do výše zmiňovaných řádků. Jedná se o JOB_ID aktuálního loadu, a ERROR_MESSAGE, která se bude skládat z kódového označení chyby Oracle a námi nadefinovaného textu pro danou chybu. Tyto výstupní proměnné pak budou pomocí nového postloadového mapingu, jehož hlavní součástí naše procedura bude, zapsány do tabulky ETL_ERROR_MESSAGES. - 29 -
7.2. Proměnné Bez proměnných by to nešlo, proto se pojďme podívat, jaké že to vlastně budeme potřebovat datové typy v jednotlivých proměnných a na co bude ta či ona proměnná sloužit.
7.2.1. Vstupní proměnné •
JOB_ID IN NUMBER
o JOB_ID je jedinečné číslo, které je pro každý load jiné. Nachází se v tabulce STL_CONTROL_RECORDS a protože jej budeme potřebovat ve výstupní proměnné, musí být i ve vstupní proměnné. •
SYSTEM_ID IN VARCHAR2
o Do této proměnné se zadává tříznaková zkratka systému, pro který chceme napočítat výstupní řádky kromě systému Midas (‘MID‘ – ten je totiž rozdělen do 3 subsystémů a tak se udává v jiném formátu) např. ‘IBL’ •
PAR_SUBSYSTEM IN VARCHAR2
o Zde se zadává 11ti charová kombinace znaků pro rozmezí MIDASového systému např. zadáním ‘MID_100_116’ by nám procedura měla napočítat midasové tabulky s pořadovými čísly 100 až 116.
7.2.2. Výstupní proměnné •
ERR_JOB_ID OUT NUMBER
o Odchozí JOB_ID, které vychází z následující definice ERR_JOB_ID:=JOB_ID;
- 30 -
o Toto je zde proto, aby se při případné chybě zaznamenalo chybové JOB_ID do tabulky ETL_ERROR_MESSAGES. Těchto logů se využívá při kontrolách noční služby či při různých haváriích, kdy je zapotřebí zjistit co se dělo a hlavně kdy se to dělo, protože podle JOB_ID lze také dohledat VALIDITY_DATE loadu. •
ERR_CHECK OUT NUMBER
o ERR_CHECK je zde proto, že pokud se stane chyba, nabývá ERR_CHECK
hodnoty ERR_CHECK:=1; a v případě že je vše
v pořádku, tak ERR_CHECK:=0; o Tato hodnota (0 nebo 1) bude pak dále zpracována v programu Informatica, kde se na ní použije následující podmínka IIF(IN_ERR_CHECK=0,’OK’,ABORT(IN_ERR_MSG)) •
ERR_MSG OUT VARCHAR2
o Tato proměnná obsahuje chybovou hlášku pro danou EXCEPTION
Teď již víme jaké budeme potřebovat proměnné a můžeme si napsat hlavičku naší procedury viz. níže. CREATE OR REPLACE PROCEDURE STL_POSTLOAD_INDEXES (JOB_ID IN NUMBER , SYSTEM_ID IN VARCHAR2, PAR_SUBSYSTEM IN VARCHAR2, ERR_JOB_ID OUT NUMBER, err_check OUT NUMBER, err_msg OUT VARCHAR2)
Jsou zde však také ještě jiné proměnné, které se v hlavičce procedury nevyskytují, ale jsou velmi potřebné.
7.2.3. Ostatní proměnné •
Like_mask VARCHAR2(50)
o Využívá se později jako proměnná do cursoru stl_cur. Do této proměnné se nalije vizáž kódu, který následuje za LIKE podmínkou v SQL pro daný cursor.
- 31 -
•
If_bottom NUMBER
a if_top NUMBER
o Tyto proměnné se plní v soustavě Ifů, kde se zjišťuje zda jde o MIDASový systém (např. PAR_SUBSYSTEM:=‘MID_100_116‘), či o normální systém bez omezení (SYSTEM_ID:=’IBL’). •
UZIVATEL VARCHAR2(50)
o Bude nastaven na INTUSER , protože se tento uživatel (schéma) používá pro plnění „Interface“ a všeho okolo co s „Interface“ má co do činění. o Jeho použití je pak v dynamickém SQL v EXECUTE IMMEDIATE
Vysvětlili jsme si ostatní proměnné a zbývají nám, co se proměnných týče, už jen proměnné typu EXCEPTION. Tyto proměnné zavádíme hlavně proto, aby naše procedura mohla hlásit chybové hlášky.
7.2.4. Proměnné pro EXCEPTION •
V_ERR_NUM VARCHAR2(35)
a V_ERR_MSG VARCHAR2(300)
o Tyto proměnné jsou zde jen kvůli reportu do error logu. •
WRONG_SYSTEM EXCEPTION
o UPPER(SYSTEM_ID)||' THIS SYSTEM_ID IS NOT VALID'; o Pakli-že se zadá špatný (neexistující SYSTEM_ID) tak procedura skončí na této EXCEPTION •
MIDAS_ERR EXCEPTION
o
‘MIDAS ERROR: WRONG PAR_SUBSYSTEM '||PAR_SUBSYSTEM||' OR YOUR IDENTIFIER MID_'||mid_id_number_bottom||'_'||mid_id_number_top|| ' IS NOT IN MIDAS RANGE '||if_mid_minimum||' '||if_mid_maximum;
o Když je zadán špatný PAR_SUBSYSTEM nebo špatné rozmezí MIDASového systému, tak zde procedura skončí •
TWO_VALUES_ERR EXCEPTION
o 'YOU HAVE (OR HAVE NOT) INSERTED 2 VALUES: SYSTEM_ID and PAR_SUBSYSTEM... ONLY ONE IS ALLOWED';
- 32 -
o Na této chybě skončí procedura v případě, že se do vstupních proměnných nenaplní žádné znaky, či pouze hodnoty NULL, a nebo v případě, že se do vstupních proměnných naplnily obě hodnoty jak SYSTEM_ID , tak i PAR_SUBSYSTEM což je zakázáno, neboť aby se procedura vykonala správně musí být vždy jedna z hodnot NULL a druhá existující SYSTEM_ID či PAR_SUBSYSTEM.
7.3. Cursory Máme-li napsanou hlavičku procedury s hlavními proměnnými, můžeme se pustit do jejího dalšího sestavování. Další co nás zajímá jsou cursory. Nastíním jaké budeme potřebovat a pak vás blíže seznámím s jejich zpracováním v kódu. Jako první cursor budeme potřebovat takový cursor, který nám vybere z STL_CONTROL_RECORDS záznamy pro všechny tabulky jenž mají STATUS=’T’ a nacházejí se v systému, který jsme zadali (neboli odpovídají proměnným SYSTEM_ID nebo PAR_SUBSYSTEM). Protože máme dva druhy systémů, bude muset být takový cursor trošku složitější a bude muset obsahovat i proměnné stejně jako dva následující. Další dva cursory budou takové, jejichž hlavní funkcí bude ověřování toho, zda námi zadané hodnoty pro PAR_SUBSYSTEM nebo SYSTEM_ID jsou validní, nebo-li jestli námi zadané hodnoty vůbec existují. Pojďme se na ně podívat blíže.
7.3.1. CURSOR stl_cur •
CURSOR stl_cur(mask VARCHAR2, bottom NUMBER, top NUMBER)
o Tento cursor, jak jsem již avizoval výše, vyhledává všechny tabulky podle zadaného SYSTEM_ID nebo PAR_SUBSYSTEM s STATUSEM=’T’ o Jak v hlavičce cursoru vidíte, má tři proměnné. Hned vysvětlím jejich význam
- 33 -
mask
– tato je úzce spojená s proměnnou like_mask, která
se vyplní podle zadaného SYSTEM_ID nebo PAR_SUBSYSTEM na příslušnou hodnotu. V případě MIDASu to je hodnota E___MID_%
a v případě jiných systémů to je např. hodnota
E___IBL_%
bottom
a top – tyto dvě proměnné samozřejmě navazují
na předešlou. Jedná se o to, že tyto proměnné vyjadřují v SQL kódu rozmezí pro jaké má být cursor zobrazen. Nebo-li pro zadání PAR_SUBSYSTEM:=’MID_100_116’; se budou proměnné rovnat bottom:=100; a top:=116; a pro jakékoli SYSTEM_ID budou proměnné vypadat následovně bottom:=0;
a top:=10000;
o Mimo proměnných v hlavičce tohoto cursoru se v proceduře vyskytují také proměnné, které s kursorem souvisí, nejsou však zapsány přímo v jeho hlavičce
stl_zaznam stl_cur%ROWTYPE
– je proměnná pro tento
cursor. %ROWTYPE znamená, že má tato proměnná tvar jednoho řádku tabulky vybrané tímto cursorem i s všemi datovými typy které v daném řádku (sloupcích) jsou.
7.3.2. CURSOR systemsCheck_cur •
CURSOR systemsCheck_cur(sys_id VARCHAR2)13
o V případě tohoto cursoru se jedná o jeden ze dvou check cursorů, které se starají o to, aby se do procedury nedostala neexistující data. Tento cursor má pouze jednu proměnnou a to sice sys_id, kterou blíže popíšu. Dále je s tímto kursorem úzce spojena další proměnná systemsCheck_var NUMBER:=0; jejíž význam samozřejmě vysvětlím také níže.
13
Tento check cursor jsem vymyslel na základě nápadu uveřejněného na internetu. Zdroj č. 2
v seznamu zdrojů na konci této práce.
- 34 -
Sys_id
– tato proměnná se při otevření cursoru naplní
obsahem proměnné SYSTEM_ID a vyhledá, zda daný systém opravdu existuje
systemsCheck_var
- je proměnná, která je s tímto
kursorem úzce spojena a to sice tak, že SQL kód tohoto cursoru vrací hodnotu 1, pakli-že najde v tabulce STL_CONTROL_RECORDS záznam pro daný SYSTEM_ID . Když SQL kód žádný záznam nenajde, tak zůstane této proměnné defaultně přiřazená hodnota 0. Tyto dvě různé hodnoty, jimiž se může proměnná systemsCheck_var naplnit, jsou později využívané v řetězci IF podmínek o kterých se samozřejmě budu později také zmiňovat.
7.3.3. CURSOR midCheck_cur •
CURSOR midCheck_cur(mid_id_bottom NUMBER, mid_id_top NUMBER, mid_bottom NUMBER, mid_top NUMBER)
o Tento check cursor, podobně jako jeho ‘kolega’ výše, má také za úkol kontrolovat, zda zkratka systému vůbec existuje. V tomto případě jde o obsah proměnné PAR_SUBSYSTEM resp. o to, zda dané rozmezí tohoto systému existuje. o Jak jste si jistě všimli tak při otvírání tohoto cursoru budeme potřebovat naplnit určité proměnné do jeho hlavičky. Pojďme si tedy vysvětlit co se do které proměnné bude vkládat14.
14
mid_id_bottom := mid_id_number_bottom
mid_id_top := mid_id_number_top
mid_bottom := if_mid_minimum
mid_top := if_mid_maximum
Pro obsahy daných proměnných se prosím podívejte do přílohy č.1 což je kód procedury
stl_postload_counting_v0.1
- 35 -
o Samozřejmě i tento cursor má své pomocné proměnné, které jsme výše vkládali při otevření cursoru do jeho hlavičky pomocné proměnné. Aby vám výpis výše uvedených rovností něco řekl, budeme si muset vysvětlit význam jednotlivých proměnných, které byly pro účely funkčnosti tohoto cursoru vytvořeny.
midCheck_var NUMBER := 0
– využití této proměnné je opět
až později v soustavě IF podmínek, ke kterým se samozřejmě také dostaneme. Tato proměnná funguje naprosto na stejném principu jako proměnná systemsCheck_var.
Když midCheck_var:=0 tak námi
zadaný systém pro MIDAS neexistuje a v případě že midCheck_var:=1
tak existuje.
mid_id_number_bottom NUMBER; NUMBER;
a mid_id_number_top
- tyto dvě proměnné ukazují minimum a
maximum MIDASového systému, který jsme zadali do vstupní proměnné PAR_SUBSYSTEM s tím, že se tyto hodnoty pak porovnávají s následujícími dvěmi proměnnými a na základě těchto porovnání se zjišťuje, zda-li je dané MIDASové rozhranní platné, nebo naopak neexistující.
if_mid_minimum NUMBER;
a if_mid_maximum NUMBER; -
ukazuje minimální a maximální hodnotu čísla v existujících MIDASových systémech. Např. Existuje-li rozmezí E100_MID_* až E200_MID_* tak proměnná if_mid_minimum
bude mít hodnotu 100 a proměnná
if_mid_maximum
bude mít hodnotu 200.
- 36 -
7.4. Soustava IF podmínek a EXECUTE IMMEDIATE Aby nám cursory z předešlé kapitoly k něčemu byly, musíme si vytvořit soustavu IF podmínek, kde výstupy z našich CheckCursorů využijeme a získáme zde obsahy dalších proměnných, jenž potřebujeme pro cursor stl_cur. Tato soustava bude využívat výstupů z CheckCursorů pro kontrolní operace (správně zadaná data nebo EXEPTION) prováděné IF podmínkami a dále se v jednotlivých větvích této soustavy IF podmínek budou plnit potřebné proměnné daty, která budou odpovídat místu (soustavě podmínek) na kterém se nachází. Pro větší přehlednost jsem si připravil v PowerDesigneru nakreslené schéma průběhu procedury, kde jsou dle mého názoru velmi přehledně znázorněny již právě zmíněné IF podmínky resp. jejich soustava.
15
Jak je z obrázku patrné (žluté kosočtverce), budeme potřebovat 4 IF podmínky. Jejich značení bude IF00 až IF03 jak zde v jejich výkladu, tak i v samotném kódu procedury kde se takto zeleně značí komentáře. Komentáře si budeme k podmínkám dělat proto, abychom se pak v jejich množství neztratili. Jsou zde sice pouze 4, ale s jejich druhými větvemi ELSE jsou pak velmi rozsáhlé.
15
Výřez z schématu průběhu procedury stl_postload_counting zpracovaného v programu
PowerDesigner
- 37 -
7.4.1. Naplnění cursorů a EXECUTE IMMEDIATE Před vlastním popisem struktury podmínkové soustavy je však nutné ještě dodat, že proto, aby nám tato sestava fungovala musíme otevřít cursory a ještě před otevřením cursorů naplnit jejich pomocné proměnné, na kterých jsou tyto cursory závislé. U prvního check cursoru systemsCheck_cur je toto naplnění jednoduché. Jediné co musíme udělat je otevřít cursor a hodnotu, kterou vybere SQL kód někam ‘nalít’ a pak samozřejmě cursor zase zavřít viz. kód níže: OPEN systemsCheck_cur(UPPER(SYSTEM_ID)); FETCH systemsCheck_cur INTO systemCheck_var; CLOSE systemsCheck_cur;
U druhého cursoru (midCheck_cur) to je již o něco více složitější operace. Před jeho otevřením se musí získat hodnoty proměnných mid_id_number_bottom a mid_id_number_top
funkcí SUBSTR, která dokáže z stringu vyříznout určitou část a pak
funkcí TO_NUMBER tuto část (která je samozřejmě číslo v datovém typu VARCHAR2) převedeme na číslo. Dalším krokem je pomocí dynamického SQL neboli EXECUTE IMMEDIATE vložit minimální a maximální hodnoty rozmezí systému MIDAS do proměnných if_mid_minimum
a if_mid_maximum. Ukázku kódu pro if_mid_minimum můžete vidět
níže: EXECUTE IMMEDIATE 'SELECT MIN(TO_NUMBER((SUBSTR(extract_name,2,3))))'|| 'MIN FROM (SELECT * FROM '||uzivatel||'.stl_control_records WHERE STATUS!=''X'' AND EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE ''\'')' INTO if_mid_minimum;
Jakmile máme tyto 4 proměnné naplněny můžeme ‘nalít’ potřebná data i do proměnné midCheck_var pomocí cursoru midCheck_cur viz. kód níže: OPEN midCheck_cur
(mid_id_number_bottom, mid_id_number_top, if_mid_minimum, if_mid_maximum); FETCH midCheck_cur INTO midCheck_var; CLOSE midCheck_cur;
- 38 -
Nyní již máme naplněna data pro všechny proměnné jenž potřebujeme pro soustavu našich IF podmínek, která kontroluje správnost zadaných údajů a plní jiné proměnné(like_mask, if_bottom, if_top), které jsou zapotřebí pro cursor stl_cur (resp. pro stl_zaznam s datový typ stl_cur%ROWTYPE;) který se po provedení těchto podmínek používá v LOOP cyklu jenž počítá řádky na výstupu což je náš konečný cíl, ale k tomu se samozřejmě postupně dopracujeme.
7.4.2. Vysvětlení soustavy IF podmínek Jak již bylo zmíněno, tato soustava má 2 účely. Zaprvé se jedná o kontrolní mechanismus, jenž kontroluje správnost zadaných dat a zadruhé se jedná o plnící mechanismus jenž již ověřená data (tzn. data, která prošla podmínkami) zapisuje do proměnných které jsou dále využívány.
7.4.2.1. IF00 V této podmínce se řeší zda jsou v proceduře zadaná vůbec nějaká data. Pakli-že podmínka zjistí, že ani v jedné z vstupních proměnných (SYSTEM_ID, PAR_SUBSYSTEM)
nejsou žádná data, či jsou zde pouze hodnoty NULL, tak spadne na
EXCEPTION TWO_VALUES_ERROR.
7.4.2.2. IF01 Jakmile se proměnné dostanou přes podmínku IF00 je jasné, že nějaká data obsahují. V tuto chvíli začíná kontrolní funkce této IF soustavy naplno, protože data, která prošla přes první podmínku nemusí být zákonitě správná. V této podmínce se rozhoduje jestli se bude kontrolovat obsah proměnné SYSTEM_ID
nebo PAR_SUBSYSTEM. Když je hodnota proměnné SYSTEM_ID:=NULL, tak se
v následující podmínce bude kontrolovat správnost obsahu proměnné PAR_SUBSYSTEM.
A v případě, že SYSTEM_ID je naplněna nějakými daty tak se
samozřejmě začne kontrolovat na jejich správnost v následující podmínce.
7.4.2.3. IF02 Zde už přicházíme ke kontrole zda je hodnota vstupní proměnné PAR_SUBSYSTEM
správná a existující.
- 39 -
V případě, že se rovná midCheck_var:=0; zadaná hodnota není správná a vykoná se EXCEPTION MIDAS_ERR; a v případě že midCheck_var:=1; je hodnota správně zadaná a vyplní se hodnoty příslušných proměnných následujícími hodnotami např. pro PAR_SUBSYSTEM:=’MID_100_116’ by naplnění vypadalo následovně: like_mask :='E___\_' || 'MID' || '\_%' ; if_bottom:= 100; if_top:= 116;
7.4.2.4. IF03 Na závěr zde máme čtvrtou podmínku, která kontroluje správnost zadaných dat do proměnné SYSTEM_ID. V tomto ifu se nám kontroluje, podobně jako v ifu IF02, hodnota proměnné systemCheck_var
u které se také kontroluje zda se rovná nule či jedné. Závěr
z tohoto zjištění je naprosto identický podmínce IF02, avšak následné plnění je zcela jiné viz. příklad níže pro SYSTEM_ID:=’E902_DON_SCON‘; : like_mask :='E___\_' || UPPER(SYSTEM_ID) || '\_%' ; if_bottom:=0; if_top:=10000;
Naplnění je takto vyřešeno z jednoho konkrétního důvodu. Protože cursor stl_cur
musí být schopen pracovat jak pro proměnnou PAR_SUBSYSTEM tak i pro
proměnnou SYSTEM_ID, která nemá omezení rozmezí jako MIDASová proměnná PAR_SUBSYSTEM, SYSTEM_ID,
je pro tuto proměnnou, respektive u selectu pro proměnnou
velmi vysoké rozmezí, které bude platné vždy. Neboli to co se nám
naplnilo do proměnných se pak bude objevovat v selectu cursoru stl_cur v této následující podmínce: FROM (SELECT * FROM stl_control_records WHERE STATUS='T' AND EXTRACT_NAME LIKE mask ESCAPE '\') s WHERE TO_NUMBER(SUBSTR(extract_name,2,3)) BETWEEN bottom AND top;
Neboli po naplnění by to vypadalo konkrétně takto: … AND EXTRACT_NAME LIKE 'E___\_DON\_%' ESCAPE WHERE 902 BETWEEN 0 AND 10000;
- 40 -
'\') s
Samozřejmě v případě, že se v této podmínce vyskytnou nekorektní data, skončnčí procedura na EXCEPTION WRONG_SYSTEM. Tímto ifem naše soustava IF podmínek končí. Máme naplněna korektní data a můžeme se dostat k jádru této procedury.
- 41 -
7.5. LOOP cyklus Jsme na konci vývoje této procedury. Po nadefinování všech potřebných proměnných, kontrole dat, definování EXCEPTION se konečně dostáváme do výpočetní části této procedury. Budeme muset vymyslet, jakým způsobem budeme vlastně počítat řádky na výstupu, a jakým způsobem budeme tento výsledek zapisovat do tabulky STL_CONTROL_RECORDS. Jak jste již jistě z přípravných operací zjistili, budeme zde v LOOP cyklu pomocí dynamického SQL (EXECUTE IMMEDIATE ‘SELECT COUNT(*)’) počítat počet řádků Skových tabulek (které již prošly transformacemi) a pak pomocí cursoru stl_cur a opět dynamického SQL (EXECUTE IMMEDIATE ‘update …’) zapisovat stavy těchto řádků do sloupce cursorového řádku STL_CONTROL_RECORDS.NRECS_ACTUAL plus ještě budeme updatovat sloupec STL_CONTROL_RECORDS.STATUS z hodnoty ‘T’ na hodnotu ‘E’. Po vykonání tohoto cyklu už zbývá jen pár věcí. Nejdříve zavřít cursor stl_cur a pak přidělit hodnoty proměnným err_check a JOB_ID, které jsou, pokud se nevyskytne jakákoli chyba, doteď prázdné viz. jejich naplnění níže: err_check:=0; ERR_JOB_ID:=JOB_ID;
- 42 -
7.6. Průběh procedury Nyní jsme vyvinuli proceduru, kterou jsme potřebovali, můžeme si vysvělit, jak vlastně probíhá její vykonání. Věřím, že na základě průběhu vývoje si jistě dokážete velmi podrobně představit co se uvnitř procedury děje, ale pro jistotu bych to zde v této kapitole několika málo slovy shrnul. V programu informatica se naplní dvě ze tří vstupních proměnných. Plní se JOB_ID,
a pak jedna z dvou následujících SYSTEM_ID či PAR_SUBSYSTEM. Jak jsem již
dříve připomínal, může být naplněna pouze jedna ze dvou posledních a druhá musí mít hodnotu NULL. Jakmile jsou
naplněny vstupní proměnné do
‘krabice’ nazývané
STL_POSTLOAD_COUNTING
(Stored Procedure)
může začít vlastní zavolání
procedury
Informaticou z Oracle. Do
procedury se pošlou
údaje zjištěné Informaticou a
spustí se procedura. Procedura běží a jako první se vykonávají a plní pomocné proměnné check cursorů. Jakmile jsou všechny tyto cursory vykonány a naplněny příslušné pomocné proměnné, dostává se procedura ke kontrolní soustavě if podmínek. V této soustavě se prověří správnost dat a popřípadě procedura zkončí na některé z 6ti EXCEPTIONS. Pakli-že se chyby nevyskytly, tak se zde naplní pomocné proměnné pro cursor stl_cur, který je zapotřebí v LOOP cyklu, jenž představuje podstatu procedury. Před tím než se dostaneme do samotného LOOP cyklu otevře se cursor stl_cur
na základě pomocných proměnných jenž byly naplněny v soustavě IF
podmínek. Nyní se dostáváme konečně k tomu, že procedura počítá řádky. Jakmile se otevře cursor stl_cur může se procedura dostat do LOOP cyklu a začít počítat a plnit (updatovat) řádky v tabulce STL_CONTROL_RECORDS pro konkrétní tabulky daného systému.
- 43 -
Pro lepší představu jak celý průběh procedury vypadá jsem si připravil diagram v Power Designeru s jehož částí (výřezem) jste se již mohli seznámit v kapitole o IF podmínkách.
- 44 -
7.7. Testování procedury Každá procedura, maping, workflow, či jiné věci, které se mají nasadit do živého DS RBCZ musí být před svým nasazením otestovány. I tato procedura prošla testováním. Testuje se standardní funkčnost procedury a pak i chybová funkčnost, neboli se simulují chybové stavy, které mohou nastat. Samozřejmostí testování je i optimalizace (tuning) kódu procedury.
7.7.1. Testování standardní funkčnosti Zde se testovaly 4 základní funkčnosti procedury: •
Když se zadá SYSTÉM_ID tak procedura napočítá počty výstupních řádků pouze pro daný systém
•
Při tomto počítání nechá ostatní záznamy bez povšimnutí a bude pracovat pouze s těmi, které požadujeme
•
Informatica nevrátí chybnou hlášku a WF skončí na stavu succeeded
•
Test na funkčnost PAR_SUBSYSTEM pro MIDAS se stejnými požadavky jako v předešlých třech bodech.
7.7.2. Testování procedury při chybových situacích Zde jsme se pokoušeli nasimulovat různé situace, při kterých by procedura měla hlásit chybu a program Informatica by měl ukončit vykonávání dané session v WF jenž obsahuje proceduru stl_postload_counting. •
Co se stane, když se zadá do vstupní proměnné SYSTEM_ID neexistující zkratka systému?
•
Co se stane, když nebude existovat, do vstupní proměnné naplněný, PAR_SUBSYSTEM?
•
Jak se zachová procedura v případě, když zadám obě dvě hlavní vstupní proměnné (SYSTEM_ID, PAR_SUBSYSTEM)?
•
Jaká bude reakce při nezadání ani jedné ze dvou hlavních proměnných (obě budou mít naplněnu hodnotu NULL)?
•
‘Padá’ session resp. WF při zahlášení chyby?
- 45 -
Toto testování se samozřejmě neobešlo bez chyb, které jsem musel opravovat. Nejdříve jsem musel opravovat chyby základní funkčnosti procedury (ať už na úrovni databáze tak i na úrovni programu Informatica) a když již procedura fungovala přesně podle našich představ, zaslal jsem její zdrojový kód k připomínkování. Samozřejmě během tohoto připomínkování se odhalilo několik ne zásadních, spíše kosmetických chyb v kódu, které jsem samozřejmě rád v rámci optimalizace procedury upravil tak aby byla procedura co nejvýkonnější a co nejčistěji napsána. Není však pochyb o tom, že můj kolega Michal Pukan, který se zabýval 3 roky pouze tuningem a optimalizacemi PL-SQL kódů, by byl schopen napsat tuto proceduru na daleko menší počet řádků než-li já a nejspíše by byla i rychlejší. Zkušenosti jeho formátu se snažím získat tím, že jsem si od něj a jeho kolegů nechal připomínkovat ať již verzi 1.0 procedury stl_postload_counting tak i verzi 1.1 a posléze 1.2, která už má i za úkol kontrolovat počet vstupních řádků na vstupu a na výstupu, což verze 1.0 a ani verze 1.1 ještě v popisu své funkčnosti neměli.
- 46 -
7.8. Nasazení procedury Procedura je otestovaná a tudíž připravená k nasazení do živého skladu. Pojďme se tedy podívat jak budou nyní vypadat všechny postloadové mapingy pro „Interfaces“.
16
Oproti starému postloadovému mappingu, kde se pro každý nový „Interface“ musela přidávat další větev do již existujícího postloadového mapingu, se s novou procedurou a mappingem pro ní vytvořeným tomuto vyhneme.
16
Stará verze mappingu pro postload counting (před vývojem procedury)
- 47 -
17
Jakmile bude pro určitý systém vytvořen postloadový mapping tak se již při vytvoření nového „Interface“ v tomto systému nebude muset mapping postloadu měnit, protože bude s touto tabulkou počítat automaticky. Nebo-li jediné co zůstane bude to, že se bude muset udělat preload a vlastní load.
17
Nová verze mappingu pro postload counting, která již počítá s naší novou procedurou.
- 48 -
8. Aplikace a ladění procedury Nyní vám již jen popíši, jaký byl osud procedury po jejím nasazení do živého skladu. Procedura se osvědčila a běhala při loadu rychleji než původní postloadové WF. Postupně se začala nasazovat na všechny systémy a nasazení na systém MIDAS se odkládalo až na chvíli, kdy bude procedura funkční na veškerých systémech, pro které se plnila proměnná SYSTEM_ID. Během jednoho z loadů se však stala neočekávaná chyba, jenž nebyla vzata v potaz v testování procedury a tak se stalo, že procedura nezareagovala zahlášením chyby, naopak bez problémů hlásila Informatica status WF = succeeded. Chyba se týkala toho, že se u několika tabulek během jejich loadu nezměnil status jejich záznamu v tabulce STL_CONTROL_RECORDS z ‘S’ na status ‘T’ tudíž je při postloadu naše procedura nebrala v potaz a nenapočítala jim řádky na výstupu. Toto by tolik nevadilo, protože constraint na kontrolu zda se vstupní počet řádků rovná výstupnímu nebyl obsažen v verzi 1.0, ale vadí to z hlediska pokračování loadu těchto „Interface“, či spíše jejich využití pro load faktovek a dimenzí. Tato chyba se tedy musela co nejdříve opravit. Noční služba ručně spustila proceduru stl_postload_counting pro nenapočítané tabulky a mě bylo zadáno, abych zahrnul do procedury check na to, zda se v tabulce STL_CONTROL_RECORDS nevyskytuje pro soubor záznamů daného systému takový záznam, jenž by měl jiný status než-li status ‘T’. Pokud by se tam takovýto záznam vyskytoval, tak by se mělo stát to, že WF skončí na chybě (EXCEPTION).
8.1. Oprava procedury – přidání kontroly na status V podstatě jde, jak jsem předeslal výše, o to, aby záznamy tabulek pro systém který chceme počítat měly všechny status ‘T’ a statusy, na kterých má procedura spadnout jsou Status = ‘S’ nebo ‘D’.
- 49 -
Jako již dva existující check cursory bude mít i tento cursor v hlavičce proměnné, jenž bude za potřebí naplnit. Tyto proměnné se budou plnit hned po vykonání soustavy IF podmínek, protože jsou to ty samé proměnné, které využívá cursor stl_cur. Dále bude potřebovat ještě proměnnou (statusCheck_var), do které se opět naplní jednička (RAISE WRONG_STATUS;) či nula (OK -> spustí se LOOP). Tyto hodnoty budou opět rozhodující pro IF podmínku, která buď dovolí nebo nedovolí spuštění LOOP cyklu. Pojďme se tedy podívat jak se nám procedura změnila oproti verzi 1.0. Zaprvé nám přibyl cursor statusCheck_cur viz. níže: CURSOR statusCheck_cur(mask VARCHAR2, bottom NUMBER, top NUMBER) IS SELECT DISTINCT(1) FROM (SELECT * FROM STL_CONTROL_RECORDS WHERE EXTRACT_NAME LIKE mask ESCAPE '\' AND (STATUS ='S' OR STATUS ='D' ) ) s WHERE TO_NUMBER(SUBSTR(extract_name,2,3)) BETWEEN bottom AND top; statusCheck_var NUMBER:=0;
A za druhé jsme museli přidat podmínku, která nám na základě hodnoty, kterou obsahuje proměnná statusCheck_var, dovolí či nedovolí provést počítání řádků na výstupu. Na konkrétní zpracování se můžete podívat na výňatku kódu níže. První ukázka kódu bude z verze 1.0, kde tato podmínka ještě není implementována a druhá ukázka kódu již bude obsahovat tuto podmínku, jenž využívá cursor statsCheck_cur.
Kód verze 1.0: OPEN stl_cur(like_mask, if_bottom, if_top); LOOP FETCH stl_cur INTO stl_zaznam; EXIT WHEN stl_cur%NOTFOUND; EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||'intuser.'||stl_zaznam.S_NAME INTO rows_counted; EXECUTE IMMEDIATE 'UPDATE '||uzivatel||'.stl_control_records S SET STATUS = :A , NRECS_ACTUAL = :B WHERE s.EXTRACT_NAME='||chr(39)||stl_zaznam.EXTRACT_NAME||chr(39) USING 'E', rows_counted; COMMIT; END LOOP; CLOSE stl_cur;
- 50 -
Kód verze 1.1: --má systém špatný STATUS (='S' or 'D')??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO OPEN statusCheck_cur(like_mask, if_bottom, if_top); FETCH statusCheck_cur INTO statusCheck_var; CLOSE statusCheck_cur; IF statusCheck_var=1 THEN--STATUS CHECK IF RAISE WRONG_STATUS; ELSE --STATUS CHECK IF OPEN stl_cur(like_mask, if_bottom, if_top); LOOP FETCH stl_cur INTO stl_zaznam; EXIT WHEN stl_cur%NOTFOUND; EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||'intuser.'||stl_zaznam.S_NAME INTO rows_counted; EXECUTE IMMEDIATE 'UPDATE '||uzivatel||'.stl_control_records S SET STATUS = :A , NRECS_ACTUAL = :B WHERE s.EXTRACT_NAME='||chr(39)||stl_zaznam.EXTRACT_NAME||chr(39) USING 'E', rows_counted; COMMIT; END LOOP; CLOSE stl_cur; END IF;--STATUS CHECK IF
Změny které byly provedeny oproti verzi 1.0 jsou vyznačeny tmavším stínováním. Po této změně se procedura opět otestovala a testy proběhly bez problémů a tak opět nic nebránilo nasazení procedury do živého DS RBCZ.
8.2. Nasazení na MIDAS Procedura byla nasazena na všechny systémy kromě MIDASového. Po týdnu pozorování verze 1.1 se rozhodlo o nasazení do zbývajících 3 systémů resp. podsystémů MIDASu. Procedura se nasadila i na zbývající 3 systémy a bez problémů proběhla a napočítala počty řádků tak, jak se očekávalo. Jediný problém, který se objevil bylo mé selhání, protože jsem při zadávání rozsahu jednoho z těchto 3 systémů zadal příliš vysoké číslo, které bylo neexistující a tak procedura zahlásila chybu. Noční služba pouze opravila rozmezí na platné a procedura doběhla v pořádku.
- 51 -
8.3. Problém s kontrolou VSTUP = VÝSTUP Další problém, který nastal se týkal právě kontroly počtu vstupních a výstupních řádků. Protože v požadavku na vytvoření této procedury nebylo to, aby procedura po napočítání výstupních řádků zkontrolovala, zda jejich počet souhlasí s počtem vstupních řádků, tak jsem tuto kontrolu, i přes to že jsem ji chtěl do procedury ze své iniciativy (ale i iniciativy mých kolegů) přidat, nepřidal a vytvořil pouze počítací proceduru. Bohužel se tato kontrola podcenila. Je sice pravdou, že některé systémy po pročištění dat vykazují méně řádků na výstupu, ale ty by se daly dát do vyjimek. Díky tomu, že tato kontrola není zaimplementována ať již v proceduře, či v nějaké ze session za ní, se stalo to, že se naplnily některé tabulky jen částečně a přišlo se na to až za pár dní. Kdyby byla kontrola zapnutá, spadlo by WF hned, jakmile by zjistila procedura nerovnost mezi vstupními a výstupními řádky. Proto jsme byli požádáni (tým podpory datového skladu), abychom tuto kontrolu zaimplementovali do procedury stl_postload_counting. Momentálně se na této kontrole pracuje, resp. pracuji na ní já.
8.3.1. Řešení problému kontroly Na přidání této kontroly se aktivně pracuje. Řešení spočívá v tom, že se vytvoří tabulka STL_POSTLOAD_EXCEPTIONS_TABLE, ve které se bude udržovat číselník tabulek u kterých je běžné, že nemají na vstupu a výstupu stejný počet řádků. Jako druhý krok se upraví procedura následujícím způsobem. V prvním kroku procedura projede tabulku STL_CONTROL_RECORDS a napočítá všem tabulkám řádky na výstupu a přidělí status ‘E’ pouze v případě, že se počet řádků na vstupu rovná řádkům na výstupu. Pakli-že se počty nerovnají, tak procedura nechá status roven ‘T’ a napočítá počet řádků na výstupu a zapíše je.
- 52 -
Po této operaci procedura znovu projede tabulku STL_CONTROL_RECORDS cursorem a zjistí (podle statusu ‘T’), kterým z tabulek se nerovnaly počty řádků na vstupu a výstupu + samozřejmě nebude brát v potaz ty tabulky, které mají záznam v tabulce STL_POSTLOAD_EXCEPTIONS_TABLE. Jakmile nějakou takovou tabulku najde, přeruší se celé WF na EXCEPTION a do tabulky INT_CONTROL_LOG, kam se kromě tabulky ETL_ERROR_MESSAGES také zapisují chyby z večerního loadu, se vloží záznamy o tabulkách a jejich počtech vstupních a výstupních řádků na kterých procedura zhavarovala. Záznam bude obsahovat popis chyby, tabulku na které k této chybě došlo a JOB_ID
loadu.
- 53 -
9. Závěr Procedura je v současné době v živém skladu RB nasazena v verzi 1.1 a verze 1.2, která má za úkol již i kontrolu vstupních a výstupních řádků, je momentálně v testovací fázi. V nejbližší době se verze 1.2 bude nasazovat do živého skladu a postupně bude nahrazovat starší verzi 1.1. Doufám, že vás má práce zaujala a vše bylo srozumitelně popsáno.
- 54 -
10. Seznam použitých zdrojů: 10.1. Zdroje s prací úzce spojené 1) HOBBS, Lilian a HILLSON, Susan a LAWANDE, Shilpa. Oracle9iR2 Data Warehousing (Oracle9iR2 Datový sklad). Digital Press, 2003, 519 s, ISBN: 1555582877 2) MAHER. “Subquery not allowed in this context?“ (podkód který není dovolen v tomto kontextu?) [online], www.orafaq.com : server s nejdotazovanějšími otázkami ohledně databáze Oracle, Datum poslední revize květen 2005,
3) PROKEŠ M. a KOZÁK I. . Stabilizace Datových Služeb – Metodika datového skladu verze 0.06 . Raiffeisenbank, 2005 4) FEUERSTEIN, Steven a PRIBL, Bill. Oracle PL/SQL Programming (Oracle PL/SQL Programování, 4tá edice). O’Reilly, 2005. 1198s, ISBN: 0-596-00977-1
10.2. Konzultace s zkušenějšími zaměstnanci 5) PIVOVARNÍK, Jan. www.ness.com . Junior System Analyst 6) KULIČ, Luboš. www.ness.com . Junior System Analyst 7) BITTNER, Tomáš. www.ness.com . Junior System Analyst 8) KMOCH, Václav. www.ness.com . Junior System Analyst 9) HOLÝ, Ondřej. Externista pracující pro RB jako OSVČ
10.3. Ostatní zdroje spjaté s tématem 10) SCALZO, Bert. Oracle® DBA Guide to Data Warehousing and Star Schemas (ORACLE DBA průvodce datovým skladem a star schématy). Prentice Hall PTR, 2003, 240 s,ISBN: 0-13-032584-8 11) ROSENZWEIG, Benjamin a SILVESTROVA, Elena. Oracle® PL/SQL™ by Example, Third Edition (ORACLE PL/SQL v příkladech, třetí edice). Prentice Hall PTR, 2003, 768 s, ISBN: 0-13-117261-1
- 55 -
12) OWENS, Kevin. Programming Oracle® Triggers and Stored Procedures, Third Edition (Programování v Oracle:Triggry a uložené procedury, Třetí Edice). Prentice Hall PTR, 2003, 448 s, ISBN: 0-13085033-0 13) LACKO, Luboslav. SQL Hotová řešení. Brno: Computer Press, 2003 , 289 stran, ISBN:80-7226-975-5
- 56 -
11. Přílohy 11.1. STL_POSTLOAD_COUNTING_v1.0.doc CREATE OR REPLACE PROCEDURE stl_postload_counting( JOB_ID IN NUMBER, SYSTEM_ID IN VARCHAR2, PAR_SUBSYSTEM IN VARCHAR2, err_check OUT NUMBER, err_msg OUT VARCHAR2, ERR_JOB_ID OUT NUMBER) /*err_check...1=err,0=OK*/ IS /* naroky na vstupni promenne: - JOB_ID napr. 1, 2, ... - SYSTEM_ID napr. 'LND' nebo 'IBL' ... - PAR_SUBSYSTEM napr. 'MID_144_199' ... */
--kurzor který vyhledá všechny tabulky pro zadaný systém budto podle SYSTEM_ID nebo podle PAR_SUBSYSTEM CURSOR stl_cur(mask VARCHAR2, bottom NUMBER, top NUMBER) IS SELECT extract_name E_NAME, 'S'||SUBSTR(extract_name,2) S_NAME, s.* FROM (SELECT * FROM stl_control_records WHERE AND EXTRACT_NAME LIKE mask ESCAPE
STATUS='T'
'\') s
WHERE TO_NUMBER(SUBSTR(extract_name,2,3)) BETWEEN bottom AND top;
--kurzor pro vyhledani existence systemu zadaneho do prominné SYSTEM_ID CURSOR systemsCheck_cur(sys_id VARCHAR2) IS SELECT 1 FROM stl_control_records WHERE SUBSTR(EXTRACT_NAME,6,3)=sys_id; systemCheck_var NUMBER :=0;
--kurzor pro zjištiní platnosti eísla pro MIDas CURSOR midCheck_cur(mid_id_bottom NUMBER, mid_id_top NUMBER, mid_bottom NUMBER, mid_top NUMBER) IS SELECT DISTINCT(1) FROM (SELECT * FROM stl_control_records WHERE EXTRACT_NAME LIKE 'E___\_MID\_%' ESCAPE
'\') s
WHERE mid_id_bottom BETWEEN mid_bottom AND mid_top
- 57 -
AND mid_id_top BETWEEN mid_bottom AND mid_top; midCheck_var NUMBER :=0; mid_id_number_bottom NUMBER; mid_id_number_top NUMBER; if_mid_minimum NUMBER; if_mid_maximum NUMBER;
stl_zaznam stl_cur%ROWTYPE; V_ERR_NUM VARCHAR2(35); V_ERR_MSG VARCHAR2(300); like_mask VARCHAR2(30); rows_counted NUMBER :=0; if_bottom NUMBER; if_top NUMBER; uzivatel VARCHAR2(20) :='INTUSER';
WRONG_SYSTEM EXCEPTION; MIDAS_ERR EXCEPTION; TWO_VALUES_ERR EXCEPTION; WRONG_STATUS EXCEPTION; BEGIN --existuje zadaný systém??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO OPEN systemsCheck_cur(UPPER(SYSTEM_ID)); FETCH systemsCheck_cur INTO systemCheck_var; CLOSE systemsCheck_cur;
--existuje rozmezi pro MIDas??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO mid_id_number_bottom:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),5,3)); mid_id_number_top:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),9)); --minimum MIDasu EXECUTE IMMEDIATE 'SELECT MIN(TO_NUMBER((SUBSTR(extract_name,2,3))))'|| 'MIN FROM (SELECT * FROM '||uzivatel||'.stl_control_records WHERE STATUS!=''X'' AND EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE ''\'')'
- 58 -
INTO if_mid_minimum; --maximum MIDasu EXECUTE IMMEDIATE 'SELECT MAX(TO_NUMBER((SUBSTR(extract_name,2,3))))'|| 'MIN FROM (SELECT * FROM '||uzivatel||'.stl_control_records WHERE STATUS!=''X'' AND EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE ''\'')' INTO if_mid_maximum;
OPEN midCheck_cur(mid_id_number_bottom, mid_id_number_top, if_mid_minimum, if_mid_maximum); FETCH midCheck_cur INTO midCheck_var; CLOSE midCheck_cur;
--IFIK PRO LIKE_MASK IF (UPPER(SYSTEM_ID) IS NULL AND UPPER(PAR_SUBSYSTEM) IS NULL) OR (UPPER(SYSTEM_ID) IS NOT NULL AND UPPER(PAR_SUBSYSTEM) IS NOT NULL) THEN--IF00 RAISE TWO_VALUES_ERR; ELSE--IF00 IF( UPPER(SYSTEM_ID) IS NULL) THEN --IF01 --MIDAS_CHECK IF midCheck_var=0 THEN--IF02 RAISE MIDAS_ERR; ELSE--IF02 like_mask :='E___\_' || 'MID' || '\_%' ; --dno if_platne if_bottom:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),5,3)); --top if_top:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),9)); END IF;--IF02 --MIDAS_CHECK
ELSE--IF01 IF systemCheck_var=0 THEN--IF03 RAISE WRONG_SYSTEM; ELSE--IF03 like_mask :='E___\_' || UPPER(SYSTEM_ID) || '\_%' ; --dno vzdy_platne if_bottom:=0; --top
- 59 -
if_top:=10000; END IF;--IF03 END IF;--IF01 END IF;--IF00
OPEN stl_cur(like_mask, if_bottom, if_top); LOOP FETCH stl_cur INTO stl_zaznam; EXIT WHEN stl_cur%NOTFOUND; EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||'intuser.'||stl_zaznam.S_NAME INTO rows_counted;
EXECUTE IMMEDIATE 'UPDATE '||uzivatel||'.stl_control_records S SET STATUS = :A , NRECS_ACTUAL = :B WHERE s.EXTRACT_NAME='||chr(39)||stl_zaznam.EXTRACT_NAME||chr(39) USING 'E', rows_counted; COMMIT; END LOOP; CLOSE stl_cur;
err_check:=0; ERR_JOB_ID:=JOB_ID; EXCEPTION WHEN NO_DATA_FOUND THEN err_check:=1; V_ERR_NUM := SQLCODE;/*SQLCODE=+100*/ V_ERR_MSG := SUBSTR(SQLERRM,1,300); err_msg:='No data found, oracle error message number:'||V_ERR_NUM||'>'||V_ERR_MSG; ERR_JOB_ID:=JOB_ID; WHEN WRONG_SYSTEM THEN err_check:=1; err_msg:=UPPER(SYSTEM_ID)||' THIS SYSTEM_ID IS NOT VALID'; ERR_JOB_ID:=JOB_ID; WHEN MIDAS_ERR THEN err_check:=1; err_msg:='MIDAS ERROR: WRONG PAR_SUBSYSTEM '||PAR_SUBSYSTEM||' OR YOUR IDENTIFIER MID_'||mid_id_number_bottom||'_'||mid_id_number_top|| ' IS NOT IN MIDAS RANGE '||if_mid_minimum||' - '||if_mid_maximum; ERR_JOB_ID:=JOB_ID;
- 60 -
WHEN TWO_VALUES_ERR THEN err_check:=1; err_msg:='YOU HAVE (OR HAVE NOT) INSERTED 2 VALUES: SYSTEM_ID and PAR_SUBSYSTEM... ONLY ONE IS ALLOWED'; ERR_JOB_ID:=JOB_ID; WHEN OTHERS THEN err_check:=1; V_ERR_NUM := SQLCODE; V_ERR_MSG := SUBSTR(SQLERRM,1,300); err_msg:='Oracle error message number:'||V_ERR_NUM||'->'||V_ERR_MSG; ERR_JOB_ID:=JOB_ID; END stl_postload_counting; /
- 61 -
11.2. STL_POSTLOAD_COUNTING_v1.1.doc CREATE OR REPLACE PROCEDURE stl_postload_counting( JOB_ID IN NUMBER, SYSTEM_ID IN VARCHAR2, PAR_SUBSYSTEM IN VARCHAR2, err_check OUT NUMBER, err_msg OUT VARCHAR2, ERR_JOB_ID OUT NUMBER) /*err_check...1=err,0=OK*/ IS /* naroky na vstupni promenne: - JOB_ID napr. 1, 2, ... - SYSTEM_ID napr. 'LND' nebo 'IBL' ... - PAR_SUBSYSTEM napr. 'MID_144_199' ... */
--kurzor který vyhledá všechny tabulky pro zadaný systém budto podle SYSTEM_ID nebo podle PAR_SUBSYSTEM CURSOR stl_cur(mask VARCHAR2, bottom NUMBER, top NUMBER) IS SELECT extract_name E_NAME, 'S'||SUBSTR(extract_name,2) S_NAME, s.* FROM (SELECT * FROM stl_control_records WHERE AND EXTRACT_NAME LIKE mask ESCAPE
STATUS='T'
'\') s
WHERE TO_NUMBER(SUBSTR(extract_name,2,3)) BETWEEN bottom AND top;
--kurzor pro check jestli je spravne zmeneny STATUS u daneho systemu --ERROR je u statusu 'S' nebo 'D' - POZOR - muze byt jakykoliv jiny status, my chceme !1 -> check na nej CURSOR statusCheck_cur(mask VARCHAR2, bottom NUMBER, top NUMBER) IS SELECT DISTINCT(1) FROM (SELECT * FROM STL_CONTROL_RECORDS WHERE
EXTRACT_NAME LIKE mask ESCAPE
'\'
AND (STATUS ='S' OR STATUS ='D' ) ) s WHERE TO_NUMBER(SUBSTR(extract_name,2,3)) BETWEEN bottom AND top;
statusCheck_var NUMBER:=0;
--kurzor pro vyhledani existence systemu zadaneho do prominné SYSTEM_ID CURSOR systemsCheck_cur(sys_id VARCHAR2) IS
- 62 -
SELECT 1 FROM stl_control_records WHERE SUBSTR(EXTRACT_NAME,6,3)=sys_id; systemCheck_var NUMBER :=0;
--kurzor pro zjištiní platnosti eísla pro MIDas CURSOR midCheck_cur(mid_id_bottom NUMBER, mid_id_top NUMBER, mid_bottom NUMBER, mid_top NUMBER) IS SELECT DISTINCT(1) FROM (SELECT * FROM stl_control_records WHERE EXTRACT_NAME LIKE 'E___\_MID\_%' ESCAPE
'\') s
WHERE mid_id_bottom BETWEEN mid_bottom AND mid_top AND mid_id_top BETWEEN mid_bottom AND mid_top; midCheck_var NUMBER :=0; mid_id_number_bottom NUMBER; mid_id_number_top NUMBER; if_mid_minimum NUMBER; if_mid_maximum NUMBER;
stl_zaznam stl_cur%ROWTYPE; V_ERR_NUM VARCHAR2(35); V_ERR_MSG VARCHAR2(300); like_mask VARCHAR2(30); rows_counted NUMBER :=0; if_bottom NUMBER; if_top NUMBER; uzivatel VARCHAR2(20) :='INTUSER';
WRONG_SYSTEM EXCEPTION; MIDAS_ERR EXCEPTION; TWO_VALUES_ERR EXCEPTION; WRONG_STATUS EXCEPTION; BEGIN --existuje zadaný systém??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO OPEN systemsCheck_cur(UPPER(SYSTEM_ID)); FETCH systemsCheck_cur INTO systemCheck_var; CLOSE systemsCheck_cur;
- 63 -
--existuje rozmezi pro MIDas??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO mid_id_number_bottom:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),5,3)); mid_id_number_top:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),9)); --minimum MIDasu EXECUTE IMMEDIATE 'SELECT MIN(TO_NUMBER((SUBSTR(extract_name,2,3))))'|| 'MIN FROM (SELECT * FROM '||uzivatel||'.stl_control_records WHERE STATUS!=''X'' AND EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE ''\'')' INTO if_mid_minimum; --maximum MIDasu EXECUTE IMMEDIATE 'SELECT MAX(TO_NUMBER((SUBSTR(extract_name,2,3))))'|| 'MIN FROM (SELECT * FROM '||uzivatel||'.stl_control_records WHERE STATUS!=''X'' AND EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE ''\'')' INTO if_mid_maximum;
OPEN midCheck_cur(mid_id_number_bottom, mid_id_number_top, if_mid_minimum, if_mid_maximum); FETCH midCheck_cur INTO midCheck_var; CLOSE midCheck_cur;
--IFIK PRO LIKE_MASK IF (UPPER(SYSTEM_ID) IS NULL AND UPPER(PAR_SUBSYSTEM) IS NULL) OR (UPPER(SYSTEM_ID) IS NOT NULL AND UPPER(PAR_SUBSYSTEM) IS NOT NULL) THEN--IF00 RAISE TWO_VALUES_ERR; ELSE--IF00 IF( UPPER(SYSTEM_ID) IS NULL) THEN --IF01 --MIDAS_CHECK IF midCheck_var=0 THEN--IF02 RAISE MIDAS_ERR; ELSE--IF02 like_mask :='E___\_' || 'MID' || '\_%' ; --dno if_platne if_bottom:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),5,3)); --top if_top:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),9));
- 64 -
END IF;--IF02 --MIDAS_CHECK
ELSE--IF01 IF systemCheck_var=0 THEN--IF03 RAISE WRONG_SYSTEM; ELSE--IF03 like_mask :='E___\_' || UPPER(SYSTEM_ID) || '\_%' ; --dno vzdy_platne if_bottom:=0; --top if_top:=10000; END IF;--IF03 END IF;--IF01 END IF;--IF00
--má systém špatný STATUS (='S' or 'D')??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO OPEN statusCheck_cur(like_mask, if_bottom, if_top); FETCH statusCheck_cur INTO statusCheck_var; CLOSE statusCheck_cur;
IF statusCheck_var=1 THEN--STATUS CHECK IF RAISE WRONG_STATUS; ELSE OPEN stl_cur(like_mask, if_bottom, if_top); LOOP FETCH stl_cur INTO stl_zaznam; EXIT WHEN stl_cur%NOTFOUND; EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||'intuser.'||stl_zaznam.S_NAME INTO rows_counted;
EXECUTE IMMEDIATE 'UPDATE '||uzivatel||'.stl_control_records S SET STATUS = :A , NRECS_ACTUAL = :B WHERE s.EXTRACT_NAME='||chr(39)||stl_zaznam.EXTRACT_NAME||chr(39) USING 'E', rows_counted; COMMIT; END LOOP;
- 65 -
CLOSE stl_cur; END IF;--STATUS CHECK IF err_check:=0; ERR_JOB_ID:=JOB_ID; EXCEPTION WHEN NO_DATA_FOUND THEN err_check:=1; V_ERR_NUM := SQLCODE;/*SQLCODE=+100*/ V_ERR_MSG := SUBSTR(SQLERRM,1,300); err_msg:='No data found, oracle error message number:'|| V_ERR_NUM||'->'||V_ERR_MSG; ERR_JOB_ID:=JOB_ID; WHEN WRONG_SYSTEM THEN err_check:=1; err_msg:=UPPER(SYSTEM_ID)||' THIS SYSTEM_ID IS NOT VALID'; ERR_JOB_ID:=JOB_ID; WHEN MIDAS_ERR THEN err_check:=1; err_msg:='MIDAS ERROR: WRONG PAR_SUBSYSTEM '||PAR_SUBSYSTEM||' OR YOUR IDENTIFIER MID_'||mid_id_number_bottom||'_'||mid_id_number_top|| ' IS NOT IN MIDAS RANGE '||if_mid_minimum||' - '||if_mid_maximum; ERR_JOB_ID:=JOB_ID;
- 66 -
WHEN TWO_VALUES_ERR THEN err_check:=1; err_msg:='YOU HAVE (OR HAVE NOT) INSERTED 2 VALUES: SYSTEM_ID and PAR_SUBSYSTEM... ONLY ONE IS ALLOWED'; ERR_JOB_ID:=JOB_ID; WHEN WRONG_STATUS THEN err_check:=1; err_msg:='STATUS ERROR:ONE OR MORE TABLES IN THIS SYSTEM_ID: '||UPPER(SYSTEM_ID)||' OR PAR_SUBSYSTEM: '||UPPER(PAR_SUBSYSTEM)||'HAS STATUS=S OR STATUS=D IN STL_CONTROL_RECORDS'; ERR_JOB_ID:=JOB_ID; WHEN OTHERS THEN err_check:=1; V_ERR_NUM := SQLCODE; V_ERR_MSG := SUBSTR(SQLERRM,1,300); err_msg:='Oracle error message number:'||V_ERR_NUM||'->'||V_ERR_MSG; ERR_JOB_ID:=JOB_ID; END stl_postload_counting;
- 67 -
11.3. STL_POSTLOAD_COUNTING_v1.2.doc CREATE OR REPLACE PROCEDURE stl_postload_counting_new( JOB_ID IN NUMBER, SYSTEM_ID IN VARCHAR2, PAR_SUBSYSTEM IN VARCHAR2, err_check OUT NUMBER, err_msg OUT VARCHAR2, ERR_JOB_ID OUT NUMBER) /*err_check...1=err,0=OK*/ IS /* naroky na vstupni promenne: - JOB_ID napr. 1, 2, ... - SYSTEM_ID napr. 'LND' nebo 'IBL' ... - PAR_SUBSYSTEM napr. 'MID_144_199' ... */ --kurzor který vyhledá všechny tabulky pro zadaný systém budto podle SYSTEM_ID nebo podle PAR_SUBSYSTEM CURSOR stl_cur(mask VARCHAR2, bottom NUMBER, top NUMBER) IS SELECT extract_name E_NAME, 'S'||SUBSTR(extract_name,2) S_NAME, s.* FROM (SELECT * FROM intuser.stl_control_records WHERE AND EXTRACT_NAME LIKE mask ESCAPE
STATUS='T'
'\') s
WHERE TO_NUMBER(SUBSTR(extract_name,2,3)) BETWEEN bottom AND top;
--kurzor pro check jestli je spravne zmeneny STATUS u daneho systemu --ERROR je u statusu 'S' nebo 'D' - POZOR - muze byt jakykoliv jiny status, my chceme !1 -> check na nej CURSOR statusCheck_cur(mask VARCHAR2, bottom NUMBER, top NUMBER) IS SELECT DISTINCT(1) FROM (SELECT * FROM intuser.stl_control_records WHERE
EXTRACT_NAME LIKE mask ESCAPE
'\'
AND (STATUS ='S' OR STATUS ='D' ) ) s WHERE TO_NUMBER(SUBSTR(extract_name,2,3)) BETWEEN bottom AND top; statusCheck_var NUMBER:=0;
--Kurzor pro zjištění zda se INTUSER.STL_CONTROL_RECORDS.EXTRACT_NAME nachází v INTUSER.INT_POSTLOAD_COUNT_EXCEPTIONS.EXTRACT_NAME CURSOR exceptionsTableCheck_cur(table_name VARCHAR2) IS
- 68 -
SELECT DISTINCT(1) FROM INTUSER.INT_POSTLOAD_COUNT_EXCEPTIONS WHERE EXTRACT_NAME = table_name; exceptionsTableCheck_var NUMBER :=0;
stl_zaznam stl_cur%ROWTYPE; V_ERR_NUM VARCHAR2(35); V_ERR_MSG VARCHAR2(300); like_mask VARCHAR2(30); rows_counted NUMBER :=0; if_bottom NUMBER; if_top NUMBER; uzivatel VARCHAR2(20) :='INTUSER'; ERROR_TABLES_NUM NUMBER:=0; --CHECK PROMĚNNÁ PRO IN=OUT ROWS CHECK ERROR_TABLE_NAME VARCHAR(100):= ''; SOURCE_RADKY NUMBER :=0; systemCheck_var NUMBER :=0;--CHECK PROMĚNNÁ PRO SYSTEM_ID midCheck_var NUMBER :=0;--CHECK PROMĚNNÉ PRO MIDAS -> PAR_SUBSYSTEM mid_id_number_bottom NUMBER; mid_id_number_top NUMBER; if_mid_minimum NUMBER; if_mid_maximum NUMBER;
WRONG_SYSTEM EXCEPTION; MIDAS_ERR EXCEPTION; TWO_VALUES_ERR EXCEPTION; WRONG_STATUS EXCEPTION; IN_OUT_ERROR EXCEPTION; SYSTEM_ID_ERR EXCEPTION; BEGIN --existuje zadaný systém??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO EXECUTE IMMEDIATE 'SELECT DECODE ((SELECT DISTINCT(1) FROM '||uzivatel||'.STL_CONTROL_RECORDS WHERE SUBSTR(EXTRACT_NAME,6,3)=:A ),1,1,NULL,0) FROM DUAL' INTO systemCheck_var USING SYSTEM_ID;
--existuje rozmezi pro MIDas???
- 69 -
---systemCheck_var=0...NE ---systemCheck_var=1...ANO mid_id_number_bottom:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),5,3)); mid_id_number_top:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),9)); --minimum MIDasu EXECUTE IMMEDIATE 'SELECT MIN(TO_NUMBER((SUBSTR(extract_name,2,3))))'|| 'MIN FROM (SELECT * FROM '||uzivatel||'.stl_control_records WHERE STATUS!=''X'' AND EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE ''\'')' INTO if_mid_minimum; --maximum MIDasu EXECUTE IMMEDIATE 'SELECT MAX(TO_NUMBER((SUBSTR(extract_name,2,3))))'|| 'MIN FROM (SELECT * FROM '||uzivatel||'.stl_control_records WHERE STATUS!=''X'' AND EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE ''\'')' INTO if_mid_maximum;
IF PAR_SUBSYSTEM IS NULL THEN midCheck_var:=0; ELSE EXECUTE IMMEDIATE
'SELECT DECODE ((SELECT DISTINCT(1) FROM'||
'(SELECT * FROM '||uzivatel||'.stl_control_records WHERE EXTRACT_NAME LIKE ''E___\_MID\_%'' ESCAPE
''\'') s '||
'WHERE '||mid_id_number_bottom||' BETWEEN '||if_mid_minimum||' AND '||if_mid_maximum||' AND '||mid_id_number_top||' BETWEEN '|| if_mid_minimum||' AND '||if_mid_maximum||' ) ,1,1, NULL,0 ) FROM DUAL' INTO midCheck_var; END IF;
--IFIK PRO LIKE_MASK IF (UPPER(SYSTEM_ID) IS NULL AND UPPER(PAR_SUBSYSTEM) IS NULL) OR (UPPER(SYSTEM_ID) IS NOT NULL AND UPPER(PAR_SUBSYSTEM) IS NOT NULL) THEN--IF00 RAISE TWO_VALUES_ERR; ELSE--IF00 IF( UPPER(SYSTEM_ID) IS NULL) THEN --IF01 --MIDAS_CHECK IF midCheck_var=0 THEN--IF02 RAISE MIDAS_ERR; ELSE--IF02
- 70 -
like_mask :='E___\_' || 'MID' || '\_%' ; --dno if_platne if_bottom:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),5,3)); --top if_top:=TO_NUMBER(SUBSTR(UPPER(PAR_SUBSYSTEM),9)); END IF;--IF02 --MIDAS_CHECK ELSE--IF01 IF systemCheck_var=0 THEN--IF03 RAISE WRONG_SYSTEM; ELSE--IF03 IF SYSTEM_ID='MID' THEN--IF04 RAISE SYSTEM_ID_ERR; ELSE--IF04 like_mask :='E___\_' || UPPER(SYSTEM_ID) || '\_%' ; --dno vzdy_platne if_bottom:=0; --top if_top:=10000; END IF;--IF04 END IF;--IF03 END IF;--IF01 END IF;--IF00
--má systém špatný STATUS (='S' or 'D')??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO OPEN statusCheck_cur(like_mask, if_bottom, if_top); FETCH statusCheck_cur INTO statusCheck_var; CLOSE statusCheck_cur;
IF statusCheck_var=1 THEN--STATUS CHECK IF RAISE WRONG_STATUS; ELSE--STATUS CHECK IF OPEN stl_cur(like_mask, if_bottom, if_top); LOOP FETCH stl_cur INTO stl_zaznam; EXIT WHEN stl_cur%NOTFOUND;
- 71 -
--má tabulka stl_zaznam.E_NAME záznam v INT_POSTLOAD_COUNT_EXCEPTIONS??? ---systemCheck_var=0...NE ---systemCheck_var=1...ANO
OPEN exceptionsTableCheck_cur(stl_zaznam.E_NAME); FETCH exceptionsTableCheck_cur INTO exceptionsTableCheck_var; CLOSE exceptionsTableCheck_cur;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||'intuser.'||stl_zaznam.S_NAME INTO rows_counted; IF rows_counted = stl_zaznam.NRECS_EXPECTED
OR (rows_counted !=
stl_zaznam.NRECS_EXPECTED AND exceptionsTableCheck_var=1) THEN --IF A EXECUTE IMMEDIATE 'UPDATE '||uzivatel||'.stl_control_records S SET STATUS = :A , NRECS_ACTUAL = :B WHERE s.EXTRACT_NAME= :C' USING 'E', rows_counted, stl_zaznam.EXTRACT_NAME; COMMIT; ELSE--IF A ERROR_TABLES_NUM:= ERROR_TABLES_NUM+1; ERROR_TABLE_NAME:=stl_zaznam.EXTRACT_NAME; EXECUTE IMMEDIATE 'UPDATE '||uzivatel||'.stl_control_records S SET STATUS = :A , NRECS_ACTUAL = :B WHERE s.EXTRACT_NAME= :C' USING 'T', rows_counted, stl_zaznam.EXTRACT_NAME; SOURCE_RADKY:=stl_zaznam.NRECS_EXPECTED; --INPUT RÁDKY ERROR_TABLE_NAME:=stl_zaznam.EXTRACT_NAME; --INSERTÍCÍ SCRIPT KTERÝ ZAPÍŠE V KTERÝCH TABULÍCH SE CHYBA VYSKYTUJE EXECUTE IMMEDIATE 'INSERT INTO '||uzivatel||'.INT_CONTROL_LOG
VALUES (:A , :B,
:C, NULL,NULL,NULL,NULL) ' USING 'T', 'stl_postload_counting_NEW' , ERROR_TABLE_NAME ||' -> INPUT ROWS ('||SOURCE_RADKY||') != OUTPUT ROWS ('|| rows_counted||')'; COMMIT; END IF; --IF A exceptionsTableCheck_var:=0; END LOOP; CLOSE stl_cur; END IF;--STATUS CHECK IF
err_check:=0; ERR_JOB_ID:=JOB_ID;
- 72 -
--Zfailování či nezfailování session --v případě nerovnosti řádků na vstupu a výstupu IF ERROR_TABLES_NUM>0 THEN RAISE IN_OUT_ERROR;--fail session ELSE ERROR_TABLES_NUM:=ERROR_TABLES_NUM;--no problems END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN err_check:=1; V_ERR_NUM := SQLCODE;/*SQLCODE=+100*/ V_ERR_MSG := SUBSTR(SQLERRM,1,300); err_msg:='No data found, oracle error message number:'||V_ERR_NUM||'>'||V_ERR_MSG; ERR_JOB_ID:=JOB_ID; WHEN WRONG_SYSTEM THEN err_check:=1; err_msg:=UPPER(SYSTEM_ID)||' THIS SYSTEM_ID IS NOT VALID'; ERR_JOB_ID:=JOB_ID; WHEN MIDAS_ERR THEN err_check:=1; err_msg:='MIDAS ERROR: WRONG PAR_SUBSYSTEM '||PAR_SUBSYSTEM||' OR YOUR IDENTIFIER MID_'||mid_id_number_bottom||'_'||mid_id_number_top|| ' IS NOT IN MIDAS RANGE '||if_mid_minimum||' - '||if_mid_maximum; ERR_JOB_ID:=JOB_ID; WHEN TWO_VALUES_ERR THEN err_check:=1; err_msg:='YOU HAVE (OR HAVE NOT) INSERTED 2 VALUES: SYSTEM_ID and PAR_SUBSYSTEM... ONLY ONE IS ALLOWED'; ERR_JOB_ID:=JOB_ID; WHEN WRONG_STATUS THEN err_check:=1; err_msg:='STATUS ERROR:ONE OR MORE TABLES IN THIS SYSTEM_ID: '||UPPER(SYSTEM_ID)||' OR PAR_SUBSYSTEM: '||UPPER(PAR_SUBSYSTEM)||'HAS STATUS=S OR STATUS=D IN stl_control_records'; ERR_JOB_ID:=JOB_ID; WHEN IN_OUT_ERROR THEN err_check:=1;
- 73 -
err_msg:='NUMBER OF ROWS IN INPUT AND OUTPUT IN '||ERROR_TABLES_NUM||' TABLE(S) FROM STL_CONTROL_RECORDS FOR SYSTEM_ID: '||UPPER(SYSTEM_ID)||' OR PAR_SUBSYSTEM: '||UPPER(PAR_SUBSYSTEM)||' IS NOT THE SAME!!! FOR MORE INFORMATIONS SEE TABLE INTUSER.INT_CONTROL_LOG'; ERR_JOB_ID:=JOB_ID; WHEN SYSTEM_ID_ERR THEN err_check:=1; err_msg:='VARIABLE "SYSTEM_ID" IN POSTLOAD_COUNTING MAPPING = MID -> THIS IS NOT ALLOWED!'; ERR_JOB_ID:=JOB_ID; WHEN OTHERS THEN err_check:=1; V_ERR_NUM := SQLCODE; V_ERR_MSG := SUBSTR(SQLERRM,1,300); err_msg:='Oracle error message number:'||V_ERR_NUM||'->'||V_ERR_MSG; ERR_JOB_ID:=JOB_ID; END stl_postload_counting_new; /
- 74 -
