JDBC application SQL -805 for NULLID.SYSLN203 -- Help please

Vamsi Gonella

JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
Hi,

We have a Java application running on Websphere App server V5 on AIX V5.2 and accessing DB2 V7 on Z/OS using JDBC Universal driver type 2.

The Application is getting SQL -805 error for the package NULLID.SYSLN203 (and some times for the package NULLID.SYSLN303). The application does open a large number of cursors as pert of a search criteria.

We did some research and understood that the NULLID.SYSLHXYY and NULLID.SYSLNXYY (X=1,2,3,4 YY=00,01,02) are the packages used by the SERVER application and we can increase the number of packages to a higher value (i.e. YY=03 to FF).

But now, the missing link is how much we need to increase the package number ?

Could someone please through some light on what these packages are and how many statements they can handle and what is the optimum number.


Thanks in advance,
Vamsi







***********************************************************************
This e-mail and any files transmitted with it are intended
solely for the use of the addressee. This e-mail may
contain confidential and/or legally privileged information.
Any review, transmission, disclosure, copying, or any action
taken or not taken, by other than the intended recipient, in
reliance on the information, is prohibited. If you received
this e-mail in error, notify the sender and delete this e-mail
(and any accompanying material) from your computer and
network. In addition, please be advised that 21st Century
Insurance Group reserves the right to monitor, access and
review all messages, data and images transmitted through
our electronic mail system. By using our e-mail system, you
consent to this monitoring.
***********************************************************************


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bart Mertens

Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
(in response to Vamsi Gonella)
Vamsi,

I don't know if I understand your question correctly. As far as I know the
sqlcode -805 means that the package is not found and should not have
anything to do with the number of open cursors.

I couldn't find the SYSLN203 / SYSLH203 in any of our DB2 V7 subsystems (x02
is the highest number we got). They are probably added through some
APAR/PTF.

Please keep me informed about your findings as we are currently looking into
using websphere with db2


met vriendelijke groeten,
Bart Mertens
Database Administrator DB2
CZ Actief in Gezondheid - Tilburg
* (013) 593 8256
* <mailto:[login to unmask email]%20> [login to unmask email]

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


==================
De informatie verzonden met dit e-mail bericht is vertrouwelijk en
uitsluitend bestemd voor de geadresseerde. Indien bovenstaand e-mailbericht
niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het
e-mailbericht te retourneren aan de verzender en het origineel en eventuele
kopieen te verwijderen en te vernietigen. Gebruik van deze informatie door
anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging,
verspreiding en/of verstrekking van deze informatie aan derden is niet
toegestaan. CZ staat niet in voor de juiste en volledige overbrenging van de
inhoud van een verzonden e-mail, noch voor tijdige ontvangst daarvan.

==================
The information contained in this communication is confidential and may be
legally privileged. If you are not the intended recipient of this
communication please return the communication to the sender and delete and
destroy all copies. It is intended solely for the use of the individual or
entity to whom it is addressed and others authorised to receive it. If you
are not the intended recipient you are hereby notified that any disclosure,
copying, distribution or taking any action in reliance on the contents of
this information is strictly prohibited and may be unlawful. CZ health
insurance is neither liable for the proper and complete transmission of the
information contained in this communication nor for any delay in its
receipt.

===================


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Gert van der Kooij

Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
(in response to Bart Mertens)

Corey McCardle

Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
(in response to Gert van der Kooij)
Good Morning Vamsi,

Here's what I'm Thinking....

I didn't see these packages until UDB v8. I am wondering if your using DB2 Connect v8 to communicate to UDB on OS390. Go to the v8 USERs guide page 243 it will explain your package in question (with little detail). Like you said an -805 is a not found, verify those packages exist on OS390. The initial connection (as long as the person is a SYSADMIN) would have created and bound those packages in OS390. If the person wasn't, you may need to bind those packages (db2clibh.bnd and db2clibn.bnd) from your remote connection to OS390, but make sure that an administrator runs them.

I can't tell you how many or the optimum number

Good Luck to you


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Gonella, Vamsi
Sent: Tuesday, July 13, 2004 7:24 PM
To: [login to unmask email]
Subject: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please


Hi,

We have a Java application running on Websphere App server V5 on AIX V5.2 and accessing DB2 V7 on Z/OS using JDBC Universal driver type 2.

The Application is getting SQL -805 error for the package NULLID.SYSLN203 (and some times for the package NULLID.SYSLN303). The application does open a large number of cursors as pert of a search criteria.

We did some research and understood that the NULLID.SYSLHXYY and NULLID.SYSLNXYY (X=1,2,3,4 YY=00,01,02) are the packages used by the SERVER application and we can increase the number of packages to a higher value (i.e. YY=03 to FF).

But now, the missing link is how much we need to increase the package number ?

Could someone please through some light on what these packages are and how many statements they can handle and what is the optimum number.


Thanks in advance,
Vamsi







***********************************************************************
This e-mail and any files transmitted with it are intended
solely for the use of the addressee. This e-mail may
contain confidential and/or legally privileged information.
Any review, transmission, disclosure, copying, or any action
taken or not taken, by other than the intended recipient, in
reliance on the information, is prohibited. If you received
this e-mail in error, notify the sender and delete this e-mail
(and any accompanying material) from your computer and
network. In addition, please be advised that 21st Century
Insurance Group reserves the right to monitor, access and
review all messages, data and images transmitted through
our electronic mail system. By using our e-mail system, you
consent to this monitoring.
***********************************************************************
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Ediger

Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
(in response to Corey McCardle)
Hi Vamsi,

We have had this problem. It occurs when you have a very very large
number of dynamic statements in your program, or, as in our case, the
program logic mistakenly Prepares the dynamic sql statement a large
number of times. JDBC will increase the number for the package name as
necessary to include the required number of sections to support the
number of prepared statements. A Package name of SYSSN200 would be
using 65 sections, SYSLN201 would use 385 sections. These should be
enough to support most large programs if you take care to handle the
dynamic preps correctly. If you are going up to SYSLN203, your could,
of course, bind a package for this but I would suggest you look at your
program logic to make sure your are doing the least number of PREPARES
necessary.
Just an interesting note (this from DB2 Information Center on the Web),
the "L" or "S" in the 4th position of the name signifies Large or Small
Packages, "H" or "N" in the 5th position signifies Cursor with HOLD or
No Cursor with HOLD, and the digit 0, 1, 2, 3, or 4 in the 6th position
of the name signifies isolation level (0-NC, 1-UR, 2-CS, 3-R2, 4-RR).

Mark Ediger

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Gonella, Vamsi
Sent: Tuesday, July 13, 2004 7:24 PM
To: [login to unmask email]
Subject: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please


Hi,

We have a Java application running on Websphere App server V5 on AIX
V5.2 and accessing DB2 V7 on Z/OS using JDBC Universal driver type 2.

The Application is getting SQL -805 error for the package
NULLID.SYSLN203 (and some times for the package NULLID.SYSLN303). The
application does open a large number of cursors as pert of a search
criteria.

We did some research and understood that the NULLID.SYSLHXYY and
NULLID.SYSLNXYY (X=1,2,3,4 YY=00,01,02) are the packages used by the
SERVER application and we can increase the number of packages to a
higher value (i.e. YY=03 to FF).

But now, the missing link is how much we need to increase the package
number ?

Could someone please through some light on what these packages are and
how many statements they can handle and what is the optimum number.


Thanks in advance,
Vamsi







***********************************************************************
This e-mail and any files transmitted with it are intended
solely for the use of the addressee. This e-mail may
contain confidential and/or legally privileged information.
Any review, transmission, disclosure, copying, or any action
taken or not taken, by other than the intended recipient, in
reliance on the information, is prohibited. If you received
this e-mail in error, notify the sender and delete this e-mail
(and any accompanying material) from your computer and
network. In addition, please be advised that 21st Century
Insurance Group reserves the right to monitor, access and
review all messages, data and images transmitted through
our electronic mail system. By using our e-mail system, you
consent to this monitoring.
***********************************************************************
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vamsi Gonella

Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
(in response to Mark Ediger)
Hi Mark and all who replied,

Thanks for your valuable information.

We have decided to do a code review and mean while bump up the package count to 20.

We hope this gives us some time before we completely understand and arrive at an optimum code and package number.


Regards
Vamsi


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Mark Ediger
Sent: Wednesday, July 14, 2004 9:17 AM
To: [login to unmask email]
Subject: Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help
please


Hi Vamsi,

We have had this problem. It occurs when you have a very very large
number of dynamic statements in your program, or, as in our case, the
program logic mistakenly Prepares the dynamic sql statement a large
number of times. JDBC will increase the number for the package name as
necessary to include the required number of sections to support the
number of prepared statements. A Package name of SYSSN200 would be
using 65 sections, SYSLN201 would use 385 sections. These should be
enough to support most large programs if you take care to handle the
dynamic preps correctly. If you are going up to SYSLN203, your could,
of course, bind a package for this but I would suggest you look at your
program logic to make sure your are doing the least number of PREPARES
necessary.
Just an interesting note (this from DB2 Information Center on the Web),
the "L" or "S" in the 4th position of the name signifies Large or Small
Packages, "H" or "N" in the 5th position signifies Cursor with HOLD or
No Cursor with HOLD, and the digit 0, 1, 2, 3, or 4 in the 6th position
of the name signifies isolation level (0-NC, 1-UR, 2-CS, 3-R2, 4-RR).

Mark Ediger

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Gonella, Vamsi
Sent: Tuesday, July 13, 2004 7:24 PM
To: [login to unmask email]
Subject: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please


Hi,

We have a Java application running on Websphere App server V5 on AIX
V5.2 and accessing DB2 V7 on Z/OS using JDBC Universal driver type 2.

The Application is getting SQL -805 error for the package
NULLID.SYSLN203 (and some times for the package NULLID.SYSLN303). The
application does open a large number of cursors as pert of a search
criteria.

We did some research and understood that the NULLID.SYSLHXYY and
NULLID.SYSLNXYY (X=1,2,3,4 YY=00,01,02) are the packages used by the
SERVER application and we can increase the number of packages to a
higher value (i.e. YY=03 to FF).

But now, the missing link is how much we need to increase the package
number ?

Could someone please through some light on what these packages are and
how many statements they can handle and what is the optimum number.


Thanks in advance,
Vamsi







***********************************************************************
This e-mail and any files transmitted with it are intended
solely for the use of the addressee. This e-mail may
contain confidential and/or legally privileged information.
Any review, transmission, disclosure, copying, or any action
taken or not taken, by other than the intended recipient, in
reliance on the information, is prohibited. If you received
this e-mail in error, notify the sender and delete this e-mail
(and any accompanying material) from your computer and
network. In addition, please be advised that 21st Century
Insurance Group reserves the right to monitor, access and
review all messages, data and images transmitted through
our electronic mail system. By using our e-mail system, you
consent to this monitoring.
***********************************************************************
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Shyam Peri

Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
(in response to Vamsi Gonella)
1:37,
Hi vamsi,
We some time back had faced this problem. It happened because we left too
many open statement unclosed(say abt 50-60-depeding on other parameters) .
So a little bit a code optimization helped us in resolving this issue.

Warm regards
Shyam Peri.


> Hi Mark and all who replied,
>
> Thanks for your valuable information.
>
> We have decided to do a code review and mean while bump up the package
> count to 20.
>
> We hope this gives us some time before we completely understand and
> arrive at an optimum code and package number.
>
>
> Regards
> Vamsi
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> Behalf Of Mark Ediger
> Sent: Wednesday, July 14, 2004 9:17 AM
> To: [login to unmask email]
> Subject: Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help
> please
>
>
> Hi Vamsi,
>
> We have had this problem. It occurs when you have a very very large
> number of dynamic statements in your program, or, as in our case, the
> program logic mistakenly Prepares the dynamic sql statement a large
> number of times. JDBC will increase the number for the package name as
> necessary to include the required number of sections to support the
> number of prepared statements. A Package name of SYSSN200 would be
> using 65 sections, SYSLN201 would use 385 sections. These should be
> enough to support most large programs if you take care to handle the
> dynamic preps correctly. If you are going up to SYSLN203, your could,
> of course, bind a package for this but I would suggest you look at your
> program logic to make sure your are doing the least number of PREPARES
> necessary.
> Just an interesting note (this from DB2 Information Center on the Web),
> the "L" or "S" in the 4th position of the name signifies Large or Small
> Packages, "H" or "N" in the 5th position signifies Cursor with HOLD or
> No Cursor with HOLD, and the digit 0, 1, 2, 3, or 4 in the 6th position
> of the name signifies isolation level (0-NC, 1-UR, 2-CS, 3-R2, 4-RR).
>
> Mark Ediger
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> Behalf Of Gonella, Vamsi
> Sent: Tuesday, July 13, 2004 7:24 PM
> To: [login to unmask email]
> Subject: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please
>
>
> Hi,
>
> We have a Java application running on Websphere App server V5 on AIX
> V5.2 and accessing DB2 V7 on Z/OS using JDBC Universal driver type 2.
>
> The Application is getting SQL -805 error for the package
> NULLID.SYSLN203 (and some times for the package NULLID.SYSLN303). The
> application does open a large number of cursors as pert of a search
> criteria.
>
> We did some research and understood that the NULLID.SYSLHXYY and
> NULLID.SYSLNXYY (X=1,2,3,4 YY=00,01,02) are the packages used by the
> SERVER application and we can increase the number of packages to a
> higher value (i.e. YY=03 to FF).
>
> But now, the missing link is how much we need to increase the package
> number ?
>
> Could someone please through some light on what these packages are and
> how many statements they can handle and what is the optimum number.
>
>
> Thanks in advance,
> Vamsi
>
>
>
>
>
>
>
> ***********************************************************************
> This e-mail and any files transmitted with it are intended
>solely for the use of the addressee. This e-mail may
> contain confidential and/or legally privileged information.
> Any review, transmission, disclosure, copying, or any action
> taken or not taken, by other than the intended recipient, in
> reliance on the information, is prohibited. If you received
> this e-mail in error, notify the sender and delete this e-mail
> (and any accompanying material) from your computer and
> network. In addition, please be advised that 21st Century
> Insurance Group reserves the right to monitor, access and
> review all messages, data and images transmitted through
> our electronic mail system. By using our e-mail system, you
> consent to this monitoring.
> ***********************************************************************
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm