Secondary SQL Broker

[zacdesai]

Hi There ,

Ive followed Casper's instructions in setting up a secondary broker for ODBC connections. ie: set up a second PF file , and start with proserve -pf "2nd file" . It works beautifully when all the users are logged off. During the day we have about 52-54 users logged on. -n is set to 70 . -Mn set to 5 . When I try to start the 2nd broker in the day i get :

PROGRESS Version 9.1E as of Tue Oct 12 15:27:52 EDT 2004
10:01:31 SERVER : You have attempted to connect to a database with too many
users connected to it. Retry the connection later,
or increase -n on the server. (5291)

any help will be greatly appreciated.
Thanks

 

[Casper]

Well like the message you have more then 70 simultanous database connections.
Fun part (well, not really) is that even in the latest release of OpenEdge this results in the _slqsrv2 proces not accepting any connections anymore, even if the number of connections have dropped. So the "Retry the connection later" option wont work for SQL. This is a bug I reported many many times to progress, but as to date, to no avail.

But to get back to your question: You say the value of -Mn=5 that means that you have to set -Mpb for the primary broker to 2 and for the secondary sql broker to 1 (or the other way around depending on the number of 4GL and SQL remote connections you expect. Did you do that?

What is the value -Ma and -Mi for both brokers.

Casper.

 

[RealHeavyDude]

Just to add to it:

-n counts each process that connects to the database. IMHO there is only an indirect relation to the number of licenses you've purchased (one user could open the application 2 times and causing two -n counts but only one license count) ...

You should also take into account any batch processes that run on the server machine and connect the database as self-service clients. This might also be true for AppServer agents.

HTH, RealHeavyDude.

 

[zacdesai]

Hi Casper , Thanks for the quick reply. ..
The primary broker :
Maximum number of clients per server (-Ma): 14
Delay of before-image flush (-Mf): 3
Maximum number of servers (-Mn): 5
Maximum number of users (-n): 71
No -Mi or -Mpb set on the primary broker.

On the secondary broker
-Mpb 5 ; -Ma 5 . I only really need 1 SQL connection for a report application . The rest would be 4gl.

Thank!

 

[Casper]

Hi Casper , Thanks for the quick reply. ..
The primary broker :
Maximum number of clients per server (-Ma): 14
Delay of before-image flush (-Mf): 3
Maximum number of servers (-Mn): 5
Maximum number of users (-n): 71
No -Mi or -Mpb set on the primary broker.

On the secondary broker
-Mpb 5 ; -Ma 5 . I only really need 1 SQL connection for a report application . The rest would be 4gl.

Thank!

Your parameters are not set correctly.
You need to count -Mn as follows:
-Mn = #4GL servers + #SQL servers + 1 for each extra broker.
So in your case (with -Mn set to 5) you need to set -Mpb on the primary broker to 3 and -Mpb on the secondary broker to 1.

I also wonder why you have such a large -Ma. I tend to max at 5 for Ma.
If you only want 1 SQL connection you can set -Ma to 1 on the secondary broker.
But I would go as follows:

primary broker:
-Ma 5
-Mi 1
-Mn 14
-Mpb 12
-ServerType 4GL
-n 71
Secondary sql broker
-Ma 5
-Mi 1
-Mpb 1
-ServerType SQL

presuming you only use remote client connections.

Casper.

 

[zacdesai]

Hi Casper,

Thanks youve been a great help. Beginning to make some sense now. I am actually going on leave from today onwards & will give it a shot when I get back.

Thanks again for your help
Regards

 

[zacdesai]

Hi Casper ,

Sorry for the laste reply. Went on a short vacation.

Thanks anyway. The parameters are working excellently. I used to have messages saying insufficient resources previously when I had the 2nd broker running. but so far thereve been none.

Regards
Zakaria

 

[RealHeavyDude]

Welcome to the Progress world of things :awink:

RealHeavyDude.

 

[zacdesai]

Hi again guys.

Having a bit of a problem. Im not sure if its the ODBC driver or the application. As soon as I try to view the tables via the reporting app - i get an " unexptected kernel trap " on the unix server. the server then shuts down.

Any idea ?

Thanks!

 

[Casper]

I assume that the with "the server shuts down", you mean the _sqlsrv2 process?
What is the java version serverside?
Does this always happen?
Does this also happen when you access the database with SQL from another application, for instance Excel or sql explorer?
What does the database log file say?

Regards,
Casper.

 

[zacdesai]

Hi again,

The Sco Unix server shuts down. it does a memory dump and I have to physically restart the server.
the server is not running java at all. I am accessing the Unix server via a windows XP pc with Progress installed and configured.

Ive tried connecting via Sql explorer. i wasnt sure what to run - so I ran this ..
select * from data_dictionary - the same thing happened . the server did a memory dump and shut down - does sql explorer use the ODBC to connect ?

Progress database admin connected no problem. I am not sure where to find the database log file - but I have generated a sql trace log on the odbc driver.

odbcad32 df4-df8 ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0007CF38

odbcad32 df4-df8 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0x0007CF38 ( 0x00a01540)

odbcad32 df4-df8 ENTER SQLSetEnvAttr
SQLHENV 00A01540
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003
SQLINTEGER -6

odbcad32 df4-df8 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 00A01540
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003 (BADMEM)
SQLINTEGER -6

Thanks again!

 

[RealHeavyDude]

1. AFAIK, the SQL Explorer uses JDBC - but that should not not matter.
2. Do you have a table called data_dictionary in your database?
3. AFAIK, you must specify the schema ( most likely PUB ). Therefore your SQL statement should look something like SELECT * FROM "PUB.data_dictionary". (Don't know if you need the quotes in any case but I always use them.)
4. Please be aware, the SQL Explorer is Java - so, if the database contains table and field names which contains a dash - it will have problems (maybe crash or produce the behavior you see) unless you quote them properly.

HTH, RealHeavyDude.

 

[Casper]

Is that SCO as in SCO OpenServer? If so then forget it, no sql components available for sco open server. You need to switch back then to sql89 access (yes 20+ year ago standard). You need to buy a driver for this.

Casper.

 

[zacdesai]

Hi again ,

Our software vendor, has sent me a Progress 9.1E sp4 update in an attempt to see if we can get pass this problem.

Can you please point me in the direction for some information on updating the sp4 on unix - or is it a case of just copying over the files into the relevant directories .


Thanks
Zakaria

 

[Casper]

Didn't you see my question/remarks?

Casper.

 

[zacdesai]

Hi Casper ,

Oh yeh , sorry ....

Yes it is Sco openserver 5.0.7 . So are you saying its hopeless ??

What are my options then ?

Thanks
Zak

 

[Casper]

KB P18365:
http://progress.atgnow.com/esprogress/Group.jsp?bgroup=progress&id=P18365

And the corresoponding way to get SQL access working on SCO (sql89):
KB 17204:
http://progress.atgnow.com/esprogress/Group.jsp?bgroup=progress&id=17204

HTH,

Casper

 

[zacdesai]

Casper, youre a legend! thanks

quick question then im out of your hair ....


is the OIbroker the same as the secondary broker I already have started ?


Thanks :blush:

 

[RealHeavyDude]

No.

The SQL92 goes via ODBC or JDBC directly to a server which can handle SQL clients. Usually these are the ones started by the secondary login broker. The ability of handling SQL92 with a dedicated SQL engine on the database level, correct me if I am wrong, was introduced in Progress V9 on most, but not all plattforms - for example SCO Unix.

The OIB (open interface broker) is some intermediate "thing" to which the SQL89 client connects via ODBC. The OIBroker, if you want to, translates the SQL89 requests so that the a 4GL database server can handle them. This was introduced back in good ole Progress V8 days, to my knowledge, as the first attempt to open up the Progress database to ODBC. From my exeperience not very stable and a sometimes lousy performance.

Regards, RealHeavyDude.

 

[Reddy P]

Your parameters are not set correctly.
You need to count -Mn as follows:
-Mn = #4GL servers + #SQL servers + 1 for each extra broker.
So in your case (with -Mn set to 5) you need to set -Mpb on the primary broker to 3 and -Mpb on the secondary broker to 1.

I also wonder why you have such a large -Ma. I tend to max at 5 for Ma.
If you only want 1 SQL connection you can set -Ma to 1 on the secondary broker.
But I would go as follows:

primary broker:
-Ma 5
-Mi 1
-Mn 14
-Mpb 12
-ServerType 4GL
-n 71
Secondary sql broker
-Ma 5
-Mi 1
-Mpb 1
-ServerType SQL

presuming you only use remote client connections.

Casper.

Hi , I have smae kind of setup mentioned above . I would like to know who are all the users connected to sql alone (Excluding progress 4GL users). could you please help ??