Creating lookups and dropdowns from a DB2 database
DB2 is a database product from IBM. It is a Relational Database Management System (RDBMS). DB2 is designed to store, analyze and retrieve the data efficiently. There are circumstances where you may want to load values to a dropdown list or use them in a lookup from a DB2 database.
The remainder of this article assumes the reader has good knowledge of Windows administration concepts. FileHold Professional Services can provide assistance to configure your system. Support for this material is not covered under FileCare.
The feature needed to support this operation is called User Configured Database Server Connection. This allows you to tell the document management software to access the data with a method specified by the user. The document management software does not require any prior knowledge of this data access method, but it does require the user to have full knowledge of how to install and configure this method.
In this example we will create a document schema database lookup used in the metadata panel, but the principals here could apply equally to a drop down menu field or with manage imports. We will use a sample DB2 database.
In order to use this feature, you will need a license with Custom Providers and Queries enabled. Upon request, a new license file can be sent to you at no charge. To request a license with this feature enabled, please use the "Request a License" feature in FileHold.
Preparing the DB2 database
To start, the DB2 database should be stored in a location accessible from the FileHold application server and you should know the following items:
- Name of the DB2 database / instance
- Username of a use who has access to the database
- Password for this user to connect to the DB2 database / instance
Preparing the data provider
The metadata field configuration will expect us to provide a compatible data provider. The data provider is the technology that knows how to read data from the data source. For this example the data source is a DB2 database file, so we will use the data provider downloaded for a DB2 Database.
If you do not have IBM DB2 installed on the document management software server you will need to download and install the IBM DB2 Database & Driver from IBM.
If you are interested to know what Data Link providers are available in your system you can run the Data Link Properties dialog. Methods for starting this dialog vary depending on your operating system version. Perhaps the easiest method is to create an empty text file with the extension UDL. Simply double click on this file and this dialog will appear.
Setting up the lookup
- To start, go to the lookup tab on your document schema configuration and select the Enable Schema Lookup box.
- Select the User Configured Database Server Connection option and select the IBM OLE DB provider for DB2 Data Provider or another driver for your DB2 database.
- The connection string must be entered before the connection can be verified. This connection string is specific to a DB2 database. Documentation will typically be provided with the vendor’s driver. A connection string sample is below:
A good source for information on the correct connection strings is available at www.connectionstrings.com.
- Press the Verify Connection button to confirm that you have entered the parameters and setup the environment correctly. You can select the database; the only possible value will be ---- as there is no actual database in this scenario.
- At this stage the remainder of the setup will be as for any database lookup.