Archive

Archive for the ‘iSeries’ Category

Stored Procedure vs Parameterized Query

June 10, 2010 3 comments

I want to start by saying I am not a database expert or an DBA by any stretch of the imagination.  I am, though, an old AS/400 RPG developer.  I believe that by necessity AS/400 developers tend to be more familiar with the native database functions and maintenance than their .NET counterparts.  Even so, there are a great many capabilities that go unused by the day to day coder.

On the other hand, .NET developers tend to be more familiar with things like SQL and Stored Procedures.  While these tools are second hand to most developers, they are far less common in RPG applications because RPG has direct database access.  These are merely observations: each access technique has its strengths and weaknesses, a discussion of which is not the purpose of this article: I’ve probably rambled on about it too long already.

My day to day experience is typically making these two worlds peacefully coexist.  The task this week is another conversion project, but this time from AS/400 tables to AS/400 tables.  An argument could be made that I should have done the conversion in RPG using native database access, but in this particular case, I have some string manipulation that is far easier to handle in .NET than RPG.

The Project

The project is simple enough: in our legacy application we are making several significant changes: normalizing data, expanding configuration options, and extracting embedded data into a supporting table.  The normalizing part requires that we combine data from two tables into a single repository.  At the same time, those values contain embedded information that needs to be extracted and stored in a separate table. 

The configuration of that embedded data has been expanded.  It used to be fixed but is now much more flexible, allowing the client to configure the description, rules, and presentation of the data.  As a result, each embedded item also produces a series of table entries in a related table: the number of entries and rules are not known at design time.

This may not be totally clear, but the result is the important part here.  In a small client data set, what used to be 3,458 records in one table and 3,013 in the 2nd table is now 6,471 records in a single table.  Those 6,471 also create 38,862 records in the extracted data table.  There is also a 3rd table with 6,471 rows that acts as a cross reference between these this table and the legacy system that adds some additional information.  So all in all, we’ve gone from 6,471 records in two tables, poorly normalized, into three tables with a total of 51,804 records.

The Problem of Performance

Obviously these are not big tables, but performing the table inserts over the wire from a .NET application incurs a big performance hit.  I’m not going to point fingers, because I honestly don’t know where to point: it could be a problem with the SQL engine on the iSeries, or with the ADO.NET Managed Provider (our version is V5R4, which is still based on .NET 1.1).  It’s unlikely that it is the network or the my PC, but I won’t rule anything out for certain.

I wrote previously about using Stored Procedures on the iSeries to wring out the last bit of performance.  Overall, we’ve been very satisfied, and performance has been excellent.  I wrote a few Stored Procedures to handle the inserts to these three tables and used them from my .NET application to perform the inserts.  Quite unexpectedly, when I tested this small data set the performance was terrible.  It took 57 minutes to perform this task, and this was our smallest data set.  Our largest is more than 10 times the size, so it could potentially run 10-12 hours.

At first, I was tempted to leave it alone: this is not production code, but rather a utility that will be run once for each of our clients and then discarded.  At least, that’s what I thought.  I later realized that each client may have multiple data sets, so this may be run as many as 10 times per client.  Then on top of that, I realized that my staff has to run this for all the clients.  At that point, performance for this “one time” application suddenly became a huge problem.

Things aren’t always what they seem…

I was floored at these initial results.  The reason we started using Stored Procedures is because they are supposed to provide the highest degree of performance.  I refactored the logic twice trying to speed it up but to no avail.  I assumed I had done something wrong in the procedure, so I hit the books, but I didn’t find anything. There’s not a lot to a procedure that just maps incoming parameters to an Insert statement. 

There is one thing in the procedure, though, that I believe was the culprit.  The way our legacy database is structured, you never know until run time what library (database) and file (table) you need to execute the function against.  This means that the variables indicating those have to be passed in to the Stored Procedure … every … time. 

This has not been a problem when pulling data, but I believe because of the frequency and interval with this mass of Insert statements that it was really hampering performance.  I believe that if the Stored Procedure had specified the library (database) and file (table) names then the performance would not have been an issue. The system had to determine what access path to use to the database on every single request.  The purpose of using Stored Procedures in the first place had been completely negated.  I might as well have been using dynamic strings in my code.

The Solution

