Allow join of base temporal table to archive enabled tables

Temporal and archive enabled tables both provide valuable features depending on the use case for the data. We've implemented both types of tables in our Production environment and had initially implemented temporal tables for one of our most critical VSAM files that have converted to DB2. Our other major DB2 table that we converted years ago to hold a log of financial transactions made more sense putting it in an archive-enabled table structure and has proved invaluable. We have a need to join these tables together but found out that we cannot join an archive enabled table to a "base" temporal table (not the temporal history table). Because of this, we had to back out from making that critical table temporal to allow us to do joins. Having temporal history allows us to see how a table record (in our case a member account) has changed over a period of time providing us invaluable data to identify who changed what. Here are a few use cases for allowing joins between these tables in a limited fashion.

Scenario 1: Join base temporal table to base table in archive-enabled relationship

Scenario 2: Join base temporal table to archive table in archive-enabled relationship

Scenario 3: Join base temporal table to base/archive table in archive enabled relationship

Example:

select col1, col2

from schema.base_temporal_table btemp inner join schema.base_table_transactions btrans

where ...

union all

select col1, col2

from schema.base_temporal_table btemp inner join schema.arch_table_transactions atrans

where ...

--The above would be one way to get data from both base/arch transaction tables without having to deal with the get_archive variable

These scenarios I believe would satisfy the vast majority of requests we would get for joining the data. What could possibly help to avoid involving the temporal history tables is that querying those tables you would use the FOR SYSTEM_TIME/BUSINESS_TIME AS OF clause. So if the underlying queries didn't have that clause, maybe that could help the parser narrow it down to a valid join scenario.