A DB2 Routines is an executable SQL object. A objects in the database can encapsulate SQL statements and procedural logic, is saved inside the database server, and can be invoked by utilizing the statements of SQL for example, the CALL proclamations. There are two types of DB2 Routines such as:

Function is a accumulation of statements, which contains some particular storage in the DB2 database server. The function takes a parameter and is utilized to perform an operation by using DB2 and return the outcome set. The function can call a function at any time, once the code is stored in the database.

By using the below example, the concept of functions can be easily understand. [sql] db2 => CREATE FUNCTION func1(str CHAR(20)) -> RETURNS CHAR(50) -> RETURN CONCAT('WELCOME TO, ',str,'!'); SQL executed ok (0.52 sec) db2 => SELECT func('SPLessons.COM'); func('SPLessons.COM') ----------------------------- WELCOME TO, SPLessons.COM! ----------------------------- 1 record(s) selected. [/sql] In the above example, the function will read the string character and display the output as welcome to SPLessons.COM.

Create procedure is utilized to create a procedure with the procedure name followed by parameters within the parentheses. Parameters can be announced for accessing the datatypes except few attributes. The procedure can adjust the values however when the procedure gives back the values then alterations is not noticeable to the client.

By using the below example, the concept of create a procedure can be easily understood. [sql] db2 => select * from employee01; emp_id emp_name city designation salary perks -------- ---------- --------- ---------------- ------------ ------- 1 david delhi manager 12000 855 2 shaha mumbai assistant manager 13000 853 3 sha puna scales manager 11000 850 4 jack bangalore designer 14000 854 5 james mangalore web designer 15000 1124 6 mike chennai develpoer 15500 840 6 record(S) selected. db2 => delimiter $$ db2 => create procedure proc3(out p1 int) -> select count(*) into p1 from employee01; -> $$ SQL executed ok (0.52 sec) db2 => delimiter ; db2 => call proc3(@a); SQL executed ok (0.52 sec) db2 => select @a; @a ------ 6 1 record(s) selected [/sql] In the above example, the procedure proc3 will count all the employees in the employee01 table from the  IN parameter and pass the values to OUT parameter.(i.e,proc3 will count all the emp_id from the table employee01 and display the output).

• DB2 Routines - Is an executable SQL objects.
• Functions - Is a collection of statements in DB2 Database.
• Procedures - Is a collection of SQL statements that are stored in the database.