our etl-tool (warehouse workbench) doesn't satisfy our requirements, therefore we try to use db2 udf instead of it. one of our requirements is to use memory-lookups, which means we need to transform a code (for example 'ABC') to an ID (for example 123). we have to do this for about 300 table-columns and about 10 mio records each month. therefore it would be useful if we only have to read the db2-transformation-table (for example transform 'ABC' to 123 for database column xyz) only once at the beginning. the transformation-table should then be kept in memory ... i think a scratchpad could be the right solution.

does anybody use such a UDF or does anybody know where i can get an example?

thanks in advance