HIGH-VALUE (COBOL) and DB2

[Yan302]

Hi everyone,

I'm working on a windows XP platform with a DB/2 database and I've got a problem when trying to insert (thru COBOL) a field that contains HIGH-VALUE.

Here is my problem illustrated with this little example :

My DB/2 table is created with :

CREATE TABLE PERS (
  RID INTEGER NOT NULL,
  "LAST_NAME"  VARCHAR(30),
  "FIRST_NAME"  VARCHAR(20),
  "AGE"  NUMERIC(3,0),
CONSTRAINT PERS_PK PRIMARY KEY (RID));

and my cobol program (before precompilation with DB2 precompiler) is :

       IDENTIFICATION DIVISION.
       PROGRAM-ID. INSER.
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 WORK-VARS.
         05 HOST-RID PIC S9(9) COMP-5.
         05 HOST-LAST-NAME PIC X(30).
         05 HOST-FIRST-NAME PIC X(20).
         05 HOST-AGE PIC S9(4) COMP-5.
         EXEC SQL END DECLARE SECTION END-EXEC.
         EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
         EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
         EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.

       PROCEDURE DIVISION.
           EXEC SQL
              CONNECT TO sample user db2admin using ******
           END-EXEC.
           MOVE 1 TO HOST-RID.
           MOVE HIGH-VALUES TO HOST-LAST-NAME.
           MOVE "Jean" TO HOST-FIRST-NAME.
           MOVE 33 TO HOST-AGE.
           EXEC SQL
      ****************************************
              INSERT INTO PERS
              (
                "RID", "LAST_NAME", "FIRST_NAME","AGE"
              )
              VALUES
              (
                :HOST-RID, :HOST-LAST-NAME, :HOST-FIRST-NAME, :HOST-AGE
              )
           
           END-EXEC.
           IF NOT SQLCODE = 0 THEN
             DISPLAY "ERROR : SQLCODE " SQLCODE UPON CONSOLE
           ELSE
             EXEC SQL COMMIT END-EXEC
           END-IF.
           STOP RUN.

The error returned is -302 :THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE :

http://publib.boulder.ibm.com/infoc.../n302.htm?resultof="SQLCODE" "sqlcode" "302"

If I replace MOVE HIGH-VALUES TO HOST-LAST-NAME. by MOVE "Dupond" TO HOST-LAST-NAME. or MOVE HIGH-VALUE TO HOST-LAST-NAME(1:15).
the insertion goes well. So it seems that DB/2 interpret a COBOL PIC X(30) that contains HIGH-VALUES as a VARCHAR(60). Does someone knows why ? If it is possible to change that ? Does somebody got a explanation of that strage transformation at the DB/2 side ?

Thanks a lot in advance for your answers.

Best regards,

Yan302

 

[papadba]

So it seems that DB/2 interpret a COBOL PIC X(30) that contains HIGH-VALUES as a VARCHAR(60)

Doubtful. . . A cobol pic x(30) is a db2 var/char(30). Are you by chance using a database that is set up to use dbcs?


What value is in "position-number"?

 

[MarcLodge]

Hi Yan302,
You varchar host variable should be defined as:
01 HOST-LAST-NAME.
03 HOST-LAST-NAME-LEN PIC S9(4) COMP.
03 HOST-LAST-NAME-TEXT PIC X(30).

You should move the data to the TEXT part and the length of the data to the LEN bit.

What is happening at the moment is you have effectively told DB2 that the length of the field is x'FFFF' which if dosen't like, and so therefore gives you the error.

Regards,
Marc Lodge

 

[Mercury2]

Marc,

Ok, so if I correctly follow your reasoning why does the insert work when using

MOVE "Dupond" TO HOST-LAST-NAME. or MOVE HIGH-VALUE TO HOST-LAST-NAME(1:15)

?



Philippe

 

[MarcLodge]

Good question Philippe, and one that off the top of my head I have no answer. The only thing I can say is that the statement as coded by yan302 is definitely wrong.

I also made a mistake in mine in that the 03 levels should in fact be 49 levels, according to the manual.

I'd be interested to hear back from yan302.

 

[mikrom]

Marc is right,
For example look at the section 3.3.2 Variable-length Character Strings here

http://supportline.microfocus.com/Documentation/books/nx30books/dbdtyp.htm

 

[Yan302]

Hi everyone,

Thanks a lot for all your answers !

Marc, you're right, I should declare a VARCHAR with two fields, one containg the value and one the length. But DB2 seems to be permissif and accept my syntax also while it works with other values that HIGH-VALUE.

I think that the problem comes from my DB that is an UTF-8 database and so a conversion of caracter 0xFF is done by DB/2. 0xFF is transformed in 0xC3BF which is coded on 2 bytes. With a database in IBM-1252 (ascii) my problem disapeared.