SQLCODE -161 on accounting table with field TRANN

Products

• Datacom/DB
• Datacom
• DATACOM - AD
• Datacom/AD

Issue/Introduction

A Datacom A04 accounting table has been defined with field TRANN.

Running a SQL SELECT on the A04 table returns a -161 error:

SELECT * FROM SYSUSR.ACCT_A04;
                                                           
SQLCODE = -161, SQLSTATE=22S03                             
MESSAGE = TRANN UDECIMAL(5,0) INVALID DATA: E4C5DF         

 

Environment

Release: 15.1 

Resolution

SQL returns a -161 when it encounters invalid data in a column, however the data in the TRANN field is not corrupted.

According to the documentation section Special Accounting Names for Fields the TRANN accounting field is usually a 3-byte packed decimal number, but it is sometimes a character field.

For CICS systems tasks this field will contain a non-numeric value. 

There is no way in SQL to prevent the -161 when it encounrters invalid data. Instead of specifying * in the query to display all columns, change it to explicitly specify the columns to display excluding the TRANN column.

Alternatively run a query against the A04 table in Dataquery, DQRY will display ***E1*** on rows with invalid numeric data.