Querying a database using interactive SQL

Using the SQL SELECT statement and interactive SQL, you can query a database on another IBM® i operating system for the data that you need in order to create or update a table on the local system.

The SELECT statement allows you to specify the table name and columns containing the desired data, and selection criteria or filters that determine which rows of data are retrieved. If the SELECT statement is successful, the result is one or more rows of the specified table.

In addition to getting data from one table, SQL allows you to get information from columns contained in two or more tables in the same database by using a join operation. If the SELECT statement is successful, the result is one or more rows of the specified tables. The data values in the columns of the rows returned represent a composite of the data values contained in specified tables.

Using an interactive SQL query, the results of a query can be placed in a database file on the local system. If a commitment control level is specified for the interactive SQL process, it applies to the server; the database file on the local system is under a commitment control level of *NONE.

Interactive SQL allows you to do the following things:

  • Create a new file for the results of a select.
  • Replace an existing file.
  • Create a new member in a file.
  • Replace a member.
  • Append the results to an existing member.

Consider the situation in which the KC105 dealership is transferring its entire stock of part number 1234567 to KC110. KC110 queries the KC105 database for the part they acquire from KC105. The result of this inventory query is returned to a database file that already exists on the KC110 system. This is the process you can use to complete this task:

Use the Start SQL (STRSQL) command to get the interactive SQL display. Before you enter any SQL statement (other than a CONNECT) for the new database, specify that the results of this operation are sent to a database file on the local system. To do so, follow these steps:

  1. Select the Services option from the Enter SQL Statements display.
  2. Select the Change Session Attributes option from the Services display.
  3. Enter the Select Output Device option from the Session Attributes Display.
  4. Type a 3 for a database file in the Output device field and press Enter. The following display is shown: 
    Change File
 
Type choices, press Enter.
 
File . . . . . . . . .   QSQLSELECT   Name
Library  . . . . . .   QGPL         Name
Member . . . . . . . .   *FILE        Name, *FILE, *FIRST
 
Option . . . . . . . .   1            1=Create new file
2=Replace file
3=Create new member
4=Replace member
5=Add to member
 
For a new file:
Authority  . . . . .   *LIBCRTAUT   *LIBCRTAUT, *CHANGE, *ALL
*EXCLUDE, *USE
authorization list name
 
Text . . . . . . . .
 
 
F3=Exit     F5=Refresh     F12=Cancel
  5. Specify the name of the database file that is to receive the results.

When the database name is specified, you can begin your interactive SQL processing as shown in the following example. 

Enter SQL Statements
 
Type SQL statement, press Enter.
Current connection is to relational database KC000.
CONNECT TO KC105__________________________________________________________
Current connection is to relational database KC105.
====> SELECT * FROM INVENTORY_____________________________________________
WHERE PART = '1234567'____________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
Bottom
F3=Exit   F4=Prompt   F6=Insert line   F9=Retrieve   F10=Copy line
F12=Cancel            F13=Services     F24=More keys