IBM DB2 for z/OS - S.K. Consulting · 2019. 7. 22. · 8. von DB2 werden einige 3GL- und 4GL-Systeme als Sprachebene für “stored procedure” unterstützt: COBOL, PL/1, Assembler,

1Dez, 04

Stored Procedures bei DB2

IBM DB2 for z/OS

2Dez, 04

Inhalt:Inhalt: Stored Procedures b ei DB2Stored Procedures b ei DB2

• 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

ThemenThemenThemen

3Dez, 04

Die typische DRDA Applikation ist in der Regel wie folgt strukturiert:

1. Stored procedures - Übersicht1.1. Stored procedures Stored procedures -- ÜÜbersichtbersicht

der Client führt die Applikationslogik ausder 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 Client-Anwendung

- 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

•• V3 Client/ServerV3 Client/Server- als typische DRDA-1

Anwendung- der Client besitzt die AP-Logik- der Server führt (nur) die DB-

Verarbeitung (SQL-Processing) durch

Client-System Server-System

Präsentati- AP-Logik Datenver-onslogik waltung

4Dez, 04

•• DRDA mit „DRDA mit „stored procstored proc‘s“‘s“

- DRDA mit „stored procedures“

- AP-Logik Aufteilung zwischenClient und Server

- der Entwickler wählt die Art der Aufteilung der Aufgaben

Client-System Server-System

Präsentati- AP-Logik Datenver-onslogik waltung

DRDA mit “stored procedures” weist eine dazu vollständig unterschiedliche Architektur auf. Die Applikationslogik kann vollständig oder teilweise auf dem Server ablaufen.

1. Stored procedures - Übersicht1.1. Stored procedures Stored procedures -- ÜÜbersichtbersicht

Vorteile: - 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 – SystemsKrit. Faktoren:

- 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

5Dez, 04

2. DB2 und Stored Procedures2.2. DB2 und Stored ProceduresDB2 und Stored Procedures

Definition: Im Sprachgebrauch der IBM stellt eine “stored procedure” ein Anwendungspro-gramm dar, das am DB2-Server liegt und vom Client über einen SQL-CALL abge-rufen werden kann.Der Systementwickler kann also die Applikation je nach Bedarf in Client- und Serverteil aufsplitten.

OS/2DDCS

DB2EXEC SQL SELECT .....

EXEC SQL UPDATE .....

EXEC SQL INSERT ....

SQL Statementbearbeiten .....



6Dez, 04


OS/2DDCS

EXEC SQLCALL PROCX .....

M V S S y s t e m

DB2 DB2 StoredProcedures Region

Sched PROGX

Perform SQL

Perform SQL

Perform SQL

Return valuesto DDCS User

EXEC SQLSELECT .....

EXEC SQLUPDATE ....

EXEC SQLINSERT ....

endproc

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/CPU-Pfad bei verteilten Anwendungen länger ist, als bei lokalen.

Eine typische DRDA-Applikation benötigt meist eine “networksend/receive”-Operationpro SQL-Statement (gilt nicht für FETCH = 1 send/receive pro Block). In vielen Fällen können bis zu 70 SQL “send/receive”Operationen in einer Sekunde ablaufen. Dennoch ist die Netzbelastung bei großen DB2-Applikationen und die Antwortzeit bei relativ

7Dez, 04


Bei der Nutzung von “stored procedures” setzt die Client-Anwendung einen “networksend/receive” ab, um die “stored procedure” zu starten. Dies impliziert die Vorteile: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 bekommen6. der Administrator kann für “stored procedures" ein CPU-Limit und eine

“abend”-Grenze vorgeben7. “stored procedures" können von anderen Applikationen wiederverwen-

det werden wie OO-Objekte8. von DB2 werden einige 3GL- und 4GL-Systeme als Sprachebene für

“stored procedure” unterstützt: COBOL, PL/1, Assembler, C, VisualGen, JAVA

8Dez, 04


EXEC SQL CONNECT ...

EXEC SQL SELECT ....

EXEC SQL CALL X(:hv)

Return aus demSQL CALL

EXEC SQL COMMIT

M V S S y s t e m

