Personal tools
You are here: Home DB2 Problem Resolution General errors SQL1596N WITH EMPTY TABLE cannot be specified for tableX
Navigation
Log in


Forgot your password?
 
Document Actions

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'"


DONE!!! you truncated your table successfully.

Related content
Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls