Question 10.2B Driver to connect to 10.1C databases

(I come from this thread: http://www.progresstalk.com/threads/10-2a-driver-to-connect-to-10-1c-databases.115797/#post-402343)


Hi,

I think I might have a similar problem as the thread mentioned above. Trouble is, I'm completely new to this, even my experience with Linux is quite limited. My colleagues (who set this up) are on holiday for more than a week, and the ODBC connection is not getting up.

I'm connecting from a Windows machine where we got OpenEdge Progress ODBC 10.2.B driver installed.

This is the Linux server where the DB is on:

[root@LNX-DB2 15-02-22]# sh /u2/progress/start
OpenEdge Release 10.1C as of Sat Feb 9 14:16:03 EST 2008
10:55:27 BROKER 0: Multi-user session begin. (333)
10:55:27 BROKER 0: Begin Physical Redo Phase at 2816 . (5326)
10:55:28 BROKER 0: Physical Redo Phase Completed at blk 2897 off 7912 upd 431. (7161)
10:55:28 BROKER 0: At end of Physical redo, transaction table size is 256. (13547)
10:55:29 BROKER 0: Started for 6600 using tcp IPV4 address 0.0.0.0, pid 3322. (5644)
OpenEdge Release 10.1C as of Sat Feb 9 14:16:03 EST 2008
10:55:30 BROKER 1: Started for 4501 using tcp IPV4 address 0.0.0.0, pid 3334. (5644)
10:55:30 BROKER 1: This is an additional broker for this protocol. (5645)
10:55:30 BROKER 1: This broker supports SQL server groups only. (8864)


The reason why I have 2 different versions is: we got this installation live at our factory, but we want to have a backup DB on our system aswell. So we create a copy each week and mount it here so we can access it too. Now, I want to use an ODBC connection to get some data from there so I can create reports locally instead of (virtually) going to the other side of the world. Our supplier gave me this driver version together with SP8.

The funny thing though, we have an active software program that is accessing this DB without a problem, so I'm sure it is working. I just can't figure out how this ODBC driver isn't working.

When I stop and start the SQLExplorer, it works without hiccups. I don't get any exceptions added to the files.
I'm connecting through the 4501 port, which is open (I can telnet on that port even) but still rejecting my connection.

Any of you guys prepared to help me solve this case? Because it's really doing my head in, looking at it myself (I have never done anything with Progress, ever).

It would be helpful to see your actual error messages.

My first guess would be that you can't connect to the server port for some reason, assuming a SQL server is running. Or that the SQL broker can't spawn a SQL server. But that this point all I can do is guess.

If you can connect locally via the port I would tend to suggest networking.
There was a strange glitch in the 10.2B SP7 (dunno about 8) where it struggled with IPV6 lookups on Windows, so try adding the IP4 address into the hostname.

hi,

This is the error message I'm getting. The SQL server is running though, since I can access the database through our software program.

Next I would have a look into the log file of the database. I am pretty sure you will find some error message that coincides with the error message you see on the client which might give you an idea why the broker rejects your connection request.

From my point of view, the most popular reason for the broker to reject SQL connections is that it is not configured correctly. You might want to have a look into the knowledgebase entry http://knowledgebase.progress.com/articles/Article/P7843 to get an idea how you should configure the broker for SQL access. I strongly recommend you to set up a secondary login broker to have each type of client ( 4GL or SQL ) have its own distinct login broker.

Heavy Regards, RealHeavyDude.

I agree with RHD: check the database log file for errors. Also, try connecting to the database on the database server, using sqlexp (SQL Explorer), a JDBC command-line client.

Example: sqlexp dbkon -S 4501 -user insadmin -password yourpassword

If that works, the SQL broker is configured correctly and some other issue prevents you from connecting remotely. If it doesn't, you need to reevaluate the broker configuration.

Looking at the log he posted earlier there is an additional broker.

Check your numbers

You need to set your maximum number of servers to be SQL + 4GL, so for example on a small system

Max Servers = 12

Then under your 4GL broker set max servers to 10
Then under your SQL broker set max servers to 2

Quite often you can find the brokers set to zero (the default), which means your 4GL in the above example will take all 12 servers (potentially)

The above can be based on timing as well, i.e. I knew a place once that would restart their databases and immediately start a SQL connection - which grabbed one of the servers, before the 4GLS kicked in.

Chris Hughes said:

You need to set your maximum number of servers to be SQL + 4GL, so for example on a small system

Max Servers = 12

Then under your 4GL broker set max servers to 10
Then under your SQL broker set max servers to 2

Click to expand...

To allow all configured servers to spawn, the -Mn parameter (maximum servers) must be equal to the sum of -Mpb (max servers per broker) for each broker, plus the number of secondary brokers.

In the example above, if you had a primary 4GL broker with -Mpb 10 and a secondary SQL broker with -Mpb 2, you would set -Mn to 13 (10 + 2 + 1).

Also, it is best practice to use separate, non-default port ranges for the servers by specifying the -minport and -maxport parameters with each broker. These TCP ports must not coincide with any defined in the OS services file.

Hi,

I tried the command Rob gave me and I got these exceptions in my log files (see attachment).

Found something else though: When I try to start 'proserve dbkon' in my terminal, I get "BROKER ** Cannot find or open file /u2/progress/dbkon.db, errno = 2. (43)". In the /u2/progress/ directory there is no .db file however. There's dbkon.db file a level lower in /u2/progress/inco though. So I guess I need to make it look a level deeper than it currently does. Would that also be in the broker file?

(ps: thanks for all the help you've given already, many kudos!)

The error message shows that the broker is unable to spawn a SQL server. It is possible that the configured range of server ports is already in use. We need to see the contents of the database log from the time of the connection attempt to be able to diagnose further.

Your second item about attempting to use the proserve command confuses me. Why are you doing this? Is this the same database as the one you are trying to connect to via SQL?

Now you are trying to manually run a proserve command, apparently without any parameters, whereas in your first post you were running a shell script ("sh /u2/progress/start"). Why the change?

As to the error message, it is telling you the problem. The proserve command takes the database path, relative or absolute, as its first parameter. If you typed "proserve dbkon" and got the error message "cannot find or open file /u2/progress/dbkon.db" then your current working directory when you issued this command was /u2/progress. So if you know there is no DB there then you should know that command won't work. If the absolute path to the DB is /u2/progress/inco/dbkon.db then the correct syntax (omitting a parameter list) would be "proserve /u2/progress/inco/dbkon". Or, equivalently, "cd /u2/progress/inco" followed by "proserve dbkon".

david_derudder said:

Would that also be in the broker file?

Click to expand...

I don't know what a "broker file" is. Do you mean the shell script (/u2/progress/start) that is used to start the broker? I don't know; that script isn't from Progress. It was probably provided by your vendor. Look in it and see what it does.

Aha I see See my previous post then as an example of my non-existent Linux (and Progress) skills. I thought the proserve command was the proper one to use, but apparently not then.
I will look up the database log file and post the entries.

This is the log you were talking about?
To show it all I'll add the exception too.

Those are the client log files created by SQL Explorer. I'm looking for the server-side log. It is in the database directory, with a .lg extension. In this case, if I understand your location properly, it would be /u2/progress/inco/dbkon.lg.

david_derudder said:

I thought the proserve command was the proper one to use, but apparently not then.

Click to expand...

The proserve command is used to start a database broker, primary or secondary. I assume the shell script you use to start your database contains (at least) two proserve commands, to instantiate broker 0 and broker 1.

Hi, the latest entry said the server was stopped, so started the SQL Server again and then tried to connect with the Process ODBC driver. It resulted in the SQL server shutting down...

Thu Apr 16 09:10:47 2015
[2015/04/16@09:10:47.529+0200] P-10850 T-0 I SHUT 14: (542) Server shutdown started by root on /dev/pts/0.
[2015/04/16@09:10:47.529+0200] P-1057 T-0 I BROKER 0: (2248) Begin normal shutdown
[2015/04/16@09:10:47.529+0200] P-1057 T-0 I BROKER 0: (-----) Sending signal 12 to user 1
[2015/04/16@09:10:48.565+0200] P-1057 T-0 I BROKER : (-----) Removed shared memory with segment_id: 2686980
[2015/04/16@09:10:48.628+0200] P-1057 T-0 I BROKER : (334) Multi-user session end.

Thu Apr 16 09:10:57 2015
[2015/04/16@09:10:57.632+0200] P-10880 T-0 I BROKER 0: (333) Multi-user session begin.
[2015/04/16@09:10:57.638+0200] P-10880 T-0 I BROKER 0: (5326) Begin Physical Redo Phase at 4864 .
[2015/04/16@09:10:57.658+0200] P-10880 T-0 I BROKER 0: (7161) Physical Redo Phase Completed at blk 4944 off 1040 upd 833.
[2015/04/16@09:10:57.658+0200] P-10880 T-0 I BROKER 0: (13547) At end of Physical redo, transaction table size is 256.
[2015/04/16@09:10:57.733+0200] P-10880 T-0 I BROKER 0: (5644) Started for 4500 using tcp IPV4 address 0.0.0.0, pid 10880.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4234) Progress OpenEdge Release 10.1C build 1282 on Linux LNX-DB2.localdomain 3.10.0-229.1.2.el7.x86_64 #1 SMP Fri Mar 27 03:04:26 UTC 2015.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4281) Server started by root on /dev/pts/0.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (6574) Started using pid: 10880.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4235) Physical Database Name (-db): /u2/progress/inco/dbkon.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (-----) LRU mechanism enabled.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4239) Number of Database Buffers (-B): 62500.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 43.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4241) Current Size of Lock Table (-L): 150016.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (13953) Maximum Area Number (-maxArea): 32000.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4242) Hash Table Entries (-hash): 18289.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 24000.
[2015/04/16@09:10:58.734+0200] P-10880 T-0 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 512 Mb.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (6573) Database Blocksize (-blocksize): 4096.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4250) Before-Image Cluster Size: 524288.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 20.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (9238) BI File Threshold size (-bithold): 0.0 Bytes.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (6552) BI File Threshold Stall (-bistall): Disabled.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4255) After-Image Block Size: 8192.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 20.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 6.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 14.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 3.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4260) Maximum Number of Users (-n): 81.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4261) Host Name (-H): LNX-DB2.localdomain.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4262) Service Name (-S): 4500.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (14268) TCP/IP Version (-ipver) : IPV4
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4263) Network Type (-N): tcp.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (4282) Parameter File: Not Enabled.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (5647) Maximum Servers Per Broker (-Mpb): 10.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 1025.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 2000.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (8863) This broker supports 4GL server groups only.
[2015/04/16@09:10:58.735+0200] P-10880 T-0 I BROKER 0: (9336) Created shared memory with segment_id: 3833860
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12813) Allowed index cursors (-c): 324.
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12815) Lock table hash table size (-lkhash): 25621
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12816) Maxport (-maxport): 2000
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12817) Minport (-minport): 1025
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12820) Maximum Servers per Broker (-Mpb): 10
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 1.1 MBytes.
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2015/04/16@09:10:58.736+0200] P-10880 T-0 I BROKER 0: (10471) Database connections have been enabled.
[2015/04/16@09:10:58.738+0200] P-10892 T-0 I BROKER 1: (5644) Started for 4501 using tcp IPV4 address 0.0.0.0, pid 10892.
[2015/04/16@09:10:58.738+0200] P-10892 T-0 I BROKER 1: (5645) This is an additional broker for this protocol.
[2015/04/16@09:10:58.738+0200] P-10892 T-0 I BROKER 1: (8864) This broker supports SQL server groups only.
[2015/04/16@09:15:48.986+0200] P-10892 T-0 I BROKER 1: (-----) Shared Library Path set to LD_LIBRARY_PATH=/usr/java/jdk1.5.0_15//jre/lib/i386:/usr/java/jdk1.5.0_15//jre/lib/i386/native_threads:/usr/java/jdk1.5.0_15//jre/bin:/usr/java/jdk1.5.0_15//jre/lib/i386/client::/usr/dlc/lib:/usr/dlc/lib
[2015/04/16@09:15:48.989+0200] P-10892 T-0 I BROKER 1: (8841) SQL server process terminated.


