Hi All,
i am trying to create a stored porcedure for truncating a table which i got from IBM website .stored procedure gets created but doesnt get executed here is the syntax of the stored procedure

Database - DB2 UDB ENTERPISE SERVER EDITION 8.2.3
O/S - IBM AIX 5.3
CREATE PROCEDURE truncate(IN sch_name VARCHAR(130),IN tab_name VARCHAR(130))
LANGUAGE SQL
---------------------------------
-- SQL Stored Procedure truncate
---------------------------------
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;

-- Check whether the table exists or not
SELECT tabname INTO a
FROM SYSCAT.TABLES
WHERE tabname = UCASE(tab_name);

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

-- Check whether the table exists or not
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
-- Uncomment one of the following statements depending on the
-- platform on which the sample is run.
SET param = 'IMPORT FROM /dev/null OF DEL REPLACE INTO '||full_name;

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

PREPARE s1 FROM stmt;
EXECUTE s1 USING param;

ELSE

-- Table does not exists.

END IF;

END @


when i run this procedure it gives me following error using
Call dsedw.truncate(NULL,'X') where x is table name
SQL0104N-an expected token import was found following "begin of statement"
expected token may include describe .

if i gave schema name instead of using null then also the error remains the same .

appreciate your response.

Thanks & Regards,

You are aware that the UCASE screws things up if you have delimited schema/table names? Therefore, I wouldn't do that in such cases.

Same here. Also, I would simplify the logic and use the CURRENT SCHEMA special register instead - or combine both table-existence checks into a single query dealing with the case where "sch_name" is NULL:
With this, you only have to consider the branching for the SET statement - and even that could simply be handled with a CASE expression.

Yet another approach (much simpler, too!) would be to just ignore the existence check completely. Just construct the IMPORT command, inject the schema/table name (including NULL handling for schema names), and then execute the command. IMPORT itself will check the existence of the table anyway and return an error if the table cannot be found. The reason for deferring to IMPORT is also that there are other checks required like verifying that the caller actually has all the necessary privileges for the import operation.

You are using the ADMIN_CMD procedure. In DB2 LUW V8, this procedure did not support the IMPORT command. http://publib.boulder.ibm.com/infoce...n/r0012547.htm So you have to move to version 9 (where it is supported), or you use an external procedure that invokes the import/load command like described here: http://www.ibm.com/developerworks/db...ein/index.html

hi ,,

i am trying to execute the truncate stored procedure which is writtten in C from IBM website for DB2 UDB V8 .here is the URL( http://www.ibm.com/developerworks/db...ein/index.html) given to me by stolze .
i have a XL compiler in my machine and i precompiling the functions.sqc using the db2 compile successfully but i compiling using the xl compiler i am not able to do that .

Help me i just my truncate stored procedure to work fine rest of the stored procedures mentioned in this URL is not required to me .

Regards,