Zaczynamy ćwiczyć z DB2

Zaczynamy ćwiczyć z DB2
Tworzymy procedurę, która wstawia do dwóch tabel: osoba i adres
Autor: Artur
Wroński, IBM
[email protected]
… i przy okazji poznamy mechanizmy pozwalające na tworzenie
optymalnych zapytań SQL
Wykonując to ćwiczenie dowiesz się:
–Jak wygenerować identyfikator dla klucza głównego?
–Jak napisać procedurę składowaną?
–Jak sprawdzić, czy zapytanie SQL jest optymalnie wykonywane przez bazę?
–Co ma wpływ na optymalizację zapytań?
–Jak sprawdzić, jakie indeksy przydadzą się dla zapytania?
–Jak przekazywać zbiory wynikowe z procedury do aplikacji?
–Co to są wyrażenia common table expressions?
1
Projekt tabel
tabela: osoba
id_osoby bigint (PK)
imie
nazwisko
miejscowosc
ulica
nr_domu
kod_pocztowy
imie varchar(50)
nazwisko varchar(50)
tabela: adres
id_osoby bigint (FK)
miejscowosc varchar(80)
ulica varchar(80)
nr_domu varchar(15)
kod_pocztowy char(5)
Założenia:
• imię i nazwisko nie może się powtarzać
• id_osoby musi być wygenerowane
• usunięcie osoby powinno usunąć rekordy z adresem
• osoba może posiadać tylko jeden adres
Na przykładzie bardzo prostego ćwiczenia poznasz elementy przydatne dla dewelopera
aplikacji działających na DB2.
Ćwiczenie będzie polegało na napisaniu procedury składowanej, która dostaje na wejściu
zestaw danych i wstawia je do dwóch tabel: osoba i adres.
2
Tworzymy tabelę OSOBA
create table
id_osoby
imie
nazwisko
);
osoba (
bigint not null,
varchar(50) not null,
varchar(50) not null
alter table osoba
add constraint osoba_id_osoby_pk primary key (id_osoby);
alter table osoba
add constraint osoba_naz_uq unique (nazwisko,imie);
Czy stosować reguły integralnościowe (constrains)?
Jak wykonać przedstawione na slajdzie instrukcje? Jest wiele metod. Możesz uruchomić
narzędzie graficzne Centrum Sterowania (DB2 Control Center), wybrać z menu
Narzędzia -> Edytor Komend. W edytorze połącz się z wybraną bazą danych, wklej
polecenie i wykonaj. Zwróć uwagę, że każda instrukcja zakończona jest średnikiem – w
narzędziach DB2 jest to domyślny znak kończący instrukcję.
Możesz uruchomić okno poleceń tekstowych DB2 (w systemie Windows: db2cmd, na
Linuksie/Uniksach jest to po prostu powłoka systemu operacyjnego), następnie
przygotować plik z w/w instrukcjami i go wywołać go:
db2 connect to baza1
db2 -tvf plik_z_instrukcjami.sql
Opcja -t oznacza, że instrukcja jest zakończona znakiem specjalnym (terminated),
domyślne takim znakiem jest średnik (;). Opcja -v wyświetla echo instrukcji (verbose),
natomiast opcja -f czyta polecenia z pliku (file)
Jeśli, chciałbyś przygotować i zarządzać bardziej złożonym modelem danych, IBM ma
do tego odpowiednie narzędzie: IBM Rational Data Architect (na bazie Eclipse).
□
Niektórzy deweloperzy unikają reguł integralnościowych (constraints), ponieważ
spowalniają modyfikacje danych. Jednak tylko w taki sposób jesteśmy zagwarantować
spójność danych w bazie. Zwróć uwagę, że baza danych wykorzystuje reguły
integralnościowe przy optymalizacji zapytań, co może przyspieszyć wykonywanie
zapytań. Pewnie zdziwisz się, ale w DB2 można zaznaczyć, by reguły integralnościowe
nie były sprawdzane przy modyfikacji (NOT ENFORCED), a były wykorzystywane do
optymalizacji (ENABLE QUERY OPTIMIZATION) – zapytania działają szybciej, a
modyfikacje nie są spowalniane. Takie więzy spójności (informational constraints) mają
jednak sens, jeśli mamy 100% przekonanie, że dane są spójne.
3
Generowanie id
alter table osoba alter column id_osoby
set generated always as identity
(start with 100);
generated always - baza zawsze generuje dane
insert into osoba (imie, nazwisko) values
('Adam', 'Mickiewicz');
Co zostało wygenerowane?
values identity_val_local()
Ostatnie w sesji wygenerowane ID.
select id_osoby from new table (
insert into osoba (imie, nazwisko) values
('Adam', 'Mickiewicz')
) as t;
SELECT z INSERT-a
Jednym ze sposobów określenia nowej wartości klucza głównego są pola generowane.
Pola generowane przechowują wartości, które zostały wygenerowane dowolną funkcją
SQL. IDENTITY jest przykładem funkcji pozwalającej wygenerować kolejny numer,
który bardzo przydaje się przy tzw. sztucznych kluczach głównych. Jeśli pole
zdefiniowane było z atrybutem ALWAYS, wtedy w aplikacji nie mamy możliwości
określenia wartości pola (musimy je pominąć w instrukcji wstawiającej, bądź
modyfikującej). Jeśli pole zdefiniowane było z atrybutem BY DEFAULT, wtedy
możemy mu także przypisać wartość jawnie.
Wygenerowaną ostatnio wartość IDENTITY możemy pobrać specjalną funkcją
identity_val_local(), ale także możemy wykorzystać specjalny rodzaj zapytania
SELECT z instrukcji INSERT, który zwróci nam dane, które rzeczywiście zostały
zapisane przez bazę danych. Na pierwszy rzut oka taka funkcjonalność może wydawać
się zbędna, bo przecież wiemy, co zapisywaliśmy do bazy danych – jednak w praktyce
taka konstrukcja bardzo się przydaje. Zwróć uwagę, że każdą próbę modyfikacji danych
możemy podmienić wyzwalaczem TRIGGER … INSTEAD OF – wtedy jedną
instrukcją SQL możemy sprawdzić, co było zapisane. Zapytanie SELECT z INSERTa
jest dużo bardziej wydajne niż zapisanie danych instrukcją INSERT i odczytanie rekordu
dodatkowym zapytaniem. Zwróć uwagę, że SELECT można robić także na instrukcjach
UPDATE, DELETE i zwrócić rekordy modyfikowane, bądź sprzed modyfikacji.
4
Generowanie id
Jeśli baza zawsze generuje wartości to jak migrować tak
założone tabele IMPORT-em?
alter table osoba alter column id_osoby
set generated by default;
generated by default - baza pozwala na INSERT, UPDATE
import from plik1 of del insert into osoba
IMPORT
alter table osoba alter column id_osoby
set generated always;
alter table osoba alter column id_osoby
restart with 999;
Zmiana na ALWAYS oraz ustawienie licznika
Skoro tylko baza danych może wygenerować wartość pojawia się pytanie „jak przenieść
dane pomiędzy tak utworzonymi tabelami bądź pomiędzy bazami danych”? Klauzula BY
DEFAULT pozwala nam także na ręczne modyfikowanie generowanych pól, dzięki
czemu będziemy mogli użyć standardowych narzędzi do migracji danych
wykorzystujących SQL INSERT, takich jak IMPORT.
Na czas ładowania danych zmieniamy definicję pola generowanego na GENERATED
BY DEFAULT. Ładujemy dane IMPORT-em. W przykładzie ładowany plik ma postać
pliku tekstowego z separtorem – klauzula of del (delimited).
Po załadowaniu danych zmieniamy definicję pola generowanego, tak by zagwarantować,
że nikt ręcznie nie nadpisze wartości pola generowanego oraz zwiększamy wartość
licznika związanego z polem IDENTITY.
□
Import jest przykładem narzędzia do ładowania danych. Import czyta dane z pliku bądź
z kursora. Plik może mieć postać tekstową (of del) bądź binarną ixf (integrated exchange
format). Format ixf jest przenośny pomiędzy różnymi systemami DB2 i dodatkowo
zawiera definicję tabeli, tzn. na podstawie pliku ixf możemy odtworzyć strukturę tabeli.
Dane z bazy możesz wyeksportować poleceniem export. Oto przykłady exportu:
db2 export to plik.del of del select * from tabela
db2 export to plik.ixf of ixf select * from tabela
5
Generowanie id, cz. II
Jeśli baza zawsze generuje wartości to jak migrować tak
założone tabele IMPORT-em?
alter table osoba alter column id_osoby
drop identity;
Usunięcie IDENTITY
import from plik1 of del insert into osoba
IMPORT
alter table osoba alter column id_osoby
set generated always as identity
(start with 100);
alter table osoba alter column id_osoby
restart with 999;
Założenie IDENTITY oraz ustawienie licznika
Atrybut IDENTITY możemy także usunąć, a następnie odtworzyć po załadowaniu
danych. Klauzula (start with 100) określa definicję pola generowanego (narzędzia do
przenoszenia schematu bazy odczytają wartość początkową równą 100), natomiast
klauzula restart with 999 określa bieżącą wartość licznika. Wartość licznika mogliśmy
nadać tylko jednym poleceniem (start with 999), lecz w ten sposób tracilibyśmy
informację, od jakiego numeru rozpoczęliśmy generowanie kluczy.
□
Jeśli chciałbyś sprawdzić definicję tabeli możesz zrobić to przy pomocy narzędzia
db2look (opcja –e pochodzi od słowa extract):
db2look -d baza1 -e -t tabela1
DDL (data definition language) tabeli możesz wygenerować także w Centrum
Sterowania DB2: kliknij prawym klawiszem na wybraną tabelą i wybierz „Generuj
DDL…”.
6
Generowanie id, cz. III
Jeśli baza zawsze generuje wartości to jak migrować tak
założone tabele LOAD-em?
load from plik1 of del modified by identityoverride insert
into osoba
LOAD
alter table osoba alter column id_osoby
restart with 999;
Ustawienie licznika
Tabelę z polem generowanym możemy także „nadpisać na siłę”, narzędziem LOAD,
które zapisuje dane bezpośrednio na dysk z pominięciem dziennika transakcji oraz
silnika SQL. W przykładzie wykorzystaliśmy modyfikator identityoverride, który
pozwala na nadpisanie pól IDENTITY z opcja GENERATED ALWAYS.
□
LOAD jest zupełnie inna metodą ładowania danych niż IMPORT. Przede wszystkim jest
dużo szybszy. Narzędzie jest wielowątkowe i automatycznie powołuje odpowiednią ilość
agentów, tak by w maksymalnym stopniu wykorzystać dostępne procesory. Indeksy
budowane są dopiero po załadowaniu danych (mogą być budowane przyrostowo).
Oczywiście zysk na szybkości musi odbywać się jakimś kosztem. Podczas ładowania
danych obszar tabel, w którym znajduje się ładowana tabela dostępny jest tylko do
odczytu. Dodatkowo, narzędzie LOAD nie sprawdza reguł integralnościowych, dlatego
też po załadowaniu danych należy uruchomić specjalną komendę SET INTEGRITY,
która sprawdza spójność danych i ponownie aktywuje więzy spójności (np. klucz główny
– klucz obcy).
7
Czy są inne metody generacji ID?
CREATE SEQUENCE seq1 AS BIGINT
START WITH 1;
VALUES NEXTVAL FOR seq1 ;
VALUES PREVVAL FOR seq1 ;
ALTER SEQUENCE seq1 RESTART WITH 100 ;
Jak jest różnica pomiędzy INDENTITY as SEQUENCE?
- INDENTITY związane jest z tabelą, a SEQUENCE jest
niezależnym obiektem, wykorzystywanym przez
aplikacje.
Kolejny identyfikator możemy także wygenerować przy wykorzystaniu sekwencji.
Sekwencje jednak nie są skojarzone z danym obiektem bazy danych – aplikacja musi być
świadoma faktu istnienia sekwencji. Kolejne wartości sekwencji generuje się funkcją
NEXTVAL (NEXT VALUE). Funkcja PREVVAL (PREVIOUS VALUE) podaje
ostatnią wygenerowaną w sesji wartość dla danej sekwencji. Warto zaznaczyć, że
sekwencje, jak i pola generowane generują po prostu następny numer i nie sprawdzają
wartości, które są aktualnie w tabeli. Jeśli chcemy wygenerować wartość, która jest
unikalna możemy skorzystać z funkcji GENERATE_UNIQUE().
8
Po co wykorzystywać IDENTITY albo
SEQUENCE?
także dla WYDAJNOŚCI !
db2look -d baza1 -e -t osoba
/.../
"ID_OSOBY" BIGINT NOT NULL
GENERATED ALWAYS AS IDENTITY (
START WITH +100
INCREMENT BY +1
MINVALUE +100
MAXVALUE +9223372036854775807
NO CYCLE
CACHE - generacja kolejnej paczki jest
CACHE 20
zapisywana do logu. Kluczowe dla wydajności!
NO ORDER,
"IMIE" VARCHAR(50) NOT NULL ,
"NAZWISKO" VARCHAR(50) NOT NULL
Atrybut IDENTITY oraz sekwencje przede wszystkim ułatwiają tworzenie aplikacji, ale
także są dużo bardziej wydajne, niż własnoręczne metody generowania wartości.
Kluczowym atrybutem wpływającym na wydajność generowanych sekwencji jest
CACHE. Silnik bazy danych przygotowuje w pamięci paczkę wartości, które następnie
rozdziela pomiędzy aplikacje (klauzula NO ORDER oznacza, że aplikacje niekoniecznie
muszą dostawać generowane numerki w określonej kolejności – wtedy jest jeszcze
szybciej).
9
Tworzymy tabelę ADRES
create table adres (
id_osoby bigint not null,
miejscowosc varchar(80),
ulica varchar(80),
nr_domu varchar(15),
kod_pocztowy char(5)
);
alter table adres
add constraint adres_id_osoby_fk foreign key(id_osoby)
references osoba(id_osoby)
on delete cascade
on update restrict;
alter table adres
add constraint adres_id_uq unique (id_osoby);
comment on table adres is 'Powiazane z tabela osoba';
Tworząc klucz obcy z klauzulą on delete cascase wymuszamy usuwanie rekordów
zależnych, przy usunięciu rekordu głównego.
Komentarze (instrukcja comment) opisujące znaczenie tabel i kolumn są
przechowywane w słowniku systemowym bazy danych – warto z nich korzystać, gdyż
schemat bazy danych staje się bardziej czytelny.
Struktura tabel osoba, adres:
10
Tworzymy procedurę
3
2
1
Jeśli będziesz chciał tworzyć i śledzić procedury w narzędziu graficznym możesz
sięgnąć po DB2 Developer Workbench. Narzędzie zostało oparte o środowisko
Eclipse i jest bezpłatne, niezależnie od wykorzystywanej przez Ciebie edycji DB2.
1) Database Explorer – w tym oknie możesz nawiązać połączenie z bazą danych i
zobaczyć, jakie obiekty zostały w bazie utworzone.
2) Data Project Explorer – tu tworzysz projekty, w których będziesz mógł budować
procedury składowane, zapytania SQL oraz XQuery.
3) Okno pokazuje kod edytowanej procedury DODAJ_OSOBE. Procedurę możesz
wdrożyć na dowolnej bazie danych (Deploy…) i wykonać. Procedurę możesz także
debuggować po uruchomieniu odpowiedniej perspektywy.
DB2 Developer Workbench możesz pobrać z adresu:
http://ibm.com/db2/express
11
Tworzymy procedurę
dodaj_osobe
create procedure dodaj_osobe (
in p_imie varchar(50),
in p_nazwisko varchar(50),
in p_miejscowosc varchar(80),
in p_ulica varchar(80),
in p_nr_domu varchar(15),
in p_kod_pocztowy char(5)
)
specific dodaj_osobe
begin atomic
-- ciało procedury
-- wstawienie do tabel: osoba i adres
end
@
begin atomic -- blok nierozerwalny, traktowany jak pojedyncza,
choć złożona instrukcja
Jeśli tworzymy procedurę z interfejsu tekstowego, musimy przygotować plik z treścią
procedury, a na końcu procedury musimy umieścić unikalny znak końca instrukcji (lub
dwa znaki, w przykładzie znak @). Procedura składowana jest przykładem tzw. złożonej
instrukcji SQL.
Utworzenie procedury z tak przygotowanego skryptu: db2 -td@ -f twój_plik (opcje: -t =
terminated, -d = defined). Oczywiście wcześniej musisz połączyć się z bazą danych.
W procedurze zdefiniowana sześć parametrów wejściowych. Nazwy parametrów
poprzedzono przedrostkiem „p_” by później ułatwić rozróżnienie parametrów od
zmiennych oraz nazw obiektów SQL.
Blok:
begin atomic
Instrukcja 1
Instrukcja 2
end
traktowany jest jak jedna złożona instrukcja, co oznacza, że wszystkie instrukcje SQL w
bloku wykonają się w całości, albo się nie wykonają w ogóle. Jeśli wystąpi błąd, i błąd
ten nie będzie obsłużony, wtedy żadna z elementarnych instrukcji zawartych w bloku nie
wykona się. Działanie bloków nierozerwalnych można odnieść np. do pojedynczej
instrukcji, która usuwa wiele rekordów z bazy (np. delete from tab1 where id > 1000). W
wyniku działania takiej instrukcji usuną się wszystkie rekordy spełniające kryterium
usuwania, albo żaden jeśli dla dowolnego rekordu wystąpi błąd.
12
Ciało procedury
dodaj_osobe
begin atomic
declare v_id_osoby bigint;
select id_osoby into v_id_osoby from new table(
insert into osoba(imie,nazwisko)
values (p_imie, p_nazwisko)) as t;
insert into adres
(id_osoby,miejscowosc,ulica,nr_domu,kod_pocztowy)
values (v_id_osoby,p_miejscowosc,p_ulica,
p_nr_domu, p_kod_pocztowy);
end
@
Dzięki wykorzystaniu bloku nierozerwalnego możemy wstawić w jednej logicznej części
osobę i adres. Mamy gwarancję, że jeśli wystąpi błąd przy wstawianiu adresu to
wstawienie osoby zostanie automatycznie anulowane. Zwróć uwagę, że nie jest to
wycofanie transakcji (rollback). Na zewnątrz bloku można umieścić instrukcję COMMIT
bądź ROLLBACK, która będzie obejmowała także instrukcje wywołane przed
uruchomieniem procedury (bloku). W programach funkcjonalność podobną do BEGIN
ATOMC można uzyskać poprzez punkty zapisu transakcji (SAVEPOINT, ROLLBACK
TO SAVEPOINT).
Dla pseudo kodu poniżej: OK oznacza poprawne wykonanie, BŁĄD oznacza
zakończenie instrukcji z błędem.
instrukcja1; -- OK
begin atomic
instrukcja2; -- OK
instrukcja3; -- BŁĄD
instrukcja4;
end;
instrukcja5; -- OK
commit;
Transakcja niejawnie rozpoczyna się w momencie wykonania instrukcji1. Błąd, który
wystąpił w instrukcji3 spowoduje, że modyfikacje dokonane w instrukcji2 będą
anulowane. Instrukcja4 nie będzie wykonana ze względu na nieobsłużony błąd w
instrukcji3. Instukcja commit zatwierdzi modyfikacje z instukcji1 oraz instrukcji5.
13
Testowanie procedury
dodaj_osobe
call dodaj_osobe( 'Anna', 'Kalinowska', 'Nowa Iwiczna',
'Biała', '23/14', '12123 ');
commit;
call dodaj_osobe( 'Anna', 'Kalinowska', 'Nowa Iwiczna',
'Biała', '23/14', '12123 ');
Czy drugie wywołanie się powiedzie?
SQLCODE
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "2" constrains
table "PL38219.OSOBA" from having duplicate rows for those columns.
SQLSTATE=23505
SQLSTATE
O jaką regułę spójności chodzi?
Powtórne wywołanie procedury dodaj_osobę () z tymi samymi argumentami nie
powiedzie się, ze względu na wcześniej zdefiniowaną regułę niepowtarzalności. Baza w
komunikacie o błędzie podaje kod błędu oraz SQLSTATE. Kod błędu ma charakter
specyficzny dla DB2, natomiast SQLSTATE ma charakter ogólny (standard ANSI) i jest
zgodny z bazami danych innych producentów.
Zwróć uwagę na wyjaśnienie kodu błędu.
14
Sprawdzenie błędu
dodaj_osobe
db2 ? SQL0803N
db2 ? 23505
Sprawdzenie błędu
SELECT INDNAME, INDSCHEMA, COLNAMES
FROM SYSCAT.INDEXES
WHERE IID = 2
AND TABSCHEMA = 'PL38219'
AND TABNAME = 'OSOBA' ;
Wynika z opisu błedu
PL38219.OSOBA_NAZ_UQ (+NAZWISKO+IMIE)
Informacja o naruszonym warunku
Podając w linii poleceń tekstowych znak zapytania i kod błędu bądź SQLSTATE
możemy szybko uzyskać opis błędu. Zwróć uwagę, że komunikat błędu dla SQLSTATE
jest bardzo lakoniczny.
db2 ? 23505
SQLSTATE 23505: Wystąpiło naruszenie ograniczenia narzuconego przez indeks
unikalny lub ograniczenie przez unikalność (UNIQUE).
Dużo dokładniejszy opis błędu można uzyskać wykorzystując kod błędu, który będzie
zawierał także szczegółowe instrukcje objaśniające, jak dowiedzieć się która reguła
integralnościowa została naruszona. W opisie wygenerowanym poleceniem db2 ?
SQL0803N podane zostało zapytanie pozwalające na identyfikację nazwy naruszonej
reguły niepowtarzalności.
15
Sprawdzenie błędu w proced.
dodaj_osobe
Modyfikujemy procedurę (trochę inaczej☺):
-jeśli OSOBA istnieje to uaktualnij ADRES
begin atomic
declare v_id_osoby bigint;
zakres działania handler-a
begin
declare continue handler for sqlstate '23505'
begin
select id_osoby into v_id_osoby from osoba
where imie = p_imie and nazwisko = p_nazwisko;
end;
select id_osoby into v_id_osoby from
new table(insert into osoba(imie,nazwisko)
values (p_imie, p_nazwisko)) as t;
end;
[..]
end @
W powyższym przykładzie najpierw próbujemy wstawiać nowy rekord i jeśli okaże się,
że dany rekord już istnieje (to samo imię i nazwisko), wtedy pobieramy z bazy id_osoby.
W ciele procedury zdefiniowaliśmy handler, czyli kod obsługi wyjątków, który będzie
wywołany, jeśli wystąpi dany błąd. Handler typu „kontynuuj” oznacza, że po
wystąpieniu błędu zostanie wywołany kod obsługi błędu (na rysunku na niebiesko), a
następnie procedura zostanie kontynuowana od instrukcji, gdzie wystąpił błąd.
Zwróć uwagę na zakres działania handlera. Gdybyśmy zdefiniowali exit handler, wtedy
po wykonaniu kodu handlera, opuścilibyśmy blok begin … end, a następnie dalej
kontynuowalibyśmy wykonywanie procedury, ale od pierwszej instrukcji znajdującej się
po bloku, z którego wyszliśmy. Innymi słowy, handler odnosi się do danego bloku, a nie
całej procedury. Exit handler wychodzi z bloku begin..end a nie z całej procedury. Zwróć
uwagę, że w naszym przykładzie wykorzystaliśmy dodatkowy, zagnieżdżony blok.
Jeszcze jedna uwaga: deklaracje handlerów, jak i deklaracje zmiennych dopuszczane są
tylko na początku bloku (zaraz po begin, a przed jakąkolwiek instrukcją SQL).
16
Zakres działania handlera
begin atomic
declare v_id_osoby bigint;
begin
declare continue handler for sqlstate '23505'
[..]
end;
Na razie nie modyfikujmy tej części kodu
insert into adres
(id_osoby,miejscowosc,ulica,nr_domu,kod_pocztowy)
values (v_id_osoby,p_miejscowosc,p_ulica,
p_nr_domu, p_kod_pocztowy);
end @
Czy teraz powtórne wywołanie procedury z tymi samymi argumentami
powiedzie się?
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "PL38219.ADRES" from having duplicate rows for those columns.
SQLSTATE=23505
Gdybyśmy teraz chcieli wykonać tak utworzoną procedurę z tymi samymi argumentami
jak wcześniej, dostaniemy znów błąd związany z naruszeniem reguły niepowtarzalności,
ale tym razem już innej niż poprzednio (reguły mówiącej, że dana osoba może mieć
tylko jeden adres – sprawdź schemat tabeli adres ze wcześniejszych slajdów).
17
Uaktualnienie adresu
merge into adres using
table(
values ( v_id_osoby, p_miejscowosc,
p_ulica, p_nr_domu, p_kod_pocztowy)
) tmp (id_osoby,miejscowosc,ulica,nr_domu,kod_pocztowy)
Zbiór wierszy, zwykle wynik SELECT-a
on ( adres.id_osoby = tmp.id_osoby)
when matched then
update set (miejscowosc, ulica, nr_domu, kod_pocztowy) =
(tmp.miejscowosc,tmp.ulica,tmp.nr_domu,tmp.kod_pocztowy)
when not matched then
insert (id_osoby,miejscowosc,ulica,nr_domu,kod_pocztowy)
values (tmp.id_osoby,tmp.miejscowosc,tmp.ulica,
tmp.nr_domu, tmp.kod_pocztowy);
By nie dostawać błędu związanego z istnieniem danego adresu przyjmijmy, że w takiej
sytuacji uaktualnimy dane adresowe. Można to zrobić jedną instrukcją SQL MERGE,
która pozwala scalać nie tylko jeden rekord ale wiele rekordów z tabelą docelową. Po
słowie kluczowym using można podać nazwę tabeli, albo zapytanie SQL.W przykładzie
podaliśmy konstrukcje table, która tworzy wirtualną tabelę składającą się z jednego
rekordu zbudowanego na podstawie parametrów wejściowych procedury:
table(
values ( v_id_osoby, p_miejscowosc, p_ulica, p_nr_domu, p_kod_pocztowy)
) tmp (id_osoby,miejscowosc,ulica,nr_domu,kod_pocztowy)
Rekord ten będzie scalony z tabelą adres. Jeśli rekord o id_osoby będzie istniał w tabeli
adres, wtedy wykona się uaktualnienie rekordu, jeśli nie będzie istniał, wtedy zostanie
wstawiony nowy rekord. Klauzulę table(..) możemy zastąpić zapytaniem na specjalnej
tabeli systemowej, która zawiera dokładnie jeden rekord:
( select v_id_osoby, p_miejscowosc,
p_ulica, p_nr_domu, p_kod_pocztowy
from sysibm.sysdummy1 )
Klauzula values dokonuje po prostu ewaluacji wyrażenia, np.: values 10.1+1 zwróci
11.1.
18
Wstawienie osoby - lepiej
begin atomic
declare v_id_osoby bigint default null;
select id_osoby into v_id_osoby
from osoba
where imie = p_imie and nazwisko = p_nazwisko;
if v_id_osoby is null then
select id_osoby into v_id_osoby from
new table(insert into osoba(imie,nazwisko)
values (p_imie, p_nazwisko)) as t;
end if;
[..]
Dlaczego lepiej niż sprawdzanie błędu?
Ponieważ nieudana próba wstawienia niepotrzebnie
zwiększa licznik pola generowanego i ogólnie jest kosztowna.
Oczywiście fragment kodu związany ze sprawdzeniem, czy dana osoba już istnieje
najlepiej poprawić, tak by nie wywoływać obsługi błędu, która z natury jest kosztowna.
Dużo lepiej jest sprawdzić na wstępie, czy rekord istnieje, a dopiero potem ewentualnie
go wstawić (chyba, że sytuacja, w której wstawiamy osobę, która istnieje już w bazie
występuje niezmiernie rzadko).
Przy wstawianiu osoby i pobraniu wygenerowanego identyfikatora wykorzystaliśmy
klauzulę „zapytanie ze wstawienia”.
19
A co jeśli ktoś wprowadzi imię
dużymi literami?
call dodaj_osobe( 'Stanisława', 'Kowalska', ...
call dodaj_osobe( 'Stanisława', 'KOWALSKA', ...
alter table osoba
drop constraint osoba_naz_uq;
alter table osoba
add column imie_szuk varchar(50) not null
generated always as ( ucase( imie) );
alter table osoba
add column nazwisko_szuk varchar(50) not null
generated always as ( ucase( nazwisko) );
Teraz warunek unikalności dotyczy dużych liter
alter table osoba
add constraint osoba_naz_uq
unique (nazwisko_szuk,imie_szuk)
Tak jednak nie pójdzie A gdyby był warunek biznesowy mówiący, że nie możemy wstawić drugi raz osoby o
tym samym imieniu i nazwisku, porównując dane dużymi literami?
Rozwiązanie: wystarczy dodać pole generowane, które przechowuje imię i nazwisko
dużymi literami a następnie założyć regułę niepowtarzalności na tak utworzonych
polach.
Pokazane na slajdzie instrukcje jednak nie zadziałają, ponieważ na czas dodania pola
generowanego należy wyłączyć sprawdzanie integralności i przegenerować wartości dla
istniejących rekordów (patrz następny slajd).
20
Teraz lepiej
alter table osoba
drop constraint osoba_naz_uq;
set integrity for osoba off;
alter table osoba
add column imie_szuk varchar(50) not null
generated always as ( ucase( imie) );
alter table osoba
add column nazwisko_szuk varchar(50) not null
generated always as ( ucase( nazwisko) );
set integrity for osoba, adres immediate checked force
generated;
Tabela adres jest powiązana kluczem
z tabelą osoba, stąd wymienione są dwie tabele
alter table osoba
add constraint osoba_naz_uq unique
(nazwisko_szuk,imie_szuk);
Tak wygląda poprawny ciąg instrukcji SQL, pozwalający dodać nowe pole i
wygenerować dla niego wartości.
Zwróć uwagę na instrukcję, która włącza sprawdzanie integralności:
set integrity for osoba, adres immediate checked force generated;
Integralność należało włączyć nie tylko dla tabeli osoba, ale także dla tabeli adres.
Dlaczego? Dlatego, iż tabele są powiązane ze sobą regułami referencyjnymi (klucz
podstawowy – klucz obcy). Wyłączając integralność tabeli osoba automatycznie
wyłączyliśmy integralność tabel powiązanych (tu: adres). By sprawdzić, które tabele są
w stanie wyłączenia sprawdzania integralności, najlepiej sięgnąć do systemowego
słownika bazy danych (system catalog).
21
Modyfikacja kodu procedury
- obsługa dużych liter
begin atomic
declare v_id_osoby bigint default null;
select id_osoby into v_id_osoby
from osoba where ucase(imie) = ucase(p_imie) and
ucase(nazwisko) = ucase(p_nazwisko);
[..]
A co z wydajnością?
Teraz sprawdzimy, czy osoba istnieje w bazie danych w następujący sposób: Funkcją
UCASE podniesiemy do dużych liter zmienne będące argumentami procedury, a
następnie porównamy je zawartością pól imię i nazwisko podniesionych do dużych liter
także funkcją UCASE.
Pewnie zadajesz sobie pytanie, „a co z wydajnością?”. Czy baza nie będzie nie będzie
musiała sekwencyjnie odczytać całą tabelę (by podnieść do dużych liter)? Odpowiedź na
następnych slajdach.
22
Plan wykonania zapytania dla pola
generowanego (tekstowo)
db2 -tf "%DB2PATH%\misc\EXPLAIN.DDL"
Utworzenie tabel dla wyjaśnionych zapytań
set current explain mode = EXPLAIN;
Zapytania będą wyjaśniane oraz nie wykonywane
select id_osoby from osoba
where ucase(imie) = ucase('Jerzy') and
ucase(nazwisko) = ucase('Pasula');
Plan zapytania jest zapisany w utworzonych tabelach
db2exfmt -d %DBNAME% -e %USERNAME% -n %% -s %% -g
-o %1.exfmt -w -1 -# 0
TIC
Prezentowanie planów zapytań w formacie tekstowym
To, jak baza danych wykonuje zapytanie najlepiej sprawdzić przy pomocy tzw. planu
wykonania zapytania. W środowisku graficznym DB2 Centrum Sterowania jest to
trywialne (klikasz prawym klawiszem na bazą danych i wybierasz Wyjaśnij zapytanie).
W środowisku tekstowym będziesz musiał najpierw utworzyć zestaw tabel, w których
będą pamiętane wyjaśnione plany zapytań (narzędzie graficzne robi to za Ciebie). Jeśli
wykonasz następującą instrukcję:
set current explain mode = EXPLAIN
wtedy zapytania dla bieżącej sesji będą tylko wyjaśniane (bez wykonywania). Plan
wykonania zapytania zostanie przechowany we wcześniej utworzonych (skryptem
EXPLAIN.DDL) tabelach.
Narzędzie db2exfmt podłącza się do bazy danych, sięga do tabel z wyjaśnionymi
zapytaniami i formatuje wyniki zapisując je do pliku tekstowego. W przykładzie
wykorzystano zmienne ustawione w systemie Windows (tak naprawdę jest to fragment
skryptu):
%DBNAME% – nazwa bazy danych
%USERNAME% – nazwa użytkownika
%1.exfmt – plik, do którego będzie zapisany plan wykonania zapytania.
W systemach Linux/UNIX użyj pojedynczego znaku % zamiast %%.
23
exfmt.ksh -- skrypt dla Linuksa/UNIX
if [[ $1 = '' ]]; then
echo „Brak argumentow: <plik SQL> <poziom optymalizacji>"
exit
fi
DBNAME="BAZA1"
QF=${1:-'sql.filename'}
QO=${2:-'7'}
db2 set current query optimization = $QO
db2 set current explain mode = EXPLAIN
db2 -tvf $QF
db2exfmt -d ${DBNAME} -e ${USER} -n % -s % -g
$QF.exfmt -w -1 -# 0
TIC -o
db2 set current explain mode = NO
Oto przykładowy skrypty shell-owy exfmt.ksh dla systemów Linux/UNIX, który można
wykorzystać do generowania planu wykonania zapytania. Skrypt tworzy plik ze
sformatowanym planem zapytania o takiej nazwie, jak plik zawierający analizowane
zapytanie, z doklejonym rozszerzeniem .exfmt
Przykład wywołania:
exfmt.ksh zapytanie.sql 5
Plik wynikowy: zapytanie.sql.exfmt
Oto odpowiednik skryptu dla systemu Windows: exfmt.bat (zmień nazwę bazy danych
wskrypcie)
set DBNAME=BAZA1
set OPTLEVEL=%2
db2 set current query optimization = %OPTLEVEL%
db2 set current explain mode = EXPLAIN
db2 -tvf %1
db2exfmt -d %DBNAME% -e %USERNAME% -n %% -s %% -g TIC -o %1.exfmt -w
-1 -# 0
db2 set current explain mode = NO
24
Plan wykonania zapytania
- skrypt exfmt.bat
Rows
RETURN
( 1)
Original Statement
Cost
I/O
select id_osoby from osoba
|
where ucase(imie) = ucase('Jerzy') and
1
ucase(nazwisko) = ucase('Pasula')
FETCH
( 2)
12.871
1
/----+---\
1
18
IXSCAN TABLE: PL38219
Optimized Statement
( 3)
OSOBA
0.0165298
SELECT Q1.ID_OSOBY AS "ID_OSOBY"
0
FROM PL38219.OSOBA AS Q1
|
WHERE (
18
Q1.NAZWISKO_SZUK = UCASE('Pasula')) AND
INDEX: PL38219
(Q1.IMIE_SZUK = UCASE('Jerzy'))
OSOBA_NAZ_UQ
Oto plan wykonania zapytania wyszukującego imię i nazwisko dużymi literami. Zwróć
uwagę, że DB2 wykorzystało indeks OSOBA_NAZ_UQ. Na grafie węzeł (3) odszukuje
dany rekord z wykorzystaniem indeksu, a następnie doczytuje z tabeli pozostałe kolumny
– węzeł (2). Analizując plan wykonania zapytania zwróć uwagę na zoptymalizowaną
postać zapytania. DB2 przepisało zapytanie do postaci, w której sięga do pól
generowanych (pola nazwisko_szuk, imie_szuk) i dlatego mogło wykorzystać indeks,
który był na nich założony.
Jak czytać graf? Zwróć uwagę, że korzeń grafu zawiera wyjaśniającą legendę. Czytając
graf analizuj, w których gałęziach drzewa przyrasta koszt.
Liczba zwróconych wierszy
1
FETCH
( 2)
12.871
1
Wykonywana operacja
Numer węzła, do którego nawiązuje dalsza część raportu
Umowny koszt (jednostka: timeron) służący do porównywania
różnych planów wykonania tego samego zapytania
Umowny koszt operacji dyskowych
IXSCAN
- oznacza odczytanie wewnętrznego adresu rekordu z wykorzystaniem indeksu.
FETCH
- doczytuje pozostałe kolumny, dla rekordu o danym adresie wewnętrznym.
25
Co ma wpływ na optymalizację?
Database Context:
---------------Parallelism:
CPU Speed:
[..]
Package Context:
--------------SQL Type:
Optimization Level:
[..]
None
3.778754e-007
Dynamic
5
wynik działania exfmt.bat
STATYSTYKI
INDEKSY
FRAGMENTACJA DANYCH
REGUŁY INTEGRALNOŚCIOWE
Jest wiele rzeczy, które wpływają na optymalizację zapytań. Analizując plik zawierający
sformatowany schemat obsługi zapytania zwróć uwagę na kontekst bazy danych oraz
kontekst zapytania – znajdziesz tam nazwy parametrów bazy danych, które mają wpływ
na sposób optymalizacji. Bardzo ważnym parametrem jest także poziom optymalizacji.
Poziom optymalizacji można ustawić niezależnie dla każdego zapytania, bądź
akceptować domyślną wartość definiowaną na poziomie bazy danych (dft_queryopt).
Bardzo ważnym, o ile nie najważniejszym, czynnikiem są statystyki dla optymalizatora,
czyli informacje o samych danych. Statystyki zawierają nie tylko informacje o ilości
rekordów, czy o rozkładzie wartości, ale także informacje o tym jak dane rozkładają się
na dyskach (jak są pofragmentowane). Jeśli np. defragmentowałeś tabelę (polecenie
REORG), wtedy także watro zebrać statystyki.
26
Zmienne sterujące kompilatorem SQL
set current query optimization = 1
Poziom optymalizacji.
Jaki ustawić?
DB2_ANTIJOIN
DB2_HASH_JOIN
DB2_INLIST_TO_NLJN
DB2_LIKE_VARCHAR
DB2_MINIMIZE_LISTPREFETCH
DB2_NEW_CORR_SQ_FF
DB2_PRED_FACTORIZE
DB2_REDUCED_OPTIMIZATION
DB2_SELECTIVITY
DB2_SQLROUTINE_PREPOPTS
Zmienne sterujące kompilatorem
Poziom optymalizacji można ustawić w aplikacji instrukcją SQL: set current query
optimization. Zwykle dla zapytań o charakterystyce OLTP, czyli takich, które pobierają
(aktualizują) niewielką liczbę rekordów bardzo dobre są niskie poziomy optymalizacji (0
lub 1), ponieważ baza powinna poświęcać stosunkowo mało czasu na samą
optymalizację. Niskie poziomy dostępu preferują dostęp z wykorzystaniem indeksów
(uproszczony model kosztowy). Wyższe poziomy optymalizacji (5,7) stosowane są
głównie w zapytaniach analitycznych, które z natury wykonują się długo, dlatego też
warto je optymalizować.
Specjalnymi zmiennymi rejestrowymi można określić także algorytmy, jakie DB2 ma
uwzględniać przy optymalizacji. Zmienne ustawia się narzędziem db2set, ale można je
także ustawić w środowisku systemu operacyjnego. Po ustawieniu danej zmiennej należy
powtórnie wystartować instancję DB2. Przykładowo zmienna DB2_HASH_JOIN,
określa czy baza danych ma wykorzystywać funkcję haszującą do wykonywania złączeń.
Warto zaznaczyć, że zmienne sterujące optymalizatorem ustawia się względnie rzadko.
Optymalizator DB2 działa w oparciu o dojrzały model kosztowy, rozwijany od przeszło
20 lat. W innych bazach danych bardzo popularną techniką jest częste stosowanie tzw.
podpowiedzi (hints), czyli dyrektyw, które określają w jaki sposób baza danych ma
wykonywać zapytanie. W DB2 takie techniki nie są praktykowane, choć istnieje
możliwość zewnętrznego skorygowania planu wykonania zapytania. Przygotowuje się
profil optymalizacyjny w postaci pliku XML, w którym podaje się treść zapytania oraz
dyrektywy optymalizacyjne. Wczytywany przez bazę plik pozwala na korektę działania
aplikacji bez modyfikacji kodu aplikacji.
27
Dlaczego statystyki są tak ważne?
To jest komenda administracyjna
runstats on table pl38219.osoba with
distribution on key columns and indexes all
set profile only
runstats on table pl38219.osoba use profile
runstats on table pl38219.osoba with
distribution on all columns
tablesample system(20)
update profile only
util_impact_priority 10
Zbieranie statystyk da się zautomatyzować.
Komentarz do slajdu z poprzedniej strony:
Wykonując zapytanie SQL możemy podać, czy chcemy by DB2 szybko dostarczyło
pierwszy rekord, albo optymalizowało pracę na dostarczenie całego zbioru wynikowego.
Dla zapytania:
select … optimize for 1 row
pierwszy rekord dostarczony zostanie szybko – taka konstrukcja szczególnie jest
przydatna, jeśli pobieramy kilka pierwszych rekordów z posortowanego zbioru.
□
Do zbierania statystyk służy polecenie runstats (dostępne w C poprzez API bazy
danych). Bardzo wygodną metodą zbierania statystyk jest przygotowanie profilu
statystyk. Profil statystyk to nic innego jak odpowiednie opcje polecenia runstats
przechowane w słowniku bazy danych, dla każdej tabeli z osobna. Operator
odpowiedzialny za zbieranie statystyk używa później dokładnie tej samej składni dla
każdej tabeli: runstats … use profile.
W przypadku dużych tabel bardzo przydatne są opcje pozwalające zbierać statystyki na
próbce danych (tablesample), oraz pozwalające określić priorytet, czyli procent
wykorzystania procesora (util_impact_priority).
W domyślnej konfiguracji bazy danych w wersji 9 statystyki zbierane są automatycznie .
Jeśli tabela zostanie zmodyfikowana na tyle, że może to wpłynąć na poziom
optymalizacji, wtedy baza zgłasza żądanie zebrania statystyk. Statystyki są zbierane w
najbliższym czasowym oknie administracyjnym, które może określić administrator.
Automatyczne zbieranie statystyk można włączyć/wyłączyć parametrami bazy danych:
auto_maint, auto_tbl_maint, auto_runstats, choć najprościej można to zrobić przy
pomocy Centrum Sterowania i opcji „konserwacja bazy danych”.
28
Jak sprawdzić czy statystyki były
zebrane?
select tabname, tabschema, stats_time
from syscat.tables
Jak zwykle takie informacje
są dostępne poprzez widoki
systemowe SYSCAT
A co jeśli tabela zmienia swoją zawartość tak często, że statystyki nigdy
nie są aktualne?
alter table dane volatile
Czy da się zebrać statystyki z aplikacji?
call admin_cmd( 'runstats on table
pl38219.osoba')
By sprawdzić, czy statystyki były zebrane dla danej tabeli wystarczy wykonać
odpowiednie zapytanie na słownikach systemowych.
Jeśli tabele zawierają dane przejściowe (ładujemy dane a później je usuwamy), wtedy
statystyki praktycznie zawsze będą nieaktualne. W takiej sytuacji należy tabelę
opatrzyć atrybutem VOLATILE (ulotny) – baza danych będzie preferowała indeksy w
dostępie do takiej tabeli i nie będzie uwzględniała statystyk.
Warto jeszcze wspomnieć o tabelach przechowujących różnego rodzaje logi aplikacji.
Dla takich tabel najlepiej wykonać:
ALTER TABLE tabela1 APPEND ON
Wtedy dane będą dopisywane na końcu tabeli, co będzie działało szybciej, ponieważ
baza nie będzie poświęcała czasu na wyszukiwanie wolnego miejsca w środkowych
obszarach tabeli (baza inaczej optymalizuje wstawianie danych).
□
Statystyki można zebrać także poprzez wywołanie procedury admin_cmd (instrukcja
SQL a nie polecenie administracyjne).
29
Zbieranie statystyk a transakcja
Zebranie statystyk dokonywane jest
w transakcji. Dopiero zatwierdzenie transakcji
spowoduje uaktualnienie katalogów systemowych.
db2 +c call admin_cmd( 'runstats on table
pl38219.osoba')
db2 commit
Ważne dla tabel tymczasowych
Zwróć uwagę, że statystyki zapisywane są do słownika systemowego w momencie
zatwierdzania transakcji.
30
Czy można samemu uaktualnić
statystyki?
- TAK !
Do symulacji środowiska produkcyjnego,
bez konieczności kopiowania danych.
- Ale po co???
db2look -d baza1 -e -m -t tab1
system produkcyjny
system deweloperski
Narzędziem db2look (które głównie wykorzystywane jest do wygenerowania schematu
bazy – DDL) można także wyeksportować statystyki do pliku. db2look z opcją -m
generuje odpowiedni skrypt z instrukcjami UPDATE, które mogą potem posłużyć do
uaktualnienia statystyk na innym systemie.
Przenoszenie statystyk wykorzystywane jest głównie w sytuacji, jeśli w środowisku
testowym chcemy odtworzyć zachowanie systemu produkcyjnego. Zbieramy statystyki
na produkcji i przenosimy je na system testowy. Optymalizator DB2 zachowuje się tak
samo, jak na systemie produkcyjnym, mimo iż system testowy zawiera zupełnie inne
dane (trzeba pamiętać, by wyłączyć automatyczne zbieranie statystyk na systemie
testowym!).
31
DB2 nie używa indeksu.
Po reorganizacji już używa, dlaczego?
reorgchk current statistics on table all
reorg table pl38219.staff allow read access
Trzeba mieć dużo miejsca w przestrzeni tymczasowej
reorg table pl38219.staff inplace allow write
access start
reorg table pl38219.staff inplace pause
list history reorg all for sample
Ta reorganizacja jest typu ONLINE - nie tworzy dużych
obiektów tymczasowych
Jeśli w zapytaniu pytasz się o 5% danych, ale dane są tak pofragmentowane, że do
realizacji takiego zapytania należy przetworzyć 80% stron z danymi (dane pasujące do
twojego zapytania rozrzucone są na wiele stron), wtedy DB2 nie skorzysta z indeksu,
ponieważ mniej kosztowny jest sekwencyjny odczyt całej tabeli.
W takiej sytuacji pomaga reorganizacja danych. Jedną z metod jest reorganizacja offlineowa -- tabela udostępniana jest tylko do odczytu a system robi kopię tabeli (w obszarze,
tam gdzie jest tabela, bądź we wskazanym obszarze tymczasowym).
Reorganizacja online (składnia inplace) działa małymi porcjami, jest asynchroniczna,
można ją wstrzymywać i wznawiać później, i co najważniejsze pozwala na pełen odczyt
i zapis do reorganizowanej tabeli.
Poleceniem list history reorg możemy sprawdzić stan reorganizacji online-owej.
Poleceniem reorgchk możemy sprawdzić, czy dana tabela potrzebuje reorganizacji.
Narzędzie analizuje statystyki i przygotowuje raport. W raporcie zwróć uwagę na
ostatnie kolumny, które zawierają współczynniki mówiące, czy tabela (indeks) nadaje się
do reorganizacji. Dla tabel prezentowane są trzy współczynniki F1, F2, F3:
Tabela: DB2ADMIN.ADRES
… F1
F2
F3 REORG
…
63
99 -*-
0
Gwiazdka na drugiej pozycji oznacza, ze tabele powinna być poddana reorganizacji ze
względu na współczynnik F2 . W nagłówku raportu współczynniki są opisane:
F2: 100 * (Efektywne wykorzystanie obszaru stron danych) > 70
Wartość F2 równa 63 oznacza, że tabela ADRES zawiera dużą ilość (37%) pustych
miejsc powstałych na skutek usuwania rekordów i powinna być zreorganizowana.
32
Jak sprawdzić czy dla mojego zapytania przydałby
się jakieś indeksy?
select imie, nazwisko
from osoba o inner join adres a
on o.id_osoby = a.id_osoby
where a.miejscowosc = 'Bydgoszcz';
plik: zapyt.sql
1) Sprawdź plan wykonania
2) Uruchom db2advis
db2advis -d sample -i zapyt.sql [-t #minutes] -o
rekomendacje.out
2) Zastosuj rekomendacje
korzysta z tabel utworzonych
przez EXPLAIN.DDL
4) Sprawdź plan wykonania
Narzędzie db2advis podpowiada, jaki indeksy warto utworzyć, by przyspieszyć działanie
danego zapytania (dokładniej zbioru zapytań – „workload”). Narzędzie w raporcie
przygotowuje listę najpotrzebniejszych indeksów, informując jednocześnie o
procentowej poprawie kosztu zapytania oraz miejscu jakie potencjalnie zajmie dany
indeks.
Zamiast pliku (opcja -i zapyt.sql) możemy podać opcję -g, która sięgnie do pamięci
podręcznej (cache) wykonywanych przez db2 zapytań. Pamięć podręczną możemy
wyczyścić poleceniem: flush package cache dynamic.
33
Przykładowy fragment raportu wygenerowanego
przez db2advis
Zalecanie indeksów...
łączne miejsce na dysku potrzebne dla zestawu początkowego: [
łączne miejsce na dysku ograniczone do [ 10,389] MB
Wypróbowywanie wariacji zestawu rozwiązań.
Optymalizacja ukończona.
liczba indeksów w bieżącym rozwiązaniu: 2
Liczba jednostek timeron: [2314,0000] (bez zaleceń)
liczba jednostek timeron: [801,0000] (w bieżącym rozwiązaniu)
poprawa: [65,38%]
4,486] MB
-- LISTA ZALECANYCH INDEKSÓW
-- ===========================
-- index[1], 1,837MB
CREATE INDEX "DB2ADMIN"."IDX610181743370000" ON "DB2ADMIN"."ADRES"
("MIEJSCOWOSC" ASC, "ID_OSOBY" ASC) ALLOW REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "DB2ADMIN"."ADRES" FOR INDEX "DB2ADMIN"."IDX610181743370000" ;
COMMIT WORK ;
-- index[2], 2,649MB
CREATE UNIQUE INDEX "DB2ADMIN"."IDX610181743560000"
ON "DB2ADMIN"."OSOBA" ("ID_OSOBY" ASC) INCLUDE ("NAZWISKO", "IMIE") ALLOW REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "DB2ADMIN"."OSOBA" FOR INDEX "DB2ADMIN"."IDX610181743560000" ;
COMMIT WORK ;
Po wykonaniu db2advis -d baza1 -i zapyt.sql -o rekomendacje.out narzędzie
zasugerowało dwa indeksy, jeden adres (miejscowość, id_osoby), oraz drugi osoba
(id_osoby) z opcją include (nazwisko, imie). Przygotowany raport musimy poddać
racjonalnej ocenie, uwzględniając korzyści, oraz zajmowane miejsce i koszt związany z
utrzymywaniem indeksu.
Pierwszy indeks ma sens ponieważ wyszukujemy po nazwisku. Do indeksu zostało
dołączone pole id_osoby, ponieważ pole to jest wykorzystywane przy złączeniu, co także
przyspieszy zapytanie, ponieważ baza nie będzie musiała sięgać do strony z danymi, by
pobrać id_osoby (wystarczy jeden odczyt z indeksu).
Drugi indeks właściwie już istnieje, ponieważ na polu id_osoby jest założony klucz
główny. DB2 w rekomendacji proponuje dołączyć do klucza indeksowego dwa
dodatkowe pola. Składnię INCLUDE można traktować dokładnie tak samo jak indeks
kompozycyjny (w naszym przypadku na trzech polach: id_osoby, nazwisko, imie), z tą
różnicą, że warunek unikalności będzie przestrzegany tylko na polu id_osoby.
Zwróć uwagę, że narzędzie podaje ile miejsca będzie zajmował indeks, oraz na jaką
poprawę wydajności można liczyć po założeniu ideksu. Po namyśle decydujemy się na
utworzenie pierwszego z proponowanych indeksów:
create index adres_miej_idx on adres ("MIEJSCOWOSC" asc, "ID_OSOBY" asc);
runstats on table db2admin.adres for index db2admin.adres_miej_idx;
Uwaga: nazwy obiektów w wygenerowanym skrypcie ujęte są w cudzysłów. Oznacza to,
że baza będzie traktowała nazwy z rozróżnieniem dużych i małych liter (w takiej postaci
są przechowywane w słownikach systemowych bazy danych). Jeśli będziesz
modyfikował skrypt to usuń cudzysłów albo pisz nazwy obiektów dużymi literami. Jeśli
nie użyjesz cudzysłowu baza przechowa nazwę obiektu w słownikach dużymi literami.
34
Rows
Rows
RETURN
RETURN
(
1)
(
1)
Cost
Cost
I/O
I/O
|
|
51,9354
51,8829
NLJOIN
NLJOIN
(
2)
(
2)
2313,79
997,775
1985,23
851,58
/-------+------\
/------+------\
51,9354
1
51,8829
1
TBSCAN
FETCH
IXSCAN
FETCH
(
3)
(
9)
(
3)
(
4)
1331,19
22,7168
15,1885
22,7168
1135,82
3
2
3
|
/---+---\
|
/---+---\
51,9354
1
53311
53311
1
53311
SORT
IXSCAN
TABLE: DB2ADMIN
INDEX: DB2ADMIN IXSCAN
TABLE: DB2ADMIN
(
4)
( 10)
OSOBA
ADRES_MIEJ_IDX
(
5)
OSOBA
1331,18
15,1505
15,1505
1135,82
2
2
|
|
|
51,9354
53311
53311
FETCH
INDEX: DB2ADMIN
INDEX: DB2ADMIN
(
5)
OSOBA_ID_OSOBY_PK
OSOBA_ID_OSOBY_PK
1331,16
1135,82
/---+---\
Plany wykonania zapytań przed i po
53311
53311
utworzeniu pierwszego indeksu
RIDSCN
TABLE: DB2ADMIN
(
6)
ADRES
rekomendowanego
przez db2advis
444,681
286,808
|
Łączny koszt zapytania zmniejszył się z 2313.79 na 997.775. W zapytaniu przed
zastosowaniem rekomendacji największym czynnikiem kosztowym była konieczność
sekwencyjnego skanowania całej tabeli (lewa strona diagramu; węzły (3),(4),(6)…). Po
zastosowaniu rekomendacji wyszukanie miejscowości przestało być tak kosztowne, a
największym czynnikiem kosztowym stało się złączenie z tabelą osoba -- węzeł (2) (co
jest oczywiste, ponieważ w jednej miejscowości może mieszkać wiele osób). NLJOIN
(nested loop join) jest takim rodzajem złączenia, w którym dla każdego rekordu ze zbioru
wynikowego po lewej stronie doczytywany jest rekord po prawej stronie (na wzór pętli
zagnieżdżonej w innej pętli). Po utworzeniu drugiego indeksu proponowanego w
rekomendacjach (IDX610181743560000) koszt dodatkowo zmniejszy się, ponieważ
złączenie będzie realizowane bez konieczności sięgania do stron z danymi (odczyt tylko
z indeksu), tak jak prezentuje to diagram poniżej. Gdybyś dołączył do zapytania
dodatkowy warunek filtrujący dane po nazwisku, wtedy koszt byłby jeszcze mniejszy.
Pamiętaj, koszt danego zapytania zależy od rozkładu wartości. Dla danych
testowych, które wygenerowałem w jednej miejscowości mieszkało kilkadziesiąt osób.
W danych rzeczywistych możesz mieć tylko kilka miejscowości i setki tysięcy osób –
wtedy tworzenie indeksu na miejscowości nie będzie miało większego sensu. W sytuacji
kiedy dane są silnie zduplikowane przydaje się mechanizm wielowymiarowego
klastrowania MDC albo partycjonowanie danych.
Rows
RETURN
(
1)
Cost
I/O
|
51,8829
NLJOIN
(
2)
556,971
481,43
/------+------\
51,8829
1
IXSCAN
IXSCAN
(
3)
(
4)
15,1885
15,1507
2
2
|
|
53311
53311
INDEX: DB2ADMIN
INDEX: DB2ADMIN
ADRES_MIEJ_IDX
IDX610181743560000
35
Śledzenie procedury
www.ibm.com/developerworks
wyszukiwujemy "put_line"
"A UDF for File Output and Debugging from SQL"
Implementacja funkcji pozwalającej na zapisywanie komunikatów
do pliku z wywołań SQL
Jeśli będziesz chciał zapisywać komunikaty o wartości zmiennych do pliku, wtedy
najlepiej jak sięgniesz po gotową funkcję napisaną w C.W DB2 możesz pisać procedury
składowane w także w języku C i języku Java. Procedury napisane w języku C albo Java
określane są jako zewnętrzne, ponieważ tworzone są przy wykorzystaniu zewnętrznego
kompilatora. W wyniku kompilacji takiej procedury powstaje biblioteka, która jest
wykonywana przez motor bazy danych. Procedury SQL PL (SQL Procedural Language)
są interpretowane przez motor bazy danych (wewnętrzna kompilacja do pseudokodu).
Na stornach portalu deweloperskiego technologii IBM możesz bez problemu odszukać
kod funkcji put_line.
36
PUT_LINE - instalacja UDFa
1) Rozpakowujemy plik zip
2003-01-17
2003-02-24
2003-01-05
2003-01-17
2005-11-07
2003-02-24
2002-10-30
2003-01-17
17:32
19:46
20:05
22:57
22:13
20:59
15:28
17:28
1 153 crefunc.sql
13 638 put_line.c
138 put_line.def
77 877 PUT_LINE.dll
109 822 put_line.zip
30 096 put_line_w2k.ZIP
1 126 testcase.sqp
656 testcase2.sql
2) Rejestrujemy funkcję
db2 connect to baza1
copy put_line.dll "%DB2PATH%\function\."
db2 -tvf crefunc.sql
2) Definiujemy katalog
gdzie funkcja będzie tworzyła pliki poprzez ustawienie zmiennej w
systemie PUT_LINE_OUT na wartość, np. C:\temp
Start-> Settings-> Control Panel-> System
-> Advanced-> Environment Variables
Na przedstawionym slajdzie przedstawiłem kroki, które szybko pozwolą ci utworzyć
funkcję put_line. Dostarczony na stronie plik zawiera kody źródłowe oraz gotowe
biblioteki (skompilowane dla wersji DB2 8).
37
PUT_LINE - instalacja UDFa
4) Restartujemy instancję
db2stop force
db2start
5) Testujemy funkcję
db2 connect to baza1
db2 values put_line ('aaa','w','to jest tekst')
db2 values put_line ('aaa','a','to jest drugi tekst')
more aaa
to jest tekst
to jest drugi tekst
Inną techniką śledzenia jest zapisywanie danych diagnostycznych do osobnej tabeli, choć
niestety nie może to być tradycyjna tabela, ponieważ jeśli w procedurze wykonasz
wycofanie transakcji, wtedy dane zostaną także usunięte z takiej tabeli. Rozwiązaniem
jest utworzenie tabeli tymczasowej z klauzulami ON COMMIT PRESERVE ROWS i
ON ROLLBACK PRESERVE ROWS. Niezależnie od wykonywanych w procedurze
instrukcji COMMIT oraz ROLLBACK dane nie zostaną usunięte z tabeli tymczasowej.
Oto przykład:
declare global temporary table moj_log (id int, info varchar (150))
on commit preserve rows not logged on rollback preserve rows ;
Byś mógł tworzyć tabele tymczasowe administrator musi wcześniej utworzyć przestrzeń
tymczasową na dane użytkownika i nadać uprawnienie USE, które zezwala na tworzenie
tabel w takiej przestrzeni.
create user temporary tablespace usertemp1
managed by system using ('USERTEMP');
grant use of tablespace usertemp1 to public;
Tabele tymczasowe zawsze tworzone są w schemacie SESSION i dlatego też należy
podawać schemat operując na tabelach tymczasowych użytkownika, np.
insert into session.moj_log values (1, 'rekord') ;
select * from session.moj_log ;
38
Jak przekazać wartość?
- poprzez parametry
create procedure dodaj_osobe (
out p_id_osoby bigint,
[..]
set p_id_osoby = v_id_osoby;
end @
create procedure wstawiaj()
specific wstawiaj
begin
declare v_id_osoby bigint;
declare v_status varchar(1);
call dodaj_osobe( v_id_osoby, 'Dominik', 'Matejko',
'Bydgoszcz', 'Jasna', '100', '22345 ');
values put_line( 'wstawiaj.out', 'a', char( v_id_osoby) )
into v_status;
end @
Załóżmy, że nasza procedura dodała nową osobę wstawiając odpowiedni rekord to tabeli
osoba i tabeli adres. W aplikacji (np. zewnętrznej procedurze) możesz chcieć dowiedzieć
się jakie id_osoby zostało wygenerowane. Wygenerowany identyfikator można
przekazać na zewnątrz wykorzystując parametr typu OUT, tak jak pokazałem na slajdzie
(procedura dodaj_osobę).
W zewnętrznym programie (w naszym przypadku w procedurze wstawiaj) zdefiniuj
zmienną, którą podstaw pod parametr OUT w wywołaniu procedury dodaj_osobe.
Procedura dodaj_osobe ustawi wartość tej zmiennej na wygenerowany identyfikator.
39
Parametry procedury
CALL PROCEDURE1
IN
INOUT
OUT
IN
procedura nie zmieni wartości zmiennej wstawionej w to miejsce
(w jęz. C przekazywanie parametrów przez wartość)
OUT
procedura nie odczyta wartości zmiennej wstawionej w to miejsce
INOUT
procedura potrafi odczytać wartość zmiennej, ale także zmienić
wartość zmiennej (w jęz. C przekazywanie parametrów przez
wskaźnik)
W DB2 są trzy rodzaje parametrów procedur.
Parametr IN kopiuje wartość podanego argumentu (tworzy lokalną kopię). Modyfikacja
parametru IN jest dozwolona, choć nie wpłynie na wartość zmiennej podanej w
argumencie wywołania.
Parametr INOUT pracuje bezpośrednio na zadanym argumencie. Możemy go odczytać
ale i możemy zmienić jego wartość. Taki rodzaj parametru można porównać do
przekazywania parametrów przez wskaźnik w języku C.
Parametr OUT wyłącznie przekazuje wartość. Można go porównać do parametru
INOUT, który jest ustawiany na wartość nieokreśloną (tu: NULL) w momencie
uruchomienia procedury.
Jeśli chcesz przetestować wywołanie procedury mającej parametry IN oraz INOUT
wtedy pod wartość takiego parametru podaj znak zapytania (?). W przykładzie poniżej w
cele procedury P1 ustawiłem wartość parametru PAR1 na 923:
db2 call p1(?)
Wartości parametrów wyjściowych
-------------------------Nazwa parametru: PAR1
Wartość parametru: 923
Status powrotu= 0
40
Przekazanie zbioru wynikowego
create procedure zbior()
specific zbior
begin
declare kur1 cursor with return for
select id_osoby from osoba;
open kur1;
end @
Otwarcie kursora przekaże zbiór wynikowy
oparty na zapytaniu
Inną metodą przekazywania wyników w procedurze są tzw. zbiory wynikowe (result
sets). Procedura DB2 może zwrócić wiele niezależnych zbiorów wynikowych. W ciele
procedur definiujemy kursor z klauzulą with return i następnie otwieramy kursor.
Kursor nie może być zamknięty. Zewnętrzny program odbiera rekord po rekordzie
(fetch) zwrócony przez taką procedurę zbiór wynikowy.
W przykładzie procedura zbior przekazuje wynik zapytania select id_osoby from osoba.
Można sobie zadać pytanie, w jakim celu przekazywać zbiór wynikowy, skoro nadrzędny
program może samemu wykonać to zapytanie??
Jednym z zastosowań zbiorów wynikowych jest kontrola dostępu. Użytkownik
wykonujący procedurę nie musi posiadać uprawnienia do tabeli, wystarczy, że będzie
miał uprawnienie do wykonywania procedury (EXECUTE). W takiej sytuacji za
pośrednictwem procedury będzie mógł czytać dane z tabeli, do której nie ma
bezpośredniego dostępu.
41
Przekazanie zbioru wynikowego
create procedure zbior()
specific zbior
begin
declare v_id_osoby bigint;
set v_id_osoby = 999;
begin
declare kur1 cursor with return for
with tmp(id_osoby) as ( values (v_id_osoby) )
select id_osoby from tmp;
open kur1;
end;
end @
Tabela złożona z jednej wartości
Dlaczego blok begin-end jest konieczny?
Oto przykład prostej procedury, która buduje kursor na dynamicznie utworzonej
niejawnej tabeli tymczasowej. Konstrukcja with tworzy niejawną tabelę tymczasową tmp
(tzn. taką, która istnieje tylko na czas wykonywania tej instrukcji), która składa się z
jednego rekordu utworzonego na postawie zmiennej v_id_osoby (tu: 999).
Jak widać, można przekazywać zbiory wynikowe, które niekoniecznie budowane są na
podstawie zapytania na tradycyjnej tabeli.
Na slajdzie zadałem pytanie: „dlaczego blok begin-end jest konieczny?”. Odpowiedź jest
prosta: sekcja deklaracji musi wystąpić zaraz na początku bloku, po słowie kluczowym
begin. Wcześniej instrukcją set określiliśmy wartość zmiennej v_id_osoby, więc w tym
samym bloku nie mogliśmy zadeklarować kursora.
Zapytania rozpoczynające się od słowa kluczowego WITH określane są jako common
table expressions (CTE).
42
Trochę o Common table expressions
with temp AS (
select nazwisko, imie,
row_number() over( order by ucase(nazwisko) desc) AS nr
from osoba
)
select nazwisko, imie, nr from temp
where nr > 10
fetch first 5 rows only
optimize for 1 roW
;
Zapytania CTE pozwalają na przetwarzanie zbiorów wynikowych w z góry określonej
kolejności. Najpierw przetwarzane są dane wewnątrz wyrażenia with (na slajdzie kod na
niebiesko). Następnie przetwarzane jest zewnętrzne zapytanie select * from temp.
Działają dużo bardziej wydajnie niż zapytania skorelowane.
W przykładzie pole nr jest dynamicznie wygenerowane i zawiera koleje numery
przypisane rekordom z tabeli osoba, które byłyby posortowane po nazwisku malejąco. W
zewnętrznym zapytaniu pytamy się o te rekordy, dla który numer jest większy od 10.
Klauzula fetch first powoduje, że baza zwróci tylko paczkę pięciu rekordów i będzie
optymalizować zapytanie na jak najszybsze przekazanie początkowych rekordów, a nie
wykonanie wewnętrznego zapytania w całości. Przeanalizujmy plan wykonania
zapytania:
Wysoki koszt 11296,5 odnosi się do całości zapytania, a nie do
Rows
RETURN
(
1)
Cost
I/O
|
5
FILTER
(
2)
11269,5
1479
|
51591
FETCH
(
3)
11247,2
1479
/---+---\
51591
51591
IXSCAN
TABLE: DB2ADMIN
(
4)
OSOBA
4819,22
632
|
51591
INDEX: DB2ADMIN
OSOBA_NAZ_UQ
pobrania pierwszych pięciu rekordów. Jak widać DB2
wykorzystało indeks, który był utworzony na polu generowanym
– czyli jest dobrze.
Jeśli przeanalizujemy plan wykonania zapytania, a dokładniej
doczytamy szczegóły węzła (1), w którym opisane jest zwracanie
wyników:
1) RETURN: (Return Result)
Cumulative Total Cost:
Cumulative First Row Cost:
11269,5
24,8634
zauważymy, że koszt pozyskania pierwszego rekordu jest bardzo
niski (24). Zwrócenie więc pięciu rekordów tą metodą nie będzie
kosztowne i wykona się bardzo szybko.
43
Przekazanie zbioru wynikowego
- ciąg dalszy
create procedure zbior()
specific zbior
begin
declare global temporary table temp1 (id_osoby bigint)
on commit preserve rows with replace;
insert into session.temp1 values( 123);
insert into session.temp1 values( 456);
begin
declare kur1 cursor with return for
select id_osoby from session.temp1;
open kur1;
end;
end @
Trzeba wcześniej utworzyć przestrzeń tymczasową
dla danych użytkownika
Ten przykład pokazuje, ze zbiór wynikowy można zbudować w całości dynamicznie. W
cele procedury tworzymy tabelę tymczasową temp1. Ponieważ tabela tworzona jest z
klauzulą with replace, powtórne uruchomienie procedury usunie i utworzy na nowo
tabelę tymczasową temp1.
W cele procedury budujemy zawartość tabeli tymczasowej a następnie przekazujemy
zbiór wynikowy do nadrzędnej aplikacji.
44
Odebranie zbiorów wynikowych
DECLARE rs1 RESULT_SET_LOCATOR VARYING;
DECLARE rs2 RESULT_SET_LOCATOR VARYING;
CALL procedure1();
ASSOCIATE RESULT SET LOCATOR (rs1, rs2)
WITH PROCEDURE procedure1;
ALLOCATE cursor1 CURSOR FOR RESULT SET rs1;
ALLOCATE cursor2 CURSOR FOR RESULT SET rs2;
FETCH FROM cursor1 INTO var1, var2 ;
FETCH FROM cursor2 INTO var3 ;
Na slajdzie pokazałem metodę odebrania zbiorów wynikowych w nadrzędnej
procedurze.
Najpierw deklarujemy lokatory zbiorów wynikowych (declare)
Wykonujemy procedurę (call)
Skojarzamy lokatory z nazwą procedury (associate)
Alokujemy kursor dla każdego ze zbiorów wynikowych (allocate)
Pobieramy rekordy ze zbiorów wynikowych, w taki sam sposób jak w przypadku
zadeklarowanych kursorów (fetch).
Każdy z języków programowania może mieć inny zestaw funkcji do przechwytywania
zbiorów wynikowych.
45
Odebranie zbioru wynikowego
create procedure przejmij()
specific przejmij
begin
declare sqlstate char(5);
declare v_id_osoby bigint;
declare v_status varchar(1);
declare rs_zbior result_set_locator varying;
call zbior();
associate result set locator (rs_zbior)
with procedure zbior;
allocate kur1 cursor for result set rs_zbior;
fetch from kur1 into v_id_osoby;
while(sqlstate = '00000')
do
values put_line( 'przejmij.out', 'a',char( v_id_osoby) )
into v_status;
fetch from kur1 into v_id_osoby;
end while;
close kur1;
end
Przykład procedury przechwytującej zbiór wynikowy przekazanych przez procedurę
zbior, utworzoną na poprzednich slajdach.
46
Funkcja tabelaryczna
create function id_osoby()
returns table(id_osoby bigint)
modifies sql data deterministic no external action language
sql
begin atomic
declare v_id_osoby bigint;
call dodaj_osobe( v_id_osoby, 'Ela', 'Matejko',
'Bydgoszcz', 'Jasna', '100', '22345 ');
return( values(v_id_osoby));
end
@
select id_osoby from table(id_osoby()) as t
Inną ciekawą metodą na przekazywanie wyników jest użycie funkcji tabelarycznych
(table function), czyli funkcji, które zwracają wirtualną tabelę.
W przykładzie funkcja id_osoby wstawia osobę i jej adres za pomocą wcześniej
utworzonej procedury, a następnie zwraca wygenerowany identyfikator w postaci
dynamiczne tabeli, która zawiera jeden rekord. Oczywiście funkcja id_osoby powinna
być sparametryzowana, by można było wstawić dowolne dane.
Zapytanie select id_osoby from table(id_osoby()) as t pokazuje, w jaki sposób
zewnętrzna aplikacja może odebrać wygenerowany przy wstawieniu identyfikator. W
porównaniu z parametrami OUT, INOUT oraz zbiorami wynikowymi ta metoda ma
jedną dużą zaletę. Aplikacja odbierająca wygenerowany identyfikator może użyć
zwykłego zapytania SQL.
47
create function dodaj (
p_imie varchar(50),
p_nazwisko varchar(50),
p_miejscowosc varchar(80),
p_ulica varchar(80),
p_nr_domu varchar(15),
p_kod_pocztowy char(5)
)
returns table(id_osoby bigint)
specific dodaj
modifies sql data deterministic no external action language sql
begin atomic
declare v_id_osoby bigint default null;
set v_id_osoby = (
select id_osoby from osoba
where ucase(imie) = ucase(p_imie) and
ucase(nazwisko) = ucase(p_nazwisko));
if v_id_osoby is null then
set v_id_osoby = (
select id_osoby from
new table(insert into osoba(imie,nazwisko)
values (p_imie, p_nazwisko)) as t);
end if;
merge into adres
[..]
return( values(v_id_osoby));
end @
Oto odpowiednik procedury dodaj_osobe napisany w postaci funkcji tabelarycznej.
Funkcje różnią się od procedur i nie wszystkie konstrukcje języka SQL PL, które można
umieścić w procedurze będzie można umieścić w funkcji.
Oto przykład wywołania takiej funkcji, które wstawi dane do tabeli osoba oraz adres i
zwróci wygenerowany dla nich identyfikator.
select id_osoby from table(
dodaj( 'Dominik', 'Matejko', 'Bydgoszcz', 'Jasna', '100', char('22345'))
char
) as t;
W funkcji wykorzystałem instrukcję merge, dokładnie tą samą jak dla procedury
dodaj_osobę.
merge into adres using
table(
values ( v_id_osoby, p_miejscowosc,
p_ulica, p_nr_domu, p_kod_pocztowy)
) tmp (id_osoby,miejscowosc,ulica,nr_domu,kod_pocztowy)
on ( adres.id_osoby = tmp.id_osoby)
when matched then
update set (miejscowosc, ulica, nr_domu, kod_pocztowy) =
(tmp.miejscowosc,tmp.ulica,tmp.nr_domu,tmp.kod_pocztowy)
when not matched then
insert (id_osoby,miejscowosc,ulica,nr_domu,kod_pocztowy)
values (tmp.id_osoby,tmp.miejscowosc,tmp.ulica, tmp.nr_domu,
tmp.kod_pocztowy);
48
Podsumowanie
Przykład prostej procedury był tylko pretekstem do omówienia różnego rodzaju
technik związanych z optymalizacją zapytań. Mam nadzieję, że przydadzą się
Tobie.
Uwagi bądź pytania dotyczące tego materiału możesz przesłać na adres:
[email protected]
COPYRIGHT/Prawa autorskie
Dokument możesz wykorzystywać na własny użytek do pogłębiania wiedzy o DB2.
Możesz udostępniać ten materiał innym osobom zainteresowanym tematyką DB2.
Wykorzystanie części lub całości tego materiału we własnej publikacji wymaga
zgody autora.
Ostatnia aktualizacja: 20 październik 2006
49