Recently someone asked me if I knew the easiest way of removing trailing blanks in PL/I. When I asked why it turned out to be because the trailing blanks was to be removed from a DB2 VARCHAR column. This in an old problem which I bumped into many years ago. There are most likely many VARCHAR columns out there stuffed with trailing blanks and of course safely stored by DB2.

If you want to exploit the idea behind a VARCAR column, you must remember that the length of the data must be correctly specified before you perform your INSERT or UPDATE of a VARCHAR. In order to calculate the correct length you must use a routine that counts backwards from the maximum length of your VARCHAR host variable until it meets the first nonblank character. Then it occurred to me that back in DB2 version 6 (I beleive) the STRIP function was introduced. One or two versions later DB2 came with a RTRIM function, which corresponds to a STRIP that only removes trailing blanks. STRIP is able to remove both trailing and leading characters of all kind.

Instead of coding the removal of trailing blanks in your program you let DB2 do the job. Consider table STRIPPED with columns STRIPKEY CHAR(8) and STRIPDATA VARCHAR(254). A traditional INSERT in STRIPPED looks like this:

INSERT INTO STRIPPED (STRIPKEY, STRIPDATA)
VALUES(:stripKey, :stripData)

But it is actually much better to use:

INSERT INTO STRIPPED (STRIPKEY, STRIPDATA)
VALUES(:stripKey, RTRIM(:stripData))

To use RTRIM as above has two advantages. Firstly you do not need to code any removal of trailing blanks in your program and secondly you do not have to worry about the data type of the host variable stripData, just make sure it is for character data and has a length of 254 bytes or less in this example. Just keep your host variable at the same maximum length as your VARCHAR column. Actually I am a bit puzzled why I have not thought of this tip a long time ago.