trying to create this stored procedure.. Frustraiting because when I try to run it in command editor it is throwing syntax errors.. 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

You care to tell us which version and platform of DB2 you are using and what the exact error message is that you got?

DB2 V9.1 Fix Pack2 on Windows Server.

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

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;



I don't think you need 'ELSE' in the above statement.. it's not necessary.


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;
END IF;

Took out the ELSE

now I get this:

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

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

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

You need to specify a statement terminator other than ';'.

yeah, i usually put the @ sign after the 'last' END..so it will look like this ---

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;
END IF;

END
@




---and then use this command to create the sproc

db2 -td@ -vf file.sql

Thank you : )