Hello,
I'm fairly new to DB2. I am having some difficulty converting some
stored procedres from Informix that use default input parameters.
Specifically, the Informix procedures I need to convert use the '*'
character as a default input parameter to be used with the 'MATCHES'
condition if the input value is null. For example:

CREATE PROCEDURE "informix".stored_proc(input1 DECIMAL(18,8), input2
CHAR(20) DEFAULT '*')

SELECT table1.column1,table1.column2,table1.column3
FROM table1
WHERE table1.column4 = input1 AND
table1.column5 MATCHES input2;

END PROCEDURE;

If the input2 parameter is passed, a single record will be matched
with column5; otherwise, the query uses the syntax <MATCHES '*'> to
return all entries where column5 is not null.

Since DB2 does not allow default values to be declared for input
parameters and does not use the MATCHES condition, I figured I could
use the LIKE condition instead and use logic to default the INPUT2
value to '%' if it is NULL. For example:

CREATE PROCEDURE "DB2".STORED_PROC( IN INPUT1 (DEC(18,8), IN INPUT2
CHAR (20) )
SPECIFIC STORED_PROC
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
SET INPUT2 = COALESCE(INPUT2, '%');
BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3
FROM TABLE1
WHERE TABLE1.COLUMN4 = INPUT1 AND
TABLE1.COLUMN5 LIKE INPUT2;
OPEN CURSOR1;
END;
END P1

However, when INPUT2 is NULL the procedure does not return any rows to
the result set. I tested running the query manually using the syntax
<LIKE '%'>, and it *does* return the same results as <MATCHES '*'>.
Any idea why it doesn't work in the stored procedure? Is there any
more efficient way to go about this?
I also tried using a dynamically constructed SQL with no luck.

Thomas Bean