Designing a single query using SPUFI

Hi, I am new to Mainframe..using QMF.Instead of using multiple select queries,my TL told me to design a single query.How to execute the query in SQL execution engine SPUFI??? Please tell the steps involved in creating a dataset & import the results?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Welcome to the world of z/OS…

I think you have 2 questions here.

1) how to invoke SPUFI

2) how to combine 2 queries into 1.

SPUFI (SQL Processor Using File Input) is invoked from ISPF. SPUFI is one of the processes under DB2I (DB2 Interactive). Find DB2I from your shop’s ISPF panels. (I cannot tell you where your shop decided to place it.

Once you find DB2I, SPUFI is listed on the DB2I main menu.

The main SPUFI menu will need to be filled in.

Line 1 – a dataset (PDS maybe best) where your SQL will reside
LINE 2 & 3 – probably not needed
line 4 – dataset where the results will be placed
lines 5 – 9 are the processes SPUFI will go thru each time ENTER is pressed from this panel.

Type an existing PDS name into line 1 – something named like
and press ENTER.

You will be presented with SPUFI defaults panel –
change them as needed

You are back on the SPFUI main menu – note how line 5 is now an * showing you have already been there.


You will now be in SPF EDIT of the pds(mem) you named.

Type your SQL statement –
SELECT col1 FROM my.table;

Add a comment – comments start with 2 dashes –
— this is a comment


now you are back on the SPUFI main menu. Note how line 6 is now an *.

Press ENTER to exec the SQL named on LINE 1.

When it completes execution, you will be placed in ISPF BROWSE to view your output.

END from there to return to the main SPUFI menu.

There are LOTS of things you can do, such place an * on any line 5-9 to skip that process.

Explore, press help, read the manual, experiement. SPFUI is a good tool. easy to use.

combining two or more SQL statements into one is the mark of a good programmer. Fewer SQL statements means fewer calls from the application to the database engine resulting in less overhead. It also puts the work in the database instead of an application.

For example, you could write an SQL statement to get all RED HEADS. The application could then use those results to find out the SHOE SIZE of those individuals. But a better approach is to combine those two queries into one query that checks on both hair color and foot dimensions, make one call to the database, and remove the application from the process.

I cannot give a more concrete answer becasue there are many, many ways to combine SQL statements. it depends on the statements. should they be UNIONed or one become a sub-select of the other, or used in a join (inner or outer), …

Hope this helps.

Discuss This Question:  

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.

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.

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


Share this item with your network: