Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Unanswered: DB2 SQL Procedure

    Hello,

    I am testing SQL procedures in DB2 6.1 for AIX, but I can not create such a procedure. I get this error:

    CREATE PROCEDURE SCHEMA1.testProc( IN column char(2),OUT result varchar(1000)) \
    LANGUAGE SQL \
    MODIFIES SQL DATA \
    BEGIN \
    UPDATE SCHEMA1.testP SET A = 100; \
    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 "SQL" was found following "char(1000))
    LANGUAGE". Expected tokens may include: "<proc_lang>". SQLSTATE=42601

    Can you help me, please?

    Thank you

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    you need to use a different delimiter, e.g.

    db2 -vtd@ ...

  3. #3
    Join Date
    Nov 2002
    Posts
    4
    Thank you so much, but I can undestand it. Could you write a little more extensive explanation, please?

    Thank you again.

    Originally posted by Damian Ibbotson
    you need to use a different delimiter, e.g.

    db2 -vtd@ ...

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I don't really know the whole story on this, as I don't understand why SQL run from the command line doesn't require a SQL delimiter but falls over when it finds the default delimiter.

    If anyone can explain, I'd like to know?

    Basically as far as you're concerned, if you set the SQL delimiter to something other than a semi-colon ('@' for example), the SQL will execute without falling over when it finds one.

    You can specify the statement termination character using the -td flag with the Command Line Processor

    db2 -td@ "CREATE PROCEDURE SCHEMA1.testProc( IN column char(2),OUT result varchar(1000)) \
    LANGUAGE SQL \
    MODIFIES SQL DATA \
    BEGIN \
    UPDATE SCHEMA1.testP SET A = 100; \
    END"

    or in an example where you would require the termintating delimiter...

    db2 -td@ << !!
    CREATE PROCEDURE SCHEMA1.testProc( IN column char(2),OUT result varchar(1000))
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    UPDATE SCHEMA1.testP SET A = 100;
    END
    @
    !!

  5. #5
    Join Date
    Nov 2002
    Posts
    4
    Thank you, but changing the delimiter has not solved my problem. It seems a problem related to languages supported by database in stored procedures.

    My problem goes on...

    Than you

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If I remember right, SQL Stored Procedures were not available on DB2 6.1 for UWO ...

    Cheers

    Sathyaram

    Originally posted by dsimonthales
    Thank you, but changing the delimiter has not solved my problem. It seems a problem related to languages supported by database in stored procedures.

    My problem goes on...

    Than 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
  •