The very basic elements of this environment are two database tables. The complication is that one is a DB2 table on a Mainframe, and the other is Oracle table. If they were both Oracle tables, we could simply do a join to get to our target rows and columns. Due to company policy, we cannot simply extract and hold in another repository the data. Previous publishes use to complete in 8 minutes, but now it is taking 40 minutes. Why?
How can we determine where the bottleneck is in this extract? How can we optimize this so we can reduce our time to delivery?
DB2 OracleTDM 4.2
The Best Possible Solution would be to create a join between the DB2 data and the Oracle table data. However, due to the disparity between the two databases, this is not possible.
The Best Practice Solution requires copying over the DB2 data into a reusable Oracle table, performing a join between the two Oracle tables, and then generating the required output. This solution by-passes the constant SQL queries that are performed against the DB2 and Oracle databases.
However, the Best Practice Solution may not be possible due to company database and security policies.
Therefore, concentrating on performance will expose the Root Cause for the performance issue which could be one or more of the following:
The first step in discovering Root Cause is breaking down the SQL into smaller pieces and then build the SQL back up again to the point of failure.
Based on your data, you should be able to resolve the issue. In the case that generated this Knowledge Document, the query returned all the table contents.