Compatibility between DB2V9.7 and ORACLE

Compatibility between DB2V9.7 and ORACLE
After DB2 v9.7, DB2 is compatible with some features of ORACLE, such as PL/SQL, ROWNUM, and CONNECT.
Enable -www.2cto.com-compatible with ORACLE by setting the registry environment variable DB2_COMPATIBILITY_VECTOR = ORA.
After the preceding command is executed, all Oracle compatibility features of DB2 are activated. But in practice, sometimes developers only want to use one or more compatible features. Therefore, we need to explain the meaning of the DB2_COMPATIBILITY_VECTOR value first. The value of the environment variable DB2_COMPATIBILITY_VECTOR is a hexadecimal number. Each digit corresponds to one of the Oracle compatibility features. Developers can select one or more compatible features based on the needs of the actual project. Hexadecimal value compatibility features 1 (0x01) ROWNUM supports ROWNUM in WHERE Clause 2 (0x02) DUAL supports DUAL table 3 (0x04) outer join operator supports external join operators (+) 4 (0x08) Hierarchical queries support nested queries using connect by 5 (0x10) NUMBER data type supports NUMBER data type 6 (0x20) VARCHAR2 data type supports VARCHAR2 data type 7 (0x40) DATE data type supports combination of DATE and TIMESTAMP using 8 (0x80) truncate table supports turncate table statement 9 (0x100) Character literals supports assignment of CHAR and GRAPHIC data types 10 (0x200) Collection methods supports Collection methods, such as the first, last, next, and previous methods of ARRAY 11 (0x400) data dictionary-compatible views supports creating Data dictionary compatibility feature view 12 (0x800) PL/SQL compilation supports PL/SQL language the following is a simple step to enable: [yansp @ db2server ~] $ Db2set DB2_COMPATIBILITY_VECTOR = ORA [yansp @ db2server ~] $ Db2set-all [I] DB2_COMPATIBILITY_VECTOR = ORA [I] DB2COMM = TCPIP [g] DB2SYSTEM = db2server [g] DB2INSTDEF = yansp [yansp @ db2server ~] $ Db2 force applications allDB20000I The force application command completed successfully. DB21024I This command is asynchronous and may not be valid tive immediately. [yansp @ db2server ~] $ Db2stop2012-03-26 10:59:34 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [yansp @ db2server ~] $ Db2start03/26/2012 10:59:40 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. [yansp @ db2server ~] $ Db2 (c) Copyright IBM Corporation 1993,2007 Command Line Processor for DB2 Client 9.7.0You can issue database manager commands and SQL statements from the command prompt. for example: db2 => connect to sample db2 => bind sample. bndFor general help, type :?. For command help, type :? Command, where command can bethe first few keywords of a database manager command. For example :? Catalog database for help on the catalog database command? CATALOG for help on all of the CATALOG commands. to exit db2 interactive mode, type QUIT at the command prompt. outside interactive mode, all commands must be prefixed with 'dbdb '. to list the current command option settings, type list command options. for more detailed help, refer to the Online Reference Manual. db2 => get instance The current database manager instance is: yanspdb2 => create database Oracle automatic storage yesDB20000I The create database command completed successfully. db2 => list active databasesSQL1611W No data was returned by Database System Monitor. db2 => connect to oracle Database Connection Information Database server = DB2/LINUX 9.7.0 SQL authorization ID = YANSP Local database alias = ORACLEdb2 => select * from dualDUMMY ----- X 1 record (s) selected. [yansp @ db2server ~] $ Clpplus yansp/yansp @ db2server: 50000/oracle database connection information host name = db2server Database Server = DB2/LINUX SQL09070 SQL authorization id = yansp local database alias = ORACLE Port = 50000 CLPPlus: version 1.0 Copyright? 2009, ibm corporation. All rights reserved. SQL> values current schema 2; 1 -------------------------------------------------- YANSP DB250000I: The command is successfully completed. SQL> create table huateng (x int); DB250000I: The command is successfully completed. SQL> begin 2 for I in 1 .. 100 3 loop 4 insert into huateng values (I); 5 end loop; 6 commit; 7 end; 8/db251_ I: The command is successfully completed. SQL> select * from huateng where rownum <= 5; X ----------- 1 2 3 4 5 SQL> begin 2 dbms_output.put_line (10); 3 end; 4/db251_ I: the command is successfully completed. SQL> set serverout onSQL> run 1 begin 2 dbms_output.put_line (10); 3 * end10DB250000I: The command is successfully completed. SQL> author: TOMSYAN