HIGH-VALUE (COBOL) and DB2

Yan302 · #1 (**permalink**) 12-18-09, 08:51

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 :

Code:

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 :

Code:

       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

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

n_i · #2 (**permalink**) 12-18-09, 10:12

If your database is defined as UNICODE (UTF-8), it is possible that 30 characters won't fit into 30 bytes.

Lenny77 · #3 (**permalink**) 12-18-09, 11:03

Quote:

Originally Posted by 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 :

Code:

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 :

Code:

       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

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

Quote:

............................................
"LAST_NAME" VARCHAR(30)
"FIRST_NAME" VARCHAR(20)
........................................
05 HOST-LAST-NAME PIC X(30).
05 HOST-FIRST-NAME PIC X(20).

But, if you know COBOL, just a little bit, you have to use:

Code:

05 HOST-LAST-NAME.
 49 HOST-LAST-NAME-len pic s9(4) binary.
 49 HOST-LAST-NAME-txt pic x(30).

05 HOST-FIRST-NAME PIC X(20)
 49 HOST-FIRST-NAME-len pic s9(4) binary.
 49 HOST-FIRST-NAME-txt pic x(20).

When you move high-values X'FF' to your host variables you change the size of your fields (first 2 bytes) became negative binary(X'FF40') = -65....

So, you have to change you COBOL program:

Code:

MOVE HIGH-VALUES TO HOST-LAST-NAME-txt
Move 1           to  HOST-LAST-NAME-len

etc....

Lenny

Yan302 · #4 (**permalink**) 12-18-09, 12:02

Quote:

Originally Posted by n_i

If your database is defined as UNICODE (UTF-8), it is possible that 30 characters won't fit into 30 bytes.

Thanks for your answer.

Yes, my database is in UTF-8. I've tested with a DB in IBM-1252 and it works
but there is no way to make it work with an UTF-8 DB ?

n_i · #5 (**permalink**) 12-18-09, 12:07

Well, you have few options:
- increase column size;
- make sure the strings you insert only contain ASCII characters;

Yan302 · #6 (**permalink**) 12-18-09, 12:07

Quote:

Originally Posted by Lenny77

But, if you know COBOL, just a little bit, you have to use:

Code:

05 HOST-LAST-NAME.
 49 HOST-LAST-NAME-len pic s9(4) binary.
 49 HOST-LAST-NAME-txt pic x(30).

05 HOST-FIRST-NAME PIC X(20)
 49 HOST-FIRST-NAME-len pic s9(4) binary.
 49 HOST-FIRST-NAME-txt pic x(20).

When you move high-values X'FF' to your host variables you change the size of your fields (first 2 bytes) became negative binary(X'FF40') = -65....

So, you have to change you COBOL program:

Code:

MOVE HIGH-VALUES TO HOST-LAST-NAME-txt
Move 1           to  HOST-LAST-NAME-len

etc....

Lenny

Thanks for your answer Lenny.

Changing my cobol program to :

Code:

       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.
            10 HOST-LAST-NAME-len pic s9(4) COMP-5.
            10 HOST-LAST-NAME-txt pic x(30).
         05 HOST-FIRST-NAME.
            10 HOST-FIRST-NAME-len pic s9(4) COMP-5.
            10 HOST-FIRST-NAME-txt 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-txt.
           MOVE 1 TO HOST-LAST-NAME-len. 
           MOVE "Jean" TO HOST-FIRST-NAME-txt.
           MOVE 4 TO HOST-FIRST-NAME-len. 
           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.

When precompiling and binding got errors...

Code:

C:\DB2ORACLE_sample2>db2 prep INSER.sqb bindfile

LINE    MESSAGES FOR INSER.sqb
------  --------------------------------------------------------------------
        SQL0060W  The "COBOL" precompiler is in progress.
   35   SQL0117N  The number of values assigned is not the same as
                  the number of specified or implied columns or variables.
                  SQLSTATE=42802
        SQL0091W  Precompilation or binding was ended with "0"
                  errors and "1" warnings.

