Pageing with record set from DB2 (as400)

gtamir
gtamir used Ask the Experts™
on
I want to make a simple page that show only 10 records from one big file.
If I'll use somehting like "SELECT * FROM V40BPCSF.IIM" I'll get all Items in IIM file (1000K records) and it is taking too much time.
Is there any way to make direct connection, and then fetch 10 time, keeping the current record so I could continue from that specific location on the next page ???

PS(1) - "SELECT TOP 10 " is not working on the AS400...

Please Help,
Gal.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
It sounds like you guys use BPCS! You have 2 choices for this (1) The simplest way is to use RPG with the SETLL and READE keywords (2)The other way is to use CURSORs if you are using embedded SQL. Also, you can use the TOP keyword in embedded SQL. If you are using interactive SQL, I don't believe that you can use CURSORs, in which case I would recommend a tool such as WinSQL (www.indus-soft.com/winsql) which lets you write SQL statements to any database that has an ODBC connection. If you use this tool, you can use CURSORs, TOP and other functions. Let me know which of these options you prefer, and I can give you the exact syntax (especially if it's BPCS!)
Did you try
db2 "select * from tabname fetch first 10 rows only"

Sumeet

Author

Commented:
Yep,
I tried, it's not working, and I realy don't know why.
when I'm writing something like "SELECT * FROM GTAMIR.X" everything is just fine and I'm getting the recordset,
but when I'm writing "SELECT * FROM GTAMIR.X FETCH FIRST 10 ROWS ONLY" I'm getting to follwing error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword FETCH not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE.

I'm Still looking for a solution...
10x,
Gal.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

db2 "select firstnme from (select rownumber() over (order by empno)  ecount,e2.* from sumeetsh.employee as e2) as r where ecount <=10"

OR

db2 "select * from sumeetsh.employee where empno in (select empno from (select rownumber() over (order by empno)  ecount,e2.* from sumeetsh.employee as e2) as r where ecount <=10)"


Hope this helps
Sumeet

Author

Commented:
I tried the following:

select * from GTAIMR.X where G#GL in (select G#GL from (select rownumber() over (order by G#GL)  ecount,e2.* from GTAMIR.X as e2) as r where ecount <=10)

and I got the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token ( was not valid. Valid tokens: , FROM INTO.


as far as I know there is no rownumber() function at all in the as400 syntax...

PS - I increased the points to 201 pts. I need a solution for that issue...

10x.
db2 "select firstnme from (select rownumber() over (order by empno)  ecount,e2.* from sumeetsh.employee as e2) as r where ecount <=10"

OR

db2 "select * from sumeetsh.employee where empno in (select empno from (select rownumber() over (order by empno)  ecount,e2.* from sumeetsh.employee as e2) as r where ecount <=10)"


Hope this helps
Sumeet
db2 "select firstnme from (select rownumber() over (order by empno)  ecount,e2.* from sumeetsh.employee as e2) as r where ecount <=10"

OR

db2 "select * from sumeetsh.employee where empno in (select empno from (select rownumber() over (order by empno)  ecount,e2.* from sumeetsh.employee as e2) as r where ecount <=10)"


Hope this helps
Sumeet
hmmm.. I am still looking into it. Can you not write an SP with the 'fetch first n rows only' clause and then call that sp from your code?

I have little idea about as400 behaviour. Sorry.

Sumeet
Here is one more thing. I had talked to a friend and he told be that the we can compile the 'fetch first n rows only' statement if we compile it statically. Prepare a cursor to get the first 10 rows(not first n rows) and then try.

Hope this helps

Sumeet

Author

Commented:
The situation now (after I talked with my Bos), is that we are going to try with SP on the AS400 that will return only 10 records.
I don't know yet how to do that, but I'll check for that...

Author

Commented:
Hi bipinpn,

I'm writing ASP pages and working on AS400 machine, and Yes, I'm talking about BPCS files. (I have 7 years experience with BPCS in Israel and Europe using RPG).
Anyway,
Now I'm wring ASP pages, and I'm using ODBC driver to query the AS400 files using simple SQL syntax.
My problem is that I don't want to get all Items in IIM file because it's very big. I want to get only 10 records or something like that. In regular SQL I'm using SELECT TOP 10 * FROM database WHERE...  but It's not working with ODBC on the AS400 and I don't know why...
I believe what I'm using is what you called "interactive SQL"
Do you know if I can do something like SELECT TOP 10 * FROM.. or something like that ?
Thanks,
Gal.
Commented:
You can try "select * from iim optimize for 10 rows". The other option is "select * from iim for 10 rows", but I don't think that this will work through the CA connection that you have. The second option I believe only works in embedded SQL on the 400.

Author

Commented:
Thanks bipinpn,

I used your idea of using OPTIMIZE FOR 10 ROWS, and I also changed my Loop over the RecordSet to run until RS.EOF AND NOT more then 10 Loops (with counter inside that loop), and now it's working great with response time like the AS400 machine.

Owh - I love that machine....

Thanks,
I'm giving you all the points for that one,

Thanks again,
Gal.

Author

Commented:
I Looked for that solution in many places with no result.

Thanks again to bipinpn,

Gal.

Commented:
Go outside and yell out loud how great the machine is!! I'm among the few fighting every day to keep processes on the AS400 and away from Microsoft machines.

Commented:
hi Gal and everybody else,

I'm also coding some ASP scripts to retrieve data from DB2 UDB. I'm trying to page the results of a recordset. here's my code:
==============================
rs.open strSQL, dbConn, adOpenDynamic, adLockReadOnly, adCmdText

dim count, totalPages
count=CInt(0)
rs.MoveFirst
rs.PageSize             = numPerPage
totalPages                   = rs.PageCount
rs.AbsolutePage       = curPage

response.write "Total Records: " & totalRecs
IF NOT rs.EOF THEN

'-- start of the table to display the results
response.write "<table>"
response.write "<tr>"
'-- write field names
      For each Field in rs.Fields
            Response.Write "<td bgcolor=""#cccccc"">" & Field.Name & "</td>"
      Next
response.write "</tr>"

'-- loop through the field collection, reporting contents
      While Not rs.EOF AND count < rs.pageSize
      response.write "<tr>"
      for each Field in rs.Fields
            Response.Write "<td bgcolor=""#eeeeee"">" & Field.Value & "</td>"
      Next
      count = count + 1
      rs.MoveNext
      response.write "</tr>"
      Wend
ELSE
      response.write "no records"
response.write "</table>"
END IF


'-- clean up the connections
rs.Close
set rs = Nothing

dbConn.Close
set dbConn = Nothing


%>
      </td>
  </tr>
  <tr>
    <td>
<%
      '-- This part creates a button "next", "prev"
response.write "Page " & curPage & " of " & TotalPages & "<P>"

if curPage > 1 then
   response.write("<INPUT TYPE=BUTTON VALUE='prev' ONCLICK=""document.location.href='stock.asp?curPage="& curPage - 1 & "';"">")
end if

if cInt(curPage) <> cInt(TotalPages) then
   response.write("<INPUT TYPE=BUTTON VALUE='next' ONCLICK=""document.location.href='stock.asp?curPage="& curPage + 1 & "';"">")
end if
'-- End of "next", "prev" buttons
%>
=======================================================

and i'm getting this error:

*************************************
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.
*************************************
the error is at this line: rs.AbsolutePage       = curPage

any ideas?

thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial