Definition of UNK and 'Database has too many unique field names' message
The error message, "Database has too many unique field names" appears when you are working in IBM Lotus Notes Client. What does this error message mean in detail, and what can you do to minimize its occurrence?
This error message may be embedded in a longer error message, for example: "Cannot Store Document; Database has too many unique field names. Please ask your administrator to compact the database."
This specific message is an indication that the limit for the UNK Table has been reached. UNK derives its name from UNique Key Table. The UNK table is an internal table which stores all of the unique fields in a Notes Database. The UNK table has a limit of 64k. This limit still exists in all releases of 4.x up to and including 4.6x. This limit is different than other 64k or 65,000 byte error messages you may receive in Notes. If the UNK limit is being reached, you should receive a specific error regarding "Database has too many unique field names".
Note: During processing the size of the table grows due to overhead. This can mean that even if a table size has been analyzed and indicates a size less than 64k that during processing it could reach the limit.
The UNK Table is derived from a list of the unique field names in a database. This list contains the field names, data type and other related information. Because the length of field names vary, it is not possible to pin down an exact number of fields that a database can contain before maxing out the UNK limit. On average, most databases can store around *3,000 field names before hitting the limit.
Due to the way the UNK table is built, if your database has fewer field names, or shorter field names, the UNK table will be smaller.
Aside from removing whitespace from a database, compacting a database deletes and rebuilds the UNK Table (as long as the database is not full text indexed or flagged for full text indexing). So, if you have shortened or deleted fields in your database, you should run Compact to rebuild the UNK Table. If a field exists in any document or form, it will be included in the UNK Table. So if you are deleting obsolete fields from Forms, you should also run an agent to delete the field from any documents in the database.
What To Do Once UNK Limit Is Reached
- Delete Obsolete Fields - If you have field names that are no longer needed, delete them from all Forms and documents in the database. For example, to delete an obsolete field called LastYear from existing documents, your agent would be "FIELD LastYear := @DeleteField".
- Use Shared Fields or Same Name Fields - In situations where different fields are being used on multiple forms to accomplish the same task, using one shared field would limit the number of unique fields, thereby bringing the UNK size down. For example, if you had a "From" field on one form and an "Author" field on the other, but they both calculated @Username, it would be best to use one shared field. Alternately, you could use the same (exact) field name and data type on multiple forms to limit the UNK. So in the prior example, instead of using "From" and "Author", you would use "From" on both forms to calculate @Username.
- Shorten Field Names - Since the UNK uses the actual field lengths of every field in your database, reducing the number of characters for a field, will limit the size of the UNK. For example, instead of using "FiscalYearGrowthAllocationForecast", use "FYGAFrcast".
- Split Up the Design into Multiple Dbs - If one database uses so many field names that it hits the UNK limit, it may be time to split up the design into multiple databases. It would be best to split the design based on functionality to lower the total number of fields in a particular database. For example, if you have documents for Inventory and documents for Orders, your inventory documents may have fields for "NumberAvailable", "WareHouseLocation", "Supplier", whereas your order form may have "CustomerName", "ShipTo", etc.. By splitting up these documents, and the forms related to them, into separate databases, you will reduce the overall field names contained in one database, thereby reducing the UNK Table size.
- Re-add and Delete Field - If an obsolete field is no longer in use on the form, but is listed under design properties of the form, it will be included in the UNK. To try to delete it, edit the form, re-add the field with the same spelling, and save the form. Now delete the field by placing your cursor to the right of the field and pressing the BACKSPACE key. Notes will prompt you to delete the field. Select Yes. If this does not work, you may need to create a new form by copy/pasting fields from the old form to a new form, and then delete the old form.
- COMPACT the Database with no Full Text Index Built - Compacting the database rebuilds the UNK Table. However, if a database is full text indexed, the UNK is preserved. Prior to deleting the UNK, we scan the database header to see if the full text index bit is set. This can be checked by opening the database with NotesPeek, looking at the "Database Information" and checking to see if the "Options" on the right contains the text, "FTIndex". If it does, then the original UNK will be preserved. If the database is full text indexed, go into Database Properties (File, Database, Properties), delete the full text index (select the Full Text tab and select Delete Index button), then compact the database. After compaction, the full text index can be recreated.
If under Database Properties, it does not appear that the database is full text indexed, but the FTIndex flag is visible through NotesPeek, create a full text index and then delete the index. This will turn off the FTIndex flag, and successfully compress the UNK, during Compact, to only fields present in the database. The compact process deletes the UNK, and scans all notes in the database for the existence of unique fields. If the field exists anywhere (design element or live document), it will be added to the newly built UNK. You should compact from the beginning to ensure your UNK Table is a representation of all the current fields being used. You should compact the database after performing any of the previous steps of deleting obsolete fields, using shared fields, shortening names, splitting up design, etc..
- Copy-style compaction must be used in order to rebuild the UNK table. In Notes R4 copy-style compaction is the default on the Client and the Server. In Notes R5 and Notes 6 copy-style compaction must be specified from a command prompt.
From the Domino Server console type:
lo compact database.nsf -c.
From a Notes Client (example is for Windows operating systems) execute from the Notes directory: ncompact \dataparth\database.nsf -c
- If running Compact against the problem database generates the "Max number for fields..." error, first make a new replica of the database before running Compact against it.
- In certain cases, unused field names will remain in the design document; hence, COMPACT will not remove them from the UNK table. This happens when the deleted field is of type Number or Time. Quality Engineering is aware of this issue. (SPR# RMAS3ZMK5B).
- An enhancement request for the removal of the Full-Text Index limitation has been submitted to Quality Engineering (SPR# RMAS42JVZ9).
- There is a utility available from IBM Lotus Support which shows the UNK table of a database; this utility will be sent out on an as needed basis. Even if the table size returned by the utility indicates a value below 64k, overhead during processing of the table adds to its size and can cause the noted error. For example, if a table size is indicated as 60k it could still surpass the 64k once the overhead is added. There are no specifics on the additional size that overhead will add.
- There is a utility available which shows the UNK table of a database; please refer to the online resource document, "Tool to Show the UNK Table Size for a Notes Database" (#4004373) for information on this utility. Even if the table size returned by the utility indicates a value below 64k, overhead during processing of the table adds to its size and can cause the noted error. For example, if a table size is indicated as 60k it could still surpass the 64k once the overhead is added. There are no specifics on the additional size that overhead will add. Please note that the utility is provided "as is", unsupported; any problems with the tool should not be reported back to Lotus since it is unsupported.
* In IBM Lotus Support, some analysis was done on customer applications, and it was found that with an average field length of 11, the UNK could store 3,200 fields. A conservative gauge would be an average field length of 10, and 3,000 fields, should bring you well within the UNK limit.
The UNK Table is used at a core level of Notes. Increasing this limit requires major revisions to the product. The customer issues surrounding the UNK limit were reported to Lotus Software Development and Quality Engineering (SPR# RSAR39ELHZ), and have been addressed in Notes 5.0 Client and Domino 5.0 Designer, with some caveats.
In R4, the UNK table size is directly affected by the length of individual field names. This is conservatively estimated at 3,000 fields averaging 10 characters in size. In R5, this limit has been increased to 22,893 fields (approximately 7 times the number of entries available in R4). There is an option available in R5 that allows additional fields to exist in an R5 database. However, there are some caveats. First we will cover how to enable this option, and then discuss caveats.
Note: The R5 documentation incorrectly notes the limit at 64,000 fields rather than 22,893. This has been corrected in the documentation in Notes/Domino 6.0 and 6.5.
To Enable :
There is now an option on the Advanced tab in the Database InfoBox called "Allow more fields in database". Selecting this option enables functionality called Large UNK Tables. In order to take advantage of Large UNK Tables, the database must be converted to the R5 format by compacting it on an R5 Server, or compacting it locally on an R5 Client. Once it is in the R5 format, then it will have the same access limitation that existed between R3 and R4. The limitation is, R4 clients will be able to access the database only on a R5 server and not locally unless a new replica is pulled down. This is because R4 clients cannot understand the R5 database format unless accessing it through a R5 server. Attempting to add fields to the database without compacting the database to convert it into the R5 format, will still result in errors regarding the 64k UNK limit. So, in summary, the 64k UNK limit is removed in the new R5 database format.
Not all areas of R5 have been coded to support the Large UNK Tables. If you turn on the "Allow more fields in database" property, once your UNK Table gets large enough and starts using the Large UNK Tables, then areas of the product that cannot handle large UNKs will not work. Currently this includes the following:
- Full Text Indexing query by field
- Query by Form
The above issues have been fixed respectively in Notes/Domino release 5.0.2 (fix for SPR LJAS4BSNZP) and release 5.0.3 (fix for SPR# LMEF4CTS6U).
Databases created with an .NTF extension in Notes R5 are created with an ODS 20 by default, and will not support the "Allow More Fields in Database" advanced property. The ODS 20 is used as a space saving measure because the format is not as robust as later ODS formats. In order to work around this, you can create a new replica of the template using the NSF extension which will have an ODS of 41. This new replica can still be used as a template based on the "Database Is a Template" setting on the Design tab of the Database Properties box (InfoBox). Notes/Domino 6.x does not use an ODS of 20 for its templates; it uses ODS 43 which will support the property without issue.
|Messaging Applications||Lotus End of Support Products||Lotus Domino Designer||6.5, 6.0, 5.0|