DB2 und Stored Procedures - SK Consulting Services GmbH

IBM DB2 for z/OS
Stored Procedures bei DB2
Dez, 04
1
Themen
Themen
Inhalt: Stored Procedures b ei DB2
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Dez, 04
STP - Übersicht
Voraussetzungen
STP‘s in Programmen
Beispiele von STP‘s
Voraussetzungen für STP‘s
Aufruf von „fremden“ Programmen aus STP‘s
Die Entwicklung von STP‘s
Restriktionen bei STP‘s
Transaktionen bei STP‘s
Vorbereiten des Einsatzes von STP‘s
REXX & STP‘s
SQL Stored Procedures
Beispiele von SQL STP‘s
STP‘s und „linkage conventions“
Ablauf von STP‘s
... Und sonst ????
Übungen / Fragen
2
1.
bersicht
1. Stored
Stored procedures
procedures -- Ü
Übersicht
Die typische DRDA Applikation ist in der Regel wie folgt strukturiert:
• V3 Client/Server
-
als typische DRDA-1
Anwendung
der Client besitzt die AP-Logik
der Server führt (nur) die DBVerarbeitung (SQLProcessing) durch
Client-System
Präsentati
onslogik
Server-System
AP-Logik
Datenver
waltung
¾
¾
der Client führt die Applikationslogik aus
der Server betreibt (nur) die Datenbank (SQL)
Vorteile:
-
einfaches Modell für die Anwendungsentwicklung
alle Anwendungen sind Client-Programme
Nachteile:
-
in verteilten Umgebungen kann es sich aus Performancegründen als
nützlich erweisen, Teile der Applikationslogik auf den Server zu
verlagern - dort liegt meist das DBMS: Die Netzbelastung wird verringert
Änderungen der Server-Datenbank sind nicht transparent für die ClientAnwendung
es ist einfacher, Programme auf einem (zentralen) Server zu verwalten
und zu warten. Der Aufwand ist erheblich geringer, als eine große Menge
von Programmkopien zu pflegen
-
Dez, 04
3
1.
bersicht
1. Stored
Stored procedures
procedures -- Ü
Übersicht
DRDA mit “stored procedures” weist eine dazu vollständig unterschiedliche Architektur auf.
Die Applikationslogik kann vollständig oder teilweise auf dem Server ablaufen.
• DRDA mit „stored proc
‘s“
‘s“
-
DRDA mit „stored
procedures “
AP-Logik Aufteilung zwischen
Client und Server
der Entwickler wählt die Art
der Aufteilung der Aufgaben
Vorteile:
-
Krit. Faktoren:
-
Dez, 04
Client-System
Präsentati
onslogik
Server-System
AP-Logik
Datenver
waltung
einfaches Modell für die Anwendungsentwicklung
Aufteilung der AP-Logiken zwischen Client und Server
der Entwickler wählt die Art der Aufteilung je nach Ziel und Aufgabe des
C/S – Systems
ein analysiertes Verteilungsmodell steuert die Verteilung der AP-Logik
die Skalierbarkeit und Flexibilität der C/S-Umgebung werden zum
kritischen Faktor (DBMS + AE-System)
Administrationsinfrastruktur und -support sind innerhalb der
bestehenden Organisation aufzubauen und zu integrieren
4
2.
2. DB2
DB2 und
und Stored
Stored Procedures
Procedures
EXEC SQL SELECT .....
DB2
SQL Statement
bearbeiten .....
EXEC SQL UPDATE .....
OS/2
DDCS
SQL Statement
bearbeiten .....
EXEC SQL INSERT ....
SQL Statement
bearbeiten .....
Dez, 04
Definition:
Im Sprachgebrauch der
IBM stellt eine “stored
procedure” ein
Anwendungsprogramm dar, das am
DB2-Server liegt und
vom Client über einen
SQL-CALL abgerufen werden kann.
Der Systementwickler
kann also die
Applikation je nach
Bedarf in Client- und
Serverteil aufsplitten.
5
2.
2. DB2
DB2 und
und Stored
Stored Procedures
Procedures
MVS System
Eine typische DRDAApplikation benötigt
meist eine “network
Sched PROGX
send/receive”-Operation
EXEC SQL
OS/2
pro SQL-Statement (gilt
SELECT .....
DDCS
Perform SQL
nicht für FETCH = 1
send/receive pro Block). In
EXEC SQL
UPDATE ....
vielen Fällen können bis
Perform SQL
zu 70 SQL “send/receive”
EXEC SQL
Operationen in einer
Perform SQL
INSERT ....
Sekunde ablaufen.
Return values
endproc
Dennoch ist die
to DDCS User
Netzbelastung bei großen
DB2-Applikationen und
die Antwortzeit bei relativ
langsamen Verbindungen häufig nicht befriedigend. Es entsteht zudem ein gewisser
“overhead” für den Aufbau der DRDA-”requests” und “replies”, zumal auch der SQL/CPUPfad bei verteilten Anwendungen länger ist, als bei lokalen.
EXEC SQL
CALL PROCX .....
Dez, 04
DB2
DB2 Stored
Procedures Region
6
2.
2. DB2
DB2 und
und Stored
Stored Procedures
Procedures
Bei der Nutzung von “stored procedures” setzt die Client-Anwendung einen “network
send/receive” ab, um die “stored procedure” zu starten. Dies impliziert die Vorteile:
Dez, 04
1.
die Anzahl der “network send/receive” Operationen wird vermindert: “elapsed
time” und “CPU-time” werden minimiert
2.
“stored Procedures” sind lokale DB2-Routinen (über Call Attach) und somit
werden Logiken zur Sicherung verteilter “requests”, z.B. 2-Phase-Commits
(2PC) nicht erforderlich, was wiederum Systemaufwand spart.
3.
Katalog-Suchen erfolgen nur am Anfang der “stored procedure”
4.
“stored procedures” benutzen keinen eignenen “thread”, sondern verwenden den
“thread” des “callers”
5.
“stored procedures” können “memory caches” zugewiesen bekommen
6.
der Administrator kann für “stored procedures" ein CPU-Limit und eine
“abend”-Grenze vorgeben
7.
“stored procedures" können von anderen Applikationen wiederverwendet werden wie OO-Objekte
8.
von DB2 werden einige 3GL- und 4GL-Systeme als Sprachebene für
“stored procedure” unterstützt: COBOL, PL/1, Assembler, C, VisualGen, JAVA
7
2.
2. DB2
DB2 und
und Stored
Stored Procedures
Procedures
MVS System
User-Applikation
EXEC SQL
CONNECT ...
DB2-System
create DB2
thread
EXEC SQL
SELECT ....
Zugriff auf eine
DB2-Tabelle ...
EXEC SQL
CALL X(:hv)
Hole PROC-Info
vom DB2-Katalog
Übergib "requests"
an xxxSPAS
Perform SQL
Dez, 04
DB2 Stored
Procedures Region
Reuse des
DB2-Thread
Laden des Moduls
für Prozedur X
beginproc
EXEC SQL
UPDATE ....
Locks gehören des
entspr. UOW
EXEC SQL
INSERT ....
endproc
Return aus dem
SQL CALL
Return IN/OUTW erte und PARM
Rückgabe ThreadKontrolle an DB2
EXEC SQL
COMMIT
COMMIT Appl &
Procedure
8
2.
2. DB2
DB2 und
und Stored
Stored Procedures
Procedures
99 “stored
“stored procedure‘s“
procedure‘s“können
könnenam
amClient
Clientund
undam
amServer
Serverunterschiedliche
unterschiedlicheSprachen
Sprachen
benutzen
benutzen
Beispiel:
Beispiel:
••
••
••
••
Ein
EinClient-Programm
Client-ProgrammininCCkann
kannein
einCOBOL-Programm
COBOL-Programmauf
aufdem
demServer
Serverals
alsProzedur
Prozedur
nutzen.
nutzen.
DB2-”stored
DB2-”storedprocedure”s
procedure”sfolgen
folgendem
demDRDA-Standard
DRDA-Standardund
unddamit
damitISO/
ISO/OSI-Richtlinien
OSI-Richtlinien
DRDA
DRDAbietet
bietetzudem
zudemdie
dieFormatkonvertierung,
Formatkonvertierung,die
dieeine
einePortierung
Portierungvon
vonDaten
Datenzwischen
zwischen
heterogenen
Systemen
ermöglichen
soll
heterogenen Systemen ermöglichen soll
DRDA
DRDAunterstützt
unterstütztzudem
zudemdas
das2-Phase-Commit-Protokoll,
2-Phase-Commit-Protokoll,sodaß
sodaßein
einClient
Clientalle
alle“stored
“stored
procedure”’s
an
allen
möglichen
Knoten
nutzen
kann
(auch
update-”stored
procedure”’s an allen möglichen Knoten nutzen kann (auch update-”stored
procedure”’s)
procedure”’s)
99 “stored
“stored procedures”
procedures” können
könnenlokal
lokaloder
oderüber
überDDF
DDF
angestossen
angestossenwerden
werden
U ser-A pp likation
99 lokale
lokaleCALL's
CALL'skönnen
können
ohne
ohneDDF
DDFerfolgen
erfolgen
D B 2-S ystem
D B 2 S tored
P ro ce du res R egio n
Program m A
DB2
Pa ckage
B
.
.
.
.
EX EC S Q L
C A LL B
Prog ram m
B
DB2
P ackag e
o der
P lan für
A
C A LL B
Dez, 04
9
3.
3. DB2
DB2 und
und Stored
Stored Procedures
Procedures Aufrufe
Aufrufe
Der
DerSQL
SQLCALL
CALList
istBestandteil
Bestandteildes
desISOI/ANSI-Standards
ISOI/ANSI-Standardsfür
fürSQL3.
SQL3.Es
Eswird
wirdim
im
statischen
SQL
unterstützt.
statischen SQL unterstützt.
Beispiel:
Beispiel:
CALL
CALL
CALL
CALL
PROCX
PROCX ((:hv1,
:hv1, 4567,
4567,:hv3
:hv3))
:prcnm
:prcnm ((:hv1,
:hv1, :hv2,
:hv2, 'ABCD',
'ABCD', 1234
1234))
CALL
CALL
PROCX
PROCX USING
USING DESCRIPTOR
DESCRIPTOR sqlda
sqlda
CALL
CALL
:prcnm
:prcnm USING
USING DESCRIPTOR
DESCRIPTOR sqlda“
sqlda“
Das SQL CALL Statement läuft wie folgt ab:
Dez, 04
1.
DB2 Thread wird erzeugt ( CONNECT)
2.
SQL-Statement wird abgesetzt
3.
DB2-Server erkennt SQL CALL
4.
SYSIBM.SYSPROCEDURES wird nach der PROC durchsucht. Das Ergebnis wird "gecached"
5.
DB2 findet einen TCB in xxxSPAS und DB2 läßt xxSPAS die STP starten
6.
xxxSPAS nutzt den DB2-Thread des USERS für die Abarbeitung der STP
7.
xxxSPAS nutzt die LE/370 Bibliotheken, um das der STP entsprechende "Load Module" zu laden und zu
starten
8.
nachdem die STP die Kontrolle erhalten hat, kann sie alle Parameter des SQL CALLS verarbeiten. Alle Locks
werden Teil dieser UoW.
9.
wird vom CALL-Programm COMMIT abgesetzt, so wird für alle beteiligten Server und Ressourcen ein
COMMIT verfügt
10
4.
4. Beispiel
Beispiel einer
einer einfachen
einfachen STP
STP
Eine Applikation läuft auf einer Workstation als Client und ruft die “stored procedure” A auf der DB2 Server
Lokation LOCA. Die STP A erledigt folgende Operationen:
•
Übernimmt ein “set of parameters” mit dem Inhalt für eine “row” der “employee to project activity”
Tabelle (DSN8810.EMPPROJACT). Diese Partameter sind “input parameters” im SQL statement CALL:
EMP:
PRJ:
ACT:
EMT:
EMS:
EME:
employee number
project number
activity ID
percent of employee's time required
date the activity starts
date the activity is due to end
•
Definiert einen Cursor, C1, mit der Option WITH RETURN, um ein “result set” mit allen “rows” in
EMPPROJACT zur rufenden Workstation Applikation zu liefern.
•
Sucht in der Tabelle EMPPROJACT nach einer zur Anforderung (Parameter PRJ, ACT, EMS, und
EMP) passenden “row” aus den Spalten PROJNO, ACTNO, EMSTDATE, und EMPNO (UIX)
•
Existiert eine “row” wird ein UPDATE ausgeführt, um die Parameter EMT und EME auf die Spalten
EMPTIME und EMENDATE zu projezieren.
•
Existiert keine “row” ( +100 ), wird ein INSERT durchgeführt (mit allen übergebenen Parametern)
•
OPEN auf C1. Dies gibt das “result set” an den “requestor” zurück, wenn die “stored procedure” endet.
•
Übergibt zwei Parameter: Einen Code, der die V erarbeitung zeigt (UPDATE oder INSERT) und den
SQLCODE für das Statement.
Dez, 04
11
4.
4. Beispiel
Beispiel einer
einer einfachen
einfachen STP
STP
Dez, 04
12
5.
ür STP
‘s
5. Voraussetzungen
Voraussetzungen ffür
STP‘s
Vorbereiten des „stored procedures environment“
•
Systemadministratoraufgaben zur Vorbereitung des DB2 Subsystems für STPs:
- Bestehende STP‘s sollten ins „WLM environment“ übernommen werden, oder es werden neue
„WLM environments“ für neue STP‘s erstellt. STP‘s können nur in einem DB2 zugeordneten
„stored procedure address space“ ablaufen.
- Erstellen von JCL Proceduren für die „SPAS“. Das Member DSNTIJMV im „data set“
DSN810.SDSNSAMP enthält ein JCL Beispiel, wie man „WLM-established“ und „DB2established“ Addressbereiche starten kann.
- Für „WLM-established AS“ sollten „WLM Umgebungen“ für STP-Gruppen definiert werden.
- Plant man „stored procedures“, die das ODBA Interface nutzen, um auf IMS Datenbanken
zuzugreifen, müssen die „startup procedures“ für die „address spaces“ modifiziert werden.
- Plant man „JAVA stored procedures“, so muss die entsprechende Software vorher installiert sein.
(siehe DB2 Application Programming Guide and Reference for Java)
- Installation des entsprechenden „Language Environments“ und der zugehörigen Compiler ( siehe
z/OS Language Environment Customization)
•
Folgende Aufgaben müssen für jede STP erledigt werden:
- Die „library“ in in der die „stored procedure“ abgelegt ist muss sich in der STEPLIB Verkettung der
STARTUP-Prozedur für den SPAS befinden
- Die STP wird über das CREATE PROCEDURE Statement definiert und über ALTER
PROCEDURE geändert
- Aufbau der „Security“ für di8e „stored procedure“.
Dez, 04
13
5.
ür STP
‘s
5. Voraussetzungen
Voraussetzungen ffür
STP‘s
Schreiben und Vorbereiten einer ”external stored procedure”
•
Eine “stored procedure” ist eine DB2 Applikation die im SPAS läuft.
•
Es gibt zwei Typen von “tored procedures”: external stored procedures und SQL procedures:
•
-
External stored procedures sind in einer zulässigen “host language” geschrieben. Der
“source code” wird separat von der Definition geführt. Eine external stored procedure ist
ähnlich einer anderen SQL Applikation. Sie kann “static” oder “dynamic” SQL Statements,
IFI Calls, und DB2 Kommandos über IFI enthalten
-
SQL procedures werden mit Hilfe von SQL “procedures statements” definiert und sind
Bestandteil des CREATE PROCEDURE Statement.
external stored procedures können in Assembler, C, C++, COBOL, Java, REXX, oder PL/I geschrieben
sein. COBOL und C++ STP‘s können objektorientierte Erweiterungen enthalten
DB2-System
User-Applikation
USERID = yyyy
DB2 Stored
Procedures Region
DB2 Agent
USERID=yyyy
.
.
.
.
EXEC SQL
CALL B
DB2
Package
oder
Plan für
A
CALL B
Dez, 04
Programm
B
Andere MVSRessource
USERID
xxxx
14
6.
6. Aufrufen
Aufrufen fremder
fremder Programme
Programme aus
aus einer
einer STP
STP
Aufrufen anderer Programme aus einer ”stored procedure”
•
Eine „stored procedure“ kann aus mehr als EINEM Programm bestehen. Jedes besitzt dabei sein eigenes
Package. Die eigene „stored procedure“ kann dabei alle anderen Programme, „stored procedures“,
oder „user-defined functions“ nutzen.
•
Der „owner“ des Package / Plans das/der das CALL Statement enthält muss die EXECUTE
Authorisierung für alle Packages haben, die diese anderen Programme nutzen.
•
Ruft eine STP ein anderes Programm, so bestimmt DB2 zu welcher „collection“ das Package des
gerufenen Programms gehört auf eine der folgenden Art und Weise:
-
Enthält die STP Definition COLLID collection-id, DB2 nutzt collection-id.
-
Führt die STP SET CURRENT PACKAGE PATH aus und enthält die NO COLLID Option, so kommt das Package
des gerufenen Programmsaus der Liste der „collections“ im CURRENT PACKAGE PATH Spezialregister, z.B
CURRENT PACKAGE PATH enthält die Liste der „collections“ COLL1, COLL2, COLL3, COLL4, dann sucht
DB2 nach dem ersten passenden
Package in der Liste dieser „collections“.
-
Nutzt die STP nicht SET CURRENT PACKAGE PATH und führt stattdessen SET CURRENT PACKAGESET
aus, so kommt das Package des gerufenen Programms aus der spezifizierten „collection“ im CURRENT
PACKAGESET Spezialregister.
•
Nutzt die STP nicht SET CURRENT PACKAGE PATH, SET CURRENT PACKAGESET, und die STP definizion
enthält die NO COLLID Option, dann nutzt DB2 die „ collection ID“ des Packages das das SQL
Statement CALL absetzt.
Wird die Kontrolle von der STP zurückgegeben, so speichert DB2 den Wert des CURRENT
PACKAGESET Specialregisters auf den Wert, den es VOR der Ausführung des SQL Statements CALL
durch das Client-Programm hatte.
Dez, 04
15
7.
‘s
7. Die
Die Entwicklung
Entwicklung von
von STP
STP‘s
Nutzen von “reentrant Code” in einer ”stored procedure”
•
Dez, 04
Wann immer möglich, sollte eine „stored procedure“ reentrant programmiert sein.Das gilt insbesondere
aus Performancegründen:
-
Eine „reentrant stored procedure“ muss nicht jedes Mal, wenn sie gerufen wird, neu in
den Speicher geladen werden.
-
Eine Kopie der „stored procedure“ kann von mehreren „Tasks“ gleichzeitig benutzt
werden („shared“). Dies senkt die Anforderung an „virtual storage“ zur Nutzung des
Codes im SPAS.
•
Um eine „stored procedure“ reentrant werden zu lassen, muss das STP-Programm als reentrant
kompiliert werden und im Link-editor als reentrant und reusable gekennzeichnet sein.
•
Um eine „reentrant stored procedure“ als „resident“ im Speicher verweilen zu lassen, spezifiziert man
STAY RESIDENT YES im CREATE PROCEDURE oder dem ALTER PROCEDURE Statement der
Stored procedure.
•
Kann eine STP nicht reentrant sein, so sollte sie als non-reentrant und non-reusable „link-edited“ werden.
Das „non-reusable“ Attribut verhindert dass unterschiedliche Tasks eine einzelne Kopie der „stored
procedure“ gleichzeitig benutzen. Eine „non-reentrant“ Stored Procedure darf nicht im Speicher
verbleiben. Sie muss deshalb mit STAY RESIDENT NO im CREATE PROCEDURE oder ALTER
PROCEDURE Statement definiert sein.
16
7.
‘s
7. Die
Die Entwicklung
Entwicklung von
von STP
STP‘s
Schreiben einer ”stored procedure” als Haupt- oder Unterprogramm
•
Eine STP kann als Haupt- oder Unterprogramm entwickelt sein. Eine „stored
procedure“ die als Unterprogramm läuft, wird eine bessere Performance aufweisen, da das
„Language Environment“ weniger Verarbeitung leisten muss.
•
Ein „subprogram“ muss folgendes, was ansonsten das „Language Environment“ für ein
Hauptprogramm tut, extra leisten :
• Initialization und „cleanup“ Verarbeitung
• Zuweisen und Freigabe von Speicher
• Close aller Files vor dem EXIT
•
Schreibt man eine „stored procedures“ als Unterprogramm gelten folgende Regeln:
• Beachten der Sprachregeln für das UPro; z.B. keine I/O Operationen in einem PL/I UPro
• Vermeiden von Statements die das „Language Environment“ am Ende des Programms beenden.
Beispiel solcher Statements sind STOP oder EXIT in PL/I oder STOP RUN in COBOL.
Im Falle der Beendigung, muss das „enclave“ des LE neu aufgebaut werden und die Vorteile von
STP als UPro gehen zum Teil verloren.
Dez, 04
17
7.
‘s
7. Die
Die Entwicklung
Entwicklung von
von STP
STP‘s
Schreiben einer ”stored procedure” als Haupt- oder Unterprogramm
Characteristiken von Haupt- und UPros:
Dez, 04
Language
Hauptprogramm
Unterprogramm
Assembler
MAIN=YES(CEEENTRY macro).
MAIN=NO
C
besitzt eine main() function
„fetchable function“
COBOL
COBOL endet mit GOBACK
dynamisch geladenes
„subprogram“ mit
GOBACK am Ende
PL/I
besitzt eine PROC mit
besitzt eine PROC mit
OPTIONS(MAIN)
OPTIONS(FETCHABLE)
18
7.
‘s
7. Die
Die Entwicklung
Entwicklung von
von STP
STP‘s
Eine C++ ”stored procedure” als Unterprogramm
/*****************************************************************
/* This subprogram is a STP that uses linkage convention GENERAL
/* and receives 3 parameters.
The extern statement is required.
/*****************************************************************
extern "C" void cppfunc(char p1[11],long *p2,short *p3);
#pragma linkage(cppfunc,fetchable)
#include <stdlib.h>
EXEC SQL INCLUDE SQLCA;
void cppfunc(char p1[11],long *p2,short *p3)
{
/***************************************************************
/* Declare variables used for SQL operations. These variables
/* are local to the subprogram and must be copied to and from
/* the parameter list for the stored procedure call.
/***************************************************************
EXEC SQL BEGIN DECLARE SECTION;
char parm1[11];
long int parm2;
short int parm3;
EXEC SQL END DECLARE SECTION;
Dez, 04
*/
*/
*/
*/
*/
*/
*/
*/
*/
19
7.
‘s
7. Die
Die Entwicklung
Entwicklung von
von STP
STP‘s
Eine C++ ”stored procedure” als Unterprogramm
/************************************************************
/* Receive input parameter values into local variables.
*/
/************************************************************
strcpy(parm1,p1); parm2 = *p2; parm3 = *p3;
/************************************************************
/* Perform operations on local variables.
/************************************************************
·
·
/************************************************************
/* Set values to be passed back to the caller.
/************************************************************
strcpy(parm1,"SETBYSP"); parm2 = 100; parm3 = 200;
/************************************************************
/* Copy values to output parameters.
/************************************************************
strcpy(p1,parm1); *p2 = parm2; *p3 = parm3; }
}
Dez, 04
*/
*/
*/
*/
*/
*/
*/
*/
*/
*/
*/
20
8.
‘s
8. Restriktionen
Restriktionen bei
bei STP
STP‘s
Dez, 04
•
Keine expliziten Calls an das „attachment facility“ in einer stored procedure.
„Stored procedures“ laufen in einem DB2-“adress space“ und benutzen das „attachment
facility“ (CAF) implizit. „Stored procedures“, die in einem WLM-“address space“ laufen,
nutzen die Resource Recovery Services „attachment facility“ (RRSAF) implizit. Macht
eine „stored procedure“ einen expliziten „attachment facility call“, wird dieser von DB2
zurückgewiesen.
•
Keine SRRCMIT oder SRRBACK Service Calls in einer
„WLM-managed stored procedure“. Ruft eine „WLM-managed
stored procedure“ SRRCMIT oder SRRBACK auf, setzt DB2
die Transaktion in einen „must roll back“ Status und der Aufruf
gibt den SQLCODE -919 zurück.
21
9.
‘s
9. Transaktionen
Transaktionen bei
bei STP
STP‘s
COMMIT und ROLLBACK in STP’s
•
Wird COMMIT oder ROLLBACK in einer „stored procedure“ durchgeführt, so betrifft das alle
Änderungen innerhalb der „unit of work“. Diese Änderungen schliessen auch die, die das Client-Programm vor dem Aufruf der STP gemacht hat, und die, die von der STP selbst durchgeführt wurden,
mit ein.
•
Eine STP, die COMMIT / ROLLBACK enthält, muss mit CONTAINS SQL, READS SQL DATA,
oder MODIFIES SQL DATA definiert sein. Es gibt keinen Zusammenhang zwischen der Klausel
COMMIT ON RETURN in einer STP und den COMMIT / ROLLBACK Statements im Code der STP.
Spezifiziert man COMMIT ON RETURN YES, so setzt DB2 ein COMMIT ab, wenn die Kontrolle von
der „stored procedure“ zurückgegen wird. Dies passiert immer, ohne Rücksicht darauf, ob die STP
COMMIT / ROLLBACK Statements enthält.
•
Ein ROLLBACK Statement wirkt sich auf Cursor in STP‘s genauso aus, wie auf Cursor in normalen
Programmen. Ein ROLLBACK schliesst ALLE „open cursors“. Ein COMMIT in einer STP schliesst
alle Cursor ausser denen mit WITH HOLD Definitionen.
•
Folgende Bedingungen lassen COMMIT / ROLLBACK Statements in einer „stored procedure“ nicht zu:
1.
2.
3.
4.
•
Dez, 04
Die STP wird innerhalb einer Triggers / „user-defined function“ aufgerufen.
Die STP wird von einem „client“ aufgerufen der „two-phase commit“ nutzt.
Das „client program“ benutzt eine “type 2 connection“ als Verbindung auf den „remote
server“, auf dem die STP läuft
DB2 ist nicht der „commit coordinator“
Verletzt ein COMMIT / ROLLBACK in einer STP o.g. Bedingungen, so setzte DB2 die Transaktion in
einen „must-rollback“ Status und das CALL Statement gibt einen -751 SQLCODE zurück.
22
10.
10.
Vorbereiten
‘s
Vorbereiten von
von STP
STP‘s
1.
Precompile / compile der Applikation. In COBOL mit der Option NODYNAM.
2.
Link-edit der Applikation. Die STP muss entweder eines der folgenden „language
interface“ Module „link-edit“en oder laden:
•
DSNALI das „language interface“ für das CAF. (immer, wenn die STP in einem
„DB2-established address space“ läuft)
•
DSNRLI das „language interface“ für das „Resource Recovery Services attachment
facility“. ( immer dann, wenn die STP in einem „WLM-established address space“ läuft;
z.B. bei LOB‘s). Der Parameter AMODE(31) ist beim „link-edit“ erforderlich.
3.
Das DBRM sollte mit BIND PACKAGE „gebunden“ werden. „Stored procedures“
benötigen lediglich ein „package“ auf der Serverseite.
4.
Definieren der „stored procedure“ im DB2-Katalog.
5.
GRANT EXECUTE zur Authorisierung des Benutzers für die „stored procedure“;
z.B
GRANT EXECUTE ON PROCEDURE SPSCHEMA.STORPRCA TO KRAUS;
Dez, 04
23
10.
10.
Vorbereiten
‘s
Vorbereiten von
von STP
STP‘s
BIND der „stored procedure“
•
Eine STP braucht keinen DB2 Plan, da sie unter dem „caller's thread“ läuft und
den Plan des Client Programms nutzt
User Workstation
DB2 System
UID=yyyy
UID=yyyy
STP Adress Space
UID=xxxx
Programm A
Package
B
EXEC SQL
CALL B
Programm
B
Package A
CALL B
Dez, 04
24
10.
10.
Vorbereiten
‘s
Vorbereiten von
von STP
STP‘s
BIND der „stored procedure“
•
Binden der STP mit BIND PACKAGE.
•
Der Owner des „package“, in dem das SQL Statement „CALL“ abgesetzt wird
muss das EXECUTE Privilege für alle Packages haben, die die STP nutzen,
inklusive derjenigen, die in SET CURRENT PACKAGESET Anweisungen
verwendet werden.
•
Wird eine “stored procedure” benutzt, so müssen folgende Objekte am Server
bereitstehen:
1.
Ein DB2-Plan oder ein Package, das die SQL-CALL’s enthält. Dies gehört zum DB2
Client-Programm.
2.
ein DB2 Package, das zur “stored procedure” gehört.
Wichtig:
Ein Package für ein Unterprogramm, das die SQL Statements enthält, muss an der
Lokation, an der die STP definiert wurde und an der Lokation wo die SQLStatements ausgeführt werden, existieren.
Dez, 04
25
11.
11.
REXX
‘s
REXX &
& STP
STP‘s
Schreiben einer REXX „stored procedure“
Dez, 04
•
Eine REXX „stored procedure“ sieht ähnlich jeder anderen REXX Prozedur
aus und folgt denselben Regeln, wie STP‘s in snderen LE‘s.
•
Unterschied gibt es in folgenden Punkten:
•
Eine REXX STP kann die Kommandos ADDRESS DSNREXX CONNECT und
ADDRESS DSNREXX DISCONNECT nicht nutzen. DB2 baut die „connection“ auf.
•
Eine REXX STP muss im „WLM-established stored procedures AS“ laufen.
•
REXX STPs müssen nicht speziell für die Ausführung vorbereitet werden (wie andere
STP‘s).REXX STP‘s nutzen einer der 4 Packages, die bei der Installation des DB2
REXX Language Support erstellt wurden. Das „ |isolation level“, das verwendet wird ,
wird über die Packages bestimmt:
Package name
Isolation level
DSNREXRR
Repeatable read (RR)
DSNREXRS
Read stability (RS)
DSNREXCS
Cursor stability (CS)
DSNREXUR
Uncommitted read (UR)
26
12.
12.
Die
‘s
Die SQL
SQL STP
STP‘s
Ein Vergleich
Beispiel einer „external stored procedure definition“
CREATE PROCEDURE UPDATESALARY1
(IN EMPNUMBR CHAR(10),
IN RATE DECIMAL(6,2) )
LANGUAGE COBOL
EXTERNAL NAME UPDSAL;
Beispiel einer „SQL procedure definition“
CREATE PROCEDURE UPDATESALARY1
(IN EMPNUMBR CHAR(10),
IN RATE DECIMAL(6,2) )
LANGUAGE SQL
UPDATE EMP
SET SALARY = SALARY * RATE
WHERE EMPNO = EMPNUMBR;
Dez, 04
27
12.
12.
Die
‘s
Die SQL
SQL STP
STP‘s
Zulässige Statements
Assignment statement
CALL Statement
CASE statement
GET DIAGNOSTICS
GOTO statement
IF statement
ITERATE statement
LEAVE statement
LOOP statement
REPEAT statement
WHILE statement
Compound statement
SQL statement
SIGNAL statement
RESIGNAL statement
RETURN statement
Dez, 04
Weist einem „output parameter“ oder einer SQL Variablen einen Wert zu
ruft eine andere „stored procedure“.
wie CASE in DB2 SQL.
enthält Information über ein vorangegangenes SQL Statement
Übergibt die Programmkontrolle an einen LOOP
Übergibt die Programmkontrolle aus einem BLOCK / LOOP
until „search condition“ true.
while „specified condition“ true.
SQL variable declarations, condition handlers, cursor declarations.
Procedure body statements (CALL, CASE, IF, LOOP, REPEAT, WHILE,
SQL)
ein „subset“ von SQL Statements
„ raise a condition“ mit einem spezifischen SQLSTATE und Meldungstext
s.o. + Rückgabe derselben „condition“ wie der Handler(!)
gibt einen „integer status“ Wert für die SQL procedure zurück.
28
12.
12.
Die
‘s
Die SQL
SQL STP
STP‘s
Deklaration und Verwendung von Variablen
•
Format:
DECLARE SQL-variable-name data-type;
DECLARE SQL-variable-name data-type
RESULT_SET_LOCATOR VARYING;
•
DB2 unterstützt nur die GENERAL WITH NULLS „linkage convention“ für SQL Procedures
•
Beenden von Statements in einer SQL Procedure: kein Terminator, wenn dann „;“
•
Types of handlers: CONTINUE oder EXIT
DECLARE CONTINUE HANDLER FOR NOT FOUND SET at_end=1; oder
DECLARE NO_TABLE CONDITION FOR '42704';
·
DECLARE EXIT HANDLER FOR NO_TABLE
SET OUT_BUFFER='Table does not exist'; oder
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
•
Beispiel:
CREATE PROCEDURE UPDATESALARY1
(IN EMPNUMBR CHAR(6),
OUT SQLCPARM INTEGER)
LANGUAGE SQL
....
BEGIN:
DECLARE SQLCODE INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET SQLCPARM = SQLCODE;
.....
Dez, 04
29
13.
13.
Beispiele
‘s
Beispiele SQL
SQL STP
STP‘s
Deklaration und Verwendung von Variablen
•
Beispiel GET DIAGNOSTICS:
CREATE PROCEDURE divide1
(IN numerator INTEGER,
IN denominator INTEGER,
OUT divide_result INTEGER,
OUT divide_error VARCHAR(70))
LANGUAGE SQL
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS CONDITION 1 divide_error = MESSAGE_TEXT;
SET divide_result = numerator / denominator;
END
•
Beispiel: Verwenden des RETURN Statement für Übergabe des „procedure status“
CREATE PROCEDURE TESTIT ()
LANGUAGE SQL
A1:BEGIN
DECLARE RETVAL INTEGER DEFAULT 0;
...
CALL TRYIT;
GET DIAGNOSTICS RETVAL = RETURN_STATUS;
IF RETVAL <> 0 THEN ...
LEAVE A1;
ELSE ...
END IF;
END A1
Dez, 04
30
13.
13.
•
Beispiele
‘s
Beispiele SQL
SQL STP
STP‘s
Beispiel von SQL STP-1:
CREATE PROCEDURE UPDATESALARY2
(IN EMPNUMBR CHAR(6),
IN RATING INT)
LANGUAGE SQL
MODIFIES SQL DATA
CASE RATING
WHEN 1 THEN
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.10
, BONUS = 1000
WHERE EMPNO = EMPNUMBR;
WHEN 2 THEN
ELSE
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.05
, BONUS = 500
WHERE EMPNO = EMPNUMBR;
UPDATE CORPDATA.EMPLOYEE
SET SALARY = SALARY * 1.03
, BONUS = 0
WHERE EMPNO = EMPNUMBR;
END CASE
Dez, 04
31
13.
13.
Beispiele
‘s
Beispiele SQL
SQL STP
STP‘s
CREATE PROCEDURE RETURNDEPTSALARY (IN DEPTNUMBER CHAR(3),
OUT DEPTSALARY DECIMAL(15,2), OUT DEPTBONUSCNT INT)
LANGUAGE SQL READS SQL DATA
P1: BEGIN
DECLARE EMPLOYEE_SALARY DECIMAL(9,2);
DECLARE EMPLOYEE_BONUS DECIMAL(9,2);
DECLARE TOTAL_SALARY DECIMAL(15,2) DEFAULT 0;
DECLARE BONUS_CNT INT DEFAULT 0;
DECLARE END_TABLE INT DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT SALARY, BONUS FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = DEPTNUMBER;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_TABLE = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET DEPTSALARY = NULL;
OPEN C1;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
WHILE END_TABLE = 0 DO
SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_SALARY + EMPLOYEE_BONUS;
IF EMPLOYEE_BONUS > 0 THEN SET BONUS_CNT = BONUS_CNT + 1; END IF;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
END WHILE;
CLOSE C1;
SET DEPTSALARY = TOTAL_SALARY;
SET DEPTBONUSCNT = BONUS_CNT;
END P1
Dez, 04
32
14.
14.
•
SQL
‘s und
SQL STP
STP‘s
und LINK
LINK
GENERAL
keine Übergabe von „null values“ für Eingabeparameter (IN
oder INOUT) an die „stored procedure“. Die STP muss eine
Variablendeklaration für jeden Parameter, der im CALL
Statement übergeben wird, haben.
•GENERAL WITH NULLS mit Übergabe von NULL-Werten an die STP.
•
1 Variable pro Parameter im CALL Statement.
•
“ null indicator structure” mit einer “indicator variablen” pro Parameter
•
Beim Eintritt: Prüfen der “indicator variables” zusammen mit den “input
parameters”
•
Beim Ausgang: Zuweisen der Werte auf die “indicator variables” und die
“output variables”.
Dez, 04
33
14.
14.
DB2
DB2 und
und Stored
Stored Procedures
Procedures Ablauf
Ablauf
DB2 “stored procedure”s werden definiert, indem eine Zeile in der
SYSIBM.SYSPROCEDURES-Tabelle des DB2-Katalogs eingetragen wird.
Diese Tabelle beschreibt alle Attribute der “stored procedure”. Für “stored procedures” ist
im MVS eine eigene Region, der sogenannte “DB2 Stored Procedures Address Space”
(xxxSPAS), eingerichtet.
Das SQL CALL Statement läuft wie folgt ab:
Dez, 04
1.
DB2 Thread wird erzeugt ( CONNECT)
2.
SQL-Statement wird abgesetzt
3.
DB2-Server erkennt SQL CALL
4.
SYSIBM.SYSPROCEDURES wird nach der PROC durchsucht. Das Ergebnis wird "gecached"
5.
DB2 findet einen TCB in xxxSPAS und DB2 läßt xxSPAS die STP starten
6.
xxxSPAS nutzt den DB2-Thread des USERS für die Abarbeitung der STP
7.
xxxSPAS nutzt die LE/370 Bibliotheken, um das der STP entsprechende "Load Module" zu laden und zu
starten
8.
nachdem die STP die Kontrolle erhalten hat, kann sie alle Parameter des SQL CALLS verarbeiten. Alle Locks
werden Teil dieser UoW.
9.
wird vom CALL-Programm COMMIT abgesetzt, so wird für alle beteiligten Server und Ressourcen ein
COMMIT verfügt
34
15.
15.
....
.... Und
Und sonst
sonst ???
???
Folgende SQL Operationen werden unterstützt:
1.
statisches / dynamisches SQL
2.
SQL-Statement mit 3-teiligen Namen
3.
DB2-Commands, z.B. -DISPLAY THREAD
4.
IFI zum lesen von DB2 TRACE-Sätzen
Restriktionen bei “stored procedures“:
Dez, 04
1.
eine STP kann einen ROLLBACK veranlassen, aber dieser ROLLBACK wird nicht
sofort wirksam. Die UoW wird hierfür in einen sogen. “must-rollback”-Status versetzt,
der den Client anweist einen ROLLBACK abzusetzen.
2.
eine STP kann folgende SQL-Statements nicht verwenden:
•
CONNECT,
•
SET CONNECTION und
•
RELEASE
35
15.
15.
....
.... Und
Und sonst
sonst ???
???
Erweiterungen im Zusammenhang mit ”stored procedures” :
•
DB2 Commands
-
DISPLAY PROCEDURE : Status, Anzahl laufender "requests", "queued requests" ....
-
-STOP PROCEDURE : stopped eine oder mehrere STP's
-
START PROCEDURE : started eine oder mehrere STP's
Beispiel: - START PROCEDURE(usreproc1, uerproc2)
•
Überlegungen zum Test von STP's
-
kein TSO TEST möglich
Test auf der Workstation (!)
CODE/370 zum Test von STP's
Benutzen der MSGFILE "runtime" Option
kleine "driver"-Applikationen mit Subroutine mit Aufruf auf die STP
Die
Diewichtigsten
wichtigstenVorteile
Vorteilevon
vonSTP's
STP's::
••
Reduktion
Reduktionvon
vonCPU-Zeit
CPU-Zeitund
und"elapsed
"elapsedtime"
time"
••
Verbesserung
der
"security
control"
Verbesserung der "security control"
••
"high
"highperformance"
performance"OLTP
OLTPApplikationen
Applikationen
••
Erweiterung
von
DRDA
Fähigkeiten
Erweiterung von DRDA - Fähigkeiten
Dez, 04
36