How to access iseries spool file through. net

Source: Internet
Author: User
Tags ibm db2
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 ();

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.