Re:SQL batch performance with called programs

Discussion created by ca.portal.admin on Oct 24, 2006
I have a user who has a non-SQL COBOL batch program (program A) that
calls a second COBOL program (program B) which issues SQL. This
combination is performing poorly - long run time, lots of CPU.

When program B's processing is incorporated into program A, thus
removing the call, it performs much better. I suspect the problem is
caused by program B setting up a new SQL session and transaction every
time it is called.

Is there any way to eliminate this additional overhead by getting
program B to reuse a single session/transaction across multiple calls?
I have looked at ""transaction sharing"" and ""global cursors"", but neither
seems to be what we are looking for.

Kay Rozeboom
State of Iowa
Information Technology Enterprise
Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@Iowa.Gov
IDMS Public Discussion Forum


Re: Symbolic arameters in QFILES
"Is it what you need ?

Advantage(TM) CA-OLQ(R)
Online Query for CA-IDMS(R)

Reference Guide

Document Number B01293-1E Parameters

You can produce variations of a report using a single qfile. You define parameters in an Advantage CA-OLQ command into which a user-defined text string can be inserted at execution time.
Access mode: You cannot use parameters in qfiles dictionary when the access switch is set to IDMS.
For example, you can tailor a qfile to produce standard summary reports for each department. The department ID is identified as the parameter. You can change the definition of the parameter to reflect the department you want to report on.
How parameters work: You include a parameter in a qfile where you want to change the value of something. For instance; replace a field name in order to change the field retrieved, or a qfile name in order to change the next qfile invoked.
Keep in mind that these parameters are positional. The order in which you specify them in the qfile is the exact order you must specify them when executing the qfile.
In the following example, the department ID value (6666) is replaced with a parameter in order to change the department being reported on.
Original SELECT:
select * from department, employee, emposition -
where dept-id-0410 = 6666
SELECT with variable parameter:
select * from department, employee, emposition -
where dept-id-0410 = &dept-no
You define the parameter in the first line of the qfile, and if you want to change the default, you specify the value at execution:
&dept-no = 6666
select * from department, employee, emposition -
where dept-id-0410 = &dept-no
If you wanted to override the default, your QFILE command would look like:
qfile emp-report 2364
(Where 2364 is the department number)
Defining parameters: Once you have your parameters in the qfile, you must define them in the beginning of the qfile. You do this on the first line of the qfile. If you don't have any parameters to define, you must leave the first line of the qfile blank. The syntax of the parameter line is:
& parameter-name = parameter-value
The parameter-value you specify acts as the default. You can override the default by specifying a value at execution of the qfile:
QFILE qfile-name parameter-value
Guidelines for defining parameter statements follow:

The parameter statement must be the first statement in the qfile.
The parameters specified in the parameter statement must be in the order in which they appear in the qfile.
If there are no parameters in the qfile, you must leave the first line of the qfile blank.
Multiple parameters in one command: You can use more than one variable parameter in a command statement. For example:
select &emp-field-name &dept-field-name
from employee, department
The parameter statement that would appear as the first line in the qfile would read:
&emp-field-name = EMP-LAST-NAME-0415 -
&dept-field-name = DEPT-ID-0410