LOB Locator Limit Exceeded

[tigr]

I am trying to read BLOBs from a large table (i.e., greater than 34K
rows) using Java and the IBM JDBC driver (actually through an
Application server).
I get the following:

[IBM][CLI Driver][DB2/LINUX] SQL0429N The maximum number of
concurrent LOB locators has been exceeded. SQLSTATE=54028

I understand that the LOB locators are pointers to the BLOBs, but is
there any way to free them explicitly, or get the server to do it ?

I have tried the workarounds (e.g., cursor hold/commit, set the driver
property materializeLobData to true, read out as binary stream or
bytes, etc.) None of these work.

It is hard to believe that there isn't a way around this. Wouldn't
the situation I have be quite common when working with large datasets
(e.g., spatial data ) ?

Any advice would be graciously accepted...

Thanks...

Mike

[PM \(pm3iinc-nospam\) CGO]

as far as i know, you can have 32000 concurrent locators.
There is a FREE locator statment you may try.
"tigr" <se*********@yahoo.com> a écrit dans le message de
news:17**************************@posting.google.c om...

I am trying to read BLOBs from a large table (i.e., greater than 34K
rows) using Java and the IBM JDBC driver (actually through an
Application server).
I get the following:

[IBM][CLI Driver][DB2/LINUX] SQL0429N The maximum number of
concurrent LOB locators has been exceeded. SQLSTATE=54028

I understand that the LOB locators are pointers to the BLOBs, but is
there any way to free them explicitly, or get the server to do it ?

I have tried the workarounds (e.g., cursor hold/commit, set the driver
property materializeLobData to true, read out as binary stream or
bytes, etc.) None of these work.

It is hard to believe that there isn't a way around this. Wouldn't
the situation I have be quite common when working with large datasets
(e.g., spatial data ) ?

Any advice would be graciously accepted...

Thanks...

Mike

[Knut Stolze]

tigr wrote:

I am trying to read BLOBs from a large table (i.e., greater than 34K
rows) using Java and the IBM JDBC driver (actually through an
Application server).
I get the following:

[IBM][CLI Driver][DB2/LINUX] SQL0429N The maximum number of
concurrent LOB locators has been exceeded. SQLSTATE=54028

I understand that the LOB locators are pointers to the BLOBs, but is
there any way to free them explicitly, or get the server to do it ?

I have tried the workarounds (e.g., cursor hold/commit, set the driver
property materializeLobData to true, read out as binary stream or
bytes, etc.) None of these work.

It is hard to believe that there isn't a way around this. Wouldn't
the situation I have be quite common when working with large datasets
(e.g., spatial data ) ?

If you would be using embedded SQL with EXEC SQL ..., then you have the FREE
LOCATOR statement to explicitly free any LOB locators you handled.

With CLI, things are a bit different. CLI uses locators under the covers to
fetch the LOB data for you. For performance reasons, those locators are by
default not freed right away. But you can change that behaviour by adding
the following line to you sqllib/cfg/db2cli.ini file

PATCH2=50

That will tell CLI to free a LOB locators when the next LOB is to be
fetched. You shouldn't hit the 32K limit that way.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[chi]

"Knut Stolze" wrote:

tigr wrote:

I am trying to read BLOBs from a large table (i.e., greater

than 34K

rows) using Java and the IBM JDBC driver (actually through

an

Application server).
I get the following:

[IBM][CLI Driver][DB2/LINUX] SQL0429N The maximum number of
concurrent LOB locators has been exceeded. SQLSTATE=54028

I understand that the LOB locators are pointers to the

BLOBs, but is

there any way to free them explicitly, or get the server to

do it ?

I have tried the workarounds (e.g., cursor hold/commit, set

the driver

property materializeLobData to true, read out as binary

stream or

bytes, etc.) None of these work.

It is hard to believe that there isn’t a way around this.

Wouldn’t

the situation I have be quite common when working with large

datasets

(e.g., spatial data ) ?

If you would be using embedded SQL with EXEC SQL ..., then you
have the FREE
LOCATOR statement to explicitly free any LOB locators you
handled.

With CLI, things are a bit different. CLI uses locators under
the covers to
fetch the LOB data for you. For performance reasons, those
locators are by
default not freed right away. But you can change that
behaviour by adding
the following line to you sqllib/cfg/db2cli.ini file

PATCH2=50

That will tell CLI to free a LOB locators when the next LOB is
to be
fetched. You shouldn’t hit the 32K limit that way.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

I have the same SQL0429N error. However I’m using DB2 Version 7.2.5
here. Would patch2=50 apply to this DB2 version?

As I need to confirm the applicabity before I can try the setting,
please help.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/DB2-LOB-Loca...ict132970.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=496515

[Knut Stolze]

chi wrote:

"Knut Stolze" wrote:

> tigr wrote:
>

> > I am trying to read BLOBs from a large table (i.e., greater

> than 34K

> > rows) using Java and the IBM JDBC driver (actually through

> an

> > Application server).
> > I get the following:
> >
> > [IBM][CLI Driver][DB2/LINUX] SQL0429N The maximum number of
> > concurrent LOB locators has been exceeded. SQLSTATE=54028
> >
> > I understand that the LOB locators are pointers to the

> BLOBs, but is

> > there any way to free them explicitly, or get the server to

> do it ?

> >
> > I have tried the workarounds (e.g., cursor hold/commit, set

> the driver

> > property materializeLobData to true, read out as binary

> stream or

> > bytes, etc.) None of these work.
> >
> > It is hard to believe that there isn’t a way around this.

> Wouldn’t

> > the situation I have be quite common when working with large

> datasets

> > (e.g., spatial data ) ?

