User Defined Functions im DB2

User Defined Functions im DB2
von Rosmarie Peter, Trivadis AG
User Defined Functions (UDFs) gestatten es, eigene Funktionen zu schreiben, die in SQL-Statements
verwendet werden können. In diesem Artikel wird beschrieben, wie diese Möglichkeit bei «DB2 UDB
for z/OS and OS/390» genutzt werden kann. Der Unterschied zwischen UDFs für DB2 Mainframe
Installationen und DB2 im Linux, Unix und Windows Umfeld ist klein und beschränkt sich weitgehend auf Unterschiede, die aus den Betriebssystemen kommen. Es ist gängige Praxis, UDFs und Stored Procedures für beide Umfelder zu entwickeln. Den Schwerpunkt dieses Artikels bilden diejenigen
Themen, in denen sich die external UDFs von den Stored Procedures unterscheiden.
1 Einführung
1.1 Was sind Funktionen?
Funktionen erweitern die Mächtigkeit von SQL enorm. Sie werden mit SQL-Sprachmitteln aufgerufen,
also innerhalb der SELECT-Klausel, der FROM-Klausel oder der WHERE-Klausel, je nach Art der Funktion.
Funktionen können unterschiedlich klassifiziert werden:
ƒ Built-in Functions: Diese Funktionen werden so genannt, wie sie im ausgelieferten DB2 Code
eingebaut sind. Beispiele von Built-in Functions sind MAX und SUBSTR.
ƒ User Defined Functions (UDFs): Dies sind selbstgeschriebene Funktionen, die in SQLStatements verwendet werden können. Geschrieben werden die UDFs von den Kunden, oder
aber von IBM selber. Beispiele von UDFs, die von IBM ausgeliefert werden, sind die MQFunktionen, oder aber die in Extenders enthaltenen Funktionen.
Funktionen können aber auch anders kategorisiert werden:
ƒ Column Functions erhalten als Eingabe eine Sammlung von Spalten-Werten und liefern als
Resultat einen einzigen Wert. Beispiele sind SUM, MIN, MAX. Sie werden in der SELECTKlausel verwendet. In der WHERE-Klausel müssen sie in Subselects eingepackt werden. Es ist
nicht möglich, neue eigene Column Functions zu schreiben. Es ist nur möglich, sie in Form
von sog. Sourced UDFs zur Verfügung zu stellen, typischerweise für User Defined Types.
ƒ Scalar Functions haben einen oder mehrere Input Werte, die der Funktion als Argumente
übergeben werden. Als Resultat liefert die Funktion einen skalaren Wert. SUBSTR ist ein Beispiel einer solchen Funktion. Scalar Functions können in der SELECT oder der WHERE-Klausel
überall dort eingesetzt werden, wo ein einzelner Wert erlaubt ist. Ein grosser Teil der mit dem
DB2 ausgelieferten Built-in Funktionen sind Scalar Functions. Es ist aber auch möglich, Skalar
Functions selber zu schreiben und als UDFs zur Verfügung zu stellen. Skalar UDFs können in
der SQL Procedure Language erstellt werden oder in einer anderen Programmiersprache.
Dann spricht man von external Functions.
ƒ Table Functions haben einen oder mehrere Input Werte, die der Funktion als Argumente
übergeben werden. Als Resultat liefert die Table Function eine Tabelle. Sie wird in der FROM
Klausel eingesetzt. Es existieren keine Built-in Table Functions. Alle Table Functions sind external Functions
Funktionen gehören zum SQL Standard. Aber bei den Built-in Functions unterscheiden sich die DBMS
erheblich. Mit User Defined Functions kann man den Grad der Portabilität erheblich verbessern.
1.2 User Defined Functions
User Defined Functions (UDFs) gestatten es, eigene Funktionen zu schreiben, die in SQL-DDL oder
DML-Statements verwendet werden können. Es gibt drei Arten von UDFs:
ƒ Sourced UDFs basieren auf einer bereits bestehenden Funktion. Die Basis-Funktion kann entweder eine Built-in Function sein oder aber eine andere UDF.
ƒ Scalar Functions können in einer höheren Programmiersprache oder in SQL Procedure Language geschrieben werden.
ƒ Table Functions müssen in einer höheren Programmiersprache geschrieben werden.
Built-in Function
UDF
SQL
External
Sourced
Scalar
ja
ja
ja
ja
Column
ja
ja
Table
ja
-
Weitere Merkmale von UDFs:
ƒ Alle UDFs werden im DB2 Katalog eingetragen. Dies geschieht mittels einem CREATE FUNCTION Statement.
ƒ Der Name der UDF besteht aus einem Schemanamen und dem Namen der Funktion.
ƒ Innerhalb von UDFs können relationale und andere Daten gelesen werden, wie z.B. IMS oder
Flatfiles. Teilweise sind auch Mutationen erlaubt.
ƒ UDFs können bis zu 16 Stufen tief verschachtelt werden. In der Aufruf-Hierarchie können sowohl UDFs als auch Stored Procedures erscheinen.
ƒ UDFs können aus Triggers aufgerufen werden.
ƒ UDFs laufen immer unter WLM-Kontrolle.
ƒ UDFs bieten die Möglichkeit des Function Overload. Man kann mehrere Funktionen mit dem
gleichen Namen definieren, die sich gegen aussen einzig in den Argumenten unterscheiden.
Es können unterschiedlich viele Argumente sein, die Argumente können sich im Datentyp unterscheiden. Das DBMS ist in der Lage, beim Aufruf der Funktion die korrekte UDF auszuwählen und auszuführen.
1.3 Warum UDFs?
Mit Hilfe von UDFs kann der Funktionsumfang von SQL beträchtlich erweitert werden. Die Built-in
Funktionen sind sehr nützlich, aber unter Umständen können damit nicht alle Anforderungen abgedeckt werden. Gründe für den Einsatz von UDFs:
ƒ Spezielle Transformationen, z.B. Konversion der Kontonummer von einem internen in ein externes Format.
ƒ Triviale Berechnungen, z.B. firmenspezifische Berechnung der Dienstjahre.
ƒ Möglichkeit von Standardisierung.
ƒ Zur Verfügung stellen der Built-in Funktionen für User Defined Types mit Hilfe der Sourced
Functions.
ƒ Migration aus anderen DBMS: Im Umfang und der Spezifikation der mitgelieferten Funktionen
unterscheiden sich die verschiedenen DBMS beträchtlich. Viele in anderen DBMS häufig eingesetzten Funktionen unterscheiden sich in Namen, in den Argumenten, oder sie existieren im
DB2 überhaupt nicht.
ƒ Komplexe SQL-Logik kann in UDFs gepackt werden. Damit ermöglicht man das Schreiben
von einfacheren SQL-Statements.
2 Sourced UDFs
Sourced UDFs basieren auf bestehenden Funktionen. Absolut notwendig sind sie, falls mit User Defined Types gearbeitet wird. Die Built-in Funktionen können nicht einfach auf die User Defined Types
angewandt werden. Falls man diese benötigt, muss man, wie im untenstehenden Beispiel gezeigt,
UDFs erstellen, die auf den gewünschten Built-in Funktionen basieren.
CREATE DISTINCT TYPE KM
AS INTEGER
WITH COMPARISONS;
CREATE FUNCTION KM_MAX(KM)
RETURNS(KM)
SOURCE SYSIBM.MAX(INTEGER);
3 Erstellen von External UDFs
Für die Erstellung der UDFs sind die folgenden Schritte notwendig:
ƒ CREATE FUNCTION: Damit wird eine UDF dem DB2 bekannt gemacht. CREATE FUNCTION
muss einmal gemacht werden. Für den CREATE FUNCTION muss die Funktion noch nicht bestehen. Sollten später Änderungen an den Definitionen notwendig werden, kann dies mit ALTER gemacht werden.
ƒ Erstellen des Programms. Das Programm kann in einer der folgenden Sprachen geschrieben
werden: C, C++, COBOL, PL/I, Java. Die Besonderheiten der jeweiligen Sprache müssen natürlich auch für UDFs beachtet werden.
ƒ Die Programme müssen anschliessend wie andere Programme umgewandelt werden. Das
DBMS ist zu einem Package zu binden.
Das Vorgehen beim Erstellen der UDFs erinnert stark an die Stored Procedures. Allerdings gilt es bei
den Funktionen einige Besonderheiten zu beachten, die im Folgenden diskutiert werden sollen:
ƒ Option DETERMINISTIC
ƒ Linkage Convention
ƒ Option SCRATCHPAD
ƒ Option FINAL CALL
ƒ Programm-Logik für Scalar Functions
ƒ Programm-Logik für Table Functions.
3.1 Option DETERMINISTIC
Diese Option ist eine unter vielen, die beim CREATE FUNCTION definiert werden muss. Wenn eine
Funktion als DETERMINISTIC definiert wird, so heisst das, dass sie bei gleichem Input stets das gleiche Resultat liefert. SUBSTR ist ein Beispiel einer solchen Funktion. RAND hingegen ist nicht determistic, denn RAND wird bei jedem Aufruf ein anderes Resultat liefern. Default ist NOT DETERMINISTIC. Die Beschreibung ist unspektakulär und verleitet dazu, diese Option nicht weiter zu beachten. Das kann fatal sein, wie das folgende Beispiel zeigt:
Die UDF in diesem Beispiel macht nichts anderes, als eine Nummer vom internen in ein externes
Format zu überführen. Mehrere Minuten Laufzeit für ein einfaches Statement sind inakzeptabel. Der
Grund für die Laufzeit war aus dem EXPLAIN-Resultat leicht ersichtlich: Für das SELECT-Statement im
Klammerausdruck in der FROM-Klausel wurde ein Tablespace Scan gemacht. Das Resultat wurde
dann materialisiert und wiederum mit Tablespace Scan gelesen.
Forschungen im SQL Reference Manual lieferten dann die Erklärung.
Man muss diesen kurzen Text wirklich gut lesen. NOT DETERMINISTIC bewirkt
ƒ schlechteren Zugriffspfad
ƒ unterwartete Resultate.
Im obigen Beispiel wurde die Definition der UDF auf DETERMINISTIC geändert. Das Resultat wurde
daraufhin im Bruchteil einer Sekunde geliefert, da für den SELECT im Klammerausdruck der vorhandene Index genutzt wurde.
3.2 Linkage für UDFs
Mit der Linkage Convention wird definiert, wie
die UDF gegen aussen kommuniziert. Der Aufbau entspricht weitgehend dem Aufbau von
Stored Procedures, die mit PARAMETER STYLE
DB2SQL und DBINFO definiert wurden. Zwei
Felder sind zusätzlich vorhanden:
ƒ Die Scratchpad: Es ist möglich, einen Bereich zu definieren, in welchem Informationen von einem Aufruf zum nächsten
weitergegeben werden.
ƒ Der CALL Type: dient der Programmsteuerung
Wie bei Stored Procedures können bei UDFs
eigene SQLSTATEs definiert werden. Was im
Feld SQLSTATE abgefüllt wird, erscheint beim
Aufrufer in der SQLCA, zusammen mit dem Text
aus Diagnostic Data.
3.3 Option SCRATCHPAD
Eine Scratchpad ist ein Speicherbereich, der von DB2 zur Verfügung gestellt wird, um Informationen
von einem Aufruf der UDF zum nächsten weiterzugeben. Ob für eine UDF eine Scratchpad angelegt
werden soll sowie deren Länge wird beim CREATE FUNCTION definiert.
DB2 stellt eine Scratchpad zur Verfügung pro
ƒ SQL-Statement
ƒ Vorkommen innerhalb dem SQL-Statement
ƒ parallel Task
Anhand von einem Beispiel soll gezeigt werden, was das heisst. Schauen wir uns das folgende Statement an:
SELECT MYUDF(C1,1), MYUDF(C2,1)
FROM TABA;
Der Optimizer habe sich dafür entschieden, dieses Statement zu parallelisieren und in drei parallelen
Tasks auszuführen. Das heisst, dass DB2 6 Scratchpads zur Verfügung stellt!
Die Scratchpad wird vom DB2 auf X’00’ initialisiert. Der Programmierer ist selber dafür verantwortlich, die maximale Länge einzuhalten. Die Initialisierung der Scratchpads ist aufwändig. Wenn für
Singleton SELECTs Scratchpads initialisiert werden müssen, so wird das für den Anwender in Form
von verlängerten Antwortzeiten unmittelbar spürbar.
3.4 Option FINAL CALL
Mit FINAL CALL kann vom DB2 für die UDF je ein spezieller Aufruf für Initialisierungsarbeiten und
ein Extra-Aufruf für Abschlussarbeiten angefordert werden. Diese Initialisierungs- und TerminierungsAufrufe werden veranlasst pro
ƒ SQL-Statement
ƒ Vorkommen innerhalb dem SQL-Statement
ƒ
parallel Task
FINAL CALL muss angegeben werden, falls für die UDF spezielle Ressourcen alloziert werden müssen. Diese müssen dann im letzten Aufruf explizit freigegeben werden.
3.5 Programm Logik für Scalar Functions
Die Programmsteuerung von UDFs muss sich am CALL
TYPE ausrichten, wie am nebenstehenden Pseudocode
gezeigt wird:
ƒ Falls «FINAL CALL» spezifiziert wurde, wird von
DB2 der CALL TYPE abgefüllt
> -1 First Call
> 0 normal Call
> +1 Final Call
> 255 Final Call, falls aufrufende Anwendung
die Unit of Work beendet
ƒ ohne «FINAL CALL» ist der CALL_TYPE für Skalar
UDFs irrelevant
ƒ Fehlermeldungen:
> UDF_SQLSTATE
> UDF_DIAG_MSG
Scalar Functions werden in der SELECT- oder in der WHERE-Klausel verwendet und können damit
den Funktionsumfang von SQL enorm erweitern. Sie werden pro SQL-Statement unter Umständen
mehrfach aufgerufen. Deshalb ist den Performance-Aspekten bei der Codierung genügend Beachtung
zu schenken.
3.6 Programm Logik für Table Functions
Eine Table UDF gibt als Resultat eine Tabelle. Sie kann damit genutzt werden als Alternative zu Stored Procedures mit Result Sets. TABLE UDFs werden in den FROM-Klauseln verwendet:
SELECT COUNT(*)
FROM TABLE(TEST.TABUDF(1,2)) AS A;
Table UDFs nehmen gegenüber dem DB2 eine Art Zwitterstellung ein:
ƒ DB2 schickt dem UDF-Programm eine Meldung, es solle bitte den Cursor eröffnen.
ƒ Bei den folgenden Aufrufen erhält das UDF-Programm den Befehl, einen FETCH zu machen
und eine Resultatzeile zurückzugeben.
ƒ Wenn keine Zeilen mehr verfügbar sind, so setzt das Programm den STLSTATE 02000.
ƒ DB2 fordert dann zum Schluss das UDF Programm auf, den CLOSE für den Cursor auszuführen.
Die geforderte Programmlogik wird auch hier durch den
CALL TYPE gesteuert.
ƒ Call-Types ohne «FINAL CALL»
> -1 Open Call
> 0 Fetch Call
> +1 Close Call
ƒ Call-Types mit «FINAL CALL»
> -2 First Call
> -1 Open Call
> 0 Fetch Call
> +1 Close Call
> 2 Final Call
> 255 Final Call, falls UOR durch aufrufende
UOR beendet wird.
ƒ Fehlermeldungen:
> UDF_SQLSTATE
> UDF_DIAG_MSG
Der Aufruf von Table UDFs erfolgt aus der FROM-Klausel. Sie kann auch als Teil von JoinOperationen verwendet werden. Abhängig vom Zugriffspfad, den der Optimizer wählt, können auch
Table Functions zu Inner Tables werden. Um dem Optimizer die Möglichkeit zu geben, wirklich einen performanten Zugriffspfad zu wählen, sollte beim CREATE FUNCTION der Parameter CARDINALITY spezifiziert werden. Damit kann die erwartete Anzahl von Resultatzeilen angegeben werden.
4 Design Überlegungen
4.1 Was geht und was nicht geht?
Das DB2 Manual ist voll von Hinweisen zu UDFs. Und man tut gut daran, sich diese zu Herzen zu
nehmen. Auch das Unscheinbare. Das Wichtigste, was jeder beachten muss:
ƒ SQLSTATE ist ein CHAR(5) Feld, dem jedesmal ein gültiger Wert zugeordnet werden muss,
wenn die Funktion endet.
ƒ Modifizierende SQL-Statements sind nur in Skalar UDFs möglich, falls der Aufruf aus einem
UPDATE oder INSERT Statement erfolgt.
ƒ In den UDF-Programmen müssen die Cursors alle explizit geschlossen werden, sonst resultiert
ein negativer SQL Code. Einen impliziten CLOSE gibt es nicht.
ƒ Falls die die UDF als NOT DETERMINISTIC definiert wurde, so gelten die folgenden Einschränkungen:
> Die UDF kann in CASE Expressions nicht verwendet werden.
> sie kann in der ORDER BY Klausel nicht verwendet werden.
> Sie sollte nicht in Prädikaten der WHERE-Klausel verwendet werden, da ansonsten die Resultate Überraschungen bieten.
ƒ In den folgenden Fällen sollte man DISALLOW PARALLEL spezifizieren:
> Falls die UDF NOT DETERMINISTIC ist
> Falls eine Scratchpad verwendet wird
> Falls FINAL CALL spezifiziert wurde
> Falls bei Skalar UDFs MODIFIES SQL DATA spezifiziert wurde
> Falls EXTERNAL ACTION spezifiziert wurde
> Falls es sich um eine Table UDF handelt.
4.2 UDF Effizienz
Einer der wichtigsten Unterschiede zwischen den Built-in Funktionen und den external UDFs ist die
Tatsache, dass alle UDFs sog. FENCEd sind. Damit wird DB2 geschützt gegen Fehler im Anwendungscode. UDFs laufen deshalb nicht im DB2 Address Space, sondern unter der Kontrolle des Language Environment in einem WLM Address Space. Die Built-in Functions hingegen sind Bestandteil
des DB2 Codes und laufen auch im DB2 Address Space. Trotzdem kann gerade auch der Entwickler
einiges zur Effizienz von UDFs beitragen:
ƒ Die Anzahl der Input-Parameter sollte möglichst klein gehalten werden, denn jeder InputParameter erhöht den Overhead.
ƒ Der Code von UDFs sollte re-entrant sein, damit die Option STAY RESIDENT YES definiert
werden kann. Dies ist besonders wichtig bei mehreren Aufrufen der gleichen UDF innerhalb
von einem einzigen SQL-Statement. STAY RESIDENT YES bewirkt folgendes:
> Sobald das Loadmodule einmal geladen ist, bleibt es im Speicher
> diese einzige Kopie kann dann über mehrere aufrufe der UDF genutzt werden.
ƒ Beim Einsatz von UDFs sollte der Zugriffspfad stets mit EXPLAIN überprüft werden. Der
Zugriffspfad kann nämlich durch UDFs verändert werden.
5 Zusammenfassung
UDFs erweitern den Funktionsumfang von SQL beträchtlich. Man kann Anwendungscode darin verpacken und so dem SQL verfügbar machen. Die Erstellung der UDFs ist nicht kompliziert. Allerdings
ist es notwendig, den Einsatz von UDFs gut zu planen, um nicht unangenehm überrascht zu werden.