1, take the first n records.
Oracle:Select * from TableName where rownum & lt； = N；
DB2:Select * from TableName only extracts the first n rows;
2. Get the system date
Oracle: select sysdate from dual.
DB2: Select the current timestamp from sysibm.sysdummy 1;
3. Null value conversion
Oracle:Select productid，loginname，nvl(cur_rate，' 0') from TableName。
DB2: Select productid, loginname, value(cur_rate,' 0') from TableName;
Union (cur_rate,' 0')
4. Type conversion (version 8 has to _ char, to _ date, and version 9 has added to_number)
Oracle:select to_char(sysdate，' YYYY-MM-DD HH24:MI:SS ')from dual；
DB2:select varchar (current timestamp) from sysibm.sysdummy1;
## Oracle data type change functions: to_char (), to_date (), to_number (), etc.
If you only take the year, month, day, etc. , which can be obtained by using to _ char (sysdate,' yyyy'), to _ char ('mm') and to _ char ('DD').
Take TRUNC for example,
Use time, minutes and seconds to _CHAR(SYSDATE,' HH24:MI:SS').
## DB2 data type change functions: char (), varchar (), int (), date (), time (), etc.
How to get the year, month, day (current timestamp), hour (current timestamp), minute (current timestamp), second (current timestamp), microsecond (current timestamp),
You can use date (current timestamp) and time, minute and second (current timestamp) only when you take year, month and day.
Char () is a fixed-length string (1-255), and varchar () is a non-fixed-length string (1-32672).
Date and time forms are changed to character forms: char (current date), char (current time).
Convert the character string into date or time form: timestamp ('2002- 10-2012: 00: 00'), date ('2002-10-20'),
Date ("10/20/2002") and time ("12:00:00")
# # At present, DB2 V8 also supports to_char and to_date.
5, quickly empty the big table
Oracle:truncate table TableName；
DB2:alter table TableName active did not record an empty table initially;
6. about ROWID
Oracle, only generated by the database, can be obtained in the program.
DB2 v8 also has this function.
7. Recipient number
Oracle:select to _ number(' 123 ')from dual；
DB2:select cast(' 123' is an integer) from sysibm.sysdummy1;
SELECT CAST (current time is char (8)) from sysibm.sysdummy1
8. Create a similar table
Oracle: create table a as select * from b；;
DB2: Create Table A like B;
Create table tab _ newselect col1,col2 … is defined only from tab _ old (version 8 is valid, version 9 is invalid).
9. Decoding method
Oracle:DECODE method (DECODE (condition, value 1, translation value 1, value 2, translation value 2, ... value n, translation value n, default value)) or case statement.
There are only CASE expressions in DB2
Select the id, name,
situation
When the integer (flag) = 0, it is false.
When the integer (flag) = 1, it is true.
Otherwise "abnormal"
end
From testing
or
Select the id, name,
Uppercase and lowercase integer (flag)
When 0, it is "false"
When 1
Otherwise "abnormal"
end
From testing
10, subquery (versions 8 and 9 also support subqueries)
Oracle: using subqueries directly
Db2:with statement
Use a 1
(The maximum value (id) selected from the test is aa 1)
Select idaa 1 from test a1
1 1, data type
A big difference:
Oracle:char 2000
DB2: char 254
Oracle: date date time
Db2: Date: Date Time: Time Timestamp: Date Time.
1, data type conversion function
Integer to character type
Plastic wire
String to floating-point type
Floating-point conversion string
Strings so far
String to timestamp
Date to string
oracle
to_char( 1)
Recipient number ('1')
to_number(' 1. 1 ')
to_char( 1. 1)
Deadline ('April 26th, 2007',' Year Month Day')
Deadline ('April 26, 2007 08: 08: 08',' YYY-MM-DDHH24: MI: SS')
To_char (to date ('April 29th, 2007',' Year Month Day',' Year Month Day')
DB2
char( 1)
int(' 1 ')
double(' 1. 1 ')
char( 1. 1)
Date ("April 26, 2007")
Deadline ('April 26, 2007 08: 08: 08',' YYY-MM-DDHH24: MI: SS')
Character (Date ('2007-04-29')
Compatible writing
Conversion (1 as character)
Cast(' 1' is int)
not have
not have
not have
be compatible
not have
2. Weak type judgment of 2.Where condition
Oracle: The where character field in (integer) is allowed, but DB2 does not.
Select "ABC" from dual, where "1" in (1) can be passed under oracle.
Select ABC from sysibm. Sysdumpy 1, where "1" in (1) gives an error under DB2.
Oracle: where character field = number field is allowed, DB2 is not allowed.
Select "ABC" from dual, where "1"=1can be passed under oracle.
Select ABC from sysibm. Under DB2, sysdummy1hre'1'=1gives an error.
Step 3 replace keywords
Oracle supports, DB2 does not support create or replace statements, which is illegal under DB2.
4. Subquery Alias
ORACLE supports select * from (1 from dual) or select * from (1 from dual).
DB2 supports select * from (select1from sysibm.sysdummy1) t or select * from (select1from sysibm.sysdummy1) as T..
Solid compatibility is written as select * from t.
5. Differences in date data types
In ORACLE, date types also include hours, minutes and seconds, but in DB2, dates are only years, months and days, such as' 2007-04-28', which can be directly operated as strings. In DB2, the timestamp type must be used to record hours and minutes
A common compatibility problem after adopting hibernate is:
If a field is defined as a date type in the mapping file
& ltproperty name = " create time " type = " Java . util . date " & gt；
& ltcolumn name = " CREATE _ TIME " length = " 7 "/>
& lt/property & gt；
Then under DB2, this field must be defined as timestamp, not DATE, or an error that the string is truncated correctly will be reported.
For DB2, you can directly specify the values of date or timestamp type fields with strings in the query conditions, such as where create_date = '2007-04-26' and where create _ timestamp =' 2007-04-26 08: 08: 08', instead of using the string-to-date function.
6, paging processing
If JDBC paging is adopted, please note that DB2 does not support rownum. For example, 10 records with the smallest area_id are obtained from the masa_area table. The statement is as follows Please pay attention to the writing of alias t here.
ORACLE:select t . * from(select rownum as r 1，masa_area。 * from masa_area order by area_id) t where t.r1<; = 10
DB2:select t . * from(select row number()over()as r 1，masa_area。 * from masa_area order by area_id) t where t.r1<; = 10
7. Decoding function
DB2 does not support the Decode function, and the compatible writing method is case when.
8.NVL function
DB2 does not support Nvl writing, and the compatible writing is coalesce.
ORACLE: selecting NVL(f_areaid,' empty') from masa_user is equivalent to selecting union (f_areaid,' empty', f_areaid) from masa_user.
DB2: Select coalesce(f_areaid,' empty', f_areaid) from masa_user.
9. the difference of 9.substr
Examples of DB2 substr are as follows:
The f_groupCode field in the masa_group table is defined as VARCHAR( 100), so the following statement will not go wrong, but it will go wrong if it is substr (f _ groupcode, 1,1).
select * from masa _ group where substr(f _ group code， 1，50)= ' 00 1006 ' order by f _ group code
There is nothing wrong with DB2, but
select * from masa _ group where substr(' 00 1006 '， 1，50)= ' 00 1006 ' order by f _ group code
Just report an error and say that the third parameter is out of limit.
This is because "00 1006" is defined as a character with a length of 6.