Обсуждение: New Oracle system in our house, migration chances

Hi All, HPNY 2022

We acquired a new company, those guys run on Oracle 10, on AIX 5.2, both systems dating to around 2006 or so.
We would like to increase our chances of recovery in case of disaster, since those are unsupported versions of
Oracle/AIXAFAIK.
 
The application is written on powerbuilder.

The ppl in the respective IT dept claim they have no exotic SQL inside powerbuilder. I am almost 21 years into
PostgreSQL,I have done some MS SQL integration with PostgreSQL but almost no Oracle 
 
experience since 90's. What are my options here?

A)
1) use a tool like ora2pg and try to migrate the schema
2) try to load the data
3) migrate ORACLE code procedures and triggers (about 50 in total)
4) setup and test the app
or
B)
1) use a layer that provides DB code (procedures/triggers) compatibility and/or DDL compatibility
2) use a tool to move/migrate the schema and load data
3) setup and test the app

Any hints where to start, technical options, help to evaluate/assess various solutions/costs would be welcome.

I know this has been discussed extensively in PgSQL community, my intention is for this email to provide a platform
alongwith my concurrent/parallel reading on the matter.
 

Thank you

-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.

czw., 13 sty 2022 o 13:26 Achilleas Mantzios <achill@matrix.gatewaynet.com> napisał(a):

Hi All, HPNY 2022

We acquired a new company, those guys run on Oracle 10, on AIX 5.2, both systems dating to around 2006 or so.
We would like to increase our chances of recovery in case of disaster, since those are unsupported versions of Oracle/AIX AFAIK.
The application is written on powerbuilder.

The ppl in the respective IT dept claim they have no exotic SQL inside powerbuilder. I am almost 21 years into PostgreSQL, I have done some MS SQL integration with PostgreSQL but almost no Oracle
experience since 90's. What are my options here?

A)
1) use a tool like ora2pg and try to migrate the schema
2) try to load the data
3) migrate ORACLE code procedures and triggers (about 50 in total)
4) setup and test the app
or
B)
1) use a layer that provides DB code (procedures/triggers) compatibility and/or DDL compatibility
2) use a tool to move/migrate the schema and load data
3) setup and test the app

Any hints where to start, technical options, help to evaluate/assess various solutions/costs would be welcome.

I know this has been discussed extensively in PgSQL community, my intention is for this email to provide a platform along with my concurrent/parallel reading on the matter.

Thank you

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Hi Achilleas,

I would go with version B - since as I understand it's ISV solution. Meaning the postgres ddl was tested and aligned with the application layer, otherwise you would need to fix app layer after the migration.

Dziekuje Alicija. The powerbuilder app is in-house developed, source is available, what could be wrong with the app? Browsing through the powerbuilder source I don't see (by eye) anything that could break something in PgSQL.

 

-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

P {margin-top:0;margin-bottom:0;}

czw., 13 sty 2022 o 13:26 Achilleas Mantzios <achill@matrix.gatewaynet.com> napisał(a):

Hi All, HPNY 2022

We acquired a new company, those guys run on Oracle 10, on AIX 5.2, both systems dating to around 2006 or so.
We would like to increase our chances of recovery in case of disaster, since those are unsupported versions of Oracle/AIX AFAIK.
The application is written on powerbuilder.

The ppl in the respective IT dept claim they have no exotic SQL inside powerbuilder. I am almost 21 years into PostgreSQL, I have done some MS SQL integration with PostgreSQL but almost no Oracle
experience since 90's. What are my options here?

A)
1) use a tool like ora2pg and try to migrate the schema
2) try to load the data
3) migrate ORACLE code procedures and triggers (about 50 in total)
4) setup and test the app
or
B)
1) use a layer that provides DB code (procedures/triggers) compatibility and/or DDL compatibility
2) use a tool to move/migrate the schema and load data
3) setup and test the app

Any hints where to start, technical options, help to evaluate/assess various solutions/costs would be welcome.

I know this has been discussed extensively in PgSQL community, my intention is for this email to provide a platform along with my concurrent/parallel reading on the matter.

Thank you

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Hi Achilleas,

I would go with version B - since as I understand it's ISV solution. Meaning the postgres ddl was tested and aligned with the application layer, otherwise you would need to fix app layer after the migration.

​I agree with Alicja, we have used EnterpriseDB's Oracle compatibility layer to migrate off Oracle in the past.

Thank you Jeremiah.

 

CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.

-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

