Re: User Query Tool Cognos & File Authority

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  The resolution to the error would seem to require determining both 
what "AOpen" is and does, and why it is apparently failing with or 
otherwise issuing a CPF2207.  Probably a review of a debug joblog of the 
ODBC server job will assist; giving SQL debug messages and the full 
message details for from & to of the error message.  Anyhow...

  My initial reaction was that the CPF2207 is not a message from any 
ODBC action, and thus the question about having ODBC ignore the LF 
authority is not germane.  This is because a SQL SELECT against a table 
without any references to the unauthorized LF should not see any 
authority error.  And if the statement did reference an LF to which the 
user has no authority, it should be manifest as SQL0551 or similar.

  However if a request is not FOR READ ONLY, then a request to SELECT 
using a driver that is not forcing read-only may fail if the default is 
FOR UPDATE.  The failure could be either for insufficient authority to 
the file to be updated, or for applications that require a unique key 
then for lack of authority to a unique keyed LF.  Since the physical 
file is understood to have *USE authority, then presumably the request 
is intended to be read-only.?

  And to my surprise, the CPF2207 is apparently issued by the host:

  Searching CPF2207 on the www.ibm.com/support/us will find a topic 
"Table Is Read-Only: ODBC Application Requirements for Updating OS/400 
or i5/OS Tables" which describes the limitation for update-capable 
requests where the [ODBC based] application requires a unique key:
  The exception to this is the ODBC API SQLStatistics. This call 
returns information on the indexes or logicals available for a specific 
table. Applications use the SQLStatistics call to determine whether 
there are unique indexes available for a table to be updatable. The 
OS/400 or i5/OS database server returns this information from the OS/400 
or i5/OS file descriptions, not the catalog tables. Here, the user _does 
need to have authority_ to the indexes or logicals over the file. If the 
user is not authorized, DB2/400 issues the message CPF2207 - Not 
authorized to use object xxxxLF in library xxxx .

  Note also there are some Cognos groups here:
 http://businessintelligence.ittoolbox.com/groups/technical-functional

Regards, Chuck
-- All comments provided "as is" with no warranties of any kind whatsoever.

Bonnie Lokenvitz wrote:
> I have a user attempting to use Cognos for report writing and the
> physical file had some logicals that had *PUBLIC authority *EXCLUDE.
>
> UDA-SQL-0107 A general exception has occurred during the operation
> "AOpen".
> [IBM][iSeries Access ODBC Driver][DB2 UDB]CPF2207 - Not authorized to
> use object XXX in library XXXXX type *FILE.
>
> He is referencing the physical in his connection and the physical has
> *PUBLIC authority *USE.  I have changed the logicals to *PUBLIC *USE.
>  
> Is there a way for the ODBC to ignore any logicals to which the user is
> not authorized?
>
> Thank you,
> Bonnie Lokenvitz