This are the commands in the start script:

/usr/dlc/bin/proserve /u2/progress/inac/stdb -N tcp -S 4600 -B 12500 -n 75 #-aistall -DBService replserv
/usr/dlc/bin/proserve /u2/progress/inco/dbkon -N tcp -S 4500 -n 80 -Mn 13 -Ma 6 -Mi 3 -Mpb 10 -e 63 -B 62500 -L 150000 -pica 1154 -ServerType 4GL #-aistall -DBService replserv
/usr/dlc/bin/proserve /u2/progress/inco/dbkon -N tcp -S 4501 -m3 -Ma 6 -Mi 2 -Mpb 2 -ServerType SQL
#
#/usr/dlc/bin/proserve /u2/progress/inco/dbkon -N tcp -S 6600 -n 180 -Mn 18 -Ma 10 -Mi 5 -Mpb 12 -e 63 -B 625000 -L 150000 -pica 1154 -ServerType 4GL -minport 10250 -maxport 20000 -aistall -DBService replserv
#/usr/dlc/bin/proserve /u2/progress/inco/dbkon -N tcp -S 4501 -m3 -Ma 10 -Mi 5 -Mpb 2 -ServerType SQL

OK, we can see your startup parameters now, as well as the fact that you have two databases and you're using OpenEdge Replication. (Not directly related, but good to know.)
The important error is at the end of the log above; the (8841) error. If you search the Progress Knowledge Base or the offline ProKB application you will find several articles about how to investigate 8841 errors. A common theme in many of them is a problem with the Progress installation, e.g. with scripts, with Progress binary file permissions, with Java installation, etc.

So one course of action to pursue is installing OpenEdge 10.1C (and its prerequisite Java version) from scratch on the database server in an alternate directory, leaving your current installation intact. Then make a copy of your database and try to start that database using that new OpenEdge installation and connect a SQL client to it (you will obviously have to use different broker and server ports for that test). If it works, you know the problem is with your original installation.

Other items of note:

• You are not specifying the -minport or -maxport startup parameters with either of the brokers on your dbkon database. This is a worst practice when using remote clients. It means all of your servers are being spawned within the default range (1025-2000). The more databases (and other networked applications) you have on the same box, the more risky this becomes and the more difficult it becomes to troubleshoot.
• Although your original question isn't about application performance, your list of startup parameters includes many default values which are sub-optimal for most workloads. This suggests there is room for performance optimization. And you have OpenEdge installed in the default location, also not a good practice. These things suggest an environment that is not "well loved", from a Progress perspective, i.e. it may have other common Progress database or application issues like an antiquated database structure or poorly-configured client startup parameters. Bear this in mind if you do hear reports of performance issues in this environment in future. Often in such situations the abundance of "low-hanging fruit" makes significant improvements relatively easy to implement.

Hi Rob,
Will check out these knowledgebase articles for starters. Thanks again for all the help!
I'd never have found all this without your directions

(This DB will only be used for creation of reports and reading of the data by 1 process or 1, maybe 2 persons, so there won't be really 'worked' on it)

Hi all,

Just to let you know we've fixed it.
We moved the drives the DB was on to the old Red Hat Linux server we have lying around and there it worked without any problems.
There were several Java versions installed on the CentOS 7 one, which caused conflicts according to my colleague.

Thanks for all the input again!

Good news. Just a bit of advice: you can have multiple versions of Java and OpenEdge installed on a server without issue. OpenEdge will use environment variables (like JAVA_HOME) and its own config files to determine which to use.