C:\DB2ORACLE_sample2>db2 bind INSER.bnd

LINE    MESSAGES FOR INSER.bnd
------  --------------------------------------------------------------------
        SQL0061W  The binder is in progress.
   35   SQL0117N  The number of values assigned is not the same as
                  the number of specified or implied columns or variables.
                  SQLSTATE=42802
        SQL0082C  An error has occurred which has terminated
                  processing.
        SQL0092N  No package was created because of previous errors.
        SQL0091N  Binding was ended with "3" errors and "0" warnings.

What's wrong ?

By the way, strange that my first syntax is accepted by oracle and also by db2 when the value of HOST-LAST-NAME is not HIGH-VALUE.

Best regards,

Lenny77 · #7 (**permalink**) 12-18-09, 14:06

Quote:

Originally Posted by Yan302

Thanks for your answer Lenny.

Changing my cobol program to :

Code:

       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.
            10 HOST-LAST-NAME-len pic s9(4) COMP-5.
            10 HOST-LAST-NAME-txt pic x(30).
         05 HOST-FIRST-NAME.
            10 HOST-FIRST-NAME-len pic s9(4) COMP-5.
            10 HOST-FIRST-NAME-txt 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-txt.
           MOVE 1 TO HOST-LAST-NAME-len. 
           MOVE "Jean" TO HOST-FIRST-NAME-txt.
           MOVE 4 TO HOST-FIRST-NAME-len. 
           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.

When precompiling and binding got errors...

Code:

C:\DB2ORACLE_sample2>db2 prep INSER.sqb bindfile

LINE    MESSAGES FOR INSER.sqb
------  --------------------------------------------------------------------
        SQL0060W  The "COBOL" precompiler is in progress.
   35   SQL0117N  The number of values assigned is not the same as
                  the number of specified or implied columns or variables.
                  SQLSTATE=42802
        SQL0091W  Precompilation or binding was ended with "0"
                  errors and "1" warnings.

C:\DB2ORACLE_sample2>db2 bind INSER.bnd

LINE    MESSAGES FOR INSER.bnd
------  --------------------------------------------------------------------
        SQL0061W  The binder is in progress.
   35   SQL0117N  The number of values assigned is not the same as
                  the number of specified or implied columns or variables.
                  SQLSTATE=42802
        SQL0082C  An error has occurred which has terminated
                  processing.
        SQL0092N  No package was created because of previous errors.
        SQL0091N  Binding was ended with "3" errors and "0" warnings.

What's wrong ?

By the way, strange that my first syntax is accepted by oracle and also by db2 when the value of HOST-LAST-NAME is not HIGH-VALUE.

Best regards,

Why doen't work ? Because:

1. Level has to be 49, not 10: because DB2 doesn't accept STRUCTUREd Variables
2. Could be COMP, or BINARY but not COMP-5.

Change and enjoy.

Lenny

Yan302 · #8 (**permalink**) 12-21-09, 04:24

Thanks Lenny for your help,

With level 49 it work's. But... the value of HOST-LAST-NAME-len should be 30 no ? Why do you said that is 1 ?

Best regards,

Lenny77 · #9 (**permalink**) 12-21-09, 09:51

Quote:

Originally Posted by Yan302

Thanks Lenny for your help,

With level 49 it work's. But... the value of HOST-LAST-NAME-len should be 30 no ? Why do you said that is 1 ?

Best regards,

Thanks for you !

You have a big number of good questions !

You

Code:

move 1 to HOST-LAST-NAME-len

because length of high-values is equal 1: high-values = X'FF', nothing else.

But this not good to use MOVE for initialize VARCHARs.
Much better to use STRING, instead (follow this example):

Code:

move 1                      to HOST-LAST-NAME-LEN

STRING  HIGH-VALUES
    DELIMITED    BY SIZE                
    INTO         HOST-LAST-NAME-TXT
    WITH POINTER HOST-LAST-NAME-LEN
END-STRING

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech