Mastering DB2 Stored Procedures and UDFs: A Primer for Advanced Users

DB2 Stored Procedures and UDFs: A Primer

Have you ever wondered how to automate some tasks in your DB2 database? Or how to create custom functions that can be used in your queries? If so, then you might be interested in learning about stored procedures and user-defined functions (UDFs). These are two powerful features of DB2 that can help you improve your productivity, performance and flexibility when working with data.

DB2 Stored Procedures and UDFs: A Primer.pdf

Download Zip: https://www.google.com/url?q=https%3A%2F%2Fgohhs.com%2F2ucRRM&sa=D&sntz=1&usg=AOvVaw2fNyj0tjbhbKy_wwOlS1md

In this article, we will give you a primer on stored procedures and UDFs in DB2. We will explain what they are, why they are useful, how they are different, how to create them and how to use them. We will also give you some tips on how to optimize them for better performance. By the end of this article, you will have a solid understanding of these concepts and be able to apply them in your own projects.

How to create stored procedures and UDFs in DB2

Before we can use stored procedures and UDFs in DB2, we need to create them first. In this section, we will show you how to do that using the SQL statements and the SQL PL language.

Creating stored procedures

A stored procedure is a named set of SQL statements that can be executed as a unit. It can accept input parameters, return output parameters or result sets, or perform some actions on the database. A stored procedure can be invoked by other programs or applications, or by other stored procedures.

To create a stored procedure in DB2, we use the CREATE PROCEDURE statement. This statement allows us to specify the name, the parameters, the language, the options and the body of the stored procedure. Here is an example of a simple stored procedure that takes two input parameters and returns their sum as an output parameter:

CREATE PROCEDURE add_numbers (IN num1 INTEGER, IN num2 INTEGER, OUT sum INTEGER) LANGUAGE SQL BEGIN SET sum = num1 + num2; END

The LANGUAGE clause specifies the language of the stored procedure. In this case, we use SQL, which means we can use the SQL PL language to write the body of the stored procedure. SQL PL is a procedural extension of SQL that allows us to use variables, control structures, condition handlers, dynamic SQL and other features.

The BEGIN and END keywords mark the beginning and the end of the body of the stored procedure. Inside the body, we can use SQL statements or SQL PL statements. In this example, we use the SET statement to assign the value of num1 + num2 to the output parameter sum.

The parameters of the stored procedure can have different modes: IN, OUT or INOUT. The IN mode means that the parameter is an input value that is passed to the stored procedure. The OUT mode means that the parameter is an output value that is returned by the stored procedure. The INOUT mode means that the parameter is both an input and an output value.

A stored procedure can also return one or more result sets, which are collections of rows that can be retrieved by the caller. To return a result set, we need to use a cursor and a RETURN statement. Here is an example of a stored procedure that returns a result set with all the employees from a table:

CREATE PROCEDURE get_employees () RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM employees; OPEN c1; RETURN; END

The RESULT SETS clause specifies how many result sets the stored procedure can return. In this case, we specify 1, which means only one result set. The DECLARE statement declares a cursor named c1, which is associated with a query that selects all rows from the employees table. The CURSOR WITH RETURN option indicates that this cursor will be used to return a result set. The OPEN statement opens the cursor and executes the query. The RETURN statement returns control to the caller and makes the result set available for retrieval.

A stored procedure can also contain compound statements, which are blocks of statements that are enclosed by BEGIN ATOMIC, BEGIN NOT ATOMIC, or BEGIN END. A compound statement can have its own variables, cursors, condition handlers, labels and dynamic SQL statements. A compound statement can also be nested inside another compound statement.

A condition handler is a statement that specifies what action to take when a certain condition occurs, such as an error, a warning or a signal. A condition handler can be declared using the DECLARE statement with one of these options: HANDLER FOR SQLEXCEPTION, HANDLER FOR SQLWARNING, or HANDLER FOR NOT FOUND. A condition handler can also specify a specific SQLSTATE value or a list of values to handle. Here is an example of a condition handler that handles any exception and prints a message:

DECLARE stmt VARCHAR(100); SET stmt = 'INSERT INTO customers VALUES (?, ?, ?)'; PREPARE s1 FROM stmt; EXECUTE s1 USING :name, :address, :phone;

The DECLARE statement declares a variable named stmt, which will hold the SQL statement. The SET statement assigns the SQL statement to the variable, using question marks as placeholders for the values to be inserted. The PREPARE statement prepares the SQL statement for execution and assigns it a name s1. The EXECUTE statement executes the prepared SQL statement using the host variables :name, :address and :phone as the values for the placeholders.

To debug stored procedures, we can use some tools that are provided by DB2, such as the SQL Procedure Debugger, the SQL Procedure Builder or the SQL Stored Procedure Language Editor. These tools allow us to set breakpoints, step through the code, inspect variables and watch expressions, view call stacks and trace files, and so on.

Creating UDFs

A user-defined function (UDF) is a named function that can be used in SQL statements to perform some calculation or operation on one or more arguments and return a value or a table. A UDF can be invoked by other programs or applications, or by other UDFs.

CREATE FUNCTION multiply (num1 INTEGER, num2 INTEGER) RETURNS INTEGER LANGUAGE SQL RETURN num1 * num2;

The RETURNS clause specifies the data type of the value that the UDF returns. In this case, we specify INTEGER, which means the UDF returns an integer value. The LANGUAGE clause specifies the language of the UDF. In this case, we use SQL, which means we can use the SQL PL language to write the body of the UDF. The RETURN statement returns the value of num1 * num2 as the result of the UDF.

A UDF can also return a table, which is a collection of rows that can be used in a query. To return a table, we need to use a RETURNS TABLE clause and a RETURN TABLE statement. Here is an example of a UDF that returns a table with all the employees who have a salary higher than a given amount:

CREATE FUNCTION get_high_salary (amount DECIMAL(10,2)) RETURNS TABLE (id INTEGER, name VARCHAR(50), salary DECIMAL(10,2)) LANGUAGE SQL RETURN TABLE (SELECT id, name, salary FROM employees WHERE salary > amount);

The RETURNS TABLE clause specifies the columns and their data types of the table that the UDF returns. In this case, we specify three columns: id, name and salary. The RETURN TABLE statement returns a table that is derived from a query that selects all rows from the employees table where the salary is greater than the input parameter amount.

CREATE FUNCTION sqrt (num DOUBLE) RETURNS DOUBLE LANGUAGE C PARAMETER STYLE SQL EXTERNAL NAME 'mylib!sqrt';

The LANGUAGE clause specifies the language of the external function. In this case, we specify C, which means the function is written in C. The PARAMETER STYLE clause specifies how the parameters and the return value are passed between the UDF and the external function. In this case, we specify SQL, which means the parameters and the return value are passed as SQL data types. The EXTERNAL NAME clause specifies the name and location of the external program or library that contains the function. In this case, we specify mylib!sqrt, which means the function is named sqrt and is located in a library named mylib.

A UDF can also be defined as an inline SQL function, which means that the body of the UDF is a single SQL statement that returns a scalar value or a table. To create an inline SQL function, we need to use a RETURNS clause and a RETURN statement. Here is an example of an inline SQL function that returns the average salary of all employees:

CREATE FUNCTION avg_salary () RETURNS DECIMAL(10,2) RETURN (SELECT AVG(salary) FROM employees);

The RETURNS clause specifies the data type of the value that the UDF returns. In this case, we specify DECIMAL(10,2), which means the UDF returns a decimal value with 10 digits and 2 decimal places. The RETURN statement returns a scalar value that is derived from a query that calculates the average salary of all employees.

How to use stored procedures and UDFs in DB2

After we create stored procedures and UDFs in DB2, we can use them in various ways. In this section, we will show you how to use them in SQL statements or in other programs or applications.

Using stored procedures

CALL add_numbers(10, 20, ?);

The CALL statement invokes the stored procedure named add_numbers and passes 10 and 20 as the input parameters. The question mark is a placeholder for the output parameter, which will be assigned the value of the sum after the stored procedure is executed. We can use a host variable or a parameter marker to receive the output parameter value. For example, we can use a host variable named :result and write:

CALL add_numbers(10, 20, :result);

This will assign the value of the output parameter to the host variable :result, which we can then use in other statements or programs.

If a stored procedure returns one or more result sets, we can use a cursor to retrieve them. Here is an example of using a cursor to retrieve the result set from the stored procedure that returns all employees:

DECLARE c1 CURSOR FOR CALL get_employees(); OPEN c1; FETCH c1 INTO :id, :name, :salary; -- do something with the fetched values CLOSE c1;

The DECLARE statement declares a cursor named c1, which is associated with the CALL statement that invokes the stored procedure named get_employees. The OPEN statement opens the cursor and executes the stored procedure. The FETCH statement retrieves a row from the cursor and assigns the values to the host variables :id, :name and :salary. We can then use these host variables in other statements or programs. The CLOSE statement closes the cursor.

CREATE PROCEDURE call_add_numbers (IN num1 INTEGER, IN num2 INTEGER, OUT sum INTEGER) LANGUAGE SQL BEGIN CALL add_numbers(num1, num2, sum); END

The CALL statement inside the body of the stored procedure invokes the stored procedure named add_numbers and passes the input parameters num1 and num2 to it. The output parameter sum is also passed to the stored procedure and will be assigned the value of the sum after the stored procedure is executed. The output parameter sum will then be returned by the stored procedure that calls it.

To use a stored procedure in DB2, we also need to have the appropriate privileges. The privileges that are required depend on the type and the mode of the parameters, the result sets, and the actions that are performed by the stored procedure. For example, to execute a stored procedure that has only input parameters and no result sets, we need to have the EXECUTE privilege on the stored procedure. To execute a stored procedure that has output parameters or result sets, we also need to have the READ privilege on the stored procedure. To execute a stored procedure that performs some actions on the database, such as inserting, updating or deleting data, we also need to have the corresponding privileges on the tables or views that are affected by the actions.

Using UDFs

To use a UDF in DB2, we can use it in a SQL statement as if it were a built-in function. Here is an example of using a UDF in a SELECT statement to calculate the product of two numbers:

SELECT multiply(10, 20) FROM sysibm.sysdummy1;

The SELECT statement invokes the UDF named multiply and passes 10 and 20 as the arguments. The UDF returns the value of 10 * 20 as a scalar value. The sysibm.sysdummy1 table is a special table that contains only one row and one column, and is used to return a single value.

-- using a UDF in the WHERE clause to filter rows based on a condition SELECT * FROM employees WHERE sqrt(salary) > 100; -- using a UDF in the GROUP BY clause to group rows based on a function SELECT department, avg_salary() AS average_salary FROM employees GROUP BY department; -- using a UDF in the HAVING clause to filter groups based on a condition SELECT department, avg_salary() AS average_salary FROM employees GROUP BY department HAVING avg_salary() > 5000; -- using a UDF in the ORDER BY clause to sort rows based on a function SELECT name, salary FROM employees ORDER BY sqrt(salary) DESC; -- using a UDF in the JOIN clause to join tables based on a function SELECT e.name, e.salary, d.name AS department FROM employees e JOIN departments d ON e.department = d.id AND multiply(e.salary, d.bonus) > 10000;

If a UDF returns a table, we can use it as a table expression in a query. A table expression is a subquery that returns a table and can be used as a source of data. Here is an example of using a table expression that invokes a UDF that returns all employees who have a high salary:

SELECT * FROM (SELECT * FROM get_high_salary(5000)) AS high_salary_employees;

The SELECT statement uses a table expression that invokes the UDF named get_high_salary and passes 5000 as the argument. The UDF returns a table with all employees who have a salary higher than 5000. The table expression is given an alias high_salary_employees, which can be used to refer to the columns of the table. The SELECT statement returns all rows and columns from the table expression.

How to optimize stored procedures and UDFs in DB2

Stored procedures and UDFs can improve the performance of our applications by reducing the network traffic, simplifying the logic, reusing the code and caching the results. However, they can also introduce some overhead and complexity that can affect the performance. In this section, we will give you some tips on how to optimize stored procedures and UDFs in DB2 for better performance.

Optimizing stored procedures

REOPT ALWAYS, which indicates that the stored procedure should be recompiled every time it is executed. This option can improve the performance if the stored procedure contains dynamic SQL statements that depend on the input parameters or the data in the database. However, this option can also increase the overhead of recompilation and consume more resources. REOPT ONCE, which indicates that the stored procedure should be recompiled only once when it is executed for the first time after it is created or altered. This option can improve the performance if the stored procedure contains static SQL statements that do not depend on the input parameters or the data in the database. However, this option can also result in suboptimal execution plans if the input parameters or the data in the database change significantly over time. REOPT NEVER, which indicates that the stored procedure should not be recompiled at all. This option can improve the performance if the stored procedure contains static SQL statements that do not depend on the input parameters or the data in the database, and if the input parameters or the data in the database do not change significantly over time. However, this option can also result in errors or poor performance if the stored procedure contains dynamic SQL statements that depend on the input parameters or the data in the database. OPTIMIZATION LEVEL, which specifies a numeric value between 0 and 9 that indicates how much optimization should be performed on the stored procedure. A higher value means more optimization, but also more compilation time and resources. A lower value means less optimization, but also less compilation time and resources. The default value is 5, which means a moderate level of optimization. This option can affect various aspects of optimization, such as index selection, join order, join method, access path, parallelism and so on. OPTIMIZATION PROFILE, which specifies a name of an XML document that contains some hints or directives for optimizing the stored procedure. These hints or directives can override or supplement the default optimization behavior of DB2. For example, we can use an optimization profile to force or avoid a certain index, join order, join method, access path, parallelism and so on. This option can help us fine-tune the performance of specific stored procedures based on our knowledge of the data and the application logic.

Optimizing UDFs

DETERMINISTIC, which indicates that the UDF always returns the same result for the same input arguments. This option can improve the performance by allowing DB2 to cache and reuse the results of the UDF. However, this option can also result in errors or poor performance if the UDF does not always return the same result for the same input arguments. For example, if the UDF depends on some external factors, such as the current date, time, user or environment variables. NOT FENCED, which indicates that the UDF can run in the same process as DB2. This option can improve the performance by reducing the overhead of interprocess communication and context switching. However, this option can also result in errors or instability if the UDF is not well-written or tested. For example, if the UDF causes a memory leak, a segmentation fault, an infinite loop or a deadlock. NO SQL, which indicates that the UDF does not contain any SQL statements or access any data in the database. This option can improve the performance by allowing DB2 to optimize the UDF more aggressively and inline it into other SQL statements. However, this option can also result in errors or poor performance if the UDF does contain SQL statements or access data in the database. For example, if the UDF uses a curso