This document explains the requirements of some ODBC applications when updating IBM® OS/400® or IBM® i5/OS® database tables and files.
Resolving The Problem
This document explains the requirements of some ODBC applications when updating IBM® OS/400® or IBM® i5/OS® database tables and files. Some ODBC applications will report that a file is read-only even though the user has the proper authorities to the table. These applications require additional restrictions on the table design; typically, a restriction is that the table have a unique key.
|o||Verify that the table has a unique key (primary key or unique constraint). Note that because of differences in precision, some applications cannot work with unique key fields of type TIMESTAMP.|
|o||Use Client side cursors or MDAC 2.1 and later (MDAC is available at support.microsoft.com/?scid=ph;en-us;1595).|
OS/400 or i5/OS Requirements:
|o||Verify that you are authorized to the table.|
|o||Verify that the ODBC data source is configured for a read/write connection and not a "read only" connection.|
|o||Verify that other security restrictions such as user exit programs and IBM® iSeries™ Access policies are not preventing the update.|
|o||Verify that the file is journaled if the isolation level is not *NONE.|
File is read-only.
This is perhaps the most common error. Typically, this is seen when the file does not have a unique key and the application is attempting to use a keyset or static cursor. The application might also be attempting to use optimistic locking. In these situations, the application is actually deleting and updating records by running an UPDATE or DELETE SQL statement that has "WHERE <field1 = old value AND field2 = old value....". If there is no unique key on the file, the application might accidentally delete or update multiple rows when the user thought that only one row was going to be affected. Rather than risk corrupting data, many applications just open the file as read-only. Another possible cause of the problem is too many indexes or too many fields in the unique key. For example, some applications have failed when the unique key was made up of 20 or more fields, apparently because there are more fields than their internal design can handle.
To resolve the problem, create a unique key on the table. If you are a programmer writing the application then you can also switch to a dynamic, updatable cursor and positioned update/delete. Later versions of Microsoft® Access (97 and 2000, for example) lifted the requirement that a unique key actually exist and prompts the user to select field(s) making up a unique key. If a unique key does exist, try reducing the number of unique keys or the number of fields in the key. This circumvents the limitation of some applications.
Also, refer to message CPF2207 below.
Run-time error '-2147467259 (80004005)' - Insufficient base table information for updating or refreshing.
This error is generated by the MDAC runtime (ADO for example) when it is unable to parse the SQL select statement due to invalid characters in the library or table name. For example, "select * from my#lib.my#file" will generate the error. The problem has been seen with MDAC/ADO 2.5 and 2.6.
The pound (#) sign is not a valid character in ODBC or in any ISO/ANSI SQL version. Although Client Access ODBC and IBM® DB2®/400 allow certain nonstandard characters, the DB2/400 SQL Reference strongly recommends using the invariant character set. Consider changing the name(s), creating and using an SQL ALIAS, or setting the default collection to resolve the problem.
Run-time error '-2147217865 (80040e37)' - Client Access Express ODBC Driver (32-bit)[DB2/400 SQL] SQL0204 - "qCustcdt" in "qiws" type *FILE not found.
Some versions of the MDAC parser appear to assume that all names are quoted identifiers (case-sensitive names). For example, the SQL statement "Select * from qiws.qCustcdt" generates the error message listed above. An SQL log shows that MDAC built the SQL Statement "Insert into "qiws"."qCustcdt" ....". Because MDAC added the double quotes around the names, the names are now treated as being case sensitive (they are quoted identifiers in SQL terms). This problem has been observed in MDAC 2.6.
To resolve the problem, treat the library name and file name as if they were case sensitive.
"[IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable."
This error is returned by Client Access if an application attempts to insert, delete, or update data using the SQLSetPos API. Client Access ODBC does not support this function. This error has been reported by numerous applications including ADO, DAO, RDO and others.
Some applications can generate the necessary insert/update/delete SQL statements. All versions of MDAC can supply the update ability if a client side cursor is selected and the table has a unique key. With MDAC 2.1 and later, ADO/MDAC can supply update ability even for dynamic server side cursors. Note that in each case the update ability is supplied by the Microsoft runtime or the application and not Client Access ODBC.
To resolve the problem, try upgrading MDAC, using a client side cursor, or changing the cursor and lock type. Note that a unique key on the table might be required. The programmer can also prepare and run their own update, insert, and delete statements. This method is required for positioned updates (dynamic, server side cursor with update lock concurrency).
CPF2207 - Not authorized to use object xxxxLF in library xxxx.
The user must have Read authorization to all logical files and all indexes built over the table. Some applications suppress this error message and only report a message such as "table is read only." For a detailed description of this problem, refer to the authority section below.
SQL7008 - xxxxxx in xxxxx not valid for operation.
The data source or application have set the isolation level to an isolation of read uncommitted or higher and attempted to modify a table that was not journaled. Set the commit mode in the data source to *NONE or journal the table.
Can Both Physical and Logical Files Be Updated?
DB2/400 treats logical files similar to an SQL view. Most logical files and views created over one base table are updateable; however, certain restrictions apply. See the SQL Reference and Database Programming Reference for further details. Even though OS/400 or i5/OS can allow updates of the view/logical file, some PC applications can report the logical file as being read-only. At this time, the OS/400 or i5/OS database server does not return information to a request for unique keys on a logical nor does it return catalog information on the constraints that a logical inherits from a physical file. Because no unique key information is reported to the client, most applications do not consider a logical file to be updateable. A design change request for this feature has been submitted and is under consideration for future releases. Newer versions of Microsoft Access, such as Access 7.0 and later, let you work around this by manually picking the fields that make up the unique key.
What Types of Tables, Files, and Indexes Are Recognized as Having Unique Keys?
The following files and indexes are recognized as having unique keys:
|1||DDS-described physical files with the keyword UNIQUE specified. DDS allows only one unique key to be defined on the base file.|
|2||DDS-described physical files that have a logical file built over them and that logical contains the keyword UNIQUE.|
|3||SQL-defined tables built with a PRIMARY KEY or UNIQUE constraint.|
Note: A unique constraint specified on CREATE TABLE is not reported correctly in OS/400 R420 through R510. Contact IBM support for further information.
|4||SQL-defined tables that have a unique index built over them with the SQL CREATE UNIQUE INDEX statement.|
Example of DDS Described Logical File with a Unique Primary Key:
00020A* SAMPLE LOGICAL FILE (CUSMSTL)
00050A R CUSREC PFILE(CUSMSTP)
00060A TEXT('Logical File’)
00100A K CUST
Example of a Unique Key Created with SQL:
CREATE UNIQUE INDEX indexname ON library/tablename (fieldname1, fieldname2)
Is Authority Required to Both the Physical File and the Logicals over It?
The short answer is yes.
When querying data files, the authorization to a file is only verified if the file is being directly used. Here is an example to illustrate. You have a physical file that has authority of public *use. You have a logical over that physical that has public *EXCLUDE. A user with public authority can successfully run a query defined on the physical file because it requires authority to the physical file only, even though an index created by the logical might be used to implement the query. A user that attempts to run a query directly over the logical file would get a not authorized error.
In addition to querying data files, ODBC applications also query the DB2/400 catalogue tables and views for information on the tables and columns available on the operating system. Applications issue the ODBC API calls for SQLTables and SQLColumns to return listings of tables and columns on the operating system. For both calls, DB2/400 returns information on all files even if the user is not authorized to the file. If the applications ask for all table types or specify a table type of View, then logical files are returned.
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.
18 December 2019