Inserting low-values into a varchar column



IBM's flagship relational database management system

Inserting low-values into a varchar column

Postby paulmac1968 » Sun Sep 02, 2012 5:19 pm

I'm having an issue inserting data into a varchar column when there are low-values in the data. In this case, the low-values are valid because it represents a value of zeroes in a comp-3 field. Basically, what is happening is that data after and including the byte with the hex 00 don't get inserted.

Anyone ever have an issue with this?

Thanks.

Paul Mac
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

 

Re: Inserting low-values into a varchar column

Postby dick scherrer » Sun Sep 02, 2012 11:01 pm

Hello,

LOW-VALUES (x'000000') is NOT valid for a comp-3 field. If this is being done in a COBOL program, move ZEROS to the host variable.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Inserting low-values into a varchar column

Postby BillyBoyo » Mon Sep 03, 2012 2:18 am

How did you get low-values into the comp-3 field?

If this is "working" elsewhere, can you show your field definition and the value of compiler option NUMPROC that you are using.

"low-values" is binary zeros, so zero in a binary (COMP, COMP-4, COMP-5) fields. It is not zero in a comp-3 field,

Can you explain exactly what you mean by this:

Basically, what is happening is that data after and including the byte with the hex 00 don't get inserted.


If it helps, show the data you have, with HEX ON, please.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Inserting low-values into a varchar column

Postby GuyC » Mon Sep 03, 2012 2:39 pm

is the table defined as EBCDIC or as UNICODE ?
Anyway, column should be defined as varbinary or as varchar for bit data
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Mon Sep 03, 2012 5:27 pm

BillyBoyo,

The field is defined as PIC 9V999 COMP-3. So when you have a value of 1.00 for example, it'll appear as x'01000F'. The important piece here would be that second byte which is x'00'. That's a low-value.

I'm using a stored procedure to do the actual insert. The cobol program that does the call to it has NUMPROC(MIG).

Finally, what I mean by the "Basically... " statement is this. If you look at the string that I'm trying to put into the varchar column, it's 7500 bytes long. When I examine the stirng in storage before I attempt to commit, all 7500 bytes are there. After the insert I look at the database. Only the first 238 bytes are there. The byte before the low-value I describe above is the last piece of data on the table.

Here's what the data actually looks like in hex

FFFFFFFF000
2013091310F

As I stated earlier, from the second to the last byte you see on, it doesn't make it to the database.

Thx.
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Mon Sep 03, 2012 5:32 pm

GuyC,

The column is defined as LONG VARCHAR.

Thx.

Paul
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby GuyC » Mon Sep 03, 2012 6:31 pm

if it's a C or java stored procedure my guess would be that the hostvariable is defined as a (null-terminated) string.
It still should be defined as LONG VARCHAR FOR BIT DATA.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Inserting low-values into a varchar column

Postby BillyBoyo » Tue Sep 04, 2012 5:05 am

paulmac1968 wrote:BillyBoyo,

The field is defined as PIC 9V999 COMP-3. So when you have a value of 1.00 for example, it'll appear as x'01000F'. The important piece here would be that second byte which is x'00'. That's a low-value.

I'm using a stored procedure to do the actual insert. The cobol program that does the call to it has NUMPROC(MIG).

Finally, what I mean by the "Basically... " statement is this. If you look at the string that I'm trying to put into the varchar column, it's 7500 bytes long. When I examine the stirng in storage before I attempt to commit, all 7500 bytes are there. After the insert I look at the database. Only the first 238 bytes are there. The byte before the low-value I describe above is the last piece of data on the table.

Here's what the data actually looks like in hex

FFFFFFFF000
2013091310F

As I stated earlier, from the second to the last byte you see on, it doesn't make it to the database.

Thx.


Can you show your Cobol definition of the structure to go into the VARCH. Maybe the first 5 sub-fields, and the five sub-fields with the around the comp-3.

The NUMPROC(MIG) is interesting, as it is intended for "migration" from pre-Enterprise to Enterprise Cobol. The presumption being that once the migration is fully "bedded-in" then PFD or NOPFD can be selected.

At the moment, not relevant to your question, as you don't have low-values in the whole comp-3 field, you happen to have one byte of a comp-3 that has X'00' in it.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Wed Sep 05, 2012 1:30 am

GuyC,

I did what you suggested. Changed to a varchar for bit data. However, I'm now getting a -311. In this case, it doesn't make sense since I'm defining a length within the length of the column itself.

Here's the column definition

type_name column_size
LONG VARCHAR FOR BIT DATA 23000

In the program, my host variables are defined as such...
01 VARIN-PAYLOAD-VARCHAR.
49 VARIN-PAYLOAD-VARCHAR-LEN PIC S9(4) USAGE COMP.
49 VARIN-PAYLOAD-VARCHAR-DATA PIC X(23000).


When the insert executes, here are my values...
49 VARIN-PAYLOAD-VARCHAR-LEN > +9999
49 VARIN-PAYLOAD-VARCHAR-DATA > 20A0033809651130913140913C2013 {lots more data after this}

When I call my stored procedure, I use VARIN-PAYLOAD-VARCHAR to populate the column.

So, this seems inconsistent with what the definition of a -311 is.

Any hints??

Thanks.

Paul M
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby BillyBoyo » Wed Sep 05, 2012 2:06 am

Another compiler option, then. What do you have for TRUNC.

Without TRUNC(BIN) your COMP PIC S9(4) can hold a maximum value of +9999 and a minimum of -9999.

If you move greater than 9999 to it, you will get truncation. Exactly how it truncates can depend on what you are MOVEing to it. If you don't have TRUNC(BIN) that'll be at least part of your problem. If so, show what you are MOVEing to the field and we can show what value you'd get after truncation. Could easily explain the situation you started out with.

If you don't have BIN, the best resolution is to make the field COMP-5, which will allow +/-32k-ish, the maximum values that the storage can hold, rather than the PICTURE defines.

If you have BIN, it is something else :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post