Creating a Linked Server to a Universe database in SQL Server 2012

If you ever have to work with the Universe database from Rocket Software and you are a Microsoft developer  used to the niceties of using a standard RDBMS like SQL Server, chances are that you want to simplify your life by connecting to the Universe database by adding it as a Linked Server in SQL Server.

The MSDN Documentation for creating a Linked Server can be found here: http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx

As you can tell from the documentation, if you are using an ODBC-based provider/ODBC Driver, you will need to either provide a System DSN or an ODBC Connection String to the database.  Well, by taking a quick look at connectionstrings.com (http://connectionstrings.com/ibm-universe), there does not seem to be an up-to-date connection string to indicate the ODBC Drivers now produced by Rocket Software (Universe was recently acquired/sold to Rocket Software from IBM).

Therefore, given that I have very little experience with the Universe platform and had absolutely no idea what the ODBC connection string meant, I opted to use a System DSN instead.  Since I had just installed the 64-bit ODBC Driver, I was simply able to type odbcad32.exe at the Run command to open the following dialog:

Note that I checked the checkbox for "Save Password" just so that I did not have to deal with re-configuring the user credentials while creating my Linked Server.

Finally, I used SQL Server Management Studio to create my Linked Server as follows:

Since I was using the OLE DB Provider for ODBC Drivers, my Product Name was "MSDASQL" while my Data Source was the name of my System DSN--in this case "UNIVERSE".

After clicking OK, my Linked Server was successfully created!!