Managing data in an iSeries seems to still get a lot of questions, therefore I thought I'd share some things I've been playing around with. For those who don't already know, there is an AS/400 MA which is available via the Microsoft Identity Integration Server Host Access Management Agent FP3; only if you have an MSDN id. Although I have yet had a chance to play with it, I understand this MA allows you to actually provision user accounts into an AS/400.
Another common scenario for accessing data is when the iSeries is hosting a DB2 instance with identity data needing to be touched by ILM. Here, we can use the IBM Data Access Provider to interface through .NET. This is well documented in IBM's Redbook, "Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET." For example, you can do pretty much everything such as executing SELECT, INSERT, UPDATE, DELETE, and STORED PROCEDURES. Here is a quick example for executing a SELECT statement to read data using IBM's .NET classes:
iDB2Connection conn = new iDB2Connection();
conn.ConnectionString = "DataSource=XXX.XXX.XXX.XXX;DataCompression=True;ConnectionTimeout=10;Password=XXXXXXX;UserID= XXXXXXX";
conn.Open();
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "SELECT * FROM %TABLEORVIEWNAME%";
cmd.CommandTimeout = 0;
cmd.Connection = conn;
iDB2DataReader dr = cmd.ExecuteReader();
DataTable schemaTable = dr.GetSchemaTable();
ArrayList attributeList = new ArrayList();
foreach (DataRow myField in schemaTable.Rows)
{
attributeList.Add(myField["ColumnName"].ToString().Replace("\"", ""));
}
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr[i] is string)
{
System.Console.WriteLine(String.Format("{0}= {1}", attributeList[i], dr[i].ToString().Trim()));
}
}
System.Console.WriteLine(String.Format(""));
}
The output looks something like this:
Column 1= value
Column 2= value
Column 3= value
Column 4= value
To insert data into a table, you'd do something like this:
iDB2Connection exportConn = new
iDB2Connection();
exportConn.ConnectionString =
"DataSource=XXX.XXX.XXX.XXX;DataCompression=True;ConnectionTimeout=10;Password=XXXXXXX;UserID= XXXXXXX ";
exportConn.Open();
string commandString = String.Format("INSERT INTO %TABLENAME% (COLUMN0, COLUMN1, COLUMN2 ");
commandString = commandString + ") VALUES (";
commandString = commandString + "VALUE0, VALUE1, VALUE2)";
iDB2Command cmd = new
iDB2Command(commandString,exportConn);
try
{
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch { throw; }
exportConn.Close();
You can also leverage common techniques for managing deltas if you're building out an XMA. As you can see, it's pretty straight forward even for non-ILM scenarios.
IBM has announced the same functionality for LINQ; however at the current beta version does not support DB2 for iSeries. Note: IBM Data Service Provider for .NET – LINQ Beta 1
