Kategorie szkoleń | Egzaminy | Kontakt

Pytanie

Zapytanie do tabeli zdalnej (Federated Objects)

  • 2
  • 2
  • 37

Obiekty:

1. Baza danych o nazwie REMOTE_DB.

2. Widok o nazwie ACCOUNTS_VW w bazie REMOTE_DB

3. Baza danych LOCAL_DB

4. Tabela o nazwie LOCAL_ACC_INFO_TAB w bazie LOCAL_DB.

 

Założenia:

ad. 1 Do bazy REMOTE_DB mam dostęp tylko z poziomu klienta DB2 i tylko do odczytu. Nie mogę tworzyć żadnych obiektów w tej bazie.

ad. 2 Widok ACCOUNTS_VW zawiera niecałe 6 milionów rekordów. Rekord składa się z 50 kolumn. Kluczem jest pole ACC_ID

ad. 3 LOCAL_DB to baza do której jestem administratorem. Mam do niej pełny dostęp z poziomu OS, klienta itd.

ad. 4 Tabela LOCAL_ACC_INFO_TAB to lokalny podzbiór widoku ACCOUNTS_VW. Zawiera 18 tys. rekordów, 10 kolumn, kluczem jest pole ACC_ID.

 

Potrzeba:

Raz w tygodniu odświeżyć dane w tabeli LOCAL_ACC_INFO_TAB danymi pobranymi z widoku ACCOUNTS_VW  (dla istniejących lokalnie kluczy ACC_ID).

 

Aktualne rozwiązanie:

1. EXPORT TO IXF 'file' FROM REMOTE_DB

SELECT col1, col2, ... , col10 FROM ACCOUNTS_VW WHERE colN = 'something'

 - powoduje pobranie około miliona rekordów ze zdalnej bazy danych do pliku na dysku lokalnym.

2. LOAD REPLACE FROM IXF 'file' TO LOCAL_TMP_TABLE

 - wczytuje dane z pliku do tymczasowej tabeli.

3. UPDATE loc

SET loc. col2 = rem.col2,

....

FROM

LOCAL_ACC_INFO_TAB loc

inner join

LOCAL_TMP_TABLE rem

ON loc.ACC_ID = rem.ACC_ID

 - odświeżam 18 tys. rekordów z tymczasowej tabeli.

 

Wady:

1. Ściągam z bazy zdalnej 1 mln rekordów mimo, że potrzebuję 18 tys.

2. Wśród tego miliona nie ma wszystkich potrzebnych.

 

Alternatywa:

Stworzyłem nickaname (używając federacji) w localnej bazie danych. Dzięki temu mogę wykonać joina:

SELECT ACCOUNTS_VW.*

FROM ACCOUNTS_VW inner join LOCAL_ACC_INFO_TAB on loc.ACC_ID = rem.ACC_ID

To pozwala mi zdobyć dane dla dokładnie tych 18 tysięcy ACC_ID dla których potrzebuję.

Ale jest piekielnie wolne (2,5 godziny), ponieważ DB2 realizuje to w następujący sposób:

- ściąga 6 milionów rekordów z bazy zdalnej,

- sortuje je,

- robi 'merge join' lokalnie.

 

Szukam rozwiązania, które pozwoli ściągnąć ze zdalnej bazy dokładnie te 18 tysięcy rekordów, których potrzebuję. Czy istnieje jakiś sposób, żeby zmusić DB2 do wysłania lokalnych kluczy na serwer zdalny, tak aby join wykonał się tam i zwrócił mi tylko potrzebne wyniki?

Tomasz_Stereńczak
  • Zapytał
  • @ Tomasz_Stereńczak | 28.07.2016
    • lider
    • 3
    • 0
    • 0
Zaloguj się aby zadać pytanie
Pokrewne

Odpowiedzi (2)

  • 4

Panie Tomaszu.

Zagadnienie, które Pan porusza nie ma trywialnego rozwiązania, ale są pewne elementy, na które trzeba zwrócić uwagę.
Jeśli nie możemy rozpocząć pracy od strony bazy danych, w której znajduje się większa ilość danych (jak w tym przypadku), to pozostaje strojenie zapytania, które Pan przedstawił. W zasadzie proponowana przez Pana idea rozwiązania z wykorzystaniem polecenia SELECT ze złączeniem tabel z bazy lokalnej i stowarzyszonej jest poprawna (pomijając drobiazgi w składni SELECT). Powinno to przynieść oczekiwany efekt (i przynosi), tylko musimy zadbać o jego optymalizację.

Optymalizator DB2 przygotowując plan dostępu do danych bierze pod uwagę bardzo wiele czynników.
Przede wszystkim:
- parametry konfiguracyjne serwera i bazy danych,
- parametry szybkości wykonywania operacji podstawowych - szybkość procesora, szybkości dostępu do danych w systemach dyskowych,
- definicje obiektów w bazie danych (w tym wypadku lokalnej i związanych ze zdalną bazą),
- statystyki zebrane dla danych lokalnych i zdalnych,
- ustawienia parametrów optymalizacji.
Wszystkie one mają swoje znaczenie w procesie optymalizacji, a ponieważ jest ich bardzo dużo, to trudno jest z góry określić stopień wpływu każdego z nich na efekt końcowy.


W zapytaniach odnoszących się do zdalnego źródła danych (jak w opisywanym przypadku) z pewnością istotne znaczenie mają parametry konfiguracyjne dla tego źródła oraz statystyki dla danych. Definiując dostęp do zdalnego źródła danych w stowarzyszonej bazie danych należy zdefiniować następujące obiekty w lokalnej bazie danych: opakowanie (wrapper), serwer, mapowanie użytkowników i pseudonimy (nicknames) dla tabel i widoków. Proszę zwrócić uwagę na opcje konfiguracyjne definicji serwera: PUSHDOWN i DB2_MAXIMAL_PUSHDOWN.
Dla DB2 v. 9.7:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.swg.im.iis.db.found.conn.fw.opt.doc/topics/iiyfarefdb2opts.html

Dla DB2 v. 10.5:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.swg.im.iis.db.found.conn.fw.opt.doc/topics/iiyfarefdb2opts.html

(W dalszym tekście będę podawał odniesienia tylko do dokumentacji w wersji 10.5, którą łatwo można zmienić z listy rozwijanej na górze strony)
Formalnie to właśnie te parametry są odpowiedzialne za "skłanianie" optymalizatora do przygotowania takiego planu dostępu, aby możliwie dużo pracy wykonywał serwer stowarzyszonej bazy danych. To "skłanianie" nie oznacza "wymuszenia", a raczej "zalecenie", o ile inne czynniki optymalizacji na to pozwalają.

PUSHDOWN
Specifies whether the federated server allows the data source to evaluate operations. Valid values are Y and N. The default is Y; the data source evaluates operations. N specifies that the federated server send SQL statements that include only SELECT with column names. Predicates, such as WHERE=; column and scalar functions, such as MAX and MIN; sorts, such as ORDER BY OR GROUP BY; and joins are not included in any SQL that the federated server sends to the data source.
DB2_MAXIMAL_PUSHDOWN
Specifies the primary criteria that the query optimizer uses to choose an access plan. Valid values are Y and N. The default is N; the query optimizer chooses the plan that has the lowest estimated cost. Y specifies that the query optimizer choose the access plan that pushes down the most query operations to the data source.

Proszę zwrócić uwagę na znaczenie tych parametrów i ich wartości domyślne (w szczególności DB2_MAXIMAL_PUSHDOWN).
Jednak aby oczekiwane działanie miało miejsce należy pamiętać o szeregu innych czynników, np. czy takie przekazanie operacji (i danych) do serwera stowarzyszonego jest w ogóle możliwe i opłacalne. Tutaj wchodzą w grę takie czynniki jak: czy serwer stowarzyszonej bazy danych potrafi wykonać daną operację (np. porównania, sortowania) i czy mogą w nim być obsługiwane określone typy danych.


W tym miejscu pojawia się także zapewnienie zgodności "kolejności zestawiania" (collating sequence) dla bazy lokalnej i stowarzyszonej.
Może to mieć istotne znaczenie jeśli ma dochodzić do porównania danych znakowych. (Brak informacji o typie danych kolumny ACC_ID).
Ten i inne aspekty dotyczące optymalizacji generowania zapytania do stowarzyszonej bazy danych są opisane w dokumentacji:

https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005330.html

Kolejnym elementem mającym istotne znaczenie jest zgromadzenie poprawnych (czyli prawdziwych) statystyk dla danych lokalnych i zdalnych.
Dla danych lokalnych można wykonać polecenie RUNSTATS, np:

db2 RUNSTATS ON TABLE LOCAL_ACC_INFO_TAB WITH DISTRIBUTION ON COLUMNS (ACC_ID) AND INDEXES ALL

Dla danych ze stowarzyszonej bazy danych, można użyć znaną Panu procedurę SYSPROC.NNSTAT.

https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.swg.im.iis.fed.query.doc/topics/rfpsts08.html

Tu kolejna istotna uwaga!
Procedura SYSPROC.NNSTAT nie generuje statystyk (!), ona tylko kopiuje statystyki zgromadzone i dostępne w stowarzyszonej bazie danych.
Jeśli administrator stowarzyszonej bazy danych nie zadbał o ich aktualność, to skopiowanie "złych" danych może w efekcie nie dać optymalnego planu dostępu. Proszę sobie wyobrazić, że statystyki te zawierają informację informację tylko o 1000 wierszy w tabeli zdalnej. W takim przypadku nie powinno dziwić przygotowanie planu dostępu z kopiowaniem wszystkich danych do serwera lokalnego.

Cały proces optymalizacji dodatkowo komplikuje fakt, że zapytanie nie odnosi się bezpośrednio do tabeli w stowarzyszonej bazie, lecz do widoku tam zdefiniowanego. Może da się z niego zrezygnować i stworzyć pseudonim bezpośrednio dla tabeli?

Jeśli statystyki nie są prawdziwe, to może trzeba będzie z nich zrezygnować, ale wówczas niezbędne będzie usunięcie i ponowne zdefiniowanie pseudonimu.

Zrobiłem parę prób we własnym środowisku testowym (dalekim od zgodnością z oryginałem) i zauważyłem, że oprócz wyżej wymienionych elementów warto także spróbować zdefiniować lokalnie indeks odnoszący się do pseudonimu. Taki indeks informacyjny, który przekaże optymalizatorowi wskazówkę, że kolumna używana do złączenia ma wartości unikatowe (o ile to prawda), np:
CREATE UNIQUE INDEX ACCNICKIDX ON ACCOUNT_VW_NICK(ACC_ID) SPECIFICATION ONLY

Życzę cierpliwości i powodzenia w strojeniu planu dostępu dla tego zapytania.
Proszę o podzielenie się na Quorum efektem końcowym tych działań.

Pozdrawiam.
Marek Raczyński.

 

  • Odpowiedział
  • @ | 04.08.2016
  • TRENER MODERATOR ALTKOM AKADEMII
  • 0

Dziękuję za odpowiedź. W wolnej chwili spróbuję skorzystać z zawartych w niej porad, aczkolwiek zrobiłem obejście, które chwilowo rozwiązuje nasze problemy, więc priorytet zadania spadł. ;)

Obejście:

Przygotowałem procedurę składowaną, która:

1. W pętli odczytuje wartości ACC_ID z lokalnej bazy.

2. Po przeczytaniu kilkuset, generuje zapytanie:

SELECT co1, col2, ..., colN, FROM remote_table WHERE remote_table.ACC_ID IN (lista_ID_przeczytanych_z_lokalnej_bazy)

To zapytanie jest w całości wysyłane do bazy zdalnej i tam realizowane.

3. Wynik powyższego zapisuje w bazie.

4. Wracam do czytania ACC_ID z lokalnej bazy.

 

To obejście generuje wiele zapytań (18 tys. IDs / kilkusetelementowe paczki) z powodu ograniczenia na maksymalną długość zapytania.

 

Dzięki temu rozwiązaniu, czas przetwarzania spadł z 2,5h do minuty.

Tomasz_Stereńczak
  • Odpowiedział
  • @ Tomasz_Stereńczak | 11.08.2016
    • lider
    • 3
    • 0
    • 0