Converting Informix SP to DB2

[Thomas Bean]

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

[Jeremy Rickard]

tb***@cpicorp.com (Thomas Bean) wrote in message news:<81**************************@posting.google. com>...

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?

The problem is that you've defined INPUT2 as CHAR(20) instead of
VARCHAR(20). Therefore you are saying COLUMN4 LIKE '%
' instead of COLUMN4 LIKE '%'
Jeremy Rickard

[Thomas Bean]

Jeremy,
That was indeed the problem, thanks for pointing it out.

--Thomas

jr******@unisystems.biz (Jeremy Rickard) wrote in message news:<d3**************************@posting.google. com>...

tb***@cpicorp.com (Thomas Bean) wrote in message news:<81**************************@posting.google. com>...

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?

The problem is that you've defined INPUT2 as CHAR(20) instead of
VARCHAR(20). Therefore you are saying COLUMN4 LIKE '%
' instead of COLUMN4 LIKE '%'
Jeremy Rickard