RE: Tracing Web Program Calls / QZDASOINIT Optimization -- MIDRANGE-L

Okie dokie. Thanks for the info. I'll have a read over and see what's up.

I'm hoping that by moving everything to PHP and onto the 400 that it will alleviate this problem.

This, coupled with our imminent purchased of a new P7 machine should help in the long run. =)

/b;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, August 24, 2011 9:48 AM
To: Midrange Systems Technical Discussion
Subject: Re: Tracing Web Program Calls / QZDASOINIT Optimization

There's basically no way for the i (or any other DB server) to know
where the statement came from...irregardless of the use of PHP, .NET,
whatever...

To help with these types of issues, IBM added the CLIENT_xxxxx special
registers at 6.1. However, your application must be modified to set
the registers in it's connect string.
http://www.itjungle.com/fhg/fhg031809-story02.html

You can use a SQL monitor to capture the statements and performance
info. Then use visual explain on the captured statement to see what's
going on and what indexes are advised. And perhaps play with
alternate forms. But if you decide to change the statements, you'd
need to manually find the captured statements in you ASP.NET.

This is one reason I like the calling stored procedures. They provide
a well defined interface that the you can tune without touching the
calling apps.

HTH,
Charles

On Wed, Aug 24, 2011 at 9:03 AM, Brian Piotrowski
<bpiotrowski@xxxxxxxxxxxxxxx> wrote:

Hi All

We're having a problem with one of our SQL statements that does an ODBC call from our ASP web server to our AS400. We're not entirely sure which program is calling the statement that is throwing the error, so I'm wondering if anyone can tell me if there's a way to see what the initial webpage call was instead of the resulting error?

The reason we are having a problem is twofold - one, the system is thrown a pretty vague error that tells us a problem in a field that is used in many different programs and many other SQL statements. Two, it is consuming a lot of CPU cycle time (in some instances, it is in excess of 30%!).

The resulting error message we are seeing is:

Job . . : QZDASOINIT User . . : QUSER Number . . . : 005126

Job 005126/QUSER/QZDASOINIT started on 24/08/11 at 06:04:12 in subsystem
QUSRWRK in QSYS. Job entered system on 24/08/11 at 06:04:12.
User WEBACCESS from client 10.160.200.15 connected to server.
Correlation without qualification occurred for column YCTRL to table
SST16.
Correlation without qualification occurred for column YCTRL to table
SST16.

What I need is the initial call that caused this error (ie: the webpage call - http://mysite.com/mywebpage.asp?someparmeters).

Looking at the job log doesn't help - there's no information on it in there either.

Anyone have any idea on how I can track this down? I found the above error through WRKACTJOB -> Selecting the offending job and doing a "5" on it and then a "10".

Long term we will be moving everything to PHP (via ZEND), but for now does anyone have any suggestions on how we can reduce the overhead of the QZDASOINIT jobs? Right now, the programmers are using this coding to access the AS400:

<DBACCESS400.ASP> - the program is an include statement in all ASP pages:
<%
Set adoCon400 = Server.CreateObject("ADODB.Connection")
adoCon400.Open "Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=webaccess;Password=mypass;Data Source=AS400_IP_ADDRESS"
%>

<ANYPROGRAM.ASP>

<%
Set rsWRA = Server.CreateObject("ADODB.Recordset")
strSQL = "MY SQL STATEMENT"
rsWRA.Open strSQL, adoCon400
<do stuff with resulting SQL data>
rsWRA.close
Set rsWRA = nothing
%>

Any ideas would be most appreciated.

Thankee-sai!

/b;

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Brian Piotrowski
Assistant Mgr. - I.T.
Simcoe Parts Service, Inc.
Ph: 705-435-7814 x343
Fx: 705-435-5029
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
http://www.simcoeparts.com

Please consider the environment. Don't print this e-mail unless you really need to.

The information contained in this communication is confidential and intended only for the use of those to whom it is addressed. If you have received this communication in error, please notify me by telephone (collect if necessary) and delete or destroy any copies of it. Thank you!

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