Database Porting Considerations

1.1.1 Fetch 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 Take 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 Conversions
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:

Convert date format changes in SQL Server style parameter: 20;
Convert date format in Oracle change format parameter: Yyyy-mm-dd hh24:mi:ss ' yyyy ', ' mm ', ' dd ', ' Hh12 ', ' hh24 ', ' mi ', ' SS ', etc.
DB2 Conversion date format changes System constants: Current timestamp ' current date ', ' Current time ', and so on;
NOTE 2:

SQL Server data type changed "data type parameter": int, varchar, etc.;
Oracle data type change function: To_char (), To_date (), To_number (), etc.;
DB2 Data type change function: varchar (), int (), date (), Time (), and so on;
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 If system then administrator when Roletype then special role else ordinary user end as case Convert from Global_code G
Db2:select Case G.master_type If system then administrator when Roletype then special role else general user end as Caseco Nvert 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 () lengths () Length ()
Take substring Substring () Substr () Substr ()
1.2
Report
1.2.1 Db2v8.
1 Common commands
Create a database on the server to execute DB2 create DB oatemp
Deleting a database executes DB2 DROP DB oatemp on the server
CREATE TABLE space DB2 create USER temporary tablespace USERSPACE1 MANAGED by SYSTEM USING (USERSPACE1)
Start database switch User su–db2inst1 start database: Db2start
Turn off database switch user su–db2inst1 Close database db2stop [Force]
Create a remote management node into 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
Connecting the database DB2 connect to OADB2 USER db2inst1 USING ibmdb2
Close the database connection DB2 terminate
Execute 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-wib MDB2-E-O PUTSQ.DB2
View the port being used 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 Usern Ame from Auth_User FETCH first 3 ROWS only
Start jdbc db2jstrt jdbc APPLET SERVER 6789

1.1 Database Porting Considerations
1.1.1 Fetch 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 Take 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 Conversions
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:

Convert date format changes in SQL Server style parameter: 20;

Convert date format in Oracle change format parameter: yyyy-mm-dd hh24:mi:ss xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/ >

' yyyy ', ' mm ', ' dd ', ' Hh12 ', ' hh24 ', ' mi ', ' SS ' and so on;

DB2 Conversion Date format Change System constant: Current timestamp

' Current date ', ' current time ' and so on;

NOTE 2:

SQL Server data type changed "data type parameter": int, varchar, etc.;

Oracle data type change function: To_char (), To_date (), To_number (), etc.;

DB2 Data type change function: varchar (), int (), date (), Time (), and so on;

1.1.6 Value judgment
SQL Server:

Select Caseconvert =

case If G.master_type = ' system ' Then ' Administrator '

When g.master_type = ' roletype ' then ' special characters '

Else ' normal user '

End

From Global_code G

Oracle:

Select Case G.master_type

When the ' system ' then ' Administrator '

When ' Roletype ' and ' special characters '

Else ' normal user '

End

As Caseconvert

From Global_code G

DB2:

Select Case G.master_type

When the ' system ' then ' Administrator '

When ' Roletype ' and ' special characters '

Else ' normal 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 () lengths () Length ()
Take substring substring () Substr () Substr ()

1.2 Attached
1.2.1 db2v8.1 Common commands
Create a database on the server to execute DB2 create DB oatemp

Deleting a database executes DB2 DROP DB oatemp on the server

CREATE TABLE Space

DB2 "CREATE USER temporary tablespace USERSPACE1 MANAGED by SYSTEM USING (' USERSPACE1 ')"

Start Database Switch User Su–db2inst1

Startup database: Db2start

Turn off database switch user su–db2inst1 Close database db2stop [Force]

Create a remote management node to 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

Connecting to a database

DB2 CONNECT to OADB2 USER db2inst1 USING ibmdb2

To close a database connection

DB2 Terminate

Execute script

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

To view the ports that are in use

Netstat-a

Querying system tables

Select COUNT (*) from SYSCAT. TABLES where tabschema= ' Db2inst1 '

Select COUNT (*) from SYSCAT. Procedures where procschema= ' Db2inst1 '

Collected in 2007-07-05

Database Porting Considerations