SQL / DB2 Optimization (timing out)

15605 pts.
Tags:
.NET
AS/400 DB2
IBM DB2
SQL

I put together a simple VB program to run SQL commands on any of our 4 I-series machines to return a simple grid view. I have run across a strange problem and was told it may have to do with optimization.  For example if I run the following SQL ( I just want a count of parts (pm) that have not been sold in our order detail file (o6).

select count(*) from pm
left join o6 on pmpart = odpn                   
where o6ord# is null

using the LF's on the I-series I get the error message "SQL0666 SQL query exceeds specified time limit or storage limit. "

If I change the SQL to use the following using the PF's of those LF's it runs fine in about 2 seconds.

select count(*) from pmp
left join o6p on pmpart = odpn                  
where o6ord# is null

 

Where is the optimization issue on the server side or the I-series side ?

Thanks in advance.



Software/Hardware used:
SQL server, i-series, VS2008

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb

    Optimization occurs at I-series.

    Recommendation is to always use physical files and let the optimizer find best or build indexes. 

    Using I-series native logical files forces the use of a slower query processor.

    51,365 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following