This article provides guidance for using anonymous blocks in DB2 9.7 in the following scenarios:
- Testing, troubleshooting, and developing new PL/SQL stored procedures
- Simulating application runs with PL/SQL
- Building complex ad-hoc queries and reports on the fly with PL/SQL
Reviewing the prerequisites and system requirements
This article is written for PL/SQL applications developers and database administrators who are moving from Oracle to DB2. You should understand the concept of PL/SQL procedural language. SQL PL developers should use the corresponding function provided by the DB2 native compound SQL statements.
To use the examples in the article, you must have installed DB2 9.7 Workgroup or Enterprise Edition for Linux, UNIX, and Windows. Refer to the Resources section to download a free trial version of DB2 9.7 for Linux, UNIX, and Windows.
Using the examples
You can run the examples using various tools, including the DB2 command line processor (CLP) and the command utilities (CLPPLUS), or the visual tools such as Optim Development Studio. If you plan to run the examples from CLP, you need to run the SET SQLCOMPAT PLSQL command to enable recognition of the forward slash character (/) on a new line as a PL/SQL statement termination character.
To enable support in DB2 for PL/SQL and Oracle data types, your database must be created with the DB2_COMPATIBILITY_VECTOR registry variable set to ORA, as shown in Listing 1.
Listing 1. Setting the DB2_COMPATIBILITY_VECTOR registry variable
db2set DB2_COMPATIBILITY_VECTOR=ORA db2stop db2start db2 create db test
For this article, Listing 2 offers the code to create a simple e-commerce PL/SQL application that manages online orders after you create a database. You will also populate the tables with some sample data.
Listing 2. The example code
Understanding anonymous blocks
Anonymous blocks are PL/SQL structures that provide the capability to create and execute procedural code on the fly without persistently storing the code as database objects in the system catalogs. The concept of anonymous blocks is similar to UNIX shell scripts, which enable several manually entered commands to be grouped and executed as one step. As the name implies, anonymous blocks do not have a name, and for this reason they cannot be referenced from other objects. Although built dynamically, anonymous blocks can be easily stored as scripts in the operating system files for repetitive execution.
Anonymous blocks are standard PL/SQL blocks. They carry the syntax and obey the rules that apply to all PL/SQL blocks, including declaration and scope of variables, execution, exception handling, and SQL and PL/SQL usage.
The compilation and execution of anonymous blocks are combined in one step, while a PL/SQL stored procedure needs to be re-defined before use each time its definition changes. This is one of the significant advantages of the anonymous blocks over the persistently named database objects, such as stored procedures and user-defined functions, because it reduces the time between implementing changes in the code and the actual execution. That makes the anonymous blocks very useful when troubleshooting, prototyping, and testing procedural code, because these are tasks that usually require multiple change-and-execute runs.
Another benefit of the anonymous blocks is that they do not create any dependencies, and they do not require any special privileges for object creation, which can avoid complications in a production environment. The anonymous blocks provide the flexibility to run any procedural sequence of actions based on simple, select privileges, and they enable you to test without creating or implicating existing database objects.
You can execute anonymous blocks from:
- SQL (for example, inside EXECUTE IMMEDIATE statements)
- DB2 APIs such as JDBC and ODBC
- Various DB2 tools, including CLP, CLPPlus, Optim Database Administrator, and Optim Development Studio
Prototyping PL/SQL code with anonymous blocks
In Listing 3, an application developer anticipates the business requirement for a mechanism to communicate (by e-mail) with the customers defined in the CUSTOMER table. To proactively satisfy this requirement, he decides to write a simple prototype PL/SQL anonymous block that sends email containing some message to the customers in the CUSTOMER table. Later, once the business requirement is finalized, the prototyped anonymous block can be enhanced and easily transformed into a new PL/SQL stored procedure. Note that this anonymous block uses new built-in packages, including UTL_SMTP (the package for sending emails) and DBMS_OUTPUT (the package for writing messages to the standard output), which are part of DB2 9.7.
Listing 3. A simple prototype PL/SQL anonymous block that sends email containing some message to the customers in the CUSTOMER table
SET SERVEROUTPUT ON / DECLARE conn UTL_SMTP.connection; reply UTL_SMTP.reply; msg VARCHAR2(1024); sender VARCHAR2(255) DEFAULT 'demo\@ca.ibm.com'; recipients VARCHAR2(255); subject VARCHAR2(255) DEFAULT 'Quick notification'; crlf VARCHAR2(2); BEGIN crlf := UTL_TCP.CRLF; FOR row IN (SELECT first_name, email FROM customer) LOOP DBMS_OUTPUT.PUT_LINE('Sending test email to customer ' || row.first_name || '...'); recipients := row.email; msg := 'FROM: ' || sender || crlf || 'TO: ' || recipients || crlf || 'SUBJECT: ' || subject || crlf || crlf || 'Hi ' || row.first_name || ', this is a test notification.'; UTL_SMTP.OPEN_CONNECTION('smtp_server.ibm.com', 25, conn, 10, reply ); UTL_SMTP.HELO(conn, 'localhost'); UTL_SMTP.MAIL(conn, sender); UTL_SMTP.RCPT(conn, recipients); UTL_SMTP.DATA(conn, msg); UTL_SMTP.QUIT(conn); END LOOP; END; / Output: Sending test email to customer Mike... Sending test email to customer Joan... Sending test email to customer Colin... Sending test email to customer Graham... Sending test email to customer Patsy...
Simulating application runs with anonymous blocks
As mentioned, one of the most common uses of anonymous blocks is to invoke procedural language objects, typically for testing purposes. Listing 4 demonstrates how to simulate an application run with the help of a PL/SQL anonymous block. The code simulates an application run while capturing performance metrics. The anonymous block simulates the creation of 10 random orders for random customers from the existing CUSTOMER table. It also prints test start and end times, along with the order details for each run. It is easy to change the number of orders from 10 to 20 and then re-run this anonymous block without recompiling. You can also add more performance metrics for additional testing.
Listing 4. An application running with the help of a PL/SQL anonymous block
SET SERVEROUTPUT ON / DECLARE v_customer_id customer.customer_id%TYPE; product_id product.product_id%TYPE:=1; o_order_id orders.order_id%TYPE; v_test_start TIMESTAMP; BEGIN SELECT CURRENT TIMESTAMP INTO v_test_start FROM dual; FOR k IN 1..10 LOOP SELECT customer_id INTO v_customer_id FROM customer ORDER BY RAND() FETCH FIRST 1 ROW ONLY; FOR i IN ( SELECT product_id, CAST(RAND()*50 as integer)+1 as quantity FROM product WHERE ROWNUM < CAST(RAND()*10 as integer)) LOOP add_item_to_shopping_cart(i.product_id, i.quantity); END LOOP; create_order(v_customer_id, o_order_id); DBMS_OUTPUT.PUT_LINE('--------------------------------------------'); END LOOP; DBMS_OUTPUT.PUT_LINE('Test start: ' || v_test_start); DBMS_OUTPUT.PUT_LINE('Test end : ' || CURRENT TIMESTAMP); END; / Output: Customer : Mike, Smith Order creation : 07-06-2009 Estimated Delivery : 07-09-2009 Status : Shipped Total price : $ 150,615.44 -------------------------------------------- Customer : Joan, Jett Order creation : 07-06-2009 Estimated Delivery : 07-09-2009 Status : Shipped Total price : $ 159,445.77 ... ... ... Customer : Colin, Taylor Order creation : 07-06-2009 Estimated Delivery : 07-09-2009 Status : Shipped Total price : $ 266,242.78 -------------------------------------------- Test start: 2009-07-06-22.214.171.1240000 Test end : 2009-07-06-126.96.36.1996000
Generating ad-hoc reports with anonymous blocks
A common reporting requirement is to concatenate data from more than one column into a single string. It is possible to write pure SQL statements with complex recursion to do this. However, you can use anonymous blocks instead to do this quickly with dynamic formatting options and simple logic flow.
Listing 5 shows how to create an ad-hoc report with the help of anonymous blocks. The code retrieves the list of all customers that ordered products from the store and the total value of all orders during last month. The names are displayed in one line and are separated by commas.
Listing 5. Creating an ad-hoc report with the help of anonymous blocks
SET SERVEROUTPUT ON / DECLARE v_customer_names VARCHAR2(4000); v_total_sales NUMBER(19,2); BEGIN DBMS_OUTPUT.PUT_LINE(' Last Month Sales Report '); DBMS_OUTPUT.PUT_LINE('---------------------------------------'); DBMS_OUTPUT.PUT('Customer List: '); FOR row IN (SELECT distinct(a.customer_id),first_name, last_name FROM customer a, orders b WHERE a.customer_id=b.order_id AND b.creation_time>CURRENT DATE -1 month) LOOP v_customer_names := v_customer_names || '"' || row.first_name || ' ' || row.last_name || '", '; END LOOP; IF(LENGTH(v_customer_names) > 0) THEN v_customer_names := SUBSTR(v_customer_names,1, LENGTH(v_customer_names)-2); ELSE v_customer_names := 'None'; END IF; DBMS_OUTPUT.PUT_LINE(v_customer_names); SELECT NVL(SUM(total_price),0) INTO v_total_sales FROM orders WHERE creation_time> CURRENT DATE - 1 month; DBMS_OUTPUT.PUT_LINE('---------------------------------------'); DBMS_OUTPUT.PUT_LINE('Total Sales: ' || TO_CHAR(v_total_sales, '$99,999,999.99')); END; / Output: Last Month Sales Report --------------------------------------- Customer List: "Mike Smith", "Joan Jett", "Colin Taylor", "Graham Norton", "Patsy Stone" --------------------------------------- Total Sales: $ 49,772.56
This article presented the following:
- The PL/SQL anonymous blocks feature introduced in DB2 9.7.
- The concept of anonymous blocks.
- How anonymous blocks can facilitate the process of testing, prototyping, and troubleshooting of procedural code.
- How anonymous blocks can simulate application runs.
- How to use anonymous blocks for powerful ad-hoc reporting.
With the support for PL/SQL anonymous blocks, you can quickly enable PL/SQL solutions in the DB2 environment by using existing PL/SQL scripts or by using individual PL/SQL and SQL statements that work with other database management systems.
- Use an RSS feed to request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
- Learn about the out-of-the-box support for Oracle SQL and PL/SQL dialects in DB2 9.7 from "DB2 9.7: Run Oracle applications on DB2 9.7 for Linux, UNIX, and Windows" (developerWorks, Jul 2009).
- Learn more about Compound SQL from the DB2 Information Center.
- Learn more about Database management and application development tools from the DB2 Information Center.
- Learn more about Anonymous block syntax from the DB2 Information Center.
- Learn more about System Defined Modules from the DB2 Information Center.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Download DB2 Express-C 9.7, a no-charge version of DB2 Express database server for the community that includes pureXML.
- Download a free trial version of DB2 9.7 for Linux, UNIX, and Windows..
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.