Some CL commands do not produce an OUTFILE like the command in Example 4-99 does.
However, many allow you to spool the output. In this section, we show how to process the
results of a command that produces a spool file. First, we create a program on the iSeries
host that copies a spool file into a database file. Then we show an example that calls the
DSPAUTUSR to generate a spool file, calls the program to copy the spool file into a database
file, and reads from the database file to get the user information.
Follow these steps to process a spool file programmatically:
1. Create a CL program on the iSeries server called SAMPLEDB/SAVESPLF. This program
uses the QSPRILSP API to determine the name and job information of the most recently
created spool file for our job. The program takes a schema, library, and member name as
input and copies the most recent spool file into that file. It then deletes the spool file.
– To create the program, create a source physical file to hold the source code:
CRTSRCPF FILE(SAMPLEDB/SRCPF)
– Add a member to this file:
ADDPFM FILE(SAMPLEDB/SRCPF) MBR(SAVESPLF) SRCTYPE(CLP)
– Edit the file:
STRSEU SRCFILE(SAMPLEDB/SRCPF) SRCMBR(SAVESPLF) TYPE(CLP) OPTION(2)
– Add the code shown in Example 4-100 on page 125 to the file.
Chapter 4. IBM DB2 UDB for iSeries .NET provider 125
Example 4-100 Source code for SAMPLEDB/SAVESPLF program
PGM PARM(&LIB &FILE &MBR)
/* Input parameters */
DCL &LIB *CHAR 10
DCL &FILE *CHAR 10
DCL &MBR *CHAR 10
/* Parameters used when calling the QSPRILSP API */
DCL &RCVVAR *CHAR 70
DCL &RCVLNG *INT 4
DCL &FORMAT *CHAR 8
DCL &ERRCODE *CHAR 8
/* Parameters used when calling CPYSPLF */
DCL &SPLFNAME *CHAR 10
DCL &SPLFNBR *INT 4
DCL &JOBNAME *CHAR 10
DCL &USERNAME *CHAR 10
DCL &JOBNBR *CHAR 6
DCL &CRTDATE *CHAR 8
DCL &CRTTIME *CHAR 6
DCL &JOBSYSNAM *CHAR 8
/* Variables used for the RTVJOBA CL command */
DCL &DATFMT *CHAR 4
/* Work vars */
DCL &UPYEAR *CHAR 2
DCL &LOWYEAR *CHAR 2
DCL &MONTH *CHAR 2
DCL &DAY *CHAR 2
DCL &HOUR *CHAR 2
DCL &MINUTE *CHAR 2
DCL &SECOND *CHAR 2
/* Initialize the input parameters */
CHGVAR &RCVLNG VALUE(70)
CHGVAR &FORMAT VALUE('SPRL0100')
CHGVAR %BIN(&ERRCODE 1 4) VALUE(0)
CHGVAR %BIN(&ERRCODE 5 4) VALUE(0)
/* Retrieve the exact identity of the most recent spool file */
CALL QSPRILSP PARM(&RCVVAR +
&RCVLNG +
&FORMAT +
&ERRCODE)
/* Set up the parameters for cpysplf */
CHGVAR &SPLFNAME VALUE(%SST(&RCVVAR 9 10))
CHGVAR &JOBNAME VALUE(%SST(&RCVVAR 19 10))
CHGVAR &USERNAME VALUE(%SST(&RCVVAR 29 10))
CHGVAR &JOBNBR VALUE(%SST(&RCVVAR 39 6))
CHGVAR &SPLFNBR VALUE(%BIN(&RCVVAR 45 4))
CHGVAR &JOBSYSNAM VALUE(%SST(&RCVVAR 49 8))
/* Convert the date from the QSPRILSP format to temp variables */
IF COND(%SST(&RCVVAR 57 1) *EQ '0') THEN(CHGVAR &UPYEAR VALUE('19'))
ELSE (CHGVAR &UPYEAR VALUE('20'))
CHGVAR &LOWYEAR VALUE(%SST(&RCVVAR 58 2))
126 Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET
CHGVAR &MONTH VALUE(%SST(&RCVVAR 60 2))
CHGVAR &DAY VALUE(%SST(&RCVVAR 62 2))
/* Convert the time from the QSPRILSP format to temp variables */
CHGVAR &HOUR VALUE(%SST(&RCVVAR 64 2))
CHGVAR &MINUTE VALUE(%SST(&RCVVAR 66 2))
CHGVAR &SECOND VALUE(%SST(&RCVVAR 68 2))
/* Get the job date format */
RTVJOBA DATFMT(&DATFMT)
/* Format for all date formats except julian */
SELECT
WHEN (&DATFMT *EQ '*YMD') +
THEN(CHGVAR &CRTDATE VALUE(&UPYEAR *CAT &LOWYEAR *CAT &MONTH *CAT &DAY))
WHEN (&DATFMT *EQ '*MDY') +
THEN(CHGVAR &CRTDATE VALUE(&MONTH *CAT &DAY *CAT &UPYEAR *CAT &LOWYEAR))
WHEN (&DATFMT *EQ '*DMY') +
THEN(CHGVAR &CRTDATE VALUE(&DAY *CAT &MONTH *CAT &UPYEAR *CAT &LOWYEAR))
OTHERWISE
ENDSELECT
CHGVAR &CRTTIME VALUE(%SST(&RCVVAR 65 6))
/* Copy the spooled file to the database file and library passed to us */
CPYSPLF FILE(&SPLFNAME) TOFILE(&LIB/&FILE) JOB(&JOBNBR/&USERNAME/&JOBNAME) +
SPLNBR(&SPLFNBR) JOBSYSNAME(&JOBSYSNAM) CRTDATE(&CRTDATE &CRTTIME) +
TOMBR(&MBR) MBROPT(*REPLACE) CTLCHAR(*NONE)
/* Delete the spool file */
DLTSPLF FILE(&SPLFNAME) JOB(&JOBNBR/&USERNAME/&JOBNAME) +
SPLNBR(&SPLFNBR) JOBSYSNAME(&JOBSYSNAM) CRTDATE(&CRTDATE &CRTTIME)
ENDPGM
– Save your source file, and then compile it:
CRTCLPGM PGM(SAMPLEDB/SAVESPLF) SRCFILE(SAMPLEDB/SRCPF) SRCMBR(SAVESPLF)
2. Now we write some code that can use the SAVESPLF program. In Example 4-101, we
invoke the DSPAUTUSR command through QCMDEXC using the CallPgm method we
created earlier (see Example 4-94 on page 121). The DSPAUTUSR command produces a
spool file called QPAUTUSR. Next, we call the SAVESPLF program we created in step 1
on page 124 to copy the spool file into a database file. Finally, we open a DataReader to
read the user information from the database file.
Example 4-101 Processing a CL command that produces a spool file
// Create and open a connection to the iSeries.
iDB2Connection cn = new
iDB2Connection("DataSource=myiseries;DefaultCollection=sampledb;");
cn.Open();
// Call the CallPgm method to execute the DSPAUTUSR command
// and tell it to spool the output.
bool success = CallPgm("DSPAUTUSR OUTPUT(*PRINT)", cn);
// If the call succeeded, create a temp file and call the
// SAVESPLF program, which will copy the spool file into
// our database file.
if (success == true)
{
// Create the file. We don't care if it already exists.
CallPgm("CRTPF FILE(SAMPLEDB/DSPAUTUSR) RCDLEN(132)", cn);
// Clear the file (in case it already has data in it).
CallPgm("CLRPFM FILE(SAMPLEDB/DSPAUTUSR) MBR(*FIRST)", cn);
// Call the SAVESPLF program.
// This program will copy the spool file we just
// created into the file we specify.
success = CallPgm("CALL SAMPLEDB/SAVESPLF PARM(SAMPLEDB DSPAUTUSR *FIRST)", cn);
}
// If we get to here without any errors, then we should have a
// file in our SAMPLEDB schema called DSPAUTUSR, which contains
// a list of authorized users on the system.
if (success == true)
{
// Open a DataReader to read a list of the authorized
// users on our system.
iDB2Command cmd = new iDB2Command("SELECT * FROM DSPAUTUSR", cn);
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
// To make our example shorter, we'll only
// look at the user profile part of the string,
// which is the first 10 characters.
String userProfile = dr.GetString(0);
Console.WriteLine(userProfile.Substring(0, 10));
}
// Close the DataReader since we're done using it.
dr.Close();
// Dispose the command since we no longer need it.
cmd.Dispose();
}
// Close the connection since we're done using it.
cn.Close();
However, many allow you to spool the output. In this section, we show how to process the
results of a command that produces a spool file. First, we create a program on the iSeries
host that copies a spool file into a database file. Then we show an example that calls the
DSPAUTUSR to generate a spool file, calls the program to copy the spool file into a database
file, and reads from the database file to get the user information.
Follow these steps to process a spool file programmatically:
1. Create a CL program on the iSeries server called SAMPLEDB/SAVESPLF. This program
uses the QSPRILSP API to determine the name and job information of the most recently
created spool file for our job. The program takes a schema, library, and member name as
input and copies the most recent spool file into that file. It then deletes the spool file.
– To create the program, create a source physical file to hold the source code:
CRTSRCPF FILE(SAMPLEDB/SRCPF)
– Add a member to this file:
ADDPFM FILE(SAMPLEDB/SRCPF) MBR(SAVESPLF) SRCTYPE(CLP)
– Edit the file:
STRSEU SRCFILE(SAMPLEDB/SRCPF) SRCMBR(SAVESPLF) TYPE(CLP) OPTION(2)
– Add the code shown in Example 4-100 on page 125 to the file.
Chapter 4. IBM DB2 UDB for iSeries .NET provider 125
Example 4-100 Source code for SAMPLEDB/SAVESPLF program
PGM PARM(&LIB &FILE &MBR)
/* Input parameters */
DCL &LIB *CHAR 10
DCL &FILE *CHAR 10
DCL &MBR *CHAR 10
/* Parameters used when calling the QSPRILSP API */
DCL &RCVVAR *CHAR 70
DCL &RCVLNG *INT 4
DCL &FORMAT *CHAR 8
DCL &ERRCODE *CHAR 8
/* Parameters used when calling CPYSPLF */
DCL &SPLFNAME *CHAR 10
DCL &SPLFNBR *INT 4
DCL &JOBNAME *CHAR 10
DCL &USERNAME *CHAR 10
DCL &JOBNBR *CHAR 6
DCL &CRTDATE *CHAR 8
DCL &CRTTIME *CHAR 6
DCL &JOBSYSNAM *CHAR 8
/* Variables used for the RTVJOBA CL command */
DCL &DATFMT *CHAR 4
/* Work vars */
DCL &UPYEAR *CHAR 2
DCL &LOWYEAR *CHAR 2
DCL &MONTH *CHAR 2
DCL &DAY *CHAR 2
DCL &HOUR *CHAR 2
DCL &MINUTE *CHAR 2
DCL &SECOND *CHAR 2
/* Initialize the input parameters */
CHGVAR &RCVLNG VALUE(70)
CHGVAR &FORMAT VALUE('SPRL0100')
CHGVAR %BIN(&ERRCODE 1 4) VALUE(0)
CHGVAR %BIN(&ERRCODE 5 4) VALUE(0)
/* Retrieve the exact identity of the most recent spool file */
CALL QSPRILSP PARM(&RCVVAR +
&RCVLNG +
&FORMAT +
&ERRCODE)
/* Set up the parameters for cpysplf */
CHGVAR &SPLFNAME VALUE(%SST(&RCVVAR 9 10))
CHGVAR &JOBNAME VALUE(%SST(&RCVVAR 19 10))
CHGVAR &USERNAME VALUE(%SST(&RCVVAR 29 10))
CHGVAR &JOBNBR VALUE(%SST(&RCVVAR 39 6))
CHGVAR &SPLFNBR VALUE(%BIN(&RCVVAR 45 4))
CHGVAR &JOBSYSNAM VALUE(%SST(&RCVVAR 49 8))
/* Convert the date from the QSPRILSP format to temp variables */
IF COND(%SST(&RCVVAR 57 1) *EQ '0') THEN(CHGVAR &UPYEAR VALUE('19'))
ELSE (CHGVAR &UPYEAR VALUE('20'))
CHGVAR &LOWYEAR VALUE(%SST(&RCVVAR 58 2))
126 Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET
CHGVAR &MONTH VALUE(%SST(&RCVVAR 60 2))
CHGVAR &DAY VALUE(%SST(&RCVVAR 62 2))
/* Convert the time from the QSPRILSP format to temp variables */
CHGVAR &HOUR VALUE(%SST(&RCVVAR 64 2))
CHGVAR &MINUTE VALUE(%SST(&RCVVAR 66 2))
CHGVAR &SECOND VALUE(%SST(&RCVVAR 68 2))
/* Get the job date format */
RTVJOBA DATFMT(&DATFMT)
/* Format for all date formats except julian */
SELECT
WHEN (&DATFMT *EQ '*YMD') +
THEN(CHGVAR &CRTDATE VALUE(&UPYEAR *CAT &LOWYEAR *CAT &MONTH *CAT &DAY))
WHEN (&DATFMT *EQ '*MDY') +
THEN(CHGVAR &CRTDATE VALUE(&MONTH *CAT &DAY *CAT &UPYEAR *CAT &LOWYEAR))
WHEN (&DATFMT *EQ '*DMY') +
THEN(CHGVAR &CRTDATE VALUE(&DAY *CAT &MONTH *CAT &UPYEAR *CAT &LOWYEAR))
OTHERWISE
ENDSELECT
CHGVAR &CRTTIME VALUE(%SST(&RCVVAR 65 6))
/* Copy the spooled file to the database file and library passed to us */
CPYSPLF FILE(&SPLFNAME) TOFILE(&LIB/&FILE) JOB(&JOBNBR/&USERNAME/&JOBNAME) +
SPLNBR(&SPLFNBR) JOBSYSNAME(&JOBSYSNAM) CRTDATE(&CRTDATE &CRTTIME) +
TOMBR(&MBR) MBROPT(*REPLACE) CTLCHAR(*NONE)
/* Delete the spool file */
DLTSPLF FILE(&SPLFNAME) JOB(&JOBNBR/&USERNAME/&JOBNAME) +
SPLNBR(&SPLFNBR) JOBSYSNAME(&JOBSYSNAM) CRTDATE(&CRTDATE &CRTTIME)
ENDPGM
– Save your source file, and then compile it:
CRTCLPGM PGM(SAMPLEDB/SAVESPLF) SRCFILE(SAMPLEDB/SRCPF) SRCMBR(SAVESPLF)
2. Now we write some code that can use the SAVESPLF program. In Example 4-101, we
invoke the DSPAUTUSR command through QCMDEXC using the CallPgm method we
created earlier (see Example 4-94 on page 121). The DSPAUTUSR command produces a
spool file called QPAUTUSR. Next, we call the SAVESPLF program we created in step 1
on page 124 to copy the spool file into a database file. Finally, we open a DataReader to
read the user information from the database file.
Example 4-101 Processing a CL command that produces a spool file
// Create and open a connection to the iSeries.
iDB2Connection cn = new
iDB2Connection("DataSource=myiseries;DefaultCollection=sampledb;");
cn.Open();
// Call the CallPgm method to execute the DSPAUTUSR command
// and tell it to spool the output.
bool success = CallPgm("DSPAUTUSR OUTPUT(*PRINT)", cn);
// If the call succeeded, create a temp file and call the
// SAVESPLF program, which will copy the spool file into
// our database file.
if (success == true)
{
// Create the file. We don't care if it already exists.
CallPgm("CRTPF FILE(SAMPLEDB/DSPAUTUSR) RCDLEN(132)", cn);
// Clear the file (in case it already has data in it).
CallPgm("CLRPFM FILE(SAMPLEDB/DSPAUTUSR) MBR(*FIRST)", cn);
// Call the SAVESPLF program.
// This program will copy the spool file we just
// created into the file we specify.
success = CallPgm("CALL SAMPLEDB/SAVESPLF PARM(SAMPLEDB DSPAUTUSR *FIRST)", cn);
}
// If we get to here without any errors, then we should have a
// file in our SAMPLEDB schema called DSPAUTUSR, which contains
// a list of authorized users on the system.
if (success == true)
{
// Open a DataReader to read a list of the authorized
// users on our system.
iDB2Command cmd = new iDB2Command("SELECT * FROM DSPAUTUSR", cn);
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
// To make our example shorter, we'll only
// look at the user profile part of the string,
// which is the first 10 characters.
String userProfile = dr.GetString(0);
Console.WriteLine(userProfile.Substring(0, 10));
}
// Close the DataReader since we're done using it.
dr.Close();
// Dispose the command since we no longer need it.
cmd.Dispose();
}
// Close the connection since we're done using it.
cn.Close();