Re: Tracing iSeries SQL Queries from a web page -- MIDRANGE-L

There are 2 ways to find out what statement was really run. One is touse the database monitor (STRDBMON, etc., commands). You can run thisas you need and get information in several files. They are not forthe faint-of-heart, but there is an excellent article somewhere atwww.iseries.ibm.com/db2 on using the database monitor for analyzingperformance. The DBMON can be run over the entire system of over aspecific job. I recommend the first for a short period and then runwhat you want to test, as remote data jobs are notoriously difficultto know the names of.

The other is to write an exit program for SQL. This is a morepermanent solution - changing registry exits is usually something youdo over the long term. Learning how to program for it is not too bad,but probably learning a few SELECTs is easier.


HTH
Vern

At 03:32 PM 1/12/2006, you wrote:

Hi All,



Not sure if this is a question for the midrange group, but I thought I'd
throw it out and see what comes back...



I'm having an issue with one of our ASP web pages that is accessing
AS/400 data.  I suspect there is a "lost in translation" issue when the
SQL command is passed to the AS/400.



I have a physical file that has many fields, of which two are my query
targets - krdate (type "L") and krtime (type "T").  I have an SQL query
constructed as such in ASP:



KLSD="select krcode,count(krcode) CD from spsl.kls_data where kplcd='" &
request.form("plantcode") & "' and char(krdate)='" & sdate & "' and
(krtime between '06:30:00' and '15:30:59') group by krcode"



When I run the ASP page, pass it a value of 01/12/2006 and query the
value of KLSD, I get:



select krcode,count(krcode) CD from spsl.kls_data where kplcd='2' and
char(krdate)='12/01/06' and (krtime between '06:30:00' and '15:30:59')
group by krcode



(I have everyone enter the data MM/DD/YYYY, but our databases are in a
DD/MM/YYYY format - I need to parse the date to get it in the proper
format).



If I take this result and plug it into my handy SQL interpreter on the
AS/400, I get back some resulting data.  However, when it is run through
the program, I get a "no records" message back from my ASP page
indicating the query results are blank.



As I mentioned, I suspect there is a disconnect between what is supposed
to be passed to the interpreter and what is actually passed.  However,
I'm not sure how to trace the problem on the iSeries side to see what
exactly was being passed to the SQL interpreter from the webpage.



I am using the IBMDA400.DataSource.1 connector through a headless
connection on the webpage.  I have another routine that uses the same
connector in the same page (but obtains different data), and I get back
the expected results.  I'm wondering if it has something to do with the
"L" and "T" fields?



Anyone have any ideas on how I can trace the input / output on our
iSeries server?



Thanks,


Brian.



-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

PH: 705-435-7814

FX: 705-435-6746

-=-=-=-=-=-=-=-=-=-=-=-=-=-



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