Using Runtime-Collations To Mimique Different Target Orders

Here is a nice conclusion of oracles language & collation settings/capabilities.

Especially the usage of the

NLSSORT

function in conjunction with an

order by

statement can help you to present result sets in exactly that order that, e.g., a target system with a specific internationalization setting will interpret or need the data:

select * from source order by NLSSORT(sort_column,'NLS_FORMAT=xxx')

Useful format parameters are, e.g., binary or ascii7.

Date Type Conversion When Federating Oracle into DB/2

Federation (or DB-Link depending on the target DWH platform) is a most convenient way to connect your staging area to your data sources.

There is the golden rule not to stretch the tempting, modern capabilities of that feature to much, hence to first replicate, then transform the data.

There are nevertheless a few issues left that may still hurt you mostly around the builtin data type conversions, especially when formulating the mostly temporally constrained replication conditions.

So, DB2 9.5 represents oracle´s DATE as a TIMESTAMP - hence be sure to use CURRENT_TIMESTAMP instead of CURRENT_DATE!