Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Unanswered: Defining a CLOB column - UDB AIX v7.x

    I have created a table with clob column and a long tablespace for it to go in.
    However when I try to use the LOAD utility it truncates the column at 32K. The data is stored in a simple text file.

    I thought I have done everything correctly...but obviously I missed something...

    If somebody could take a look at the following SQL I would much appreciate it.

    Here is my SQL:

    CREATE TABLESPACE RBTEMPLATES_TS
    MANAGED BY DATABASE USING (FILE '/udb/dmscontainers/rbtemplates' 1M)
    ;

    CREATE LONG TABLESPACE RBTEMPLATELONG_TS
    MANAGED BY DATABASE USING (FILE '/udb/dmscontainers/rbtemplateslong' 1M)
    ;

    CREATE TABLE DB2ADMIN.RBTEMPLATES
    (TEMPLATE_NM CHAR(30) NOT NULL
    ,TEMPLATE_INSTANCE CHAR(20) NOT NULL
    ,EFFECTIVE_DT DATE NOT NULL
    ,TEMPLATE CLOB(50000) NOT LOGGED
    ,PRIMARY KEY(TEMPLATE_NM,TEMPLATE_INSTANCE,EFFECTIVE_DT))
    IN RBTEMPLATES_TS LONG IN RBTEMPLATELONG_TS
    ;

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by duffMan
    I have created a table with clob column and a long tablespace for it to go in.
    However when I try to use the LOAD utility it truncates the column at 32K. The data is stored in a simple text file.

    I thought I have done everything correctly...but obviously I missed something...

    If somebody could take a look at the following SQL I would much appreciate it.

    Here is my SQL:

    CREATE TABLESPACE RBTEMPLATES_TS
    MANAGED BY DATABASE USING (FILE '/udb/dmscontainers/rbtemplates' 1M)
    ;

    CREATE LONG TABLESPACE RBTEMPLATELONG_TS
    MANAGED BY DATABASE USING (FILE '/udb/dmscontainers/rbtemplateslong' 1M)
    ;

    CREATE TABLE DB2ADMIN.RBTEMPLATES
    (TEMPLATE_NM CHAR(30) NOT NULL
    ,TEMPLATE_INSTANCE CHAR(20) NOT NULL
    ,EFFECTIVE_DT DATE NOT NULL
    ,TEMPLATE CLOB(50000) NOT LOGGED
    ,PRIMARY KEY(TEMPLATE_NM,TEMPLATE_INSTANCE,EFFECTIVE_DT))
    IN RBTEMPLATES_TS LONG IN RBTEMPLATELONG_TS
    ;
    You missed the most important one... your LOAD command!

    If you're loading from a non-delimited ASCII file "The total length of any record, including LOBs, cannot exceed 32KB." (from the Data Movement guide).

    Is that possibly what you're running into?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Apr 2003
    Posts
    4
    It is a tildy delimited file with one of the columns > 32K.....



    LOAD FROM ./RBTEMPLATES.dat OF DEL MODIFIED BY pagefreespace=0 totalfreespace=0
    coldel~ MESSAGES db2load.msg REPLACE INTO DB2ADMIN.RBTEMPLATES STATISTICS NO CO
    PY NO INDEXING MODE AUTOSELECT;

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by duffMan
    It is a tildy delimited file with one of the columns > 32K.....



    LOAD FROM ./RBTEMPLATES.dat OF DEL MODIFIED BY pagefreespace=0 totalfreespace=0
    coldel~ MESSAGES db2load.msg REPLACE INTO DB2ADMIN.RBTEMPLATES STATISTICS NO CO
    PY NO INDEXING MODE AUTOSELECT;
    The documentation seems to suggest the 32K restriction is only for positional ASCII, but I'm pretty sure it applies to any ASCII file where lobsinfile isn't used.

    I'm almost 100% certain it's your LOAD that's truncating, the column definition looks fine.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Apr 2003
    Posts
    4
    I externalized my LOBs in a separate file and it worked....

    Thanks for you help..much appreciated.


    Now I get to test the performance of this yippeeee!

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by duffMan
    I externalized my LOBs in a separate file and it worked....

    Thanks for you help..much appreciated.


    Now I get to test the performance of this yippeeee!
    No problem, good luck!
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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