Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

AS/400 SQL transaction file help

Iseries database expert Kent Milligan offers an index for an efficient way of selecting the last transactions in a transaction file.

Can you help me with the best way to select the last three transactions made in a transaction file using AS/400 SQL?

Example of file structure:
CustID, TransDate, Amount.

Is there something more efficient than: select top 3 CustID, TranDate, Amount from Trans where CustID = '1234'
order by TranDate desc

No, that would be the most efficient. However, DB2 for i5/OS supports the FETCH FIRST clause instead of the proprietary "TOP n" syntax. SELECT custid, transdate, amount from Trans Where CustID='1234' FETCH FIRST 3 ROWS ONLY

To make sure you get the best performance, you want to ensure the optimizer has the best index to implement this query. In this case, I'd recommend creating the following index:
CREATE INDEX myix ON trans(CustID, TranDate Desc, amount);

Dig Deeper on DB2 UDB (universal databases)