Používáte k napodobení databáze db2look?
On 14 února, 2021 by adminJsem v procesu migrace některých databází z iso8859-1 do utf-8. Jedna z databází obsahuje více než 1000 tabulek, spoustu procedur, funkce, spouštěče, omezení atd. Chtěl bych proces co nejvíce automatizovat, protože k tomu dojde několikrát a pro několik instancí systému. V ideálním případě bych chtěl předat práci mým přátelům Jenkinsovi a Ansible 😉
Mým plánem bylo vygenerovat ddl pomocí db2look, ale nedaří se mu vygenerovat „objekty“ ve správném pořadí. Zkoušel jsem spustit jak s příznakem -ct, tak bez něj (selže z různých důvodů) . Například:
connect to <db>; create table t1 (x int not null); create unique index t1pk on t1 (x); alter table t1 add constraint t1pk primary key (x); connect reset;
spuštěno
db2look -d <db> -e -td @ -ct
generuje nesprávné pořadí indexu a omezení.
CREATE TABLE "DB2INST1"."T1" ( "X" INTEGER NOT NULL ) IN "USERSPACE1" ORGANIZE BY ROW@ ALTER TABLE "DB2INST1"."T1" ADD CONSTRAINT "T1PK" PRIMARY KEY ("X")@ CREATE UNIQUE INDEX "DB2INST1"."T1PK" ON "DB2INST1"."T1" ("X" ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS@
Odstranění -ct funguje v tomto triviálním příkladu, ale ve skutečné databázi selže kvůli jiným závislostem.
Představuji si, že migrace na UTF-8 je docela běžný úkol, takže jsem zvědavý, co p lidé to udělali. Dvě možná řešení, která vidím, jsou:
a) write a parser that inspects the catalog for database objects and sort them topologically using dependency tables. b) write a parser that reads the output from db2look, identify each object and sort them topologically using dependency tables.
Je zřejmé, že u obou existují nevýhody, přehlédl jsem nějaký triviální způsob migrace databází?
EDIT: Další pozorování spočívá v tom, že pokud index neobsahuje žádné další atributy ve srovnání s implicitně vytvořeným indexem prostřednictvím omezení jedinečného / primárního klíče, upozornění QL0605W
je zvednutý. Pokud jsou naopak zadány další atributy, je vyvolána chyba SQL0601N
. Příklad:
CREATE UNIQUE INDEX X1 ON T1 (C1, C2) COMPRESS NO INCLUDE NULL KEYS DISALLOW REVERSE SCANS
generuje varování SQL0605W
, pokud je podobný index vytvořen pomocí příkazu primárního klíče.
Na druhé straně index jako:
CREATE UNIQUE INDEX X2 ON T2 (C1, C2) INCLUDE (C3) CLUSTER COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS
generuje chybu SQL0601N
. Předpokládám, že je to kvůli klauzuli INCLUDE, ale možná toto chování způsobí také klauzule CLUSTER.
Komentáře
Odpověď
Před několika lety jsme migrovali naši databázi DB2 z 9,7 na 10,5. Také jsme přesunuli databázi na nový hardware a implementovali kompresi dat. Z tohoto důvodu jsme se rozhodli vytvořit databázi od nuly a exportovat a importovat data.
K provedení úlohy jsme použili programy db2look a db2move. Neexistoval však způsob, jak získat DDL vytvořený programem db2look ve správném pořadí. Generovaný skript jsme museli rozdělit na různé části pro vytváření tabulek, vytváření spouštěčů, vytváření indexů atd.
Nakonec skončíme s následujícími kroky:
- exportovat existující data
- vytvořit novou databázi
- vytvořit skupiny vyrovnávacích pamětí a tabulkové prostory
- vytvořit tabulky
- vytvořit primární klíče
- vytvářet indexy
- vytvářet zobrazení
- načítat data
- spouštět reorgs a runstats
- vytvářet uložené procedury, uživatelem definované funkce a triggery
Doufám, že to pomůže, i když to není odpověď, kterou jste možná čekali.
Komentáře
- Děkujeme za odpověď. Situace je trochu komplikovanější (existují například tabulky závislé na funkcích a naopak). I ‚ pravděpodobně vytvořím analyzátor, který seřadí výstup z db2look v topologickém pořadí pomocí tabulek závislostí.
Odpovědět
Drsný nápad, jak získat databázové objekty ve správném pořadí. Graf závislostí není úplný, ale zdá se, že splňuje mé potřeby.
#!/usr/bin/python3 import ibm_db import ibm_db_dbi from toposort import toposort, toposort_flatten cfg = ... conn = ibm_db.connect("DATABASE=%s;HOSTNAME=%s;PORT=50000;PROTOCOL=TCPIP;UID=%s; PWD=%s" % cfg,"","") find_edges = """ select * from ( SELECT "CONSTRAINT" as type, CONSTNAME, TABSCHEMA, TABNAME, BTYPE, "N/A", BSCHEMA, BNAME FROM SYSCAT.CONSTDEP WHERE TABSCHEMA NOT LIKE "SYS%" AND BSCHEMA NOT LIKE "SYS%" UNION ALL SELECT "I", "N/A", D.INDSCHEMA, D.INDNAME, D.BTYPE, "N/A", D.BSCHEMA, D.BNAME FROM SYSCAT.INDEXDEP D JOIN SYSCAT.INDEXES I ON D.INDSCHEMA = I.INDSCHEMA AND D.INDNAME = I.INDNAME WHERE I.TABSCHEMA NOT LIKE "SYS%" UNION ALL SELECT "I", "N/A", I.INDSCHEMA, I.INDNAME, "T", "N/A", I.TABSCHEMA, I.TABNAME FROM SYSCAT.INDEXES I WHERE I.TABSCHEMA NOT LIKE "SYS%" UNION ALL SELECT "F", "N/A", R1.ROUTINESCHEMA, R1.ROUTINENAME, D.BTYPE, "N/A" , COALESCE(R2.ROUTINESCHEMA, D.BSCHEMA), COALESCE(R2.ROUTINENAME, D.BNAME) FROM SYSCAT.ROUTINEDEP D JOIN SYSCAT.ROUTINES R1 ON D.ROUTINESCHEMA = R1.ROUTINESCHEMA AND D.SPECIFICNAME = R1.SPECIFICNAME LEFT JOIN SYSCAT.ROUTINES R2 ON D.BSCHEMA = R2.ROUTINESCHEMA AND D.BNAME = R2.SPECIFICNAME AND D.BTYPE = "F" WHERE D.ROUTINESCHEMA NOT LIKE "SYS%" AND D.BSCHEMA NOT LIKE "SYS%" AND D.BTYPE <> "K" UNION ALL SELECT "T", "N/A", TABSCHEMA, TABNAME, BTYPE, "N/A", BSCHEMA, BNAME FROM SYSCAT.TABDEP WHERE TABSCHEMA NOT LIKE "SYS%" AND BSCHEMA NOT LIKE "SYS%" UNION ALL SELECT "X", "N/A", TRIGSCHEMA, TRIGNAME, BTYPE, "N/A", BSCHEMA, BNAME FROM SYSCAT.TRIGDEP WHERE TRIGSCHEMA NOT LIKE "SYS%" AND BSCHEMA NOT LIKE "SYS%" UNION ALL SELECT "T", "N/A", TABSCHEMA, TABNAME, "T", "N/A", REFTABSCHEMA, REFTABNAME FROM SYSCAT.REFERENCES WHERE TABSCHEMA NOT LIKE "SYS%" ORDER BY 3,4 ) """ sedges = ibm_db.prepare(conn, find_edges) edges = {} ibm_db.execute(sedges, ()) lastnode = None tpl = ibm_db.fetch_tuple(sedges) while tpl: n1 = (tpl[0], tpl[1], tpl[2], tpl[3]) n2 = (tpl[4], tpl[5], tpl[6], tpl[7]) if lastnode == n1: edges[n1].add(n2) else: # print("new") edges[n1] = set() edges[n1].add(n2) lastnode = n1 tpl = ibm_db.fetch_tuple(sedges) x = list(toposort_flatten(edges))
Nyní lze z db2look pomocí x vybrat věci ve správném pořadí. Použil jsem triviální analyzátor, který čte výstup z db2look
do seznamů. Opakováním přes x a výběrem definice z pravého segmentu lze dosáhnout tříděného výstupu. Samotný analyzátor je jen spousta regulárních výrazů a není nijak zvlášť zajímavý, ale jelikož jsou výroky rozloženy do několika řádků, je hezké mít čtenáře:
# helper for reading stmt by stmt def myreadlines(f, newline): buf = "" while True: while newline in buf: pos = buf.index(newline) yield buf[:pos] buf = buf[pos + len(newline):] chunk = f.read(4096) if not chunk: yield buf break buf += chunk
EDIT: Mám analyzátor na https://github.com/lelle1234/Db2Utils . Není to v žádném případě úplné, ale fungovalo to pro mé potřeby.
K dispozici je také indexový poradce, který se snaží přijít s optimální sadou indexů pro daný dotaz a databázi.
Odpověď
Ke kopírování produkčního schématu jsem použil následující postup pro testování serverů pro vývojáře. Trik je ignorovat chyby během jednotlivých běhů a na konci porovnat schéma, abyste se ujistili, že je vše správně vytvořeno. Vím, že to není čistý přístup. Udělal jsem to proti schématu s více než 1000 tabulkami, 500 funkcemi se závislostmi, omezeními atd. Kroky můžete automatizovat pomocí skriptu shellu. Vyžaduje mnohem menší úsilí ve srovnání s psaním syntaktického analyzátoru a jeho testováním.
Krok 1 Pomocí skriptu extrahujte schéma z produkčního serveru v určitém pořadí (db2look nejprve pro tabulkové prostory, db2look pro další tabulky atd.). Použití konkrétní objednávky snižuje počet iterací pro krok 2 & 3.
Krok 2 Spusťte výstup z kroku 1 na testovacím serveru
Krok 3 Extrahujte schéma z testovacího serveru a porovnejte ho se schématem z produkčního serveru.
Krok 4 Opakujte kroky 2 a 3, dokud nebudou obě schémata synchronizována.
AUTO_REVAL
DEFERRED_FORCE
vyřešit váš problém?db2 +c -s ...
), abych mohl bezpečně říci, že je vše správně vytvořeno.