QZDASSINIT job

[jj_dahlheimer]

We had one that was working our system pretty good, and it was a connection from our website. I was trying to figure out what it was doing. There is nothing in the job log and I can't find anything in the sql plan cache that would tell me what statements it was running. Anyway to get a log as to what they were doing/ran?

[tomliotta]

Those are the database SSL server program jobs; I think the SSL equivalent to the QZDASOINIT jobs. In general, if you run a secure (remote) database, then perhaps every (remote) statement was running there. If multiple instances of that prestart job were running, then you'd need to know what connections were established at that time through that job. and of course your system would have to be logging (remote) SQL statements.

You might be able to determine what the basic connection was like this:

DSPLOG PERIOD((*AVAIL '03/31/17')) JOB(157513/QUSER/QZDASSINIT)
   MSGID(CPIAD09)

If you don't know the complete job name, you might be able to use just 'QZDASSINIT'. You can specify PERIOD() to narrow it down some. The CPIAD09 messages should help determine what user was connecting, though right now I can only test for QZDASOINIT jobs -- a different message ID might be sent from QZDASSINIT. (This local network isn't a true production network, so there are no SSL database connections.) If you don't specify a message ID, you'll see all history log messages for QZDASSINIT jobs. You can check those to see if a message ID will help to shrink the list.

But that only gives basics of a connection. It won't tell you the statements nor any details of what was happening with in the connection.

Also, you might use DSPJRN against QAUDJRN, the system audit journal. Depending on what auditing is configured on your system, you might learn what types of actions were attempted or done against what objects. The date/time and job name are useful there.

Beyond those, if SQL statements aren't being logged, it's not clear what you can do. Logging would most often be done through a database monitor job via STRDBMON or through exit programs.

[jj_dahlheimer]

We do have the complete the name but you are correct dsplog does not tell us much, and it does not appear we have a lot of good info in our qaudjrn but we may have to review that for future use and see what additional config we can do to capture some of it.

I was digging through the plan cache and you can get statement history which lists each of the jobs that ran the statement, I was thinking if I could figure out how it got that I may be able to get all the statements ran for a specific job but so far have not been able to find where that is stored.

Thanks.

[EmmanuelW1]

One way is via Navigator - use Work Management, then Server Jobs, then right-click the QZDASSINIT job(s), then select Details, then SQL. That should show the SQL statement being performed.

Cheers,

Emmanuel

[tomliotta]

You'd have to do some digging, but iNav calls (external) stored proc QSYS2.ANALYZE_PLAN_CACHE(). It has a pretty good sized list of possible parms, though most are easy enough to figure out. A couple are tougher, so a comm trace or one or two other tools might help. Beyond that... it's mostly up to you.