DB2 functions allow you to extend the functionality of database. Functions can be either database provided like SUM, AVG, COUNT, VALUE, etc whereas the other type is User Defined Function (UDF).

User defined function allows you to define your own features which normally cannot be provided by inbuild database functions.

Example - EXISTIN

This is the function that helps to check occurrence of string in CSV of string. Here’s the source -

-- Returns 0 if match found, else returns 1
CREATE FUNCTION schema.EXISTSIN
(VALUELST VARCHAR(1000), -- CSV String
VALUE VARCHAR(255), -- match to be found
DEL CHAR(1)) -- delimeter used in CSV string

RETURNS INTEGER
LANGUAGE SQL
BEGIN ATOMIC
DECLARE V_INDEX INTEGER ;
DECLARE V_RET INTEGER;
DECLARE V_TEMP VARCHAR(255);
DECLARE V_TEMP_LIST VARCHAR(1000);

SET V_TEMP_LIST = SUBSTR( VALUELST, 1 );
WHILE( LENGTH( V_TEMP_LIST ) > 0 )
DO
SET V_INDEX = POSSTR(V_TEMP_LIST , ‘,’);
SET V_TEMP = SUBSTR(V_TEMP_LIST , 1, V_INDEX-1);
IF( V_TEMP = VALUE ) THEN
RETURN 0;
END IF;
SET V_TEMP_LIST = SUBSTR( V_TEMP_LIST, V_INDEX + 1 );
END WHILE;

RETURN 1;
END

NOTE: though we did create this function, but later we realized there is function available in DB2 UDB 8.2 that does the same job. But may be, if other database don’t provide you with a function to do such job, then you can always write special user defined function to implement this.

SELECT * FROM MYTABLE WHERE LOCATE( COL1, 'ROHITBLOG,ROHITREVIEW' );

If there the column COL1 contains values ‘ROHITBLOG’, then it’ll return position of the match i.e. here it’s 1. The drawback here is if the value is ‘ROHIT’, then also it’ll return match and return non-zero value. So it would be safe to use existin function which breaks the CSV string and does exact match.

Well this article is for technocrats, specially those writing Stored procedures. Sometimes, for some reasons, you (as a developer) may want to put some data in a temporary table and then later retrieve this for processing. I recently faced such need, wherein I had take things one by one and then find details on each individual item, and then return the whole information in cursor.

Oracle offers you VARRAY - variable array where you can store data in each index starting 0… But DB2 on UDB doesn’t had any such thing.
Well there’s way to create temporary table for the current session. Here’s the code -

Step1. Declare the table, notice the prefix “SESSION” to differentiate it with normal tables.

DECLARE GLOBAL TEMPORARY TABLE
SESSION.TEMP_REP2(COL1 VARCHAR(255), COL2 INTEGER, COL3 INTEGER, COL4 INTEGER) not logged;

Step2. Insert the data in this temporary table -

INSERT INTO SESSION.TEMP_REP2 VALUES ('RohitBlog', 10, 10, 10);

Step3. Read data from the temporary table -

SELECT * FROM SESSION.TEMP_REP2

Note - for being able to declare global temporary table, your userid on database needs to have some special permissions on the temporary Userspace i.e. USER access. Ask your DBA to grant you the necessary permissions.

When you connect / execute Stored procedure through your J2EE web app or for any other matter any application type, its extremely important to take care of any SQL Exceptions arising out of Stored procedure failure. Otherwise, you can wonder why some weird things happen with your system.

Locking tables to prevent concurrent access is another issue, but let’s keep it for separate article. 

I faced an issue wherein if the insert /update statements in your stored procedure fails throwing SQL exceptions, then it may inadvertently keep the locks un-closed. When any insert / update statement is executed, the database puts lock on the table under operation. This is to prevent dirty data being read by other Select statements (though they still can read using the ‘with ur’ clause in the select).

1. Now let’s say insert / update fails may be due to not null or primary key constraints, then it raises SQL exception.
2. Ok now if you have not rolled back at connection level or within Stored procedure, then the locks may remain un-closed resulting in the table getting locked.  
3. The table may remain locked for timeout specified by lock-timeout attribute in database setting.

To avoid such lock issues, always rollback, either in Stored proedure or in your front end code program.

Â