Geeks With Blogs
A Developer's Expedition

Working at a bank, I get to play with an iSeries a lot. I've been trying to build a data warehouse that uses day old data from the iseries and importing the data into a MS SQL database. I was able to do a data import using SSIS but I started to think about about how to do this in code. It was pretty easy!

First getting the data out of the iSeries/AS400. You have to install the latest IBM data provider. You get this when you install IBM's programmers toolkit. After you install the toolkit, add the reference to your project; IBM.Data.DB2.iSeries.

using IBM.Data.DB2.iSeries;

To read data out of IBM is almost the same as any other SQL statment with the expection of the table name. IBM uses libraries so your select statment has to have the library name in the from line - not a big deal;

The select statement is the same but your from statement has to point to the library and the table. The rest is the same, make a connection object, comman object and a data adapter.

string sql = "SELECT * FROM  LIBRARY.TABLE ";

            iDB2Connection conn = new iDB2Connection(";userid=user;password=xxx");
            iDB2Command cmd = new iDB2Command(sql, conn);
            cmd.CommandType = System.Data.CommandType.Text;
            iDB2DataAdapter myCommand = new iDB2DataAdapter(cmd);
            myCommand.Fill(TempDS, "SQLStatement");
            TempDS.DataSetName = "SQLStatement";

Creating a table using SMO is pretty easy, You make a server object, a database object and a Table object. Then read through the columns to make the data types. In this example I make all of them VarChar(50). You should send the data column to an object and set the datatype in the object.

SqlConnection connection =  new SqlConnection(connectionString);
            Server server = new Server(new ServerConnection(connection));
            Database db = server.Databases["MyDataBase"];
            Table table = new Table(db, "MyTable");

            for (int i = 0; i < DT.Columns.Count; i++)
                Column c = new Column(table, DT.Columns[i].ColumnName);
                c.DataType = DataType.VarChar(50);

That is it - pretty simple. Now just read through your data table and insert the data to your new table.


Posted on Sunday, November 23, 2008 1:36 AM | Back to top

Comments on this post: Iseries/AS400 DB2

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Ken Lovely, MCSE, MCDBA, MCTS | Powered by: