Solved

How do you retrieve a list of all AS400 UserID's with their Expiration Dates from VB.Net?

Posted on 2011-09-30
5
1,143 Views
Last Modified: 2012-05-12
I see where the CWBX dll is used to retrieve profile information for one user but I want to retrieve profile information (loop through) all users.   Is this possible to do from vb.net?  If the answer is calling an AS400 command line from VB, how is that done?

Thank you,
Elliot

0
Comment
Question by:esemmoc
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 36894062
There are a number of ways to do this.  I'd suggest the following:

Use the DSPUSRPRF command to dump user profile information to a file, and then process that file:

DSPUSRPRF USRPRF(*ALL) TYPE(*BASIC) OUTPUT(*OUTFILE) OUTFILE(mylib/PROFILES)

Where "mylib" is an AS/400 library that you are allowed to use to create temporary object like this in.  You many need to ask your system administrator where a good place for this is.

Once you are done, you'll have a DB2 table in you library called "PROFILES" that you can process using ODBC or ADO.NET.

There are several Remote Command interfaces and APIS, including ActiveX, ADO/OLEDB, the Client Access C/C++ APIs, and Java.  You can also perform remote command execution over AS/400 database interfaces, including the .NET classes, ActiveX, ADO/OLEDB, C/C++ APIs, Java Toolkit classes, JDBC, and ODBC.

A great source of information, documentation, and examples is in the Clietn Access Programmer's Toolkit.  This is an optionally-installable component of Client Access (you'll need to install Client Access to get the AS/400 ODBC Driver anyway), so make sure an install it if you plan on doing AS/400 Client/Server development.

You can find some older example online at:  

http://www-03.ibm.com/systems/i/software/access/windows/toolkit/remote_command.html

In the Programmer's Toolkit, navigate to Remote Command, and you can find documentation for the various interfaces.  

Here's a simple ADO exmaple:  http://www.itjungle.com/mgo/mgo080803-story01.html

This redbook is LOADED with examples of how to connect from .NET to the AS/400 using the ADO.NET interface.  Check out section 4.6.5 for how to execute system commands using QCMDEXC.

http://www.redbooks.ibm.com/redbooks/pdfs/sg246440.pdf

One thing to note when dealing with the database interfaces:  These interfaces use the term "command" to refer to a SQL command, not an OS command.  In general, to execute OS commands, you have to either wrap them in an SQL CALL to the QCMDEXC ssystem stored procedure, or take other steps to indicate to the data provider that you are executing an OS command and not an SQL command.

- Gary Patterson

0
 

Author Comment

by:esemmoc
ID: 36894282
Thank you for your reply. I'll pursue your suggestions.

I just want to eliminate the possibility of any methods or properties in the CWBX library that can be used to accomplish the same thing.  The following example uses the PasswordExpireDate property for a single user:

  Dim systemNames As New cwbx.SystemNames
  Dim as400 As New cwbx.AS400System
       
  as400.Define(systemNames.DefaultSystem)
  as400.ValidateMode = cwbx.cwbcoValidateModeEnum.cwbcoValidateAlways
  as400.VerifyUserIDPassword("joesmith","joepassword")

  MessageBox.Show(as400.PasswordExpireDate)

This works but you have to know the password for the user.  Any way to retrieve the expire information for all users using this approach?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 36895223
Any way to retrieve the expire information for all users using this approach?

Not that I am aware of. That's not quite the purpose of it.

Gary's suggestion is a better approach. There are alternatives, but more advanced methods are needed. User profiles are persistent objects on the system that require object methods for retrieving attributes. Gary's suggestion bypasses a number of complicating steps. It's not always straightforward to access object attributes on a different system, especially from security/sensitive objects.

Tom
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 250 total points
ID: 36897087
The cwbx.AS400System.PasswordExpireDate property is there to allow client applications to provide users a warning of the need for an upcoming password change.  

It is not a general interface user profile objects, and only allows access to a limited amount of information about the currently-signed on user profile.

There are three basic "general purpose" interfaces that I can think of that allow access to user profile data:

1) A command-based interface via DSPUSRPRF / RTVUSRPRF that we've already discussed.  Convenient if you need to dump information to a file, since that is directly supported.  Also understands *ALL and wildcard profile names.

2) The AS/400 Java Toolbox class.  Very flexible, and offers access to some user-related attributes that are not stored in the User Profile object, inclusing some System Directory attributes (com.ibm.as400.resource.RUser): http://publib.boulder.ibm.com/infocenter/iadthelp/v7r1/index.jsp?topic=/com.ibm.etools.iseries.toolbox.doc/javadoc/com/ibm/as400/resource/RUser.html

3) The native system API interface. This is the core API, and the commands and toolbox both eventually call this API: QUSRJOBI  http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fapis%2Fqusrjobi.htm

- Gary Patterson
0
 
LVL 27

Expert Comment

by:tliotta
ID: 36905545
I'd expect the core API to be the Retrieve User Information (QSYRUSRI) API -- after creating a user space, then listing user profile objects into that space and retrieving user profile identifiers from the space to pass into the QSYRUSRI API.

I'm not clear how the QUSRJOBI API would be used. I assume that it got mentioned by mistake (perhaps while working on a different question concurrently?)

Tom
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now