Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of switching from DB2 database to oracle database, I believe that most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Because there are too few people using DB2 database, the company decided to switch the database from DB2 database to oracle database because of the lack of professional DBA, but when it was really switched, it was found that there were a lot of unexpected things.

Because the bottom layer of the system uses ORM mapping tool, because there are no stored procedures, custom functions and triggers, I think the system has not changed much, but there are a lot of problems found.

1. Our primary keys are basically shared by Sequence, not auto-growing as primary keys.

However, getting Sequence is not the same in the two databases.

The method of obtaining DB2

The method of getting in values next value for eas.seq_SequenceNameOracle

Select seq_SequenceName.nextval from dual2 We use the ORM mapping tool ActiveRecord at the bottom, and it's strange to find that there can't be a semicolon at the end of the statement when executing SQL. This problem seems to be the problem of ActiveRecord itself.

For example:

/ public object ScalarBySQL (String sqlstr) {ISession sess = ActiveRecordMediator.GetSessionFactoryHolder (). CreateSession (typeof (ActiveRecordBase)); IDbConnection dbconn = sess.Connection; try {IDbCommand dbCommand = dbconn.CreateCommand (); dbCommand.CommandText = sqlstr; object thisReader = dbCommand.ExecuteScalar () Return thisReader;} catch (Exception ex) {LogInfo.Error (sqlstr + ex.Message); throw new Exception ("database execution statement error");} finally {dbconn.Close ();}}

For example: sqlstr= "select * FROM EAS.T_HRWagesLog where HRWL_Month='201603'"

Can be executed normally in DB2,Oracle, but if there is a semicolon at the end, it is an error directly in Oracle

ORA-00911: invalid character

3. Some database types are not supported, and some functions do not.

No XML, data type. The double-precision floating point data type is DOUBLE in DB2 and BINARY_DOUBLE in Oracle

Due to the use of a tool to export DOUBLE, directly converted to NUMBER (13), resulting in no decimal part, resulting in a large number of errors.

YEAR,MONTH,DAY does not have these functions.

The data in the conditional in is limited, otherwise the maximum number of expressions in the ORA-01795: list is 1000

4. Tables in the view definition cannot use as.

Create view ViewNameasselect a. HWD users FKMagold b. Upright name from T_HRWxData as a join T_user as b on a.HWD_User_FK=b.u_id can be used in Server SQL,DB2, but cannot be used in Oracle. It is necessary to remove create view ViewNameasselect a. HWD users FK Magnum b. Upright name from T_HRWxData a join T_user b on a.HWD_User_FK=b.u_id.

The biggest problem is the date

Select * FROM EAS.T_HRWxData where HWD_Date > = '2016-04-14' as above, it is OK in SQL Server,DB2, but it is wrong in Oracle. Oracle will not convert the string to date type.

In Oracle, it can be changed to

Select * FROM EAS.T_HRWxData where HWD_Date > = to_date ('2016-04-14 mm maidd`)

Date format processing containing hours 24 hours

To_date ('2016-03-06 13-12-12-12-9-13-13-13-13-12-12-12-12-12-12-12-12-12-13-13-13-13-13-12-12-12-12-12-12-12-12-12-12-13-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-13-13-13-13-13-03-06-13-13-03-06-13-13-13-13-13-13-13-13-13-13-13-03-06-13-13-03-06 13-13-13-13-13-12-12-13-03-06 13-13-03-06 13

The date format containing hours is processed for 12 hours, and an error will occur if the hour is 13:00 in the afternoon

To_date ('2016-03-06 01 VLV 12 purl 09mm Murray dd hh:mi:ss')

You can save timeless dates like 2016-04-14 in DB2, but it doesn't seem to work in Oracle, which leads to a lot of problems.

The above is all the contents of the article "sample Analysis of switching from DB2 Database to oracle Database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.