Hi all.

I am running a stored procedure in a loop -- this Stored procedure
inserts one record at a time.

After inserting about 1326 records, my thread crashes and I get the
following error:
"Got database error. [DB2/NT] SQL0805N Package "NULLID.SYSLH203
0X5359534C564C3031" was not found. SQLSTATE=51002"

Here's the body of the stored procedure.

===================
create PROCEDURE db2.sp_add_log
(
IN PLAYEROID_in CHAR(36),
IN PLAYTIME_in CHAR(23),
IN CONTENTNAME_in VARCHAR (1024),
IN CONTENTOID_in CHAR(36),
IN REASON_in VARCHAR (256),
IN DATAFIELD_in VARCHAR (256)
)
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SIGNAL SQLSTATE '02444';

SET PLAYTIME_in = REPLACE(PLAYTIME_in,'T',' ');

insert into DB2.PLAYLOGS_
(PLAYEROID_, PLAYTIME_,CONTENTNAME_,CONTENTOID_,REASON_, DATAFIELD_)
values
(PLAYEROID_in, PLAYTIME_in, CONTENTNAME_in, CONTENTOID_in, REASON_in,
DATAFIELD_in);
END

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

Any ideas what this error message might mean?

I found out from the IBM web-site what Error 51002 means:
51002 The package corresponding to an SQL statement execution request
was not found.

What package is it referring to? How could I suddenly (after inserting
1326 records) need this package NULLID.SYSLH203 0X5359534C564C3031 ?

My primary key is a composite key created by combining the 1st 3 fields
-- could it be that it got a NULL value for one of those fields and
this is DB2's way of telling me about the problem ?

I found somebody else who had a similar grievance:
http://www.lazydba.com/db2/2__5491.html -- it says that I was probably
not using the correct way to open/close cursors....

Thanks,
-Anil

anilcool@gmail.com wrote:
> Hi all.
>
> I am running a stored procedure in a loop -- this Stored procedure
> inserts one record at a time.
>
> After inserting about 1326 records, my thread crashes and I get the
> following error:
> "Got database error. [DB2/NT] SQL0805N Package "NULLID.SYSLH203
> 0X5359534C564C3031" was not found. SQLSTATE=51002"
>
> Here's the body of the stored procedure.
>
> ===================
> create PROCEDURE db2.sp_add_log
> (
> IN PLAYEROID_in CHAR(36),
> IN PLAYTIME_in CHAR(23),
> IN CONTENTNAME_in VARCHAR (1024),
> IN CONTENTOID_in CHAR(36),
> IN REASON_in VARCHAR (256),
> IN DATAFIELD_in VARCHAR (256)
> )
> LANGUAGE SQL
> BEGIN
> DECLARE SQLSTATE CHAR(5);
> DECLARE not_found CONDITION FOR SQLSTATE '02000';
> DECLARE EXIT HANDLER FOR not_found
> SIGNAL SQLSTATE '02444';
>
> SET PLAYTIME_in = REPLACE(PLAYTIME_in,'T',' ');
>
> insert into DB2.PLAYLOGS_
> (PLAYEROID_, PLAYTIME_,CONTENTNAME_,CONTENTOID_,REASON_, DATAFIELD_)
> values
> (PLAYEROID_in, PLAYTIME_in, CONTENTNAME_in, CONTENTOID_in, REASON_in,
> DATAFIELD_in);
> END
>
> ======================
>
> Any ideas what this error message might mean?
>
> I found out from the IBM web-site what Error 51002 means:
> 51002 The package corresponding to an SQL statement execution request
> was not found.
>
> What package is it referring to? How could I suddenly (after inserting
> 1326 records) need this package NULLID.SYSLH203 0X5359534C564C3031 ?
>
> My primary key is a composite key created by combining the 1st 3 fields
> -- could it be that it got a NULL value for one of those fields and
> this is DB2's way of telling me about the problem ?
>
> I found somebody else who had a similar grievance:
> http://www.lazydba.com/db2/2__5491.html -- it says that I was probably
> not using the correct way to open/close cursors....
I have a hard time believing that the stored proc is at fault.
What happens if you CALL an EMPTY procedure (BEGIN END) ro skip teh CALL
all together?
What does your app do in side the loop? Do you allocate cursor handles
(and not free them)?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>> Hi all.
>>
>> I am running a stored procedure in a loop -- this Stored procedure
>> inserts one record at a time.
>>
>> After inserting about 1326 records, my thread crashes and I get the
>> following error:
>> "Got database error. [DB2/NT] SQL0805N Package "NULLID.SYSLH203
>> 0X5359534C564C3031" was not found. SQLSTATE=51002"
>>
>> Here's the body of the stored procedure.
>>

See this link:
http://www-1.ibm.com/support/docview...id=swg21208123

Also, make sure you have upgraded to at least FP10.

Hi Serge,

You said: "Allocating cursor handles and not free them" -- lemme try
and understand what you mean by that.

I have another stored procedure where I declare 4 cursors with 4
different select statements... then based on the received parameter
value I either OPEN cursor1 OR cursor2 ... and so on. Is this ok?

Or maybe you can give me an example of how one can allocate cursors and
not free them.

Thanks,
-Anil

anilcool@gmail.com wrote:
> Hi Serge,
>
> You said: "Allocating cursor handles and not free them" -- lemme try
> and understand what you mean by that.
>
> I have another stored procedure where I declare 4 cursors with 4
> different select statements... then based on the received parameter
> value I either OPEN cursor1 OR cursor2 ... and so on. Is this ok?
>
> Or maybe you can give me an example of how one can allocate cursors and
> not free them.
Am I correct that these cursors are defined WITH RETURN?
If so do you consume and close the cursors in the application?

Cheers
Serge

PS: I was referring to allocating cursors in CLI. A CLI package allows
you only so many statement handles at a time.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

<anilcool@gmail.com> wrote in message
news:1139990872.867209.128720@g44g2000cwa.googlegr oups.com...
> Hi Serge,
>
> You said: "Allocating cursor handles and not free them" -- lemme try
> and understand what you mean by that.
>
> I have another stored procedure where I declare 4 cursors with 4
> different select statements... then based on the received parameter
> value I either OPEN cursor1 OR cursor2 ... and so on. Is this ok?
>
> Or maybe you can give me an example of how one can allocate cursors and
> not free them.
>
> Thanks,
> -Anil
>

If you read the link I posted, you will see that creating 4 cursors at one
time may exhaust the resources and you need to create more packages.

Here's the stored procedure that I have...

This could very well be 4 different stored procedures.. the reason I
have them clubbed as one is so that all the code can stay at one place.


====================
create PROCEDURE DB2.sp_get_logs ( IN starttime TIMESTAMP,
IN endtime TIMESTAMP,
IN TypeOfLog VARCHAR(15),
IN oidObjectType CHARACTER(1),
IN oid CHARACTER(36)
)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
select playeroid_, playtime_, contentname_, contentoid_, reason_,
datafield_ from db2.playlogs_ where playtime_ >= starttime and
playtime_ <= endtime and playeroid_ = oid ;

DECLARE c2 CURSOR WITH RETURN FOR
select playeroid_, playtime_, contentname_, contentoid_, reason_,
datafield_ from db2.playlogs_ where playtime_ >= starttime and
playtime_<= endtime and contentoid_ = oid ;

DECLARE c3 CURSOR WITH RETURN FOR
select contentname_, contentoid_, COUNT(*) As TotalPlayed_ from
db2.playlogs_ where playtime_ >= starttime and playtime_ <= endtime and
playeroid_ = oid GROUP BY contentoid_, contentname_;

DECLARE c4 CURSOR WITH RETURN FOR
select contentname_, contentoid_, COUNT(*) As TotalPlayed_ from
db2.playlogs_ where playtime_ >= starttime and playtime_ <= endtime and
contentoid_ = oid GROUP BY contentoid_, contentname_;


if ( TypeOfLog = 'playlog' ) Then -- For playlog queries --
if ( oidObjectType = 'P' ) Then
OPEN c1;
else
if ( oidObjectType = 'C' ) Then
OPEN c2;
end if;
end if;
else
if ( TypeOfLog = 'playlogsummary' ) Then
if ( oidObjectType = 'P' ) Then
OPEN c3;
else
if ( oidObjectType = 'C' ) Then
OPEN c4;
end if;
end if;
end if;
end if;
END

Serge Rielau wrote:

> anilcool@gmail.com wrote:
>> Hi all.
>>
>> I am running a stored procedure in a loop -- this Stored procedure
>> inserts one record at a time.
>>
>> After inserting about 1326 records, my thread crashes and I get the
>> following error:
>> "Got database error. [DB2/NT] SQL0805N Package "NULLID.SYSLH203
>> 0X5359534C564C3031" was not found. SQLSTATE=51002"
>>
>> Here's the body of the stored procedure.
>>
>> ===================
>> create PROCEDURE db2.sp_add_log
>> (
>> IN PLAYEROID_in CHAR(36),
>> IN PLAYTIME_in CHAR(23),
>> IN CONTENTNAME_in VARCHAR (1024),
>> IN CONTENTOID_in CHAR(36),
>> IN REASON_in VARCHAR (256),
>> IN DATAFIELD_in VARCHAR (256)
>> )
>> LANGUAGE SQL
>> BEGIN
>> DECLARE SQLSTATE CHAR(5);
>> DECLARE not_found CONDITION FOR SQLSTATE '02000';
>> DECLARE EXIT HANDLER FOR not_found
>> SIGNAL SQLSTATE '02444';
>>
>> SET PLAYTIME_in = REPLACE(PLAYTIME_in,'T',' ');
>>
>> insert into DB2.PLAYLOGS_
>> (PLAYEROID_, PLAYTIME_,CONTENTNAME_,CONTENTOID_,REASON_, DATAFIELD_)
>> values
>> (PLAYEROID_in, PLAYTIME_in, CONTENTNAME_in, CONTENTOID_in, REASON_in,
>> DATAFIELD_in);
>> END
>>
>> ======================
>>
>> Any ideas what this error message might mean?
>>
>> I found out from the IBM web-site what Error 51002 means:
>> 51002 The package corresponding to an SQL statement execution request
>> was not found.
>>
>> What package is it referring to? How could I suddenly (after inserting
>> 1326 records) need this package NULLID.SYSLH203 0X5359534C564C3031 ?
>>
>> My primary key is a composite key created by combining the 1st 3 fields
>> -- could it be that it got a NULL value for one of those fields and
>> this is DB2's way of telling me about the problem ?
>>
>> I found somebody else who had a similar grievance:
>> http://www.lazydba.com/db2/2__5491.html -- it says that I was probably
>> not using the correct way to open/close cursors....
> I have a hard time believing that the stored proc is at fault.
> What happens if you CALL an EMPTY procedure (BEGIN END) ro skip teh CALL
> all together?
> What does your app do in side the loop? Do you allocate cursor handles
> (and not free them)?
>
> Cheers
> Serge

Hi!

I get this errors also with regular SELECT statements.
Then I run the following command from db2cmd:
db2jdbcbind.exe -url jdbc:db2://SERVER:50000/DATABASE_NAME -user DB_USERNAME
-password DB_PASSWORD -size 300

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~