StarQuest: keeping data in motion

 

StarQuest Technical Documents

Using StarSQL to Access Multi-Member Files

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ035

Abstract

By default, a SELECT statement against a physical file containing multiple members returns the first member of the file. This document explains how to use StarSQL to access members in multi-member files on the AS/400 system.

Solution

This example shows how to access files using Microsoft Query, and it assumes the following:

  • DAVIDLIB is a library on the AS/400.
  • QCSRC is a multi-member file containing C source code.
  1. Select the library:

    SELECT * FROM DAVIDLIB.QCSRC


    This results in the contents of the first member of the file.

  2. Issue the OVRDBF command (Override with Database File) in an SQL statement.

    CALL QSYS.QCMDEXC('OVRDBF FILE(MYFILE) TOFILE(LIB.MYFILE) MBR(MEMBER) OVRSCOPE(*JOB)', 0000000065.00000)


    The first parameter is the OVRDBF command. The second parameter is the number of characters, including spaces, between the single quotes in the OVRDBF command, which in this case is 65. Note: The second parameter must be contain exactly 15 digits: ten to the left of the decimal and five to the right. (If you are building a custom application, you can write a module to count the length of the command and create the second parameter for you.)

  3. For example, to access member SRCVR in file QCSRC, if you know that it is NOT the first member of the file, issue the following command:

    CALL QSYS.QCMDEXC('OVRDBF FILE(QCSRC) TOFILE(DAVIDLIB.QCSRC) MBR(SRCSVR) OVRSCOPE(*JOB)', 0000000068.00000)

  4. Issue the command:

    SELECT * FROM DAVIDLIB.QCSRC

    This returns the contents from the member SRCSVR.

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.