Fortunately, I came up with a solution I can live with: I turned to an old friend, the Parameterized Query.  As I got to thinking about the problem, I realized that what I needed was to create the command object once and reuse it.  I used Parameterized Queries frequently before turning to Stored Procedures, and they suit this particular problem because I only need to set the library (database) and file (table) names once.

So I fired up a branch in Mercurial and hacked up my code.  I refactored out some of my refactorings and replaced all the Stored Procedure calls with Parameterized Queries.  I made sure I was only creating the command once and then reusing it for each insert.  I anxiously ran the program again: 9 minutes later I had the confirmation I needed.  This was MUCH better.

So the lesson for today is to never trust your assumptions.  I was sure that Stored Procedures would automatically give me the best performance.  I wasted time developing the procedures, I wasted time implementing them, then I wasted time testing and debunking my incorrect assumption.  Now I’m wasting time writing about it :-)  But it reminds me of a very valuable tip: choose the right tool for the right job.

Categories: AS400, Database, iSeries, System i

Working with AS400-iSeries-System i Stored Procedures in .NET

October 26, 2009 6 comments

At my company our primary database is the IBM System i, formerly known as iSeries, formerly known as AS/400, and still affectionately referred to as simply “the 400” by most people I know.  The 400 is a fantastic database server: fast, reliable, secure, and best of all the darn thing just never breaks.

I spend a great deal of my time writing .NET apps that use the 400 as the backend database.  In the past this has meant a lot of dynamic SQL, which I am extremely comfortable developing.  Unfortunately, this can be pretty brittle, difficult to debug, and performance frequently lags.  I’ve done lots of things to improve performance, like using Parameterized Queries. 

In an effort to wring out the last bit of performance and improve the quality of our code, we have been making an effort to expose our data via Stored Procedures. How to create those procedures on the 400 is a topic I’m not going to delve into, mostly because I haven’t been doing it myself, but also because there are lots of options.  In our case we are writing them in SQL, but they could just as easily execute an RPG program.

Consuming a Stored Procedure

I use the IBM DB2 iSeries .NET Data Provider, V5R4.  I also have SP1 installed (SI35287) which corrects a nasty ObjectDisposedException problem that’s been floating around since .NET 2.0 came out.  The classes are all in the IBM.Data.DB2.iSeries namespace.  Be sure you add a reference to <installed_location>\IBM\Client Access\IBM.Data.DB2.iSeries.dll.

The process is actually pretty simple.  Create a Connection, create a Command, add some Parameters, Execute the Command, Process the results.

var connectionstring = String.Format(ConnectionStringFormat,
    IpAddress, User, Password);
_conn = new iDB2Connection(connectionstring);
_conn.Open();

if (_conn != null && _conn.State == ConnectionState.Open;)
{
    _cmd = _conn.CreateCommand();
    _cmd.CommandTimeout = 0;
    _cmd.CommandType = CommandType.StoredProcedure;
    _cmd.CommandText = "LIBNAME.PROCNAME";

    // Add Parameters
    _cmd.Parameters.Add(name, type, size);
    _cmd.Parameters[name].Value = value;
    _cmd.Parameters[name].Direction = direction;

    _cmd.Prepare();

    // For individual value returns (direction intput/output or output
    _cmd.ExecuteNonQuery();
    var dict = new Dictionary();
    foreach (iDB2Parameter parm in _cmd.Parameters)
    {
        if (parm.Direction == ParameterDirection.Input)
            continue;

        dict.Add(parm.ParameterName, parm.Value);
    }

    // For collection value returns
    var adapter = new iDB2DataAdapter(_cmd);
    var ds = new DataSet();
    adapter.Fill(ds);

    _cmd.Dispose();
    _conn.Close();
    _conn.Dispose();
}

Testing Stored Procedures

I quickly found when we started down this path that I was missing a way to easily test Stored Procedures, and since we were planning on writing lots of them this just would not do.

Executing ad-hoc SQL against the 400 can be a draining experience.  There is a command line tool called Interactive SQL (STRSQL in 400 speak) and a Java tool embedded deep in Operations Navigator which I wrote an article about this back in my ITJungle days. Disappointed in the available tools, my first serious .NET application, written in .NET 1.1 back in 2003, was an ad-hoc SQL tool for the iSeries called iHoc Query.  We still use it in house today but I quit distributing it a while back for various reasons.  Mostly it needs an overhaul and I just don’t have the time to spare.

Unfortunately, I never could get ad-hoc execution of a Stored Procedure to work against the 400, which meant I needed to find a tool. There is a utility out there called SQLThing, which is where we started.  It seemed to work fine, with a few glitches here and there, but I needed to get a good handle on how to code for this data access approach.  It also did not have some of the features I wanted, so naturally I hacked together something myself.

It doesn’t look pretty (I wrote it in WinForms), but it works very well and has these goodies:

  • Retains Data Connection information
  • Supports Input, Output, and Input/Output parameter directions
  • Supports returning multiple result sets, displaying each in a tab
  • Allows saving and loading of Stored Procedures, so you can reuse them without reentering the parameters each time

Here is a screen shot:

TestStoredProcScreenShot

You can download this tool here.

Some helpful code

I wrote a wrapper class called SprocEngine to isolate the ugly Managed Provider code and make it more palatable to use them in my programs. Here is the code for the SprocEngine class:

using System;
using System.Collections.Generic;
using System.Data;
using IBM.Data.DB2.iSeries;

namespace SprocEngine
{
    public class As400SprocEngine : PropertyChangedBase, IDisposable
    {
        public enum As400DbType
        {
            Numeric,
            Character
        }

        public As400SprocEngine()
        {
        }

        public As400SprocEngine(string ip, string user, string password)
            : this()
        {
            IpAddress = ip;
            User = user;
            Password = password;

            ConnectToDatabase();
        }

        private Dictionary _typeConverter =
            new Dictionary()
                {
                    {As400DbType.Numeric, iDB2DbType.iDB2Numeric},
                    {As400DbType.Character, iDB2DbType.iDB2Char}
                };

        private iDB2Connection _conn = null;
        private iDB2Command _cmd = null;

        private string _ip;
        private string _user;
        private string _pass;

        private const string ConnectionStringFormat = "DataSource={0};UserID={1};Password={2};";

        public string QualifiedProcedureName
        {
            get
            {
                return _cmd.CommandText;
            }
            set
            {
                _cmd.CommandText = value;
                FirePropertyChangedEvent("QualifiedProcedureName");
            }
        }

        public bool IsConnected
        {
            get { return _conn != null && _conn.State == ConnectionState.Open; }
        }

        public string IpAddress
        {
            get { return _ip; }
            set { _ip = value; FirePropertyChangedEvent("IpAddress"); }
        }

        public string User
        {
            get { return _user; }
            set { _user = value; FirePropertyChangedEvent("User"); }
        }

        public string Password
        {
            get { return _pass; }
            set { _pass = value; FirePropertyChangedEvent("Password"); }
        }

        public List ParmTypes
        {
            get
            {
                var list = new List();
                list.AddRange(Enum.GetNames(typeof(iDB2DbType)));
                return list;
            }
        }
        public void ConnectToDatabase()
        {
            if (IpAddress.Equals("") || User.Equals("") || Password.Equals(""))
                throw new ArgumentException("Required field is blank.");

            var connectionstring = String.Format(ConnectionStringFormat,
                IpAddress, User, Password);
            _conn = new iDB2Connection(connectionstring);
            _conn.Open();

            FirePropertyChangedEvent("IsConnected");

            if (IsConnected)
            {
                _cmd = _conn.CreateCommand();
                _cmd.CommandTimeout = 0;
                _cmd.CommandType = CommandType.StoredProcedure;
            }
        }

        public void ClearParameters()
        {
            _cmd.Parameters.Clear();
        }


        public void AddParameter(string name, As400DbType type, object value)
        {
            AddParameterIDB2(name, _typeConverter[type], null, value, ParameterDirection.Input);
        }

        public void AddParameterIDB2(string name, iDB2DbType type, object value)
        {
            AddParameterIDB2(name, type, null, value, ParameterDirection.Input);
        }

        public void AddParameter(string name, As400DbType type, int? size, object value, ParameterDirection direction)
        {
            AddParameterIDB2(name, _typeConverter[type], size, value, direction);
        }

        public void AddParameterIDB2(string name, iDB2DbType type, int? size, object value, ParameterDirection direction)
        {
            if (size.HasValue)
            {
                _cmd.Parameters.Add(name, type, size.Value);
            }
            else
            {
                _cmd.Parameters.Add(name, type);
            }
            _cmd.Parameters[name].Value = value;
            _cmd.Parameters[name].Direction = direction;
        }

