Status: Unverified
FACT(s) (Environment):
IBM AS/400 (RISC)
SYMPTOM(s):
Unable to update field at offset <offset> in table <table name>. (1735)
Unable to update <filename> Field. (142)
CAUSE:
Prior to Progress/400 DataServer 8.0C (7.3C30), Progress supported Legacy Support for the progress unknown value. That is if Progress client sends an unknown value to the DB2/400 database, Progress/400 converted the unknown value to a arbitrary date value in the format that the DB2/400 has defined. Please look at the Appendix B of the Progress/400 product guide for details.
Progress/400 DataServer 8.0C (7.3C30) introduces support for SQL NULL
value, which works with client versions starting with 8.1A. The SQL
NULL is meant to replace the previous use of the Progress Unknown
Value (?). There was a new parameter ALWPROUNK "Allow PROGRESS Unknown
Value" in DUPPRODB command in version 8 of Progress/400 where the user can choose the "old" or"new" behavior. Please look at the section 2.2.3 (Progress Unknown Value Support) of the Progress/400 product guide for details if you like to implement the SQL NULL support with Progress/400 DataServer.
Starting Progress/400 version 9, the ALWPROUNK parameter was taken out from the DUPPRODB screen to eliminate the Legacy support for the Progress unknown value completely.
The aim in version 8 was that existing schemas created with DataServer 7.3C20 or
previous work with this parameter to *YES to allow backward compatibility. Unfortunately, there is a bug which makes this behavior completely on the contrary. The result is that unknown values stored in the database are not seen
as such with the new 8.1A or later client. Or trying to create new
records with ASSIGN, the client receives the error:
Unable to update field at offset <offset> in table <table>. (1735)
These and other minor problems with Unknown Values have been
completely solved starting in version 8.0C40 for the DataServer and
8.1B for the client.
Progress always recommends to upgrade to the newer release even though the version 9 of progress/400 does not support legacy support for Progress unknown value. However, there is a workaround which might be useful if the legacy support is necessary for some reason. It consists in changing a flag in the server schema in the as/400 and in the schema holder(s) in the progress client. To fix the server schema connect the schema holder and AS/400 database and run this code from the windows client:
FIND FIRST P__DB EXCLUSIVE-LOCK.
P__DB._Db-Misc1[4] = 1
P__DB.Db-Misc[1] = P__DB.Db-Misc[1] + 1.
The DB2/400 Data Dictionary interface is not implemented for UNIX client. In this case, the server schema should be modified in the as/400 machine using the DFU utility.
If a new server schema is created using version 9, the only flag is needed to be changed is P__DB._Db-Misc1[4]. Modifying P__DB._Db-Misc1[4] to 1 and creating a new schema holder in the client afterward will give the legacy support for date field back.
The error "Unable to update <filename> Field. (142)" is because of the first error. Once the workaround is implemented this error will disappear.
FIX:
For an existing server schema in as/400, perform the following code using the windows client after connecting to the server schema:
FIND FIRST P__DB EXCLUSIVE-LOCK.
P__DB._Db-Misc1[4] = 1
P__DB.Db-Misc[1] = P__DB.Db-Misc[1] + 1.
After the change in server schema, the schema holder in the client machine also needs to be changed. The synchronization of the client schema holder with server schema does not change the above field. In order to change the above flag and value to the client schema holder, just connect to the client schema holder only and run the same code.
For a new server schema in as/400, perform the following code using the windows client after connecting to the server schema:
FIND FIRST P__DB EXCLUSIVE-LOCK.
P__DB._Db-Misc1[4] = 1.
After modification of the flag, create a new client's schema holder.
In the case of the UNIX client, use the DFU utility in as/400 to change the flag of P__DB._Db-Misc1[4] and increment the value of P__DB.Db-Misc[1] by 1 for the existing server schema.
For the new server schema the only field needs to be changed using the DFU utility is P__DB._Db-Misc1[4] to 1. Then create a new schema holder in the client machine.