We need to execute a TRIM on each and every column in all our stagging tables before uploading them due to a high chance of spaces coming in all the data.

update tabname set colname=ltrim(rtim(colname) )

Is there any faster way to do this ? Any way i can restrict Extra spaces while inserting into these tables?

  • Is there a reason why the process that is writing the data to the staging table can't do this (instead of relying on the database to do it)? – Ian Bjorhovde Dec 26 '15 at 19:19
  • There are multiple sources of data coming to these tables. Different teams and formats. XML, text, sometimes they push data straight to. These tables – rrrohanb Dec 27 '15 at 0:01

Some thoughts (you don't mention platform or version so I assume LUW on a recent version):

use trim(colname) instead of ltrim(ltrim(colname))

use a before trigger

create trigger trg
before insert on tbl
    set (col1, col2, ..., col_n) = (trim(col1), trim(col2), ..., trim(col_n))
  • Its DB2 LUW 10.5 that we are using as of now. We did take this option into consideration. But did not take it up because the time taken to insert increases by a lot more. – rrrohanb Dec 25 '15 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.