• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Paging Cursors

    October 22, 2014 Paul Tuohy

    Note: The code accompanying this article is available for download here.

    I would like to share with you one of the techniques I use for paging large lists when using embedded SQL in RPG. This method came about when I needed to write a routine which could be used in both an interactive (green screen) and web environment. There were two main challenges:

    • The size of a “page” could vary. This was easy enough to handle, I just had to decide what the maximum page size would be.
    • The web interface would not be persistent. This meant that different server jobs would be handling different paging requests. This, in turn, meant that the cursor (for the SQL SELECT statement) had to be closed after each retrieve. But, the SQL Query Engine (SQE) is very clever and the work done for one job could be used by another. Therefore, the opening and closing of the cursor for every page request has a minimal impact.

    The Requirements

    As well as retrieving the required page of information, there were a few other requirements about how information was requested and some additional information that would be returned from the routine.

    The routine required input parameters to specify:

    • The number of rows in a page.
    • Which page to retrieve.
    • An offset as to which page to retrieve, i.e., the page retrieved is the previous parameter plus this number. This allows for a roll forward or roll back logic in the caller.

    The routine required output parameters that specify:

    • The page retrieved.
    • The total number of pages.
    • The total number of rows.
    • The number of rows in this page.

    The call interface for one of these SQL paging routines is shown in the next piece of code.

         d get_list_Employees...
         d                 pr                  extProc('get_list_Employees')
         d  department                   10a   const
         d  currentPage                  10i 0 const
         d  nextPage                     10i 0 const
         d  pageSizeIn                   10i 0 const
         d  currentPageIs                10i 0
         d  totalpages                   10i 0
         d  totalRows                    10i 0
         d  gotRows                      10i 0
         d  data                               likeDS(b_list_Employees)
         d                                     dim(9999)
    

    Now let’s look at a piece of code that shows the definition of template b_list_Employees, which is used in the definition of the host variable array used in retrieving data.

    d b_list_Employees...
    d                 Ds                  qualified template
    d  employee                      5a
    d  fullName                     50a   varying
    d  library                      10a
    d  salary                       15p 2
    

    The SQL Bit

    The fact that the cursor is opened and closed on each call means that the embedded SQL is fairly straight forward. The next snippet of code below shows an SQL paging routine. The process is as follows:

    • Declare the cursor. The cursor is defined as an insensitive cursor to ensure the correct number of rows is returned on the later GET DIAGNOSTICS.
    • Open the cursor.
    • Use GET DIAGNOSTICS to determine the total number of rows in the result set.
    • Call the calculate_Page_Data() subprocedure to calculate returned parameter values (total pages, etc.) but, most importantly, to calculate the offset to the first row to be retrieved from the result set. We will look at calculate_Page_Data() in a moment.
    • Use a multi-row fetch to retrieve the requested number of rows (page size) from the result set. FETCH RELATIVE is used with the calculated row offset to ensure that the correct page is retrieved.
    • Get the number of rows retrieved.
    • Close the cursor.

    p get_list_Employees...
    p                 b                   export
    d                 pi
    d  department                   10a   const
    d  currentPage                  10i 0 const
    d  nextPage                     10i 0 const
    d  pageSizeIn                   10i 0 const
    d  currentPageIs                10i 0
    d  totalpages                   10i 0
    d  totalRows                    10i 0
    d  gotRows                      10i 0
    d  data                               likeDS(b_list_Employees)
    d                                     dim(9999)
    
    d pageSize        s             10i 0
    d rowOffset       s             10i 0
    
    /free
    
      exec SQL
       declare C001 insensitive scroll cursor for
       select empno, firstNme || ' ' || lastname, salary
           from employee
            where workdept = :department
            order by empno
            for read only;
    
      exec SQL
         open C001;
    
      exec SQL
         get diagnostics :totalRows = DB2_NUMBER_ROWS;
    
         calculate_Page_Data(currentPage
                            :nextPage
                            :pageSizeIn
                            :%elem(data)
                            :currentPageIs
                            :totalpages
                            :totalRows
                            :pageSize
                            :rowOffset
                            );
    
      exec SQL
         fetch relative :rowOffset from C001
               for :pageSize rows into :data;
    
      gotRows =  SQLERRD(3);
    
      exec SQL
         close C001;
    
      return;
    /end-Free
    p                 e
    

    The calculate_Page_Data() Subprocedure

    Since the process of calculating page data will be the same in all the SQL paging procedures that use this method, it makes sense to put it in its own subprocedure: calculate_Page_Data() is shown in the code below. The main purpose of the subprocedure is to calculate the total number of pages in the result set, based on the total number of rows in the result set divided by the page size, and to calculate the offset to the first row of the requested page, based on the requested page plus the next page parameter multiplied by the number of rows per page. calculate_Page_Data() also ensures that requested parameters (request page, rows per page, etc.) are valid and within the boundaries of the result set.

         p calculate_Page_Data...
         p                 b                   export
         d                 pi
         d  currentPage                  10i 0 const
         d  nextPage                     10i 0 const
         d  pageSizeIn                   10i 0 const
         d  maxPageSize                  10i 0 const
         d  currentPageIs                10i 0
         d  totalpages                   10i 0
         d  totalRows                    10i 0
         d  pageSize                     10i 0
         d  rowOffset                    10i 0
    
          /free
           // Ensure valid page size - default to size of requested array
           pageSize = pageSizeIn;
           if (pageSize < 1 or pageSize > maxPageSize);
              pageSize = maxPageSize;
           endIf;
    
           // Calculate total pages in result set
           totalpages = (totalRows / pageSize);
           if (%rem(totalRows: pageSize) <> 0);
              totalpages += 1;
           endIf;
    
           // Calculate the current page to retrieve
           //    Ensure it is within the result set
           //      - default to 1st or last page, if not
           currentPageIs = currentPage + nextPage;
           if (currentPageIs < 1);
              currentPageIs = 1;
           endIf;
    
           if (currentPageIs > totalpages);
              if (totalpages < 1);
                 currentPageIs = 1;
              else;
                 currentPageIs = totalPages;
              endIf;
           endIf;
    
           // Calculate offset to 1st row to retrieve
           rowOffset = ((currentPageIs - 1) * pageSize) + 1;
    
          /end-Free
         p                 e  
    

    Putting It Into Practice

    The program, which you can download here, demonstrates how to use the example shown in this article. The example makes use of the EMPLOYEE table in the standard SQL sample database, but feel free to change it to a table of your choice.

    I would usually have all of the subprocedures in service programs and the calculate_Page_Data() subprocedure, at least, belongs in a service program.

    I hope you find it useful.

    Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Manta Technologies

    The Leader in IBM i Education!
    Need training on anything i?
    Manta is all you need.

    Spring Sale! Save 20% off any Manta Combination Package, including the complete IBM i Training Library. Now through April 30.

    130 courses and competency exams on:
    · IBM i operations
    · System Management and Security
    · IBM i Programming Tools
    · Programming in RPG, COBOL, CL, Java
    · Web Development
    · SQL, DB2, Query

    Product features:
    · Runs in every popular browser
    · Available 24/7/365
    · Free Student Reference Guides
    · Free Student Administration
    · Concurrent User License
    · Built-In IBM i Simulator

    You can download our 200-page catalog and take sample sessions at MantaTech.com.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    BCD:  On-Demand Webinar: Deliver First-Rate IBM i Mobile Applications Quickly.
    COMMON:  Join us at the COMMON 2014 Fall Conference & Expo in Indianapolis, Oct 27-29
    LANSA:  Getting ready to build a mobile app? Webinar: October 21, 11 am CT / 1 pm ET

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    iBelieve Tour Returns to Europe What The IBM Chip Biz Selloff Means To IBM i Shops

    Leave a Reply Cancel reply

Volume 14, Number 24 -- October 22, 2014
THIS ISSUE SPONSORED BY:

CCSS
WorksRight Software
Shield Advanced Solutions

Table of Contents

  • SQL Functions You Didn’t Know You Had, Part 1
  • Paging Cursors
  • The IBM i *LOOPBACK Interface Problem

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • LANSA Developing Business Intelligence Tool
  • Blazing The Trail For VTL In The Cloud
  • Data De-Dupe Gives VTL Customers More Options
  • Four Hundred Monitor, March 29
  • The Big Spending On IT Security Is Only Going To Get Bigger
  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle