Stabilisierung dynamischer SQL-Statements

Dynamisches SQL gibt es schon so lange, wie es DB2 und SQL gibt. Die hohe Flexibilität und Einsatzbreite erkauft man sich allerdings durch einen gewissen Performanceverlust. Einiges, was beim statischen SQL einmalig zum Zeitpunkt des BINDs passiert (Syntaxprüfung, Parsing, Berechtigungskontrolle, Ermittlung des Zugriffspfads ...) erfolgt bei dynamischem SQL erst zum Zeitpunkt der Ausführung - und das jedesmal neu. Bereits mit der DB2 for OS390 Version 5 hat IBM den dynamic statement cache (DSC) eingeführt. Vor allem für die Unterstützung von Anwendungen, die überwiegend oder ausschließlich dynamisches SQL verwenden (wie z.B. SAP oder people soft). Der DSC ist ein Speicherbereich des EDM-Pools im DBM1 Adressraum. In ihm werden Informationen, die beim PREPARE des dynamischen SQL Statements gewonnen wurden, abgelegt und eliminieren so die Notwenigkeit eines weiteren PREPAREs, wenn das identische Statement erneut ausgeführt werden muss. Verschwand das Statement aus dem DSC, z.B. weil dieser zu klein war und nicht alle Statements aufnehmen konnte, weil sich RUNSTATS-Daten oder Berechtigungen änderten oder schlicht, weil das DB2-System neu gestartet wurde, dann ging die Information verloren und der Prepare musste wiederholt werden. Ab V12 function level 500 bietet DB2 jetzt die Möglicheit, diese Informationen im Katalog abzulegen und dadurch über einen längeren Zeitraum verfügbar zu halten.

relevante zParms

ParameterBeschreibungmögliche Werte
CACHEDYNsollen präparierte Statements im cache gespeichert werdenYES (Default)
NO
MAXKEEPDmaximale Anzahl präparierter dynamischer Statements, die im cache gespeichert werden sollen(numerischer Wert) , Default: 5000
CACHEDYN_STABILIZATION
CACHEDYNST		wie soll mit dynamic sql stabilization umgegangen werden
CAPTUREStatements können zwar mittels -START DYNQUERY stabilisiert werden, aber stabilisierte Stamements werden nicht von den DB2-Tabellen geladen
LOADbereits stabilisierte Statements werden zwar von den DB2-Tabellen geladen, aber es können keine weiteren Statements stabilisiert werden
BOTH (default)Statements können neu stabilisiert werden und bereits stabilisierte Statements können geladen werden
NONEes ist weder ein stabilisieren neuer Statements noch ein Verwenden bereits stabilisierter Statements möglich

Ermittlung relevanter Statements

- aus dem dynamic statement cache

Es gibt eine Reihe von Software-Produkten, mit deren Hilfe relevante Statements aus dem dynamic satement cache angeschaut bzw. identifiziert werden können. Man kann es aber auch mit Hausmitteln machen. Dazu benötigt man eine DSN_STATEMENT_CACHE_TABLE. (das entsprechende CREATE statement findet sich in der SDSNSAMP-Bibliothek im Member DSNTESC). Mittels des SQL-Befehls EXPLAIN STMTCACHE ALL; lässt sich der DSC in diese Tabelle übertragen und dann mittels SELECT auslesen. Zuvor am besten noch die STATEMENT_CACHE_TABLE löschen. Hier das Ergebnis eines entsprechenden SPUFI:



DELETE FROM DSN_STATEMENT_CACHE_TABLE ;
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------

EXPLAIN STMTCACHE ALL;
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------

SELECT STMT_ID, SUBSTR(STMT_TEXT,1,80) AS TEXT
FROM DSN_STATEMENT_CACHE_TABLE
ORDER BY CACHED_TS DESC
---------+---------+---------+---------+---------+---------+---------+---------+
STMT_ID TEXT
---------+---------+---------+---------+---------+---------+---------+---------+
1144   DELETE FROM DSN_STATEMENT_CACHE_TABLE
1143   SELECT NAME, TSNAME FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06'
1142   INSERT INTO MYUSER.MYTABLEVIEW      VALUES     (S ...
1141   WITH NN# AS ( SELECT A ...

Da der letzte SELECT absteigend nach CACHED_TS sortiert ist befindet sich der DELETE FROM DSN_STATEMENT_CACHE_TABLE natürlich ganz oben. Danach folgen die anderen Statements sortiert nach dem Zeitpunkt ihres Speicherns im DSC. Wurde nun ein entsprechend relevantes Statement ermittelt kann es mit einem neuen DB2 Kommando stabilisiert werden. Der DELETE von der DSN_STATEMENT_CACHE_TABLE ist jetzt nicht unbedingt ein geeigneter Kandidat dafür, also nehmen wir einfach den SELECT auf die Katalogtabelle. Das Kommando zum Stabilisieren verlangt noch nach einem Gruppennamen, mit dessen Hilfe mehrere Statements gruppiert und z.B. später gemeinsam gelöscht werden können:



-STA DYNQUERYCAPTURE STBLGRP (GROUP01) STMTID(1143)

DB2 quittiert unser Kommando mit einem


********************************* Top of Data **********************************
DSNX221I -DB2S DSNXESTC DYNAMIC QUERY CAPTURE FOR COMMAND NUMBER 1
STARTED SUCCESSFULLY.
******************************** Bottom of Data ********************************

- Alternativen mit dem neuen -STA DYNQUERYCAPTURE - Befehl

Neben der oben aufgeführten Möglichkeit, ein individuelles Statement zu stabilisieren kann mit dem -STA DYNQUERYCAPTURE auch ein Monitor gestartet werden, der Statements automatisch stabilisiert, wenn bestimmte Bedingungen erfüllt sind, nämlich alle Statements, die eine bestimmte CURRENT SQLID haben oder Statements die mindestens x mal ausgeführt werden. Für letzteres müssen allerdings sowohl IFCID 316 als auch IFCID 318 aktiv sein (sonst wird die Anzahl der Ausführungen nicht mitgezählt und der Grenzwert wird nie erreicht). Ein


-STA DYNQUERYCAPTURE STBLGRP (GRPTH20) THRESHLD(20)

würde alle dynamischen Statements stabilisieren, die mindestens 20 mal ausgeführt wurden oder noch werden. Dieser Monitor bleibt so lange aktiv, bis ein entsprechender -STO DYNQUERYCAPTURE ausgeführt wird oder das DB2 gestoppt wird. Bei einem Neustart des DB2 wird der Monitor nicht automatisch neu gestartet.

Speicherung im Katalog

Egal welche Methode verwendet wurde um ein Statement zu stabilisieren, es befindet sich nun im Katalog. Dazu gibt es neue Tabellen: die SYSIBM.SYSDYNQRY mit ihren auxiliary tables und die SYSIBM.SYSDYNQRYDEP. Die erste enthält Informationen zu den stabilisierten Statements wie z.B. den Zugriffspfad, die meisten Informationen davon in einem nicht lesbaren internen Format, die zweite enthält Abhängigkeiten der Statements zu anderen Objekten (ähnlich der SYSPACKDEP). Momentan enthalten bei uns beide Tabellen nur Informationen zu dem oben stabilisierten SELECT. Per SPUFI können die Tabellen ausgelesen werden:


SELECT SDQ_STMT_ID, STBLGRP, STMTTEXT
FROM SYSIBM.SYSDYNQRY
---------+---------+---------+---------+---------+---------+---------+---------+
SDQ_STMT_ID  STBLGRP     STMTTEXT
---------+---------+---------+---------+---------+---------+---------+---------+
          1  GROUP01     SELECT NAME, TSNAME FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06'
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+

SELECT SDQ_STMT_ID ID, SUBSTR(BQUALIFIER,1,10) BQUALIFIER,
SUBSTR(BNAME,1,20) BNAME , BTYPE , BAUTH, AUTHID
FROM SYSIBM.SYSDYNQRYDEP
--------+---------+---------+---------+---------+---------+---------+------
ID  BQUALIFIER  BNAME         BTYPE  BAUTH  AUTHID
--------+---------+---------+---------+---------+---------+---------+------
 1  DSNDB06     DSNRSTAT      P      0
 1  SYSIBM      SYSTABLES     T      0
 1  SYSIBM      SYSTABLES     T      50     MYUSER
 1  SYSIBM      DSNDTX05      I      0
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+

Die zweite Tabelle zeigt hier an, dass Abhängigkeiten des Statements zum Tablespace DSNDB06.DSNRSTAT, zur Tabelle SYSIBM.SYSTABLES und zum Index SYSIBM.DSNDTX05 bestehen und der User MYUSER eine Select-Berechtigung benötigt.

Löschen nicht mehr benötigter Queries

Mit dem DSN subcommand FREE STABILIZED DYNAMIC QUERY können nicht mehr benötigte Statements aus den beiden Katalogtabellen entfernt werden. Gleichzeitig wird das Statement auch aus dem DSC gelöscht. Es kann entweder ein einzelnes Statement gefreed werden oder eine komplette Stabilisierungsgruppe. Um das Statement in unserem Beispiel zu entfernen setzen wir ein


FREE STABILIZED DYNAMIC QUERY SDQSTMTID (1)

ab (ohne ein Minuszeichen vor dem FREE), was uns DB2 mit der Meldung


DSNT340I -DB2S FREE STABILIZED DYNAMIC QUERY COMPLETED SUCCESSFULLY
FOR 1 STATEMENTS.

bestätigt. Damit ist alles wieder auf Anfang.

Zurück zu DB2 Home Impressum / Datenschutz