Setup databases (DB2 and SqlServer), build and connect PMF to databases

Installation, configuration, guides and tips

PMF on Linux

Connecting to Sql Server

PMF on Windows

Connecting to a DB2 database

This is rather simple on both Linux and Windows and there are good manuals available online. Simply search for "DB2 client installation" in your favourite search engine.
You basically need to

start "db2setup" (either from the servers installation disk or as download)
install the client
catalog your database(s)

As soon as the DB2 client is set up, you will see all catalogued databases in PMF's connection menu. When first connecting to a DB2 database, a BIND file needs to be bound to this database. This will happen automatically.

General concept

PMF consists of a GUI (GraphicalUserInterface) and currently three plugins. Two are used to connect to DB2 databases, the other to access Sql Server.
All three plugins exist for Linux and Windows. Here is the layout:

To connect to one (or both) of these databases, the client system needs to be prepared.
For DB2, the CAE (ClientApplicationEnabler), sometimes called DB2 Connect or simply DB2 Client needs to be installed. This is easily done through the DB2 installation process.
For Sql Server, OBDC needs to be configured. Depending on the clients operating system (Windows or Linux), this is a bit more complicated.
Naturally, the server side needs some configuration too (user rights, open ports etc).
All this is covered in more detail in the corresponding chapters, see here.

Connecting to SQL Server on Linux

Obviously, SQL Server runs on Windows only. Nevertheless, it is possible to connect to SQL Server on Linux.
Microsoft is offering an ODBC driver for Linux (closed source), but I haven't tried it yet.
Instead, I'm using the tools described here. This guide should help you set up a connection to Sql Server from your Linux machine.
First, install a couple of packages:

Freetds
unixODBC or unixODBC2
unixODBC-dev (development files)
zlib development files, which depending on your distribution may be contained in zlib1g-dev

The names may vary on different systems.
Next, we need to edit three files.

/etc/freetds/freetds.conf or /etc/freetds.conf, depending on your distribution
/etc/odbc.ini
/etc/odbcinst.ini

These files should have been installed through the packages above. If some of them are missing, consult a search engine:
Search for your distribution and the missing file(s).

Perform the following steps as root.

Let's start with /etc/odbcinst.ini: Open it in your favourite editor and add these lines:

[ms-w7pro64]
Description= MS SQL Database
Driver     = /usr/lib64/libtdsodbc.so.0
Setup      = /usr/lib64/libtdsodbc.so.0
UsageCount=1

Obviously, the path on your system may vary, so make sure to enter the correct path to libtdsodbc.so.0
Again, if libtdsodbc.so.0 is missing, consult a search engine.
Also, "[ms-w7pro64]" is just an example, you can use your own marker, but make sure that you use it consistently
in the other files (see graphic below).

Next, edit /etc/odbc.ini and add these lines:

[w7-sqlsrv]
Description=TEST
Driver=ms-w7pro64
Servername=ms-w7pro64
UID=[your username, optional]
PORT=1433
TDS_Version=7.2 
Database=SomeDB [default database, optional]

Currently you should use TDS version 7.2 when you configure ODBC for usage with PMF. TDS 8.0 was renamed to TDS 7.2, but a lot of documentation still refers to 8.0
"w7-sqlsrv" is the string that we will use to connect to the server.
And finally, in /etc/freetds/freetds.conf or /etc/freetds.conf add these lines:

[ms-w7pro64] 
	host = W7Pro64
	port = 1433
	client charset = UTF-8
	use ntlmv2 = yes

"host" is of course the Windows machine where Sql Server runs. Try "ping [host]" to check that name resolution works.

Side note: To get name resolution working in Linux, install the packages "samba winbind" and "nss_wins".
Edit /etc/nsswitch.conf and find the line that starts with "hosts:". Add "WINS" (without quotation) at the end of this line.
Example: Simply change
hosts: mdns4_minimal files nis dns myhostname mdns4
to
hosts: mdns4_minimal files nis dns myhostname mdns4 wins

Here's a graphical explanation of the connections between the three ODBC files:

Make absolutely sure that there are no typos!

You can test your configuration in the command line (as root):

#osql -S w7-sqlsrv -U [username] -P [password]

If you get errors, read on.

A few notes and hints and possibly a little help:

Setting up SQL Server

You will need to configure the Windows server and Sql Server:

Configure the Windows firewall to accept connections on port 1433
Use "Sql Server Configuration Manager" to enable TCP/IP and named pipes
Use "Sql Server Configuration Manager" to assign the TCP/IP port number 1433 to the SQL Server Database Engine
Use "Sql Server Management Studio" to create a user, select "SQL Server authentification" (not "Windows authentification")

You can use nmap (from a Linux machine) to check if the port is open:

# nmap w7pro64 -p 1433

(replace w7pro64 with the name of your Windows server). It should give something like

Starting Nmap 6.40 ( http://nmap.org ) at 2014-07-11 22:13 CEST
....
PORT     STATE SERVICE
1433/tcp open  ms-sql-s
...

The port should be marked as "open".

Tabbing

When editing the files, tabbing appears to be important

Error "no IP address found for..."

If you get an error "no IP address found for [host]", you can try editing /usr/bin/osql
Open the file (as root) and search for "no IP address found" (around line 350)

ADDRESS=$(host ${HOST} | awk '/has address/ {print $4}' | head -1)
if [ -z "${ADDRESS}" ]
then
	echo "$(basename $0): no IP address found for \"${HOST}\""
#	exit 1  <--- comment out this line (add "#")
fi

and comment out the line "exit 1". The code does a reverse DNS lookup and this is not really necessary.

tds_version

TDS versioning can be a bit confusing because the nomenclature changed, see here. Generally you should use TDS version 7.2 (which was previously called 8.0)

Connecting

A successful connection (in a console) will look like this:

#osql -S w7-sqlsrv -U [username] -P [password]
checking shared odbc libraries linked to isql for default directories...
strings: '': No such file
	trying /tmp/sql ... no
	trying /tmp/sql ... no
	trying /etc ... OK
checking odbc.ini files
	reading /root/.odbc.ini
[w7-sqlsrv] not found in /root/.odbc.ini
	reading /etc/odbc.ini
[w7-sqlsrv] found in /etc/odbc.ini
found this section:
	[w7-sqlsrv]
	Description=TEST
	Driver=ms-w7pro64
	Servername=ms-w7pro64
	UID=uid
	PORT=1433
	TDS_Version=8.0
	Database=SomeDB
	
looking for driver for DSN [w7-sqlsrv] in /etc/odbc.ini
  found driver line: "	Driver=ms-w7pro64"
  driver "ms-w7pro64" found for [w7-sqlsrv] in odbc.ini
found driver named "ms-w7pro64"
"ms-w7pro64" is not an executable file
looking for entry named [ms-w7pro64] in /etc/odbcinst.ini
  found driver line: "	Driver     = /usr/lib64/libtdsodbc.so.0"
  found driver /usr/lib64/libtdsodbc.so.0 for [ms-w7pro64] in odbcinst.ini
/usr/lib64/libtdsodbc.so.0 is an executable file
Using ODBC-Combined strategy
DSN [w7-sqlsrv] has servername "ms-w7pro64" (from /etc/odbc.ini)
cannot read "/root/.freetds.conf"
/etc/freetds.conf is a readable file
looking for [ms-w7pro64] in /etc/freetds.conf
found this section:
	[ms-w7pro64]
		host = W7Pro64
		port = 1433
		tds_version = 8.0
		client charset = UTF-8
		use ntlmv2 = yes
	
osql: no IP address found for "W7Pro64"

Configuration looks OK.  Connection details:

                   DSN:	w7-sqlsrv                     
              odbc.ini:	/etc/odbc.ini                 
                Driver:	/usr/lib64/libtdsodbc.so.0    
       Server hostname:	W7Pro64                       
               Address:	                              

Attempting connection as [username] ...
+ isql w7-sqlsrv [username] [password] -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

Configuring OBDC on Windows 32-bit and 64-bit systems

To connect to SQL Server with the Windows version of PMF, you need to create a ODBC datasource.
This is done in the Windows ODBC-manager. Depending on the version of PMF (32bit or 64bit) and on your Windows version, you need to choose the correct ODBC-manager.
Usually it's enough to simply open the default Windows ODBC-manager. If, after configuring your datasources, you get an error in PMF along the lines of "wrong architecture", you selected the wrong ODBC-manager.

Start the Windows ODBC-manager by typing "ODBC" in the search field of Windows.

This will bring up the ODBC manager for 32 bit applications:

odbc01

Select either "System DSN" or "User DSN" and click "Add",
this will bring up the next menu:

odbc02

Select "SQL Server Native Client" with the highest version and click "Finish".
(Note: You may have to install "SQL Server Native Client" first)

In the next dialog you need to provide a server name:

odbc03

Give the connection a name, in this example it's simply "test".

In the next step, chose the authentication mode.
This depends on your environment, so you may have to try
out both options:

odbc05

Click "Next" as long as it takes fo finalize the assistant.

In the last step, test your configuration:

odbc06

If the test was successful, start PMF, select "SQL Server"
and select the connection you just created.

Accessing an MDF file on 64 bit Windows with PMF via ODBC

If you want to manipulate data in an mdf file, you will need to set up ODBC on the Windows machine first.
PMF for Windows is a 32 bit application, so on 64 bit Windows you need to start the correct ODBC manager.

The standard ODBC manager will configure connections for 64 bit applications and if you
try to use such a connection with PMF, you'll get an error along the lines of "wrong architecture".

Start the ODBC manager for 32 bit applications

PMF is 32-bit application, so it's very important to use the correct ODBC-manager.
On 32-bit Windows, use

c:\WINDOWS\system32\odbcad32.exe

On 64-bit Windows, use

c:\windows\SysWOW64\odbcad32.exe

This will bring up the ODBC manager for 32 bit applications.

odbc01

Select "System-DSN" and click "Add", this will bring up the next menu:

odbc02

Select the highest version of "SQL Server Native Client" and click "Finish".
In the next step, give the connection a name and select a server.

odbc03

The connection name "test" is later used to connect with PMF.

Click "Next" twice to bring up this menu:

odbc08

Enable "Change default database to" and enter a name ("mdf_test" in this example). Do NOT use the name of an existing database here!
Enter path and name of your mdf file.

Click "next" as long as it takes to finalize the assistant. In the last step you have the option to test the new connection.

Connect PMF

If everything works, use the connection name in PMF to connect to the database in the mdf file:
Select "SqlServer" and type in "test"

>>>>>>> 724e9246763af35ae062ef0d648b48c31e3c138d