Powershell SQL to IBM I

Pre-requirements
First make sure java is installed for the ibm i Access tools.

Install IBM i Access
To download this you need an IBM account, wich is for free to create it if you don`t already have one.
Then go and download the the client.
After downloading extract the package.
Open the extracted folder and open the folder Windows_Application.
Now choose the right installation:
– To install the 32 bit version for the current user, double-click on install_acs_32.
– To install the 64 bit version for the current user, double-click on install_acs_64.
– To install the 32 bit version for all users on a multi-user PC, double-click on install_acs_32_allusers.
– To install the 64 bit version for all users on a multi-user PC, double-click on install_acs_64_allusers.

Follow the setup and complete the installation process.

Configuring ODBC
Open configuration panel, and select the ODBC data sources.

There are two data sources here if you are running 64-bit windows, one for 32-bit and one for 64-bit. I recommend you to configure them both.

In the window that opens, click on Add.

In the next window select the driver iSeries Access ODBC Driver and click finish.

Fill in the name for the source, this will be the name you use to connect to the IBM I server. Fill in the ip-adress or the name of the IBM I server. Click Ok.

The magic
Now we have that set up we can get going with the fun part.
Open an text-editor or a Powershell IDE and paste the following code:

##############################################
function ibmIsql([string]$Conn, [string]$SQLstmt)
{
$result = $null
$AS400Connection = New-Object system.Data.OleDb.OleDbConnection(“Provider=IBMDA400;Data Source=$Conn;User id=$IBMIusername;Password=$IBMuserpass;”)
$dataSet = new-object “System.Data.DataSet”
$da = new-object “System.Data.OleDb.OleDbDataAdapter” ($SQLstmt, $AS400Connection)
[Void]$da.Fill($dataSet)
$resultAS400 = new-object “System.Data.DataTable”
$resultAS400 = $dataSet.Tables[0]
$result = $resultAS400 | FOREACH-OBJECT { $_ }
return $result
}

$IBMIusername = “IBMIuser” #username
$IBMuserpass = “Secretpassword” #Password
$IBMItarget = “IBMIsourceName” #odbc source name

$SQLSTMT = “select SYS_DNAME as library from qsys2.systables”
ibmIsql $IBMItarget $SQLSTMT
##############################################

Change the following variables so the are correct for you:
-$IBMIusername
-$IBMuserpass
-$IBMItarget

Run the program the way you always run a powershell script.

If you have any questions or you have suggestion how to do it a better/cleaner way please let me know in the comments below.

Blogging site

WordPress Appliance - Powered by TurnKey Linux