Accessing records from a specific member using the SQL Task
Posted: Wed Mar 04, 2009 5:10 pm
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:
/* 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.
- 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
/* 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.