Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    76

    Unanswered: modeling question

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    Last edited by Marcus_A; 10-22-03 at 18:21.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please disregard my comments about MOD. Just define the column as smallint and there should be no problem. Sorry.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •