Part 3 of 3
Now, in this sample we are going to executre an i5 command to clear a table. This is the Clear Physical Member command "
CLRPFM". In ADO.NET, we are going to use the OLEDbCommand() to hold the command to be executed.
You have to make sure to use the open/closing double brackets "{{ }}" or it will not work. Then, using the ".
ExecuteNonQuery()" to execute the command.
Again, make sure your i5 Security Officer give you the permission required to execute this command.
Executing an i5 command
|
## Load GAC
[System.Reflection.Assembly]::LoadWithPartialName("System.Data")
[System.Reflection.Assembly]::LoadWithPartialName("IBM.Data.DB2.iSeries")
## Setup you userID and Password
[string] $i5UID = 'YourName_here';
[string] $i5PWD = 'YourPassword_here';
## Create connection to i5Serie
$i5Conn = New-Object System.Data.OleDb.OleDbConnection("Provider=IBMDA400;Data Source=IBMeServer;User ID=$i5UID;Password=$i5PWD;Initial Catalog=S1042B3A");
$i5Conn.Open();
## Perform a i5 command to clear physical file member
$cmd = New-Object System.Data.OleDb.OleDbCommand("{{CLRPFM SALES/EURSTRMST}}",$I5Conn);
$cmd.ExecuteNonQuery() | Out-Null;
$i5Conn.Close();
|
I you have notice, I'm piping the '$cmd.ExecuteNonQuery()' to a 'Out-Null' cmdlet becuase I don't want the result to returned to the consoie. Now, during debuggin ypou may want to avoid using this so you can see the numbers of record been affected or a returned value (0 or 1).
As you can see with these three blog entries you can connect, query, and run a command on your i5 System.
Part 2 of 3
Using the provider IBM ADO.NET provider, in this sample will query a table in our i5 System. Now, in this sample code it will access the table and display onky the first 10 records.
DB2 Query using IBM ADO.NET
|
## Load GAC
[System.Reflection.Assembly]::LoadWithPartialName("System.Data")
[System.Reflection.Assembly]::LoadWithPartialName("IBM.Data.DB2.iSeries")
## Setup User
[string] $i5UID = 'YouUserID_here';
[string] $i5PWD = 'YourPassword_here';
## Create connection to i5Serie
$i5Conn = New-Object System.Data.OleDb.OleDbConnection("Provider=IBMDA400;Data Source=IBMeServer;User ID=$i5UID;Password=$i5PWD;Initial Catalog=S1042B3A");
$i5Conn.Open();
## Select records to build a dataset collection
$ds = new-object "System.Data.DataSet"
$q = "SELECT EUSTRN,EUODAT,EUCDAT FROM Sales.EURSTRMST"
$da = new-object "System.Data.OleDb.OleDbDataAdapter" ($q, $i5Conn)
$da.Fill($ds)
## Loading records into the object
$c = $ds.tables | Select rows
## Display the first 10 records
$c.rows | Select -First 10
|
As you might notice, how do I get the properties ".tables" and/or ".rows". You can find these properties by using the "get-member" or the alias "gm" everytime you create a variable.
Next, will use PowerShell to execute an i5 command.
Part 1 of 3
First, you need to install your "IBM iSeries Access for Windows" and make sure to include to load the ".NET provider". Then, you can use the IBM ADO.NET provider to connect to the i5 System.
The i5 System is a very secure environment and may need to discuss access permissions to the database(s) with your i5 Securiy Officer.
Now, the following simple sample will get you connected to the i5 System:
IBM ADO.NET connection
|
## Load GAC
[System.Reflection.Assembly]::LoadWithPartialName("System.Data")
[System.Reflection.Assembly]::LoadWithPartialName("IBM.Data.DB2.iSeries")
## Create strongly typed variables to hold your ID and Password
[string] $usr = 'YourUserID_here';
[string] $pwd = 'YourPassword_here';
## Build connection to i5
$i5Conn = New-Object System.Data.OleDb.OleDbConnection("Provider=IBMDA400;Data Source=IBMeServer;User ID=$usr;Password=$pwd;Initial Catalog=S1042B3A")
$i5Conn.Open();
|
Now, building a strongly typed variable served the purpose to hold the content as a full string. I had the experience that my userID had a $ and by not defining my variable string enougn then PowerShell was expecting another variable.
Here's the one line provider string: (in the sample is wrapped at the end of the line)
"
Provider=IBMDA400;Data Source=IBMeServer;User ID=$i5UID;Password=$i5PWD;Initial Catalog=S1042B3A"
Next, will use PowerShell to query an i5 table.