IDUG content committee DOJO challenge 2

April 2015 “Fun with SQL” Challenge 2

This month on the Content Committee blog we’ll be doing an online version of the “Fun with SQL” dojo. Each week we’ll present an SQL challenge and request the community to solve it. People can respond to the challenge in the comments section of the blog post, or they can email Kurt directly at kurt.struyf@lone-star.be with a solution or idea and he will post it to the blog. You can post a possible answer, ask a question, and comment on something someone else has posted, or offer alternatives to something that’s been posted. 

Fully using the power of SQL, becomes clear using SQL PL, so that is where this week's challenge will be. understanding how to write native stored procedures, isn't only a good skill to have, since the are ZIIP offloadable when called over DRDA, they pack a performance benefit as well.

Here is the second SQL challenging based upon the DB2 sample database:

create a SQL stored procedure that has the following features:

with one input parameter with a datatype of char(3)

Use the department number input parameter to do an existence check against the department table before processing the cursor against the employee table. If the department does not exist then fail the call to the stored procedure with SQLSTATE '38001'.

If the department does exist then continue with returning the employee cursor. The cursor to the stored procedure reads the employee table (columns empno, lastname, and salary) for a workdept value equal to the new input parameter.

return the cursor to the caller

Don’t be shy about posting your answer if someone else has already posted. At the end of the month, the content committee will take the names of the people that have participated and randomly select one to receive a reward of an IPad case, courtesy of IBM! So, don’t be shy, just dive in and share your experience or inexperience and get a free and fun education!

have fun !

Kurt Struyf

Content Committee

8 Comments

One way

April 14, 2015 10:09 AM by Lennart Jonsson

 

CREATE OR REPLACE PROCEDURE GET_EMPLOYEES(IN_DEPTNO CHAR(3))
LANGUAGE SQL
BEGIN
    DECLARE TMP CHAR(3);
    DECLARE C1 CURSOR WITH RETURN TO CALLER FOR
        select e.* from employee as e where WORKDEPT = in_deptno;

    DECLARE EXIT HANDLER FOR SQLSTATE VALUE '02000'
        SIGNAL SQLSTATE '38001'
            SET MESSAGE_TEXT = 'Unknown department';

    select deptno into TMP from department
    where deptno = in_deptno
    fetch first 1 rows only;

    OPEN C1;
end @

[lelle@dustbite src]$ db2 "call get_employees('A01')"
SQL0438N  Application raised error or warning with diagnostic text: "Unknown
department".  SQLSTATE=38001
[lelle@dustbite src]$ db2 "call get_employees('A00')"


  Result set 1
  --------------

  EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE            JOB      EDLEVEL SEX BIRTHDATE           SALARY      BONUS       COMM      
  ------ ------------ ------- --------------- -------- ------- ------------------- -------- ------- --- ------------------- ----------- ----------- -----------
  000010 CHRISTINE    I       HAAS            A00      3978    1995-01-01-00.00.00 PRES          18 F   1963-08-24-00.00.00   152750.00     1000.00     4220.00
  000110 VINCENZO     G       LUCCHESSI       A00      3490    1988-05-16-00.00.00 SALESREP      19 M   1959-11-05-00.00.00    66500.00      900.00     3720.00
  000120 SEAN                 O'CONNELL       A00      2167    1993-12-05-00.00.00 CLERK         14 M   1972-10-18-00.00.00    49250.00      600.00     2340.00
  200010 DIAN         J       HEMMINGER       A00      3978    1995-01-01-00.00.00 SALESREP      18 F   1973-08-14-00.00.00    46500.00     1000.00     4220.00
  200120 GREG         -       ORLANDO         A00      2167    2002-05-05-00.00.00 CLERK         14 M   1972-10-18-00.00.00    39250.00      600.00     2340.00

  5 record(s) selected.

 

Commenting on first solution

April 14, 2015 04:09 PM by Peter Vanroose

I like (and prefer) the EXIT HANDLER way of programming. Perfect.

Personally I wouldn't call the variable "TMP" but rather "d" or "dept" or "tmp_deptno" or so.

Also two syntactic issues, though:

  • shouldn't the input param "in_deptno" be preceded by the keyword "IN" ?
  • shouldn't the "DYNAMIC RESULT SETS 1" clause be added?

I'm fairly sure both are required for a similar procedure to compile on DB2 for z/OS.
(And the "OR REPLACE" is not supported by DB2 for z/OS -- must use ALTER PROCEDURE instead, when it exists.)
On the other hand, "LANGUAGE SQL" is not required since it's the default (since DB2 9).

And finally a less known syntactic possibility: replace the "select ... into ... fetch first ..." by:

   SET tmp = ( SELECT deptno FROM department WHERE deptno = in_deptno);

Since this is a scalar full-select, it's implicitly "fetch first row only", which saves on typing.
Moreover, the assignment is more apparent.
So I personally prefer this for its slightly better readability (hence maintainability).

--      Peter Vanroose
         ABIS Training & Consulting,
         Leuven, Belgium.

Commenting on first solution

April 14, 2015 04:44 PM by Kal Sub

Like it...just one possible flaw. If the Employee table does not have any rows for that departent, would the open cursor return a sqlstate of 02000 and invoke the exit handler ?

Regards

Correction - and a working version

April 14, 2015 05:08 PM by Peter Vanroose

Good ideas don't always work ;-)

Anyhow, the exit handler on "not found" (sqlstate 02000 a.k.a. sqlcode 100) was not triggered with my earlier suggestion of "var = (SELECT ...)". So back to Lennart's original idea, but with some other minor modifications. Works on DB2 for z/OS.

CREATE PROCEDURE employees_of(IN dept_p CHAR(3))
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE d_v INT;
  DECLARE emps CURSOR WITH RETURN FOR
    SELECT empno, lastname, salary FROM employee WHERE workdept=dept_p;
  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE VALUE '38001'
      SET MESSAGE_TEXT = 'Department '||dept_p||' does not exist';
  SELECT 1 INTO d_v FROM department WHERE deptno = dept_p;
  OPEN emps;
END

Note the presence of the parameter in the error message; and note the "SELECT 1" which has more of an existence check than a "SELECT deptno", imho.

--      Peter.

sqlstate 02000 on open cursor?

April 14, 2015 05:12 PM by Peter Vanroose

In reply to Kal Sub: "If the Employee table does not have any rows for that departent, would the open cursor return a sqlstate of 02000 and invoke the exit handler ?"

No, it doesn't: it's the first fetch which would do that, but that fetch occurs at the caller's, not inside the procedure, so the exit handler won't be "triggered" in that case.

(Just tried it to be sure.)

Comments

April 15, 2015 12:18 AM by Lennart Jonsson

Peter, I agree with most of your comments

  • IN is the default, but for clarity it is better to declare it that way
  • DYNAMIC RESULT SETS 1, this is a must, leaving this one out will cause problems for the caller under certain circomstances
  • FETCH FIRST 1 ..., not necessary since the p.k. guarantees that at most one row will qualify but one might argue that it makes the intention a bit clearer (in case you are not familiar with the department table, and too lazy to look it up)
  • SELECT 1 INTO is clearly better

Some final thought, in a real situation constructions that are not obvious at first sight deserves a comment so something like:

      -- Check for existence of department, none found triggers exit handler
    SELECT 1 INTO ...

would probably be a good call. For this scenario it may be redundant, but if the EXIT HANDLER is not declared in a close neighbourhood of the check, it clarifies the intention.

 

A completely different programming logic

April 15, 2015 04:26 AM by Peter Vanroose

Just for the sake of having a (completely) different implementation, that is, without exit handler.

But for the record, I still prefer the "exit handler" logic.

CREATE PROCEDURE employees_of(IN dept_p CHAR(3))
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE d_v INT;
  DECLARE employees CURSOR WITH RETURN FOR
    SELECT empno, lastname, salary FROM employee WHERE workdept=dept_p;
  SET d_v = (SELECT 1 FROM department WHERE deptno = dept_p);
  IF d_v IS NULL THEN
    SIGNAL SQLSTATE VALUE '38001'
         SET MESSAGE_TEXT = 'Department '||dept_p||' does not exist';
  ELSE
    OPEN employees;
  END IF;
END

--      Peter.

Implementation using a cursor data type...

April 15, 2015 08:14 PM by Robert Mala

Different implementations are informative especially when novel or new features used.   Here is one that uses the cursor data type introduced in db2 v9.7.

create procedure employees_of( in $dept char(3), out employees cursor )
begin
  if not exists ( select 1 from department where deptno = $dept ) then
    signal sqlstate value '38001' set message_text = 'department does not exist';
  else
    set employees = cursor for select empno, lastname, salary from employee where workdept = $dept order by lastname;
    open employees;
  end if;
end

Annoyingly in LUW, the message_text can only be set to a literal string, local variable or parameter!

Recent Stories
Selecting Appropriate Data Architecture Technologies

SQL Tuning for Developers – Part 2 Explain Output

Some of my favorite tunings tips and tricks for DB2 by Joe Geller