Calling iSeries (AS/400) Commmand Using Visual Basic.NET

This blog post demonstrate on how to run iSeries Command using VB.Net. This is needed for example to change user ID password via an application.
Schedule Task also might be used it to perform some jobs that have conditional things or validation.

In order to run AS/400 Command via Visual Basic,you need a pre-requisite. You have to install IBM iSeries Client Access and make reference to IBM.Data.DB2.iSeries.dll in your application.
In my computer the library is located in C:\Program Files\IBM\Client Access folder.

Basically to call AS/400 Command, you will use ADO.Net objects and execute QSYS.QCMDEXEC syntax.
For example, if you have a program called MYPGM that you want to execute, you could send this SQL statement to the AS/400:
CALL QSYS.QCMDEXC('CALL PGM (MYLIB/MYPGM)', 0000000022.00000)
Note: The second parameter must contain exactly 15 digits: ten to the left of the decimal and five to the right.
You can write a program to count the length of the command and create the second parameter for you

Let’s try out a simulation. This simulation creates a AS/400 Library, Copy file from another Library, Show copied file’s records, Delete that file & library.

Helper Procedure/Function

Function ExecuteTable(ByVal sql As String) As DataTable
	Dim cn As iDB2Connection = New iDB2Connection("DataSource=192.168.1.3;DefaultCollection=S08XXXYY; UserId=MyUserID; Password=MyPWD")
	cn.Open()

	Dim cmd As New iDB2Command(sql, cn)
	Dim da As New iDB2DataAdapter(cmd)
	Dim dt As New DataTable()
	da.Fill(dt)
   
	da.Dispose()
	cmd.Dispose()
	cn.Close()
	Return dt
End Function
Sub ExecuteNonQuery(ByVal sql As String)
	Dim cn As iDB2Connection = New iDB2Connection("DataSource=192.168.1.3;DefaultCollection=S08XXXYY; UserId=MyUserID; Password=MyPWD")
	cn.Open()

	Dim cmd As New iDB2Command(sql, cn)
	cmd.ExecuteNonQuery()
	cmd.Dispose()
	cn.Close()
End Sub

These are helper functions to execute As400 command via Visual Basic.NET

Create a Library
The create library As400 command is CRTLIB LIB(MYLIB) TYPE(*TEST) TEXT('TEST ONLY FROM PROGRAM').

Sub CreateLib()	
	Dim As400CMD As String = "CRTLIB LIB(MYLIB) TYPE(*TEST) TEXT('TEST ONLY FROM PROGRAM')"
	Dim sql As String = "CALL QSYS.QCMDEXC('" & As400CMD & "',00000000" & As400CMD.Length.ToString() & ".00000)"
	ExecuteNonQuery(sql)
End Sub

Copy File

Sub CopyFile()
	dim As400CMD as String = "CPYF FROMFILE(FRLIB/CFILE) TOFILE(MYLIB/CFILE) MBROPT(*ADD) CRTFILE(*YES)  "
	Dim sql As String = "CALL QSYS.QCMDEXC('" & As400CMD & "',00000000" & As400CMD.Length.ToString() & ".00000)"
	ExecuteNonQuery(sql)
End Sub

Copy a file from library FRLIB To MYLIB

Show Records

Sub ShowFileRecords()
	Dim sql As String = "select * from MYLIB.CFILE fetch first 10 rows only"
	Dim dt As DataTable = ExecuteTable(sql)
	For Each dr As DataRow In dt.Rows
		Console.WriteLine(dr(0).ToString() & " " & dr(1).ToString())
	Next
End Sub

Above source code will view CFILE contents. This CFILE file is basically a database Table.

And So on you can make it your own.
Below are As400 commands to Clear File, Delete File, and Delete Library for your references. Please use with caution.
Clear File CLRPFM FILE(MYLIB/CFILE)
Delete File DLTF FILE(MYLIB/CFILE)
Delete Library DLTLIB LIB(MYLIB)

Regards,
Agung Gugiaji

Advertisements