Using Different ODBC DSN Types for i5 Access

When I create new ODBC Data Sources to connect my Windows PCs to my i5 systems, I notice that Microsoft‘s ODBC Data Source Administrator will let me configure three different categories of Data Source Names (DSN): User DSN, System DSN, and File DSN. I always choose User DSN. Am I making the right choice?

–Bob

Don’t worry. No one has ever lost any teeth by choosing the wrong ODBC DSN type. Rather, form should follow function in DSN category selection and the type you select is wholly dependent on how you want to use your connection. Each DSN category has a specific use and as long as your DSN meets your application needs, there is no one correct type to select.

When you enter the ODBC Data Source Administrator, you will see a number of tabs across the top of the panel. The first three tabs are specified as User DSN, System DSN, and File DSN. If you click on any of those tabs, it will show you the DSN connections that are already configured using these DSN types. To add, remove, or change an existing DSN, you must click on one of the DSN tabs on this screen because the Add, Remove, and Configure buttons will only appear on the panel when those tabs are selected (they aren’t shown when the other tabs, such as Drivers, Tracing, or Connection Pooling, are highlighted). This means that to add a new data source, you first need to select the DSN category tab that it is going to exist under and then click on the Add button. The rest of the ODBC configuration for your new DSN will be exactly the same for all three DSN categories, but the final ODBC connection will be stored as your target DSN type with the following advantages and disadvantages associated with each type. Here’s how the three DSN categories stack up against each other.

User DSNs are only available on the PC they were created on, and they can only used by the logged on Windows user who created the connection. No other user can access a User DSN when they log on to the same PC. User DSNs are useful when you want to restrict a connection to a specific user and make it unavailable to any other user who may sign on to the machine.

System DSNs can be used by any user that signs on to your Windows PC. They can also be used by any process or service that is running on that PC. If you have multiple Windows users who share this PC during the day (like a first and second shift operator), you can sign on as the Windows administrator and configure your ODBC connection as a System DSN. That connection will then be available to any user who signs on to the system. System DSNs can also be used when you are using a Windows process or service to serve i5/OS data to other users who access the PC.

File DSNs are similar to System DSNs except that these DSNs are stored as files in either a Windows PC folder or on a network file share. Because these DSNs can be stored remotely, other users can access them for their Windows –> connectivity, and one File DSN can service many networked users, eliminating the need to create separate DSNs on different Windows desktops. The other advantage in using File DSNs is that you can email them to other users who can then point their applications at a newly saved File DSN for instant connectivity.

When you select the File DSN tab off the ODBC Data Source Administrator main panel, you also need to designate two other parameters that will be used with your Data Source. The Look in Windows dropdown box allows you to browse your PC or the network to specify the location where you want to store and retrieve your File DSNs from. Once you find the location, you can click on the Set Directory button to specify that this is the default File DSN location on your PC. Aside from this, there are only two other differences in creating a File DSN. First, the ODBC Administrator will prompt you for an actual file name to store your DSN in before you start configuring the DSN. The other difference is that you cannot use the Administrator program to change the name of your new ODBC File DSN after you start creating it. If you want to change the DSN name, you would have to remove and recreate the File DSN.

Once you select the DSN category for the Data Source that you are creating, the DSN configuration process is virtually identical for all three categories, with the exception of the naming restriction for File DSNs. The nice thing about DSN category selection is that it doesn’t change anything about using ODBC except for where your DSNs are stored and what users, processes, and services can access them.

The key to selecting the right DSN type for your Windows PC lies in knowing how that DSN will be used. For single user access on one PC, a User DSN will perform just fine. However, if you need to share your DSNs between users or applications, you can also use System and File DSNs for multiple user, process, or service access. Since you configure all three DSN types exactly the same way, the hardest choice may be determining which DSN configuration that you want to use.