Testing ODBC to OE 10.2A - Broker rejects connection

[mgerbi]

I created the ODBC and tested the connection, I get this error. This is Progress 64bit with 64bit ODBC drivers installed on a Windows Server 2008 64 bit.

Now I noticed that their db is not installed in the standard directory that it should (this is part of an ERP system). The properties files were updated to point to the new location but I don't believe the ERP program uses the ODBC drivers at all. The ERP system is working just fine, so only the ODBC is causing issues.

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Broker rejects connection.

Checked the db log and this is the error there.
Server's received count 1 does not equal client(1)'s send count 16777216.

I'm pretty sure the drivers were installed from the existing OpenEdge install directory. Any help would appreciated.

 

[Rob Fitzpatrick]

Where is the SQL client relative to the database? Are they on the same Windows server or on different boxes? If different, there could be a firewall in between that you have to configure.

If not, you need to check the SQL configuration of the database. Is there a DBA you can talk to? Have you looked for clues in the database log (dbname.lg)?

There are a lot of things that can prevent connection. The database must have the SQL engine installed. It must have a SQL-only or shared 4GL/SQL broker running (SQL only is preferred). If the broker is running you must know the TCP port number the broker is listening on, and specify it in your connection string. The broker must be able to spawn a SQL server, if necessary, or have a server with a slot available for a connecting client. If a server is to be spawned, there must be an available TCP port in its minport/maxport range for it to bind to. Your client must be able to make a connection to the server on that port. The server must be able to open the database files and attach to the shared memory segments. The user name you are specifying in your connection string must exist in the database as a SQL user (which is different from an ABL user).

To troubleshoot, first off I would check on the database side and make sure that the SQL broker is running and is able to spawn servers. Check the value of the -S parameter for this broker; it is either a port number or a service name. Make sure you are connecting on that port/service. Try to determine based on connected users and startup parameters whether the next SQL client connection will be assigned to an existing server or cause a new server to be spawned. Check in promon R&D 1 17 (servers by broker) to see the current load on the servers, and whether there are any pending connections. I've seen broker bugs related to server port assignments, so check as well whether you're using the default minport and maxport or specifying them explicitly.

 

[mgerbi]

Where is the SQL client relative to the database? Are they on the same Windows server or on different boxes? If different, there could be a firewall in between that you have to configure.

If not, you need to check the SQL configuration of the database. Is there a DBA you can talk to? Have you looked for clues in the database log (dbname.lg)?

There are a lot of things that can prevent connection. The database must have the SQL engine installed. It must have a SQL-only or shared 4GL/SQL broker running (SQL only is preferred). If the broker is running you must know the TCP port number the broker is listening on, and specify it in your connection string. The broker must be able to spawn a SQL server, if necessary, or have a server with a slot available for a connecting client. If a server is to be spawned, there must be an available TCP port in its minport/maxport range for it to bind to. Your client must be able to make a connection to the server on that port. The server must be able to open the database files and attach to the shared memory segments. The user name you are specifying in your connection string must exist in the database as a SQL user (which is different from an ABL user).

To troubleshoot, first off I would check on the database side and make sure that the SQL broker is running and is able to spawn servers. Check the value of the -S parameter for this broker; it is either a port number or a service name. Make sure you are connecting on that port/service. Try to determine based on connected users and startup parameters whether the next SQL client connection will be assigned to an existing server or cause a new server to be spawned. Check in promon R&D 1 17 (servers by broker) to see the current load on the servers, and whether there are any pending connections. I've seen broker bugs related to server port assignments, so check as well whether you're using the default minport and maxport or specifying them explicitly.

This isn't a SQL install, nor is there a SQL db attached. This is just progress 4GL. I have the port number of the db, the host, db name and username and password correct. I noticed that in the conmgr.properties file, the type is set to 4GL for the server group.

I did also find this though that may be something.
http://knowledgebase.progress.com/articles/Article/P126491?retURL=/apex/progresskbsearch&popup=false

The customer does not have any SQL server groups setup. Would I need to create this?

 

[Rob Fitzpatrick]

