GigaMegaBlog

We’ve recently come across a couple of tricky bugs in IBM’s ADO.NET driver for their U2 UniVerse database, showing that there are still a few sharp edges on that shiny new finish.

The first and more serious bug is that the driver always returns numbers with a decimal point as a zero when using French “regional settings” in Windows.

For example, when using these Regional Settings…

Zut alors, c'est un comma!

…the sample .Net application that IBM provides in its QuickStart tutorial displays this:

French toast

We ran into this bug because many of our product’s users have the “French (Canada)” setting on their PCs. I assume that the root cause of the bug is that a comma, rather than a period, is used as the decimal point, as marked in yellow above. IBM support has confirmed that this is a bug are internally testing a fix in the Runtime Client, so the fix will likely be available in Fix Pack 3c or the one after that.

I’m surprised that nobody (including us) ran into this bug earlier, but that’s probably because most developers (including us) have got into the habit of reading data from UniVerse as strings, then converting the data into its proper format in their code. As I’ve mentioned in past articles, UniVerse has a “multivalue” heritage where all data is stored internally as strings. Developers should go against this grain at their own peril.

For example, rather than defining a currency field in the Universe dictionary like this…

COMMISSION
001 A
002 34
005 S
007 MD3
009 R
010 7

… define it as….

COMMISSION.STR
001 A
002 34
005 S
006 CHAR,7
009 R
010 7

… and read the value into a String. Then, you can convert it to a Decimal variable using code like…

Decimal.TryParse(strValue, System.Globalization.NumberStyles.Number,
System.Globalization.CultureInfo.InvariantCulture, out decValue);

The “InvariantCulture” thing is needed because, when defined as a CHAR, UniVerse will return a decimal value using — surprise, surprise — a period as the decimal point.

The second bug that we tripped over was a little more obscure. The bug was found by one of our developers, who was double-checking the configuration of our clients’ databases to make sure that all of our latest patches had been applied. He found that one particular SQL statement used in our application was failing with the following error:

Error  occurred: ERROR [Command_ExecuteDirect]  [IBM  U2][UCINET][UNIVERSE]:UniVerse/SQL: syntax error.  Unexpected verb.  Token was  "LIST". Scanned command was FROM VENDORS SELECT LIST  :  IBM.Data.DB2: -2147467259

This type of error usually indicates a mismatch between our dictionary entries and our application, which was the type of configuration issue that he was checking for. It wasn’t a big surprise, since the problem was found in a database that is only used for in-house training. Surprisingly, though, after reapplying the latest dictionary entries the bug remained.

Like most database systems that I’ve worked with, UniVerse’s SQL parser tends to return some pretty vague error messages. The particular SQL statement that was failing was quite long, containing about 20 columns, none of which were named “LIST”. By trial-and-error, I found that the culprit was a field named DEMO.LIST. The DICT definition of this field was fine – it exactly matched the dictionary entry used in the other databases at this client site, none of which were experiencing this error. While scratching my head over this, our developer told me that he found the same error in the same SELECT statement in a database at a different client. This database was also one used for in-house training.

This made me suspect that the error was caused by bad data. The databases used for in-house training are more likely to contain weird characters like embedded tabs or carriage-returns.

The dictionary definition of DEMO.LIST was a 100-character string:

DEMO.LIST
001 A
002 5
005 S
006 CHAR,100
009 L
010 100

As mentioned earlier, strings are usually a safe choice for data in UniVerse. I’m not aware of any type of data that can cause a SELECT of a a string to fail in UniVerse’s ADO.NET driver, but there’s a first time for everything. However, a bit of trial-and-error testing made it clear that this SELECT statement was failing on the plainest of records: vanilla text that couldn’t possibly cause a problem. It was a real whodunnit.

Back to the not-very-helpful error message, It was complaining about a “token” named LIST. If I simplified the variable name to something like FIELD1, did that affect the error message? Well, yeah: it made the error go away completely. The SELECT statement worked fine if the column name was changed to FIELD1, or DEMO or even DEMOLIST. Weird. We have periods in lots of our column names, and a lot of them are named LIST. DEMO, not so much.

At this point, things finally fell into place. The name that we give our databases that are used for in-house training is DEMO. And, of course, in standard SQL syntax, a period in a column name is used to identify the table, such as “SELECT TABLE1.FIELD1″. Sure enough, I ran some tests in our development database, named DEV, and found that naming a column “DEV.LIST” or “DEV.FIELD” or “DEV.WHATEVER” caused SELECT statements that use that field to fail.

The moral of the story, I suppose, is don’t use periods in your column names. That was IBM support’s suggestion, though I’m hoping that they will fix this bug as well.

I suppose that both of these bugs are indicative of what happens when you drag a 40-something year-old non-relational database kicking and screaming into the .Net world. Oops, better make that “dotNet” to be safe!

This article is the third and final part of a series that covers some lessons learned when trying to plug Visual Studio into our IBM U2 UniVerse database using IBM’s ADO.NET driver.  Part 1 was an overview of how well the ADO.NET driver has worked for us, and the prerequisite software that is required to use it.  Part 2 gave some tips on preparing your UniVerse dictionaries for use with Visual Studio.  In this part of the series, we finally get to fire up Visual Studio and connect it to the database.

One of the great things about the ADO.NET driver, when compared to other UniVerse APIs like UniOLEDB and UniVerse Objects.NET, is that it allows Visual Studio to finally recognize UniVerse as a database.  That means you don’t have to write a bunch of code in order to see the data — you can use use Visual Studio to explore the database and test your SQL statements.  (On the other hand, if you prefer to just write code and avoid all the point-and-click stuff, you can skip ahead to this point in the article.)

The first step is to configure your UniVerse database in Visual Studio’s Server Explorer.  This process is explained well in IBM’s developerWorks article "Access IBM U2 data server from your .NET applications, Part 2".  Since I would strongly advise you to read IBM’s three-part tutorial series anyway, I won’t repeat the information here.

You should now have a new node in the Data Connections section of the Server Explorer, as shown in the screenshot on the right, with a name like "<user-ID>@<account>[UNIVERSE 10.02.0000]".  Expand that node, and you’ll see 4 sections: Tables, Views (which will be forever empty), Procedures and Functions (also empty).  Expand the Tables node and, hopefully, you’ll see a list of the tables in your account.  Expand one of the tables, and you’ll hopefully see the columns in that table.  Right-click on the table and select "Show Data", and you’ll see a grid containing all the rows of the table.  Groovy!  UniVerse and Visual Studio sitting in a tree-ee…

Alas, you might, like I, find that Visual Studio’s love for UniVerse is not so easily requited.  When I expand the Tables node in our development account, I see the warning on the left.  After clicking on the Yes button, I am then left staring at the hourglass for a few minutes before the table list appears.  If I expand one of the tables to view its columns, I once again find myself contemplating the Windows hourglass for a minute or so, even if the table only contains a few columns.  If I want to use Visual Studio’s query builder with my UniVerse database: you guessed it, again with the hourglass.  Trust me, that gets real old real fast!

It would seem that Visual Studio’s Explorers like to, well, explore.  If you have a large UniVerse master dictionary, all that exploring takes some time.  The key to speeding up Visual Studio is to limit how much of your master dictionary it sees.  If you read Chapter 4 of the UniVerse ODBC Guide, you’ll find that happily there is a mechanism for doing just that: create a file named HS_FILE_ACCESS and add an entry in it for each file that you want to use with Visual Studio.  Unhappily, while this limits the number of tables that you’ll see in Visual Studio, it only makes things slightly faster.

I suspect that the solution to this problem is to create a new account whose master dictionary contains only the data files you want Visual Studio to work with.  I’ll be trying this for myself at some point in the future, and I’ll blog about it then. 

Fortunately, even if you find that slow performance makes some of Visual Studio’s point-and-click features unusable, you are still left with enough tools to get the job done. 

Let’s start with some SELECT queries.  Right-click the database connection in the Server Explorer and, instead of New Query, choose New Script.   This opens the Script Builder (shown in the screenshot below), an IBM tool that is basically a stripped-down version of Visual Studio’s Query Builder: no Diagram pane, no Criteria pane, just a box for your SQL and a  grid with your results.  (And a tiny little 1-icon toolbar – cute!).  More typing, but a lot less waiting, since this dialog do a scan of your UniVerse master dictionary.

The SELECT commands that you enter here follow most of the same rules as for a SQL database, but there are a few tricky things:

1) Case matters – if your columns are defined in your DICT in upper-case, you must use upper-case here, something that is sure to trip up any SQL Server or Oracle developer.

2) Columns and tables that contain hyphens must be surrounded with double quotes — the square brackets that you may be used to using with other databases won’t cut it here.  For example,

select USER.ID from "CSUSER-FILE"

is valid, but not

select USER.ID from [CSUSER-FILE]

or

select user.id from "csuser-file"

Oddly, names that contain a period do not have to be quoted – unlike its SQL database brethren, UniVerse is smart enough to figure out when the period delimits the table ID from the column name.

3) A "SELECT *" query will return the key fields along with whatever fields are defined in a special dictionary entry named "@".  For example, if you want SELECT * FROM USERS to return the FIRST-NAME and LAST-NAME fields, you’ll need to edit the dictionary as follows:

ED DICT USERS
Record name = @
2 lines long.

----: 2
0002: 1 2
Bottom at line 2.
----: R FIRST-NAME LAST-NAME
0002: FIRST-NAME LAST-NAME
Bottom at line 2.
----: FI
"@" filed in file "DICT USER".

If you don’t have a record named "@" in your file’s dictionary, then "SELECT *" will return the column named "@ID" if you have one (which you probably do, since the primary key is automatically added to all dictionaries under this name when you first create them).  If you happen to have deleted the "@ID" column, then SELECT * will fail with a "No column rows" error.

What if you have a 200 column table and you want SELECT * to return all 200 columns?  Sorry, but you’ll have to enter all 200 column names in that "@" record.  And if you add, delete or rename a column, you’ll have to remember to edit that "@" record. I feel your pain!  This cumbersome approach dates back to UniVerse’s ODBC driver, and I suppose they can’t change it now without breaking a lot of applications.  You can find the details described in inscrutable IBM-ese in the UniVerse ODBC Guide.

4) Errors encountered when executing your SQL statement are reported in a pretty obscure manner.  You’ll see a message box with the "Error in SQL.  Check the SQL and try again.  For error detail information refer to the IBM Output Message Pane".  It took me awhile to figure out where the "IBM Output Message Pane" was hidden.  It’s in the Output window (Ctrl-W O), but you’ll need to change the "Show output from" combo to "IBM Output Message Pane" as shown below. 


5) If you have subvalued fields, only the first subvalue in the field will appear in the Results grid.  (Subvalues are ASCII 252 delimiters which are used to fit multiple fields into the same column).  This is presumably a side effect of a bug in IBM’s ADO.NET driver which replaces subvalue delimiters with CR/LF.

6) There are a couple of bugs in the ADO.NET driver for which, as far as I know, there is no good workaround.  The first is that the SQL processor can’t handle hard-coded values which contain the "at sign", such as:

SELECT * FROM MY.TABLE WHERE THE.KEY = "DAN@WORK"

Depending on the structure of your data you may be able to get at the data by using a wildcard instead

SELECT * FROM MY.TABLE WHERE THE.KEY LIKE "DAN%WORK"

The other bug is that zero-length numeric fields such as the one in attribute 5 below will result in an “Input string was not in a correct format” error (that funny little accented character is ASCII 253, the multivalue delimiter):

0001: SMITH
0002: 1414 W. 8TH
0003: DENVER
0004: CO
0005: V2001ýýC8181
0006: 200ý3599ý1050

This latter bug was fixed by IBM in the recently released Fix Pack 3, but if you have users on earlier releases there is a workaround you can implement in your code, as explained later in the article.

7) Last, but not least, multivalues.  As described in my previous article there are a few different ways to configure your dictionary to return multivalues, but the method that IBM recommends is to list the multivalued fields in a special "association" dictionary entry that looks like this:

    DETAILS
001 PH ASSOCIATION OF ORDER DETAIL FIELDS
002 ORDER.IDS ORDER.AMOUNTS

The advantage of this approach is that you can return the items within the multivalued field as if they were separate columns, which makes the data a lot easier to work with.  The disadvantage is that you’ll have to puzzle out the syntax for a join with a "virtual table".   If the above field DETAILS was defined in the dictionary of a table named CUSTOMERS, then a SQL statement to return a list of order IDs and amounts would be:

SELECT a.KEY.FULL, ORDER.IDS, ORDER.AMOUNTS FROM CUSTOMERS a JOIN CUSTOMERS_DETAILS b ON a.KEY.FULL = b.KEY.FULL

Since the dictionary field that is automatically added for the primary key, "@ID", can’t be used in SQL statements, you’ll have to add one yourself in order to use it in the JOIN — in the above example I added a field named KEY.FULL to CUSTOMERS.

If by this point you’re fully freaked out by the UniVerse way of implementing SQL, then I’ve got some good news for you: the code itself is pretty much plain vanilla and won’t require much explanation. 

