Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Introducing Pagination to a DB2 query

    Env: DB2 V8.2/AIX 5.3

    I would like to add pagination feature to the following query
    select
    t1.participantid, t3.queuelocation, t3.createdate,
    t1.dlgt_id, t3.duedate, t1.task_type_id, t1.task_resolution_code,
    t3.enddate
    from
    tab1 t1
    left outer join
    tab2 t2
    on
    t1.id = t2.id,
    tab3 t3,
    tab4 t4
    where
    t1.id=t3.id and
    t4.case_id=t3.caseid and
    t1.participantid = '?'
    order by
    t1.code, t1.task_num
    fetch first 50 rows only
    Modified Query
    with temp as
    (
    select
    row_number() over (order by t1.code, t1.task_num) as row_id ,
    t1.participantid, t3.queuelocation, t3.createdate,
    t1.dlgt_id, t3.duedate, t1.task_type_id, t1.task_resolution_code,
    t3.enddate
    from
    tab1 t1
    left outer join
    tab2 t2
    on
    t1.id = t2.id,
    tab3 t3,
    tab4 t4
    where
    t1.id=t3.id and
    t4.case_id=t3.caseid and
    t1.participantid = '?'
    )
    select
    *
    from
    temp
    where
    row_id between 50 and 100
    Is this a right approach or do I need to make any change from performance perspective ? Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is OK, but every time you want another page, you rerun the whole query. Why not create a temp table, insert into from your outer query with the row_number() function, put an index on the row number column, then do you page queries on the temp table?

    Andy

  3. #3
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks for the nice idea. I will consider it.

    Only problem is that my query's order by clause would vary based on user's selection from the front end portal. Users can select order by based on 5 categories. So whenever users select different category I think I need to delete the record in temp table and reinsert again.

    I will do some test to determine which is a better option in that case.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2udbgirl
    So whenever users select different category I think I need to delete the record in temp table and reinsert again.
    You would have to clear / recreate the temp table whenever the base query changes anyway.

    Andy

Posting Permissions

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