Archive for September, 2011

How to Exclude Some Data From dbexport

If you use the dbexport utility to prepare some test or development instances of a database, you’ve probably come to conclusion that you don’t need the data from all the tables in a database. There are often some tables used for logging, images and such, which must exists in schema but could be empty in a non-production environment.

The idea is that there should be a way to instruct dbexport to omit exporting the data from particular tables. So I was pleased to see the new usage message of this utility:

Usage:
 dbexport  <database> [-X] [-c] [-q] [-d] [-ss [-si]]
  [{ -o  <dir> | -t  <tapedev> -b  <blksz> -s  <tapesz> [-f  <sql-command-file>] }] [-nw]
  [-no-data-tables[=table name{,table name}]]
  [-no-data-tables-accessmethods[=access method name{,access method name}]]

  NOTE: arguments to dbexport are order independent.
        By default, -no-data-tables=tsinstancetable
        By default, -no-data-tables-accessmethods=ts_rtl_vtam,ts_vtam

Note the options -no-data-tables and -no-data-tables-accessmethods. Usage message is the same in actual versions 11.50FC9 and 11.70FC3. I’m not sure in which versions were these two new options introduced, but in time of writing this post, those were not yet mentioned in official Informix documentation for 11.50 nor 11.70. Haven’t tested the -no-data-tables-accessmethods option, but the other one, -no-data-tables, works exactly as expected – provides the whole schema without the data for the specified table(s).

, ,

Leave a comment

Naming Your DBA User

Even if it seems irrelevant how you name your DBA user, the one you would use for creating most of the database objects, actually it could be of importance. There are some scenarios where this could speed up the system beyond expectations. Here’s one.

In a fairly complex implementation of the role-based access control (RBAC) while using multiple views to constrain the data users can work with in several ways, with the users accessing the system using the same application, there is need for excessive use of synonyms. The main idea is that application can execute queries like “select a,b from somename” where somename is the synonym for a view that’s reducing the table data for the logged-in user.

For each user and all the views she is granted, private synonyms are created. All the synonyms related to views that are built upon a same table have the same name, so a frequently used table would, in this manner, produce that many same-name private synonyms as there are users on a system. Also, for administrators’ use, additional public synonym with the same name is created for each table. Public synonyms are also used for accessing the data in many stored procedures that are used by other group of applications. This results in having thousands of same-name private synonyms and one public. Hope you’ve not given up on reading this already, this is where complexity ends.

The problems (aside from obvious administration nightmare) are performance-related. In cases where mainly public synonyms are used, there are many systables reads. In fact, majority of the reads are those on systables table. If you remember, there is a composite index on systables, on tabname and owner columns, but it seems that, in order to determine the public synonym is the one to be used, the system has to go through other synonyms with the same name.

Now for the numbers regarding this story: I got a hint from an Informix developer, to create a user with the name that should order before others in used locale, and use that user to re-create all public synonyms. My current DBA user is named with “h”, so I created one called “aaaaa”, and re-created the public synonyms. This reduced the systables reads to about 40%, and sped up the system more that a double!

So there you have it, changing the name of a user that we created only the public synonyms with reduced the time of queries by about 50-60%. I’m not sure if this kind of algorithm is used elsewhere by Informix in determining actual objects to be used while parsing the queries. Only Informix developers could say, maybe there is still some possible performance gain. But from now on, my DBA users on all systems would be named “aaadba”.

, ,

Leave a comment

We Missed the Big 30

Even though I intend this blog to be mostly technical, there are occasionally some thoughts I’d like to share. Feel free to comment and add your own thoughts.

Couple of months ago I was doing some research on history of Informix so one of the things I found was Wikipedia page on Informix which states that the product was first released in 1981. This intrigued me to look further for some addional info on this first release, but apparently Google doesn’t have any page related to this event in its index. Later, Mr Scott Pickett pointed me to this video, where Mr Sippl says (at 14 min mark) that he sold some copies of Informix to some folks at 1980.

Now, all of this wouldn’t make much of a story if one could google for “30 years” and “informix” and find at least one decent site or event that celebrates this (IMHO) big anniversary for a software product. The mentioned search results mostly in CVs. “30 years of informix” search (with quotes) finds no pages. At the same time, prompted by this years celebration of 10 years of Informix being part of IBM, you could google for (quotes) “10 years of informix” and find that 4 out of first 10 pages relate to this anniversary.

I believe the mere fact that one product has survived this many years could be a potent marketing punch-line – make the same search, just replace database name with the “main competitor” and see for yourself. Not really surprised that IBM hasn’t mentioned 30 years, that’s somehow in sync with their strategy of “not marketing specific products”, but at least it should have been announces somewhere. As a Java developer, I had the luck to attend 2005 JavaOne Conference. It was a great celebration of Javas 10th birthday, and those guys from Sun made each of us 15.000 attendees feel proud to be part of this great technology. They understood the real power of the product comes from the users, developers, solution providers. Unfortunately for them, seems like there were some other things they didn’t quite understood, but nevertheless Java is still marching on.

Though it was IBM that should make the hype of it then, it also seems to me that we, as an Informix community, users, dealers, vendors… didn’t seize this opportunity to emphasize the greatness of the product that mostly gets our bills payed. There wasn’t enough talk about it throughout 2010, not even at the annual IIUG Conference. I’m not deluding myself, this is not in any way comparable to some cool feature like flexible grid etc, but it is something that tells other users and customers there is a tradition and loyalism involved here. For what I’m told and what I have read, Informix survived  some very hard times in good part because of its users, and a big anniversary should be a great time to remember it.

Anyhow, that’s water under the bridge now. We can all set it straight very soon – next year there’ll be the first 0x20 years of Informix (or 040 if you prefer octal numbers)!

, , ,

Leave a comment

Using Groovy in Daily DBA Work

Groovy is a relatively new programming language. I like to think of it as Java on steroids. It is based on Java, so all of Java code is automatically Groovy code, but not vice versa. Groovy is also compiled to Java byte-code, so it can run on every JRE. Better yet, you can use GDK (Groovy Development Kit) which provides many very easy to use APIs – working with files, XML is lot easier than in Java.

Some of its main characteristics are:

  • it is real object oriented language,
  • it is a dynamic language,
  • it can be used for scripting.

This last thing means that you can write Groovy script file and execute it from the command line without the need to compile it first, and that’s what makes it appealing. You get to use real powerful language within simple script. Of course, lots of things you’d write in Groovy could be done using shell scripts and if you’re not a programming soul, you won’t find many usefulness in it. Nevertheless, some of my next posts will include ready-to-use Groovy scripts that you may find useful, so here are some hints on what you need to do in order to run Groovy on your PC or server.

First of all, you’ll need JRE (Java Runtime Environment), which is probably already installed on every desktop PC and most of servers. As you’re probably  going to run scripts from shell or command line, ensure that you have JAVA_HOME environment variable set. It should point to main Java install directory which contains bin, lib and other directories.

After that, you need to download Groovy binaries from Groovy download site. I suggest to download zipped binaries. This file is suitable for all operating systems, as libraries are all jar files, and binaries contain both windows batch files and Unix/Linux shell scripts. All you need to do after that is unzip the file wherever you find suitable.

Final step is adjusting your PATH environment variable. It should contain both Java binaries directory and Groovy binaries directory (e.g. /usr/java/bin and /usr/groovy/bin).

You can check if all is set well by executing the command “groovy” from the command line. It should output standard usage message, something like this:

[~]> groovy
error: neither -e or filename provided
usage: groovy [options] [args]
options:
  -a,--autosplit <splitPattern>    split lines using splitPattern (default '\s')
                                   using implicit 'split' variable
  -c,--encoding <charset>          specify the encoding of the files
  -D,--define <name=value>         define a system property
  -d,--debug                       debug mode will print out full stack traces
  -e <script>                      specify a command line script
  -h,--help                        usage information
  -i <extension>                   modify files in place; create backup if
                                   extension is given (e.g. '.bak')
  -l <port>                        listen on a port and process inbound lines
  -n                               process files line by line using implicit
                                   'line' variable
  -p                               process files line by line and print result
                                   (see also -n)
  -v,--version                     display the Groovy and JVM versions

So now we can start groovying. As a teaser, here’s a readable example: calculate bufwaits ratio based on onstat -p output (version 11.50xC9):

groovy -e "out='onstat -p'.execute().in.text.readLines(); \
pg=out[5].tokenize()[1].toLong(); \
bwr=out[5].tokenize()[6].toLong(); \
bwai=out[17].tokenize()[0].toLong(); \
println '\nBufwaits Ratio: ' + ((pg+bwr)>0 ? (bwai*100)/(pg+bwr) : 0)"

Although Groovy doesn’t require a semicolon at the end of the statement, it has to be used here to delimit the statements because this is actually one-liner. You could make a script out of it, bw_ratio.groovy, with the same contents:

out='onstat -p'.execute().in.text.readLines()
pg=out[5].tokenize()[1].toLong()
bwr=out[5].tokenize()[6].toLong()
bwai=out[17].tokenize()[0].toLong()
println '\nBufwaits Ratio: ' + ((pg+bwr)>0 ? (bwai*100)/(pg+bwr) : 0)

and execute it:

[~/]> groovy bw_ratio.groovy 

Bufwaits Ratio: 0.0602958416

In addition to Groovy home page with loads of examples and information, there are many sites dedicated to this powerful technology, just google for Groovy. There are some cool books about it, but I’d definitely recommend “Groovy in Action” authored by Dierk Konig.

, , ,

1 Comment