home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
C
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 5 supports SQL PL for implementing stored procedures, triggers, and user defined functions (UDFs). Together with PostgreSQL, both DB2 and MySQL thereby serve 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 benifit.

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,

Schedule

datedur.lang.  location  price
23 Nov2?Leuven  (BE)1000 EUR  (excl. VAT) 
SESSION INFO AND ENROLMENT

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. Forthermore they will gather useful ideas for automating some DBA tasks in a simple way.

Background

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 • parametrisation • 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.

Duration

2 days.

Course leader

Peter Vanroose.

Reviews

     
Goede uitleg van de theorie en een goede begeleiding bij het maken van de oefeningen. (, )
     
Cours avec une bonne approche par support aux exercices. Formateur maitrisant son sujet. (, )
     
Correct mais un peu trop de temps pour la pratique. (, )
     
interessante cursus. Misschien spreiden over 3 dagen zodat de oefeningen kunnen afgewerkt worden. Ofwel de oefeningen wat simpeler houden (, )
     
Programmation de stored-procedure (, )
     
uitstekend (, )
     
Voldoende voor mijn behoefte aan kennis (, )
     
Eerste dag was voor mij iets te langdradig/simpel. tweede en derde dag was nivo goed (, )
     
Beaucoup de pratique avec explication et aide constante permettent un apprentissage rapide (, )
     
Goede cursus, goede oefeningen (, )

Refresh this page to see other comments.


SESSION INFO AND ENROLMENT