Before you start
About this series
Thinking about seeking certification on DB2 SQL Procedure Developer (Exam 735)? If so, you've landed in the right spot. This series of six DB2 certification preparation tutorials covers all the basics -- the topics you'll need to understand before you read the first exam question. Even if you are not planning to seek certification right away, this set of tutorials is a great place to start learning all about database development for DB2 V9.5.
About this tutorial
This tutorial introduces you to advanced SQL features you need to understand to fully utilize DB2 9.5. Get an introduction to
This is the fifth in a series of six tutorials you can use to help prepare for the DB2 9.5 SQL Procedure Developer exam 735. The material in this tutorial primarily covers the objectives in Section 5 of the test, which is entitled "Advanced SQL Features." See Resources.
- Learn how you can use global temporary tables
- Understand how to use hierarchical queries and the MERGE statement
- See how you can use ADMIN_CMD routine to perform administration task within stored procedures
- Discover how the GET_DIAGNOSTICS statement can help you with error handling
- Understand SAVEPOINTS and the use of XML technology in stored procedures
This tutorial is written for Linux®, UNIX®, or Windows® database developers or administrators, whose skills and experience are at a beginning to intermediate level. You should have a general familiarity with using a UNIX or Windows command-line shell and a working knowledge of DB2 and SQL commands.
The examples in this tutorial are specific to DB2 9.5 running on a Windows operating system. The concepts and information provided are relevant to DB2 running on any distributed platform.
You do not need a copy of DB2 9.5 to complete this tutorial. However, you will get more out of the tutorial if you download the free trial version of IBM DB2 9.5 to work along with this tutorial.
Global declared temporary tables
Temporary tables are most often used to store temporary and intermediate results. They can increase performance because they do not require logging and do not appear in the system catalog. Also, they do not require locking because the temporary table only allows single connection access.
A declared global temporary table (DGTT) is accessible only by the connection in which it was declared (created). It is dropped when this connection to the database is ended.
To create DGTT, you need to issue the DECLARE GLOBAL TEMPORARY TABLE statement. The following is the syntax diagram for this statement:
Listing 1. Syntax for global temporary table declaration
Please note that when the
WITH REPLACE clause is
specified, an existing DGTT with the same name is dropped and replaced with a new table definition.
Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.
Following is an explanation of some of these options:
ON COMMIT DELETE ROWS: When a COMMIT operation is performed, all rows of the table are deleted if no WITH HOLD cursor is open on the table. This is the default.
ON COMMIT PRESERVE ROWS: When a COMMIT operation is performed, all rows of the table are preserved.
ON ROLLBACK DELETE ROWS: When a ROLLBACK (or ROLLBACK to SAVEPOINT) operation is performed if the table data has been changed, all rows of this table are deleted. This is default.
ON ROLLBACK PRESERVE ROWS: When a ROLLBACK (or ROLLBACK to SAVEPOINT) operation is performed, all rows of the table are preserved.
Note that BLOB, CLOB, DBCLOB, LONG VARCHAR, LONG VARGRAPHIC, XML, reference, and structured types cannot be used as data type for the columns of declared global temporary tables.
To use DB2 GDTT you need to perform the following steps:
Ensure that a user temporary table space exists. If a user temporary table space does not
exist, issue a CREATE USER TEMPORARY TABLESPACE statement using the following syntax:
CREATE USER TEMPORARY TABLESPACE usr_tbsp MANAGED BY SYSTEM USING ('c:\temp\usertempspace') ;
- Step 2. Issue a DECLARE GLOBAL TEMPORARY TABLE statement in your application
using syntax provided above. Here is an example:
Listing 2. Example of DGTT declaration
DECLARE GLOBAL TEMPORARY TABLE temp_proj (projno CHAR(6), projname VARCHAR(24), projsdate DATE, projedate DATE,) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED IN usr_tbsp ;
The schema for a declared temporary tables is always SESSION.
Step 3. When you reference this table in your procedure, you need to qualify the temporary table with schema SESSION. The following example demonstrates the use of temporary table:
Listing 3. Use of a temporary table
CREATE PROCEDURE DB2ADMIN.temp_table ( ) P1: BEGIN DECLARE GLOBAL TEMPORARY TABLE temp1 AS ( SELECT deptnumb as dnum, deptname as name, manager as mgr FROM org ) DEFINITION ONLY ON COMMIT PRESERVE ROWS; BEGIN DECLARE c1 CURSOR WITH RETURN FOR SELECT dnum, mgr FROM SESSION.temp1; INSERT INTO SESSION.temp1 (dnum, name, mgr) (SELECT deptnumb, deptname, manager FROM org); OPEN c1; END; END
Expressing hierarchical relationships in a relational database
When working with hierarchical data in a relational database, it becomes a challenge to retrieve and display the data. A recursive SQL statement provides a means of working with these complex logical structures.
A recursive SQL statement is one where an SQL statement repeatedly used the resulting set to determine further results. This kind of SQL statement is built using a common table expression that makes references to itself (that is, it used its own definition). These type of queries “with (…) as tabname “ are also known as common table expressions (CTE).
An example of this type of data structure would be a bill of materials. The below table represents a hierarchical structure of a list of materials for building a computer. In this example, the computer has sub-assemblies that are made up of a hard drive, monitor, keyboard, mouse, and motherboard. The sub-assemblies themselves can be decomposed into sub-assembles or components, that is, a motherboard is made up of processors and RAM.
Table 1. Sample date from the bill_of_materials table
The following statement performs a recursive query. The WITH statement defines a temporary table called ASSEMBLY. The upper part of the UNION ALL is only invoked once. It populates the assembly table with five rows that have the assembly ID of 1000.
The lower part of the UNION ALL executes recursively until there are no more matches to the join. That is, the recursive query iterates through the bill_of_materials table row by row creating a final result set and also input to the next iteration of the recursive query.
The SELECT statement at the end simply returns rows from temporary ASSEMBLY table that was just created using CTE.
Listing 4. WITH statement
WITH assembly (sub_assembly_id, assembly_nm, assembly_id) AS (SELECT sub_assembly_id, assembly_nm, assembly_id FROM bill_of_materials WHERE assembly_id=1000 UNION ALL SELECT child.sub_assembly_id, child.assembly_nm, child.assembly_id FROM bill_of_materials child, assembly p WHERE child.assembly_id = p.sub_assembly_id) SELECT assembly_id, sub_assembly_id, assembly_nm from assembly;
The final result set returned from the WITH statement:
Listing 5. Final result sets returned from WITH statement
ASSEMBLY_ID SUB_ASSEMBLY_ID ASSEMBLY_NM 1000 1100 Hard Drive 1000 1200 Monitor 1000 1300 Keyboard 1000 1400 Mouse 1000 1500 Motherboard 1100 1110 Hard drive Cables 1300 1310 Keyboard Cables 1400 1410 Mouse Cables 1500 1510 Processors 1500 1550 RAM 10 record(s) selected.
Figure 1 gives you a graphical view of how these results would appear.
Figure 1. Example of the hierarchical query
Using the MERGE statement to combine conditional update, insert, or delete operations
The MERGE statement updates a target table or updatable view using data from a source table. During a single operation, rows in the target that match the source can be updated or deleted, and rows that do not exist in the target can be inserted.
For example, consider the EMPLOYEE table to be the target table that contains up-to-date information about the employees of a large company. Branch offices handle updates to local employee records by maintaining their own version of the EMPLOYEE table called MY_EMP. The MERGE statement can be used to update the EMPLOYEE table with information that is contained in a MY_EMP table, which is the source table for the merge operation.
The following statement inserts a row for new employee number 000015 into the MY_EMP table.
Listing 6. Inserting a row for a new employee
INSERT INTO my_emp (empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary) VALUES ('000015', 'MARIO', 'M', 'MALFA', 'A00', '6669', '05/05/2000', 'ANALYST', 15, 'M', '04/02/1973', 59000.00)
And the following statement inserts updated salary data for existing employee number 000010 into the MY_EMP table.
INSERT INTO my_emp (empno, firstnme, midinit, lastname, edlevel, salary) VALUES ('000010', 'CHRISTINE', 'I', 'HAAS', 18, 66600.00)
At this point, the inserted data exists only in the MY_EMP table because it has not yet been merged with the EMPLOYEE table. Listing 7 shows the MERGE statement that takes the contents of the MY_EMP table and integrates them with the EMPLOYEE table.
Listing 7. MERGE statement
MERGE INTO employee AS e USING (SELECT empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary FROM my_emp) AS m ON e.empno = m.empno WHEN MATCHED THEN UPDATE SET (salary) = (m.salary) WHEN NOT MATCHED THEN INSERT (empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary) VALUES (m.empno, m.firstnme, m.midinit, m.lastname, m.workdept, m.phoneno, m.hiredate, m.job, m.edlevel, m.sex, m.birthdate, m.salary)
Correlation names have been assigned to both the source and the target table to avoid ambiguous table references in the search condition. The statement identifies the columns in the MY_EMP table that are to be considered. The statement also specifies the actions that are to be taken when a row in the MY_EMP table is found to have a match in the EMPLOYEE table, or when a row does not have a match.
The following query executed against the EMPLOYEE table now returns a record for employee 000015:
SELECT * FROM employee WHERE empno = '000015'
And the following query returns the record for employee 000010 with an updated value for the SALARY column:
SELECT * FROM employee WHERE empno = '000010'
System procedure ADMIN_CMD
DB2 provides ADMIN_CMD procedure in SYSPROC schema to run administrative commands directly from application or from another procedure by using a CALL statement. This procedure can not be called from a UDF or trigger.
ADMIN_CMD allows one input parameter of type CLOB(2M) that can contain a single
administrative command to be executed. The syntax for that statement is:
The procedure currently supports numerous DB2 v9.5 administrative commands. Here are the most commonly used administrative commands:
- FORCE APPLICATION
- REORG INDEXES/TABLE
- UPDATE DATABASE CONFIGURATION
The full list of supported command can be found in the IBM DB2 9.5 Information Center .
The following example demonstrates the use of this procedure:
Listing 8. Sample ADMIN_CMD procedure
CREATE PROCEDURE test_admin_cmd ( ) P1: BEGIN DECLARE sql_string VARCHAR(200); SET sql_string ='LOAD FROM C:\DB9.5_test\org_exp.txt OF DEL METHOD P (1, 2, 3, 4, 5) INSERT INTO DB2ADMIN.ORG1 (DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION) COPY NO INDEXING MODE AUTOSELECT'; CALL SYSPROC.ADMIN_CMD(sql_string); END P1
GET DIAGNOSTIC statement
SQL PL provides a GET DIAGNOSTICS statement to obtain information about a previously executed SQL statement. For example, if you need to determine how many rows are affected as a result of an INSERT, DELETE or UPDATE statement, the GET DIAGNOSTICS statement with the ROW_COUNT option could be used to provide this information.
Listing 9 is the syntax diagram for GET DIAGNOSTICS statement:
Listing 9. Syntax for GET DIAGNOSTICS statement
The GET DIAGNOSTICS statement can be use to obtain:
- The number of rows processed by a previously executed SQL statement
- The status value returned from the procedure associated with a previous CALL statement
- The native DB2 error or warning message text returned from the previously executed SQL statement
SQL-variable declaration depends on the information you are trying to obtain. It needs to be declared as INTEGER to store ROW_COUNT or DB2_RETURN_STATUS, but it should be declared as varchar(70) to store and error and warning messages.
Please note that the GET DIAGNOSTICS statement does not change the contents of the SQLSTATE or SQLCODE special variable.
The following examples demonstrate the use of GET DIAGNOSTICS statement with different options:
Listing 10. Get DIAGNOSTICS statement to retrieve ROW_COUNT
CREATE PROCEDURE UPDATE_RCOUNT(sales_corr INT, qtr int, OUT row_updated INT) P1: BEGIN UPDATE SALESBYQUARTER SET sales = sales+sales_corr WHERE y < year(current date) and q = qtr; GET DIAGNOSTICS row_updated = ROW_COUNT; END P1
Listing 11. GET DIAGNOSTICS statement to retrieve message text
CREATE PROCEDURE mess_text1 (num int, new_status varchar(10), OUT p_err_mess varchar(70)) P1: BEGIN DECLARE SQLCODE INTEGER default 0; DECLARE SQLSTATE CHAR(5) default ''; DECLARE v_trunc int default 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS EXCEPTION 1 p_err_mess = MESSAGE_TEXT; SET v_trunc = 2; END; INSERT INTO tab1 VALUES (num, new_status); RETURN v_trunc; END P1
The DB2_RETURN_STATUS returns the status value of the invoked SQL procedure. If the called procedure is successful, it returns a value of zero or a positive value indicating a failure.
Listing 12. Example of GET DIAGNOSTICS statement to retrieve return status
CREATE PROCEDURE myproc1 () A1:BEGIN DECLARE RETVAL INTEGER DEFAULT 0; … CALL MYPROC2; GET DIAGNOSTICS RETVAL = DB2_RETURN_STATUS; IF RETVAL <> 0 THEN … LEAVE A1; ELSE … END IF; END A1
Savepoints provide a means of implementing subtransactions within a unit of work (transaction). This is accomplished by creating multiple reference points (savepoints) within a transaction that later can be referenced. For example, within an application, you can set multiple savepoints; at any point after the savepoint is created, you can roll back any work up to the savepoint without impacting any work performed prior to the savepoint.
The following syntax shows you how to set up savepoints:
SAVEPOINT savepoint-name ON ROLLBACK RETAIN CURSORS
Let’s take a look at an example of performing a rollback operation for nested savepoints. First you create a table named T1. You then insert a row into new table T1. After the insertion, you create the first savepoint(SAVEPOINT1). After creating the savepoint, you insert another row into table T1 and create another savepoint(SAVEPOINT2). You insert another row (third row) into table T1. Then you create your last savepoint(SAVEPOINTS3). Finally, insert another row after SAVEPOINT3. You now have four rows in table T1 (‘A’, ‘B’, ‘C’, ‘D’).
You then perform a rollback to savepoint3, which removes row ‘D’ from table T1. The next rollback that you perform (rollback to savepoint1) removes rows ‘C’ and ‘B’. You then commit the transaction (commit work), which causes the savepoints to no longer be available. You now have two rows (‘A’ and ‘E’) in table T1.
Listing 13 shows you the commands you use to implement these actions.
Listing 13. Using savepoints
CREATE PROCEDURE p1() BEGIN CREATE TABLE T1 (C1 CHAR); INSERT INTO T1 VALUES ('A'); SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('B'); SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('C'); SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('D'); ROLLBACK TO SAVEPOINT SAVEPOINT3; ROLLBACK TO SAVEPOINT SAVEPOINT1; COMMIT WORK; INSERT INTO T1 VALUES ('E'); END@
PureXML and stored procedures
DB2 9.5 is a hybrid database that supports XML data type for column definition as well as different methods and functions for XML data manipulation. SQL procedures support parameters and variables of data type XML. Those variables also can be passed as parameters to XQuery expressions in XMLEXISTS, XMLQUERY and XMLTABLE expressions.
The following stored procedure returns a result set of a relational column and attribute (customer name) from XML column:
Listing 14. Stored procedure example
CREATE PROCEDURE proc_wXML ( ) DYNAMIC RESULT SETS 1 ------------------------------------------------------------------------ -- SQL Stored Procedure ------------------------------------------------------------------------ P1: BEGIN -- Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT cid, xmlquery('declare default element namespace "http://posample.org"; $cu/customerinfo/name/text()' passing d.info as "cu") from customer d; -- Cursor left open for client application OPEN cursor1; END P1 The next procedure is accepting XML as input variable along with integer parameter and inset row into CUSTOMER table that has INFO column declared as XML: CREATE PROCEDURE PROC_INS_XML1 ( v_cid int, v_info XML) DYNAMIC RESULT SETS 1 ------------------------------------------------------------------------ -- SQL Stored Procedure ------------------------------------------------------------------------ P1: BEGIN INSERT INTO CUSTOMER (CID, INFO) VALUES (v_cid, v_info); END P1
If you run this procedure using IBM Data Studio, you can parse XML directly into input value windows as Figure 2 illustrates.
Figure 2. XML string as input parameter
In this tutorial, you've learned how to declare global temporary tables and use them in stored procedures. You've also learned how you can use savepoints and the GET DIAGNOSTICS statement to improve your database application. In addition, you've learned about the new DB2 hieratical query and MERGE functionality to help you implement complex business solutions. The ability to use XML technology in your database development can greatly simplify your complex business logic. Knowledge of those advanced features will help you to pass 735 Certification Exam.
- Read Part 1 of the series, "SQL Procedure Language," to learn about SQL Procedure Languag, including a variable, condition, and handler declaration, flow of control and iterative statements, as well as an error-handling mechanism.
- In Part 2 of the series, "DB2 SQL Procedures," learn about DB2 9.5 SQL procedures, including an introduction to stored procedures, the advantages of using stored procedures, and the differences between SQL procedures and external procedures.
- Part 3 of the series, "DB2 SQL functions," introduces you to user-defined functions and walks you through the basic steps used to construct user-defined functions. This tutorial also introduces you to the structure of SQL functions and covers the ins and outs of SQL function development.
- Visit the Test 735: DB2 9.5 SQL Procedure Developer page to get comprehensive resources related to the exam.
- The DB2 Family Fundamentals tutorial series gives you the resources you need to ace the DB2 Family Fundamentals test.
- Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
- The DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond IBM Redbook contains information about SQL Procedures.
- DB2 v9.5 Information Center: Learn more about DB2 SQL procedures.
Get products and technologies
- Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
- Download a free trial version of DB2 9 for Linux, UNIX, and Windows.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.