Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CALL SYSPROC.ADMIN_CMD problem

Status
Not open for further replies.

jpotucek

Technical User
Jan 26, 2005
144
US
DB2 on Windows V9.1

the stored procedure (below) was created specifically to
CALL SYSPROC.ADMIN_CMD ('IMPORT FROM NUL OF DEL REPLACE INTO MYTABLE')

but we are getting this error:
DB2 Database Error: ERROR [] [IBM][DB2/NT] SQL3015N An SQL error "-444" occurred during processing.
I cannot find SQL3015N (code 444) - does anyone know how we can pass 'IMPORT FROM NUL OF DEL REPLACE INTO MYTABLE in a stored procedure?

CREATE PROCEDURE KCDWHUAT.truncate_table(IN sch_name VARCHAR(130),IN tab_name VARCHAR(130))
LANGUAGE SQL
BEGIN

DECLARE stmt VARCHAR(1000);
DECLARE param VARCHAR(1000);
DECLARE full_name VARCHAR(1000);
DECLARE a VARCHAR(130);

IF sch_name IS NULL
THEN
SET full_name = tab_name;

SELECT tabname INTO a FROM SYSCAT.TABLES WHERE tabname = UCASE(tab_name);

ELSE
SET full_name = sch_name||'.'||tab_name;

SELECT tabname INTO a FROM SYSCAT.TABLES WHERE tabname = UCASE(tab_name) AND tabschema = UCASE(sch_name);

END IF;

IF UCASE(a) = UCASE(tab_name)
THEN

SET param = 'IMPORT FROM NUL OF DEL REPLACE INTO '||full_name;

SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)';

PREPARE s1 FROM stmt;
EXECUTE s1 USING param;

ELSE

END IF;

END

 
It can't find the stored proc. From the manual:

444
USER PROGRAM name COULD NOT BE FOUND

Explanation
DB2® received an SQL CALL statement for a stored procedure or an SQL statement containing an invocation of a user-defined function, and found the row in the SYSIBM.SYSROUTINES catalog table associated with the requested procedure name. However, the MVS™ load module identified in the EXTERNAL_NAME column of the SYSIBM.SYSROUTINES row could not be found.
name
The name of the MVS load module that could not be found
System action
The statement cannot be executed.

Programmer response
If the EXTERNAL_NAME column value in the SYSIBM.SYSROUTINES table is incorrect, use the ALTER FUNCTION or ALTER PROCEDURE statement to correct the value.

If the EXTERNAL_NAME column value is correct, use the MVS linkage editor to create the required MVS load module in one of the MVS load libraries used by your installation for stored procedures.

This error can also occur if you are invoking a WLM-managed stored procedure that is not APF authorized, and the DB2 load libraries are not in the STEPLIB concatenation because they are being loaded from LINKLIST. In this case, if you want the stored procedure program to run APF-authorized, link-edit it with AC=1 into an MVS APF authorized library. If you do not want the stored procedure program to run APF authorized, add the DB2 load library to the STEPLIB concatenation of the JCL used to start the WLM-managed address space.

SQLSTATE
42724

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top