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
Hi, can you help me where I cannot find where to download IBM Client Access. Got the dll from nuget but cannot use it.
Hi,
Sorry for late reply :-). You need to install IBM client access software beforehand. It is client solution in order to access iSeries via Windows. After install it then the dll is located at the installation folder