Posts
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.
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.
Comments