Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2014
    Posts
    13

    Unanswered: db2batch -w switch and import operation

    Hello
    I am using db2 10.5 with fixpack 4. What i try to achieve is to measure time of importing varius size of blob and measure their read time. I am using table test with columns id of integer type and data of blob with length 2147483647.

    Now to the point: for measure reading i am using db2batch - works fine but... i keep getting cli that readed row was truncated. Currently biggest file in table has 12 MB and while running db2batch im using: db2batch -d SAMPLE -a ADMINISTRATOR -f c:\batch.sql -r c:\results.txt -w 2147483647. According to documentation -w is right thing to use if we want to specify how long row can be read but it doesnt seems to be working for me ;/

    Second thing: For import i am using command: import from c:\import.dta of del lobs from c:\images modified by lobsinfile insert into administrator."test"

    that command works fine in command line but while trying to use it in db2batch i get error regarding backslash.. so my question is - is there a way to "escape" this character so it will be processed by db2batch or is there any other way to make command like that work in db2batch?

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    db2batch is more for SQL and xquery statements (and not for commands such as import, which establish their own connections).

    If performance is your consideration, then why are you using IMPORT (instead of LOAD , or Ingest at worst) ?

    Additionally for performance, ensure your table is defined with LONG IN ... tablespace clauses to separate data and lobs , and ensure the long tablespace has pagesize 32k and also has file system caching disabled for that long tablespace.

  3. #3
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by db2mor View Post
    db2batch is more for SQL and xquery statements (and not for commands such as import, which establish their own connections).

    If performance is your consideration, then why are you using IMPORT (instead of LOAD , or Ingest at worst) ?

    Additionally for performance, ensure your table is defined with LONG IN ... tablespace clauses to separate data and lobs , and ensure the long tablespace has pagesize 32k and also has file system caching disabled for that long tablespace.
    I dont optimize for performance, just checking it, thats why i am using import as its fine for me. If import cant be used in db2batch what about that -w switch? Which keeps truncating columns during select even if -w is provided way bigger than actuall column size?

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Full disclosure needed: what's in your db2batch input file, the queries , the DDL , which column(s) get truncated, any CLI* codes etc.

  5. #5
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by db2mor View Post
    Full disclosure needed: what's in your db2batch input file, the queries , the DDL , which column(s) get truncated, any CLI* codes etc.
    In db2batch input file is just:
    SELECT "data" FROM ADMINISTRATOR."test"
    In first post i wrote how i am starting db2batch.
    Since one column gets selected, just this one is being truncated

    CLI:
    (-99999): [IBM][CLI Driver] CLI0002W SQLSTATE=01004

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The exact DDL ?

  7. #7
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by db2mor View Post
    The exact DDL ?
    CREATE TABLE "ADMINISTRATOR"."test" (
    "id" INTEGER,
    "data" BLOB(2147483647) INLINE LENGTH 312
    )

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Seems that that db2batch -w option behaves differently for blobs. V10.5 FP4 / Linux x86 / utf-8 db / utf-8 locale.
    If I substr the blob column and use a length less or equal to the value of the -w then I don't get truncation CLI0002W.
    I notice some changes in this area (IC88620), perhaps there are still bugs outstanding here but the docs have the phrase "Data is truncated to this width when displayed, unless the data cannot be truncated" (docs don't define clearly on this page when data cannot be truncated).
    You might want to open a PMR.

  9. #9
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by db2mor View Post
    Seems that that db2batch -w option behaves differently for blobs. V10.5 FP4 / Linux x86 / utf-8 db / utf-8 locale.
    If I substr the blob column and use a length less or equal to the value of the -w then I don't get truncation CLI0002W.
    I notice some changes in this area (IC88620), perhaps there are still bugs outstanding here but the docs have the phrase "Data is truncated to this width when displayed, unless the data cannot be truncated" (docs don't define clearly on this page when data cannot be truncated).
    You might want to open a PMR.
    OK but using substr its just maks 32567 (ot what is that value) anyway so cant read blob using it either ;/

    Othery way around then - is there a way to measure time of import and export operation? Tried with event monitor but not sure if it captured those ;/
    Last edited by xitzee; 11-14-14 at 04:52.

Posting Permissions

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