Before you start coding, you’ll need to add a reference to the IBM.Data.DB2 DLL to your project References list, as shown at the right.  You’ll also need to add "IBM.Data.DB2" to your list of namespaces:

using IBM.Data.DB2;

The process of building a connection string is quite similar to other databases like SQL Server.  You’ll need to set the Server, User, Password and Database (which is the UniVerse account name).   In addition, you need to set two less familiar properties — ServerType and Pooling:

private string providerName = "IBM.Data.DB2";
private DbConnectionStringBuilder builder;
private DbConnection conUniVerse;

builder = new DbConnectionStringBuilder();
builder["Server"] = txtServer.Text.Trim();
builder["User ID"] = txtUserID.Text.Trim();
builder["Password"] = txtPassword.Text.Trim();
builder["ServerType"] = "universe";
builder["Pooling"] = "false";
builder["Database"] = txtAccount.Text.Trim();
string strConnectionString = builder.ConnectionString;

DbProviderFactory m_provider = DbProviderFactories.GetFactory(providerName);
conUniVerse= m_provider.CreateConnection();

conUniVerse.ConnectionString = strConnectionString;
conUniVerse.Open();

You’ll then need to create an ADO Data Adapter and feed it the SQL statement.

DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.FillError += new FillErrorEventHandler(adapter_FillError);
adapter.SelectCommand = DB_GetCommand(strSQL);

Lastly, you create an ADO Dataset to hold the results of the query, then bind that Dataset to a grid on your form to display the data:

DataSet dsData = new DataSet();
adapter.Fill(dsData , "MyData");

bindingSource1.DataSource = dsData ; // bindingSource1 is a BindingSource object that I dragged from the Toolbox onto my form
bindingSource1.DataMember = "MyData";
gridData.DataSource = bindingSource1; // gridData is a DataGridView object on my form
conUniVerse.Close();

You may have noticed that I added a FillError event handler to my adapter.  This is a workaround for the zero-length multivalue problem that I mentioned earlier in the article.  In the error handler, you can examine the Exception’s properties and, if it appears to be the zero-length multivalue error, set the e parm’s Continue property to true.

void adapter_FillError(object sender, FillErrorEventArgs e)
{
    if (e.Errors.Message.Contains("Input string was not in a correct format"))
        e.Continue = true;
}

This will result in the dataset being filled, but without the multivalued " virtual record" that contained the zero-length value. If the zero-length value is just bad data then that’s probably what you want — otherwise, you’ll need Fix Pack 3.

Whew!   That might seem like a lot of work to read some data into a grid, but it’s stable, it’s pretty fast, and it’s still a lot less coding than the only other stable and fast UniVerse API out there, UniVerse Objects.  Once you’ve learned the ropes, UniVerse is now a viable database option for .Net development. 

This article is a continuation of my previous post about using IBM U2 UniVerse’s ADO.NET driver with Visual Studio.  Today I’m going to deal with the part of the process that makes .Net programmers cringe but is the key to crafting effective SELECT statements: the UniVerse dictionary.

You probably already have dictionary entries that you (or others) use with UniVerse Basic code, but there are a few cases where you may need to modify them in order to access the fields through ADO.Net.  UniVerse Basic is pretty forgiving about incorrect dictionary entries — as long as the entry tells the code which attribute to look in, UniVerse Basic is happy.  Unfortunately, ADO.Net (and other SQL-oriented APIs) are picky about data types and conversion codes — they frown on UniVerse’s attempt to portray every piece of data as a string.

IBM has provided a utility named HS.SCRUB which analyzes your dictionaries and flags any fields which might cause problems for the database driver.  If you are brave, you can even let HS.SCRUB "autofix" your dictionaries. This utility was actually introduced with the UniVerse ODBC driver, and you’ll find the documentation for it in the UniVerse ODBC Guide and the Using UniOLEDB manual.  (You can download PDF copies of any of the UniVerse manuals from here.) Frankly, the utility found so many problems in our dictionaries, including a lot of false positives, that I ended up ignoring it and building new dictionary entries from scratch.  Your mileage may vary.

However, even after letting HS.SCRUB have its way with your dictionaries, there are some cases that will require special attention:

1) Non-string fields that may be null.

If you don’t specify the data type of a field, the database driver will helpfully try to guess the data type on-the-fly by looking at the data.  HS.SCRUB uses a similar approach to determine the correct data type.  Unfortunately, both of them are tripped up by non-string fields which contain some zero-length values — if you   define these as any data type except strings, the zero-length values will result in "invalid data type" .Net Exceptions at run time.

