MV / Relational exchanges – Part 2

In the U2 forum there have been a couple questions on the new U2/DB2 .NET data provider from IBM. I thought I’d write up a couple quick notes.

Warning: While this started as a completely objective note, there are uncharacteristic, gratuitous solicitations here, because this is about exactly the sort of thing that we do for a living. This blog is about sharing information, not solicitation, but I don’t see anything wrong with occasionally making it clear that we do sell products and services that make use of the information that I provide here. Another way of saying it is that none of this is theory, so if you’re really interested in solutions, please let me know.

Brian Leach’s notes, from a recent post in the U2 forum, are right on:

The problem is that AFAIK only the selects – either direct or using the SQLExecDirect inside a Basic program – seem to return actual datasets. I haven’t tried it but I suspect that using a regular execute of a SELECT will only produce the output in string format, not as a dataset. It always did under OleDB, which is why the SQLExecDirect() is such a useful function. In fact, it’s a useful way to generate and parse data anyway for popups, listings etc outside of the SQL APIs – hmm now that sounds like another article <grin>.

On the .Net side, you’re not limited to datasets for binding. You can bind most controls (e.g. gridview) to a typed collection like a List<MyType>. If you want to get more fancy you can bind through an ObjectDataSource though that means wrapping your List<MyType> into a class that will provide select, update and insert methods. These are being used quite heavily for mainstream three tier solutions where the business layer is split off from the data access tier, so your web guys should be comfortable with that.

Of course, parsing string data is a pain so I would probably wrap the non-selectable data into a simple XML format in Basic using some form of template – which should be quick, it’s what Basic is good at. Then use LINQ on the .Net side to parse it into an IEnumerable collection. Now that’s a buzzword-compliant sentence.

• Pulling MV data into a string array is easy.
• Arrays can be wrapped in strongly typed classes.
• Class objects can be exposed as Generic List<MyType> instances.
• These can serve as ObjectDataSources for just about anything.
• XmlDataSources may be just as good if you already feel comfortable using the U2 XML capabilities, but this wouldn’t be my preference.

I know nothing about the new .NET data provider from IBM, so I can’t tell you how that works. I can tell you that mv.NET does a lot of this work for you automatically. An mvItemList object is a list of raw items (or item segments to reduce bandwidth). The DataTable property of this object returns a real DataTable which can be bound to anything that can use it. Columns are referenced using their dictionary definition names.

These days it seems there are only two complications with moving data from MV into any relational environment.

The first is getting all of the data types to match up with Dictionary-as-schema on one side and strongly typed RDBMS schema on the other. That’s not too much of an issue with mv.NET because it supports typing and easily converts common MV data into strong types accepted elsewhere.

The second is how to move the data from MV into the relational environment in the most performant way. Ideally we could just stream data from one environment to the next. The perfect scenario would be to have MV stream data directly into a SqlBulkCopy object. I’ll work on this if I can find some time or a client asks for it. The next best thing is to extract all the MV data at once, and the DataTable property does exactly that, morph that to the proper data typing, then mass-move that again using a SqlBulkCopy. Unfortunately this sort of streaming requires a large in-memory image of the data. The final and most common method so far is to pull in a record from MV, then use the data to update a SqlCommand object and related Parameters, then use an Insert or Update command to move the data into relational. Loop as required. But that can be slow. How slow? I dunno yet, I haven’t benchmarked this technique but I know it works. SqlBulkCopy can reportedly move data 4 times faster than a mass of Insert Statements.

There is also another technique for doing inserts, by creating massive Insert or Update statements with a Union clause, then executing these all in one shot. Again, reportedly, the performance is supposed to be some orders of magnitude faster than individual CRUD queries. I would look closely at this option because the mvItemList can return any number of items on demand rather than retrieving an entire resultset in one shot. So controlled batches can be processed without killing off memory or bandwidth.

Since we’re here, one of the U2 forum postings mentioned an issue with the IBM .NET data provider and Windows Vista. mv.NET is certified for Windows Vista and we have clients running with it.

For you U2 users, here’s a hint – the new U2.NET utility is really an older version of mv.NET which has been modified by IBM. You’re getting it for free, with some enhancements and some limitations, but it’s certainly not the latest and greatest mv.NET. I believe you can do all of the things mentioned here with your free tools, but if you want to be guaranteed of getting the job done in some number of days from start to finish, then please consider getting mv.NET from Nebula R&D, along with consulting services from people who know how to do this. Your tasks will be completed quickly, rather than having someone spending months trying to get free information in forums about free tools. I have nothing against free tools, (see what I’m using) but I’ve found that many "free" solutions have high long-term costs in terms of the time required to make them do what’s required. That’s not a sales line necessarily, just something to think about… Now if you want a sales line… please consider contracting with Nebula R&D for services even if you are using the free tools. Our people have expertise that may help to reduce your development time, and considering the similarities between mv.NET and U2.NET, you have a ready group of people here who are already familiar with the tools that IBM has given you.