Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: multiple row fetch in db2 SQl stored procedure

    Hi,

    Could some one help me with the code that can do multiple fetch row in db2 SQL stored procedure?

    I tried with the below code which throws sql code -104 error

    DECLARE CURSOR_NAME CURSOR WITH ROWSET POSITIONING WITH HOLD FOR SELECT A,B,C FROM TABLE A
    FETCH FIRST 100 ROWS ONLY
    OPTIMIZE FOR 100 ROWS;

    could some one help with a solution?

    Thanks in Advance
    sumi

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Always post your DB2-server Version and Fixpack, and operating system name.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    please try to publish all code and error when executing/compiling and platform info and vrm
    or see
    SQL0104N
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If your DB2 server is on Linux, a Unix variant, or Windows, then this syntax (rowset positioning) is not supported in current V9.x or V10.1 version. It is supported on DB2 for Z/OS however.

  5. #5
    Join Date
    Apr 2012
    Posts
    4
    Thanks for the Quick response.

    Our DB is installed in Unix and version9.x.

    Is there any command that does the same function as Rowset positioning.

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If you are capable of writing CLI procedures (i.e. in the 'C' language), then CLI can use array fetch techniques for rowsets.
    But as stated, for LUW, at the SQL level multi-row fetch syntax via rowsets is not available yet in v9.x or v10.1.
    Besides, a set based approach in SQL can yield dramatically better performance than old style row-by-row cursor based batch processing.

Posting Permissions

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