Re: Is there a way to debug or trace a SQL statement submitted from a client system.

On 29-Sep-2016 15:26 -0500, a4g atl wrote:

We are on v6.1; just recently upgraded to it on the way to 7.1.

A insert into our table from a remote system using Web Methods is
failing with a SQL0104 if I remember.

The statement is rejected and I cannot see what is wrong or what the
system sees to reject it.

This is the statement generated by WebMethods.

"INSERT INTO OEFILES.FTFOLHDV
( H1ORTP, H1ORD#, H1CPO#, H1EDAT, H1CST#, H1BNAM, H1BAD1, H1BAD2
, H1BCTY, H1BSTE, H1BZIP, H1BCNT, H1SNAM, H1SAD1, H1SAD2, H1SCTY
, H1SSTE, H1SZIP, H1SCNT, H1LNTL, H1RDAT, H1PDAT, H1MFOR, H1SHPC
, H1SHPM, H1PLST, H1OPO#, H1OPOL, H1DROP, H1QUOT
) VALUES
( ?, ?, ?, ?
, ?, coalesce(?,' '), coalesce(?,' '), coalesce(?,' ')
, coalesce(?,' '), coalesce(?,' '), coalesce(?,' '), coalesce(?,' ')
, ?, ?, coalesce(?,' '), ?
, ?, ?, ?, ?
, ?, ?, coalesce(?,' '), coalesce(?,' ')
, coalesce(?,' '), coalesce(?,' '), coalesce(?,' '), coalesce(?,' ')
, ?, coalesce(?,' ')
)". "(22001/0) Data truncation"

On more /recent/ releases [as if v6r1 qualifies], some failures to diagnose some warnings was corrected by the SQL; perhaps that sqlstate=22001 is such a condition, presumably being for a sqlcode=-404 for which the msg SQL0404 might be expected [i.e. probably not sql0104 which is a syntax error, and the reformatted SQL looks syntactically valid]. Perhaps if the feature is using SQL Packages (SQLPKG), then the use of an outdated package could play a role; that if what package is known, then the Delete SQL Package (DLTSQLPKG) of that package object might resolve the issue -- per the creation of a new package and storing of an updated statement when the request is repeated.?

As to debugging, there are connection options that can activate the /debug messaging/ and perhaps other features. Without client specifications, the job that services the SQL requests can be located [e.g. by WRKOBJLCK The_UsrPrf_Name *USRPRF] and that job serviced [STRSRVJOB] after which tracing and debug are enabled against that job. There is also the DB Monitor that can be requested [STRDBMON] to collect detailed data about the job. Some of those and other features may be available in some database components of iNav, that might be found to be useful.