Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22

    Unanswered: how do you run Oracle style PL/SQL in 9.7?

    We have been running DB2 9.1.5 on the Z and now have a test server running 9.7 under Linux. I'd like to run some Oracle style PL/SQL blocks from the the Command Editor, but I get errors.

    BEGIN
    END;
    --- this runs with no error, so it seems to support BEGIN/END

    Here are two examples from the IBM manual on PL/SQL blocks:

    "The following example shows the simplest possible anonymous block statement that the DB2 data server can compile:"

    BEGIN
    NULL;
    END;

    "The following example shows an anonymous block that you can enter interactively through the DB2 CLP:"

    SET SERVEROUTPUT ON;

    BEGIN
    dbms_output.put_line( 'Hello' );
    END;

    Both give an error similar to below if I try and run them:
    "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 "NULL" was found following "BEGIN ". Expected tokens may include: "<psm_function_compound_stmt>". LINE NUMBER=1. SQLSTATE=42601"

    Any ideas here?

    Dave

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you set DB2_COMPATIBILITY_VECTOR? Do you use CLPPlus to run your example?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    Thanks for the reply. I did not perform the install, so I'm not sure what options were used or where to set them. I am using the Control Center/Command Editor to run my programs/code. Here is the "about"

    ================================================== ==========
    About DB2 Administration Tools Environment
    ================================================== ==========
    DB2 administration tools level:
    Product identifier SQL09050
    Level identifier 03010107
    Level DB2 v9.5.0.808
    Build level s071001
    PTF NT3295
    ================================================== ==========
    Java development kit (JDK):
    Level IBM Corporation 1.5.0
    ================================================== ==========

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Setting up DB2 for Oracle application enablement - IBM DB2 9.7 for Linux, UNIX, and Windows

    Your client is at version 9.5, so obviously it won't support Oracle CLP features. Use clpplus.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    Thanks! We will check it out.

    Dave

  6. #6
    Join Date
    Jan 2009
    Posts
    33

    running pl sql in db2 v9.7

    Hello,
    in order to run a pl sql script in db2 we have to set the compatibility vector to ora before creating the db. I would like to know
    if the db is created and I set the parameter to ora and take a db restart will the parameter take effect on the existing databases.
    if the above one does not happen if instead of creating a db , i have a backup of the database from another server and I set the parameter and do a restore of the backup will the parameter take effect on the restored db.

    Pls assist me with this.

    Regards,
    Sharath

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sharaths_81 View Post
    if the db is created and I set the parameter to ora and take a db restart will the parameter take effect on the existing databases.
    No.

    Quote Originally Posted by sharaths_81 View Post
    if instead of creating a db , i have a backup of the database from another server and I set the parameter and do a restore of the backup will the parameter take effect on the restored db.
    No.

    The database must be created while DB2_COMPATIBILITY_VECTOR=ORA is in effect.

    You will still be able to run PL/SQL code in a database that was not created as compatible, because the PL/SQL compiler is integral to the database manager, but there won't be Oracle datatype support or compatible catalog views.

Posting Permissions

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