Results 1 to 6 of 6

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 2002
    Location
    Minneapolis, MN
    Posts
    253
    I APOLOGIZE.....THIS IS A REPEAT OF ANOTHER POST. ACCIDENTALLY SUBMITTED TWICE.
    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..."

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    Hi Ansonee,

    Please check the tablespace definition of CLOB tablespace. If it is segmented and the SEGSIZE parm (page size) was only 32K, you might experience the problem.

    Also, the max length of the VARCHAR datatype is usually dictated by the SEGSIZE (page size) value.

    So, I recommend you either to have to insert additional rows for data beyond 32k or try dropping the tablespace and re-create the same with SEGSIZE 64K and see your 64k is absorbed in a single row easily.

    Please let me know how you are doing with this..

  4. #4
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    I've been diggin through the documentation and found references to EXTENTSIZE and PAGE SIZ. You can create a tablespace with EXTENTSIZE of 64K, but I saw that the largest you can make a page is 32K - also holds true for bufferpools.

    Is this what you were referring to: EXTENTSIZE? We're running DB2 Version 8, FixPak 4 on AIX. It looks like SEGSIZE is an option only available z/OS, OS/390....

    Thank You!!
    Last edited by ansonee; 03-01-04 at 18:14.
    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..."

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    Yes. I am referring to page size. In db2 varchar size is always dictated by page size. I am positive that your page size is only 32k


    --------------------------------------------------------------------------
    Originally posted by ansonee
    I've been diggin through the documentation and found references to EXTENTSIZE and PAGE SIZ. You can create a tablespace with EXTENTSIZE of 64K, but I saw that the largest you can make a page is 32K - also holds true for bufferpools.

    Is this what you were referring to: EXTENTSIZE? We're running DB2 Version 8, FixPak 4 on AIX. It looks like SEGSIZE is an option only available z/OS, OS/390....

    Thank You!!

  6. #6
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    If our max page size then is only 32k, then I'm assuming there's no way to work around the issue of CLOB's larger than 32k? What other options are available besides just doing a plain insert?

    We'd rather do it the nice and simple way if at all possible.
    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
  •