Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: help creating a stored procedure

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You care to tell us which version and platform of DB2 you are using and what the exact error message is that you got?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    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

  4. #4
    Join Date
    Dec 2002
    Posts
    123
    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;

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You need to specify a statement terminator other than ';'.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2002
    Posts
    123
    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

  8. #8
    Join Date
    Dec 2007
    Posts
    288
    Thank you : )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •