Results 1 to 3 of 3

Thread: Host variables

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Host variables

    We're encountering an issue with working with CLOB's. We're doing a basic insert into a table that has a CLOB column. Sometimes the CLOB is fairly tame in size (10k-15k), and once in a while they're fairly large (30k-60k).

    When the CLOB is greater than 32K, the insert fails. Found some documentation which mentions that a LOB can't be larger than 32K, and if it is, it needs to be handled via assignment of host variables . I've seen host variables before (:xyz), but haven't actually worked with them.

    How can one use host variables within an SQL stored procedure?

    Thanks!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Feb 2004
    Posts
    24

    Re: Host variables

    I'm sure that lob's can be greater than 1gb and much more.
    the host vars u mean are lob var (kind of call by value)
    or lob locator vars (kind call by reference). if u define a table
    containing a lob column i think u need to create an auxilliary table too.
    the first part of a lob 'object' is stored in the base table and the rest is stored in the auxilliary table. but be carefull with logging and large lob's !!! i'm not sure but i think i read about an capability to store lob outside db2 and a reference to the external location within a db2 table.

    hope this will help

    regards

    marc gaines

    Originally posted by ansonee
    We're encountering an issue with working with CLOB's. We're doing a basic insert into a table that has a CLOB column. Sometimes the CLOB is fairly tame in size (10k-15k), and once in a while they're fairly large (30k-60k).

    When the CLOB is greater than 32K, the insert fails. Found some documentation which mentions that a LOB can't be larger than 32K, and if it is, it needs to be handled via assignment of host variables . I've seen host variables before (:xyz), but haven't actually worked with them.

    How can one use host variables within an SQL stored procedure?

    Thanks!

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    In DB2 for OS/390, yes, there are different structures which house an auxiliary index and also house the actual LOB. In LOB tables, an additional column is created which contains a pointer to the row in the auxiliary table. Here's what IBM says my problem is:

    SQL0102N The string constant beginning with
    "'SUkqANiYAAAmoCA3+QyQ1Bzj5DJCFmQybZrkMgGdQVsDKw45 DUcgcwLwQyQbvynFODI+I" is too long.

    Explanation:

    One of the following has occurred:
    The string constant beginning with "<string>" has a length
    greater than 32672 bytes. Character strings with lengths greater
    than 32672 bytes or graphic strings with lengths greater than 16336
    characters can be specified only through assignment from host
    variables. Note that other servers in the DB2 family of products
    may specify a different size limit for character strings. Consult
    the documentation for the appropriate DB2 product for details.

    sqlcode : -102

    sqlstate : 54002

    The column this is being inserted into is a CLOB(1M) - plenty of room for a 52K CLOB...

    I'm out of ideas here...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •