Retrieving data from a specific member of a physical file

Post by **Support_Julie** » Mon Mar 23, 2009 1:14 pm

Overview
This article provides a detailed example of how to retrieve data from a specific member of a physical file.

Environment
GoAnywhere MFT - all versions

Resolution
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)
/* Select records from the member */
SELECT * FROM file;
/* Delete the override to the member */
CALL QSYS.QCMDEXC('DLTOVR FILE(file) LVL(*JOB)', 0000000027.00000);

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:

Code: Select all

<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)

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