How to debug client server applications?

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

Hello all!
We had a bizarre problem with a Delphi PC application that uses ODBC.  The
app was working great for years, then fell over Thursday.  No changes to the
app, the database or the AS/400.  No QSYSOPR messages, no object locks,
nothing unusual.  The app creates SQL statements to get info from the
AS/400.  The 400 services these requests in the QSERVER subsystem with one
of many QZDASOINIT jobs.  The job logs have messages similar to these:

CPIAD02 Servicing user profile XXXXXX.                         
CPIAD12 Servicing user profile XXXXXX from client xxx.xxx.xxx.xxx.
SQL7917 Access plan not updated.                                

These messages occur when the app works as when as when it fails.

The biggest hassle was trying to locate which server-side job is servicing
the particular client requests.  This is because all the QZD... jobs show up
in WRKACTJOB as QUSER, but internally they switch user profiles to match the
user ID of the client requester.

Ultimately, we spent hours displaying each of these QZDASOINIT jobs (there
are hundreds of them) to look for the user which was complaining of a locked
job.  The hassle is because there are _other_ Delphi apps that use these
server side prestart jobs, and they were running fine.  What a pain! 

Is there an easy way to find out what server jobs are servicing a particular
user profile?  WRKUSRJOB is useless as is WRKACTJOB.  I could use QUSLJOB
and QUSRJOBI and brute force search each job (which is what I'm starting on
now.) <grrrrr>

Buck Calabro
Aptis; Albany, NY

ps.  The problem turned out to be locks on the SQL package.  We killed all
jobs holding a lock on the package and the "lock up" went away.  We suspect
that the PC locked up requiring a re-boot at the exact moment the optimiser
was trying to update the access plan in the package.  This apparently
corrupted the package AND left it locked so that the next user couldn't
update the (now corrupt) access plan.  Because of the paucity of tools, we
can only hypothesize. :-(
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---