Gen CREATE: SQLCODE=-206, SQLSTATE=42703 for unused attribute

Products

• Gen - Workstation Toolset
• Gen
• Gen - Run Time Distributed
• Gen - Host Encyclopedia

Issue/Introduction

Gen Java application running against DB2 database.
Developers have added new attributes to an existing entity and implemented those attributes in Technical Design/Data Structure List.
However the new attributes are not used in the Gen code and will only be used later by developers. Also the entity's corresponding DB2 table has not been updated with the new columns for those attributes.

Even though the new attributes are not used in newly generated code when the application executes a CREATE statement receive this error for one of newly added attributes:
===
TIRM037E: Fatal error was encountered ***
TIRM170E: An error was encountered on the server
DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703,
SQLERRMC= ATTRIBUTE_NAME, DRIVER=4.27.5
SQLSTATE= 42703
SQLCODE= -206
DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703,
...
TIRM038E: Fatal database error was encountered ***
TIRM039E: DB last status = SE
===

Is this expected behaviour?

In addition, the generated Java source code for the action block also contains references to the newly added attribute names, even though they are not used in the action block code.

Environment

Release : 8.6

Component : Gen Generators

Cause

From DB2 references SQL error codes and SQLSTATE values and common error codes
SQLCODE=-206 means "object-name IS NOT VALID IN THE CONTEXT WHERE IT IS USED"
SQLSTATE=42703 means "An undefined column or parameter name was detected."

That error is expected because the current database table is missing the corresponding column for the newly added attribute.
The code generation uses the current Technical Design/Data Structure List and for an entity CREATE statement the codes includes all the attributes defined for that entity in the Data Structure List. So it does not matter if the newly added attribute is referenced directly or not in the code because for something like a CREATE statement it is still going to reference that attribute indirectly.

Resolution

Always ensure that all parts of the application are synchronised e.g. in this scenario the database table is out of sync with the Technical Design/Data Structure List that is used to generate the code.
The developers can add new attributes to existing model entities but they should not implement them into the Technical Design/Data Structure List until they are ready to implement that changed design in the database itself so it matches with any newly generated code.

Additional Information

Support tested with simple test model targeting Oracle and received similar error for missing column/identifier for newly added attribute NAME1
===
TIRM038E: Fatal database error was encountered ***
TIRM039E: DB last status = DB
ORA-00904: "NAME1": invalid identifier
SQLSTATE: 42000
SQLCODE: 904
===