FAQ
I'm researching options to access an Informix database
Right now, the process involves generating a flat text file, ftp and
importing into our Oracle Database .
I know I can create an external table agains the flat file, but im
looking to connect to the Informix database and issue queries to
eliminate file transfers
I've heard of Oracle Gateway for Informix Database Heterogeneous
services, I'm wondering if anyone here has any experience with accessing
Informix from oracle and how much cost and trouble is it to set up?

Yep, Ive googled and started reading the doc(s),
http://download.oracle.com/docs/html/B10528_01/ch1.htm#1005648

But its looking bigger than Id like... hopefully soemone here has some
info for a more sane direction

Thanks!
Bob

--
"Oracle error messages being what they are, do not
highlight the correct cause of fault, but will identify
some other error located close to where the real fault lies."

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Hrishy at Jul 24, 2007 at 11:24 am
    Hi

    In my opinion the only real option you have is option
    2 and 3.

    Option 1 is more work out because you need to run it 3
    times.

    Option 2 is the most attractive one where you do a

    create table my_table parallel X nologging
    as
    sleect * from table partition_table p1
    union all the partitions which you wnat to keep

    then drop the partition tables or use

    The syntax available here
    http://www.psoug.org/reference/partitions.html

    in 10g you can do the partition redefination online.

    Hi All,

    We are in the process of migrating our Enterprise
    Edition 9i db to Standard
    Edition 9i. As we all are aware, Partitioning is not
    enabled in the Standard
    Edition, we would like to collapse all the partitions
    in our db before
    migrating it to Standard Edition. The current DB
    (Enterprise Edition) has 45
    tables with partitions with around a million records
    on an average in each
    table. The worst part is there is no primary key on
    these tables. Initially
    i thought an Online Redefinition of the tables will
    fix the thing for me,
    but the absence of Primary Keys makes this impossible.
    The data so far i
    have gathered is this:

    Online Redefinition is possible - But we do not
    have Primary keys - Can
    we do with ROWIDs? If yes, how?
    ALTER TABLE EXCHANGE PARTITION can be feasible -
    But i am not much aware
    of this option?
    Tried checking if Export/Import will help but i
    dont think this will.

    What are the various ways i have to collapse
    partitions in a DB?

    Thanks a lot in advance,
    Vikram Singh,
    Consultant DBA,
    Sierra Atlantic S/W Services Ltd.,
    Hyderabad,
    Andhra Pradesh - 509028
    India.

    Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
    now.
    http://uk.answers.yahoo.com/

    --
    http://www.freelists.org/webpage/oracle-l
  • Hrishy at Jul 24, 2007 at 11:43 am
    Hi

    Ohh Boy did i forget the merge partition statement if
    you have range partitions

    You can try
    ALTER TABLE PART MERGE PARTITIONS P1, P2 INTO

    PARTITION P12

    regards
    Hrishy

    hrishy wrote:
    Hi

    In my opinion the only real option you have is
    option
    2 and 3.

    Option 1 is more work out because you need to run it
    3
    times.

    Option 2 is the most attractive one where you do a

    create table my_table parallel X nologging
    as
    sleect * from table partition_table p1
    union all the partitions which you wnat to keep

    then drop the partition tables or use

    The syntax available here
    http://www.psoug.org/reference/partitions.html

    in 10g you can do the partition redefination online.




    Hi All,

    We are in the process of migrating our Enterprise
    Edition 9i db to Standard
    Edition 9i. As we all are aware, Partitioning is not
    enabled in the Standard
    Edition, we would like to collapse all the
    partitions
    in our db before
    migrating it to Standard Edition. The current DB
    (Enterprise Edition) has 45
    tables with partitions with around a million records
    on an average in each
    table. The worst part is there is no primary key on
    these tables. Initially
    i thought an Online Redefinition of the tables will
    fix the thing for me,
    but the absence of Primary Keys makes this
    impossible.
    The data so far i
    have gathered is this:

    1) Online Redefinition is possible - But we do not
    have Primary keys - Can
    we do with ROWIDs? If yes, how?
    2) ALTER TABLE EXCHANGE PARTITION can be feasible -
    But i am not much aware
    of this option?
    3) Tried checking if Export/Import will help but i
    dont think this will.

    What are the various ways i have to collapse
    partitions in a DB?

    Thanks a lot in advance,
    Vikram Singh,
    Consultant DBA,
    Sierra Atlantic S/W Services Ltd.,
    Hyderabad,
    Andhra Pradesh - 509028
    India.





    Yahoo! Answers - Got a question? Someone out there
    knows the answer. Try it
    now.
    http://uk.answers.yahoo.com/
    One email account is enough. Simplify your life by switching other accounts into Yahoo! Mail.
    http://uk.docs.yahoo.com/mail/trueswitch_overview
  • Robyn at Jul 24, 2007 at 1:43 pm
    Hi Bob,

    We're using Oracle Heterogeneous Services to connect to an Informix
    database. We're on HP-UX and needed to purchase ODBC for HP-UX, but that
    was the only expense. ( I had it working with free stuff, but it was less
    reliable.) Overall, it's been a reasonable solution for our purposes, the
    connection has been stable and once it was configured, it has been very very
    low maintenance.

    However, this is a very low volume, non critical data exchange so we didn't
    implement the whole transparent gateway thing. Cost and complexity would
    have increased considerably. The Oracle database for the connection is
    9.2.0.8.

    Robyn
    On 7/23/07, Bob wrote:

    I'm researching options to access an Informix database
    Right now, the process involves generating a flat text file, ftp and
    importing into our Oracle Database .
    I know I can create an external table agains the flat file, but im looking
    to connect to the Informix database and issue queries to eliminate file
    transfers
    I've heard of Oracle Gateway for Informix Database Heterogeneous services,
    I'm wondering if anyone here has any experience with accessing Informix from
    oracle and how much cost and trouble is it to set up?

    Yep, Ive googled and started reading the doc(s),
    http://download.oracle.com/docs/html/B10528_01/ch1.htm#1005648

    But its looking bigger than Id like... hopefully soemone here has some
    info for a more sane direction

    Thanks!
    Bob

    --
    "Oracle error messages being what they are, do not
    highlight the correct cause of fault, but will identify
    some other error located close to where the real fault lies."
    --
    http://www.freelists.org/webpage/oracle-l
  • Bob at Jul 25, 2007 at 11:24 am
    Thanks for the reply. I was going round and round with oracle all day to
    try and get pricing. An SR could not provide me with a verifiable price
    list - rather I was quoted "about 15k from the transparent gateway...
    form an internal price list" and

    "...HSODBC Heterogeneous services / ODBC aka Generic Transparent
    Gateway" is "free" from oracle..." but I would need to get the drivers
    from a third party or informix.

    I can see why license compliance can be a service provided by outside
    vendors....I challange onyone to search the net or oracle site for
    verifiable licensing costs... :)

    Anyways, my os is oracle 9i 2 on solaris, if you have any notes on how
    this is done please send them to me, I would greatly appreciate it ...
    all the documentation about this is what Ive come to label "circular
    reference...."

    Thanks again
    bob

    Robyn wrote:
    Hi Bob,

    We're using Oracle Heterogeneous Services to connect to an Informix
    database. We're on HP-UX and needed to purchase ODBC for HP-UX, but
    that was the only expense. ( I had it working with free stuff, but it
    was less reliable.) Overall, it's been a reasonable solution for our
    purposes, the connection has been stable and once it was configured,
    it has been very very low maintenance.

    However, this is a very low volume, non critical data exchange so we
    didn't implement the whole transparent gateway thing. Cost and
    complexity would have increased considerably. The Oracle database for
    the connection is 9.2.0.8 <http://9.2.0.8>.

    Robyn

    On 7/23/07, *Bob* > wrote:

    I'm researching options to access an Informix database
    Right now, the process involves generating a flat text file, ftp
    and importing into our Oracle Database .
    I know I can create an external table agains the flat file, but im
    looking to connect to the Informix database and issue queries to
    eliminate file transfers
    I've heard of Oracle Gateway for Informix Database Heterogeneous
    services, I'm wondering if anyone here has any experience with
    accessing Informix from oracle and how much cost and trouble is it
    to set up?

    Yep, Ive googled and started reading the doc(s),
    http://download.oracle.com/docs/html/B10528_01/ch1.htm#1005648

    But its looking bigger than Id like... hopefully soemone here has
    some info for a more sane direction

    Thanks!
    Bob

    --
    "Oracle error messages being what they are, do not
    highlight the correct cause of fault, but will identify
    some other error located close to where the real fault lies."
    --
    "Oracle error messages being what they are, do not
    highlight the correct cause of fault, but will identify
    some other error located close to where the real fault lies."

    --
    http://www.freelists.org/webpage/oracle-l
  • Jason Heinrich at Jul 25, 2007 at 2:27 pm
    You can find Oracle¹s ³official² price list at
    http://www.oracle.com/corporate/pricing/eplext.pdf
    On 7/25/07 6:24 AM, Bob wrote:

    Thanks for the reply. I was going round and round with oracle all day to try
    and get pricing. An SR could not provide me with a verifiable price list -
    rather I was quoted "about 15k from the transparent gateway... form an
    internal price list" and

    "...HSODBC Heterogeneous services / ODBC aka Generic Transparent Gateway" is
    "free" from oracle..." but I would need to get the drivers from a third party
    or informix.

    I can see why license compliance can be a service provided by outside
    vendors....I challange onyone to search the net or oracle site for verifiable
    licensing costs... :)

    Anyways, my os is oracle 9i 2 on solaris, if you have any notes on how this is
    done please send them to me, I would greatly appreciate it ... all the
    documentation about this is what Ive come to label "circular reference...."

    Thanks again
    bob
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College
    (850) 478-8496 x2509
    jheinrich_at_pcci.edu

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 23, '07 at 11:44p
activeJul 25, '07 at 2:27p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase