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

    Unanswered: Insert files to db2 table

    Hello,
    at the start id like to say that i indeed was looking for my answer around - just couldnt find anything that works or is explained enough (mabe im just too dumb ;/)

    I need help regarding loading/inserting files to db2 table - to blob column. I need to insert images, videos etc so files relatively big (not few KB but MB).
    What is important i need the file to reside in database - not just pointer to file or something like that.
    Currently i am using db 10.5 on Windows.

    So far i came with such solution:
    CALL SYSPROC.ADMIN_CMD ('import from c:\foty\import.txt of del modified by lobsinfile insert into ADMINISTRATOR."test"');
    but despite it being processed correctly it got rejected (probably because file is bigger than 32K?). Ofc import.txt file has path to file which i want to be loaded.
    So ive read some stuff about LOBS FROM command instead of lobsinfile but really cant get it to work ;/
    Any detailes help would really really appreciated.
    My table is simple, just one field with id of integer type and 2nd column "data" of blob type and size 2048

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so what is the error you get
    this worked for me
    C:\work>db2 import from test.dta of del lobs from . modified by lobsinfile insert into ttt

    C:\work>cat test.dta
    1,c:\work\test.lob
    C:\work>
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    More facts needed...

    When asking for help never write "got rejected", always show the exact command and exact result (the error message and error number), using copy/paste if possible.

    If you get an error code you don't understand then show also what lines get added to the db2diag.log for the import.

    Specify LOBS FROM ... to make it explicit the location of the lob file.
    Show the exact content of one line of the file you want to import, we need to sanity check the LLS.
    Show the content of the filesystem at c:\foty and below.
    Show how you created the lob file(s).

  4. #4
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by przytula_guy View Post
    so what is the error you get
    this worked for me
    C:\work>db2 import from test.dta of del lobs from . modified by lobsinfile insert into ttt

    C:\work>cat test.dta
    1,c:\work\test.lob
    C:\work>
    Well it works and doesnt works at once.. well my file is jpg extension, dont know if that matters and has 200 KB.. execution of:
    CALL SYSPROC.ADMIN_CMD ('import from c:\foty\import.dta of del lobs from c:\foty modified by lobsinfile insert into ADMINISTRATOR."test"')

    showed just:
    Query execution time => 9 s: 375 ms

    no errors whatsover but select from that table shows nothing at all.. in data studio there is result tab and there is showed:
    ROWS_READ ROWS_SKIPPED ROWS_INSERTED ROWS_UPDATED ROWS_REJECTED ROWS_COMMITTED MSG_RETRIEVAL MSG_REMOVAL
    --------- ------------ ------------- ------------ ------------- -------------- ------------- -----------
    1 0 0 0 1 1 NULL NULL

    thats what i meant by "it got rejected". There is no error showed at all.

    To db2mor:
    file i want to import has just one line:
    1, c:\foty\ch6_MagicShop.jpg

    Content of c:\foty is just about 100 .jpg files with size from 167 KB to 12 MB
    Show how you created the lob file(s): what do you mean by it?

    EDIT:
    Ok there was one thing which i fixed already, column id had generate always and i wanted to load data to id. But still it gives error:
    SQLCODE -302 SQLSTATE=22001
    Last edited by xitzee; 11-10-14 at 14:29.

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.

    Get your import statement working at the command-line first (i.e not by using sysproc.admin_cmd), - on the db2-server if possible (otherwise you will need to configure a db2-client appropriately first).

    Once you get the command-line import working, then try getting sysproc.admin_cmd working, but remember that you have to ensure that the file(s) to be loaded are *local to the db2-server* in this case, which means that c:\foty\import.dta is relative to the db2-server file systems and not your workstation.

  6. #6
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by db2mor View Post
    SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.

    Get your import statement working at the command-line first (i.e not by using sysproc.admin_cmd), - on the db2-server if possible (otherwise you will need to configure a db2-client appropriately first).

    Once you get the command-line import working, then try getting sysproc.admin_cmd working, but remember that you have to ensure that the file(s) to be loaded are *local to the db2-server* in this case, which means that c:\foty\import.dta is relative to the db2-server file systems and not your workstation.
    I received this error while executing import from command line. Also db2 is on disk C like everything else, just testing it so far on virtual environment with one disk.

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If you are not at DB2 V10.5 fixpack 4, then I suggest you apply that fixpack.

  8. #8
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by db2mor View Post
    If you are not at DB2 V10.5 fixpack 4, then I suggest you apply that fixpack.
    Its already implemented and has been for the whole time:

    SERVICE_LEVEL FIXPACK_NUM
    ----------------- -----------
    DB2 v10.5.400.191 4


    edit: quick test with really small file - like 2 KB - got inserted without problems, but trying to insert 167 KB file still throws error posted yesterday
    Last edited by xitzee; 11-11-14 at 05:58.

  9. #9
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Show the lines that get added to the db2diag.log when you perform the *command-line* import that gives the SQL0302N.
    Show also the exact command line and the exact output.

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Show the lines that get added to the db2diag.log when you perform the *command-line* import that gives the SQL0302N.
    Show also the exact command line and the exact output.

  11. #11
    Join Date
    Sep 2014
    Posts
    13
    Fixed and working. Turned out size of the blob column was too low - it accepted max 2 KBs. Changed to 2147483647 allows to insert any size (well to the limit ofc). Thanks for help

Tags for this Thread

Posting Permissions

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