UNIT 2

Using SQL/PRO

Starting SQL/PRO

 Make sure that you have added the library SQLPRO to your library list. To start SQL/PRO, enter STRPDSQL at the command line.

Entering SQL Statements

 The following statement entry panel will be displayed once you start SQL/PRO. From here you will enter your SQL statements.


Figure 2.1

As you can see from the panel's lower lines, you can access all of the other functions of SQL/PRO by pressing the appropriate function keys.

The cursor is initially located at the first position of the SQL statement entry field. This is where you key your SQL statement. You are allotted up to 2,720 characters (34 lines) for our SQL statement. The Page Up and Page Down keys will roll you through all 234 lines.

Four function keys assist you in putting together your SQL statement. The F6 key will insert a blank line at the position of the cursor. F14 will delete the line on which the cursor is placed. F10 will copy a line to the line below it (moving down other lower lines if they contain any data). F15 will split a line at the cursor; the remainder of the line following the cursor will be moved down one line.

There is no need to memorize or write down the field names of files that you will be using in an SQL statement. The F17 key will allow you to view and then insert the field names from any file. It starts by prompting you for the name of the file and the library in which it's found. It then displays a list of the field names, sizes and field descriptions:


Figure 2.2

From here, you can select any fields to be included into your SQL statement by placing a '1' to the left of the field name. Selected field names will be placed at the cursor on the SQL statement entry screen. If more than one field is selected, the fields will be separated by commas. If the statement string goes beyond the 80-column screen width, the line will automatically wrap.
Note: If you use IDDU to create your physical files, the files must be externally described or you will not be able to use them properly with SQL/PRO.

Running the SQL statement

Selecting the Output Device

 You have the ability to select an output device of either display, printer or database file. This is performed by selecting option 1 from the Services menu (F13). You'll be presented a panel that provides for the selection:


Figure 2.3

Enter the number of the selection you wish. The default output device is display. Each time you start SQL/PRO, it will begin with this setting. If you select number 3, Database file, you'll be presented with the additional panel to define your file criteria:


Figure 2.4

When output is directed to the printer, SQL/PRO uses the print file QPQXPRTF. The default page width is 80 columns. This can be changed to 132 by enter the CHGPRTF command and specifying 132 on the Page Size keyword (width-positions per line).

Selecting the Execution Environment

 You are able to run your SQL statement either interactively or in batch, depending upon how you have set it. To set the execution environment, take option 2 of the Service menu (F13). Enter a "B" for batch or "I" for interactive. The default is Interactive: each time you start SQL/PRO, it will begin with this setting.


Figure 2.5

History

 SQL/PRO has the ability to retain a history of executed SQL statements with the only size limitation being the capacity of your disk. Each time you hit enter to execute an SQL statement from the interactive SQL/PRO screen, the statement is logged into the SQL/PRO history file. This holds true even if the statement fails, as long as you hit enter, the statement is logged. If you key a statement and hit F22 to go into the report formatter but do not actually hit enter, the statement will not be logged.

You can review the history by selecting option 5 of the Services Menu (F13). This will give you a summary listing of SQL/PRO statements, with each line showing the SQL statement and the date and time it was run. This display is in chronological order with the initial display showing the most recent page of statements. You can page back and forth through the statements with the roll keys.


Figure 2.6

You can pull up the entire SQL statement by entering a '1' in the option field. A selected statement will be displayed in the original main screen. From here, you can do anything that you are able to do from the main SQL statement entry screen.

You can retain as much or as little history as you like. The history file will continue to grow until you run the purge history step from the services menu. ON this step you tell it the number of statements you wish to end up with after the purge.

Saved Queries

 A unique feature of SQL/PRO is Saved Queries. This function allows you to name and save any SQL statement that you have successfully run. You'll find this invaluable for those queries that you run time and time again. Rather than rewrite the SQL statement each time, just pull up the tried and true query. For example, if you frequently run a list of all customers with a balance due, just develop it once and create a Saved Query entry with a meaningful name like BALDUE. From that point on, you only need to select and run BALDUE from the Work With Saved Queries display. In fact, you can even reference the Saved Query in a CL program with the RUNSQLSQ command (more on that later).

You can create a Saved Query from any SQL statement you have on the SQL/PRO Statement Entry screen. Go to the Services menu (F13) and select option 3 (Create Saved Query). From here you will get the following panel:


Figure 2.7

Enter the name of the Saved Query (Choose an appropriate name since the list of Saved Queries displays in alphabetical order), a library and a meaningful description. The library defaults to the library specified in your configuration. Then enter the public authority which can be *ALL, *CHANGE, *USE or *EXCLUDE. This parameter default value is part of your configuration.

For each of the operations listed, the public requires the corresponding authorities:

If the public is excluded from a saved query it will not be included in the list of saved queries presented by the Work With Saved Queries panel.

You can save an existing saved query by specifying 'Y' value for Replace. The default is no ('N') which will cause an error message if you attempt to save a query under a name that already exists.

The current output device information, including any specific database information, will be saved with the Saved Query. If you desire an output device other than the current one, you can change this. Later, when you run a Saved Query, you will be able to override the set output device if necessary.

When you save a query a Query Management (*QMQRY) object is created. If you have created a report format, a Query Management Form (*QMFORM) object is also created. This can take several minutes. The save process can be submitted to batch or run interactively by specifying Y or N in the submit to batch parameter. A default value of Y or N can be stored by using the configuration service option from the Services panel.

You may wish to create a Saved Query from a statement in your history. To do this, select the statement from the history display to bring it up in the SQL/PRO Statement Entry screen. Then proceed as discussed above as if you had just keyed it.

Now to view the Saved Queries, select option 4 (Work With Saved Queries) from the Services menu (F13). Here is a sample of the Work With Saved Queries screen:


Figure 2.8

From here you can run a job, select it, delete it, copy it or print the statement by entering the appropriate number in the option field to the left of the query name. Queries can be saved to libraries of your choice therefore the "Work with Saved Queries" panel allows you to choose the library from which to display saved queries. The library defaults to the target library that has been configured by the user from the Configuration function panel.

With the WRKSQ command you can directly access the "Work with Saved Queries" display for saved queries within the library you specify. The format is:

WRKSQ LIB (library_name)

All options will be available except Select therefore, you cannot edit a saved query from the WRKSQ command.

Running a Saved Query

 You can run a saved query either in batch or interactively. Option 9 runs it interactively and 8 runs it in batch. You can also run a Saved Query by selecting it with option 1 which will copy it into the SQL Statement Entry Screen. Then follow the normal run procedures from there. With either of these three methods, you can run the job using the output device stored in the Saved Query, unless you choose to override the output device on the following screen (which is presented automatically prior to execution):


Figure 2.9

Changing the output parameters will only affect running this current statement. Once completed, the session values that were in effect prior to selecting the Saved Query will be in effect once again.

You can also override any value of a Saved Query by selecting it with option 1 and then changing the statement itself, the output device and the execution environment before running the statement.

Changing a Saved Query

 Changing a Saved Query begins the same as overriding a Saved Query. You select with option 1 to bring the statement into the SQL Statement Entry screen. Make any changes to the statement you want. Then, go to the Services menu and run option 3 to save the query. You will be presented with the name of the query, the description, the public or private authorization and the output device that you specified when you last saved this query. At this point you can change any value before pressing enter. Obviously if you are changing a Saved Query, keep the Query name the same. However, you can change the name to save the query to a new Saved Query name.

Copying a Saved Query

 The easiest way to copy a Saved Query is with option 3. This will provide you with the following screen from which you can copy to another Saved Query of another name.


Figure 2.10

Deleting a Saved Query

 You can delete a Saved Query with option 4. You will be presented with a screen to confirm the deletion. You can also use the DLTSQ command described later in this section.

Printing a Saved Query

 You can print a Saved Query with option 6.

Services

 In previous sections of this chapter we've pointed you to the Services menu. This is it:


Figure 2.11

You've already seen how you can select the output device, set the execution options, work with history and create and work with Saved Queries. Option 6, Work with History, allows you to reorganize your history file. SQL/PRO stores history records for each user in a separate member of the history file. Eventually you will want to reorganize your history file to remove old, unwanted entries.

Running a Save Query from the Command Line

 The command STRQMQRY will allow you to run a Saved Query from the command line. By keying STRQMQRY and pressing F4, you'll get this panel:


Figure 2.12


Figure 2.13

In this example we are running the Saved Query LSTCSTMST, printing the output and substituting the value 'AZ' for the variable STATE. (Refer to chapter 5 for more information about variables.)

Just key the name of the Saved Query and any other necessary information and press enter. This will begin running the Saved query as it is set up with SQL/PRO.

Running a Saved Query from a CL Program

 You can also execute a Saved Query from a CL program with the STRQMQRY command. The previous example where we used the command prompter could easily be embedded in a CL program like this:

STRQMQRY QMORY (LSTCSTMST) +
OUTPUT (*OUTFILE) +
SETVAR ((STATE 'AZ'))
QMQRY: The name of the Saved Query you wish to run.

OUTPUT: The output device (* = Display, *PRINT = Printer, *OUTFILE = database file)

SETVAR: Optional parameter used to substitute values into variables embedded in your queries. Maximum allowed is 50.

Embedding SQL Statements in CL Programs

 Another unique feature of SQL/PRO over SQL/400 is its ability to execute an SQL statement from a CL program. This is somewhat similar to running a Saved Query from a CL program, only you can construct the SQL statement dynamically in the CL Program. This is done with the RUNPDSQL command, which has the following format:
RUNPDSQL STM ('SQL-statement') +
OUTPUT (* or *OUTFILE or *PRINT ) +
OUTFILE (qualified outfile name) +
OUTMBR (member_name or *FIRST *REPLACE OR *ADD)
STM: The SQL statement, in parentheses.

OUTPUT: *For display (changes to *PRINT if run in batch), *OUTFILE to create an outfile and *PRINT to print.

OUTFILE: The qualified outfile name, if *OUTFILE specified.

OUTMBR: This parameter has two elements in the list. The first is either the specific outfile member name or *FIRST. The second element specifies *REPLACE (to replace the member if it already exists) or *ADD (to add the new member).

Here is an example of running a dynamic SQL statement from within a CL program that creates an outfile:

PGM

RUNPDSQL STM ('SELECT + FROM CUSTOMER') +
OUTPUT (*OUTFILE) +
OUTFILE (QTEMP/CUSTLIST) +
OUTMBR (*FIRST *REPLACE)

CALL MYPROG
END PGM

Of course you can construct the SQL statement dynamically in your CL program and then place it into STM through a variable:

RUNPDSQL STM (&DYNSTM) ...

Retrieving a Saved Query into a Variable

 You may have a reason to retrieve a Saved Query statement into a variable. For example, you may want to retrieve one, modify it in a CL program, and then run the modified statement with our RUNPDSQL command. You can do this with the RTVSQLSQ command:


Figure 2.14

Converting Query/400 Queries to SQL/PRO

 For those of you who have Query/400 queries SQL/PRO provides the Convert QRYDFN to SQL/PRO (CVTQRYSQL) command which will convert any QUERY/400 query to an SQL/PRO saved query. The converted query will execute through SQL/PRO just as it did through QUERY/400. However, if you need to edit the report format, the report formatter will force you to create your report format from scratch.


Figure 2.15