Determine SQLRPGLE Program Executing SQL Causing SQL0206 (Column not found)

[TheZenbudda]

I am seeing a bunch of repeating SQL0206's in our http server instance job logs. The error does not indicate which SQLRPGLE program contains the erroneous SQL statement. Is there some way (unknown to me) to find the offending program?

[Rocky]

I'm curious how this message is getting into the joblog - my system doesn't show that in a joblog without the code in the program to generate it by checking SQLCOD <> 0 or something similar.

[jtaylor___]

I don't know of any easy way. I always have to resort to serious detective work.

[TheZenbudda]

I'm curious how this message is getting into the joblog - my system doesn't show that in a joblog without the code in the program to generate it by checking SQLCOD <> 0 or something similar.

This is a web job (QHTTPSVR). We have tons of SQLRPGLE written that doesn't check for SQLSTT or SQLCOD and assumptions are made about the response. We even have code where someone forgets to put the colon in front of a variable and the error pops up in the job log stating the field does not exist in the file.

[Rocky]

If you press F1 then F9 on the message it doesn't give any insight?

[TheZenbudda]

If you press F1 then F9 on the message it doesn't give any insight?

No. So since have 40+ web jobs, and the job logs are flooded with junk, I use a query that invokes QSYS2.ACTIVE_JOB_INFO to pull the job logs. The below copy/paste is from that query.

Here is an example

Result of SELECT more than one row.

QSYS    QSQRUN2    QSQFETCH    CK_DEBUG    24004    QSYS    QSQRUN2    QSQFETCH    CK_DEBUG    24004

&N Cause . . . . . :   The result table of a SELECT INTO statement, a subquery, or a subselect of a SET statement contains more than one row.  The error type is 1. If the error type is 1 then a SELECT INTO statement attempted to return more than one row.  If the error type is 2 then a subselect of a basic predicate has produced more than one row.  Only one row is allowed. &N Recovery  . . . :   Change the selection so that only one result row is returned and then try the request again.  The DECLARE CURSOR, OPEN, and FETCH statements must be used to process more than one result row.  For a subquery the IN, EXISTS, ANY or ALL predicates can be used to process more than one result row.  If one row was expected, there may be data errors, such as duplicate rows, that are causing more than one row to be returned.

SQL0811    DIAGNOSTIC 

[Vectorspace]

I have always found it incredibly frustrating that when an embedded SQL statement fails and produces a job log message, the job long entry resolve to the SQL Engine program instead of your actual program that contained the statement.

[Herb...]

If you have the latest version of Access Client Solutions, then the SQL Performance Center will help track down the SQL statement. This is an amazing tool for monitoring SQL statements being run on the system.
- Launch ACS (or if you're in an ACS 5250 session, click the Action menu and then SQL Performance Center)
- Select System and launch SQL Performance Center (in the Database group)
- Click Show Statements... (toward the top)
- Set the filter value(s) (based on the joblog message; you do not need to set all the filters, start a little wide and refine as needed)
- Click Show (this is creating a snapshot so it might take a moment or two to display)
- Job information is out to the right so you may need to scroll
- Right clicking on any of the SQL statements gives you the option to work with the SQL statement with (or without) values

[TheZenbudda]

If you have the latest version of Access Client Solutions, then the SQL Performance Center will help track down the SQL statement. This is an amazing tool for monitoring SQL statements being run on the system.
- Launch ACS (or if you're in an ACS 5250 session, click the Action menu and then SQL Performance Center)
- Select System and launch SQL Performance Center (in the Database group)
- Click Show Statements... (toward the top)
- Set the filter value(s) (based on the joblog message; you do not need to set all the filters, start a little wide and refine as needed)
- Click Show (this is creating a snapshot so it might take a moment or two to display)
- Job information is out to the right so you may need to scroll
- Right clicking on any of the SQL statements gives you the option to work with the SQL statement with (or without) values

Thanks for pointing this out! It at least gives me a start! Too bad it doesn't have a job filter. I should be be able to compare the timestamps of the job log message to the Performance Center statements.

[B.Hauser]

BTW easier than pageing through the joblog on the green screen or spoolfile, might be using the JOBLOG_INFO User Defined Table Function within the QSYS2 library:

-- Joblog of the Current Job
Select * from Table(Qsys2.Joblog_Info('*')) x;

--JobLog of any (active) job
Select * from Table(Qsys2.Joblog_Info('123456/JOBUSER/JOBNAME)) x;

Birgitta

[TheZenbudda]

BTW easier than pageing through the joblog on the green screen or spoolfile, might be using the JOBLOG_INFO User Defined Table Function within the QSYS2 library:

-- Joblog of the Current Job
Select * from Table(Qsys2.Joblog_Info('*')) x;

--JobLog of any (active) job
Select * from Table(Qsys2.Joblog_Info('123456/JOBUSER/JOBNAME)) x;

Birgitta

Thanks Birgitta! That's exactly what i'm doing (see post #6). I got that one from you from another post on this forum. lol.