SQL1596N WITH EMPTY TABLE cannot be specified for tableX
what is another option to truncate a table that has children
If you trying to truncate a table with the command below:
db2 alter table <table_name> activate not logged initially with empty table
and you got the message below:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1596N WITH EMPTY TABLE cannot be specified for "tableX".
SQLSTATE=42928
it is, probably, because this table has children,so another option to truncate this table is with the following command:
db2 load from <null_file> of del replace into tableX nonrecoverable
where <null_file> is, example on Linux: /dev/null
After that you have to verify what are the tables which are in pending integrity:
db2 "select substr(rtrim(tabschema)||'.'||rtrim(tabname),1,40) from syscat.tables where status = 'C'"
If you have any table in set integrity, apply the command below:
db2 set integrity for tableX immediate checked
You will probably see the message below:
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
Apply the command above again:
db2 "select substr(rtrim(tabschema)||'.'||rtrim(tabname),1,40) from syscat.tables where status = 'C'"
db2 set integrity for tableX immediate checked
When you will not see more results to the select below:
db2 "select substr(rtrim(tabschema)||'.'||rtrim(tabname),1,40) from syscat.tables where status = 'C'"