By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,833 Members | 2,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,833 IT Pros & Developers. It's quick & easy.

Oracle to DB2 Migration issues

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"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
Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a

"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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.