About ABIS
All CoursesbalkjeGeneral courses » Introduction to HW & SW » Soft skills » TracksOperating systems » MVS - z/OS » Linux - UNIX » Mac OS X » iPad and iPhone iOSDatabases and middleware » Relational databases & SQL » Db2 for z/OS » Db2 for LUW » Oracle » SQL Server » MySQL & MariaDB » IMS » CICS » IBM MQ » WebSphere » Data Science, Big Data and AnalyticsApplication development » Methods and techniques » TOGAF » PRINCE2 » Agile development and Scrum » Programming languages » Internet development » Object Oriented systems » Java » Development tools » SAS » XML » SOA & web servicesSystems management » ITIL » SecuritybalkjePractical informationRegistration 
SQL PL database programming

Since version 9, Db2 has greatly extended its support of the SQL 'Procedural Language', known as SQL PL. Currently essentially all syntax possibilities of the SQL standard for procedural logic inside SQL are supported. Recently, Db2 even added support for the "ARRAY" data type, only within SQL PL.

Also MySQL (since version 5) and MariaDB support SQL PL for implementing stored procedures, triggers, and user defined functions (UDFs). Db2, MySQL and MariaDB thereby serve, together with PostgreSQL, as vanguard in the implementation of the "Persistent Stored Modules" (PSM) of the SQL standard for ISO and ANSI (since SQL:1999).

Unfortunately, Oracle uses an other procedural language viz. PL/SQL (see Software development with PL/SQL) for which this course is not appropriate. Also Transact-SQL, used by SQL Server, is not compatible with SQL PL.

SQL PL is especially useful for creating stored procedures, which will more and more replace applications which in the earlier days were mainly written in (for z/OS) COBOL, PL/I, REXX, (for Unix) Java, C, C++, (for MS-Windows), and Java, or C#. SQL PL will thus become the dedicated programming language for integrating application logic with embedded SQL. On Db2, this automatically means that the procedure will be "static SQL": it will be interpreted just once, and then executed several times, which is an important performance benefit.

This course primarily covers the programming language SQL PL; all syntactic details and possibilities will be treated in sufficient detail. This does of course include the interaction between the elements of the programming environment (like variables, program flow, exception handling) and the embedded DML ingredients like SELECT statements or cursors. The participants will have the opportunity to develop themselves some stored procedures with SQL PL during the course, either on Db2 for z/OS, on Db2 for LUW, on PostgreSQL, or on MySQL, in order to make themselves familiar with this new material.

When completing this course,


datedur.lang.  location  price
27 Sep2?Leuven  (BE)1000 EUR  (excl. VAT) 

Intended for

This course is first of all meant for application programmers and developers of programs that make use of Db2 or MySQL, and who want to learn how to write this kind of programs with SQL PL.

Also DBAs will be able to build up the necessary experience during this course to build or deploy stored procedures, triggers or user-defined functions. Furthermore they will gather useful ideas for automating some DBA tasks in a simple way.


Some experience with SQL and with Db2 (cf. Db2 for z/OS fundamentals course or Db2 for LUW fundamentals course), MySQL (cf. MySQL & MariaDB fundamentals course) or PostgreSQL is needed. Knowledge of an other procedural programming language (like COBOL, PL/I, REXX, Java, C, ...) might be an advantage.

Main topics

declaration, initialisation, use; BEGIN ... END blocks

conditional structures IF...THEN...ELSE structure; CASE-statement;

iterations: WHILE..DO loop • FOR...DO loop • ITERATE • REPEAT...UNTIL • LOOP • cursor-based FOR-loop

SELECT INTO • declaration and use of cursors • parametrization • insert, delete, update 'where current of' cursor

predefined exceptions • applicatively defined exception handling • SIGNAL • GET DIAGNOSTICS

user-defined scalar functions • native stored procedures • triggers • SQL CALL-statement;

possibilities of parameter declaration and 'locators'; possible options like 'commit on return'

PREPARE and EXECUTE of a dynamically built query

Training method

Classroom instruction with exercises.


2 days.

Course leader

Peter Vanroose.

Global score

4.0/5 (based on 49 evaluations)


J'ai appris pas mal de choses intéressantes (, )
De leraar kon de materie goed overbrengen (, )
Cours avec une bonne approche par support aux exercices. Formateur maitrisant son sujet. (, )
Correct mais un peu trop de temps pour la pratique. (, )
Prima cursus. (, )
Voldoende voor mijn behoefte aan kennis (, )
Satisfait, explications détaillées sur les chapitres, avec le lien avec le système db2 (, )
Beaucoup de pratique avec explication et aide constante permettent un apprentissage rapide (, )
Programmation de stored-procedure (, )
Niveau misschien wat hoog voor sommige voorbeelden (wegens tot nu ongebruikelijk in onze omgeving). (, )

Refresh this page to see other comments.