Is there any way to set automatic trimming of spaces for char data types fields?

grofaty · #1 (**permalink**) 01-25-12, 02:29

Hi,
using db2 v9.5 on Linux. I have a table with following definition (simple sample):

Code:

create table admin.tab1 (col1 char(5))

I have inserted one row:

Code:

insert into admin.tab1 values ('aaa')

I executed select command:

Code:

select col1 from admin.tab1

Result is:

Code:

'aaa  ' --> two spaces at the end.

Java programmer is getting the following code:

Code:

"aaa".equals("aaa  ")

So he is constantly annoyed by trimming spaces, so he has to use trim Java function to get rid of spaces:

Code:

"aaa".equals("aaa  ".trim())

I know I can do the same by executing the following select statement:

Code:

select rtrim(col1) as col1 from admin.tab1

Is there any other way on DB2 to specify in general that all char data types are AUTOMATICALLY trimmed when data are received from DB2?
Or any other idea how to remove the need for trimming?

Thanks

przytula_guy · #2 (**permalink**) 01-25-12, 02:36

this is not possible with char
this is the case for varchar

grofaty · #3 (**permalink**) 01-25-12, 04:09

Yes, I know about varchar not having spaces at the end of string. But is it something on DB2 to generally set trimming for char strings?

przytula_guy · #4 (**permalink**) 01-25-12, 04:39

not that I am aware off. maybe others have different ideas ??

sathyaram_s · #5 (**permalink**) 01-25-12, 07:25

view?

create view v1 as select rtrim(col1) from tab1

and let the the app select from v1

sathyaram_s · #6 (**permalink**) 01-25-12, 07:32

BTW,why do you want to do the comparison in java? why not pass it to db2 in the select sql?

Even if you have to do the selection and then compare, why not do the trim when reading the column value to a local variable? this means all comparisons can go without the trim?

grofaty · #7 (**permalink**) 01-25-12, 08:09

Quote:

Originally Posted by sathyaram_s

BTW,why do you want to do the comparison in java? why not pass it to db2 in the select sql?

This was just one of the use cases when trim is required in applications. Programmers are constantly using this trim function in various situations and they just asked how to avoid this annoying task at all.

Quote:

Originally Posted by sathyaram_s

Even if you have to do the selection and then compare, why not do the trim when reading the column value to a local variable? this means all comparisons can go without the trim?

Like said before, trim done is several different uses in applications. I just thought if there is some "hidden" global DB2 setting that would trim all char values when then are passed to Java application (running on WebSphere). You know one time global trim setting without being annoyed every single time working with char data fields.

tonkuma · #8 (**permalink**) 01-25-12, 08:39

grofaty,

What were asvantages in creating the columns as (fixed)CHAR and tirm automatically when passed to application,
over creationg the columns as VARCHAR?

Marcus_A · #9 (**permalink**) 01-25-12, 08:43

In DB2 compare 'aaa ' will test the same as 'aaa'.

przytula_guy · #10 (**permalink**) 01-25-12, 09:19

but the problem is not the compare
in the cmpy I work for they have the same issue
the problem is when doing display or getting data from different columns together they have the extra blanks in between
they use varchar to resolve this problem
but as I am still from the old-days, I always dis-recommend them to use varchar for very small fields (they even used varchar(1) - with overhead of 2 length bytes)
as dba we decided to force them to use char for fields smaller than 15 but this is not always accepted, as they have middleware that generates sql and they can not manually change the generated sql (and even do not see this sql)
they have a temp-solution for this appl but are looking for a global solution in WAS to resolve this..

grofaty · #11 (**permalink**) 01-25-12, 09:23

Quote:

Originally Posted by tonkuma

What were asvantages in creating the columns as (fixed)CHAR and tirm automatically when passed to application, over creationg the columns as VARCHAR?

As far as I know decision was made years ago and general rule of trump is use char for data fields with less then 255 characters and use varchar when more characters are required. If I remember correctly this was a performance recommendation. Because if using varchar data type every row is required to calculate the size of string before saving data into database (by database engine) this influences (at least in theory - I haven't measured this) CPU performances, because additional CPU cycles are required.

But at the moment I can't influence the existing system by changing data types to varchar. There are ten-thousands columns with char data types, converting to varchar would most probably be a migration nightmare for 24/7 system.

Quote:

Originally Posted by Marcus_A

In DB2 compare 'aaa ' will test the same as 'aaa'.

Yes I know in select statement there can be "where column='aaa' " or "where column='aaa ', but programmers are doing several different comperings (I should probably ask them to post me whole set of problems they are having with trimming).

Quote:

Originally Posted by przytula_guy

they have the same issue the problem is when doing display or getting data from different columns together they have the extra blanks in between

Yes, this is also one of the problem in our company.

Quote:

Originally Posted by przytula_guy

they have a temp-solution for this appl but are looking for a global solution in WAS to resolve this..

That would also be fine if there is some solution at WAS level. I would be very interested in seen this kind of solution. Can you provide more info?

shammat · #12 (**permalink**) 01-25-12, 09:43

Quote:

Originally Posted by grofaty

As far as I know decision was made years ago and general rule of trump is use char for data fields with less then 255 characters and use varchar when more characters are required. If I remember correctly this was a performance recommendation.

This might have been true 20 years ago (and even that I'm not sure about), but I'm pretty sure there is no performance overhead for using a VARCHAR(10) column over using CHAR(10) nowadays.

I would recommend to use VARCHAR to solve your problems rather than finding some obscure workaround.

Marcus_A · #13 (**permalink**) 01-25-12, 10:19

Quote:

Originally Posted by grofaty

As far as I know decision was made years ago and general rule of trump is use char for data fields with less then 255 characters and use varchar when more characters are required. If I remember correctly this was a performance recommendation. Because if using varchar data type every row is required to calculate the size of string before saving data into database (by database engine) this influences (at least in theory - I haven't measured this) CPU performances, because additional CPU cycles are required.

VARCHAR requires a little more CPU, but not much. The main consideration is how much storage is wasted with CHAR, taking into consideration that VARCHAR always requires 2 extra bytes for the length. So you have to figure out how often on average the column width is not fully used to decide. Remember that DB2 does everything in memory (bufferpools) so it is not just a question of disk storage, but also of RAM.

tonkuma · #14 (**permalink**) 01-25-12, 13:09

Quote:

Originally Posted by grofaty

But at the moment I can't influence the existing system by changing data types to varchar. There are ten-thousands columns with char data types, converting to varchar would most probably be a migration nightmare for 24/7 system.

(1) Use view described by sathyaram_s for newly developing applications.

Quote:

Originally Posted by sathyaram_s

view?

create view v1 as select rtrim(col1) from tab1

and let the the app select from v1

(2) Use varchar for new/additional tables.

Those two strategy will not influence existing tables and applications,
and will remove burdon of trimming from newly developing applications.

Extra CPU cycles for varchar may be a little, like pointed out by Marcus_A.
It may be much or less compared with trimming work in applications or in views.

Internet Services

Web Development

Digital Marketing

Consumer Tech