        public Dictionary ExecuteSproc()
        {
            try
            {
                _cmd.Prepare();
                var q = _cmd.ExecuteNonQuery();

                var dict = new Dictionary();
                foreach (iDB2Parameter parm in _cmd.Parameters)
                {
                    if (parm.Direction == ParameterDirection.Input)
                        continue;

                    dict.Add(parm.ParameterName, parm.Value);
                }

                if (dict.Count <= 0)
                    dict.Add("AffectedRowCount", q);

                return dict;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet ExecuteSprocCollection()
        {
            try
            {
                _cmd.Prepare();

                var adapter = new iDB2DataAdapter(_cmd);
                var ds = new DataSet();
                adapter.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region IDisposable Members
        public void Dispose()
        {
            _cmd.Dispose();
            _conn.Close();
            _conn.Dispose();
        }
        #endregion
    }
}

It inherits a simple class that implements INotifyPropertyChanged called PropertyChangedBase.  If you aren’t planning on binding this to WPF you can remove it, but if you want it here is the code for it:

using System.ComponentModel;
namespace SprocEngine
{
    public class PropertyChangedBase : INotifyPropertyChanged
    {
        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        #endregion

        internal void FirePropertyChangedEvent(string propertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

Using SprocEngine

Here are a couple of samples showing how to use SprocEngine.

Processing a single returned value:

using (var sproc = new As400SprocEngine(ipaddress, user, password))
{
    sproc.QualifiedProcedureName = ProcName;
    sproc.AddParameter("libname", As400SprocEngine.As400DbType.Character, 10,
        null, ParameterDirection.InputOutput);

    // Execute SPROC
    var retVals = sproc.ExecuteSproc();
    if (retVals == null || retVals.Count <= 0 )
        return null;

    string DataLibrary = retVals["libname"].ToString().Trim();
}

Processing multiple result sets:

using (var sproc = new As400SprocEngine(ipaddress, user, password))
{
    sproc.QualifiedProcedureName = ProcNames[searchType];
    sproc.AddParameter("@prefix", As400SprocEngine.As400DbType.Character, localityPrefix);
    sproc.AddParameter("@record", As400SprocEngine.As400DbType.Numeric, record);
    var header = new ParcelHeaderCollection();
    try
    {
        var ds = sproc.ExecuteSprocCollection();

        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            var parcel = new ParcelHeader();
            parcel.Record = Convert.ToInt32(dr["mrecno"].ToString());
            parcel.Card = Convert.ToInt32(dr["mdwell"].ToString());
            parcel.OwnerName = dr["mlnam"].ToString().Trim();
            parcel.Map = dr["mmap"].ToString().Trim();
            parcel.StreetNumber = Convert.ToInt32(dr["house"].ToString());
            parcel.StreetName = dr["mstrt"].ToString().Trim();
            parcel.OccupancyCode = Convert.ToInt32(dr["moccup"].ToString());
            parcel.SalesPrice = Convert.ToInt32(dr["msellp"].ToString());
            parcel.YearSold = Convert.ToInt32(dr["myrsld"].ToString());
            parcel.MonthSold = Convert.ToInt32(dr["mmosld"].ToString());

            header.Add(parcel);
        }
    }
    // - you should use a more descriptive Exception type here
    catch (Exception ex)
    {
        throw ex;
    }

Final Notes

As always, Your Mileage May Vary.  This is written specifically for the iSeries, but you should be able adapt it to any other provider.  I have not worked much with Stored Procedures on any other system, so I’m not sure how it may need to be varied.

I hope you find this helpful. If you work in .NET with the iSeries, please share any tips or tricks you may have in the comments below.

Categories: AS400, iSeries, System i

IBM iSeries .NET Managed Provider ObjectDisposedException problem SOLVED

October 23, 2009 1 comment

I can’t believe how remiss I’ve been: this has been a big problem for me since early 2007, and we finally got it solved a couple of months ago, and I can’t believe I never blogged about it!

Two things to fix this:

1) Install iSeries Client Access V5R4.  Be sure to install the .NET Managed Provider if it is not already installed.  A full install will NOT do this, you must check the box to include it.

2) Install SI35287 on any PC that is connecting to the 400.  This is essentially V5R4 SP1 and will finally solve this little issue.

I do not know what the status of this bug on V6R1 is, but I will say that once I installed the SP this problem finally stopped.

Categories: .NET, AS400, iSeries, System i
Follow

Get every new post delivered to your Inbox.