The number of characters that can be stored in a IDMS userfield is determined by the column length in the underlying database table that holds the IDMS cardholder records. IDMS stores the database table, column data type and column length in the DBL filetype.


The IDMS userfield size can therefore be made smaller or larger by altering the column length in the source database table. Please consult your DBA for assistance to alter the source database table column length, or refer to the Advanced section.


How do I find the database column name?

It is likely the IDMS userfield name and the database column will be named similarly. To verify the name of the database table and column name open the DBL and view the mapping information.


  1. Login to IDMS as SYSTEM OPERATOR or equivalent admin account
  2. Open Utilities > Setup > Map Data > Edit
  3. Choose the DBL then click OK to open the map

  4. The table name is located on the left

  5. The database column name can be found in the Current Data Mappings list

  6. Click CANCEL to close the map without saving changes



Update the IDMS DBL file

After the database column length has been resized, the DBL file must be updated to account for the new column length. The IDMS DBL mapping file can be updated by following the below steps:

  1. Alter the column length in the MS-SQL, Access, or Oracle database
  2. Login to IDMS as SYSTEM OPERATOR or equivalent admin account
  3. Open Utilities > Setup > Map Data > Edit
  4. Choose the DBL then click OK to open the map
  5. Click OK to close the map


Troubleshooting

  • String fields should always use the VARCHAR data type. Do not use NVARCHAR as it 
  • A data type of -9 in the DBL file will typically indicate the use of a NVARCHAR field. It will further cause a SQL 42000 error in the IDMS application and prevent use of IDMS. We recommend converting NVARCHAR fields to VARCHAR.

    WORKAROUND: Edit the DBL file in notepad, and replace all instances of Type=-9 with Type=12. However, please note that re-editing a modified DBL in IDMS will revert the type to -9.


Advanced

Advanced users or database administrators are permitted to alter the column length of any IDMS userfield.

Execute the below script command. Be careful to only change the length and not the column data type otherwise data loss may occur. 


Note: Decreasing the size of a database column may result in loss of data. Always backup the database prior to altering the schema.


Microsoft SQL Database

ALTER TABLE IDCARD
ALTER COLUMN EMAIL VARCHAR(125)
GO