Programmatically Retrieve Defined System i Names

February 27, 2008 Michael Sansoterra

In recent tips, I have discussed some of the useful client side tools that IBM has given to developers in the iSeries Access programmer’s toolkit for Windows. In this tip, I’d like to discuss how a Windows script or program can access all of the system names defined within iSeries Navigator (soon to be renamed System i Navigator).

In these days of multiple System i systems and LPARs, it is increasingly common to see multiple systems defined within iSeries Navigator. There are several scenarios where it would be useful to harvest this information for use within a program.

For example, consider an Excel application that can execute a single query against multiple System i platforms (whether test and live, or between a single byte and double byte application on different systems). It would be nice if Excel could present the user with a choice of system names to connect with.

For another example, say a Windows script is created to retrieve data from multiple System i machines and dump it into an Oracle or SQL server-based data warehouse. In this case, rather than hard coding the system name(s) within the script, the script can automatically learn which system names it needs.

To get the list of defined system names, IBM has given us a “SystemNames” class that can be used to retrieve this list. The following Visual Basic for Applications (VBA) code shows how to generate the list of system names:

Sub BuildSystemsList()
Dim AS400Systems As cwbx.SystemNames
Set AS400Systems = New cwbx.SystemNames
Dim i As Integer
For i = 1 To AS400Systems.Count
    Debug.Print i, AS400Systems(i)
Next
End Sub

In order to use this library, make sure you reference the “IBM AS/400 iSeries Access for Windows ActiveX Object” library in your VBA environment (in Microsoft Office applications you can find it under Tools→References). When using VBScript or late binding VBA code, use the CreateObject command:

Set AS400Systems = CreateObject("cwbx.SystemNames")

This simple loop can be modified to do something like establish an ODBC connection to each system using the ActiveX Data Objects library:

Sub ODBCConnection()

Dim AS400Systems As cwbx.SystemNames
Set AS400Systems = New cwbx.SystemNames
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim i As Integer
For i = 1 To AS400Systems.Count
    conn.Open "DSN=AS400;System=" + AS400Systems(i), "UserId", "Pwd"
    '
    ' Do something
    '
    conn.Close
Next

End Sub

And here is an example showing what a prompt form might look like within an Excel application that can be used to query multiple System i systems:

Figure 1. This Excel form presents the user with possible systems to use when querying data.

The code to populate a list box on an Excel form is easy. If your Excel form contains a list box called “lboSystemNames,” here is the same basic code to populate it:

Private Sub UserForm_Initialize()
Dim AS400Systems As cwbx.SystemNames
Set AS400Systems = New cwbx.SystemNames

Dim i As Integer
For i = 1 To AS400Systems.Count
    Me.lboSystemNames.AddItem (AS400Systems(i))
Next
End Sub

Please notice that this code is sandwiched within the UserForm_Initialize() subroutine, which will automatically be executed whenever the form is run.

One other useful property of the SystemNames class worth mentioning is DefaultSystem. DefaultSystem can be used to inform the program of the default configured system within iSeries Access so that a default host can be chosen without input from the user.

Dynamically retrieving system names using this IBM library provides a good alternative to hard-coding system names into various applications and scripts.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. He can be contacted by email through the IT Jungle contact page.