How to achieve Oracle in DB2 (2) Author: CCBZZP In realistic applications, you may often encounter some of the functions of Oracle in DB2, here I simply summarize, implement a certain 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 view the version of Oracle and DB2 in the database Oracle can implement: SQL> Connect System / Manager124 @ test; connected. SQL> SELECT * FROM V $ VERSION;
Banner ------------------------------------- --------------- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL / SQL Release 9.2.0.1.0 - Production Core 9.2.0.1.0 Production Tns for 32-Bit Windows : Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - PRODUCTION
DB2 can implement this: DB2LEVEL D: / SQLLIB / BIN> DB2LEVEL DB210851 Instance "DB2" Uses DB2 Code Release "SQL07020" DB2 "" DB2 "User Tokens" DB2 V7.1.0.40 "," N010415 "WR21254" .2. How to quickly empty a big table of Oracle and DB2 Oracle can implement: SQL> Truncate Table Table_name; DB2 can be implemented like this: alter table table_name active not logged initially with empty table; 3. How to Viewing the use of the table space Oracle and DB2 Oracle Oracle can be implemented like this: SELECT TABLESPACE_NAME, MAX_M, Count_BLOCKS Free_BLK_CNT, SUM_FREE_M, TO_CHAR (100 * SUM_FREE_M / SUM_M, '9999 ') ||'% 'AS
pct_free FROM (SELECT tablespace_name, sum (bytes) / 1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name), (SELECT tablespace_name AS fs_ts_name, max (bytes) / 1024/1024 AS max_m, count (blocks) AS count_blocks,
sum (bytes / 1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name) WHERE tablespace_name = fs_ts_name DB2 can be achieved: list tablespace containers for your tablespace number show detail; 4 how to remove portions of the date from a time point. The commonly used Oracle and DB2 Oracle can be implemented like this: 1>. Writing of the time point: select to_char (sysdate, 'yyyy') from dual; 2>. Write a time point: Select to_char (sysdate , 'Mm') from Dual; 3>. Write actions at the time of time: select to_char (sysdate, 'dd') from dual; 4>. Writing at the time point: select to_char (sysdate, 'hh24 " ) From dual; 5>. Writing of time points: select to_char (sysdate, 'mi') from dual; 6>. Write actions at the time point: select to_char (sysdate, 'ss') from dual; 7>. Writing of the milliseconds of the time point: (9i or more) Select Substr (SystimeStamp, 20, 6) from Dual; 8>. Date of Tetting Time Point: SELECT TRUNC (SYSDATE) from Dual; 9>. The time of time point is written: select to_char (sysdate, 'hh24: mi: ss') from dual; 10>. Date, time form becoming the character SELECT TO_CHAR (SYSDATE) from Dual; 11>. Convert cell string to Date or time form: select to_date ('2003/08/01') from dual; 12>. Return parameters of the weekly writing method: select to_char (sysdate, 'd') from dual; 13>. Return parameter for one year The next few days of writing: Select to_char (sysdate, 'ddd') from dual; 14>. Returns the number of seconds between the time values specified in midnight and parameters: select to_char (sysdate, 'sssss') from dual; 15>. Return parameters The first few weeks of one year: select to_char (sysdate, 'ww') from dual; DB2 can be implemented like this: 1>. Writing of the time point: select year (current timestamp) from sysibm.sdummy1; 2> . Write the time of the time point: Select Month (current timestamp) from sysibm.sysdummy1; 3>. Take time point of view: select day (current timestamp) from sysibm.system.sdummy1; 4>. Time point Writing: SELECT HOUR (CURRENT TIMESTAMP) from sysibm.sysdummy1; 5>. Take the point of time point: SELECT Minute (current timestamp) from sysibm.sysdummy1; 6>
. Write actions in time: Select Second (current timestamp) from sysibm.sdummy1; 7>. Write arsenic to the time point: Select Microsecond (Current TimeStamp) from sysibm.system.sdummy1; 8>. Date of Take the time point Writing: Select Date (Current TimeStamp) from sysibm.sdummy1; 9>. Time to take the time point of time: select time (current timestamp) from sysibm.system.sdummy1; 10>. Date, time morphology becomes character form: SELECT char (Current Date) from sysibm.system.sibmy1; select char (current time) from sysibm.sysdummy1; select char (Current Date 12 Hours) from sysibm.system.sdummy1; 11>. Convert cell string into date or time form: SELECT TIMESTAMP '2002-10-20-12.00.00.000000') from sysibm.sysdummy1; select timestamp ('2002-10-20 12:00:00') from sysibm.sysdummy1; select Date ('2002-10-20') from Sysibm.sysdummy1; Select Date ('10 / 20/2002 ') from sysibm.sysdummy1; select time ('12: 00:) from sysibm.sysdummy1; select time ('12.00.00') from sysibm.system; 12>. Tickets for returning parameters: Select DayName (Current TimeStamp) from sysibm.sysdummy1; select dayofweek (current timestamp) from sysibm.sy SDumMy1; Select Dayofweek_iso (current timestamp) from sysibm.sysdummy1; 13>. Returns the first day of the year in the year: select dayofyear (current timestamp) from sysibm.system.sdummy1; 14>. Returns the midnight and parameters specified in the parameters The number of seconds between time values: select midnight_seconds (current timestamp) form sysibm.system.sdummy1; 15>. Return parameter's first few weeks: SELECT WEEK (Current TimeStamp) Form Sysibm.sdummy1; .
