Recently I had a customer who contacted me with the following problem:
I have made my data base connection to the iSeries (AS400) and can see my files and library list structure.
When I go to access a file (table) I keep getting the below message:
[IBM][Series Access ODBC Driver] [DB2 UDB] SQL5016 – Qualified Object Name [file name] not valid on any file I try to look at.
I jumped on a quick webex after doing a little research on the error and guided the user to change the settings in the ODBC Driver following the instructions below:
Probable cause: Your ODBC Data Source Name (DSN) configuration uses the wrong naming convention. Use the ODBC Administrator to change your DSN to use the proper (*SQL or *SYS) naming convention. Always use *SQL unless your application design specifically expects *SYS.
SQL and system naming conventions: You can use either the system (*SYS) or the SQL (*SQL) naming convention in DB2® UDB for iSeries™ programming.
The naming convention used affects the method for qualifying file and table names and the terms used on the interactive SQL displays. The naming convention used is selected by a parameter on the SQL commands or, for REXX, selected through the SET OPTION statement. See Qualification of unqualified object names in the SQL Reference for more details.
This setting is enabled via the odbc adminstrator panel and may require IT implementation dependent on your user rights on your local machine. However if you have permission to amend the odbc connector or you are setting up the connection yourself ensure that you go to the Server Tab and in the drop down box select SQL Naming Convention. This convention allows Toad Data Point to connect correctly and query the objects within the database.
This simple change when setting up your iSeries ODBC Connection can prevent annoying head scratching and frustration when trying to query the database.