Retrieving data from a specific member of a physical file

View some of the Frequently Asked Questions to our support staff. Included are some tips and tricks making this forum ideal for users getting started with GoAnywhere Director. Note: Users can reply to existing topics but only our support staff can add new topics to this forum.
1 post Page 1 of 1

Support_Julie

User avatar
Support Specialist
Posts: 91
Joined: Thu Mar 05, 2009 3:49 pm
Location: Ashland, NE USA

Post by Support_Julie » Mon Mar 23, 2009 1:14 pm
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 */

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 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:
Code:
<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>
Julie Rosenbaum
Sr Support Analyst
e. [email protected]
p. 1.800.949.4696
w. HelpSystems.com
1 post Page 1 of 1