Efficient Provision of Multiple Versions of Data

Question

We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred different research projects, and each project needs its own version of the data, with certain columns of the data encrypted to a project-specific value, as well as other restrictions (like only providing certain rows and/or columns that a project has permission to us).

Here is a simplified example of some of the data:

Hospital_spell

persion_id   spell_id  hospital_id   admission_dt   discharge_dt   
1234         9023      12            2012-09-04     2012-09-05
1234         1111      12            2014-01-01     2014-01-01
9876         5432      8             1999-04-27     2000-02-29

Hospital_diagnosis

spell_id  hospital_id  diag_num diag_code
9023      12           1        A12
9023      12           2        C45

These two tables link on spell_id and hospital_id. For each project that uses this data, we need to provide a version of the data with person_id, spell_id, and hospital_id encrypted to new values (this restriction limits unauthorised combination of data from different projects). Also, a given project might only get data from certain years or for certain people (however many projects get the entire dataset).

How can we provide these multiple versions of the data efficiently, in terms of performance and storage? Ideally this would mean not copying all of the data for each project, and having only a limited performance hit for each project's use of the data (30% slower than querying the original tables would be fine, but not 300% slower).

Options we have considered:

• Making a copy of the data for each project. This results in no performance hit, but when each project has potentially billions of rows, it takes up a lot of storage space with mostly duplicated data.
• A view of the original table that encrypts on the fly with a scalar function. Uses the least storage space, but performance is not acceptable, particularly when the encrypted columns are often used for joins (as above).
• A view of the original table that joins to an additional table with precomputed encrypted values. This has been our preferred option, but it turns out to also perform quite poorly. Queries against these views are many times slower than against the original table. We have worked on optimising indexing, but it hasn't solved this.

Example of the third option:

Additional table:

person_id_encrypted

person_id   person_id_e
1234        1A30495810293D0293CB2093A3FED2B

View:

create view hospital_spell_encrypted as (
    select person_id_e,
           spell_id_e,
           hospital_id_e,
           admission_dt,
           discharge_dt
      from hospital_spell
      join person_id_encrypted on 
         hospital_spell.person_id = person_id_encrypted.person_id
      join spell_id_encrypted on 
         hospital_spell.spell_id = spell_id_encrypted.spell_id
      join hospital_id_encrypted on 
         hospital_spell.hospital_id = hospital_id_encrypted.hospital_id
) 

Does anyone have any thoughts on how we can get better performance? So far, just copying the data for each project looks like the only workable solution.

We are using DB2 9.7 LUW, running on an IBM Blue C Supercomputer with multiple parallel servers.