Thread limitations in PowerCOBOL and moving to LINQ

[Pete Dashwood]

We have found that if a PowerCOBOL application makes more than 21
connections to a SQL Server database, no data is returned.

The SQLSTATE returned is '9999A' or '9999B' which is not a standard
SQLSTATE, but seems to be coming from the Fujitsu ODBC driver.

We have been unable to find any way to configure this, although there
probably is, somewhere.

(The non-industrial strength free version of SQL Server accommodates 64,000
simultaneous connections, so the problem is not with the RDB.)

So, applications that use large numbers of ISAM files may encounter this
problem if they are converted to RDB. (For networked systems "small is
beautiful"...)

It has caused me to re-evaluate the whole use of SQL and ODBC. I believe
this is obsolete technology and I want to move on from it.

I have re-visited some early experiments I did with LINQ and Lamdas. This
doesn't require ADO, OLEDB, or ODBC; instead, it connects directly, using
the Database providers in the .NET framework.

And there is no SQL. (LINQ uses virtually the same syntax to manipulate
lists of objects in memory, XML files, or relational databases). When
accessing RDB, the LINQ to SQL engine generates very efficient SQL because
that is what the RDB requires, but it is transparent to your program. There
is no longer ANY SQL in your code and it is pretty much future proof. It
took me a few days to get used to using the LINQ syntax (after 30 years of
SQL) but now, I can see the beauty of it and I wouldn't go back to SQL.
(LINQ was designed mainly by Anders Hejlsberg, the same guy who did visual
Delphi at Borland, and invented C# at Microsoft. Very good credentials.)

If you want to have a 5 minute overview, take a look here:
http://www.hookedonlinq.com/LinqToSQL5MinuteOVerview.ashx

Although LINQ is available to the .NET languages (and Java) it is not
available to COBOL. (If anyone is using one of the .NET COBOL compilers and
it is documented as supporting LINQ, please correct me.)

There are a number of links on that page which are also interesting. ("How
to write a LINQ query" is a good one...)

I intend to have the PRIMA DAL layer generating and using LINQ (as an option
for clients) by the end of this year.

Pete.
--
"I used to write COBOL...now I can do anything."

[Frederico Fonseca]

The issue here is that ODBC does have a limit on the number of open
connections per process. This has nothing to do with the limit of
active connections the database server has.

from Netcobol manual
sqlstate - sqlcode
9999A -999999990
sqlmsg
The number of connections exceeds the maximum.
The number of connections exceeds the
maximum specified in the COBOL system.
programmer response
Decrease the number of connections. The maximum number of connections
can vary depending on the ODBC environment. Refer to the ODBC
environment manual and take
action.

sqlstate - sqlcode
9999B -999999800
sqlmsg
The specified connection does not exist.
programmer response
SQL statements cannot be executed because the connection is not
active. Check the SQL statement sequence in the program, and take
corrective action on the error.
- This one is likely to happen after the first one happened as it is
trying to use a connection object that has never been created due to
the first error.
One thing on Netcobol that affects this is how you have defined your
connection scope, as it can be process(default), thread or
object-instance


Note that under odbc 3.0 (which is most likely the one you are using
now) the connections now share a pool, and the default max of
connections is 100 - however as the default behaviour now is that when
a connection is closed it to move it to the pool of available
connections instead of being really closed this has a negative effect
on those applications that do not reuse a connection (connection
pooling)

You should enable connection pooling on your apps.
This link has info on this http://support.microsoft.com/kb/169470

As a further effect connections should normally be opened and closed
as soon as possible, and sqls should use the same connection whenever
possible.
Again, and depending on the odbc driver, it may be a requirement to
enable MARS on the connection string once you use connection pooling


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com

[Pete Dashwood]

Since doing more investighation I realize that isn't going to happen.We will
provide a DAL LINQ option but it won't be soon...

There is a HUGE amount of work in this...

>>
>> Pete.
>
>
> The issue here is that ODBC does have a limit on the number of open
> connections per process. This has nothing to do with the limit of
> active connections the database server has.

Yep. We found that out.

>
> from Netcobol manual
> sqlstate - sqlcode
> 9999A -999999990
> sqlmsg
> The number of connections exceeds the maximum.
> The number of connections exceeds the
> maximum specified in the COBOL system.
> programmer response
> Decrease the number of connections. The maximum number of connections
> can vary depending on the ODBC environment. Refer to the ODBC
> environment manual and take
> action.
>

I know this by heart, having read it over and over... :-)

There is no "ODBC Environment Manual" and attempts toconfigure the
connection through ODBC32 have failed.

> sqlstate - sqlcode
> 9999B -999999800
> sqlmsg
> The specified connection does not exist.
> programmer response
> SQL statements cannot be executed because the connection is not
> active. Check the SQL statement sequence in the program, and take
> corrective action on the error.
> - This one is likely to happen after the first one happened as it is
> trying to use a connection object that has never been created due to
> the first error.
> One thing on Netcobol that affects this is how you have defined your
> connection scope, as it can be process(default), thread or
> object-instance

Yes, we tried all of them... :-)

>
>
> Note that under odbc 3.0 (which is most likely the one you are using
> now) the connections now share a pool, and the default max of
> connections is 100 - however as the default behaviour now is that when
> a connection is closed it to move it to the pool of available
> connections instead of being really closed this has a negative effect
> on those applications that do not reuse a connection (connection
> pooling)

One of the reasons for enquiring about passing Object References around is
so that we can pass a reference to an already connected DAL object down to
sub forms.

>
> You should enable connection pooling on your apps.
> This link has info on this http://support.microsoft.com/kb/169470

It's very interesting and thanks for posting it. I'll do some experiments
and let you know what happens.

>
> As a further effect connections should normally be opened and closed
> as soon as possible, and sqls should use the same connection whenever
> possible.

Yes, agreed. Unfortunately, the legacy is written in traditional COBOL
approach with files opened at the beginning (whether they will be used or
not) and closed at the end. When these are converted to DB tables it means
there is a bunch of connections made at the start then held right through
the process. The client is not keen to restructure the application (it is
pretty big).

> Again, and depending on the odbc driver, it may be a requirement to
> enable MARS on the connection string once you use connection pooling

MARS? Not familiar with that.

Thanks very much for your insights, Frederico.

We still haven't solved this problem but I am spending a good deal of time
on it and hope to crack it soon.

[iNFO_rene]

On Friday, September 27, 2013 8:42:13 AM UTC+8, Pete Dashwood wrote:
> We have found that if a PowerCOBOL application makes more than 21
> connections to a SQL Server database, no data is returned.
>
> The SQLSTATE returned is '9999A' or '9999B' which is not a standard
> SQLSTATE, but seems to be coming from the Fujitsu ODBC driver.
>

I am using Microfocus NetExpress v3.10 and they have OpenESQL ODBC driver. Just wondering (for those who use Microfocus), does the Microfocus Cobol ODBC driver act the same?

Haven't tried exposing "some" of the SQL Cobol statements I coded into such test environment so far.

[Pete Dashwood]

I need to update this:

On investigation I found there were 6 real connections (ESQL CONNECT) for
each of the threads so it was really 126 connections. Given a couple for
internal overhead, it looks like 128 is the limit.

Anyone who wants the details should look at this thread:

http://social.msdn.microsoft.com/Forums/vstudio/en-US/d0b2d9ae-e438-42d5-941b-ba51d27d44a8/connection-limitations-in-crt?forum=vcgeneral