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

Serverless JavaScript

We recently joined in an interesting two-hour long conversation about Serverless JavaScript led by Steve Faulkner of Bustle who answered questions on Bustle, the Shep framework, the mindset behind the AWS Lambda infrastructure, and related topics.

The discussion took place on the Sideway conversation-sharing platform on January 6th. Here we present the best takeaways from the session which really should be taken notice of by anyone working on AWS.

Steve Faulkner:
At Bustle we serve over 50 million unique readers per month through a "serverless" architecture based on AWS Lambda and Node.js.  Of course there are still servers but we don't manage them. This shift has allowed us to develop products faster and decreased the cost of our infrastructure. I'll answer any questions about how we made this transition and how it has worked out. I'll also discuss some of the tools and best practises including our open source framework shep

Eran Hammer:
When would you…

Export your Wunderlist tasks with XPath

As brought up in this ProductHunt thread, the news is that Wunderlist is going to be deprecated in favor of the new Microsoft To-Do note taking platform.

This is what Wunderlist support had to say in response to my inquiry on Wunderlist's future:

"Now that the next evolution of Wunderlist is here, in the form of Microsoft To-Do Preview (, Wunderlist will no longer receive any updates or bug fixes and will eventually be retired. It won’t happen in the next few months and we’ll be sure to give our users plenty of notice beforehand. In the meantime, you can continue to use Wunderlist normally. Of course, we’d also love for you to try To-Do and let us know how you like it – and how we can improve it. While Wunderlist will continue to exist alongside To-Do for the time being, support for Wunderlist will eventually be removed. Not to worry, though! We will inform all Wunderlist users prior to shutting down service. You'll have ample opport…

Google's Cloud Spanner To Settle the Relational vs NoSQL Debate?

Cloud Spanner is a new proposition for database as a service that emphatically offers "Relational with NoSQL scaling". Will Google come to dominate yet another market?

Once upon a time there was only one kind of database management system, the RDBMS, "R" for relational. Despite its resilience and trustworthiness, it had its shortcomings; it did not scale well, and the relational model it served proved inadequate in the dawn of the Big Data era for handling massive amounts of schema-less, unstructured data.
For this and a few other reasons, a new breed of DBMS's emerged, one that could handle the avalanche of big data, based on the notion of the key-value pair, and doing so by scaling horizontally. But, in order to become versatile, this new breed of management systems had to forgo the safety of the ACID and the cosiness of SQL, both long term partners of the relational model. full article on i-programmer