Tip #3
9/5/2019

  • Tip

    Use delprioritychar to suppress row delimiter in a string

  • If your string in your DEL file to import include a row delimiter, this will cause trouble.
    For example, the following is a DEL file to import has two rows: 
    C:\Program Files\IBM\SQLLIB\BIN>type t1.del
1,"abc",123
2,"de
f",456
    Note the second column of second row include a row delimiter. This will cause trouble when importing: 
    >db2 import from t1.del of del insert into t1
SQL3109N  The utility is beginning to load data from file "t1.del".

SQL3137W  Row "2" is too short.  At least one input value being loaded to a
non-nullable column is missing.  The row was not loaded.

SQL3120W  The field value in row "3" and column "1" cannot be converted to a
INTEGER value, but the target column is not nullable.  The row was not loade

SQL3110N  The utility has completed processing.  "3" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "3".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "3" rows were processed from the input file.  "1" rows were
successfully inserted into the table.  "2" rows were rejected.


Number of rows read         = 3
Number of rows skipped      = 0
Number of rows inserted     = 1
Number of rows updated      = 0
Number of rows rejected     = 2
Number of rows committed    = 3
    In this case, you need to use "modified by delprioritychar" to put the priority of char
    ahead of row delimiter. 
    >db2 import from t1.del of del modified by delprioritychar insert into t1
SQL3109N  The utility is beginning to load data from file "t1.del".

SQL3110N  The utility has completed processing.  "2" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "2".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "2" rows were processed from the input file.  "2" rows were
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 2
Number of rows skipped      = 0
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 2