Page 1 of 1

Retrieving data from a specific member of a physical file

Posted: Mon Mar 23, 2009 1:14 pm
by Support_Julie
You can work with multi-member files using SQL in the following way –

Call a system procedure to override the default (*FIRST) member (just for this job)
Run the SQL Select statement in the usual way
Call another system procedure to reset the default member to what it was.

Below is an example:

/* Override to the member (the second parm 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 RED

/* Select records from the member */


/* 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 RED

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.

For example, the XML would look like this:
<sql label="get member" resourceId="selected_database_server">
<query label="override">
<statement>CALL QSYS.QCMDEXC(&apos;OVRDBF FILE(file) TOFILE(library/file) MBR(member) OVRSCOPE(*JOB)&apos;, 0000000065.00000)

<query label="get records" outputVariable="memberRecs">
<statement>SELECT * FROM library.file </statement>
<query label="remove override">
<statement>CALL QSYS.QCMDEXC(&apos;DLTOVR FILE(file) LVL(*JOB)&apos;, 0000000027.00000)</statement>