Re: Web Processing jobs have large (> 300) Open Data Paths

Thanks Alan.
I will check with them on this.

I had a feeling that this might be it.
Most of the time, since the jobs are not doing anything, there is no
performance issue, but sometimes, with 4 - 5 jobs using over 20% of the
cpu, the system grinds to a halt.


Jeff Young
Sr. Programmer Analyst

On Mon, Dec 19, 2016 at 1:16 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:

Is it possible that they are either not using Prepare at all or not using
it correctly?

What they should be doing is

SqlStmt = 'Select CUSTOMER_NUMBER, CUSTOMER_BALANCE +
From CUSTOMER_RECORDS +
Where CUSTOMER_NUMBER = Cast(> as VarChar(20) And +
TRANSACTION_DATE Between Cast(? As Numeric(8,0)) And
Cast(? As Numeric(8,0))';
Exec Sql Prepare P1 From :SqlStmt;
// Error Check

Exec Sql Declare C1 Cursor For P1;

Exec Sql Open C1 Using :CustomerNumber, :FromDate, :ToDate;

Then use the prepaare statement again and again.

What they are probably doing instead is

SqlStmt = 'Select CUSTOMER_NUMBER, CUSTOMER_BALANCE +
From CUSTOMER_RECORDS +
Where CUSTOMER_NUMBER = ' + CustomerNumber + ' And +
TRANSACTION_DATE Between ' + %Char(FromDate) + '
%Char(ToDate) + ';

Exec Sql Execute Immediate;

The net result would be a different SQL statement every time and a new file
open.

The reason I mention this is that if you go into DB Monitor and look at the
SQL statements they are executing you can see if they are formatting each
statement or whether they are using prepare properly.

The bottom line is if they are doing what I am saying they are doing that
is murder on the system. A new open every time, a new access plan every
time.

I ran into this at another company I was at. It was from VB but they
couldn't understand why they were getting so horrible performance. I
explained why but they won't listen to me and kept right on doing it.

On Mon, Dec 19, 2016 at 9:22 AM, Jeff Young <jyoung0950@xxxxxxxxx> wrote:

I recall from a long time ago that it was a bad thing to have a large
number of open data paths in a job as it could cause paging in the PAG
which would result in severe performance issues.
At this time, that has not been reported, but is it still a concern?
Most of the files in the Open Files section of the Display Files section

in

DSPJOB are repeated over and over again.
I spoke with one of the web developers and was informed that they use

JAVA

programs with SQL to get and update data and have no control over the

Open

and Close functions.



Jeff Young
Sr. Programmer Analyst
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD