Pagination in DB2 Z/OS

25 pts.
Tags:
db2 z/os
SQL
I have a select query that returns 500000 rows. I need to display 100 rows per page. When the user navigates to next page, I need to display the rows between 100 and 200 and so on. I am using DB2 v10 and java.
My query is taking long time to fetch the data even though I am fetching 100 rows at a time because of many order by clauses in the query. I am using SQL Pagination approach. Could you please let me know the best approach to do this for better performance?
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    How are you trying to display your results? Can you post some code for us to review?
    132,520 pointsBadges:
    report
  • Vithal
    I am displaying 100 records at a time. When user navigates to next page, I am fetching next 100 records using SQL pagination. My table has 7 columns together primary key. So I need to order by 7 columns and when the user navigates to next page, I need to have all those OR predicates for 7 columns in the query and the table has millions of records.
    25 pointsBadges:
    report
  • ToddN2000
    Is this data being displayed using a .NET app? is it VB, C# or other language. Post a sample of the code so we can provide a better response.
    132,520 pointsBadges:
    report
  • Vithal
    It is a java based application. I am using spring framework and ExtJS Grid for the front end to display the records.

    My query is taking long time to fetch the data. As table has millions of records. My query looks something like the below one. I am actually getting 20 to 25 columns in the query. Here in the sample query, for simplicity, I am showing only 5 columns.


    SELECT POS.col1,POS.col2,POS.col3,POS.col4,POS.col5, POS.col6 FROM   TABLE1 AS POS ,TABLE2 AS OIN  WHERE OIN.col1 = POS.col2 AND OIN.col2 = POS.col7  AND OIN.col7 = POS.col7  AND POS.col7 = '01' AND POS.col1 in ('VAL1','VAL2','VAL3','VAL4')  AND OIN.col2 = ?  
    UNION ALL 
    SELECT 'SUMMARY',POS.col2,   '9999999999', POS.col4,POS.col5,POS.col6 FROM   TABLE1 AS POS  ,TABLE2 AS OIN  ,TABLE3 AS SPR  WHERE  
    OIN.col10 = POS.col2 AND OIN.col2 = POS.col7 AND SPR.col7 = POS.col7  AND OIN.col7 = POS.col7 AND SPR.col2= POS.col2 AND  SPR.col11 = POS.col11 AND 
    SPR.col12 = POS.col4  AND SPR.col5 = POS.col5 AND SPR.col6 = POS.col6 AND POS.col7 = '01' AND SPR.col3 = '9999999999' AND 
    POS.col1 = ?   AND SPR.col1 = ? AND SPR.col10 = ?  AND OIN.col2 = ?  ORDER BY  2,3,4,5,6 ASC FETCH FIRST 100 ROWS ONLY

    I need order by in the query because I have to show pagination. If the user navigates to next page , I am using SQL Pagination approach to fetch the next 100 records. Those 5 columns in the order by together is primary key in my table. And we have index for all the columns. Please suggest as soon as possible.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: