-30081 Solution

[cwahlmeier]

I wanted to post this for anyone else who has been in -30081 hell like
me. Our web servers run on the Windows platform and use DB2 Connect to
query our DB2 for z/OS systems. For the last 4 years I have been
getting between 20 and 100 SQL30081N errors a day from our web servers.
That is about a .01% failure rate. This problem has become a huge
political thing with our customers. It is the proverbial needle in a
haystack problem. I have opened two pmr's with IBM, asked questions at
the DB2 Tech Conf, and posted to this and other newsgroups about this
problem. Yet, not one answer.

Well, our network guy figured it out. Ironically, IBM and Microsoft
both know about this problem... On Windows Server 2000 and 2003, the
default available port range for tcp connections is 1025 to 5000.
There is a 4 minute timeout value. So when the DB2 Client seeks to
make a connection to DB2 Connect, it has to find an available port.
Once it finishes, that port is unavailable for 4 minutes. If you have
a busy system, you will get a -30081 if there are not any available
ports.

Both Microsoft and IBM recommend you increase this value by adding the
following registry key on the DB2 Client machine in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\TCPIP\Parameters,
create a new REG_DWORD named MaxUserPort. Set the value anywhere from
32768 to 65536.

Apparently, the Websphere folks know about this:
http://publib.boulder.ibm.com/infoce...v4r0/index.jsp, search
on maxuserport.

The Query Patroller folks know about this:
http://publib.boulder.ibm.com/infoce...w/v8/index.jsp, search on
maxuserport.

The installation of Microsoft Exchange automatically adds the registry
key.

The part that really, really hacks me off is this:
http://support.microsoft.com/kb/196271
Microsoft returns an error to the application when a port is not
available. Why in the world doesn't DB2 give me that information
instead of the generic -30081?!

Here is another idea, add this information to the DB2 Connect or Client
documentation. Websphere and Query Patroller have it...

Hope this helps someone else.

Craig Wahlmeier

[Phil Sherman]

You have nearly 4000 ports available. If they become reusable after a
four minute timeout, then you are averaging just over 16 NEW connections
a second when you get the -30081 errors.

I'd much rather see some form of connection pooling at the client to
eliminate the CPU expense of the connect/disconnect processing. This
will significantly decrease the use of TCP/IP ports and free up CPU
cycles at both the client and the DB2 Connect server.
Phil Sherman

cw********@data-tronics.com wrote:

I wanted to post this for anyone else who has been in -30081 hell like
me. Our web servers run on the Windows platform and use DB2 Connect to
query our DB2 for z/OS systems. For the last 4 years I have been
getting between 20 and 100 SQL30081N errors a day from our web servers.
That is about a .01% failure rate. This problem has become a huge
political thing with our customers. It is the proverbial needle in a
haystack problem. I have opened two pmr's with IBM, asked questions at
the DB2 Tech Conf, and posted to this and other newsgroups about this
problem. Yet, not one answer.

Well, our network guy figured it out. Ironically, IBM and Microsoft
both know about this problem... On Windows Server 2000 and 2003, the
default available port range for tcp connections is 1025 to 5000.
There is a 4 minute timeout value. So when the DB2 Client seeks to
make a connection to DB2 Connect, it has to find an available port.
Once it finishes, that port is unavailable for 4 minutes. If you have
a busy system, you will get a -30081 if there are not any available
ports.

Both Microsoft and IBM recommend you increase this value by adding the
following registry key on the DB2 Client machine in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\TCPIP\Parameters,
create a new REG_DWORD named MaxUserPort. Set the value anywhere from
32768 to 65536.

Apparently, the Websphere folks know about this:
http://publib.boulder.ibm.com/infoce...v4r0/index.jsp, search
on maxuserport.

The Query Patroller folks know about this:
http://publib.boulder.ibm.com/infoce...w/v8/index.jsp, search on
maxuserport.

The installation of Microsoft Exchange automatically adds the registry
key.

The part that really, really hacks me off is this:
http://support.microsoft.com/kb/196271
Microsoft returns an error to the application when a port is not
available. Why in the world doesn't DB2 give me that information
instead of the generic -30081?!

Here is another idea, add this information to the DB2 Connect or Client
documentation. Websphere and Query Patroller have it...

Hope this helps someone else.

Craig Wahlmeier

[cwahlmeier]

Phil,

Each intranet "application" uses a different userid. We have over 1500
different userids. The opportunity to re-use is not as high as you
might like.

Thanks,

Craig
Phil Sherman wrote:

You have nearly 4000 ports available. If they become reusable after a
four minute timeout, then you are averaging just over 16 NEW connections
a second when you get the -30081 errors.

I'd much rather see some form of connection pooling at the client to
eliminate the CPU expense of the connect/disconnect processing. This
will significantly decrease the use of TCP/IP ports and free up CPU
cycles at both the client and the DB2 Connect server.
Phil Sherman

cw********@data-tronics.com wrote:

I wanted to post this for anyone else who has been in -30081 hell like
me. Our web servers run on the Windows platform and use DB2 Connect to
query our DB2 for z/OS systems. For the last 4 years I have been
getting between 20 and 100 SQL30081N errors a day from our web servers.
That is about a .01% failure rate. This problem has become a huge
political thing with our customers. It is the proverbial needle in a
haystack problem. I have opened two pmr's with IBM, asked questions at
the DB2 Tech Conf, and posted to this and other newsgroups about this
problem. Yet, not one answer.

Well, our network guy figured it out. Ironically, IBM and Microsoft
both know about this problem... On Windows Server 2000 and 2003, the
default available port range for tcp connections is 1025 to 5000.
There is a 4 minute timeout value. So when the DB2 Client seeks to
make a connection to DB2 Connect, it has to find an available port.
Once it finishes, that port is unavailable for 4 minutes. If you have
a busy system, you will get a -30081 if there are not any available
ports.

Both Microsoft and IBM recommend you increase this value by adding the
following registry key on the DB2 Client machine in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\TCPIP\Parameters,
create a new REG_DWORD named MaxUserPort. Set the value anywhere from
32768 to 65536.

Apparently, the Websphere folks know about this:
http://publib.boulder.ibm.com/infoce...v4r0/index.jsp, search
on maxuserport.

The Query Patroller folks know about this:
http://publib.boulder.ibm.com/infoce...w/v8/index.jsp, search on
maxuserport.

The installation of Microsoft Exchange automatically adds the registry
key.

The part that really, really hacks me off is this:
http://support.microsoft.com/kb/196271
Microsoft returns an error to the application when a port is not
available. Why in the world doesn't DB2 give me that information
instead of the generic -30081?!

Here is another idea, add this information to the DB2 Connect or Client
documentation. Websphere and Query Patroller have it...

Hope this helps someone else.

Craig Wahlmeier