JPA DB2 - Getting SQLCODE "-204", SQLSTATE "42704" and SQLCODE=-727, SQLSTATE=56098 after dropping table

Question

I've got an annoying problem after had one of our tables dropped.

I'm trying to persist an entity but every time I call the persist(...) on it I get this error message:

Caused by: com.ibm.db2.jcc.am.SqlException: An error ocurred during the implicit action "3". SQLCODE "-204", SQLSTATE "42704" and tokens from message "MYDB2SCHEMA.TB_ABCD".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.12.56

It looks like it is not able to find "MYDB2SCHEMA.TB_ABCD", of course it won't as DBA dropped it last week. But it is very weird because the entity I'm trying to persist has no relationship to "MYDB2SCHEMA.TB_ABCD" entity, lets call it ABCDEntity.

To let things even more complicated, I deleted the ABCDEntity so that there is no entity related to it any more, then I searched for "MYDB2SCHEMA.TB_ABCD" in the entire project and there is nothing too, but still I get the same error.

The table I'm trying to persist in DB2 has no relationship to the dead table, I'm not a DBA but I believe if it the dead table had any relationship it would have been removed during the drop.

So, why is DB2 or JPA still trying to looking for that table if there is no Entity/table related to it any more?

mustaccio mustaccio · Accepted Answer · 2015-11-10T20:52:41

There must be some other object in the database, such as trigger or view, that references the dropped table. DB2 attempts to revalidate that object when it is referenced. You might get a better idea what is happening if you trace the exact SQL statement generated by your persistence framework.

If for some reason you cannot ask your DB2 to find out the offending object, you could try this query to find what other objects still depend on the missing table:

with t(s,n) as (values ('YOURSCHEMA','YOURTABLE'))
select 'control'    as object_type, 
       dschema      as schema, 
       dname        as name, 
       case dtype when 'y' then 'row permission' when '2' then 'column mask' else 'unknown' end as depend_type
from syscat.controldep join t on btype = 'T' and (bschema,bname) = (s,n)
union all
select 'datatype'   as object_type, 
       typeschema   as schema, 
       typename     as name, 
       case when typemodulename is not null then 'in module '|| typemodulename else '' end as depend_type
from syscat.datatypedep join t on btype = 'T' and (bschema,bname) = (s,n)
union all
select 'index'      as object_type, 
       indschema    as schema, 
       indname      as name, 
       ''           as depend_type
from syscat.indexdep join t on btype = 'T' and (bschema,bname) = (s,n)
union all
select 'package'    as object_type, 
       pkgschema    as schema, 
       pkgname      as name, 
       ''           as depend_type
from syscat.packagedep join t on btype = 'T' and (bschema,bname) = (s,n)
union all
select 'routine'    as object_type, 
       routineschema    as schema, 
       specificname     as name, 
       case when routinemodulename is not null then 'in module '|| routinemodulename else '' end as depend_type
from syscat.routinedep join t on btype = 'T' and (bschema,bname) = (s,n)
union all
select 'table-ish'  as object_type, 
       tabschema    as schema, 
       tabname      as name, 
       case dtype when 'S' then 'MQT'
                  when 'T' then 'staging table'
                  when 'V' then 'view'
                  when '7' then 'synopsis table'
                  else dtype end as depend_type
from syscat.tabdep join t on btype = 'T' and (bschema,bname) = (s,n)
union all
select 'trigger'    as object_type, 
       trigschema   as schema, 
       trigname     as name, 
       ''           as depend_type
from syscat.trigdep join t on btype = 'T' and (bschema,bname) = (s,n)
union all
select 'global var'     as object_type, 
       varschema        as schema, 
       varname          as name, 
       case when varmodulename is not null then 'in module '|| varmodulename else '' end as depend_type
from syscat.variabledep join t on btype = 'T' and (bschema,bname) = (s,n)

JPA DB2 - Getting SQLCODE "-204", SQLSTATE "42704" and SQLCODE=-727, SQLSTATE=56098 after dropping table

1 Answers