Thanks to Dejan Lekic (Deyan) on #ingres for inspiring me to write this short “how to” on building the community edition of Ingres 9.2 on 64-bit openSUSE 11.1. Currently (2009-07-24) there is no 64-bit Linux binaries of the community edition of Ingres 9.2 available at the Ingres ESD site. Because of this, the only way to run the community edition of 64-bit Ingres version 9.2 on Linux is to build it by yourself. In this posting I shortly describe the steps to get it built on 64-bit openSUSE 11.1.
I like to keep Ingres installations up to date by upgrading them occasionally by using the latest patches from ESD. Since I am a responsible DBA, I always read through the list of fixed bugs (“Bugs addressed with this patch” section in the readme file). Some bug fixes even need special attention (marked with “ATTENTION”), so it would be careless not to read the readme file, which is included with the patch.
Ingres patches are cumulative, meaning that each patch also contain the fixes included in the previous patches. Since the list of fixed bugs is ordered by bug numbers and not chronologically, it’s pretty hard to figure what have been fixed since previous installed patch. For example there’s 145 new bug fixes between patches 13322 and 13435.
Here’s a simple tip, how to compare two readme files on Linux using command line tools. First, save the readme files on disk with names p13322.html and p13435.html. Then run
grep "Bug " p13322.html | perl -p -e 's/<.*?>//g' > p13322.txt
grep "Bug " p13435.html | perl -p -e 's/<.*?>//g' > p13435.txt
Now you have a simple listing of bugs fixed in the patches. You can then use diff to compare, what have been fixed between 13322 and 13435:
diff -u p13322.txt p13435.txt | grep "^+" | less
The best thing would be, if Ingres Co. provided their customers a simple web application, which could be used to check these. Until that… use this tip or propose a better one!
I wrote a pretty simple Perl script which outputs a list of open user sessions in an Ingres installation (remote or local). This information can also be retrieved using Ingres utilities iinamu and iimonitor, but not in a very straigth-forward fashion. The script is also a decent example how to access an Ingres database using Perl scripting language and Database Interface (DBI).
The script uses DBD-Ingres driver to access the imadb database, which is part of the Ingres Management Architecture (IMA). The script retrieves the necessary data from the imadb and then prints it. A short, concise list of open sessions is printed first with a more verbose listing (“iimonitor show user sessions formatted” alike) following.
The version 1.0 of the script is available at http://pauli.borodulin.fi/misc/ingres/ima_sessions-v1.0.pl. The requirements for running the script are listed in the beginning of the script.
I recently needed to know how much disk space each table in a Ingres 2006 R2 database took. Any Ingres DBA knows that by default Ingres stores the data under location II_DATABASE, but the filenames used for the data files are not too informative. Fortunately it is possible to check the filename of a table using the following query in the corresponding database:
SELECT file_name, file_ext FROM iifile_info WHERE table_name = 'mytable';
The file_name column contains the name of the file for the table without extension and the column file_ext contains the extension. If the database uses multiple locations, it is also necessary to check the value of the column ‘location’, which will tell you the location of the file.
But this is not all. Ingres also stores all secondary indexes in separate files. It is also possible to see, using the table iifile_info, which index is stored in which file. This way it is even possible to hunt down indices that require more disk space than you expected.