×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

(OP)
This may belong in the DB2 forum but I am using this with COBOL so put it here.  

I have a fun problem.  I have a COBOL program that is retriving a list of numbers from an Oracle database.  Each time it retrieves a new number it puts that number in a comma delimited string with single quotes around each number (ex.. '8','7'). The list is defined as "PIC  X(500) VALUE SPACES".  When all the numbers are put in the list that varable is moved to another variable that is in a copybook containing working storage elements.  That copybook variable is defined as PIC  X(500).  This copybook is then called in a COBOL program that calls a DB2 select statement.  The copybook variable is moved to a local variable defined as "PIC  X(500) VALUE SPACES".  That local variable is then put in a where clause of a select statement.  

My issue is that when the data is returned from that select statement it is like the "not in" statement that list is used in is ignored.  I run the query outside of the program in sql/station and it works perfect.  

Any ideas or suggestions as to why that not in statment would be ignored?  I know this is probably complicated to follow but anything is appriciated.  
 

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

Debug the program which uses the DB2-select statement to see, what happens. You have probably an error, when constructing the select-statement in your COBOL program.
I used similar things, that is I first computed a string and then I used it in where-condition and it worked fine.

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

please post a full sample of the exec SQL you are using on the DB2 COBOL program, including all variable definition.

Also bear in mind that any WS variable that is not used on dynamicly prepared SQL will be treated as a SINGLE item on any SQL, e.g. it can not be used as if it contained a list of items to use on a "WHERE FIELD IN (a,b,c,d)".

 

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

(OP)
Thank you both for replying.  

mikrom -
I have tried to debug but we have changed our debugger and I didn't know what I was doing.  I will have to ask for help today on how get things started.  

fredericofonseca -
If I understand you correctly then what I am trying to do will not work.  Here is what you were asking for though.  

CODE

    *Working Storage Definition
        01  PM164-PARAMETERS.
            05  PM164-GET-EXCLD-STR-PARMS.
                10  PM164-EXCLD-STR             PIC  X(50).
    *Values of PM164-EXCLD-STR being passed
    *I have tried it with one store and with two stores.
        PM164-EXCLD-STR = '8'     OR    PM164-EXCLD-STR = '8','7'
    *Program
        05  PV-STR-LIST                 PIC  X(50) VALUE SPACES.
        MOVE PM164-EXCLD-STR                 TO PV-STR-LIST.
    *found in a paragraph.  Didn't use a cursor since only one value is found.  
        PERFORM WITH TEST AFTER
          VARYING PV-SELECT-COUNTER
          FROM 1 BY 1
        UNTIL (PV-SELECT-COUNTER > PC-MAX-RETRY-CNT OR
               SQLCODE NOT = -904)
            EXEC SQL
                SELECT
                    MIN(A.FIRST_RCVD_DATE)
                INTO
                    :TSKST-FIRST-RCVD-DATE :PV-FIRST-RCVD-        DATE-NULL
                FROM
                    TSKST A
                WHERE
                      A.ITEM_NMBR = :PV-ITM-NBR
                  AND A.LOC_NBR <> 0
                  AND CHAR(A.LOC_NBR) NOT IN (:PV-STR-LIST)
                  AND (A.FIRST_RCVD_DATE > 0
                      OR A.FIRST_RCVD_DATE < 800101)
                GROUP BY A.ITEM_NMBR
            WITH UR
        END-EXEC
 

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

Hawley,
Let me see if I have understood correctly what you are trying to achieve. You are dynamically building a list of values eg 'A1', 'B1', 'C1' and then passing that to DB2:

SELECT COL1 FROM TABLE WHERE COL2 NOT IN (:WS-VARIABLE)

where WS-VARIABLE contains 'A1', 'B1', 'C1'. You are expecting DB2 to pass back any rows where COL2 is either A1 or B1 or C1.

If that is what you are expecting, then it is not going to work. DB2 will look at WS-VARIABLE and treat it as one variable i.e it will pass back any rows where COL2 equals "'A1', 'B1', 'C1'".

You will have to build the query using dynamic SQL and the PREPARE statement, I think.

Hope this helps.
Marc

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

(OP)
Here is something I was trying this weekend.  I was just passing in a store in a seperate variable and then comparing it to the result from the sql.  
My issue with this is for some reason my if statement is wrong.  Any ideas and/or suggestions would be helpful.  

[code]
*definition in the working storage
01  PM164-PARAMETERS.
    05  PM164-GET-EXCLD-STR-PARMS.
        10  PM164-STR-01                PIC  X(04).

*definition in the program
05  PV-LOC-NBR                  PIC  X(04)  VALUE SPACES.

*sql used for the cursor.
      EXEC SQL
          DECLARE FRSTRCVDCSR CURSOR
          FOR SELECT
              CHAR(A.LOC_NBR),
              A.FIRST_RCVD_DATE
          FROM
              TSKST A
          WHERE
                A.ITEM_NMBR = :PV-ITM-NBR
            AND A.LOC_NBR <> 0
            AND (A.FIRST_RCVD_DATE > 0
                OR A.FIRST_RCVD_DATE < 800101)
          WITH UR
      END-EXEC.
*open the cursor

*fetch the cursor
EXEC SQL
    FETCH  FRSTRCVDCSR
     INTO  :PV-LOC-NBR
          ,:TSKST-FIRST-RCVD-DATE :PV-FIRST-RCVD-DATE-NULL
END-EXEC.
        WHEN SQLCODE = +0
            ..........
            DISPLAY 'DB2 LOC-NBR = ' PV-LOC-NBR '.'
            DISPLAY 'FIRST STORE = ' PM164-STR-01 '.'
            IF (PV-LOC-NBR EQUAL PM164-STR-01)
               DISPLAY 'STORE NUMBER IS SAME AS 1 '
            ELSE
               DISPLAY 'STORE NUMBER IS NOT SAME AS 1'
               MOVE TSKST-FIRST-RCVD-DATE TO PV-MIN-FIRST-DATE
            END-IF

The output displays.
DB2 LOC-NBR = 8   .
FIRST STORE = 8   .
STORE NUMBER IS NOT SAME AS 1
[\code]
Why is the stores not seen as being equal?

I have also tried it this way.  
[code]
*definition in the working storage
01  PM164-PARAMETERS.
    05  PM164-GET-EXCLD-STR-PARMS.
        10  PM164-STR-01                PIC  X(04).

*definition in the program
05  PV-LOC-NBR                  PIC  X(04)  VALUE SPACES.

*sql used for the cursor.
      EXEC SQL
          DECLARE FRSTRCVDCSR CURSOR
          FOR SELECT
              CHAR(A.LOC_NBR),
              A.FIRST_RCVD_DATE
          FROM
              TSKST A
          WHERE
                A.ITEM_NMBR = :PV-ITM-NBR
            AND A.LOC_NBR <> 0
            AND (A.FIRST_RCVD_DATE > 0
                OR A.FIRST_RCVD_DATE < 800101)
          WITH UR
      END-EXEC.
*open the cursor

*fetch the cursor
EXEC SQL
    FETCH  FRSTRCVDCSR
     INTO  :PV-LOC-NBR
          ,:TSKST-FIRST-RCVD-DATE :PV-FIRST-RCVD-DATE-NULL
END-EXEC.
        WHEN SQLCODE = +0
            ..........
            DISPLAY 'DB2 LOC-NBR = ' PV-LOC-NBR '.'
            DISPLAY 'FIRST STORE = ' PM164-STR-01 '.'
            IF (PV-LOC-NBR NOT EQUAL PM164-STR-01)
                DISPLAY 'STORE NUMBER IS NOT SAME AS 1'
                MOVE TSKST-FIRST-RCVD-DATE TO PV-MIN-FIRST-DATE
            ELSE
                DISPLAY 'STORE NUMBER IS SAME AS 1'
            END-IF

The output displays.
DB2 LOC-NBR = 8   .
FIRST STORE = 8   .
STORE NUMBER IS NOT SAME AS 1
[\code]
Stores are not being seen as equal here either...why?

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

(OP)
Didn't see the code was not wrapped correctly...here is the post again...a bit cleaner.  

MarcLodge - I figured that as my issue after seeing fredericofonseca post.  I will try the dynamic sql as well.  Thanks for the help

Here is something I was trying this weekend.  I was just passing in a store in a seperate variable and then comparing it to the result from the sql.  
My issue with this is for some reason my if statement is wrong.  Any ideas and/or suggestions would be helpful.  

CODE

*definition in the working storage
01  PM164-PARAMETERS.
    05  PM164-GET-EXCLD-STR-PARMS.
        10  PM164-STR-01                PIC  X(04).

*definition in the program
05  PV-LOC-NBR                  PIC  X(04)  VALUE SPACES.

*sql used for the cursor.
      EXEC SQL
          DECLARE FRSTRCVDCSR CURSOR
          FOR SELECT
              CHAR(A.LOC_NBR),
              A.FIRST_RCVD_DATE
          FROM
              TSKST A
          WHERE
                A.ITEM_NMBR = :PV-ITM-NBR
            AND A.LOC_NBR <> 0
            AND (A.FIRST_RCVD_DATE > 0
                OR A.FIRST_RCVD_DATE < 800101)
          WITH UR
      END-EXEC.
*open the cursor

*fetch the cursor
EXEC SQL
    FETCH  FRSTRCVDCSR
     INTO  :PV-LOC-NBR
          ,:TSKST-FIRST-RCVD-DATE :PV-FIRST-RCVD-DATE-NULL
END-EXEC.
        WHEN SQLCODE = +0
            ..........
            DISPLAY 'DB2 LOC-NBR = ' PV-LOC-NBR '.'
            DISPLAY 'FIRST STORE = ' PM164-STR-01 '.'
            IF (PV-LOC-NBR EQUAL PM164-STR-01)
               DISPLAY 'STORE NUMBER IS SAME AS 1 '
            ELSE
               DISPLAY 'STORE NUMBER IS NOT SAME AS 1'
               MOVE TSKST-FIRST-RCVD-DATE TO PV-MIN-FIRST-DATE
            END-IF

The output displays.
DB2 LOC-NBR = 8   .
FIRST STORE = 8   .
STORE NUMBER IS NOT SAME AS 1
Why is the stores not seen as being equal?

I have also tried it this way.  

CODE

*definition in the working storage
01  PM164-PARAMETERS.
    05  PM164-GET-EXCLD-STR-PARMS.
        10  PM164-STR-01                PIC  X(04).

*definition in the program
05  PV-LOC-NBR                  PIC  X(04)  VALUE SPACES.

*sql used for the cursor.
      EXEC SQL
          DECLARE FRSTRCVDCSR CURSOR
          FOR SELECT
              CHAR(A.LOC_NBR),
              A.FIRST_RCVD_DATE
          FROM
              TSKST A
          WHERE
                A.ITEM_NMBR = :PV-ITM-NBR
            AND A.LOC_NBR <> 0
            AND (A.FIRST_RCVD_DATE > 0
                OR A.FIRST_RCVD_DATE < 800101)
          WITH UR
      END-EXEC.
*open the cursor

*fetch the cursor
EXEC SQL
    FETCH  FRSTRCVDCSR
     INTO  :PV-LOC-NBR
          ,:TSKST-FIRST-RCVD-DATE :PV-FIRST-RCVD-DATE-NULL
END-EXEC.
        WHEN SQLCODE = +0
            ..........
            DISPLAY 'DB2 LOC-NBR = ' PV-LOC-NBR '.'
            DISPLAY 'FIRST STORE = ' PM164-STR-01 '.'
            IF (PV-LOC-NBR NOT EQUAL PM164-STR-01)
                DISPLAY 'STORE NUMBER IS NOT SAME AS 1'
                MOVE TSKST-FIRST-RCVD-DATE TO PV-MIN-FIRST-DATE
            ELSE
                DISPLAY 'STORE NUMBER IS SAME AS 1'
            END-IF

The output displays.
DB2 LOC-NBR = 8   .
FIRST STORE = 8   .
STORE NUMBER IS NOT SAME AS 1
Stores are not being seen as equal here either...why?  

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

I may be wrong, but it looks like yout variable may contain low values or other "undisplayable" chars.
Try and compare one by one (with reference modification) and see what it says.

And now that you gave more details, only the following options available to you.

1- fully dynamic SQL.

2- if there is a limit to the number of IN list (e.g. 10 max or 20 max values) then you can code all of them as
db2_var in (:ws-var1, :ws-var2... ws-varn)

3 - use a global declared table on the main SQL, and when you receive the "variable list" from oracle, insert into that temporary table, and then open the cursor.
Select could then be using a inner join to link the main tble and that other temporary table.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

(OP)
Thanks for all the help.  I ended up making my second solution work.  When I kept the variables as numbers they compared to each other correctly.   

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

Hi hawley,

Some times ago I have done similar thing. Here are the example code snippets from a program which is really running in production - I changed only some comments to English for better understanding:

1. First I define in Working Storage a string, which will hold my select statement

CODE

       01 SELECT-R000090V.
           05 SELECT-ALL-R000090V     PIC X(23)  VALUE
           'SELECT * FROM R000090V '.
           05 WHERE-BEDINGUNG         PIC X(50)  VALUE
           'WHERE KONTOART = 2 AND LOESCHMKM = 0 AND TARIF IN '.
           05 TARIF-LISTE             PIC X(100) VALUE SPACE.
           05 ORDER-BY                PIC X(25)  VALUE
           'ORDER BY BSSTAMMNR, BSVNR'.   

2. Here the field TARIF-LISTE is a placeholder for a list of tarifes in this form ('AA', 'AB', 'ZC', 'XZ') which I compute with this paragraph/procedure from other table

CODE

       ERMITTLE-TARIFE.
      *    Alle Tarife aus P000140V ermitteln
      *    Declare Cursor for the SELECT
           EXEC SQL
             DECLARE C140 CURSOR FOR
             SELECT DISTINCT(TARIF) FROM P000140V
           END-EXEC.
      *    Open Cursor
           EXEC SQL
             OPEN C140
           END-EXEC.
           INITIALIZE ZW-NR-TARIF
           MOVE '(' TO TARIF-LISTE
           PERFORM UNTIL SQLCODE NOT = ZERO
             EXEC SQL
               FETCH NEXT FROM C140 INTO :ZW-TARIF
             END-EXEC
      *      If FETCH ok
             IF SQLCODE = ZERO
                ADD 1 TO ZW-NR-TARIF
      *         insert ', ' before next Tarif
                IF ZW-NR-TARIF > 1
                   STRING TARIF-LISTE DELIMITED BY SPACE
                          ','         DELIMITED BY SIZE
                          INTO TARIF-LISTE
                   END-STRING
                END-IF
                STRING TARIF-LISTE DELIMITED BY SPACE
                       QUOTE       DELIMITED BY SPACE
                       ZW-TARIF    DELIMITED BY SPACE
                       QUOTE       DELIMITED BY SPACE
                       INTO TARIF-LISTE
                END-STRING
             END-IF
           END-PERFORM
      *    At End ')'
           STRING TARIF-LISTE DELIMITED BY SPACE
                  ')'         DELIMITED BY SPACE
                  INTO TARIF-LISTE
           END-STRING
      *    Close Cursor
           EXEC SQL
             CLOSE C140
           END-EXEC.

3. Then the main processing paragraf is here

