Oracle to DB2 z/OS conversion

Aurora Emanuela Dellanno

Oracle to DB2 z/OS conversion

Hey y'all,

 

firstly, yes I'm still writing DB2 because we're on version 11 so it's alright.

 

Secondly, we have a customer who are porting and therefore converting their application from Oracle to big DB2, and we are trying to work out the caveats, the main worry for us being NULL values in different CHAR columns.

 

These are the things I have thought about:

 

  • are these values only in CHAR or also in numeric columns
  • are these nullable columns ever used in indices
  • do they use either of these functions in their applications: NVL/DECODE.

 

Any other things I should ask them, that you can think of?

 

Thanks.

 

Aurora

Joe Geller

RE: Oracle to DB2 z/OS conversion
(in response to Aurora Emanuela Dellanno)

I think the big thing to watch out for are empty Varchars.  An empty string in Oracle is a NULL (whereas in DB2 it is a string of length 0).  So of course that affects string comparisons since DB2 will pad unequal length strings with blanks.

Date semantics can be different too, especially since the date datatype in Oracle is actually a datetime.

A smaller item (but important if used) is that the parameters to the Translate function are reversed from DB2.

From a syntax point of view the biggest things are:

- sysdate is frequently used (equivalent to current date - well except that it is a date/time not just a date)

- dual is used instead of sysibm.sysdummy1 (i.e. select .... from dual)

Oracle doesn't have clustering the way DB2 does, so if you just convert the Index DDL you will end up with no clustering index defined.  Which of course may be better for some tables, but may not be.

Joe

In Reply to Aurora Emanuela Dellanno:

Hey y'all,

 

firstly, yes I'm still writing DB2 because we're on version 11 so it's alright.

 

Secondly, we have a customer who are porting and therefore converting their application from Oracle to big DB2, and we are trying to work out the caveats, the main worry for us being NULL values in different CHAR columns.

 

These are the things I have thought about:

 

  • are these values only in CHAR or also in numeric columns
  • are these nullable columns ever used in indices
  • do they use either of these functions in their applications: NVL/DECODE.

 

Any other things I should ask them, that you can think of?

 

Thanks.

 

Aurora

Michael Hannan

RE: Oracle to DB2 z/OS conversion
(in response to Joe Geller)


In Reply to Joe Geller:

Oracle doesn't have clustering the way DB2 does, so if you just convert the Index DDL you will end up with no clustering index defined.  Which of course may be better for some tables, but may not be.

Joe

In what way would lack of a cluster in DB2 be good? I generally saw lack of clustering as one of the biggest weaknesses in Oracle. Db2 systems designed to utilize clustering well might not be able to perform well in Oracle non clustered. When I find a customer with no cluster on a table, I always recommend one for savings if there is any significant cost on the table.

Having a good cluster can save the need for "index Only" access with excessive columns in that index and possibly excessive levels as well. Overblown indexes is a classic symptom of bad or no clustering.

Given that DB2 is capable of clustering data, seems to be little reason not to do so according to which data table accesses will benefit most from it. Chaotic data row sequence can hardly be better. We do have TABLE APPEND to avoid following the Cluster at Insert time (but still allow a Reorg to improve sequence), and various other features to avoid Insert hotpot contention such as row level locking, Member Cluster, and finally Insert Algorithm 2.

Never saw any case to benefit from removing the table cluster index, other than changing to a better one, so that is why I ask what is in your mind. Random/Chaotic sequence is always poor in my personal view.

So I would think conversion to DB2 allows the opportunity to choose the best cluster index to improve the performance of dominant cost queries, that are not "index only", or reducing columns in a highly clustered index that does not need to be Index Only any longer, to remove heavily updated columns or long columns with no filtering.

Without this good choice of clustering, DB2 could perform worse than it should and maybe similar to Oracle.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Phil Grainger

Oracle to DB2 z/OS conversion
(in response to Michael Hannan)
If data access is TOTALLY random (ie NO sequential access at all) then why worry about clustering (except, of course, Db2 will choose a clustering index even if we don’t)

And there IS a cost in attempting to maintain clustering – perhaps for no advantage

Reminds me of something I only recently learned about how Amazon stocks its warehouses - http://www.systemid.com/learn/why-chaotic-storage-is-perhaps-the-best/
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]





From: Michael Hannan [mailto:[login to unmask email]
Sent: 07 December 2018 11:25
To: [login to unmask email]
Subject: [DB2-L] - RE: Oracle to DB2 z/OS conversion


In Reply to Joe Geller:

Oracle doesn't have clustering the way DB2 does, so if you just convert the Index DDL you will end up with no clustering index defined. Which of course may be better for some tables, but may not be.

Joe

In what way would lack of a cluster in DB2 be good? I generally saw lack of clustering as one of the biggest weaknesses in Oracle. Db2 systems designed to utilize clustering well might not be able to perform well in Oracle non clustered. When I find a customer with no cluster on a table, I always recommend one for savings if there is any significant cost on the table.

Having a good cluster can save the need for "index Only" access with excessive columns in that index and possibly excessive levels as well. Overblown indexes is a classic symptom of bad or no clustering.

Given that DB2 is capable of clustering data, seems to be little reason not to do so according to which data table accesses will benefit most from it. Chaotic data row sequence can hardly be better. We do have TABLE APPEND to avoid following the Cluster at Insert time (but still allow a Reorg to improve sequence), and various other features to avoid Insert hotpot contention such as row level locking, Member Cluster, and finally Insert Algorithm 2.

Never saw any case to benefit from removing the table cluster index, other than changing to a better one, so that is why I ask what is in your mind. Random/Chaotic sequence is always poor in my personal view.

So I would think conversion to DB2 allows the opportunity to choose the best cluster index to improve the performance of dominant cost queries, that are not "index only", or reducing columns in a highly clustered index that does not need to be Index Only any longer, to remove heavily updated columns or long columns with no filtering.

Without this good choice of clustering, DB2 could perform worse than it should and maybe similar to Oracle.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image003.png (9.3k)

Bill Gallagher

Oracle to DB2 z/OS conversion
(in response to Phil Grainger)
That’s a fascinating article about chaotic storage.

And I agree with your comment about clustering indexes . . . if there is no defined or identified processing need for sequential access, then I see no advantage of specifically defining a clustering index. Particularly if it causes you to perform unnecessary REORGs just to maintain a high cluster ratio which, in the end, provides little to no benefit.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: Grainger, Phil <[login to unmask email]>
Sent: Friday, December 7, 2018 7:25 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Oracle to DB2 z/OS conversion

If data access is TOTALLY random (ie NO sequential access at all) then why worry about clustering (except, of course, Db2 will choose a clustering index even if we don’t)

And there IS a cost in attempting to maintain clustering – perhaps for no advantage

Reminds me of something I only recently learned about how Amazon stocks its warehouses - http://www.systemid.com/learn/why-chaotic-storage-is-perhaps-the-best/
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----


________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Aurora Emanuela Dellanno

RE: Oracle to DB2 z/OS conversion
(in response to Aurora Emanuela Dellanno)

thanks, guys.

 

keep it coming please!

 

incidentally, this application makes mass deletes and then a huge number of SQL inserts so I suppose the chaotic storage thing is particularly apt.

Philip Sevetson

Oracle to DB2 z/OS conversion
(in response to Aurora Emanuela Dellanno)
Aurora,

In the high-volatility scenario which you describe, the fix (in DB2 on Z) used to be “unload only what you want to keep; reload that; load-resume the new/additional data”, for faster performance and probably better space management. Has it been considered, is it feasible to consider?

Also, if this is a high-availability app (again on Z), you would want to use a Clone Table as target of the loadreplace/loadresume, and then EXCHANGE DATA at the end of the process.

--Phil Sevetson

From: Aurora Emanuela Dellanno [mailto:[login to unmask email]
Sent: Friday, December 07, 2018 9:15 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Oracle to DB2 z/OS conversion


thanks, guys.



keep it coming please!



incidentally, this application makes mass deletes and then a huge number of SQL inserts so I suppose the chaotic storage thing is particularly apt.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Tony Andrews

Oracle to DB2 z/OS conversion
(in response to Aurora Emanuela Dellanno)
Aurora, some initial thoughts from the application side. I am not an Oracle expert by far
but know some of the obvious differences from an SQL development side.

Dates in Oracle are stores as both date and time, however has a TIMESTAMP datatype
Oracle also has an INTERVAL datatypes that store date or time intervals
Oracle can specify NULL in the create DDL, Db2 cannot
Default Columns in DDL, Oracle uses DEFAULT, Db2 uses WITH DEFAULT
Oracle uses SYSDATE, Db2 uses CURRENT DATE
Oracle has a number of functions not available in Db2 (TO_CHAR, TO_NUMBER)
Subtracting 1 date from another is different in Oracle vs Db2
Numerically, Oracle only has NUMBER and BINARY FLOAT datatypes
Character, Oracle uses VARCHAR2 (VARCHAR is synonymous though)
Character Double Byte, Oracle uses NCHAR
Recursion SQL is much different syntax in Oracle (and much simpler)
The MERGE statement is a little more limited in Oracle and may use the DUAL table
Oracle uses the DUAL table like Db2 uses the SYSDUMMY1 table
Oracle has materializes Views, Db2 has materialized Tables
Watch out for SQL mathematics. 2 whole numbers in division will return only the whole
number in Db2, but Oracle also returns decimals

Tony Andrews


From: Aurora Emanuela Dellanno [mailto:[login to unmask email]
Sent: Thursday, December 06, 2018 8:33 AM
To: [login to unmask email]
Subject: [DB2-L] - Oracle to DB2 z/OS conversion


Hey y'all,



firstly, yes I'm still writing DB2 because we're on version 11 so it's alright.



Secondly, we have a customer who are porting and therefore converting their application from Oracle to big DB2, and we are trying to work out the caveats, the main worry for us being NULL values in different CHAR columns.



These are the things I have thought about:



* are these values only in CHAR or also in numeric columns
* are these nullable columns ever used in indices
* do they use either of these functions in their applications: NVL/DECODE.



Any other things I should ask them, that you can think of?



Thanks.



Aurora

-----End Original Message-----

Joe Geller

RE: Oracle to DB2 z/OS conversion
(in response to Phil Grainger)

I agree with both Micahel and Phil G.  Clustering is very good but there some exceptions where it may not provide benefit and has iverhead to maintain. Of course a clustering index is only good if you have chosen the right columns to cluster by.  I have seen many designs with generated keys for the child tables and those tables may not even have columns for the full hierarchy. For example customer, order, item with the item table not having customer number   

Since Oracle doesn’t have our clustering this design is more common

 

. Joe

In Reply to Phil Grainger:

If data access is TOTALLY random (ie NO sequential access at all) then why worry about clustering (except, of course, Db2 will choose a clustering index even if we don’t)

And there IS a cost in attempting to maintain clustering – perhaps for no advantage

Reminds me of something I only recently learned about how Amazon stocks its warehouses - http://www.systemid.com/learn/why-chaotic-storage-is-perhaps-the-best/
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]





From: Michael Hannan [mailto:[login to unmask email]
Sent: 07 December 2018 11:25
To: [login to unmask email]
Subject: [DB2-L] - RE: Oracle to DB2 z/OS conversion


In Reply to Joe Geller:

Oracle doesn't have clustering the way DB2 does, so if you just convert the Index DDL you will end up with no clustering index defined. Which of course may be better for some tables, but may not be.

Joe

In what way would lack of a cluster in DB2 be good? I generally saw lack of clustering as one of the biggest weaknesses in Oracle. Db2 systems designed to utilize clustering well might not be able to perform well in Oracle non clustered. When I find a customer with no cluster on a table, I always recommend one for savings if there is any significant cost on the table.

Having a good cluster can save the need for "index Only" access with excessive columns in that index and possibly excessive levels as well. Overblown indexes is a classic symptom of bad or no clustering.

Given that DB2 is capable of clustering data, seems to be little reason not to do so according to which data table accesses will benefit most from it. Chaotic data row sequence can hardly be better. We do have TABLE APPEND to avoid following the Cluster at Insert time (but still allow a Reorg to improve sequence), and various other features to avoid Insert hotpot contention such as row level locking, Member Cluster, and finally Insert Algorithm 2.

Never saw any case to benefit from removing the table cluster index, other than changing to a better one, so that is why I ask what is in your mind. Random/Chaotic sequence is always poor in my personal view.

So I would think conversion to DB2 allows the opportunity to choose the best cluster index to improve the performance of dominant cost queries, that are not "index only", or reducing columns in a highly clustered index that does not need to be Index Only any longer, to remove heavily updated columns or long columns with no filtering.

Without this good choice of clustering, DB2 could perform worse than it should and maybe similar to Oracle.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Michael Hannan

RE: Oracle to DB2 z/OS conversion
(in response to Joe Geller)



In Reply to Joe Geller:

I agree with both Micahel and Phil G.  Clustering is very good but there some exceptions where it may not provide benefit and has iverhead to maintain. Of course a clustering index is only good if you have chosen the right columns to cluster by.  I have seen many designs with generated keys for the child tables and those tables may not even have columns for the full hierarchy. For example customer, order, item with the item table not having customer number   

Since Oracle doesn’t have our clustering this design is more common 

Yes indeed, databases designed for random and without good co-correlation of join keys in the hierarchy, cannot benefit so much from DB2 clustering meaning that the joins in DB2 or a batch process to process all Customers in sequence (Multiple tables) might perform as badly as the Oracle ones. When the reverse happens, i.e. a DB2 design for good cluster performance, is ported to Oracle (without cluster), the performance could go downhill badly.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Peter Vanroose

RE: Oracle to DB2 z/OS conversion
(in response to Tony Andrews)

<Sorry, hit the ENTER too soon...>

Edited By:
Peter Vanroose[Organization Members] @ Dec 09, 2018 - 08:28 PM (Europe/Brussels)

Peter Vanroose

Re: Oracle to DB2 z/OS conversion
(in response to Tony Andrews)

Tony,

Apparent differences between Db2 & Oracle can be smaller than they seem:

 

  • both Oracle and Db2 understand CURRENT_DATE and CURRENT_TIMESTAMP (note the underscore!)
    (but Oracle does indeed not have the Db2 "TIME" datatype, and DATE actually is kind-of DATE +TIME + microseconds)
  • Oracle understands VARCHAR(n), CHAR(n), INTEGER, SMALLINT, DECIMAL(p,s), CLOB, BLOB (the latter two without size argument). The three numeric ones internally collapse into a single NUMERIC type, though.
    This is useful when going from Db2 to Oracle, but when going from Oracle to Db2, "NUMBER" without arguments will have to be replaced by DECIMAL(38,s) for some appropriate s (or by INT or BIGINT, if you are really sure).
  • Both Oracle and Db2 understand adding/subtracting integers to/from dates as adding/subtracting days. (The syntax is only slightly different: no "DAYS" keyword in Oracle.)
  • The "infamous" Oracle functions TO_CHAR, TO_NUMBER and TO_DATE exist now actually also in Db2! (Try it out -- both on LUW & z/OS !)
  • Oracle has since long adopted the Db2 (i.e.: ISO standard) syntax for recursive SQL. B.t.w.: even "FETCH FIRST n ROWS ONLY" is now understood by Oracle (that is: only since its version 12c).

In Reply to Tony Andrews:

Dates in Oracle are stored as both date and time, however has a TIMESTAMP datatype
Oracle also has an INTERVAL datatypes that store date or time intervals
Oracle can specify NULL in the create DDL, Db2 cannot
Default Columns in DDL, Oracle uses DEFAULT, Db2 uses WITH DEFAULT
Oracle uses SYSDATE, Db2 uses CURRENT DATE
Oracle has a number of functions not available in Db2 (TO_CHAR, TO_NUMBER)
Subtracting 1 date from another is different in Oracle vs Db2
Numerically, Oracle only has NUMBER and BINARY FLOAT datatypes
Character, Oracle uses VARCHAR2 (VARCHAR is synonymous though)
Character Double Byte, Oracle uses NCHAR
Recursion SQL is much different syntax in Oracle (and much simpler)
The MERGE statement is a little more limited in Oracle and may use the DUAL table
Oracle uses the DUAL table like Db2 uses the SYSDUMMY1 table
Oracle has materializes Views, Db2 has materialized Tables
Watch out for SQL mathematics. 2 whole numbers in division will return only the whole
number in Db2, but Oracle also returns decimals

Tony Andrews

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Edited By:
Peter Vanroose[Organization Members] @ Dec 11, 2018 - 08:53 PM (Europe/Brussels)

Aurora Emanuela Dellanno

RE: Oracle to DB2 z/OS conversion
(in response to Aurora Emanuela Dellanno)

once more, thanks everyone. 

 

As you may have noticed, I have kept it vague since I will discuss all these items with the application team - I am afraid we don't have enough detail yet but I thought I would get the hive brain working before the project starts in earnest.

 

Any more suggestions will be gratefully accepted, in case I don't "speak" to y'all before my holidays next week, happy holidays to one and all, happy new year and that.