0

I have table in db2 (using bigsql) that is partitioned as per date on IBM BigInsights 

table_name_abc
   20150810
      data corresponding to partition
   20150811
      data corresponding to partition
   ....

what I want is to delete particular partition say 20150810 or delete data from that partition

I tried this

db2 "truncate table test_schema.table_name_abc where partition_date = 20150810";

But it gave following error

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "where" was found following "test_table".
Expected tokens may include:  "".  SQLSTATE=42601

Can someone please instruct on how to do this?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Raunak Kathuria
  • 3,185
  • 1
  • 18
  • 27
  • `TRUNCATE` deletes all data in the table and `WHERE` is not supported by DB2. You could do `DELETE FROM test_schema.table_name_abc WHERE partition_date = 20150810` – data_henrik Aug 24 '15 at 13:36
  • @data_henrik I tried that also but it errors out `db2 "delete from test_schema.table_name_abc where partition_date = 20150515";` DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0150N The target fullselect, view, typed table, materialized query table, range-clustered table, or staging table in the INSERT, DELETE, UPDATE, MERGE, or TRUNCATE statement is a target for which the requested operation is not permitted. SQLSTATE=42807 – Raunak Kathuria Aug 25 '15 at 04:36

1 Answers1

2

Solved it by using the following command

db2 "ALTER TABLE test_schema.table_name_abc DROP PARTITION (partition_date = 20150515)";

Adding it as answer just in case someone needs it

Raunak Kathuria
  • 3,185
  • 1
  • 18
  • 27