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?
Announcement
Collapse
No announcement yet.
Determine SQLRPGLE Program Executing SQL Causing SQL0206 (Column not found)
Collapse
X
-
Originally posted by Rocky View PostI'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.
Comment
-
Originally posted by Rocky View PostIf 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
PHP Code: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
Comment
-
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
- Likes 1
Comment
-
Originally posted by Herb... View PostIf 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
Comment
-
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:
Code:-- 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;
Comment
-
Originally posted by B.Hauser View PostBTW 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:
Code:-- 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;
Comment
Comment