Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: clarification on cursor

    Dear all,
    I have a scenario like this:
    I need to read a file sequentially and get a value say x123 and use this in a host variable of a cursor (in where clause). So the first time i read the file and get a value x123 and move this value and to the host varaible.
    Now I open the cursor and fetch records using a cursor(host variable has x123). Now when it reaches the end of cursor I need to the read the file and get the next record and move again to the host variable and execute the cursor.
    So each time when i pass a new value to the host variable should I open and close the cursor or is it ok if I just open the cursor only once during the execution of the cobol program.

    Appreciate you kind help !!!

    Thanks...

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    thamin, yes, you have to close and open the cursor each time you change the host variable value.

    However, if you have any type of volume, this constant open / fetch / close cursor process can result in bad performance.

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    Thanks very much for the reply Stealth...
    So here U mean to say that if the number of rows retrieved is high then it leads to bad performance....
    Can you suggest me any other way so that i ca achieve this...

    Thanks for your help !!!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    One way may be the following...

    (1) Read all records in the file and put the data into a working table.
    You may want to declare temporary table for the working table.

    (2) Declare a cursor joining table(s) in your original cursor with the working table.
    There may be another ways to use the working table depending on your orginal select statement of cursor,
    for example use the working table in IN or EXISTS predicate.
    You may want to add ORDER BY to the cursor.

    (3) Process the cursor in a loop.


    The advantages of this design strategy of host program are not only performance,
    rather it simplifies logic of host program and encapsulate the logic/code related to database access into an SQL statement.
    The simplified program logic will improve productivety of development and maintenance, and quality of the program.

    I think this is one of the most advantage of fully utilized SQL(and RDB) functionalites,
    i.e. it's non-procedural(declarative) language nature and set oriented operations.


    It will eliminate at least one nesting level of loops.
    See the following simplified psudo codes.

    (a) Sample psudo code without fully utilizing SQL capabilities:
    Code:
    Loop1 until end-of-file
       Read a record from a file
       Put the data into host-variable
    
       Open cursor
    
       Loop2 until not-found
          Fetch cursor
          Process data
       End loop2
    
       Close cursor
       post process, if necessary
    
    End loop1

    (b) Sample psudo code with using SQL capabilities:
    Code:
    Loop1 until end-of-file
       Read all records from file
        and put the records into work-table
    End loop1
    
    Open cursor
    
    Loop2 until not-found
       Fetch cursor
       Process data
       Process control break, if necessary
    End loop2
    
    Close cursor

  5. #5
    Join Date
    Jul 2011
    Posts
    13
    Thanks veru much Tonkuma !!! Appreciate it

Posting Permissions

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