DB2 9.7: Using PL/SQL anonymous blocks in DB2 9.7

Learn how to use PL/SQL anonymous blocks in a DB2 environment

IBM DB2® for Linux®, UNIX®, and Windows® 9.7 introduces support for PL/SQL anonymous blocks: a feature that enables PL/SQL application developers to test, troubleshoot, and prototype new procedural code, simulate application runs, and dynamically build complex ad-hoc queries and reports. This article describes the concept of anonymous blocks in DB2 9.7 and illustrates the use of this feature using common database scenarios.

Maksym Petrenko, DB2 Open Database Technologies, IBM  

Maksym Petrenko photoMaksym Petrenko is part of DB2 Beta Enablement Team in IBM's Toronto Lab. He assists early adopters with moving their applications to the latest and greatest DB2 codebase. Maksym has worked with DB2 since 2001 as a developer, technical support analyst and lab services consultant. His experience includes supporting clients with installation, configuration, application development, and performance issues related to DB2 Databases on Windows, Linux, and UNIX platforms. Maksym is a certified DB2 Advanced Database Administrator and DB2 Application Developer.



Maria Schwenger, DB2 Open Database Technologies, IBM

Photo of Maria SchwengerMaria Schwenger joined IBM in 2005 as part of the Entity Analytic Solutions team, bringing more then 10 years of experience in performance engineering, database architecture, administration, and database development on Oracle and MS SQL server, as well as extensive experience in migration from legacy to relational databases. Currently, Maria works in a high-touch model with early release participants to promote DB2 Open Database Technology’s early adoption.



27 August 2009

Also available in Vietnamese Spanish

Introduction

Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See DB2 fundamentals for Oracle professionals: Introduction to DB2

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-11.10.11.500000
Test end  : 2009-07-06-11.10.11.546000

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

Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=423099
ArticleTitle=DB2 9.7: Using PL/SQL anonymous blocks in DB2 9.7
publish-date=08272009