473,746 Members | 2,678 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Informix to Oracle Migration

Hi,
I am sure if anyone can help me, you guys can!

I am an Informix Developer, moving to Oracle 8i and later databases.
I am struggling to find the Oracle equivelant to many of the useful
Informix features that I have come to rely upon.

I have looked through the Oracle/Infomix groups and some helpful
users have posted some solutions, but many of them seem to be very
complex work-arounds for features that were readily available in
Informix.

Let me give you some examples:

1) We regularly used the Informix LOAD FROM and UNLOAD TO statments
e.g.

UNLOAD TO '/tmp/orderdata.unl'
SELECT * FROM ORDER
WHERE val > 100000;

There does not seem to be an easy way to do this in Oracle. Am I
missing something ( I am sure the answer to that is "Yes"! )

2) Informix has a serial data type, so if you had a primary key that
you would like to auto-increment, you declare it as serial, all done.
I searched groups and the solution seems to be to create a sequence
and on insert triggers. Seems a long workaround to me. Is this really
the recommended way of implementing this behavior in Oracle?

If, during testing, you wish to unload some of the tables to file
and reload them when the tables have been dropped and re-created, it
seems to be a struggle, as the sequence will generate new key values
for all the records on insert, and referential integrity is broken.
Informix's serial datatype does not have a problem with this, if you
insert a row with a specific value into a serial column, and the value
is unique, it inserts that value rather than a new serial value, so
loading old data back into the table is not a problem, integrity in
maintained.

3) Any user other than the table owner must prefix the table name in
queries with the owner name, e.g. DEVUSER.ORDER. Why is this? Would
any sensible database design allow for two identically named tables in
the same schema, but with different owners? I do appreciate that
synonyms can be created to work around this, but again this seems to
be a workaround for a problem that just creates extra hassle and no
benefit.

I am sure you guys can help me out with this stuff, it would make my
professional life a hell of a lot easier if I felt with Oracle, like I
did with Informix, that I am being assisted by the database, not that
it is my greatest enemy to hitting deadlines.

Thanks in advance

PH
Jul 19 '05 #1
1 8817
ph************* @totalise.co.uk (Phil Hindmoor) wrote in message news:<34******* *************** ****@posting.go ogle.com>...
Hi,
I am sure if anyone can help me, you guys can!

I am an Informix Developer, moving to Oracle 8i and later databases.
I am struggling to find the Oracle equivelant to many of the useful
Informix features that I have come to rely upon.

I have looked through the Oracle/Infomix groups and some helpful
users have posted some solutions, but many of them seem to be very
complex work-arounds for features that were readily available in
Informix.

Let me give you some examples:

1) We regularly used the Informix LOAD FROM and UNLOAD TO statments
e.g.

UNLOAD TO '/tmp/orderdata.unl'
SELECT * FROM ORDER
WHERE val > 100000;

There does not seem to be an easy way to do this in Oracle. Am I
missing something ( I am sure the answer to that is "Yes"! )
look up the SQl*PLUS SPOOL command.

2) Informix has a serial data type, so if you had a primary key that
you would like to auto-increment, you declare it as serial, all done.
I searched groups and the solution seems to be to create a sequence
and on insert triggers. Seems a long workaround to me. Is this really
the recommended way of implementing this behavior in Oracle?
Yes. You got a problem with that? ;^)
If, during testing, you wish to unload some of the tables to file
and reload them when the tables have been dropped and re-created, it
seems to be a struggle, as the sequence will generate new key values
for all the records on insert, and referential integrity is broken.
Informix's serial datatype does not have a problem with this, if you
insert a row with a specific value into a serial column, and the value
is unique, it inserts that value rather than a new serial value, so
loading old data back into the table is not a problem, integrity in
maintained.
When you program the trigger to handle that column, you can progrm
this way.
IOW, in the trigger, if the value for that column is NULL, then get
the next sequence value, else just let the current value be inserted.

Look at the examples again. It's not the problem that you think.
3) Any user other than the table owner must prefix the table name in
queries with the owner name, e.g. DEVUSER.ORDER. Why is this?
That's standard SQL
Would
any sensible database design allow for two identically named tables in
the same schema, but with different owners?
Read the manuals about SCHEMA, but I think, your answer
to this question is the expected NO.
(and I beleive it is also impossible to do.)
I do appreciate that
synonyms can be created to work around this, but again this seems to
be a workaround for a problem that just creates extra hassle and no
benefit.
You will find much more benefit than hassle to distinct SCHEMAs.
Think of it as similar to the difference between programming in BASIC
(global variables) and programming in C or PASCAL.

I am sure you guys can help me out with this stuff, it would make my
professional life a hell of a lot easier if I felt with Oracle, like I
did with Informix, that I am being assisted by the database, not that
it is my greatest enemy to hitting deadlines.

Thanks in advance

PH

You are going thru a small culture shock. Much of what you mentioned
is just going thru the learning curve. Don't dispair, you'll be fine.
Take a class and you'll get comfortable more quickly.

and Welcome to the world of ORACLE!

Ed
Also checkout comp.databases. oracle.misc and the other 3 ORACLE
related groups, since comp.databases. oracle is suposed to go away.
(Why GOOGLE still lets me post here is a mystery.)
Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
7595
by: ky | last post by:
I'll ask the age old question, can anyone direct me to some Oracle vs. Informix comparision information? I am looking for something as concise as possible but with good content. Thanks in advance.
4
19993
by: susmita_ganguly | last post by:
Hi I am trying to upgrade from oracle 8i to oracle 9i on the same server ..I don't know much abt migration . Can anyone help me out. Thanks. Susmita
0
2191
by: Chavan Koya | last post by:
Hi, This is my first posting to this group. We are thinking to migrate a Informix database which is running on HP-UX 11. Initially we are try to evaluate the Oracle Migration WorkBench to do this. Did any one do migration using this tool. We are getting "No suitable Driver" error message after giving the INFORMIXSERVER name, port number, user id, password. We are using JDBC 2.1 Version for connectivity. Does any body has any ides or...
4
3986
by: Abram Friesen | last post by:
Hi, I'm a developer for a software application vendor, and our application makes use of a customer-maintained Oracle 8i/9i database. We've had a customer request to support DB2 database, and I'm looking into the feasibility of migration. Our application runs on NT Server and, the DB2 database would be in some cases on a separate AIX machine, in other cases on an AS/400. There are two problems we are running into, both relating to our...
8
3786
by: Tavish Muldoon | last post by:
At a high level - what would be involved in switching from Oracle to DB2? Pretty vague, I know - but anyone have experience with this kind of migration? Pointers? Things to look for? Tmuld
6
5583
by: FireHawkBr® | last post by:
Hi, My company uses Informix as corporative database. As experienced DBA of the company, I have that to make a study comparing Oracle, Informix and DB2 databases and suggesting new alternatives. I have 10 years of experience with Oracle, 2-3 year with DB2 UDB and few experience with Informix (there are anothers DBAs that manage Informix here). Anyone have any kind of comparative ? Or personal opinions that whom good experience with DBs.
56
4954
by: Ashish Patankar | last post by:
I want to migrate my Oracle 10g database to Db2. I want some documentation for the comparision between these to databases. I also want to know which features of Oracle 10g are supported by Db2 and which are not supported.
0
2662
by: Lester Knutsen | last post by:
A two-day IBM Informix and DB2 User Group Technical Conference - Friday and Saturday, December 8-9, 2006 We are very please to announce our Keynote Speakers --------------------------------------------------- * Friday, December 8, 2006 – Ambuj Goyal, General Manager of IBM Information Management * Saturday, December 9,2006 - Arvid Krishna, Vice President of Database Servers, IBM Information Management These are the top two executives...
3
4284
by: Rodolfo | last post by:
Hi everybody! I'm working on a migration from Informix to SQL Server... I'm doing it through DTS and ODBC. What I'm doing is using the DTS, my data origin is tha Informix database and mi target is the SQL Server database that I just created, this is via IBM Informix ODBC and I copy the tables... but the results are something like this:
0
8974
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9508
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9218
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8229
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6772
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6060
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3292
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2765
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2199
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.