System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig.

[ALPBRCH]

Hello,
unfortunately, the SQL query to IBM DB2 fails:

Error in SQL statement: ODBC-call returned [-1] : [42000][-104][IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. Gültige Token: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.

SELECT * FROM TEST.TABLE

ODBC Info:

Isn't DB2 supported?

regards
J.Immel

[edwig]

Hello J.Immel, Wel certainly it is. As you can see in the info tree the ODBC driver is recognized and working. The nasty snare with the ODBC “42000” state is that the ‘official description’ from the standard states 42000: Syntax error or access violation And as I can see the SQL0104: invalid token error (German Gültige Token) I suspect that the problem Here does not lie in the syntax of your select statement (SELECT * FROM <table>) But that you face some access problem with the logged in user not being able to see the “table” in the “test” schema. Furthermore as we google for the “FOR USE SKIP WAIT… etcetera” you can find that all sorts of problems where the select cannot follow up naturally end with this error text in DB2. Personally I would suggest investigating in the direction of a privileges problem, Best regards, Edwig Huisman From: ALPBRCH ***@***.***> Sent: dinsdag 2 mei 2023 09:54 To: edwig/ODBCQueryTool ***@***.***> Cc: Subscribed ***@***.***> Subject: [edwig/ODBCQueryTool] System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. (Issue #7) Hello, unfortunately, the SQL query to IBM DB2 fails: Error in SQL statement: ODBC-call returned [-1] : [42000][-104][IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. Gültige Token: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET. SELECT * FROM TEST.TABLE ODBC Info: <https://user-images.githubusercontent.com/113665911/235609832-94ae3321-75cd-4abc-9e1f-30366da27779.jpg> Isn't DB2 supported? regards J.Immel — Reply to this email directly, view it on GitHub <#7> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABFDS33WGM7I36WUHD7EYKLXEC4RVANCNFSM6AAAAAAXSVY55U> . You are receiving this because you are subscribed to this thread. <https://github.com/notifications/beacon/ABFDS37KKZT5ORZKNBZUKQLXEC4RVA5CNFSM6AAAAAAXSVY55WWGG33NNVSW45C7OR4XAZNFJFZXG5LFVJRW63LNMVXHIX3JMTHGJWI2LA.gif> Message ID: ***@***.*** ***@***.***> >

[ALPBRCH]

Hello Edwig,
thank a lot for the fast response. It's not because of the permissions - the user has admin rights to the database and tables. It works with other SQL query tools.
The last version of ODBC Query Tool that worked with DB2 was 1.04xx if I remember correctly :).
It may also be due to the IBM Access ODBC driver. Thanks for your help.
Best regards
Jürgen Immel

[edwig]

Hello Jürgen,

To verify I installed the community version 11.5.8 of DB2.
During installment I selected to NOT use the OS integrated security but local database security.
Alsoo I opted to install the standard "SAMPLE" database and the DB2 ODBC drivers.

Firing up Sthe ODBCQuerytool showed all tables in the table tree when using the standard "db2admin" account.
But no SELECT could be run. This resulted in a strange error, namely:

[42501][-551][IBM][CLI Driver][DB2/NT64] SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "DB2ADMIN". Operation: "SELECT". Object: "CUSTOMER". SQLSTATE=42501

So I retried using my MS-Windows account and Ho Presto! Lo and behold!
Everything worked fine! SELECT, UPDATE, everything.

The standard installation of the latstest version and ODBC drivers led to a working situation with the current ODBC Querytool.
I am afraid that in your situation a different approach may be needed to find a working combination, but I think it is hereby proven that the standard software (DB2, ODBC-Driver, ODBC Querytool) is not to blame.

But do check out this post:
https://www.sqlservercentral.com/forums/topic/as400-as-linked-server
The error message that you reported can be found on the internet, with a root cause that people are trying to connect to, and use, a linked server.

For now, I will not further investigate in this matter, unless you can give me some more clues. Sorry.

Best regards,
Edwig Huisman.