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

Did you set DB2_COMPATIBILITY_VECTOR? Do you use CLPPlus to run your example?

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
================================================== ==========

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.

Thanks! We will check it out.

Dave

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

No.

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.