I understand that this is a Progress database. However you are connecting to it via ODBC which means you aren't using the 4GL query engine. You are using the SQL-92 engine.

A Progress database broker has a server type; it is 4GL, SQL, or both. This determines the type of servers it can spawn. 4GL clients connect to 4GL servers and SQL clients connect to SQL servers. Note that when I say "SQL servers" I mean "Progress OpenEdge RDBMS SQL server processes"; this has nothing to do with Microsoft SQL Server. As I said earlier, you should use separate brokers for 4GL and SQL, each with the appropriate -ServerType startup parameter.

You said you have "the port number of the db". The database doesn't have a port number. Database brokers have port numbers, and you need to ensure you are talking to the correct broker. It would be easier if you obtained the startup portion of the database log from its most recent start. It is about 70 lines or so, starting with the (333) message (Multi-user session begin.). Post it within CODE tags so it is legible.

 

[mgerbi]

The server type is 4GL. Can I just email you the code, I would rather not display it in here since it is not my system.

I did notice this line: "This broker supports 4GL server groups only".

 

[Rob Fitzpatrick]

There is nothing proprietary in the startup portion of a database log. However, I will not repost it here.

You are trying to connect a SQL client to a Progress database that is not enabled for SQL connections. It has one 4GL broker, which will only accommodate 4GL clients. If you want to connect via ODBC you need to configure a secondary broker with "-ServerType SQL" and assign it a different port with the "-S <port no>" startup parameter. You will also need to specify -Mpb and -Ma, which are servers per broker and maximum clients per server respectively, for this SQL broker. You will choose these values based on how many SQL clients need to connect to the database. You will also have to increase -n accordingly; this is the total allowed connections. Your -Mn (total servers) should be the sum of your -Mpb for your 4GL and SQL brokers, plus 1 for the secondary broker.

While you're looking at broker startup parameters you may want to consider some optimization. The lock table (-L) is set very high, above 200,000. This shouldn't be necessary unless there is some sloppy code in this environment. Also the buffer pool (-B) is very small. At 20,000 blocks (8 KB per block) it is only 160 MB. Unless this database a similar size that will probably result in a poor buffer hit ratio.

Anyway, first things first. Have your DBA or your customer's DBA configure a secondary SQL broker on its own TCP port. Try to connect to it via ODBC and go from there.

 

[mgerbi]

There is nothing proprietary in the startup portion of a database log. However, I will not repost it here.

You are trying to connect a SQL client to a Progress database that is not enabled for SQL connections. It has one 4GL broker, which will only accommodate 4GL clients. If you want to connect via ODBC you need to configure a secondary broker with "-ServerType SQL" and assign it a different port with the "-S <port no>" startup parameter. You will also need to specify -Mpb and -Ma, which are servers per broker and maximum clients per server respectively, for this SQL broker. You will choose these values based on how many SQL clients need to connect to the database. You will also have to increase -n accordingly; this is the total allowed connections. Your -Mn (total servers) should be the sum of your -Mpb for your 4GL and SQL brokers, plus 1 for the secondary broker.

While you're looking at broker startup parameters you may want to consider some optimization. The lock table (-L) is set very high, above 200,000. This shouldn't be necessary unless there is some sloppy code in this environment. Also the buffer pool (-B) is very small. At 20,000 blocks (8 KB per block) it is only 160 MB. Unless this database a similar size that will probably result in a poor buffer hit ratio.

Anyway, first things first. Have your DBA or your customer's DBA configure a secondary SQL broker on its own TCP port. Try to connect to it via ODBC and go from there.

Thanks for the help and explaining everything in detail. This all makes sense and I appreciate that. The database is probably a gig, I don't remember off hand, but this ERP system is not known for good progress optimazation. My application is quite small and no one else will be using the ODBC, at least in the near term. Oh and the db has over 500 tables, again I'm not sure, so I don't know why the lock table is set as high as it is, but there are a lot of possible add-ons one could buy that tie into the db. Plus you could have up to 150 users connected to the db at once. For now though I will create the extra broker, but like I said I appreciate your explanation, I actually learned something. I considered this thread as answered.