We are going to have to do a look up based on the last 4 digits of a users social security number. The social is stored in a db with about 4 million rows where the social is integer. This obviously presents indexed read problems. Has anyone else dealt with this, are there options other than creating another field to just store the last 4 digits? If it does have to be another field, is there a way to have it auto-updated, set, etc. in the table?


thanks

Without knowing what DB2 version or OS you have, I will take a stab at this. There really is no way to do it other than a separate column. You can have this auto-generated by adding the column like this:

ALTER mytable add column LAST4SSN integer generated always as (MOD(ssn,10000));


HTH

Andy

In order to use the b-tree of the index, you will need a separate column. Otherwise DB2 will have to read the entire index to find the matching rows.

The problem with the MOD function to create the column for you is that either:

1. SSN may be character, or
2. If defined as decimal, bigint, real, etc, I don't believe that the resulting column will be smallint (which is what you want).

Another option is add the column with the last 4 numbers onto an existing index that will be used in the search criteria (since obviously the last 4 digit of SSN are not unique). So if the query says something like:

select * from user_table
where user_id = :user_id and last4_ssn = :last4_ssn

Then you could have an index on the following columns:

create unique index index1 on user_table
(user_id)
include (last4_ssn)

This would add the column last4_ssn onto the unique index without enforcing uniqueness on the last4_ssn column. I am not 100% sure how declared RI would like such an index to satisfy its need for an index on the primary key.

Obviously, you could also add last4_ssn column to another index that was used in your application if suitable. If the index was not unique before-hand, then you could just add the column to the index without the "include" clause.

Please disregard my comments about MOD. Just define the column as smallint and there should be no problem. Sorry.