CODE

       VERARBEITUNG-DA.
           PERFORM PREPARE-SELECT-R000090V
           PERFORM OPEN-CURSOR-R000090V
      *    Processing in Loop
           MOVE SQLCODE TO ZW-SQLCODE90
           PERFORM UNTIL ZW-SQLCODE90 NOT = ZERO
             PERFORM FETCH-NEXT-R000090V
             MOVE SQLCODE TO ZW-SQLCODE90
      *      if exists next record, i.e. if FETCH was ok
             IF ZW-SQLCODE90 = ZERO
      *         Select fields from table R000090T
                MOVE BSSTAMMNR OF R000090T TO WS-BSSTAMMNR
                MOVE BSVNR     OF R000090T TO WS-BSVNR
                MOVE KONTOART  OF R000090T TO WS-KONTOART
                MOVE LOESCHMKM OF R000090T TO WS-LOESCHMKM
                MOVE TARIF     OF R000090T TO WS-TARIF
                MOVE ZINSSATZ  OF R000090T TO WS-ZINSSATZ
      *         Process selected fields
                PERFORM VERARBEITE-SATZ-R000090V
                   THRU VERARBEITE-SATZ-R000090V-EXIT
             END-IF
           END-PERFORM
           .

with these temorary paragraphs for preparing select statement and working with cursor (opening, closing, fetching)

CODE

       PREPARE-SELECT-R000090V.
           PERFORM ERMITTLE-TARIFE
      *    Constructed SQL-SELECT into retazca
           MOVE SELECT-R000090V TO SQLSOURCE

      *    Declare SQL-Statement SELECT90
           EXEC SQL
             DECLARE SELECT90 STATEMENT
           END-EXEC

      *    Declare Cursor for Statement
           EXEC SQL
             DECLARE CLOOP90 CURSOR FOR SELECT90
           END-EXEC

      *    Prepare SQL-Statement form string SQLSOURCE
           EXEC SQL
             PREPARE SELECT90 FROM :SQLSOURCE
           END-EXEC                  



       OPEN-CURSOR-R000090V.
           IF CLOOP-CLOSED
      *       Open Cursor
              EXEC SQL
                OPEN CLOOP90
              END-EXEC
              MOVE SQLSTATE TO WS-SQLSTATE
              IF SQL-OK
                 SET CLOOP-OPENED TO TRUE
              END-IF
           END-IF.

       FETCH-NEXT-R000090V.
           EXEC SQL
             FETCH NEXT FROM CLOOP90 INTO :R000090T
           END-EXEC.

       CLOSE-CURSOR-R000090V.
           IF CLOOP-OPENED
      *       Close Cursor
              EXEC SQL
                CLOSE CLOOP90
              END-EXEC
              MOVE SQLSTATE TO WS-SQLSTATE
              IF SQL-OK OR SQL-NOT-OPEN
                 SET CLOOP-CLOSED TO TRUE
              END-IF
           END-IF.          

Look specially at paragraph PREPARE-SELECT-R000090V how do I create the SQL-statement from a constructed string and how do I declare cursor for that statement.

Happy COBOLing
smile
 

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

In the paragraph PREPARE-SELECT-R000090V I move the constructed SELECT-R000090V to string SQLSOURCE and then from this string I prepare the select-statement. I forgot it's definition in working storage - here is it:   

CODE

01 SQLSOURCE                   PIC X(2000).

RE: Using a list passed from an Oracle COBOL pgm to a DB2 COBOL pgm

It can be done with static SQL. You should realize that

CODE

where x in  ('1','2','3')
and

CODE

where x in  ('1','2','3','1','1','1','1','1')
generate the same result. DB2 performance does not suffer.
This means that you can code like this

CODE

01  w-group.
    49 w-gr-01 pic x(1).
    49 w-gr-02 pic x(1).
    49 w-gr-03 pic x(1).
    49 w-gr-04 pic x(1).
01  w-group-r redefines
    w-group    pic x(4).
move all '1' to w-group-r.
move     '2' to w-gr-02.
move     '3' to w-gr-03.
exec sql .... WHERE a in (:w-gr-01
                         ,:w-gr-02
                         ,:w-gr-03
                         ,:w-gr-04) end-exec
See what I mean?
   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close