Thursday, October 6, 2011

Generated columns UP_* for case insensitive search in V7


You might not find anything while searching about this info center. At least not when I did the search.

In V7 data migration generates some new generated fields and these fields are tablename.UP_*  columns. These fields are used by out of the box case insensitive searches. The grouping functions such as to_lower or to_upper are expensive operations for search when dealing with vast data and that is one of the performance enhancements in V7.

I am pretty sure, if you use out of the box data migration tool for migrating to V7. It should take care of  these generated fields. These fields should not be populated from code, there are triggers in database that would take care of populating these fields.

e.g.
  "UP_MFNAME" VARCHAR(64) GENERATED ALWAYS AS (UPPER(MFNAME)) ,
  "UP_MFPARTNUMBER" VARCHAR(64) GENERATED ALWAYS AS (UPPER(MFPARTNUMBER))
  "UP_PARTNUMBER" VARCHAR(64) GENERATED ALWAYS AS (UPPER(PARTNUMBER)) )

References:
https://www-304.ibm.com/support/docview.wss?uid=swg21498502
http://www.ibm.com/developerworks/data/library/techarticle/0203adamache/0203adamache.html
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0020109.html

1 comment:

  1. I wish these UP_ columns were included in the V6 to V7 schema changes documentation.

    ReplyDelete