How to implement Oracle in DB2 (3) Author: CCBZZPP in real-world applications May often encounter some of the functions of Oracle in DB2, here I simply summarize, implement a function There are many ways, there is not all listed here. You are welcome to continue, so you can share, discuss together, and close in the same! (The following mainly in Oracle8i, 9i, and DB2 7.x as an example).
1. How to check how many database instances Oracle and DB2 Oracle Oracle can implement this: SQL> Select * from V $ instance; DB2 can be implemented like this: DB2ILIST2 is executed in the command window. How to query how many tables with how many tables in the database, Oracle and DB2 Oracle can implement this: SQL> Select * from all_tables; DB2 can be implemented in this way: SELECT * from syscat.tables; 3. How to know the situation of the chain Oracle and DB2 Oracle can be implemented: SQL > Select s.SID session_id, s.username, decode (lmode, 0, 'none, 1,' null ', 2,' row-s (sx) ', 3,' row-x (sx) ', 4 , 'Share', 5, 'S / ROW-X (SSX)', 6, 'Exclusive', To_Char (LMODE)) MODE_HELD, DECODE (Request, 0, 'None', 1, 'Null', 2, ' Row-S (SS) ', 3,' ROW-X (SX) ', 4,' Share ', 5,' S / Row-x (SSX) ', 6,' Exclusive ', TO_CHAR (Request) Mode_Requested , O.owner || '.' || O.Object_name || '(' || Object_Type || ')', S.Type Lock_Type, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 from V $ LOCK L, Sys.dba_Objects O, V $ session s where l.sid = s.SID and L.ID1 = O.Object_id DB2 can be implemented like this: Before you perform your stored procedure, execute the command to open the lock DB2 Update Monisor Switches Using Lock ON; then perform your stored procedure, use the command db2 get snapshot for locks on YourDaSename during the execution of the stored procedure; you can see you Lock 4. How to unlock the locked table to unlock Oracle and DB2 Oracle to implement: SQL> ALTER SYSTEM KILL session 'SID, Service #'; DB2 can be implemented in this way: DB2 Force Application All; DB2 Terminate 5. Test SQL statement ORACLE and DB2 Write Oracle Oracle can be implemented like this: SQL> SET TIMING ON; SQL> SELECT * from TableName; DB2 can be implemented like this: DB2BATCH -D library name -f contains files with SQL statements Name; 6. How to configure sequence's Oracle and DB2 Oracle Oracle can implement this: Jian SEQUENCE SEQ_CUSTID CREATE SEQUENCE SEQ_CUSTID START 1 IncremeMT by 1;
When building a table: CREATE TABLINT NOT NULL, ...} Insert When: INSERT INTO TABLE CUST VALUES (seq_cust.nextval, ...) DB2 can be implemented: Identity field attribute usage: CREATE TABLE CUST_ID SMALLINT NOT Null Generated Always As Indentity (Start with 1 Increment By 1) Insert: Insert Into Table Cust (Cust_ID, ...) Values (Default, ...) Renewed ...
