1.1 Database Transplant considerations
1.1.1 Take the previous N record
SQL Server:
SELECT TOP N *WM XTABLE
Oracle:
Select * from xtable where rownum <= n
DB2:
Select * from xtable fetch first n rows only
1.1.2 Take the date
SQL Server:
SELECT getDate ()
Oracle:
Select sysdate from dual
DB2:
Select Current TimeStamp from sysibm.sysdummy1
1.1.3 Connection string
SQL Server:
SELECT 'HELLO' 'TOONE'
Oracle:
SELECT 'HELLO' || 'Toone' from DUAL
DB2:
SELECT 'HELLO' || 'Toone' from sysimb.sysdummy1
1.1.4 null conversion
SQL Server:
SELECT Userid, UserName, Isnull (Email, '0') from auth_user
Oracle:
Select Userid, UserId, NVL (Email, '0') from auth_user
DB2:
SELECT Userid, UserName, Value (Email, '0') from auth_user
1.1.5 Type conversion
SQL Server:
Select Convert (Varchar, Getdate (), 20)
Oracle:
Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from Dual
DB2:
Select varchar (current timestamp) from sysibm.sysdummy1
Note 1:
■ Conversion date format in SQL Server changes Style parameters: 20;
■ Oracle Conversion Date Format Change Format Parameters: YYYY-MM-DD HH24: MI: SS
'YYYY', 'MM', 'DD', 'HH12', 'HH24', 'MI', 'SS', etc .;
■ DB2 Conversion Date Format Changing System Confoliation: Current TimeStamp
'Current Date', 'Current Time', etc .;
Note 2:
■ SQL Server Data Types Change "Data Type Parameters": int, varchar, etc .;
■ Oracle data type change function: to_char (), to_date (), to_number (), etc .;
■ DB2 data type change function: varchar (), int (), Date (), TIME (), etc .;
1.1.6 value judgment
SQL Server:
SELECT CASECONVERT = CASE WHEN G.MASTER_TYPE = 'System' TEN 'Administrator' when g.master_type = 'Roletype' Then 'Special Role' Else 'Ordinary User' End From Global_code Gracle:
SELECT CASE G.MASTER_TYPE WHEN 'SYSTEM' TEN 'Administrator' WHEN 'ROLETYPE' TEN 'Special Role' Else 'Ordinary User' End As CaseConvert from Global_code G
DB2:
SELECT CASE G.MASTER_TYPE WHEN 'SYSTEM' TEN 'Administrator' WHEN 'ROLETYPE' TEN 'Special Role' Else 'Ordinary User' End As CaseConvert from Global_code G
1.1.7 location
SQL Server:
SELECT Charindex ('E', 'Abcdef')
SELECT PATINDEX ('% E%', 'Abcdef')
Oracle:
SELECT INSTR ('Abcdef', 'E') from Dual
DB2:
Select Locate ('E', 'Abcdef') from sysibm.sysdummy1
1.1.8 Other functions
SQL Server
Oracle
DB2
Length len () length () length () tub string Substring () Substr () Substr ()
1.2 attached
1.2.1 DB2V8.1 Common Order
■ Create a database
Execute on the server
DB2 CREATE DB OATEMP
■ Delete the database
Execute on the server
DB2 DROP DB OATEMP
■ Create a table space
DB2 "Create User Temporary TableSpace Userspace1 Managed by System Using ('UserSpace1')"
■ Start database
Switch user
Su - db2inst1
Start the database:
DB2Start
■ Turn off the database
Switch user
Su - db2inst1
Close the database
DB2STOP [Force]
■ Create a remote management node
Enter the DB2 command window
DB2 Catalog Tcpip Node Asnode Remote 10.1.22.176 Server 50000
DB2 Catalog DB OADB2 AS OADB2 AT Node Asnode
■ Connect the database
DB2 Connect To OADB2 User DB2Inst1 Using IBMDB2
■ Turn off the database connection
DB2 Terminate
■ Execute scripts
DB2 -TD! -VF IOA2.DB2 -Z Info.log
■ Export script
DB2LOOK -D OADB2 -I DB2InSt1 -w ibmdb2 -e -o putsql.db2 -t auth_user
DB2LOOK -D OADB2 -I DB2Inst1 -w ibmdb2 -e -o putsq.db2 ■ View the port in use
Netstat -a
■ Query system table
SELECT Count (*) from syscat.tables where tabschema = 'db2inst1'
Select count (*) from syscat.procedures where procschema = 'db2inst1'
■ Common commands
DB2 "SELECT 'DROP TALBE' || TabName from syscat.tables where t
Abschema = 'db2inst1' and type = 't'> DB2DROPTABLES.DB2
DB2 "SELECT 'DROP View' || TabName from Syscat.tables Where T
Abschema = 'db2inst1' and type = 'v'> DB2DROPVIEWS.DB2
DB2 -VF DB2DROPTABLES.DB2 -Z Info.log
DB2 "SELECT Username from Auth_User Fetch First 3 Rows Only"
■ Start JDBC
DB2JSTRT JDBC Applet Server 6789 (executed on the server)
