Help creating a new stored procedure

[jpotucek]

IBM DB2 UDB on Windows V9.1 Fix Pack 1

trying to create this stored procedure.. Frustrating because when I try to run it in command editor it is throwing syntax errors.. I open command editor, get a connection to the Databae then excexute the CREATE PROCEDURE statement. Not sure what I'm doing wrong.....can anyone help???

CREATE PROCEDURE KCDWHDUT.truncate_table(IN sch_name VARCHAR(30),IN tab_name VARCHAR(30))
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

Get These errors:

CREATE PROCEDURE KCDWHDUT.truncate_table(IN sch_name VARCHAR(30),IN tab_name VARCHAR(30))
LANGUAGE SQL
BEGIN

DECLARE stmt VARCHAR(1000)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "E stmt
VARCHAR(1000)". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=5. SQLSTATE=42601

DECLARE param VARCHAR(1000)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DECLARE param VARCHAR" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
SQLSTATE=42601

DECLARE full_name VARCHAR(1000)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DECLARE full_name VARCHAR" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
SQLSTATE=42601

DECLARE a VARCHAR(130)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DECLARE a VARCHAR" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
SQLSTATE=42601

IF sch_name IS NULL THEN SET full_name = tab_name
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "IF sch_name IS NULL THEN" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601

SELECT tabname INTO a FROM SYSCAT.TABLES WHERE tabname = UCASE(tab_name)
SQL0206N "TAB_NAME" is not valid in the context where it is used.
SQLSTATE=42703

ELSE SET full_name = sch_name||'.'||tab_name
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "ELSE SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<variable_set>".
SQLSTATE=42601

SELECT tabname INTO a FROM SYSCAT.TABLES WHERE tabname = UCASE(tab_name) AND tabschema = UCASE(sch_name)
SQL0206N "TAB_NAME" is not valid in the context where it is used.
SQLSTATE=42703

END IF
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END IF".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

IF UCASE(a) = UCASE(tab_name) THEN SET param = 'IMPORT FROM NUL OF DEL REPLACE INTO '||full_name
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "IF UCASE(a) = UCASE(tab_name) THEN" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601

SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "STMT" is not valid in the context where it is used. SQLSTATE=42703

PREPARE s1 FROM stmt
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "s1" was found following "PREPARE ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601

EXECUTE s1 USING param
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "s1" was found following "EXECUTE ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601

ELSE END IF
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END" was found following "ELSE ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601

END
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.
User Response:
Examine and correct the statement in the area of the specified
token.
sqlcode : -104
sqlstate : 42601