Posts Tagged ‘UniVerse’

Update to Installing UniVerse on CentOS post

I’ve been asked a few times on how to get the U2 DBTools products, like XAdmin to connect to their VirtualBox machine. I obviously left something out in the article, so I’ve went back and updated the post for how to configure your firewall. You can follow these same steps for UniData as well.

Just to make sure you don’t miss it, I decided I should also duplicate it here…

Configure firewall

If you want to access UniVerse (say using XAdmin from the U2 DBTools package), you will need to modify your iptables configuration.

First, in my case I have the VirtualBox network adapter set to ‘Bridged’. Now, in a shell window update iptables ‘sudo vi /etc/sysconfig/iptables’

In vi, before any LOG or REJECT lines, add ‘-A INPUT -m state –state NEW -m tcp -p tcp –dport 31438 -j ACCEPT’.

Once that is done, you simple run ‘service iptables restart’ to pick up the changes.

The updated iptables file

The updated iptables file

XAdmin once connected

XAdmin once connected

Installing UniVerse on CentOS 6.2

July 8, 2012 1 comment

Previously we looked at installing UniData on a Linux machine. This time around we are going to install UniVerse on a CentOS 6.2. I’ve chosen CentOS as it is essentially a re-branded (de-branded?) version Red Hat Enterprise Linux. RHEL is officially supported by Rocket Software, making CentOS a great free OS for playing around with U2 databases.

As always, I suggest you do this in a Virtual Machine so that you can create as many dedicated test systems as your heart desires (or storage limits). For this I’ve used Oracle’s Virtual Box which is available for free.


Okay, so to start, let’s make sure we have everything we need to do this:

  1. Suggested: Dual Core CPU or better (particularly if running as a VM)
  2. Suggested: 2GB RAM or better (particularly if running as a VM)
  3. Virtual Box software
  4. Latest CentOS LiveCD/LiveDVD ISO (as of 2012/06/07, version 6.2)
  5. UniVerse Personal Edition for Linux

Preparing the VM

After you have installed Virtual Box and have it running, we will need to create a new image to run CentOS. Doing this is as simple as clicking the ‘New’ button and follow the prompts.

Create a new Virtual Machine

Most questions can be left as is, except for the operating system. For the operating system, set it to ‘Linux’ with version ‘Red Hat’.

My old laptop has 2GB of RAM, so I’m assigning 1GB to this machine image.

Setting the Virtual Machine's  memory

I stick with dynamic allocation of my disks for most testing as it is easier to move the smaller images around. For more serious work, you might be better served creating a fixed disk size as it generally performs better.

Selecting the disk type

The default 8GB disk is just fine. You can always create and add more disks later.

Now that you have your machine image ready, select the image and click on the settings button. In this screen click on the storage option and select the DVD drive from the IDE Controller. On the right-side there is a small CD/DVD image you can click on then select the option that let’s you choose a CD/DVD image. This will let you select the CentOS ISO you downloaded so we can boot from it.

Virtual Machine settings

While in the settings screen, you should also add a shared folder and click on the read-only and auto-mount checkbox options.

Installing CentOS

CentOS-UniVerse [Running]

If you are not installing this as a virtual machine, you can burn the ISO image to CD/DVD and start the machine with the CD/DVD in the drive (or on modern machines, via USB drive). Only do this is you know what you are doing or are intending to have CentOS as the sole operating system. From here on in, I’ll be assuming you are taking the VM route.

Select the VM image and click on the start button.

CentOS should auto-boot from the CD/DVD image. Once it has loaded and is sitting at the desktop, there is an ‘Install to Hard Drive’ option. Click on this and follow the installation instructions CentOS provides you. Generally speaking, the default options are the ones you want.

Early on in the installation CentOS will issue a ‘Storage Device Warning’. This is for your newly created 8GB disk. In this case you can select ‘Yes, discard any data’. Warning: If you are not doing this in a VM, you must know what you are doing or you risk losing data.

Storage Device Warning

Where it asks you for hostname, you can leave it as the default. I’ve taken to naming them {OS}.{DB} in lowercase; so in this case I’m naming it ‘centos.universe’.

Once the installation is finished, you can restart the VM image. Be sure to remove the CD/DVD image so that it boots from the hard drive. It will ask you a few final questions once it restarts (such as entering a non-root user) before it takes you to the login prompt.

To make our life easier, once we have logged in, we will add ourselves to the list of allowed sudoers. To do this, open a terminal window by selecting Applications -> System Tools -> Terminal. I also added this shortcut to the desktop since I use it so much.

In the terminal, switch to the root user by running ‘su -‘. We can now edit the list of sudoers using visudo. At the end of the file, add ‘{user} ALL=(ALL) ALL’ where {user} is the username you created for yourself earlier.

Now is a good time to shut down and take a back-up of the image so you can clone as many freshly minted VM’s as you want. I also try to do some common tasks such as installing/updating gcc (terminal: ‘sudo yum install gcc’), installing Google Chrome ( and ant (terminal: ‘sudo yum install ant’) first.

Installing UniVerse

Download UniVerse Personal Edition inside your VM image and place it into a temporary directory.

While you are waiting for it to download, you can create the ‘uvsql’ user we will require later. From the ‘System’ menu, select ‘Administration’ -> ‘Users and Groups’. Once you have the program up, click on the ‘Add User’ button, then fill in the username and password fields. Click ok and exit out of the user manager.

Create uvsql user

Open up a terminal window then change to your temporary directory where the UniVerse download is located. The first step will be to extract everything from the compressed file; to do this you can type in ‘unzip’. Replace ‘’ with whatever the downloaded filename is in your case.

The next step will be to extract the uv.load script to install UniVerse. To complete this step, run this command to extract it from the STARTUP archive: ‘cpio -ivcBdum uv.load < ./STARTUP'

You can now run uv.load as root with the following command: 'sudo ./uv.load'. Select 1 on the first prompt to install UniVerse with 'root' as the default owner. This is okay as we are just building a dev system.

On the next screen select option 4 to change the 'Install Media Path' to whatever the path of the temporary location you extracted UniVerse into. In my case it was '/home/itcmcgrath/temp'. The rest of the options are okay being left set to the defaults. Press [Enter] to continue with the installation process.

UniVerse will now be installed then put you into an administrative program. [Esc] out of this to drop to a UniVerse prompt then type in 'QUIT' to drop back to the command line.

There you have it, a working UniVerse server running in a Virtual Machine. Shutdown your VM and take a copy of the machine image so you have a fresh copy of UniVerse in easy reach.

Installation completed

Update: Configure firewall

If you want to access UniVerse (say using XAdmin from the U2 DBTools package), you will need to modify your iptables configuration.

First, in my case I have the VirtualBox network adapter set to ‘Bridged’. Now, in a shell windows update iptables ‘sudo vi /etc/sysconfig/iptables’

In vi, both any LOG or REJECT lines, add ‘-A INPUT -m state –state NEW -m tcp -p tcp –dport 31438 -j ACCEPT’.

Once that is done, you simple run ‘service iptables restart’ to pick up the changes.

The updated iptables file

The updated iptables file

XAdmin once connected

XAdmin once connected

Disclaimer: This does not create a UniVerse server that will be appropriate to run as a production server.

Statement Code Coverage Testing – Part 2

November 26, 2011 1 comment

Back in November 2009 I posted the “UniBasic Code Coverage” project as an open-source project. Back then it was stripped version based on one I set up for my then employer. The version for my employer used an in-house pre-processor that greatly simplified the work I needed to do for it work with our source files.

I have now released the v0.2 (update: v0.8) development version which has fixed several bugs, added the ability to specific a customer pre-process for those don’t use string UniBasic and provided improved the documentation on installing, using and contributing.

As you will already be aware, the source code for this is hosting on the UniBasic Code Coverage Project at SourceForge in a Subversion repository. If you have Subversion installed, you can checkout the code with the following command:

svn co ucov

If you are running UniData or UniVerse on Windows, I highly recommend you install Tortoise SVN as it greatly simplifies working with Subversion.

On the SourceForge site you will not only find the Subversion repository for all the code, but also ‘Tracker’ which will allow you to submit Feature and Bug tickets. If you need help with anything, you can submit a Support Request as well.

If you wish to contribute to the code or documentation, you can introduce yourself on the Developer Forum. The best way to submit code or doc is by generating a Diff of the changes, as well as what the behaviour was before the change and what it was after the change.

When you have used UBC, be sure to fill out a Review. All constructive input is welcome and appreciated!

Replacing Legacy Reporting with U2 DataVu

November 5, 2011 2 comments

International Spectrum has published the first article I have ever written for a magazine.

The title of the article is “Replacing Legacy Reporting with U2 DataVu” and you can find it here on page 12.

Here is a quick tease:

We all know what they look like: hard-to-read reports with mono-spaced fonts and — aside from the columns of text and the occasional company header — completely barren. More often than not, customers must log into a terminal session in order to generate, print, or view the reports. These reports are almost never available in easily consumable or distributable formats such as PDF.

Let me know what you think!

Application Level Caching

August 26, 2011 Leave a comment

Everyone here probably knows the various levels of caching that exist on a modern computer: From multiple CPU caches through to disk cache and even caching in the database engine itself. If you want to quickly touch up on some caching concepts/terminology, check out this short slide deck from Serhiy Oplakanets on Caching Basics

What I’m going to do shortly is outline some other methods of gaining significant performance improvements on your UniData and UniVerse systems.

There really isn’t anything special outside of U2 that you will need to do to get benefits from this, although a few extra tricks that do require either additional hardware or OS work can give quite a boost

First, just to make sure everyone is on the same page: Since UniData and UniVerse support hash-tables as their file (table) structure, you can simply use a file as a gloried key-value store. Key-value stores are ideal for caching.

I’ve dividing this post into 4 sections:

  1. Session Level Caching
  2. Account Level Caching
  3. Improving the above (SSD and RAM Disk)
  4. In Summary

Let me know what you think.


Session Level Caching


COMMON provides a method of keeping a small in-memory cache for the entire duration of a session. Simply declare an array in a named common block and away you go.

A real world example, I’ve seen this used for when a dictionary item made a SUBR call to a subroutine that in turn would read a multitude of control items to process the original record. This dictionary item was called nightly by an external reporting tool on a large number of records.

The original solution had an unacceptable run-time and after some profiling, it was determined that the READs of the control items were the main culprit. Since it was known that the control items would not change (and should not) during the processing, it was determined that caching the control items in-memory after they were read would reduce the run-time.

The solution involved: An array of ‘x’ elements. When a control item needed to be read in, it checked this array via a simple look-up and if it existed, it used it. If not, it would read it from disk and store it in the array.

The result: 10+ hour run-time was now less than 1 hour.


Account Caching


Alright, so you have a system that needs to handle some messages (perhaps via some form of SOAP/REST web service) The majority are read requests with a few write requests for good measure.

One of these messages is to ‘Get Products’. This message returns a list of products (ID, name and latest available version) that a customer currently has.

In your system, there are 2 files used by this request. ‘CUSTOMERS’ and ‘PRODUCTS’. CUSTOMERS<30> is a multivalued list of record ids for ‘PRODUCTS’. PRODUCTS<1> is the name of the product and PRODUCTS<11> is the latest available version.

Traditionally for each ‘Get Products’ request your system would read in the appropriate record then read in all the linked records from PRODUCTS to compile the response to the query. Assuming an average customer has 10 products, the average disk reads for this query is 11

Now this query is being called a lot, all these extra disk reads and processing are beginning to cause performance impacts. Thankfully, because your database supports key-value storage, you can quickly implement a cache to sit in between the receipt of the message and the processing.

All that is needed is a new file called ‘CACHE.GETPRODUCTS’. @ID is the CUSTOMERS id requested in the query, <1> is the date requested, <2> is the time requested and <3> is the response generated

Now, when ‘Get Products’ query is received, it will first do a read of the cache file and if it exists, simply return <3>. If the entry doesn’t exist, it will hand the request/response off to the usual processing routine. The subsequent request will then be stored in the cache before being returned.

Assuming the average declared above, a cache hit will result in 1 disk read and a cache miss will result in 12 disk reads and 1 write. If – for ease of math – we treat a write equal to a read, you only need a 16.7% Cache hit rate for it to perform better. That isn’t even taking in to considering CPU usage reduction, better disk cache performance, etc.

How you handle cache invalidation is dependent on your situation. It could be as simple as clearing it every ‘x’ period, as straight forward ignoring the cache record if it is older than ‘y’ time or as complex as individually invalidating records based on when the appropriate records in CUSTOMERS or PRODUCTS change.

What has been implemented here is a cache that is available not only in the current session, but to any program running or that will be run in the account(s) that have access to this cache file.


Improving the above


Okay, so you have a more intensive system than the above and you have determined caching can help you out. The problem is, even with the caching it still doesn’t meet your requirements and disk has been determined to be the biggest bottleneck.

You have 2 next steps that can be implemented easily.

The Disk Approach

Simple drop in a shiny new SSD drive or a WD Raptor and move the cache files over there. No need to back them up, mirror them or anything else as caching files are temporary data. As long as your system is setup to recreate them if missing on start-up and treat it as a cache miss if unavailable during operation, you are all set.

The benefit here is faster disk access as well as moving the activity off on to another device/bus.

The RAM approach

Instead of adding new hardware, perhaps you’d prefer to spare 64MB of RAM to the cause. In this case, you would simply create a RAM Drive and move the cache files there. You have now essentially created a RAM based key-value store to use as your heart desires.

For an example of what type of improvements this can have, I took the DOSAC test I previously created and ran it twice. Once with the file on traditional disk and once with the file on RAM Disk. The system stats are identical to last time I ran the test, except it was on Fedora (it comes with multiple 16MB RAM disks pre-configured).



That’s right: Massive improvements, as expected (excuse the display text bug).


In Summary


So, keep this in mind. U2 Databases give you some great flexibility in how you implement your software. Knowing the options available is crucial to being able to get the best results.

As the saying goes, measure twice, cut once. Work out what your performance bottlenecks are then determine the best solution. Some times it is better hardware, sometimes it is code clean up. Sometimes… it might just call for caching.

New Developer Zone – U2 PHP PDO Driver

March 2, 2011 1 comment

Rocket U2 Developer Zone


I spent last week at the U2 University in Sydney and had a great time. During the opening keynote speech, Rocket announced the new U2 Developer Zone.

Great news! Finally a public site for developers that links all the resources you would expect. White papers, podcasts, demos, links to manuals, personal editions of the database servers. Not just a public site, but a public site for developers from Rocket itself. That’s what we needed, strong, visible vendor support of the development community.

It is still a bit rough with a fair amount of content missing, but it has enough in there to make it worth signing up (free) to check it out.

It breaks the site down into 4 key areas.

  • Ignite
  • Launch
  • Accelerate
  • Dock

Ignite is aimed at new players and features explanations of what Multi-Value Databases are, some information about U2 as well as summaries of the Developer & Admin tools available for download.

Launch works on getting a developer up and running as quickly as possible with instructions and links for downloading and installing both the UniVerse/UniData servers, as well as their 4GL tool – SB/XA. A bonus is some professional looking video tutorials for getting them up and running.

Accelerate is focused more on in-depth content of the system with various articles and tutorials that have been produced by Rocket as well as some community figures as well.

Dock appears to be aimed at the forming a community/developer collaboration. It has links to U2UG as well as Rocket U2 on Facebook and Twitter (even though the twitter link is missing on the site at the moment). It also has a message board, but this appears to be one of those unfinished features for the time being.

One point of disappointment at the moment is ‘The Wall’ it throws up to get any content. It requires you to sign-up and log in before you can actually access the content. While I can appreciate their probable reasonings for this and appreciate it is still free, I believe this is one of those things that will prevent those who stop by from search results/ideal curiosity from actually getting involved.

By throwing up a wall, instead of openly allowing read-only access, it has a 2 fold effect. First, google (and other search engines) will not be able to correctly index the content. In an age where > 90% of website traffic generally comes from search engines, this is definitely not ideal. The other negative effect is that the bounce rate of people not currently involved will surely be higher.

Hopefully they will review this decision and decided upon a more open and effective path.


U2 PHP PDO Driver


So, my title indicated something about a U2 PHP PDO Driver and you were not mislead. While at the U2U Conference I had the pleasure of, among others, speaking with Jackie from Rocket Software. At one point the conversation turned towards dynamic languages and in particular, PHP. I was told that some tutorials had actually been written on getting PHP to natively connect to U2 and should be able to be found on the new developer site. Bingo!

After some quick searching on the site, I present you 2 links so you can build your own native connector between PHP and U2:

Hopefully you find this useful!

Data Integrity

February 27, 2011 Leave a comment

One of the features not present in UniData that you many have become used to in the world of SQL is referential integrity.

Data is one of the most valuable assets of a company. If only for this reason alone, it should be treated with the utmost respect and professional care. Everybody knows that backing up data is essential, but what data are you backing up?

If the data is already corrupt you’re in a whole world of hurt. How long has it been it corrupt? Has it corrupted other data? Has it already impacted the business and to what extent? You can’t just restore from several months ago. You have to spend the time manually working out what went wrong, how to fix and potentially trawling through backups to find data to reinstate.

Here I should clarify exactly what I’m referring to by ‘corrupt data’. I’m not talking about OS-level corruption; from here on I will be talking about 2 types of logical corruption:

Unlike the major databases (such as MSSQL, Oracle and MySQL) UniData and UniVerse do not have logical data integrity constraints supported in the database layer. This leaves it up to each individual application to ensure data integrity.

Anyone working with databases knows that bugs (both current and of old) can result in logical inconsistencies creeping into your data. The more denormalised your data, the higher the chance for this corruption.

Some of this corruption will become apparent immediately because a process will fail and require you to locate and fix both the cause of the corruption as well as the corruption itself. Surprisingly, these are not the ones you should be most worried about. The worst are the ones you don’t notice, because they don’t cause the system to visibly malfunction. These are the worst because they can fester in your system for years, silently corrupting data that is derived from it and potentially impacting business decisions. Soon the data itself will become much harder to repair since needed information may no longer be readily at hand. If/when these eventually cause a problem, it will be much harder and time-consuming to address, if even possible.

Since we have to handle logical data integrity entirely in the application layer, U2 databases are somewhat more susceptible to these issues from code bugs. To combat this, there are 2 methods I propose you adopt.

The first is a Data Integrity Audit (DIA) you can schedule regularly in production. This validates your data and reports on any inconsistencies it encounters. This helps you identify issues earlier and potentially help track down the programs/conditions that are causing the corruption. We have already implemented this system for ourselves and I’ll explain how we did it below.

The second method is based on the above DIA. Modifying it to run from file triggers, you can implemented a system to use while testing (Unit, System and at User Acceptance Testing) that can report exactly what program/line is writing the corrupted record as it happens. Catch it BEFORE it reaches production! However, I don’t recommend actually implementing this into production (at least, without great care/load testing) since it will have performance implications that may be unacceptable.

Implementing a solution

Alright, enough of the prelude. Lets talk about implementing a DIA program in to your system. It isn’t as hard as you might think and it can be set up incrementally so you can cover your most important data first.

The system has 4 parts to set up:

  1. Defining the Rules
  2. Storing the Rules
  3. Checking the Data
  4. Reporting on Violations

Defining the Rules

The first step is the logical rules that should be constraining your data. The rules will fall into 2 categories:

  • Referential integrity: Identify any attributes that are foreign keys (or lists of foreign keys)
  • Domain integrity: Specify the ‘domain’ of the field. This includes type (alpha, numeric, etc), enumerations, length, and if NULL is allowable.

Looking at a few of your key tables, you should be able to quickly identify some basic rules your data naturally should abide by. Write these down as these will be some easy rules to start testing.

Storing the Rules

The second step is determining how to store the rules. Although you can do this however you want, there are several reasons that make using the dictionary file ideal:

  • Placing the constraints in with the schema (both are structural metadata). Collocation is a good thing.
  • Attribute 1 can store anything after the type; it allows you to store the constraint directly with the section of the schema you are constraining!
  • X-Type Attributes allow you to use enumerations (part of domain integrity) while still keeping them defined in the schema, instead of elsewhere.
  • It allows you to easily test and/or enforce the constraints with the ‘CALCULATE’ function (more on this later)

So, how exactly do you store the constraints in with the dictionary records? Here is the following scheme we use:

TYPE [FKEY filename [PING f,v,s]] [MAND] [ENUM enum_item]

  • FKEY: Foreign key to ‘filename
  • PING: Checks for @ID in the foreign record location <f,v,s>
  • MAND: Value cannot be NULL
  • ENUM: Value must be an enumeration in the dictionary X-type record ‘enum_item

When attribute 6 of the dictionary item indicates that the data is a multivalued list, FKEY, MAND, ENUM and DATATYPE should adhere to it and treat the each item in the list separately. The only special case is MAND, which only causes a violation when a multivalue in the list is empty. That means it does not cause a violation when there is no list at all. If you want to cover this you can create another non multivalued dictionary item as well and apply the MAND rule to it.

Checking the Data

The third part is how you will test/enforce these constraints:

  • Production: A program, that given a filename, reads in the dictionary items and associated constraints. It can then test each record and report any violations. This would typically be run as part of a nightly job, and/or if you are set up for it, on a backup/restore of production onto a development machine.
  • Development: An update trigger subroutine that is only implemented on development. This also allows you to transparently test if new or modified code is corrupting your data before it even makes it into production. Although this would typically not be implemented into your actual production system due to performance impacts, there is no technical reason that it cannot be done if so desired (even just for selected files)

These methods are not mutually exclusive and are designed to cover different situations. The first is a post corruption check that allows you to identify issues faster than you normally would. The second allows you to provide better test coverage and reduce the risk of introducing faulty code into your production system.

Reporting the Violations

The fourth and final part of the system is how you report it.

There are many options you many want to consider depending on your needs and which of the 2 options above you are considering it for.

We decided upon a non-obtrusive option that allowed us to build either reports or select lists from the results. This method requires you to create a new file to store the results. For the sake of this article, let us call it DIA_RESULTS. You can clear this file just before running the DIA program, or performing tests if you are using the trigger method.

In DIA_RESULTS, each record should contain the following information:

  • Date failed
  • Time failed
  • Filename the violation was on
  • Key the violation was on
  • Dictionary item used when the violation occurred
  • Rule name the violation occurred on
  • The value that caused the violation (just in case it changes before you get to it)
  • If from a trigger, the current call stack

Using this information it is easy to print off reports, create select lists to get to the records and to determine exactly what was wrong in the data.

%d bloggers like this: