Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    12

    Unanswered: HIGH-VALUE (COBOL) and DB2

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If your database is defined as UNICODE (UTF-8), it is possible that 30 characters won't fit into 30 bytes.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation COBOL also Language...

    Quote Originally Posted by Yan302 View Post
    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
    ............................................
    "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
    Last edited by Lenny77; 12-18-09 at 12:42.

  4. #4
    Join Date
    Feb 2009
    Posts
    12
    Quote Originally Posted by n_i View Post
    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 ?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, you have few options:
    - increase column size;
    - make sure the strings you insert only contain ASCII characters;
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2009
    Posts
    12
    Quote Originally Posted by Lenny77 View Post
    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,

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Fix your mistakes

    Quote Originally Posted by Yan302 View Post
    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

  8. #8
    Join Date
    Feb 2009
    Posts
    12
    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,

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Talking COBOL made Easy

    Quote Originally Posted by Yan302 View Post
    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
    Last edited by Lenny77; 12-21-09 at 10:55.

Tags for this Thread

Posting Permissions

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