Oracle to DB2 Migration issues

[Abram Friesen]

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 use
of Oracle Supplied Packages:

First, we make use of the DBMS_PIPE Oracle Supplied Package to
communicate from a trigger back to our application via named pipes. Is
there a way to do this in DB2?

Second, we want that communication to be transactionally controlled.
Of course, even in Oracle, communicating out of the trigger back to
the application happens immediately, not on the COMMIT. The way we
handle this in Oracle is by making use of the DBMS_TRANSACTION Oracle
Supplied Package. This allows us to retrieve a unique identifier for
the current transaction in the trigger, send it to the application via
DBMS_PIPE, and later check that transaction id against the data
dictionary table V$TRANSACTION to determine the state of the
transaction. The application then is able to apply the update only
after a COMMIT. So in DB2 how would I find a unique identifier for the
current transaction, and then how would I find out the state of a
transaction later from a different session?

Thanks in advance,
Abram Friesen

[Rhino]

"Abram Friesen" <ab***@oax.com> wrote in message
news:e4**************************@posting.google.c om...

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 use
of Oracle Supplied Packages:

First, we make use of the DBMS_PIPE Oracle Supplied Package to
communicate from a trigger back to our application via named pipes. Is
there a way to do this in DB2?

Second, we want that communication to be transactionally controlled.
Of course, even in Oracle, communicating out of the trigger back to
the application happens immediately, not on the COMMIT. The way we
handle this in Oracle is by making use of the DBMS_TRANSACTION Oracle
Supplied Package. This allows us to retrieve a unique identifier for
the current transaction in the trigger, send it to the application via
DBMS_PIPE, and later check that transaction id against the data
dictionary table V$TRANSACTION to determine the state of the
transaction. The application then is able to apply the update only
after a COMMIT. So in DB2 how would I find a unique identifier for the
current transaction, and then how would I find out the state of a
transaction later from a different session?

I can't address your specific questions directly but I seem to recall that
there used to be migration tools and/or guides for DB2's competitors at the
IBM website. For instance, if a customer was switching from Oracle to DB2,
these tools or guides would help them do so. Perhaps they might contain
discussions that would answer your questions? I never used or read any of
these things but I remember seeing mention of them on this newsgroup a few
years back.

I don't know where the migration tools/guides were and the website has been
recently reorganized so any URL mentioned in an old newsgroup post might be
dead anyway. All I can suggest is that you look around the DB2 portion of
the IBM website and see what you can find. A search on "migration" or
"migration Oracle DB2" might find them for you, if they are still there and
up-to-date.

Rhino

[Larry]

http://www-306.ibm.com/software/data/db2/migration/

Larry Edelstein

Rhino wrote:

"Abram Friesen" <ab***@oax.com> wrote in message
news:e4**************************@posting.google.c om...

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 use
of Oracle Supplied Packages:

First, we make use of the DBMS_PIPE Oracle Supplied Package to
communicate from a trigger back to our application via named pipes. Is
there a way to do this in DB2?

Second, we want that communication to be transactionally controlled.
Of course, even in Oracle, communicating out of the trigger back to
the application happens immediately, not on the COMMIT. The way we
handle this in Oracle is by making use of the DBMS_TRANSACTION Oracle
Supplied Package. This allows us to retrieve a unique identifier for
the current transaction in the trigger, send it to the application via
DBMS_PIPE, and later check that transaction id against the data
dictionary table V$TRANSACTION to determine the state of the
transaction. The application then is able to apply the update only
after a COMMIT. So in DB2 how would I find a unique identifier for the
current transaction, and then how would I find out the state of a
transaction later from a different session?

I can't address your specific questions directly but I seem to recall that
there used to be migration tools and/or guides for DB2's competitors at the
IBM website. For instance, if a customer was switching from Oracle to DB2,
these tools or guides would help them do so. Perhaps they might contain
discussions that would answer your questions? I never used or read any of
these things but I remember seeing mention of them on this newsgroup a few
years back.

I don't know where the migration tools/guides were and the website has been
recently reorganized so any URL mentioned in an old newsgroup post might be
dead anyway. All I can suggest is that you look around the DB2 portion of
the IBM website and see what you can find. A search on "migration" or
"migration Oracle DB2" might find them for you, if they are still there and
up-to-date.

Rhino

[Abram Friesen]

Thanks Larry and Rhino for the direction.

In case anyone's interested, I found an implementation of DBMS_PIPE
package for DB2 here:

http://www-106.ibm.com/developerwork...mukherjee.html

The link for the download of pipe.zip was broken, but I managed to
download it with:

http://www-106.ibm.com/developerwork...erjee/pipe.zip

Thanks again.

Abram Friesen

Larry <La***@nospam.net> wrote in message news:<0o**********************@news4.srv.hcvlny.cv .net>...

http://www-306.ibm.com/software/data/db2/migration/

Larry Edelstein

Rhino wrote:

"Abram Friesen" <ab***@oax.com> wrote in message
news:e4**************************@posting.google.c om...

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 use
of Oracle Supplied Packages:

First, we make use of the DBMS_PIPE Oracle Supplied Package to
communicate from a trigger back to our application via named pipes. Is
there a way to do this in DB2?

Second, we want that communication to be transactionally controlled.
Of course, even in Oracle, communicating out of the trigger back to
the application happens immediately, not on the COMMIT. The way we
handle this in Oracle is by making use of the DBMS_TRANSACTION Oracle
Supplied Package. This allows us to retrieve a unique identifier for
the current transaction in the trigger, send it to the application via
DBMS_PIPE, and later check that transaction id against the data
dictionary table V$TRANSACTION to determine the state of the
transaction. The application then is able to apply the update only
after a COMMIT. So in DB2 how would I find a unique identifier for the
current transaction, and then how would I find out the state of a
transaction later from a different session?

I can't address your specific questions directly but I seem to recall that
there used to be migration tools and/or guides for DB2's competitors at the
IBM website. For instance, if a customer was switching from Oracle to DB2,
these tools or guides would help them do so. Perhaps they might contain
discussions that would answer your questions? I never used or read any of
these things but I remember seeing mention of them on this newsgroup a few
years back.

I don't know where the migration tools/guides were and the website has been
recently reorganized so any URL mentioned in an old newsgroup post might be
dead anyway. All I can suggest is that you look around the DB2 portion of
the IBM website and see what you can find. A search on "migration" or
"migration Oracle DB2" might find them for you, if they are still there and
up-to-date.

Rhino

[Rhino]

"Abram Friesen" <ab***@oax.com> wrote in message
news:e4*************************@posting.google.co m...

Thanks Larry and Rhino for the direction.

In case anyone's interested, I found an implementation of DBMS_PIPE
package for DB2 here:

http://www-106.ibm.com/developerwork...mukherjee.html
The link for the download of pipe.zip was broken, but I managed to
download it with:

http://www-106.ibm.com/developerwork...erjee/pipe.zip
Thanks again.

Abram Friesen

You're welcome!

Rhino