Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    3

    Unanswered: New to DB2: Storing large amounts of BLOB Data (up to 2GB per File and >200GB at all)

    Hallo,
    I have a Application which should store large amounts of Files in DB2 Database.
    The Files could be up to 2GB in size and the total size of the file table is estaimated between 200 and 300 GB.
    As I said, I'm new to DB2 so I created the Database with the Wizzard in the Command center (default settings).
    The table is:
    create table FILES
    (ID char(32) not null, FILENAME VARCHAR(255) not null, FILETYPE VARCHAR(255) not null, FILESIZE bigint not null, FILEHASH VARCHAR(50), FILE BLOB(2G) NOT LOGGED COMPACT, primary key (ID))

    I tested it with test files in total 4GB size and it worked.
    Question:
    Does this configuration work for the estimated size?
    Do I need special Database/Tablespace settings for the file table?
    Can you give me an example create script for a Database/table of this size?

    I realy tried to understand the documentation of DB2 but the relations between database/table size and tablespaces etc. is hard to understand.

    The database is DB2 8.1 fixpack 10 on solaris 9.

    Thanks in advance
    hero06

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    blob

    is it really needed to store this data in the db ? will it be parsed, queries executed on ?
    you could store the files local to the db and replace the data in the db with the pointer to this file. when needed, then handling the file instead of the column.
    see devworks for some documents on this subject
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Nov 2006
    Posts
    3
    unfortunatetly the blob storage is a requirement of the customer...
    I know, that this is not the best solution to store/handle large files, but I do have no choice. To refine my questions:
    Does DB2 needs special precautions to handle large databases/tables?
    Is there a limit for the size of my FILE table when creating the database with
    default settings?
    I tried to understand the tablespace thing but for my understanding the tablespace (8,16,32 pagesize) does only count for "normal" data and not for blobs which are stored seperate????
    Thanks in advance
    hero06

  4. #4
    Join Date
    Jun 2006
    Posts
    471

    limit

    see infocenter with sql limits and check :
    Description 4K page size limit 8K page size limit 16K page size limit 32K page size limit
    Most columns in a table 500 1 012 1 012 1 012
    Maximum length of a row including all overhead 4 005 8 101 16 293 32 677
    Maximum size of a table per partition (in gigabytes) 64 128 256 512
    Maximum size of a long DMS table space (in terabytes) c 2
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Nov 2006
    Posts
    3

    Sorry, guyprzytula can't get it ...

    Hallo guyprzytula,

    thanks for your help, but I can't get it to run.
    Ok as a result from your last msg, I need a 32k pagesize tablespace. so I added a tablesapce with command center. It required also a new Bufferpool.
    So now I have the default tablespace "USERSPACE1" which was created by
    default and my new tablespace "BIG_TBLSPACE" which I created now.
    I added the "IN BIG_TBLSPACE" to my FILE table but it will not work.
    I also tried "IN BIG_TBLSPACE LONG IN BIG_TBLSPACE" and
    "IN USERSPACE1 LONG IN BIG_TBLSPACE" but I always get errors
    like: SQL0284N, SQL0285N ...

    Do I need to drop and recreate the whole database?

    Sorry for my ignorance ...

    Here my are my tries..:
    CREATE BUFFERPOOL "BIG_PUFFERPOOL" SIZE 500 PAGESIZE 32768 NOT EXTENDED STORAGE;

    CREATE LARGE TABLESPACE BIG_TBLSPACE IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE
    USING (FILE '/data/data'960000)
    EXTENTSIZE 64
    PREFETCHSIZE 64
    BUFFERPOOL BIG_PUFFERPOOL
    OVERHEAD 11.670000
    TRANSFERRATE 0.140000
    FILE SYSTEM CACHING ;

    create table FILES (ID char(32) not null, TX_VERSION integer not null, FILENAME VARCHAR(255) not null, FILETYPE VARCHAR(255) not null, FILESIZE bigint not null, FILEHASH VARCHAR(50), FILE BLOB(2G) NOT LOGGED COMPACT, primary key (ID)) IN "BIG_TBLSPACE" LONG IN "BIG_TBLSPACE" ;

    create table FILES (ID char(32) not null, TX_VERSION integer not null, FILENAME VARCHAR(255) not null, FILETYPE VARCHAR(255) not null, FILESIZE bigint not null, FILEHASH VARCHAR(50), FILE BLOB(2G) NOT LOGGED COMPACT, primary key (ID)) IN "USERSPACE1" LONG IN "BIG_TBLSPACE" ;

    create table FILES (ID char(32) not null, TX_VERSION integer not null, FILENAME VARCHAR(255) not null, FILETYPE VARCHAR(255) not null, FILESIZE bigint not null, FILEHASH VARCHAR(50), FILE BLOB(2G) NOT LOGGED COMPACT, primary key (ID)) IN "BIG_TBLSPACE" ;

  6. #6
    Join Date
    Jun 2006
    Posts
    471

    tablespace

    statement 2
    create table FILES (ID char(32) not null, TX_VERSION integer not null, FILENAME VARCHAR(255) not null, FILETYPE VARCHAR(255) not null, FILESIZE bigint not null, FILEHASH VARCHAR(50), FILE BLOB(2G) NOT LOGGED COMPACT, primary key (ID)) IN "USERSPACE1" LONG IN "BIG_TBLSPACE" ;
    should do : but : if not indicate the message you get and do not forget
    userspace1 has to be managed by database (DMS) also
    in that case you can create 3 tablespaces : 2 regular 1 long (all dms)
    and create table data in ts1 index in ts2 long in ts3
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

Posting Permissions

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