Skip to main content

Ingres resource limiting : Part4 (Final Thoughts)

ABF is not used widely today as it is considered legacy, but with the advent of OpenRoad these techniques can be applied to modern day applications, as only minor modifications of the ABF/4GL code are required for transiting to the OpenRoad environment.

The rationale behind this project was to use the QEP or the ingres resource limiter as means of making decisions on the fly; whether or not to execute a query based on the results fed back by the optimizer.
Usually a QEP is used for identifying problematic queries using procedures as described in the very good article
A Procedure to Identify and Fix Long running Queries
which requires monitoring the system and analyzing QEP's offline but here I am trying a proactive approach.
This could be useful for identifying and not allowing heavy querys to run during peak hours on an already loaded server.The platform used for both testing and production was Ingres II 2.6 on SCO Unix

The error handling procedures as far 4GL/ABF goes :
The ESQL error handler called by iiseterr() is limited to suppressing the default error messages appearing on the screen of the user which can be replaced by custom made ones.
Using sql or session directives or switching to another session from within the error handler (well switching to a new one is permitted but switching back to the previous one is not, as it gives runtime errors) is not permitted.

The indicative way of capturing sql errors is by issuing inquire_ingres after each sql statement or embedded Select loop and acting upon the errors.

Using the ' set maxio' and acting on the resource limiting error was not just a simple case of catching the error. It required user interaction, limiting the user's action to a narrow field of actions by using loops,suppressing the default error message,and other programming procedures for controlling the session.

It worked fine in the end for the ABF code, but as far as the report generation goes this whole procedure is not applicable.
Reports are called by using 'call system' which invokes a shell and creates a new session.
Alternatively, the 'call report' subsystem can be used which does not start a new session but it is limited to the length of the parameter string passed to the report to 255 chars.In this case 255 is too small, so it breaks.
Also the report subsystem does not return a value on failure or success so one cannot know if the report generation succeeded or not as to programmatically control the situation.What it does return is an runtime error in pop up message that the report subsystem failed.
Furthermore you cannot have an inquire_ingres inside the report or interrogate the sql error value when the call report subsystem returns to the current session.
So in both cases (call system,call report) of report generation the only way that this approach will work is by using the custom made 3GL/C procedure which scans the QEP plan.The approach of capturing the error works only for4GL code.

One final note : you MUST pass the directives 'set qep;set optimizeonly;' as parameters inside the report because if you pass them through a sub shell as environmental variables (as to invoke the report with those in action),thus avoiding passing them as parameters inside the report making life a bit easier,ingres throws the whole procedure off since it produces QEP's for its own functionality (does selects such as 'select from iidbcapabilities') before running the report's QEP hence the C procedure reads a false value.

There is a drawback though.The report must not contain any DML statements such as 'create table' with 'nooptimizeonly' on because the QEP generation will stop after the first DML statement and the C procedure will read a false value.In this case the DML statements should be shifted inside the ABF code.


Popular posts from this blog

Book Review : How To Create Pragmatic, Lightweight Languages

At last, a guide that makes creating a language with its associated baggage of lexers, parsers and compilers, accessible to mere mortals, rather to a group of a few hardcore eclectics as it stood until now.

The first thing that catches the eye, is the subtitle:

The unix philosophy applied to language design, for GPLs and DSLs"
What is meant by "unix philosophy" ?. It's taking simple, high quality components and combining them together in smart ways to obtain a complex result; the exact approach the book adopts.
I'm getting ahead here, but a first sample of this philosophy becomes apparent at the beginnings of Chapter 5 where the Parser treats and calls the Lexer like  unix's pipes as in lexer|parser. Until the end of the book, this pipeline is going to become larger, like a chain, due to the amount of components that end up interacting together.

The book opens by putting things into perspective in Chapter 1: Motivation: why do you want to build lan…

Deep Angel-The AI of Future Media Manipulation

Undeniably, we live in the era of media manipulation. Such powerful and accessible tools exist today that nearly everyone can do it. Now add to this collection Deep Angel, an artificial intelligence that can erase objects from photographs and videos.

I was notified of Deep Angel around the time I was watching Kill Switch, a futuristic and dystopic movie about our Earth getting cloned in order to suck the resources of the cloned planet, something that would sustain our world's energy needs for at least another millennium. To cut a long story short... full article on

SAP's Creating Trustworthy and Ethical Artificial Intelligence

With the ink hardly dry on the pages of the EU Ethical AI Guidelines manifest, a free online course exploring the issues they raise is already in prospect on the openSAP platform. Run by members of the very same group, the European Union’s High-Level Expert Group on Artificial Intelligence, who wrote the guidelines and in cooperation with SAP's online education platform, a course with the titleCreating Trustworthy and Ethical Artificial Intelligence has been made accessible to anyone with an interest on AI or ML:
full article on