× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.