CALL SQL Stored Procedure: SQL0444N Reason code: 4

Rob Wright

CALL SQL Stored Procedure: SQL0444N Reason code: 4
Hi

I have DB2 UDB V8.2, FIXPAK 10, running on a Win2K machine.
I have created a SQL stored procedure. When I try and invoke it, I get the
error

42724(-444)[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*CATEGORY"
(specific name "SQL051222152349180") is implemented with code in library or
path "...E.SEARCHCATEGORY", function "LIVE.SEARCHCATEGORY" which cannot be
accessed. Reason code: "4". SQLSTATE=42724
(0.01 secs)

The interesting thing is that each time I run the call, the specific name
changes.

Have you managed to get SQL procedures to work in DB2 8.2? ie - they no
longer require a compile of a generated source application? Have I missed
something that needs to be run/setup for SQL procedures to run without
requiring a separate compilation?

Here is the create statement

>---- START ------ SQL Create Procedure ---- START ------>

CREATE PROCEDURE LIVE.SEARCHCATEGORY(
IN DEBTOR_CODE CHAR(10),
IN UNSPSC CHAR(8),
IN KEYWORDS VARCHAR(1000),
OUT PRODUCT_CODE CHAR(10),
OUT PRODUCT_DESCRIPTION VARCHAR(100))

DYNAMIC RESULT SETS 100
READS SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL
NO EXTERNAL ACTION

BEGIN
DECLARE SELECT_STMT VARCHAR(4000);
DECLARE SEARCH_CLAUSE VARCHAR(2000);
DECLARE SEARCH_C CURSOR WITH RETURN TO CALLER FOR SEARCH;
SET SEARCH_CLAUSE = LIVE.GET_SEARCH_CLAUSE(KEYWORDS);
SET SELECT_STMT = 'Select product_code, live.delete_last_word
(product_description) PRODUCT_DESCRIPTION from LIVE.BRANCH_PRODUCT WHERE
product_code in (Select PRODUCT_CODE from LIVE.WEB_INFO where unspsc
like ?) AND BRANCH_CODE = (SELECT DISTINCT BRANCH_CODE FROM LIVE.DEBTOR
WHERE DEBTOR_CODE = ?)' || SEARCH_CLAUSE;
PREPARE SEARCH FROM SELECT_STMT;
OPEN SEARCH_C USING UNSPSC, DEBTOR_CODE;
END

<---- END ------ SQL Create Procedure ---- END ------<


and the call statement

CALL LIVE.SEARCHCATEGORY('1579101', '10121801%', 'HILLS')

Any help would be appreciated.

Thanks
Rob

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Shyam Peri

Re: CALL SQL Stored Procedure: SQL0444N Reason code: 4
(in response to Rob Wright)
Rob,
As part of the logic of your procedure you are placing an explicit call to
the procedure LIVE.GET_SEARCH_CLAUSE.
There could be a possibility that db2 is not finding the shared lib for this
procedure?



SET SEARCH_CLAUSE = LIVE.GET_SEARCH_CLAUSE(KEYWORDS);

On 12/22/05, Rob Wright <[login to unmask email]> wrote:
>
> Hi
>
> I have DB2 UDB V8.2, FIXPAK 10, running on a Win2K machine.
> I have created a SQL stored procedure. When I try and invoke it, I get the
> error
>
> 42724(-444)[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*CATEGORY"
> (specific name "SQL051222152349180") is implemented with code in library
> or
> path "...E.SEARCHCATEGORY", function "LIVE.SEARCHCATEGORY" which cannot be
> accessed. Reason code: "4". SQLSTATE=42724
> (0.01 secs)
>
> The interesting thing is that each time I run the call, the specific name
> changes.
>
> Have you managed to get SQL procedures to work in DB2 8.2? ie - they no
> longer require a compile of a generated source application? Have I missed
> something that needs to be run/setup for SQL procedures to run without
> requiring a separate compilation?
>
> Here is the create statement
>
> >---- START ------ SQL Create Procedure ---- START ------>
>
> CREATE PROCEDURE LIVE.SEARCHCATEGORY(
> IN DEBTOR_CODE CHAR(10),
> IN UNSPSC CHAR(8),
> IN KEYWORDS VARCHAR(1000),
> OUT PRODUCT_CODE CHAR(10),
> OUT PRODUCT_DESCRIPTION VARCHAR(100))
>
> DYNAMIC RESULT SETS 100
> READS SQL DATA
> NOT DETERMINISTIC
> LANGUAGE SQL
> NO EXTERNAL ACTION
>
> BEGIN
> DECLARE SELECT_STMT VARCHAR(4000);
> DECLARE SEARCH_CLAUSE VARCHAR(2000);
> DECLARE SEARCH_C CURSOR WITH RETURN TO CALLER FOR SEARCH;
> SET SEARCH_CLAUSE = LIVE.GET_SEARCH_CLAUSE(KEYWORDS);
> SET SELECT_STMT = 'Select product_code, live.delete_last_word
> (product_description) PRODUCT_DESCRIPTION from LIVE.BRANCH_PRODUCT WHERE
> product_code in (Select PRODUCT_CODE from LIVE.WEB_INFO where unspsc
> like ?) AND BRANCH_CODE = (SELECT DISTINCT BRANCH_CODE FROM LIVE.DEBTOR
> WHERE DEBTOR_CODE = ?)' || SEARCH_CLAUSE;
> PREPARE SEARCH FROM SELECT_STMT;
> OPEN SEARCH_C USING UNSPSC, DEBTOR_CODE;
> END
>
> <---- END ------ SQL Create Procedure ---- END ------<
>
>
> and the call statement
>
> CALL LIVE.SEARCHCATEGORY('1579101', '10121801%', 'HILLS')
>
> Any help would be appreciated.
>
> Thanks
> Rob
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>



--
warm regards
Peri

The best way out of difficulty is through it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm