Non-iSeries or non-System/38 Query/38 example

 

This example shows how to create a local file and use it to define the data that is to be queried in a non-iSeries™ or non-System/38™ remote file.

Assume that a DDM file named RMTS36FILE exists on your iSeries server and it refers to a remote System/36™ file that you want to query. You can perform the following steps to: determine the attributes of the remote System/36 file; locally create a physical file that has the attributes of the remote file; and define, create, and run the Query/38 against the remote file.

  1. Use the Display File Field Description (DSPFFD) command and specify SYSTEM(*RMT) to display the attributes of the remote file associated with the RMTS36FILE DDM file. 
    DSPFFD  FILE(RMTS36FILE) SYSTEM(*RMT)

    In this example, the displayed results would show that the remote file's record length is 80 characters, its record format name is RMTS36FILE, and it has two fields: K00001, with 12 characters (starting in position 1), and F00001, with 68 characters (starting in position 13). The K in field K00001 indicates it is the key field for this format.

  2. Using the DDS and the preceding information before defining your Query/38 application, create a local physical file and call it LCLS36FILE. The DDS might look something like this: 
          A          R RMTS36FILE       A            CUSNO           6A       A            BILLCODE        6A       A            ADDR1          15A       A            ADDR2          15A       A            ADDR3          15A       A            ZIP             5A       A            AMTOWE          7S 2
      A            OUTBAL          7S 2
      A            MISC            4A       A          K CUSNO       A          K BILLCODE
    Three main rules must be followed when defining the local file:

    • The record format name must be the same as the record format name displayed by the Display File Field Description (DSPFFD) command.

    • Key integrity must be maintained. In this case, the key must be 12 characters long, and must start at the beginning of the file in position 1.

    • The total record length must be the same as the record length displayed by the DSPFFD command.

  3. Define your Query/38 application using the local file created in step 2. Because the remote file is a non-iSeries file, OPTIMIZE(*NO) should be specified on the query command.

  4. Before your Query/38 application is run, issue the following Override Database File (OVRDBF) command: 
    OVRDBF  FILE(LCLS36FILE) TOFILE(RMTS36FILE)

    When the Query/38 application is run, this command overrides the local file you created with the DDM file that is associated with the desired target file.

  5. Run your Query/38 application using the Query Data (QRYDTA) command. The net effect is that a query of the remote file is done using the local file description.

 

Parent topic:

System/38-compatible database tools

 

Related reference


System/38-compatible query utility (Query/38)
Query/38 optimization for DDM