Cheetah spot by spot: ontape to directories

Welcome to the third article about Cheetah new features. This time I'll check ontape's ability to use directories for backup and restore.

First let's see why this was implemented and what do we get from using it.
As most of us know, ontape is still widely used in Informix shops. Although OnBar is an advanced tool that allows integration with enterprise backup and restore systems (storage managers), customers like ontape simplicity and independence.

With ontape you don't need anything else to manage Informix backups, except the devices (disk files or tape drives in pre-cheetah versions). You dont' need more expensive licenses, complex storage systems or any non-informix knowlegde.
But of course you loose some features. ontape works in serial mode, so the performance is not optimal. Nevertheless for small to medium sized systems it is a good (if not optimal) choice.

The limitations or annoyances of ontape usage can be resumed as:

• If we use tape drives we need two drives for logical logs and for normal backups
• If we use tape drives and we have several instances in the same host we may need too many drives or we must carefully schedule the backups (system and logical logs)
• If we use files we have to manage them and assure the rename between different backups
• The use of tape drives implies similar devices on different machines if we need to exchange backup images (you can use remote drives but you'll get into network bottlenecks)
• ontape generaly needs user input. It is possible but complex and error prone, to try to script ontape questions

Well, folks at R&D decided to give us a new functionality that helps solving this problems. It's now possible to make ontape backups (logical logs and other backups) to directories. This backups are unattended, so we don't need complex scripts and this keeps ontape simplicity.

To use this feature we should define LTAPEDEV and TAPEDEV as existing and valid directory paths. We can use the same path to both parameters, and we can use network mounted directories (if we can afford the bandwidth).
Then you can use the normal ontape commands. IDS will generate the filenames using the following structure:


<hostname>_<servernum>_<backup-type>

• HOSTNAME is the server name where the Informix instance is running
• SERVERNUM is the SERVERNUM parameter (unique within each host)
• BACKUP TYPE can be: L0 (level 0), L1 (level 1), L2 (level 2) or Log<##########> (logical log number ##########)

This nomenclature assures that there are no conflits even when you use the same paths for different instances and even for different hosts (network paths).
The system seems to be smart enough to rename old files if a new backup needs to write a specific file and it already exists (from a previous backup command). It will append the original date/time stamp. This will save old backup images, but it will of course consume space.

So, with this feature, we can avoid reserving physical tape drives for our instances, if we have enough filesystem storage. The fact that we can use network mounted directories will also simplify restores on different hosts. This can be useful for HDR/RSS setups and also for using another new ontape feature: continuous logical log restore.
But hopefully, this will be covered in a future article.

Regards.

Cheetah spot by spot: sysdbopen/sysdbclose

Continuing the Cheeta's new features articles, it's time to talk about sysdbopen and sysdbclose.
These are DBA controlled procedures that are run by the users when the try to connect/disconnect to/from a database.

These procedures are regular SPL procedures but with a few particularities:

• Only DBAs can create or drop them
• The owner has more meaning than usual. If you create a procedure called myuser.sysdbopen, only "myuser" will run it when he connects to the database.
  If you create a procedure called public.sysdbopen, then all users that don't have a matching user.sysdbopen procedure, will run public.sysdbopen

So, why is this useful? Well... I can only point out several uses for it... But it will really depend on your environment:

• Can be used to do some kind of logging for connects and disconnects
• Can be used to change the isolation level when changing the application code is not an option
• Can be used to change the LOCK MODE in the same situations as above
• Can be used to stop the creation of new database sessions (inhibit connections during a maintenance period)
• Can be used to restrict certain users from connecting at certain hours, or from certain hosts...
• sysdbclose can be used to gather session statistics (and save them in some history table)

Some of the above are obviously very useful, some are less, and you can certainly find a lot more of useful actions that you can do with it.

This mechanism was made available in XPS (DW Informix engine) a long time ago, but it took really too much time to be ported to IDS. Flexibility is the keyword here. It improves usability and adds functionality.

One final note. There is an environment variable (IFX_NODBPROC) that can be used to stop the server from executing these functions, BUT it can only be used by DBAs. Since a DBA could even drop the function this is a nice feature.

It could be nice to have sysdbopen/sysdbclose procedures associated with database roles. Currently this is not possible, but you can use the generic "public.sysdb*" procedure and put some logic in it so that it behaves differently for different default roles (just an example).

For now, let's just appreciate the fact that this was finally implemented.

To summarize, this is really a simple feature, but one that really brings more flexibility and manageability for the DBAs.

Regards.