In cases like this, I’d suggest you force the field to be read as a string, by specifying "CHAR" in the data type attribute of the dictionary entry.  The data type attribute is 6 for A and S-type dictionary entries, and attribute 8 for D- and I-type correlatives.  If you have no idea what that means, then you’d better have a look at Chapter 5 of the UniVerse System Description manual.  (As mentioned earlier, PDF copies of all of the UniVerse manuals can be found here.)

2) Time fields with contain milliseconds. 

In Universe files, time fields are written in a "Julian" format which converts the time to the number of seconds past midnight: for example, 1:30 am is 5400.  Optionally, these time fields can contain the number of milliseconds, which is written as a decimal value: for example, 5400.5 is half a second after 1:30 am.  Unfortunately, the UniVerse ADO.Net driver can’t convert a string with that format into a TimeSpan.  One workaround is to define the field as a string, by specifying "CHAR" as the data type (see point (a), above).  You will then have to write a bit of code to convert the string to a .Net TimeSpan value:

public static TimeSpan TimeSpan_FromUniverseString(string strUniverseTime)
{
    int intUniverseTime = 0;
    // if the string contains milliseconds, discard them
    int intIndex = strUniverseTime.IndexOf('.');
    if (intIndex >= 0)
    {
        strUniverseTime = strUniverseTime.Substring(0, intIndex);
    }

    if (!Int32.TryParse(strUniverseTime, out intUniverseTime))
    {
        // if not a numeric, then return 00:00:00
        return new TimeSpan(0);
    }
    int intDays = intUniverseTime / 86400;
    int intRemainder = intUniverseTime % 86400;
    int intHours = intRemainder / 3600;
    intRemainder = intRemainder % 3600;
    int intMinutes = intRemainder / 60;
    int intSeconds = intRemainder % 60;
    return new TimeSpan(intDays, intHours, intMinutes, intSeconds);
}

However, an easier workaround (which I stumbled across after using the above approach for months) is to take advantage of the "correlative" code. 

This code only exists in A- and S-type dictionary entries, in attribute 8.  Ordinarily, time fields are defined in UniVerse dictionaries by specifying a "conversion code", such as "MTS" in attribute 7.

    ACCESS.TIME
001 A
002 3
005 S
007 MTS
009 R
010 5

This tells ADO.Net (or UniVerse Basic) that the value in this field can be regarded as a time.  However, by specifying the same conversion code in attribute 8, the conversion to a time value is handled internally by the UniVerse database before ADO.Net sees it.

    ACCESS.TIME.SQL
001 A
002 3
005 S
008 MTS
009 R
010 5

Therefore, a value such as "3600.5" will be seen by the ADO.Net driver as 1:30 am, without that nasty millisecond value that it so dislikes.

3) Multivalues

Ah yes, multivalues.  This is a method for storing multiple values into the same field, separated by a special delimiter.  For example, attribute 1 might contain a list of order numbers, and attribute 2 the amount (in cents) of each of order:

001 C2000ýM3000ýS3000
002 600782ý700422ý101456

Multivalued strings are the primary feature that distingishes UniVerse and its brethern databases from all the others, but .Net doesn’t really understand the concept (nor do many .Net programmers, actually).  UniVerse provides some tools which allow you to dress up the multivalued data to resemble columns in a normalized SQL database — it is up to you whether you want to play dress-up, or just process the multivalued fields in their underlying form: delimited strings.

There are 3 different approaches to definining multivalued strings in the dictionary so that they are safe for consumption by .Net:

i) The documented approach is to use a D-type dictionary entry which contains an "M" in attribute 6 and an "association" name in attribute 7.  For example, the definition of the Order ID and Order Amount fields might look like:

    ORDER.IDS
001 D Order IDs
002 1
004 Order IDs
005 10L
006 M
007 DETAILS

    ORDER.AMOUNTS
001 D Order Amounts
002 2
003 MD0,$
004 Order Amount
005 7R
006 M
007 DETAILS

And the dictionary entry which defines the virtual "DETAILS" table would be:

    DETAILS
001 PH ASSOCIATION OF ORDER DETAIL FIELDS
002 ORDER.IDS ORDER.AMOUNTS

If the main table which contained the multivalued fields was named SALES, then there would now be a virtual table named SALES_DETAILS containing three fields: ORDER.IDS, ORDER.AMOUNTS, and a foreign key field that points back to the primary key of the SALES table. 

For details, see chapter 5 of the UniVerse System Description manual, and for a thorough example see the sample code which is included with Part 2 of IBM’s tutorial on the ADO.Net driver.  In the third part of this series, I will show an example of how you would use a JOIN clause in a SELECT statement to return the fields in the virtual table in the same record as the fields in the main table.

The advantage of this approach is that the resulting SQL statements will closely resemble those that would be used for a relational database, and this should make it easier to port your application from UniVerse to a relational database should you wish to.  The downside is that it can become quite cumbersome to code when your records contain multivalued attributes that aren’t related on one another. For example, if a customer record contains a list of order numbers in attribute 1, and a list of customer contacts in attribute 2, you’ll have to specify a different association name for these 2 entries, and retrieve them using either a very complicated 3-way JOIN, or using 2 different SELECT statements. Also, since UniVerse does not offer any constraints or other mechanisms for enforcing the integrity of your data, you are likely to find that bad data fouls up your application, resulting in missing rows or runtime Exceptions.  In our application, we use this approach only for small and simple tables.

ii) You can return the multivalued strings in their "native" form — strings with delimiters separating the values.  You do this by using an I-type dictionary entry that converts the multivalue delimiter (ASCII 253) to whatever character you want.  For example, to return attribute 1 with the multivalued fields delimited by commas:

   FIELD.LIST
001 I
002 CONVERT(@VM,',',@RECORD<1>)
004 FIELD.LIST
005 50L
006 S
008 VARCHAR,32767

You might be wondering why you can’t just return the strings using the original delimiter, ASCII 253.  For reasons known only to IBM, this results in a runtime exception.  Incidentally, there is is a second type of delimiter which is also common in UniVerse, the subvalue delimiter (ASCII 252).  UniVerse subvalues  occur when the parts of a multivalued fields are, in turn, delimited into smaller fields.  For reasons also known only to IBM, these are automatically converted by the ADO.NET driver to a carriage return and line feed (i.e. "\r\n").   IBM support has indicated that this delimiter conversion is actually a bug and will be changed back to ASCII 252 to a future Fix Pack.

Once you have the delimited string, you’ll need to use .Net’s string parsing capabilities to separate it into individual fields and convert the fields to their correct data type.  You’ll find that .Net isn’t ideally suited for this type of string processing.  UniVerse, on the other hand, is very much suited to it, and IBM provides a library named UniVerse Objects.Net that contains a variety of methods for extracting data from delimited strings.  Frustratingly, your ability to use UniVerse Objects to parse your data is hampered by the fact that ADO.NET has forced you to replace all the multivalue and subvalue delimiters with other delimiters that UniVerse Objects doesn’t support.

If you decide to read UniVerse multivalued data in its native form, you might want to consider bypassing ADO.NET altogether and reading the data using UniVerse Objects.  This is what our application does when dealing with tables that contain data that is mostly multivalued.  It is much faster, and will likely require less code.

iii)  While "official" UniVerse multivalued fields are delimited using ASCII 253 and 252, many UniVerse Basic programmers grow so fond of multivalues that they use them all over the place, with whatever delimiter character tickles their fancy.  I’ve seen multivalued strings used as the primary key, and I’ve seen multivalued strings that contain embedded dates and time fields.  While that kind of data structure would make SQL programmer cringe and a SQL database crawl, UniVerse is tuned to handle these strings efficiently and provides functions that you can insert in your dictionary to extract these fields relatively easily.

The trick is to place a correlative in attribute 8.  Appendix C of the UniVerse Basic manual documents these correlatives, but be forewarned: they are cryptic enough to make a Perl programmer weep with frustration.  Here are a couple of examples to get you started.

Say you had a key that was delimited with "@" signs.  Just to make things interesting, say that one of the fields was a UniVerse Julian date, and another was a UniVerse Julian time.  A typical key would be: FIRSTPART@14927@49055.75.

If you wanted to return the 1st part of the key ("FIRSTPART") as a separate field in the SELECT statement, you could define it as follows in the UniVerse dictionary:

    THE.STRING
001 S
002 0
005 S
008 F;0;(G0@1)
009 L
010 20

The correlative in attribute 8 means "take attribute 0 (the key), then split it into sections with ‘@’ as the delimiter and return part 0".  If you want all the gory details of what the "F code" can do, see Appendix C of the UniVerse Basic manual. 

To return the date string as a date, you would use a similar "F code" string in attribute 8, along with a conversion code in attribute 7:

    THE.DATE
001 S
002 0
005 S
007 D2
008 F;0;(G1@1)
009 R
010 9

The time field is trickier – as mentioned above, the ADO.NET driver doesn’t like time fields with milliseconds.  The best way to handle these fields is by putting the conversion code in attribute 8, but that’s where we need to put the correlative, and (as far as I know) you can’t put both a conversion code and a correlative in the same dictionary attribute.  You can, however, combine 2 extract commands in a correlative:

	THE.TIME
001 S
002 0
005 S
007 MTS
008 F;0;(G2@1);(G0.1)
009 R
010 12

This correlative tells the database to extract the time field from the key and then, having done that, extract the part before the decimal point.  The conversion code in attribute 7 then converts that value to a .Net TimeSpan.  (The Universe Basic manual refers to this as "reverse Polish format (Lukasiewicz)" — how geeky is that?)

So, there you have it: an overview of the tricks that I’ve used to whip our UniVerse data into a form suitable for consumption by .Net. 

If you are new to UniVerse, you are likely thinking at this point: "um, so how do I edit a dictionary".  As a crash course on UniVerse for Windows programmers, I would strongly recommend the "Learner Pack" put together by the U2 User Group.  The UniVerse manuals are a well written, exhaustive reference when you are ready to dive deeper, but as a crash couse you can’t do better than the U2UG Learner Pack.

In third and final part of this series, I’ll show you the (relatively simple) coding required to load UniVerse data into a DataGridView using ADO.Net.

It has been a couple of months since we switched database APIs for accessing our IBM U2 UniVerse database from .Net.  We had a lot of reasons to leave UniOLEDB behind, and pretty high expectations from IBM’s new "DB2 Client" ADO.NET driver, but the 3 most important reasons behind the switch were:

1. Stability.  UniOLEDB’s intermittent "memory is corrupt" errors were driving me nuts.  Upgrading the database server from UniVerse 10.1 to 10.2 improved UniOLEDB’s stability significantly, but switching to the ADO.NET driver has made things better still.  Stability is no longer a concern — we’ve had no problems with dropped connections or failed queries.




2. Deployment.  UniOLEDB’s archaic install program was driving our clients’ IT guys nuts.  The fact that UniOLEDB had to be installed by manually on each desktop was a dealbreaker for the larger sites.  The DB2 Client’s installation program has its own quirks, as I wrote about here, but once we figured out the trick our clients have had no problems rolling it out with automated tools like Microsoft Systems Management Server.




3. Productivity.  The ability to have Visual Studio treat UniVerse like a full-fledged database would make it a lot easier to developer new forms.  With UniOLEDB we had to laboriously handcraft the DataAdapters for each table: write the SELECT, INSERT, UPDATE and DELETE statements, set their parameters, use them to construct the DataAdapter, then write the code to populate and manage the DataSet tables.  That was a lot of code, and we have a lot of tables!  Wouldn’t it be nice to replace all of that with a simple drag-and-drop?

Well, 2 out of 3 ain’t bad.

We haven’t quite reached that third goal yet.  We’ve got a relatively messy UniVerse database: our master dictionary and most of our data dictionaries are littered with references to things that don’t exist or aren’t correctly defined.  UniVerse itself ignores these obsolete entries, but it seems that the DB2 Client dutifully tries to make sense of them, each and every time that we connect.

As a result, any attempt to access a table through the Visual Studio IDE, be it from the Server Explorer or the Data Sources window, is painfully slooooooow.   I have an idea for doing an end-run around this problem, and when I have time to pursue it I’ll write a blog post describing what I did and how it turned out.

But, for now, drag and drop isn’t an option.

Our internal developers are still accessing UniVerse data through a labour-intensive code-based approach, without using the Server Explorer or Data Sources IDE at all.  That’s fine for us, since we’ve built a framework to support it, but when I was recently asked to help a client’s .Net developers get to our data, I knew I had to give them something easier.

My boss assured them that once they had the UniVerse 10.2 and its new IBM ADO.NET driver, UniVerse would be as easy to use as SQL Server.  Heck, we’d even create Views for the data they want.

Well, not so fast.  Even with an ADO.NET driver, UniVerse doesn’t support Views. There’s a node called "Views" sitting under the UniVerse data connection in Visual Studio’s Server Explorer, but it will always remain forlornly barren.  There is simply no equivalent entity in the world of UniVerse.

