Some Cl commands do not produce an OUTFILE like the command in Example 4-99 does.
However, please allow you to spool the output. In this section, we show how to process
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 callthe
Dspautusr to generate a spool file, callthe 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
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 ('spr0100 ')
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 partition t 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
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
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
// Savesplf program, which will copy the spool file
// 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 shoshould have
// 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 re done using it.
Dr. Close ();
// Dispose the command since we no longer need it.
Cmd. Dispose ();
}
// Close the connection since we're re done using it.
CN. Close ();
