Insert Into DB2 VarChar Field

jtrapat1 used Ask the Experts™
I need to know the best way to insert data into a varchar field in a db2 table to optimize space and performance.
Here's how the db2 field is defined:
    short length;
    char  data[400];
  } purpose;
  char purpose_0[400+1];
Now, I'm trying to insert from a C Program and the dba tells me that there is a lot of wasted space when I do my insert.  
Here's my C code:
I'm reading from a struct and copying to a host variable.
My adds.purpose field is defined as
char purpose[240]
so you can see that there is a lot of space empty after the insert.
Sometimes the value inserted is blank or spaces and it still fills up the entire varchar field.

What is the best way to do this insert from C?
Or, are there some string functions I could use to strip away the trailing spaces before inserting the value?

Thanks in Advance
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

How about this:

  strips trailing blanks from str and returns the new length
int rtrim(char *str) {
  int i;
  if ( str == NULL )
    return -1;

  for ( i = strlen(str) - 1;
        i >= 0 && isspace(str[i]);
        i++ )
    str[i] = '\0';

  return i + 1;


Thanks for the function.
Can you tell me?

Now that I have the exact length of the string, do I need to insert both the length AND the string into the struct?

I know we did something similar with cobol but I've never dealt with varchars in C.

You don't even need the struct (see the programming guide under 'supported datatypes').
The DB2 API can use zero terminated strings. If you
use the struct, you need to fill in the length too.

If you are using embedded SQL, the precompiler should do
everything for you. If you are using the CLI-API, you
have to specify SQL_NTS as last parameter in a call to

The last paragraph in the previous post referred to
the case when you use a zero terminated string.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial