This is certainly not the only way to paginate with DB2. I just thought
it might be useful to have a working example in the WEB400 archive.
The script assumes a DB2 file called MYLIB/MYEOP. The MYEOP file
contains four fields: EOPYEAR, EOPPRD, EOPBEGDT, EOPCLSDT.
EOPYEAR = year
EOPPRD = period
EOPBEGDT = begin date
EOPCLSDT = close date
The file holds the begin and close dates for each business period of
every year. I want to scroll back and forth through the file, seeing the
information for one year at a time. (We happen to have 13 business
periods in each year. So I want to see 13 records at a time.).
The script uses a nested SELECT to accomplish the scrolling:
"SELECT * FROM
(SELECT A.EOPYEAR, A.EOPPRD, A.EOPBEGDT, A.EOPCLSDT, ROWNUMBER()
OVER() AS RN
FROM MYLIB.MYEOP A)
X WHERE X.RN BETWEEN $rn AND $limit";
The inner SELECT retrieves all the records from the MYEOP file and adds
a new "row number field" using the ROWNUMBER() OVER() option. The outer
SELECT retrieves only the records with the desired range of row numbers.
The variable $rn contains the first row number. The variable $limit
contains the last row number.
The script also contains two forms. The form buttons let the user post
requests to scroll forward to the "next" records or backward to the
"previous" records. The session variable $_SESSION["rn"] stores the row
number across scrolling requests.
The script is listed in full below. Hope it helps.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.