Query for iSeries was designed with multiple member files in mind and will allow you to specify the member name. However, SQL will not allow you to access a specific database member directly. When you run SQL on an iSeries file, it will always use the first member in the file.
Let’s assume we have a database file which holds cities from countries in Europe and each country is represented by a separate member.
File/Member . . . . . . :COUNTRY(FRANCE) Paris Lille Marseilles File/Member . . . . . . :COUNTRY(BELGIUM) Brussels Antwerp Gent File/Member . . . . . . :COUNTRY(LUXEMBOURG) Luxembourg Esch-sur-Alzette Differdange File/Member . . . . . . :COUNTRY(HOLLAND) Amsterdam Rotterdam Utrecht File/Member . . . . . . :COUNTRY(UK) London Glasgow Cardiff
With Query you can easily access a specific member:
===> RUNQRY QRYFILE((COUNTRY BELGIUM))
This would give the following results:
Line ....+....1....+....2....+....3 COUNTRY 000001 Brussels 000002 Antwerp 000003 Gent ****** ******** End of report ********
However, SQL only returns data from the first member which was created in the file.
===> STRSQL
SELECT * FROM country
The first member in the file is FRANCE. We have no control on the country selection and get the following results.
CITY Paris Lille Marseilles ******** End of data ********
Fortunately, there are a few different methods of accessing data in the other members with SQL. This can be achieved by using aliases, file overrides or logical files.
Aliases
To perform an iSeries SQL member select you first need to create an alias for the member.
===> STRSQL
CREATE ALIAS bel FOR country(belgium) SELECT * FROM bel
....+....1....+....2....+....3 CITY Brussels Antwerp Gent ******** End of data ********
This all takes place within SQL code. It is therefore self-contained and easy to read. The alias can be removed with the DROP statement:
DROP ALIAS bel
Overrides
There is another approach performing SQL on an iSeries SQL multiple member file. Create an override using the Override with Database File command. SQL can then be performed on the override.
===> OVRDBF FILE(gbr) TOFILE(country) MBR(uk) ===> STRSQL
SELECT * FROM gbr
....+....1....+....2....+....3 CITY London Glasgow Cardiff ******** End of data ********
The override remains active until the job ends. It can also be removed manually with the Delete Override command:
===> DLTOVR gbr
Logical files
The third way of processing multi-member files with SQL is to use a logical file. Creating a logical file will allow you to specify which member(s) are used when accessing data. This is achieved by means of the parameter DTAMBRS.
When you name a specific member in the Create Logical File command, the SQL which references this logical file will only access data in the specified member.
===> CRTLF FILE(countries) DTAMBRS((country (belgium)))
SELECT * FROM countries
....+....1....+....2....+....3 CITY Brussels Antwerp Gent ******** End of data ********
You can also specify a selection of members so that the SQL retrieves data from multiple members. This has a similar to specifying UNION in the SQL statement.
===> CRTLF FILE(countries) DTAMBRS((country (belgium holland luxembourg))) ===> STRSQL
SELECT * FROM countries
....+....1....+....2....+....3 CITY Brussels Antwerp Gent Luxembourg Esch-sur-Alzette Differdange Amsterdam Rotterdam Utrecht ******** End of data ********
If you would like data from all members to be available via SQL then don’t specify a value for parameter DTAMBRS. All members will be used as default.
===> CRTLF FILE(countries) ===> STRSQL
SELECT * FROM countries
....+....1....+....2....+....3 Paris Lille Marseilles Brussels Antwerp Gent Luxembourg Esch-sur-Alzette Differdange Amsterdam Rotterdam Utrecht London Glasgow Cardiff ******** End of data ********