Posts Tagged dba

Mr Jan Musil, the man who does the best and the most complex Informix live demos most of us have ever seen, has put together a great document: Informix for Newbies (part one).

Document is in the form of slides, and it covers topics from download and install to simple configuration and instance monitoring. It is intended for people how have the basic Linux skills and understanding of how database (engine) works.

This kind of material is something that Informix community is always short off, so it’s more than welcome. Please feel free to download, use and share it: https://lnkd.in/dY387zC.

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”.