DB2 Stored Procedure Hell

I'm a systems integrator and have no desire to understand everything at the lowest level of detail possible. I understand things conceptually and know where to find manuals to help me complete tasks but I have not got the time to understand every technology going.

Today, I had my first need to create a DB2 Stored Procedure and use it. In 20 years, I've never had to do that - which seems strange even to me. I've always known about them and understood their power but it has always been "someone else" who has created them and consumed them in their applications. Remember... I integrate systems?

So - how hard can it be? Well, my good friend Google helped when I asked for information on "creating a stored procedure". A tonne of links - must of which assumed that I would be using some heavyweight IDE. I'm a command-line kind of guy though!

It was at that point that I thought that even though this is a simple task and I'm fairly convinced I'll be able to do this within minutes rather than hours, I still figured I should maybe record the process I went through. The reason? I'm fed up being sent down blind alleys by trash on the internet.

I create a myfirstproc.sql file with the following contents:

CREATE PROCEDURE MYFIRSTPROC
(IN username CHAR99), IN disclaimer INT)
LANGUAGE SQL
BEGIN
IF disclaimer = 1 THEN
INSERT INTO USER (USERID, DISCLAIMER) VALUES (username, 'Y');
ELSE
INSERT INTO USER (USERID, DISCLAIMER) VALUES (username, 'N');
END IF;
END @

Straightforward, eh?

I applied it (eventually) using the db2 -td@ -vf myfirstproc.sql command.

Next, I wanted to use IBM Tivoli Directory Integrator to invoke the stored procedure. I created a passive JDBC connector called manageDB and created a script as such:

var con - manageDB.connector.connection;
command = "{call MYFIRSTPROC(?,?)}";
try {
cstmt = con.prepareCall(command);
cstmt.setString(1, "H12345678");
cstmt.setString(2, 1);
cstmt.execute();
cstmt.close();
}
catch (e) {
task.logmsg(e);
}

Now, that all looks quite neat but there is a bug in the code. When I ran the code I received a "Method Not Yet Supported" message. Now, what can you imagine would cause such a message? My first thought was that maybe I was using an out-of-date driver so I decided to get the latest one. This in itself is a major undertaking as anyone who has tried to find anything on the IBM website will testify! Certainly searching for db2jcc.jar (as I did) did not take me to anywhere from which I could download it!

The new JAR file was put in place and the routine executed again. "Method Not Yet Supported" again. Now - that's quite frustrating! Searching for "Method Not Yet Supported" yields information on how the method I'm calling isn't supported. In other words, a useless message!

Trawling through the code, it wasn't immediately obvious what the issue could've been. Not obvious because the code looked syntactically correct (and in any case, if there was a coding error, surely I would've been presented with an appropriate error message).

Well, the eagle eyed amongst you will notice that the original code above was attempting to set parameter 2 to a value of 1 as a string. Converting this to a setInt statement rectified the problem! The resulting code:

var con - manageDB.connector.connection;
command = "{call MYFIRSTPROC(?,?)}";
try {
cstmt = con.prepareCall(command);
cstmt.setString(1, "H12345678");
cstmt.setInt(2, 1);
cstmt.execute();
cstmt.close();
}
catch (e) {
task.logmsg(e);
}

So, why share this with you? Well... I'm not I guess. I've written this more as a reminder to myself. The important following lessons have been learned:

• Don't trust the information on the internet - it's typically out-of-date (just as this article will be as soon as I hit the publish button?)
• Don't trust error messages - they've been constructed by developers and could be meaningless
• Good luck with searching that IBM website
• Everything is possible with a smidgin' of perserverance