Results 1 to 14 of 14
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: How to compile stored procedure on DB2 9.7

    Hi,

    Please let me know How to compile stored procedures on DB2 9.7?

    Thanks in advance.

    Regards,
    Nagaraj

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    In which language are your stored-procedures written ?
    For plain SQL/PL procedures, it is enough to use 'CREATE PROCEDURE' syntax.

    For procedures written in C or Java or other languages, consult the documentation

  3. #3
    Join Date
    May 2012
    Posts
    4

    How to compile stored procedure on DB2 9.7

    Yes, its a normal PL/SQL statements, But i would like to know How to compile the stored procedures on DB2 9.7 version?

    Thanks,
    Nagaraj

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    For PL/SQL see
    CREATE PROCEDURE statement (PL/SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows

    You can use this statement in any client tool (e.g. Toad, DB-Vis, Command-Editor) or at the command-line.

  5. #5
    Join Date
    May 2012
    Posts
    4

    How to compile stored procedure on DB2 9.7

    Yes, I am executing the procedures on command editor and am getting the below error.

    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 "' WHERE C1 in (1,2)
    ". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=3.
    SQLSTATE=42601

    SQL0104N An unexpected token "END" was found following "' WHERE C1 in (1,2)
    ". Expected tokens may include: "<psm_semicolon>

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18

  7. #7
    Join Date
    May 2012
    Posts
    4

    How to compile stored procedure on DB2 9.7

    Can you please verify below sample SP?

    CREATE PROCEDURE SP_SAMPLE
    AS
    BEGIN
    SELECT * FROM DIM_STORE;
    END

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You need to study the example SQL/PL procedures that IBM provides with your DB2 installation.
    Or Get some training.

    If your DB2-server is on Windows default-installation look in this directory:
    C:\Program Files\IBM\SQLLIB\samples\sqlpl

    If your DB2 server is on a Unix/Linux, look in $INSTHOME/sqllib/samples/sqlpl

    There are many examples there. Study and Learn.

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    spoon feeding is not a habit in this forum
    see
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Did you read
    http://www.dbforums.com/db2/1679074-...ml#post6551310
    ?

    BTW, your Stored Proc cannot have a SELECT statement like this , without INTO .. Try INSERT/UPDATE/DELETE for a sample proc

    As others have mentioned, do some basic reading/understanding


    Quote Originally Posted by Nagarajch View Post
    Can you please verify below sample SP?

    CREATE PROCEDURE SP_SAMPLE
    AS
    BEGIN
    SELECT * FROM DIM_STORE;
    END
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Nagarajch View Post
    Can you please verify below sample SP?

    CREATE PROCEDURE SP_SAMPLE()
    AS
    BEGIN
    SELECT * FROM DIM_STORE;
    END
    Try this:
    Code:
    CREATE PROCEDURE SP_SAMPLE()
    DYNAMIC RESULT SETS 1
    BEGIN
    DECLARE c CURSOR WITH RETURN FOR
       SELECT * FROM DIM_STORE;
    OPEN c;
    END
    Don't forget to set the client SQL command delimiter to something else than ";"
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Mar 2013
    Posts
    19
    Quote Originally Posted by Peter.Vanroose View Post
    Try this:
    Code:
    CREATE PROCEDURE SP_SAMPLE()
    DYNAMIC RESULT SETS 1
    BEGIN
    DECLARE c CURSOR WITH RETURN FOR
       SELECT * FROM DIM_STORE;
    OPEN c;
    END
    Don't forget to set the client SQL command delimiter to something else than ";"
    THIS HELPED ME IN WRITING MY 1st DYNAMIC STORED PROC in DB2
    THANKS MATE.............

  13. #13
    Join Date
    Mar 2013
    Posts
    19
    can any1 tell me wat's wrong in following code :

    CREATE PROCEDURE SP_SAMPLE123()
    DYNAMIC RESULT SETS 2
    BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR
    SELECT * FROM syscat.schemata;
    OPEN c1;
    DECLARE c2 CURSOR WITH RETURN FOR
    select * from sagar123.emp;
    OPEN c2;
    END


    It is giving me error :

    "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 "<cursor declaration>" was found following "".
    Expected tokens may include: "<SQL statement>". LINE NUMBER=7.
    SQLSTATE=42601"

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see syntax diagram of
    Compound SQL (compiled) statement.

    Compound SQL (compiled) - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    Syntax
    
                          .-NOT ATOMIC-.   
    >>-+--------+--BEGIN--+------------+---------------------------->
       '-label:-'         '-ATOMIC-----'   
    
    >--+-----------------------------------------+------------------>
       | .-------------------------------------. |   
       | V                                     | |   
       '---+-| SQL-variable-declaration |-+--;-+-'   
           +-| condition-declaration |----+          
           '-| return-codes-declaration |-'          
    
    >--+----------------------------------+------------------------->
       | .------------------------------. |   
       | V                              | |   
       '---| statement-declaration |--;-+-'   
    
    >--+---------------------------------+-------------------------->
       | .-----------------------------. |   
       | V                             | |   
       '---DECLARE-CURSOR-statement--;-+-'   
    
    >--+--------------------------------+--------------------------->
       | .----------------------------. |   
       | V                            | |   
       '---| handler-declaration |--;-+-'   
    
    >--+------------------------------------+--END--+-------+------><
       | .--------------------------------. |       '-label-'   
       | V                                | |                   
       '---| SQL-procedure-statement |--;-+-'                   
    
    ...
    ...
    and How to read the syntax diagrams.
    How to read the syntax diagrams - IBM DB2 9.7 for Linux, UNIX, and Windows

    Code:
    How to read the syntax diagrams
    
    SQL syntax is described using the structure defined as follows: 
    
    Read the syntax diagrams from left to right and top to bottom, following the path of the line.
    
    The ►►─── symbol indicates the beginning of a syntax diagram.
    
    The ───► symbol indicates that the syntax is continued on the next line.
    
    The ►─── symbol indicates that the syntax is continued from the previous line.
    
    The ──►◄ symbol indicates the end of a syntax diagram.
    
    Syntax fragments start with the |─── symbol and end with the ───| symbol
    
    ...
    ...
    at the near bottom
    Code:
    ...
    ...
    
    Adjacent segments occurring between "large bullets" (●) may be specified in any sequence.
    
    
    >>-required_item--item1--●--item2--●--item3--●--item4----------><
    
    The above diagram shows that item2 and item3 may be specified in either order. Both of the following are valid: 
       required_item item1 item2 item3 item4
       required_item item1 item3 item2 item4
    
    ...
    As a consequence, DECLARE-CURSOR-statement can't be intermixed with other statements.
    All DECLARE-CURSOR-statements in a BEGIN END block should be written in one place without intermixed with other statements or declarations.
    Last edited by tonkuma; 03-08-13 at 07:37.

Posting Permissions

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