LOB Locators and DB2CLI

[Laurent]

Error While fetching BLOBs from a table using DB2CLI :

------------------------------------
SQL0429N
The maximum number of concurrent LOB locators has been exceeded.
Explanation: A maximum of 32,000 concurrent LOB locators per unit of
work, is supported by DB2.
User Response: Modify the program so that it requires fewer concurrent
LOB locators and try again.
sqlcode: -429
sqlstate: 54028
------------------------------------

My problem is that I don't use LOB LOCATORS to extract BLOBs,
I simply bind sql column FIELD_BLOB with SQL_C_BINARY
and fetch chunks with SQLGetData API.
Is DB2 creating lob locators implicitly ?
Any idea ?

[Knut Stolze]

Laurent wrote:

Error While fetCLIXng BLOBs from a table using DB2CLI :

------------------------------------
SQL0429N
The maximum number of concurrent LOB locators has been exceeded.
Explanation: A maximum of 32,000 concurrent LOB locators per unit of
work, is supported by DB2.
User Response: Modify the program so that it requires fewer concurrent
LOB locators and try again.
sqlcode: -429
sqlstate: 54028
------------------------------------

My problem is that I don't use LOB LOCATORS to extract BLOBs,
I simply bind sql column FIELD_BLOB with SQL_C_BINARY
and fetch chunks with SQLGetData API.
Is DB2 creating lob locators implicitly ?

CLI uses LOB locators internally to fetch the LOB data. For performance
reasons, those locators are not freed right away. This is most likely the
cause of your problem.

You can add the following to the [common] section of your db2cli.ini file:

PATCH2=50

That will change the behavior of CLI to close the locators right away and,
thus, you should not run into tha 32K-limit.

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

[Laurent]

Knut Stolze <st****@de.ibm.com> wrote in message news:<cd**********@fsuj29.rz.uni-jena.de>...

Laurent wrote:

Error While fetCLIXng BLOBs from a table using DB2CLI :

------------------------------------
SQL0429N
The maximum number of concurrent LOB locators has been exceeded.
Explanation: A maximum of 32,000 concurrent LOB locators per unit of
work, is supported by DB2.
User Response: Modify the program so that it requires fewer concurrent
LOB locators and try again.
sqlcode: -429
sqlstate: 54028
------------------------------------

My problem is that I don't use LOB LOCATORS to extract BLOBs,
I simply bind sql column FIELD_BLOB with SQL_C_BINARY
and fetch chunks with SQLGetData API.
Is DB2 creating lob locators implicitly ?

CLI uses LOB locators internally to fetch the LOB data. For performance
reasons, those locators are not freed right away. This is most likely the
cause of your problem.

You can add the following to the [common] section of your db2cli.ini file:

PATCH2=50

That will change the behavior of CLI to close the locators right away and,
thus, you should not run into tha 32K-limit.

Thanks for your answer !
Question : is it possible to close locators by using explicit
transaction even for SELECTs ?
I mean, instead of putting My Engine in AUTOCOMMIT Mode when I read,
can I switch it to AUTOCOMMIT OFF, then read 100 blobs, then COMMIT -
Will such a commit ensure locator cleanup ?

[Knut Stolze]

Laurent wrote:

Knut Stolze <st****@de.ibm.com> wrote in message
news:<cd**********@fsuj29.rz.uni-jena.de>...

Laurent wrote:

> Error While fetCLIXng BLOBs from a table using DB2CLI :
>
> ------------------------------------
> SQL0429N
> The maximum number of concurrent LOB locators has been exceeded.
> Explanation: A maximum of 32,000 concurrent LOB locators per unit of
> work, is supported by DB2.
> User Response: Modify the program so that it requires fewer concurrent
> LOB locators and try again.
> sqlcode: -429
> sqlstate: 54028
> ------------------------------------
>
> My problem is that I don't use LOB LOCATORS to extract BLOBs,
> I simply bind sql column FIELD_BLOB with SQL_C_BINARY
> and fetch chunks with SQLGetData API.
> Is DB2 creating lob locators implicitly ?

CLI uses LOB locators internally to fetch the LOB data. For performance
reasons, those locators are not freed right away. This is most likely
the cause of your problem.

You can add the following to the [common] section of your db2cli.ini
file:

PATCH2=50

That will change the behavior of CLI to close the locators right away
and, thus, you should not run into tha 32K-limit.

Thanks for your answer !
Question : is it possible to close locators by using explicit
transaction even for SELECTs ?
I mean, instead of putting My Engine in AUTOCOMMIT Mode when I read,
can I switch it to AUTOCOMMIT OFF, then read 100 blobs, then COMMIT -
Will such a commit ensure locator cleanup ?

Yes, it should free the locators.

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