Evaluate

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.

Kent Milligan, IBM

Published: 26 Sep 2007

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.

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

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);

Use Java method modifiers to avoid public method antipattern

Improve query performance, cut costs with Amazon Athena

Manage your SQL Server databases with SQL DMVs

Putting SQL Server catalog views to work

Please create a username to comment.

IoT data center tech aims to improve operational efficiency

An introduction to DCIM software metrics and terms

6 steps to prep your organization for ISO 9001 certification

Dig Deeper on DB2 UDB (universal databases)

Use Java method modifiers to avoid public method antipattern

Improve query performance, cut costs with Amazon Athena

Manage your SQL Server databases with SQL DMVs

Putting SQL Server catalog views to work

Related Q&A from Kent Milligan

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.