Querying a database using interactive SQL

 

Using the SQL SELECT statement and interactive SQL, you can query a database on another i5/OS^® 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 application server (AS); 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  

 

Parent topic:

Moving data from one system to another

 

Related concepts

SQL programming

 

Related reference

SQL reference