Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

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

    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
    Last edited by grofaty; 01-25-12 at 03:36.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    this is not possible with char
    this is the case for varchar
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    not that I am aware off. maybe others have different ideas ??
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    view?

    create view v1 as select rtrim(col1) from tab1

    and let the the app select from v1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by sathyaram_s View Post
    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 View Post
    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.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    grofaty,

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

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In DB2 compare 'aaa ' will test the same as 'aaa'.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by tonkuma View Post
    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 View Post
    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 View Post
    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 View Post
    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?
    Last edited by grofaty; 01-25-12 at 10:31.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by grofaty View Post
    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.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by grofaty View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by grofaty View Post
    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 Post
    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.

Posting Permissions

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