On 13/1/22 4:12 μ.μ., Kenneth Marshall wrote:
>>> Hi Achilleas,
>>> I would go with version B - since as I understand it's ISV
>>> solution. Meaning the postgres ddl was tested and aligned with the
>>> application layer, otherwise you would need to fix app layer after
>>> the migration.
>> Dziekuje Alicija. The powerbuilder app is in-house developed, source
>> is available, what could be wrong with the app? Browsing through the
>> powerbuilder source I don't see (by eye) anything that could break
>> something in PgSQL.
>> -- 
>> Achilleas Mantzios
>> DBA, Analyst, IT Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
> Hi Achilleas,
>
> If the schema and usage is straight-forward, I would recommend using
> ora2pg to give you an estimation cost for your migration by letting you
> know what cannot be automatically translated. Once you have that you
> will be better informed as to the work needed.
Thank you Ken!
>
> Regards,
> Ken
>
>


-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

On 13/1/22 4:13 μ.μ., Julien Rouhaud wrote:
> On Thu, Jan 13, 2022 at 04:06:06PM +0200, Achilleas Mantzios wrote:
>> Dziekuje Alicija. The powerbuilder app is in-house developed, source is
>> available, what could be wrong with the app? Browsing through the
>> powerbuilder source I don't see (by eye) anything that could break something
>> in PgSQL.
> I would go with option A if possible.  If you want to get an idea of how
> complicated a migration would be, ora2pg does have a migration cost assessment
> report [1].  It can even check the queries if you have the audit trail enabled
> on your oracle database (if that existed in that version).
>
> [1] https://ora2pg.darold.net/documentation.html#Migration-cost-assessment
Such great info. Thank you so much. 21 years ago we started building our system on top of PostgreSQL, there have been
challenges,but we never regreted our initial decision (vs mysql) not even once.
 


-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

As an ex-PB programmer, you should recompile the PowerBuilder app.  It 
is probably using "datawindows" to abstract the SQL away.  This way you 
can catch any incompatibilities and fix the datawindows accordingly.  
Obviously, you have to provide a different database driver as well.  
What version of PB are you using?

Regards,
Michael Vitale


Achilleas Mantzios wrote on 1/13/2022 9:20 AM:
> On 13/1/22 4:13 μ.μ., Julien Rouhaud wrote:
>> On Thu, Jan 13, 2022 at 04:06:06PM +0200, Achilleas Mantzios wrote:
>>> Dziekuje Alicija. The powerbuilder app is in-house developed, source is
>>> available, what could be wrong with the app? Browsing through the
>>> powerbuilder source I don't see (by eye) anything that could break 
>>> something
>>> in PgSQL.
>> I would go with option A if possible.  If you want to get an idea of how
>> complicated a migration would be, ora2pg does have a migration cost 
>> assessment
>> report [1].  It can even check the queries if you have the audit 
>> trail enabled
>> on your oracle database (if that existed in that version).
>>
>> [1] 
>> https://ora2pg.darold.net/documentation.html#Migration-cost-assessment
> Such great info. Thank you so much. 21 years ago we started building 
> our system on top of PostgreSQL, there have been challenges, but we 
> never regreted our initial decision (vs mysql) not even once.
>
>

On 13/1/22 4:28 μ.μ., MichaelDBA wrote:
Hi Michael, thank you
> As an ex-PB programmer, you should recompile the PowerBuilder app.  It is probably using "datawindows" to abstract
theSQL away.  This way you can catch any incompatibilities and fix the datawindows 
 
> accordingly.  Obviously, you have to provide a different database driver as well.  What version of PB are you using?
>
Yes they do use datawindows, do not know version yet.
> Regards,
> Michael Vitale
>
>
> Achilleas Mantzios wrote on 1/13/2022 9:20 AM:
>> On 13/1/22 4:13 μ.μ., Julien Rouhaud wrote:
>>> On Thu, Jan 13, 2022 at 04:06:06PM +0200, Achilleas Mantzios wrote:
>>>> Dziekuje Alicija. The powerbuilder app is in-house developed, source is
>>>> available, what could be wrong with the app? Browsing through the
>>>> powerbuilder source I don't see (by eye) anything that could break something
>>>> in PgSQL.
>>> I would go with option A if possible.  If you want to get an idea of how
>>> complicated a migration would be, ora2pg does have a migration cost assessment
>>> report [1].  It can even check the queries if you have the audit trail enabled
>>> on your oracle database (if that existed in that version).
>>>
>>> [1] https://ora2pg.darold.net/documentation.html#Migration-cost-assessment
>> Such great info. Thank you so much. 21 years ago we started building our system on top of PostgreSQL, there have
beenchallenges, but we never regreted our initial decision (vs mysql) not even once.
 
>>
>>
>
>
>


