• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Return a User Profile Listing to a .NET Client

    October 19, 2011 Hey, Mike

    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:

    PROFILE

    USER_CLASS

    DESCRIPTION

    EXP_DATE

    DRADMIN

    *SECOFR

    IBM DR Admin
    – Do not alter

    Null

    MDUNTITLED

    *USER

    IBM DB2 WEB
    QUERY DEVELOPER AUTHORITY

    Null

    MRADMIN

    *USER

    IBM DB2 WEB QUERY
    ADMINISTRATOR AUTHORITY

    Null

    MRSCHEDULE

    *USER

    IBM DB2 WEB
    QUERY BROKER SCHEDULE AUTHORITY

    Null

    NOGROUP

    *USER

     

    Null

    NICK

    *SECOFR

     

    2012-01-01

    VLPADMIN 

    *SECOFR

    VLP Admin –
    Do not alter

    Null

    ZENDADMIN

    *SECOFR

     

    Null

    ZS5250DEMO

    *USER

    Zend 5250 demo user

    2011-04-12

    You will need to register the RPG program as a stored procedure for use with SQL by using the CREATE PROCEDURE statement listed in the program header. My sample assumes that both the RPG program and the procedure will be placed in the DEV schema (a.k.a., library).

    As for consuming the results with VB.NET, I wouldn’t bother using the cwbx ActiveX controls. From VB.NET, it’s probably best to call the external stored procedure using ODBC, OLE DB, or the DB2 for i managed provider that comes with the System i Access product.

    The following piece of code contains VB.NET sample code that invokes the procedure and retrieves the results into a DataTable. The IBM managed provider (iDB2Connection, etc.) is used to make the connection.

    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
    

    If you have an HLL developer at your disposal, a fancier alternative option to achieve the same results would be as follows (in RPG, C, or COBOL):

    • Use the List Object API (QUSLOBJ) to gather all the user profiles on the system.
    • Retrieve the specific information for each profile to get the profile’s enabled status, expiration date, etc., using the Retrieve User Information API (QSYRUSRI).
    • Place the collected info into a data structure array and then return the array as a result set.
    • Consume the array from the client side using OLE DB/ODBC/managed provider/etc., just the same as you would for a database query.

    –Mike



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Focal Point Solutions Group

    A CloudSAFE Company

    The Power of Services:
    IBM Cloud and Managed Solutions

    Upgrade your business processes, and save time and resources with specialized, best-in-class IT solutions.

    Managed, Cloud, and Custom Solutions

    Managed Services

    • Infrastructure Monitoring & Management
    • Server Patching
    • Application Patching
    • Managed Backup
    • High Availability/Disaster Recovery Monitoring
    • Cloud Environment Monitoring
    • Office 365 Management
    • Endpoint Management
    • Managed Colocation

    Cloud Infrastructure

    • IBM i Private Cloud
    • IBM AIX Private Cloud
    • VMware Private Cloud
    • VMware Cloud Director
    • Multi-Tenant Cloud
    • Desktop as a Service

    Data Protection & High Availability

    • Disaster Recovery as a Service
    • Backup as a Service
    • IBM i Vaulting

    Security

    • Security Consulting
    • Remote Security Awareness Training & Education
    • Onsite Security Awareness Training & Education
    • Phishing Tests
    • Penetration Tests
    • Mail Security
    • Managed Detection & Response
    • Managed Firewall
    • Endpoint Protection
    • Vulnerability Management
    • Vendor Risk Assessments
    • Security Risk Assessments

    Professional Services

    • Server Virtualization
    • Data & Infrastructure Migrations
    • Hardware & Software Installation
    • Microsoft Office 365 Implementation & Migration
    • Infrastructure Assessments
    • IBM i Consulting
    • IBM AIX Consulting

    Focal Point provides all the tools you need to protect your data, ensure the integrity of your IT infrastructure, and keep your business running.

    Contact Focal Point to Learn More About Our IBM Solutions and Partnerships

    Follow us on LinkedIn

    focalpointsg.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Townsend Security:  View the recorded Webcast: Secure Managed File Transfers for the IBM i
    Dan Riehl Presents:  Fall Training Sale – Discounts up to 40%! RPG IV COBOL CL Admin Security
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    RTFM Being Replaced by GTFM IBM Gooses Power Systems Storage and Networking

    Leave a Reply Cancel reply

Volume 11, Number 31 -- October 19, 2011
THIS ISSUE SPONSORED BY:

SEQUEL Software
Bug Busters Software Engineering
Botz & Associates, Inc.

Table of Contents

  • Return a User Profile Listing to a .NET Client
  • Dealing with Faulty Logic
  • Admin Alert: Adding Redundancy to Power i SMS Monitoring

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Development and Modernization is Getting A Fresche Start with Some Ground-Breaking Subscriptions
  • CloudSAFE And Focal Point Solutions Group Combine Services, Unify Brands
  • Guru: Partitioning Result Sets Using SQL
  • As I See It: Elusive Connections
  • IBM i PTF Guide, Volume 25, Number 47
  • AWS Inks Deal With Connectria To Have a Power Play
  • IBM i Shops Have Alternatives to Db2 Web Query
  • Eradani Lays Waste to API Payload Restrictions
  • Four Hundred Monitor, November 15
  • Old PHP and Other PASE Apps Break on IBM i 7.5

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle