Re: Debugging malfunctioning ODBC

[David Gibbs <david@xxxxxxxxxxxx>]

With jdbc you can set server trace properties that enable debug on the host
server job. Is that possible with an odbc connection?

https://javadoc.midrange.com/jtopen/com/ibm/as400/access/doc-files/JDBCProperties.html

David

On Fri, Aug 20, 2021 at 5:42 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

> Hello,
>
> since "a few days", I have a problem with ODBC from Linux to two of my
> V4R5 machines of mine. Some queries work, some don't. I can't tell since
> when the problem actually exists, so I can't tell which of the numerous
> changes in the last days might have introduced that behavior. The only
> thing I can tell for sure is that there have been no changes to the TCP/IP
> networking configuration of the client- and server machines for years.
>
> What I could isolate about "the problem" so far is:
> - It isn't related to a certain client machine.
> - Failing client machines can successfully query a similarly configured
> (odbc.ini) V7R2 machine.
> - The problem is appearing on two independent, but manually
> try-to-keep-configurations-synchronized server machines running V4R5.
> - It's not related to a defective *SQLPKG, because I deleted dynamically
> created ones, and for testing purposes I set ExtendedDynamic=0 in odbc.ini
> — no difference.
> - The failing queries also fail when done with QSECOFR => No authorization
> problem.
> - The failing queries work when done remotely with STRSQL/CONNECT TO via
> DRDA.
> - The failing queries work on a local STRSQL.
> - Testing is done with the isql command line tool on linux, while most
> automatisms use Perl.
> - Most working automatisms just INSERT data. These mostly seem to work.
> (For example, with the Spamassassin database, it's not too easily deducted
> if *everything* works or not. Asterisk inserting call data records fails.
> Asterisk querying a number to name translation table fails. Both have
> worked "before". Perl automatism inserting Spamassassin statistics works.)
> - Re-IPLing OS/400 didn't help.
>
> It seems that SELECTing from databases is generally affected.
>
> With the older iseries access 7.1.0 driver, I get:
>
> [S1000][IBM][System i Access ODBC Driver]Error in host server data stream.
>
> With the newer ibm-iaccess 1.1.0.14, I get:
>
> [S1000][IBM][System i Access ODBC Driver][DB2 for i5/OS]PWS0001 - function
> not completed successfully.
>
>
> When doing INSERTs into a database which definitely works with the Perl
> automatism (I see new records appearing):
>
> With the older iseries access 7.1.0 driver, I get:
>
> [S1000][IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0901 -
> SQL-System Error.
>
> With the newer ibm-iaccess 1.1.0.14, I get:
>
> [S1000][IBM][System i Access ODBC Driver][DB2 for i5/OS]PWS0001 - function
> not completed successfully.
>
>
> Of course, there isn't any output in the job logs of any QZDASOINIT, so I
> don't know what exactly is going on. I also can't find entries in QHST
> hinting to an error.
>
> How can I debug on OS/400 what's going wrong here?