The STRSQL command launches the SQL command interface shown in Figure 25.22.
Figure 25.22: Launching STRSQL.
Type in an SQL statement, press Enter, and the statement executes immediately. If the SQL statement was a SELECT, the results automatically appear.
To get started, let's run a simple statement:
SELECT * FROM KPFSQL/CUSTMAST
This statement displays every column and every row in CUSTMAST, as shown in Figure 25.23. Use the "Position to line" and "Shift to column" fields on the display to change the rows and columns displayed. When finished, press Enter or F12 to return to the previous screen.
Figure 25.23: All rows and columns from CUSTMAST.
From within STRSQL, press F13 to bring up the Session Services menu, then take option 1 to change session attributes. Change the second parameter (SELECT Output) to a ‘2’ to send the output of all future SELECT statements to spool files, or to a ‘3’ to send the output to database file.
When specifying a ‘2’ for Printer files, you are prompted to enter the name of a printer file to use with the output. That printer file could easily define such things as the default output queue or printer device to be used, the page size, characters per inch, lines per inch, and more. Once the output has been redirected to a printer file, all future SELECT statements will create reports rather than present their data on the screen.
SELECT * FROM CUSTMAST
Figure 25.24: Report created from STRSQL.
The previous SELECT statement generates the report shown in Figure 25.24. It includes the results of the SELECT. The column headings on the report are the same headings that are used when the data is displayed on the screen. Date, time, and page number are also added to the headings. If the SELECT statement returns more data than fits on one line of the report, the data wraps around to the next line. In the previous example, the customer city, state, zip code, and phone number have all wrapped around to a second print line. Once the report is printed, it can be handled just like any other report. It can be printed or, using iSeries Navigator, it can easily be downloaded via drag and drop to a PC and converted into an ASCII format. Once on the PC, attaching it to an e-mail is a simple process. This kind of flexibility is tremendously useful for ad hoc reporting. It allows you to quickly respond to requests from management and sales staffs without making a huge effort.
When specifying a ‘3’ for Database files, you are prompted to enter the name and location of the file to output to. By default, the file name is set to QSQLSELECT. (This is a just an example from IBM; feel free to change it to something more meaningful.) The option parameter determines whether the file should be created, replaced, or appended to. Set it to option ‘2’ to create the output file if needed, and to replace it, if it already exists. After directing the output to a database file, all SELECT statements direct their output to the specified file:
SELECT CUSTNBR, CUSTNAM FROM CUSTMAST WHERE CUSTST = 'OH'
This command creates a list of customers who are in the state of Ohio and stores the list in the specified file. This file can then be used as input to a job stream, another SQL, saved for historical purposes, or downloaded to a PC for use within a spreadsheet or similar applications.
The STSQL tool is a valuable asset in today's IT world. The need to perform ad hoc queries, extract data for downloads, and patch files benefits from using SQL. Although this is hardly the only SQL tool you could use, it is convenient and powerful, and well worth getting familiar with.
Depending on your database design, you may need to deal with flat files and various types of date manipulations. SQL has great flexibility in dealing with these issues, although at times it becomes more difficult than we would like. Later in this book, we review techniques for simplifying some of these issues.