DB2 StoredProcedures Region

DB2-System

create DB2 thread

Zugriff auf eineDB2-Tabelle ...

Hole PROC-Infovom DB2-Katalog

Übergib "requests"an xxxSPAS

Perform SQL

Locks gehören desentspr. UOW

Return IN/OUT-Werte und PARM

COMMIT Appl &Procedure

Reuse desDB2-Thread

Laden des Modulsfür Prozedur Xbeginproc EXEC SQL UPDATE ....

EXEC SQL INSERT ....endproc

Rückgabe Thread-Kontrolle an DB2

User-Applikation

9Dez, 04


“stored procedure‘s“ können am Client und am Server unterschiedliche Sprachenbenutzen Beispiel:

• Ein Client-Programm in C kann ein COBOL-Programm auf dem Server als Prozedur nutzen.

• DB2-”stored procedure”s folgen dem DRDA-Standard und damit ISO/ OSI-Richtlinien• DRDA bietet zudem die Formatkonvertierung, die eine Portierung von Daten zwischen

heterogenen Systemen ermöglichen soll• DRDA unterstützt zudem das 2-Phase-Commit-Protokoll, sodaß ein Client alle “stored

procedure”’s an allen möglichen Knoten nutzen kann (auch update-”storedprocedure”’s)

“stored procedures” können lokal oder über DDFangestossen werdenlokale CALL's können ohne DDF erfolgen

“stored procedure‘s“ können am Client und am Server unterschiedliche Sprachenbenutzen Beispiel:

• Ein Client-Programm in C kann ein COBOL-Programm auf dem Server als Prozedur nutzen.

• DB2-”stored procedure”s folgen dem DRDA-Standard und damit ISO/ OSI-Richtlinien• DRDA bietet zudem die Formatkonvertierung, die eine Portierung von Daten zwischen

heterogenen Systemen ermöglichen soll• DRDA unterstützt zudem das 2-Phase-Commit-Protokoll, sodaß ein Client alle “stored

procedure”’s an allen möglichen Knoten nutzen kann (auch update-”storedprocedure”’s)

“stored procedures” können lokal oder über DDFangestossen werdenlokale CALL's können ohne DDF erfolgen Programm A

.

.

.

.

EXEC SQL CALL B

DB2Package B

DB2Package oder Plan fürA

CALL B

Program m B

DB2 StoredProcedures RegionUser-Applikation

DB2-System

10Dez, 04

3. DB2 und Stored Procedures Aufrufe3.3. DB2 und Stored Procedures AufrufeDB2 und Stored Procedures Aufrufe

Der SQL CALL ist Bestandteil des ISOI/ANSI-Standards für SQL3. Es wird im statischen SQL unterstützt.

Beispiel: CALL PROCX ( :hv1, 4567, :hv3 )

CALL :prcnm ( :hv1, :hv2, 'ABCD', 1234 )CALL PROCX USING DESCRIPTOR sqlda

CALL :prcnm USING DESCRIPTOR sqlda“

Der SQL CALL ist Bestandteil des ISOI/ANSI-Standards für SQL3. Es wird im statischen SQL unterstützt.

Beispiel: CALL PROCX ( :hv1, 4567, :hv3 )

CALL :prcnm ( :hv1, :hv2, 'ABCD', 1234 )CALL PROCX USING DESCRIPTOR sqlda

CALL :prcnm USING DESCRIPTOR sqlda“

Das SQL CALL Statement läuft wie folgt ab:1. DB2 Thread wird erzeugt ( CONNECT)2. SQL-Statement wird abgesetzt3. DB2-Server erkennt SQL CALL4. 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 starten6. xxxSPAS nutzt den DB2-Thread des USERS für die Abarbeitung der STP7. xxxSPAS nutzt die LE/370 Bibliotheken, um das der STP entsprechende "Load Module" zu laden und zu

starten8. 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

11Dez, 04

4. Beispiel einer einfachen STP4.4. Beispiel einer einfachen STPBeispiel einer einfachen STP

Eine Applikation läuft auf einer Workstation als Client und ruft die “stored procedure” A auf der DB2 ServerLokation 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: employee number PRJ: project number ACT: activity ID EMT: percent of employee's time required EMS: date the activity starts EME: 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 SpaltenEMPTIME 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.

12Dez, 04

4. Beispiel einer einfachen STP4.4. Beispiel einer einfachen STPBeispiel einer einfachen STP

13Dez, 04

5. Voraussetzungen für STP‘s5.5. Voraussetzungen fVoraussetzungen füür r STPSTP‘‘ss

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 „DB2-established“ 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“.

14Dez, 04

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 sindBestandteil 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

USERID = yyyy

.

.

.

.

EXEC SQL CALL B

DB2Package oder Plan fürA

CALL B

Programm B

DB2 StoredProcedures Region

User-Applikation

DB2-System

DB2 AgentUSERID=yyyy

USERIDxxxx

Andere MVS-Ressource

5. Voraussetzungen für STP‘s5.5. Voraussetzungen fVoraussetzungen füür r STPSTP‘‘ss

15Dez, 04

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.BCURRENT 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 PACKAGESETaus, 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 definizionenthä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.

6. Aufrufen fremder Programme aus einer STP6.6. Aufrufen fremder Programme aus einer STPAufrufen fremder Programme aus einer STP

16Dez, 04

7. Die Entwicklung von STP‘s7.7. Die Entwicklung von Die Entwicklung von STPSTP‘‘ss

Nutzen von “reentrant Code” in einer ”stored procedure”• 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 reentrantkompiliert 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.

17Dez, 04


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.

18Dez, 04


Schreiben einer ”stored procedure” als Haupt- oder Unterprogramm

Characteristiken von Haupt- und UPros:

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 mitOPTIONS(MAIN) OPTIONS(FETCHABLE)

19Dez, 04

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;

20Dez, 04

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; }}

21Dez, 04

• Keine expliziten Calls an das „attachment facility“ in einer stored procedure. „Stored procedures“ laufen in einem DB2-“adress space“ und benutzen das „attachmentfacility“ (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-managedstored procedure“ SRRCMIT oder SRRBACK auf, setzt DB2 die Transaktion in einen „must roll back“ Status und der Aufruf gibt den SQLCODE -919 zurück.

8. Restriktionen bei STP‘s8.8. Restriktionen bei Restriktionen bei STPSTP‘‘ss

22Dez, 04

• 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-Pro-gramm 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 schliesstalle Cursor ausser denen mit WITH HOLD Definitionen.

• Folgende Bedingungen lassen COMMIT / ROLLBACK Statements in einer „stored procedure“ nicht zu: 1. Die STP wird innerhalb einer Triggers / „user-defined function“ aufgerufen. 2. Die STP wird von einem „client“ aufgerufen der „two-phase commit“ nutzt. 3. Das „client program“ benutzt eine “type 2 connection“ als Verbindung auf den „remote

server“, auf dem die STP läuft 4. 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.

9. Transaktionen bei STP‘s9.9. Transaktionen bei Transaktionen bei STPSTP‘‘ss

COMMIT und ROLLBACK in STP’s

23Dez, 04

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;

10. Vorbereiten von STP‘s10.10. Vorbereiten von Vorbereiten von STPSTP‘‘ss

24Dez, 04

• Eine STP braucht keinen DB2 Plan, da sie unter dem „caller's thread“ läuft und den Plan des Client Programms nutzt


BIND der „stored procedure“

EXEC SQL CALL B

User Workstation DB2 System STP Adress Space

Programm A

Package B

Package A

CALL B

ProgrammB

UID=yyyy UID=yyyy UID=xxxx

25Dez, 04


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 Serverbereitstehen: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 SQL-Statements ausgeführt werden, existieren.

26Dez, 04

11. REXX & STP‘s11.11. REXX & REXX & STPSTP‘‘ss

Schreiben einer REXX „stored procedure“

• Eine REXX „stored procedure“ sieht ähnlich jeder anderen REXX Prozeduraus 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)

27Dez, 04

12. Die SQL STP‘s12.12. Die SQL Die SQL STPSTP‘‘ss

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;

28Dez, 04


Zulässige Statements

Assignment statement Weist einem „output parameter“ oder einer SQL Variablen einen Wert zu CALL Statement ruft eine andere „stored procedure“. CASE statement wie CASE in DB2 SQL. GET DIAGNOSTICS enthält Information über ein vorangegangenes SQL StatementGOTO statementIF statementITERATE statement Übergibt die Programmkontrolle an einen LOOP LEAVE statement Übergibt die Programmkontrolle aus einem BLOCK / LOOPLOOP statementREPEAT statement until „search condition“ true. WHILE statement while „specified condition“ true. Compound statement SQL variable declarations, condition handlers, cursor declarations.

Procedure body statements (CALL, CASE, IF, LOOP, REPEAT, WHILE, SQL)

SQL statement ein „subset“ von SQL StatementsSIGNAL statement „ raise a condition“ mit einem spezifischen SQLSTATE und Meldungstext RESIGNAL statement s.o. + Rückgabe derselben „condition“ wie der Handler(!)RETURN statement gibt einen „integer status“ Wert für die SQL procedure zurück.

29Dez, 04


Deklaration und Verwendung von Variablen

• Format: DECLARE SQL-variable-name data-type; DECLARE SQL-variable-name data-typeRESULT_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; oderDECLARE NO_TABLE CONDITION FOR '42704'; ·DECLARE EXIT HANDLER FOR NO_TABLE

SET OUT_BUFFER='Table does not exist'; oderDECLARE 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;

.....

30Dez, 04

13. Beispiele SQL STP‘s13.13. Beispiele SQL Beispiele SQL STPSTP‘‘ss

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

31Dez, 04


• 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 UPDATE CORPDATA.EMPLOYEE SET SALARY = SALARY * 1.05


ELSE UPDATE CORPDATA.EMPLOYEE SET SALARY = SALARY * 1.03


END CASE

32Dez, 04


CREATE PROCEDURE RETURNDEPTSALARY (IN DEPTNUMBER CHAR(3), OUT DEPTSALARY DECIMAL(15,2), OUT DEPTBONUSCNT INT)

LANGUAGE SQL READS SQL DATAP1: 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

33Dez, 04

14. SQL STP‘s und LINK14.14. SQL SQL STPSTP‘‘ss und LINKund 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”.

34Dez, 04

14. DB2 und Stored Procedures Ablauf14.14. DB2 und Stored Procedures AblaufDB2 und Stored Procedures Ablauf

Das SQL CALL Statement läuft wie folgt ab:1. DB2 Thread wird erzeugt ( CONNECT)2. SQL-Statement wird abgesetzt3. DB2-Server erkennt SQL CALL4. 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 starten6. xxxSPAS nutzt den DB2-Thread des USERS für die Abarbeitung der STP7. xxxSPAS nutzt die LE/370 Bibliotheken, um das der STP entsprechende "Load Module" zu laden und zu

starten8. 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

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.

35Dez, 04

15. .... Und sonst ???15.15. .... Und sonst ???.... Und sonst ???

Folgende SQL Operationen werden unterstützt:1. statisches / dynamisches SQL2. SQL-Statement mit 3-teiligen Namen3. DB2-Commands, z.B. -DISPLAY THREAD4. IFI zum lesen von DB2 TRACE-Sätzen

Restriktionen bei “stored procedures“: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

36Dez, 04

15. .... Und sonst ???15.15. .... Und sonst ???.... Und 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'sBeispiel: - 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 wichtigsten Vorteile von STP's :• Reduktion von CPU-Zeit und "elapsed time"• Verbesserung der "security control"• "high performance" OLTP Applikationen• Erweiterung von DRDA - Fähigkeiten

Die wichtigsten Vorteile von STP's :• Reduktion von CPU-Zeit und "elapsed time"• Verbesserung der "security control"• "high performance" OLTP Applikationen• Erweiterung von DRDA - Fähigkeiten

Documents

IBM DB2 for z/OS - S.K. Consulting · 2019. 7. 22. · 8. von DB2 werden einige 3GL- und 4GL-Systeme als Sprachebene für “stored procedure” unterstützt: COBOL, PL/1, Assembler,