-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Julien Rouhaud schrieb am 13.01.2022 um 15:13:
> I would go with option A if possible.  If you want to get an idea of how
> complicated a migration would be, ora2pg does have a migration cost assessment
> report [1].  It can even check the queries if you have the audit trail enabled
> on your oracle database (if that existed in that version).
>

I second this. Option A) is much cleaner. In my experience compatibility layers
that promise that "System A" behaves exactly like "System B" also introduce
a lot of additional problems. And if something goes wrong, you can never be sure
which part causes the problem.

On 13/1/22 4:51 μ.μ., Thomas Kellerer wrote:
> Julien Rouhaud schrieb am 13.01.2022 um 15:13:
>> I would go with option A if possible.  If you want to get an idea of how
>> complicated a migration would be, ora2pg does have a migration cost assessment
>> report [1].  It can even check the queries if you have the audit trail enabled
>> on your oracle database (if that existed in that version).
>>
> I second this. Option A) is much cleaner. In my experience compatibility layers
> that promise that "System A" behaves exactly like "System B" also introduce
> a lot of additional problems. And if something goes wrong, you can never be sure
> which part causes the problem.
Thanks, of course I agree with the last sentence.
>
>
>


-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

On 1/13/22 8:15 AM, Achilleas Mantzios wrote:
> On 13/1/22 4:12 μ.μ., Kenneth Marshall wrote:
>>>> Hi Achilleas,
>>>> I would go with version B - since as I understand it's ISV
>>>> solution. Meaning the postgres ddl was tested and aligned with the
>>>> application layer, otherwise you would need to fix app layer after
>>>> the migration.
>>> Dziekuje Alicija. The powerbuilder app is in-house developed, source
>>> is available, what could be wrong with the app? Browsing through the
>>> powerbuilder source I don't see (by eye) anything that could break
>>> something in PgSQL.
>>> -- 
>>> Achilleas Mantzios
>>> DBA, Analyst, IT Lead
>>> IT DEPT
>>> Dynacom Tankers Mgmt
>> Hi Achilleas,
>>
>> If the schema and usage is straight-forward, I would recommend using
>> ora2pg to give you an estimation cost for your migration by letting you
>> know what cannot be automatically translated. Once you have that you
>> will be better informed as to the work needed.
> Thank you Ken!

ora2pg worked perfectly for us in migrating 8TB of Oracle 11 data and simple 
triggers to Pg 12.

-- 
Angular momentum makes the world go 'round.

czw., 13 sty 2022 o 15:54 Achilleas Mantzios <achill@matrix.gatewaynet.com> napisał(a):

On 13/1/22 4:51 μ.μ., Thomas Kellerer wrote:
> Julien Rouhaud schrieb am 13.01.2022 um 15:13:
>> I would go with option A if possible.  If you want to get an idea of how
>> complicated a migration would be, ora2pg does have a migration cost assessment
>> report [1].  It can even check the queries if you have the audit trail enabled
>> on your oracle database (if that existed in that version).
>>
> I second this. Option A) is much cleaner. In my experience compatibility layers
> that promise that "System A" behaves exactly like "System B" also introduce
> a lot of additional problems. And if something goes wrong, you can never be sure
> which part causes the problem.
Thanks, of course I agree with the last sentence.
>
>
>


--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

just to be clear is it an ISV or in-house built system? Because I see two different answers ... 

in-house, and when not in house source is available, can you pls show me where I wrote a conflicting answer?

-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

On 13/1/22 5:06 μ.μ., Ron wrote:
> On 1/13/22 8:15 AM, Achilleas Mantzios wrote:
>> On 13/1/22 4:12 μ.μ., Kenneth Marshall wrote:
>>>>> Hi Achilleas,
>>>>> I would go with version B - since as I understand it's ISV
>>>>> solution. Meaning the postgres ddl was tested and aligned with the
>>>>> application layer, otherwise you would need to fix app layer after
>>>>> the migration.
>>>> Dziekuje Alicija. The powerbuilder app is in-house developed, source
>>>> is available, what could be wrong with the app? Browsing through the
>>>> powerbuilder source I don't see (by eye) anything that could break
>>>> something in PgSQL.
>>>> -- 
>>>> Achilleas Mantzios
>>>> DBA, Analyst, IT Lead
>>>> IT DEPT
>>>> Dynacom Tankers Mgmt
>>> Hi Achilleas,
>>>
>>> If the schema and usage is straight-forward, I would recommend using
>>> ora2pg to give you an estimation cost for your migration by letting you
>>> know what cannot be automatically translated. Once you have that you
>>> will be better informed as to the work needed.
>> Thank you Ken!
>
> ora2pg worked perfectly for us in migrating 8TB of Oracle 11 data and simple triggers to Pg 12.
wow! Thank you!


-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

On 13/1/22 5:02 μ.μ., Alicja Kucharczyk wrote:

czw., 13 sty 2022 o 15:54 Achilleas Mantzios <achill@matrix.gatewaynet.com> napisał(a):

On 13/1/22 4:51 μ.μ., Thomas Kellerer wrote:
> Julien Rouhaud schrieb am 13.01.2022 um 15:13:
>> I would go with option A if possible.  If you want to get an idea of how
>> complicated a migration would be, ora2pg does have a migration cost assessment
>> report [1].  It can even check the queries if you have the audit trail enabled
>> on your oracle database (if that existed in that version).
>>
> I second this. Option A) is much cleaner. In my experience compatibility layers
> that promise that "System A" behaves exactly like "System B" also introduce
> a lot of additional problems. And if something goes wrong, you can never be sure
> which part causes the problem.
Thanks, of course I agree with the last sentence.
>
>
>


--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

just to be clear is it an ISV or in-house built system? Because I see two different answers ... 

in-house, and when not in house source is available, can you pls show me where I wrote a conflicting answer?

to be more clear, they forked the initially ISV 20 years ago, so now there is effectively only in-house.

-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

czw., 13 sty 2022 o 17:04 Achilleas Mantzios <achill@matrix.gatewaynet.com> napisał(a):

On 13/1/22 5:02 μ.μ., Alicja Kucharczyk wrote:

czw., 13 sty 2022 o 15:54 Achilleas Mantzios <achill@matrix.gatewaynet.com> napisał(a):

On 13/1/22 4:51 μ.μ., Thomas Kellerer wrote:
> Julien Rouhaud schrieb am 13.01.2022 um 15:13:
>> I would go with option A if possible.  If you want to get an idea of how
>> complicated a migration would be, ora2pg does have a migration cost assessment
>> report [1].  It can even check the queries if you have the audit trail enabled
>> on your oracle database (if that existed in that version).
>>
> I second this. Option A) is much cleaner. In my experience compatibility layers
> that promise that "System A" behaves exactly like "System B" also introduce
> a lot of additional problems. And if something goes wrong, you can never be sure
> which part causes the problem.
Thanks, of course I agree with the last sentence.
>
>
>


--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

just to be clear is it an ISV or in-house built system? Because I see two different answers ... 

in-house, and when not in house source is available, can you pls show me where I wrote a conflicting answer?

ok, then sorry for the confusion. I interpreted the answer from Michael Vitale asking for the version etc. as it would be an ISV which has not matched with your statement. So in that case I also do vote for A :)

PowerBuilder was (is?) a development environment/solution which was blooming back in the client-server era. The language is weird to the eyes of a java/C/etc person.

Achilleas Mantzios wrote on 1/13/2022 1:07 PM:
> PowerBuilder was (is?) a development environment/solution which was 
> blooming back in the client-server era. The language is weird to the 
> eyes of a java/C/etc person.

So what's the PB version? v6, v7, v8, v9, 10?, 11.5?

I assume you still have the PG IDE that comes with it, right?

I still have a working copy of 11.5 that I use for some windows GUI tools.

Regards,
Michael Vitale

Στις 13/1/22 8:17 μ.μ., ο/η MichaelDBA έγραψε:
> Achilleas Mantzios wrote on 1/13/2022 1:07 PM:
>> PowerBuilder was (is?) a development environment/solution which was 
>> blooming back in the client-server era. The language is weird to the 
>> eyes of a java/C/etc person.
>
> So what's the PB version? v6, v7, v8, v9, 10?, 11.5?
No access yet to anything, I asked, still waiting for answer.
>
> I assume you still have the PG IDE that comes with it, right?
>
> I still have a working copy of 11.5 that I use for some windows GUI 
> tools.
>
Good to know!
> Regards,
> Michael Vitale
>
>
>

Στις 13/1/22 8:17 μ.μ., ο/η MichaelDBA έγραψε:
> Achilleas Mantzios wrote on 1/13/2022 1:07 PM:
>> PowerBuilder was (is?) a development environment/solution which was 
>> blooming back in the client-server era. The language is weird to the 
>> eyes of a java/C/etc person.
>
> So what's the PB version? v6, v7, v8, v9, 10?, 11.5?
10.2.1 (Build 9004)
>
> I assume you still have the PG IDE that comes with it, right?
>
yep
> I still have a working copy of 11.5 that I use for some windows GUI 
> tools.
>
> Regards,
> Michael Vitale
>
>
>