Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    121

    Unanswered: large object question

    If we define lob or blob with some specific size, but if it uses only few for somw rows not the full size, does db2 consume full size or whatever allocated ?

    ( For example in case of varchar(10) ,if you insert only 5 letter string like 'abcde' it only consumes varchar(5) , does this appy to lob/clob/blob also ? )

    TIA.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    IIRC, it will occupy only the required amount of disk space

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Keep in mind that DB2 does not use bufferpools for LOB data, so all reads and writes are synchronous I/O to disk. It is better to use varchar if possible to store such objects unless they will not fit, or performance is not important. I have clearly seen a degradation in performance with tables that have LOB columns using SQL Statement Monitoring snapshots.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2003
    Posts
    121

    any suggestio on BP tunning for this

    for lob is there any specific bufferpoold should be created ?
    what is your experience ?
    TIA.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by udbdb2
    for lob is there any specific bufferpoold should be created ?
    Quote Originally Posted by Marcus_A
    Keep in mind that DB2 does not use bufferpools for LOB data
    You probably could create a separate tablespace for your LOBS using file containers - in that case the file system cache will help.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't think that DB2 can use bufferpools for LOB objects, becasue they are too large and don't fit in 4K, 8K, 16K, or 32K chunks.

    In fact, the LOB objects for the DB2 catalog are stored in the CATALOGCACHE_SZ memory heap. The regular catalog table data are in the SYSCATSPACE tablespace and its associated bufferpool.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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