Reposted from: longtang Bay
1.1.1 obtain the first N records
SQL Server: Select Top N * From xtable
ORACLE: Select * From xtable where rownum <= N
DB2: Select * From xtable fetch first n rows only
1.1.2 obtain the current 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 value conversion
SQL Server: Select userid, username, isnull (email, 0) from auth_user
ORACLE: Select userid, username, 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:
■ Changing the style parameter for the conversion date format in SQL Server: 20;
■ In Oracle, the format of the conversion date changes the formatting parameter: yyyy-mm-dd hh24: MI: SS 'yyyy', 'mm', 'dd', 'hh12', 'hh24', 'mi', and 'ss;
■ Changing the date format in DB2: Current timestamp 'current date', 'current time', and so on;
NOTE 2:
■ Changing the Data Type of SQL Server to "data type parameters": int and varchar;
■ Oracle Data Type Change functions: to_char (), to_date (), to_number (), and so on;
■ DB2 data type change functions: varchar (), INT (), date (), time (), etc;
1.1.6 value judgment
SQL Server: Select case convert = case when G. master_type = system then Administrator when G. master_type = roletype then special role else normal user end from global_code g
ORACLE: Select case G. master_type when system then Administrator when roletype then special role else common user end as caseconvert from global_code g
DB2: Select case G. master_type when system then Administrator when roletype then special role else common 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 ()
Substring () substr ()
1.2
Appendix
1.2.1 db2v8.
1 Common commands
■ Create a database and execute DB2 create dB oatemp on the server
■ Delete the database and execute DB2 drop dB oatemp on the server
■ Create a tablespace DB2 create user temporary tablespace userspace1 managed by system using (userspace1)
■ Start database switch user Su-db2inst1 start Database: db2start
■ Shut down database switch user Su-db2inst1 shut down database db2stop [force]
■ Create a remote management node and 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 to the database DB2 connect to oadb2 user db2inst1 using ibmdb2
■ Shut down database connection DB2 terminate
■ Execute the script 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-wibmdb2-e-o putsq. DB2
■ View the port netstat-A in use
■ Query System Table select count (*) from syscat. tables where tabschema = 'db2inst1' select count (*) from syscat. procedures where procschema = 'db2inst1'
■ Common command 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.
1.1 precautions for database migration
1.1.1 obtain the first N records
SQL Server: Select Top N * From xtable
ORACLE: Select * From xtable where rownum <= N
DB2: Select * From xtable fetch first n rows only
1.1.2 obtain the current 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 value conversion
SQL Server: Select userid, username, isnull (email, '0') from auth_user
ORACLE: Select userid, username, 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:
■ Changing the style parameter for the conversion date format in SQL Server: 20;
■ In Oracle, the format of the conversion date changes the formatting parameter: yyyy-mm-dd hh24: MI: ss xml: namespace prefix = o ns = "urn: Schemas-Microsoft-com: Office: office "/>
'Yyyy', 'mm', 'dd', 'hh12', 'hh24', 'mi', and 'ss;
■ Changing the date format in DB2: Current Timestamp
'Current date', 'current time', etc;
NOTE 2:
■ Changing the Data Type of SQL Server to "data type parameters": int and varchar;
■ Oracle Data Type Change functions: to_char (), to_date (), to_number (), and so on;
■ DB2 data type change functions: varchar (), INT (), date (), time (), etc;
1.1.6 value judgment
SQL Server:
Select caseconvert =
Case when G. master_type = 'system' then 'admin'
When G. master_type = 'Role type' then' special role'
Else 'normal users'
End
From global_code g
ORACLE:
Select case G. master_type
When 'system' then' administrator'
When 'Role type' then' special role'
Else 'normal users'
End
As caseconvert
From global_code g
DB2:
Select case G. master_type
When 'system' then' administrator'
When 'Role type' then' special role'
Else 'normal users'
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 ()
Substring () substr ()
1.2 attachment
1.2.1 Common commands for db2v8.1
■ Create a database and execute DB2 create dB oatemp on the server
■ Delete the database and execute DB2 drop dB oatemp on the server
■ Create a tablespace
DB2 "create user temporary tablespace userspace1 managed by system using ('userspace1 ')"
■ Start database switch user Su-db2inst1
Start Database: db2start
■ Shut down database switch user Su-db2inst1 shut down database db2stop [force]
■ Create a remote management node and 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 to the database
DB2 connect to oadb2 user db2inst1 using ibmdb2
■ Close database connection
DB2 terminate
■ Execute scripts
DB2-Td! -VF ioa2.db2-Z info. Log
■ Export scripts
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-
■ Query system table
Select count (*) from syscat. tables where tabschema = 'db2inst1' select count (*) from syscat. procedures where procschema = 'db2inst1'