When I asked IBM about this, they recommended using UniVerse subroutines instead.  These are regarded by the ADO.NET driver as stored procedures, meaning that they can be viewed and invoked from the Server Explorer under the Procedures node.   You can use them with Visual Studio’s Data Source Configuration Wizard to create DataSets and DataAdapters without having to write any code.

Pretty nifty, and that’s something you couldn’t do with UniOLEDB.  (Predictably, it would throw up a "memory is corrupted" error if you tried).  But the subroutines are written in dowdy old UniVerse Basic, and our clients’ developers didn’t want to go there.  They opted for SQL.

Running SQL SELECT statements against a UniVerse database is a lot easier with the new ADO.NET driver, but it’s still tricky, with quite a few pitfalls that aren’t mentioned in IBM’s tutorials or (as far as I know) anywhere else.

So, having just played matchmaker between UniVerse and some .Net developers, I thought it might be useful to describe the quickest path to getting your UniVerse data into a .Net grid.

My next post will describe what needs to be done to prepare the UniVerse dictionaries for .Net, and the following post will show how to create UniVerse-friendly SELECT statements and feed the results into a DataGridView.

Having made the leap over to IBM’s new "DB2 Client" ADO.Net driver for the U2 UniVerse database, we recently spent some time figuring out how to get it onto users’ PCs.  This turned out to be harder than I expected, and harder than it should be.

The new Data Client’s installer was actually a big selling point for us.  The previous database driver, UniOLEDB, used a proprietary install program that didn’t support automated, silent installs.  This means that someone from the user’s IT department had to visit each user’s desktop and 1) launch the install program, 2) click Next a bunch of times, 3) phone us and complain about how annoying this is.  Every install program that we’ve created or incorporated into our product for the last 10 years has supported silent installs, except this one.  

I was so giddy at the prospect of moving on to a proper installer that I assured our clients that we would now fully support silent installs without (ahem) actually trying to do one.  My optimism was based on the fact that 1) the new database client uses the same installer as IBM’s flagship database, DB2, 2) this installer was created using one of the industry standard tools, InstallShield, which has supported silent installs for ages, and 3) the DB2 Information Center’s extensive documentation includes a section entitled "Silent Installation".  It sure looks easy when you read about it.

Unfortunately, it doesn’t quite work the way it’s described, and finding the specifics required quite a lot of Googling as well as some trial and error. 

Here’s what doesn’t work:

1) Installing without a response file.  This will launch the installer, but it will quickly abort, and the error message written to the log is enigmatic:

Action ended 20:06:46: DB2SetDB2INSTANCECA.2BC48F01_561E_4906_8321_946A9F5A90AA. Return value 3.
Action ended 20:06:46: INSTALL. Return value 3.
MSI (s) (E4:68) [20:06:46:607]: Product: IBM Data Server Runtime Client – DB2COPY1 — Configuration failed.

Presumably, the response file is mandatory.

2) The standard InstallShield command line switch for generating a response file in the Windows folder (-r).  I never thought that the Windows folder was a great place to put it — apparently IBM agrees.

3) The Response File Generator utility (db2rspgn).  Although this utility does, indeed, generate a response file, we had absolutely no luck when trying to use this response file to do an install.  Googling the problem suggested that many others had the same result, but I still have no idea what the solution is, if any.

4) Manually editing one of the sample response files, as suggested by the Information Center, might have worked if we actually had a sample response file.  The sample response files are apparently located on the "Information Integration Product DVD", but I’ve never seen such a beast.

Here’s what does work:

1) Run the installer normally (with no command line switches), then look in your %TEMP% folder.  Sure, enough, there’s a .rsp file left behind in there. 

2) This response file works just fine with the standard InstallShield parameters for a silent install.  For the current version of the DB2 runtime client, this is :

v9.5fp2_nt32_rtcl_en.exe /v"/qn RSP_FILE_PATH=<path of the .rsp file>.

The contents of the elusive response file are somewhat of a letdown.  Unless you need to install 2 different versions of the database drivers, you’re unlikely to ever want to change a thing.   Should you not have an Information Integration Product DVD at hand, feel free to use our response file as a sample:

PROD=RUNTIME_CLIENT
INSTALL_TYPE=TYPICAL
DB2_COPY_NAME=DB2COPY1
DEFAULT_COPY=YES
DEFAULT_CLIENT_INTERFACE_COPY=YES
DEFAULT_INSTANCE=DB2
INSTANCE=DB2
DB2.NAME=DB2
LIC_AGREEMENT=ACCEPT