>
> If you would be using embedded SQL with EXEC SQL ..., then you
> have the FREE
> LOCATOR statement to explicitly free any LOB locators you
> handled.
>
> With CLI, things are a bit different. CLI uses locators under
> the covers to
> fetch the LOB data for you. For performance reasons, those
> locators are by
> default not freed right away. But you can change that
> behaviour by adding
> the following line to you sqllib/cfg/db2cli.ini file
>
> PATCH2=50
>
> That will tell CLI to free a LOB locators when the next LOB is
> to be
> fetched. You shouldn’t hit the 32K limit that way.
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena

I have the same SQL0429N error. However I’m using DB2 Version 7.2.5
here. Would patch2=50 apply to this DB2 version?

As I need to confirm the applicabity before I can try the setting,
please help.

As far as I can see here:
http://www-306.ibm.com/software/data...htm#Header_856
this option is not available on V7. (I just followed the link from the DB2
Information Center to get to this site.)

If you want to compare the list with v8, go here:
http://www-306.ibm.com/software/data...cli/patch2.htm

p.s: Given that V7 goes out of service at the end of this month, it might be
a good idea to upgrade to V8...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[chi]

"Knut Stolze" wrote:

chi wrote:

"Knut Stolze" wrote:

*> > tigr wrote:
*> >
**> > > I am trying to read BLOBs from a large
table (i.e., greater
*> > than 34K
**> > > rows) using Java and the IBM JDBC driver
(actually through
*> > an
**> > > Application server).
**> > > I get the following:
**> > >
**> > > [IBM][CLI Driver][DB2/LINUX] SQL0429N The
maximum number of
**> > > concurrent LOB locators has been exceeded.
SQLSTATE=54028
**> > >
**> > > I understand that the LOB locators are
pointers to the
*> > BLOBs, but is
**> > > there any way to free them explicitly, or
get the server to
*> > do it ?
**> > >
**> > > I have tried the workarounds (e.g., cursor
hold/commit, set
*> > the driver
**> > > property materializeLobData to true, read
out as binary
*> > stream or
**> > > bytes, etc.) None of these work.
**> > >
**> > > It is hard to believe that there isn’t a
way around this.
*> > Wouldn’t
**> > > the situation I have be quite common when
working with large
*> > datasets
**> > > (e.g., spatial data ) ?
*> >
*> > If you would be using embedded SQL with EXEC SQL
..., then you
*> > have the FREE
*> > LOCATOR statement to explicitly free any LOB
locators you
*> > handled.
*> >
*> > With CLI, things are a bit different. CLI uses
locators under
*> > the covers to
*> > fetch the LOB data for you. For performance
reasons, those
*> > locators are by
*> > default not freed right away. But you can change
that
*> > behaviour by adding
*> > the following line to you sqllib/cfg/db2cli.ini
file
*> >
*> > PATCH2=50
*> >
*> > That will tell CLI to free a LOB locators when the
next LOB is
*> > to be
*> > fetched. You shouldn’t hit the 32K limit that way.
*> >
*> > --
*> > Knut Stolze
*> > Information Integration
*> > IBM Germany / University of Jena

I have the same SQL0429N error. However I’m using DB2

Version 7.2.5

here. Would patch2=50 apply to this DB2 version?

As I need to confirm the applicabity before I can try the

setting,

please help.

As far as I can see here:
http://www-306.ibm.com/software/data...htm#Header_856
this option is not available on V7. (I just followed the link
from the DB2
Information Center to get to this site.)

If you want to compare the list with v8, go here:
http://www-306.ibm.com/software/data...cli/patch2.htm

p.s: Given that V7 goes out of service at the end of this
month, it might be
a good idea to upgrade to V8...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Thanks for your suggestion. However, it’s a long way before we can
upgrade v7 to v8. I found there’s LOBCACHESIZE which may be helpful
to temperary solve the problem. The thing is I’m not sure the value I
should set for the parameter. There are mainly 4 CLOB types in a DB
table and takes up 1M size for each CLOB.

Can you help to tell me how to estimate the value for this parameter,
please?

Many thanks.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/DB2-LOB-Loca...ict132970.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=499804

[Knut Stolze]

chi wrote:

As far as I can see here:
http://www-306.ibm.com/software/data...htm#Header_856
this option is not available on V7. (I just followed the link
from the DB2
Information Center to get to this site.)

If you want to compare the list with v8, go here:
http://www-306.ibm.com/software/data...cli/patch2.htm

p.s: Given that V7 goes out of service at the end of this
month, it might be
a good idea to upgrade to V8...

Thanks for your suggestion. However, it’s a long way before we can
upgrade v7 to v8. I found there’s LOBCACHESIZE which may be helpful
to temperary solve the problem. The thing is I’m not sure the value I
should set for the parameter. There are mainly 4 CLOB types in a DB
table and takes up 1M size for each CLOB.

Can you help to tell me how to estimate the value for this parameter,
please?

You might want to read here (V8 documentation):

http://publib.boulder.ibm.com/infoce...d/r0010868.htm

I think that explains rather well the meaning of the parameter. In your
case, you would need to set the LobCacheSize to 4MB or more (assuming that
the column was declared as CLOB(4M).

In general, there are only two ways to free a LOB locator:

(1) call an explicit FREE LOCATOR
(2) run a COMMIT

CLI uses locators under the covers and you can't run a FREE LOCATOR
yourself. So the patch2=50 is available in V8 to tell DB2 to free a
locator before the next lob is retrieved.

That leaves you with firing a COMMIT. You could open the cursor using WITH
HOLD, fetch 32000 rows/lobs (resulting in 32000 LOB locators being
concurrently used by the application), then run a COMMIT and all the
locators will be freed and you can start fetching again where new locators
will be allocated again.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena