Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    29

    Unanswered: issue with coldel during a load

    Hi all,
    I'm using DB2 10.5 on Linux.

    I'm trying to have a load reading from a pipe and feeding this pipe with a select in a separate process.
    The problem is that one of the columns to be moved is a varchar that can hold whatever character, so if the load finds the character used as column delimiter in the selected string it will think the column to end there.

    I'm wondering what I can use as column delimiter in such a case, or if there's a way to escape characters in the selected string so that the load won't consider them as column delimiters.

    Thanks for any reply!

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Can you provide an example of what your select statement returns and which modifiers you use with a load command?
    Regards,
    Mark.

  3. #3
    Join Date
    Jan 2012
    Posts
    29
    From shell 1:
    --------------
    mkfifo mypipe;
    db2 "create table tb0(a int, b varchar(7000))";
    db2 "create table tb1(a int not null, b varchar(7000))";
    db2 "insert into tb0 values( 1,'www$%&/()=?^*;,:_-.'";
    db2 "insert into tb0 values( 2,'{}<>347;,:_-.')";
    db2 load from mypipe of del modified by coldel, insert into tb1

    From shell 2:
    --------------
    db2 -x "select a, ',', b from tb0" > mypipe

    The load inserts two rows into tb1 (correct) but the strings are truncated at the first "," (character that I used as coldel):
    For example 'www$%&/()=?^*;,:_-.' is truncated to 'www$%&/()=?^*;'

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try this:

    db2 -x "select a||',\"'||replace(b, '\"', '\"\"')||'\"' from tb0" > mypipe

    The idea is to use some character delimiter (double quotes by default) and to double all its possible occurrences in each string column used in the select statement.
    Load by default can recognize and convert double delimiter to a single one.
    Regards,
    Mark.

Posting Permissions

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