Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2014-01-27T23:20:05Z by urielzamora
itsonlyme4
itsonlyme4
24 Posts
ACCEPTED ANSWER

Pinned topic Creating a new Stored Procedure??

‏2008-09-11T09:56:51Z |
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
Updated on 2008-09-11T16:21:13Z at 2008-09-11T16:21:13Z by Salvatore.Vacca
  • Salvatore.Vacca
    Salvatore.Vacca
    73 Posts
    ACCEPTED ANSWER

    Re: Creating a new Stored Procedure??

    ‏2008-09-11T10:13:05Z  in response to itsonlyme4
    Try adding '--' after ';' and adding ';' after last 'END'

    regards
    Salvatore Vacca

    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;
  • itsonlyme4
    itsonlyme4
    24 Posts
    ACCEPTED ANSWER

    Re: Creating a new Stored Procedure??

    ‏2008-09-11T14:01:48Z  in response to itsonlyme4
    That worked! Thank you so much!!!

    Can you explain WHY It worked????
  • Salvatore.Vacca
    Salvatore.Vacca
    73 Posts
    ACCEPTED ANSWER

    Re: Creating a new Stored Procedure??

    ‏2008-09-11T16:18:45Z  in response to itsonlyme4
    You have to distinguish the statement terminator of the stored procedure from the CLP default terminator (";").

    see also:
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apdv.routines.doc/doc/c0024345.html

    you can also use following way where is used "@" as terminator character;

    db2 -td@ -f <script_name>

    regards
    Salvatore Vacca
    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
    @
  • Salvatore.Vacca
    Salvatore.Vacca
    73 Posts
    ACCEPTED ANSWER

    Re: Creating a new Stored Procedure??

    ‏2008-09-11T16:21:13Z  in response to itsonlyme4
    Sorry the correct link:

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apdv.routines.doc/doc/t0009220.html
  • urielzamora
    urielzamora
    1 Post
    ACCEPTED ANSWER

    Re: Creating a new Stored Procedure??

    ‏2014-01-27T23:20:05Z  in response to itsonlyme4

    I have a similar problem just that I have several Stored procedures within a sql file, after applying '--' after the ';' I can see less errors but just one still appears, can I use the ';' at the final of all the 'END' ?

     

    thanks