Return a User Profile Listing to a .NET Client

I am a VB.NET programmer and am looking to retrieve a list of all users and their expiration dates on our AS/400 system. I stumbled across the cwbx library and have been able to connect to our system, but not much else from there.

Can you suggest how this info can be retrieved from VB.NET? Thanks.

–Elliot

Hi, Elliot:

The easiest way to retrieve this user info is to use the i/OS Display User Profile (DSPUSRPRF) command and dump the results to a table (also known as an outfile):

DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/LSTUSRPRFP)

The above command (assuming you have the authority) will dump all of the user profile information (including expiration date) to a temporary table that can be queried by SQL.

To access this data from a remote client, it would probably be best to wrap this code in something called an external stored procedure. An external procedure is written in a High Level Language (HLL), such as C, COBOL or RPG, that is made accessible to SQL. The next piece of code below contains an embedded SQL RPG program that will run the above DSPUSRPRF command, query the temporary table for relevant information and return the results to the caller. The query does a few minor things, such as exclude IBM system profiles and pick a few columns out of the many available.

 //
 // List User Profile Stored Procedure Registration
 // ======================================================
 // CREATE PROCEDURE DEV/LSTUSRPRFR
 // RESULT SETS 1
 // LANGUAGE RPGLE
 // EXTERNAL
 // PARAMETER STYLE GENERAL
 // READS SQL DATA
 //
HOption(*NoDebugIO:*SrcStmt) UsrPrf(*Owner)

DQCmdExc          PR                  ExtPgm('QCMDEXC')
D Command                    32000    Const Options(*VarSize)
D CommandLen                    15  5 Const
 /Free
   CallP(E) QCmdExc('DLTF QTEMP/LSTUSRPRFP':21);
   QCmdExc(
   'DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/LSTUSRPRFP)':
   66);

     Exec SQL
         SET OPTION COMMIT=*NONE, DATFMT=*ISO;

     Exec SQL
         DECLARE USER_LIST CURSOR FOR
         SELECT UPUPRF AS PROFILE,
                UPUSCL AS USER_CLASS,
                UPTEXT AS DESCRIPTION,
                CASE WHEN UPEXPC=' ' THEN NULL
                     ELSE CAST(
                          CASE UPEXPC WHEN '0' THEN '19'
                                      WHEN '1' THEN '20'
                                      WHEN '2' THEN '21' END ||
LEFT(UPEXPD,2)||'-'||
SUBSTR(UPEXPD,3,2)||'-'||
SUBSTR(UPEXPD,5,2) AS DATE) END AS EXP_DATE
           FROM QTEMP/LSTUSRPRFP
          WHERE UPUPRF NOT LIKE 'Q%'  /* Exclude System Profiles */
         ORDER BY UPUPRF;

     Exec SQL
         OPEN USER_LIST;

     *InLR=*On;
     Return;
 /End-Free

The results sent to the client will look something like this:

Imports IBM.Data.DB2.iSeries
Module Module1
Sub Main()
Dim DB2Connection As iDB2Connection = New 
iDB2Connection
("DataSource=AS400;UserId=MIKE;Password=123;DefaultCollection=dev")
Dim DB2Command As iDB2Command = New 
iDB2Command("LSTUSRPRFR", CommandType.StoredProcedure, DB2Connection)
Dim ProfileData As DataTable = New 
DataTable()

DB2Connection.Open()
ProfileData.Load(DB2Command.ExecuteReader(CommandBehavior.CloseConnection))

For Each r As DataRow In 
ProfileData.Rows
    Console.WriteLine(r("PROFILE"))
    Console.WriteLine(r("USER_CLASS"))
    Console.WriteLine(r("DESCRIPTION"))
    Console.WriteLine(r("EXP_DATE"))
    Console.WriteLine("-----")
Next

DB2Command.Dispose()
DB2Connection.Dispose()
Console.ReadLine()
End Sub
End Module