Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    14

    Unanswered: Data type for keep the file data

    Hi,

    I'm a newbie for DB2. Could you please suggest about the data type that is proper to keep the file in table?

    Thank you in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Need to know the contents of the file or file types.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2009
    Posts
    14
    I've 2 kinds of data.
    1. just text file
    2. pdf file

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    1. CLOB
    2. BLOB

    If the data was less than about 32K, you could use the following for better performance, but you will need a 32K bufferpool, tablespace, and system temporary tables:

    1. VARCHAR
    2. VARCHAR FOR BIT DATA
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2009
    Posts
    14
    Thank you very much.

  6. #6
    Join Date
    Sep 2009
    Posts
    14
    Hi,

    I've estimated the file and found that we the file size will be around 200 - 300 kb per row so based on your suggestion I'd better used blob right?

    How about the performance for this file size?

    Thank you in advance.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You would use BLOB for binary data (including a PDF), and CLOB for text data. Performance will not as good as regular data since LOB's do not use DB2 bufferpools (memory caching of DB2 tables) and will require synchronous disk I/O required for read and writes. But it may not be that bad depending on your requirements.

    Best option is to define a separate 32K tablespace for LOB's and then use it the Create Table command ("LONG IN TS_XXXX" -- see Create Table command in manual), and make sure that file caching is turned on for that tablespace where the LOB's reside. If the LOB will not ever (or only rarely) be updated after it is inserted (deleted is a separate issue), then use the COMPACT keyword when defining the LOB column.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you have a bunch of small files (smaller than 32K), you can wrap the BLOB inside a structured type and use a large INLINE LENGTH for that type. This will allow you to exploit the buffer pool for short BLOBs (< 32K) while everything longer is treated as a regular BLOB with disk I/O.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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