IBM i > ADMINISTRATOR > DB2

SQL Stored Procedure Tutorial


What are the first things you think about with Business Intelligence dashboard reporting? Where to get the data? How to retrieve the data from the ERP or Business system database? How to format data to make it easily used and understood? These are all key questions.

Previously, I explored a few dashboard concepts and potential data presentation techniques and formats. In part 1 of this two-part series, I’ll explore using an SQL stored procedure for data retrieval to feed a DB2 Web Query for i dashboard document.

Background

If you haven’t used stored procedures much, know that they can be created in two ways: by wrapping a high-level language program with SQL or by writing strictly in SQL. The HLL language program, written with RPG, COBOL, C or Java might use embedded SQL or native I/O methods to access DB2 data. Alternatively, using SQL to entirely define your procedure, you employ the SQL procedures language (SPL) which adds logic control to the other SQL statements you are likely familiar with, providing a robust programming environment. Neither approach is better than the other. Your choice depends on your environment, skills and reporting requirements. When high-level languages are utilized, these procedures are called External procedures. Procedures can accept zero, one or more parameters as input, can return zero, one or more parameters as output and can also return zero, one or more Result Sets. It’s the result set capability that inspires this article, a powerful and useful tool for building reports and dashboards in Web Query.

Before choosing your venue, External or SQL, consider which languages you’re familiar with and whether you have important business logic already developed. If, time-tested RPG business logic already exists and provides the data you need, then you might want to make that logic available via SQL. Reusing your existing program logic might be the fastest path to realize your reporting objectives.

A Simple SQL Stored Procedure

In this article, I am going to illustrate using a simple SQL stored procedure. It utilizes neither input nor output parameters. When called from either Run SQL Scripts or DB2 Web Query for i, the procedure will execute the enclosed SQL statements and return a Result Set for each declared cursor. Think of cursors, in this situation, as a pointer to results from each of the executed statements. The Web Query designer will show each of the result sets, with the fields for each, so that you can choose them for either charts or reports. Note that result sets are used on a “per-report” basis; you can’t mix fields from two different result sets in a single report.

Suppose your dashboard idea calls for a presentation of revenue generated in three distinct ways: by salesperson, by plant and by store. In the old-school Query/400 way, this involves three different reports, right? But with Web Query, it’s possible to present this data as reports or charts or both, if needed, all on a single page. This is where the power of the Stored Procedure (SP) can come into play. Figure 1 shows part of the SQL procedure code that defines the procedure, to be named DASH2, which will return four result sets, and the needed SELECT statements.

If I’d used input/output parameters, I would have defined them after the name of the procedure. In this case, I have none to define, but I need to specify that I expect four results and, that I’m using SQL as the language. Any statements between the BEGIN and END keywords of the procedure represent the logic of the procedure. Figure1 shows code from the top of the SQL procedure.

Subsequent SQL logic that opens and returns the results, shown in Figure 2, represents the bottom of the procedure. The combined code in these two figures, contains all the SQL that is needed for this application, to create the procedure and return data. This procedure code is a vehicle for giving Web Query access to the results. How you might implement your own reporting scenario is up to you and will depend upon your application, site objectives and standards. Your SQL select statements could be more complex, or could reference SQL Views, themselves having greater complexity.

Rick Flagler is an information technology consultant, teacher and mentor.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.


comments powered by Disqus

Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters