Skip to main content
Windward

How to Setup Progress SQL-89 Connector and OIB/OID Broker

Overview

This article will describe how to setup and SQL89 connector through Windows ODBC connection interface.  It will also show how to setup the OID Broker communications server on the server where the Progress database is installed.

Resolution

Setting up an ODBC connection to a Progress Version 8 Database requires that an OIB/OID broker be started for the database. This will need to be done by the Database Administrator for the Progress Database. It also requires setting up the ODBC DataSource on the client.

The customer must purchase Progress Client Networking for the client machine and the DataDirect (Merant) ODBC Driver. Both can be purchased directly from Progress.

Below are instructions on setting up the SQL89 OIB/OID connection to a Version 8 database.

To setup a SQL-89 ODBC connection to the Progress database the PC client has to have Client Networking version 8.3x or 9.x installed locally (or pointing to a network installation). It also requires the DataDirect 32-bit ODBC Driver that can be purchased directly from Progress. The Driver will connect to a database on a Windows or Unix machine.

 
Part 1. The Database -Windows 
Part 1 refers to having a database running on a Windows server, and includes steps that need to be performed on this server. If your database is on a UNIX machine, go to Part 2.
 
A. Your database is probably already up and running. Make a note of how it was started. It is probably something like:
 
_mprosrv.exe databasename -H hostname -S servicename1 -N TCP
 
Hostname is the name of the Windows server machine.
Servicename1 is an entry in the services file that is associated with a port number.
 
B. Starting the OIBroker
 
The OIBroker is a listener that waits for a 3rd party ODBC client (i.e. your non-progress application) to call to it. 
At that point, the OIBroker spawns an OIDriver to complete the connection to the database. You only need one OIBroker running on your system. It doesn't matter how many databases you will need to connect to or how many drivers will need 
to be started.
 
(NOTE: Most configurations will start the OIBroker on the same machine as the database and will therefore make a direct connection (shared memory connection) to the database. There are a few situations that require the OIBroker to run on a different machine, or to make a remote connection to the database.
If your situation requires such a configuration, go to Part 5. Alternate configurations for more information before continuing)
 
You will need to set the following environment variables.

NOTE: 
In the following instructions all 
references to dlc as a directory, refer to the Progress installation directory.

The paths below must be in reference to the local machine  Example:(C:\progressdb\progress\bin) and NOT UNC names. 


DLC=<path to dlc>
PROMSGS=<path to dlc>\promsgs
IDLC=<path to dlc>
IPROMSGS=<path to dlc>\promsgs
 
prooibrk=<path to dlc>\bin\oibrkr32.exe
prooidrv=<path to dlc>\bin\oidrvr32.exe
 
where <path to dlc> is replaced by the actual path. To set them, go to the System Icon in the Control Panel and click on the Environment tab. Set them as System Environment Variables -NOT the User Environment Variables.
 
Now, make another entry in the services file, associated with a different port number for the OIBroker, for example servicename2. (NOTE: This SAME EXACT entry will need to be made to the services file on the PC client machine also. See Part 3 below)
 
To actually start the OIBroker use the following command:
 
<path to dlc>\bin\oibrkr32.exe -SV -S servicename2 -H hostname -N TCP
 
where <path to dlc> is replaced by the actual path.
You can verify that it started successfully by starting the NT task manager and verifying that there is an oibrkr32 process in the process list.
 
C. Shutting down the OIBroker 
 
**DO NOT shut the OIBroker down now**
 
The instructions are given here for completeness.
 
<path to dlc>\bin\_mprshut.exe -SV -H hostname -S servicename2 -N tcp
 
where the -H, -S values are the same ones used when the OIBroker was started.
 
Go to Part 3. 
 
Part 2. The Database Machine - UNIX 
 
Part 2 refers to having a database running on a UNIX machine, and includes steps that need to be performed on this UNIX machine. If your database is on an NT server, go to Part 1.
 
A. Your database is probably already up and running. Make a note of how it was started. It is probably something like:
 
_mprosrv databasename -H hostname -S servicename1 -N tcp
 
Hostname is the name of the UNIX machine. Servicename1 is an entry in the services file that is associated with a port number. (The services file is usually located in /etc.) 
 
B. Starting the OIBroker
 
The OIBroker is a listener that waits for a 3rd party ODBC client to call to it. At that point, the OIBroker spawns an OIDriver to complete the connection to the database. You only need one OIBroker running on your system. It doesn't matter
how many databases you will need to connect to or how many drivers will need to be started.
 
(NOTE: Most configurations will start the OIBroker on the same machine as the database and will therefore make a direct connection (shared memory connection) to the database. There are a few situations that require the OIBroker to run on a different machine, or to make a remote connection to the database.
If your situation requires such a configuration, go to Part 5. Alternate configurations for more information before continuing)
 
You will need to set the following environment variables.

NOTE: 
In the following instructions all references to DLC as a directory, refer to the Progress installation directory.

DLC=<path to dlc>;export DLC
PROMSGS=<path to dlc>/promsgs;export PROMSGS
IDLC=<Path to DLC>; export IDLC 
IPROMSGS=<Path to DLC>/promsgs; export IPROMSGS 
 
PROOIBRK=<path to dlc>/bin/_prooibk;export PROOIBRK
PROOIDRV=<path to dlc>/bin/_prooidv;export PROOIDRV
 
where <path to dlc> is replaced by the actual path. 
 
Now, make another entry in the services file, associated with a different port number for the OIBroker, for example servicename2. (NOTE: This SAME EXACT entry will need to be made to the services file on the PC client machine also. See Part 3 below)
 
To actually start the OIBroker use the following command from the dlc/bin directory:
 
<path to dlc>/bin/_prooibk -SV -S servicename2 -H hostname -N tcp
 
where <path to dlc> is replaced by the actual path. You can verify that it started successfully by using the following command:
 
ps -ef |grep _prooibk
 
On AIX, the process doesn't show when you grep the process itself, try:
 
ps -ef |grep servicename2
 
C. Shutting down the OIBroker 
 
**DO NOT shut the OIBroker down now**
 
The instructions are given here for completeness.
 
<path to dlc>/bin/_mprshut -SV -H hostname -S servicename2 -N tcp
 
where the -H, -S values are the same ones used when the OIBroker was started.
 
Part 3. The PC Client
 
A. You need to set the following environment variables on the PC client:

NOTE: 
In the following instructions all references to dlc as a directory, refer to the Progress installation directory.
 

NOTE:  

If you have setup your Progress database in a net sharing mode then you WILL need to use UNC names to reference your server and path, IE \\HOSTNAME\path

 

DLC=<path to dlc>
PROMSGS=<path to dlc>\promsgs
IDLC=<path to dlc>
IPROMSGS=<path to dlc>\promsgs
 
where <path to dlc> is replaced by the actual path.
 
If the PC client is Windows 95, 98 or Me the above variables should be set in the autoexec.bat file using the set command, and then then reboot the pc.
 
If the PC client is WinNT or Win2000, go to the System Icon in the control panel and click on the Environment tab. Set them as system environment variables - NOT the User Environment Variables.
 
NOTE: With the release of the "MERANT 4.00 32-BIT PROGRESS" ODBC driver the PATH environmnet variable MUST include:
<path to dlc>\bin
 
B. Make an entry in the services file on this PC client machine, for the OIBroker. (See Solution P13195, "Host, Protocol and Services files on Windows" for more information on the services file) It has to be exactly the same as the entry made on the database machine for the OIBroker, i.e. same name,and port number. 
 
Part 4. Configuring the ODBC Datasource
 
You will now define your ODBC Datasource using the ODBC Administrator. The ODBC Administrator can be accessed from the control panel by using the ODBC-32 icon.
 
One datasource will be needed for each database you will connect to. Therefore if you intend to use your non-progress application to connect to three Progress databases, you will need three distinct datasources.
 
Within the administrator, click Add, and select the MERANT 3.70 (3.50 or 3.60) 32-BIT PROGRESS driver. Do NOT select the SQL-92 driver. Click Finish and begin configuring the new datasource.
 
A. General Tab
 
You will only need to fill in 2 fields here; the datasource name and the database name. Assign a name to the datasource - it can be anything you like. Then, type the name of the database in the database name field (for example, sports).
 
B. OID/OIB Options Tab
 
The three entries on this screen, are the same three network parameters you used when starting the OIBroker in Part 1 or 2 above. 
 
Protocol will be TCP.
Service Name is the same -S value used when starting the OIBroker servicename2 from the example.
Host name is the name of the machine that the OIBroker is running on.
 
C. Database Options Tab
 
This screen refers to how the OIDriver will connect with the database. Because the OIBroker (and OIDrivers spawned) are on the same machine as the database, you will use DIRECT for Database Access.
 
Database Path refers to the path on the server machine where the database is located. 
 
If the database resides on an WinNT or Win2000 machine in directory c:\progress\dbs then the database path here will be c:\progress\dbs\
 
*** BE SURE TO PUT THE trailing \ on the database path! ***
 
If the database resides on a UNIX machine in directory /usr/progress/dbs then the database path here will be /usr/progress/dbs/
 
*** BE SURE TO PUT THE trailing / on the database path! ***
 
Operating system can be set to ignore for all platforms.
 
D. Advanced Tab
 
There is nothing on this form that is required to be filled in.
 
Click OK. This completes the definition of the ODBC Datasource and you should now be able to use your non-progress application to connect to the Progress database via the ODBC.
 
Part 5. Alternate Configurations
 
If you have completed Parts 1-4 above, your configuration is complete, and you should not go forward with the directions that follow. 
 
Most configurations will start the OIBroker on the same machine as the database and will therefore make a direct connection (shared memory connection) to the database. There are a few situations that require the OIBroker to run on a 
different machine, or to make a remote connection to the database.
The specifics of these situations will not be discussed here.
 
After reviewing the specifics below, you should start back at Part 1, and proceed through Parts 1-4 making the noted changes below.
 
A. If you need to have your OIBroker running on a machine other than the database machine:
 
Part 1B (or 2B) will be performed on the "different machine". 
For example, if the OIBroker has to run on the pc client the environment variables prooibrk and prooidrv need be set (appropriately for the version of windows), the service name for the OIBroker needs be made to the services file on the pc, and then the OIBroker can be started. 
 
This scenario will then require that the OIBroker make a remote connection to the database, so follow the steps in B below.
 
B. If the OIBroker (& OIDriver) needs to make a remote connection to the database, Part 4C above would read as follows:
 
Database Options Tab
 
This screen refers to how the OIDriver will connect with the database. Because the OIBroker (and OIDrivers spawned) are not on the same machine as the database, you will use VIA SERVER for Database Access, and you will specify the network protocols used when starting the database:
 
Protocol will be TCP.
Service Name is the same -S value used when starting the database (servicename1 from the example).
Host Name will be the -H value used when starting the database.