So, the other day Bono called me and said “Hey, that new app you wrote is giving me some weird message about ‘corrupted memory’. It’s, like, totally Zoo Station. Should I call Bill?’. “Nah”, I replied, “it’s just U2 acting up again. It doesn’t get along so well with .Net”. “Yeah”, Bono replied”, “U2 is, like, so 1980s”.
That was Bob Bono, by the way, a technical support guy. The “U2″ we were griping about is an IBM database platform, once known by the even less apropos name of “Universe”. After buying the product several years ago IBM rechristened it in what was presumably an attempt to make it seem more modern. I don’t think they’re fooling anyone. Bono was being generous when he suggested that U2 is 20 years past its prime. This is a database system that stores all of its data as delimited strings, and whose native programming language, Pick Basic, makes heavy use of line numbers and GOTO statements and is intended for use in Telnet-based applications. U2 predates relational database technology, not to mention its namesake band.
Nonetheless, U2/Universe is still alive and kicking with a well entrenched competitive position. There is no quick or easy way to migrate the data to a modern database system, and converting Telnet-based Pick Basic code to something designed for a Windows IDE is unthinkable. So, some of us have the imposing challenge of getting U2 to play nice with .Net, a technology for which IBM has no great love and rather bare-boned support. If you are one such wretched soul, then I offer the following humble advice to get you started.
When I began my current project, a Google search revealed an assortment of 3rd party .NET drivers for U2, and an IBM implementation of OLEDB called UniOLEDB. In theory, the latter could be connected to Microsoft’s .Net Data Provider for OLEDB and .Net would happily accept it as a data source. Since the 3rd party solutions all charged significant runtime licenses (almost as much as the runtime license for the U2 database, itself), and our product is being sold to customers who would have to absorb that extra charge, the choice of the freebie UniOLEDB seemed like a no-brainer.
Yet, presumably someone was paying for the expensive 3rd party solutions, so I knew that UniOLEDB must have some serious drawbacks. By Googling UniOLEDB I was able to find a lot of questions, not so many answers, and a lot of grumbling about UniOLEDB’s deficiencies when compared to native Pick Basic coding or the old school Universe API: UniObjects.Net. (UniObjects doesn’t try to pretend that U2 is a real database with fields and relational joins and such, it just makes it a little easier to extract and parse the database’s delimited strings). But, hey, I reasoned, that didn’t necessarily mean that UniOLEDB wasn’t a viable platform. Most Universe programmers cut their teeth on Pick Basic and Telnet programming and would be instinctively averse to a Microsoft SQL-based API, and for all products there is a satisfied silent majority that doesn’t bother posting about how satisfied they are.
After a few months of getting the basic infrastructure ironed out, I wasn’t part of that satisfied majority. Reading from the database was reasonably fast and stable, but updates were hit-and-miss and the odd way that UniOLEDB handled “multivalues” was a major problem for coding productivity. (In case anyone who isn’t proficient in Universe has read this far, multivalues are a way of dividing database fields into sub-fields, and our database uses them extensively). IBM’s technical support seemed surprised that we were even trying to use .Net with the product. After my first bug report was met with silence for a few weeks, their techie apologized for the delay in responding and explained that he was having trouble finding a copy of Visual Studio.
In exasperation, I posted a question to the U2 User Group site asking if anyone had successfully deployed a VS2005 product using UniOLEDB. The answer, posted on the forum and in sent in private e-mails, was basically “don’t go there”. (And some of the private e-mails were kind enough to offer to rewrite our code using UniObjects.Net, for a nice hourly fee).
But we did go there, partly out of lack of affordable alternatives and partly out of bull-headed determination to leverage at least some of ADO.Net’s incredible productivity advantage over delimited strings. And now we are just about ready to deliver the product. I’ll have more to say about U2 and .Net in future postings, but here’s my top 10 list of suggestions for getting U2 and .Net to rock (well, maybe strum) together:
1. Use UniOLEDB for reading, but UniObjects.Net for writing. This one is a must (unless your users don’t mind corrupting the database now and then). I really, really hated to do it, because I really, really, like ADO.Net’s Dataset model, but I minimized the damage by only involving UniObjects.Net at the very end of the update process. Our code still uses Datasets to keep track of what records were added, updated or deleted, and we still call GetChanges to retrieve the list of updated records, and we still code SQL UPDATE, INSERT, and DELETE statements to handle the database updates. All I did was write some custom code to parse the SQL statements, look up the corresponding field attributes from the Universe dictionary, and handle the insert or update of those attributes (or deletion of records) using UniObjects.Net. If IBM ever provides a stable .Net API for handling updates (and they are currently beta testing a .Net driver for U2), it will be easy to knock UniObjects out of the picture and let the DataSet finish the job.
2. Don’t try to use the UniOLEDB model for multivalued fields — access them as delimited strings. This can be done on the read side by adding an “I-type” dictionary entry that returns these fields as a delimited string. On the update side, the code which does the low-level parsing of the SQL statements (see item #1) has to figure out which fields are multivalues and piece them together as delimited strings. The UniOLEDB model for multivalues is just plain weird — perhaps I’ll explain it in a future post.
3. Ensure that all of your code that accesses Dataset columns can handle nulls gracefully (UniOLEDB actually returns them as System.DBNull). In U2 databases any field can be null: string, integer, date, whatever. (Delimited strings, remember?) Even if nulls aren’t supposed to be there, the database won’t do anything to prevent them so you’ll have to code defensively.
4. Define the data type as specifically as possible in the dictionary entry. We use “S” type dictionary entries, so we’ll put a specifier in attribute 6 such as “CHAR,20″ for a 20-character field or “MTS” for a timestamp. (Actually, it’s almost always a CHAR type — see point #5). Without a typed dictionary entry, UniOLEDB tries to determine on the fly what data type to return when you read a field — if it finds integers in that field, it will return an Int32. If your customer happens to have some very large values in that field, UniOLEDB will return doubles. While it’s possible to wrap your .Net code in a layer that will convert whatever it gets from the database to the format you expect, that flexibility isn’t worth the performance hit.
5. Unless you have really clean databases, define your fields as CHARs, and convert the strings to their desired format in your .Net code. As a database, U2 considers everything a string, so type safety is completely out the window. If you define as field as a timestamp, and it contains something that UniOLEDB doesn’t recognize as a timestamp, it throws an Exception. (If you’re lucky — when faced with runtime problems UniOLEDB will sometimes just return a null without signalling an error).
6. Specify a static culture ID whenever you convert a string read from the U2 database. For example, a user with French regional settings will see an Exception if the application tries to convert “1.23″ using Decimal.Parse(strValue), since French regional settings use a comma as the decimal place. So, instead, use something like Decimal.Parse(strValue, new CultureInfo(“EN-US”)). The same rule applies when creating a string value to be inserted into the database: use decValue.ToString(new CultureInfo(“EN-US”))
7. Use F type U2 dictonary entries, not Q type. Q types don’t fully SQL statements executed through UniOLEDB.
8. Write code to automatically update the uci.config file with an entry for the server and account that the user is logging into. The format of these entries is pretty simple, and it sure beats having the users manually update these files everytime a new server name or account is encountered. And this approach allows you to set connection options on the fly, such as MAXFETCHBUFF. By default, the longest field that UniOLEDB can return is 8K. If any of your fields are longer than that (and they likely will be if you have multivalued fields and you’re following my advice in point #2), set MAXFETCHBUFF accordingly. This results in a buffer of that size being allocated on the server for each user session, which is why you may need to set MAXFETCHBUFF on the fly rather than statically.
9. In very large tables, SELECT using specific keys only. We have a table that at large clients is about 12G in size, and contains over 12 million records. If we try to retrieve a range of records using a SELECT statement like SELECT * WHERE KEY LIKE ABC%, the SELECT statements takes 20 minutes to complete (on our relatively new development server), even if it doesn’t return any records. Don’t even think about trying a statement like SELECT * WHERE NONKEYFIELD = ‘X’. To make this table workable, we instead write code to determine the specific keys that we need to retrieve, then use SQL like SELECT * WHERE KEY IN (‘A’, ‘B’, ‘C’). It can be a pain to develop an algorithm for figuring out what the exact keys are, but the records are then returned instantaneously.
10. Use the VS2005 Query Manager to access your Pick Tables. Universe graybeards know the Pick command line interface like the backs of their hands, so firing up Telnet to look through your data is a hard habit to break. But if UniOLEDB is good enough for your code then it’s good enough for you — c’mon, eat your own dog food! You can add each account of your database as a separate Data Connection in the Server Manager (more on how to do that in a future post), then right click on the Connection and select New Query. (At this point you’ll get the scary “corrupted memory” error that started this column — don’t worry, your memory is fine). Entering SQL commands will give you good practice at interacting with U2 using SQL, and having the results displayed in a grid makes U2 look, like, kind of 1990s.
Yes, after long months of trial and error and a little ingenuity, I’m pleased to really is possible to successfully develop a large application using U2 and .Net. (Successfully deploy? To be determined.) I hope that some of the above tips will help you avoid some of the more common pitfalls. This post barely scratches the surface, though, so stay tuned for lots more on this topic in the future.
