Accessing records from a specific member using the SQL Task

[Support_Ron]

It is quite common for physical files on the iSeries to have multiple members. Often times you may want to process records from a specific member than the default (*FIRST) member. This can be accomplished by:

• Calling a system procedure to override the default (*FIRST) member (just for this job)
• Running the Select SQL statement in the usual way
• Finally by calling another system procedure to reset the default member to what it was

Below is an example:

/* Override to the member (the second parameter must be the exact length of the string) */

CALL QSYS.QCMDEXC('OVRDBF FILE(file) TOFILE(library/file) MBR(member) OVRSCOPE(*JOB)', 0000000065.00000)

The number 0000000065.00000 is the length of the command in BOLD

/* Select records from the member */

SELECT * FROM file;

/* Delete the override to the member */

CALL QSYS.QCMDEXC('DLTOVR FILE(file) LVL(*JOB)', 0000000027.00000);

The number 0000000027.00000 is the length of the command in BOLD.

In essence, You will have 3 Query Elements in your SQL Task, with the first query to override the default member name, the second to run the actual query and the third to delete the override.