DB2 partition Table Delete and add partitions

1. Database version

2. Specific procedure

DROP PROCEDUREDb2user. Tools_partition_table_show (VARCHAR ());CREATE OR REPLACE PROCEDURETools_partition_table_show (inchEtl_dateVARCHAR(8))  /****************************************************************************** NAME:PURPOSE:REVISIONS: Ver Date Author Description----------------------------------------------------------- --------1.0 2015-07-22 Zen 1.  An example of adding and quickly deleting partitions as partitioned tables is provided for scripting references with the same requirements. ******************************************************************************/LANGUAGE SQLBEGIN  DECLAREV_locationVARCHAR( -); DECLAREV_start_timeTIMESTAMP; DECLAREV_sqlmsgVARCHAR(255); DECLAREV_cntINT; DECLAREV_partition_nameVARCHAR( -); DECLAREV_partition_endVARCHAR( -); DECLARE EXITHANDLER forSQLEXCEPTIONBEGIN GET Diagnostics EXCEPTION1V_sqlmsg=Message_text; Call Db2user.pro_log (Etl_date,'Tools_partition_table_show','Test partition Table Job mode', V_start_time, Current timestamp,'ERROR', v_location,v_sqlmsg); END; /*Clear the target table*/   SETV_start_time=  Current timestamp; SETV_location= 'Clear Data'; /*DELETE from Db2user.    S_CLM_RATE_POL_AAA WHERE BBQ = SUBSTR (etl_date,1,6); COMMIT;*/  SETV_location= 'the extraction of the Test policy odds table begins'; SETV_partition_name= 'P'||SUBSTR (Etl_date,1,6); SETV_partition_end=To_char (Add_months (To_date (Etl_date,'YYYYMMDD'),1),'yyyymm'); --SELECT To_char (add_months (To_date (etl_date, ' YYYYMMDD '), 1), ' yyyymm ') into the v_partition_end from sysibm.dual;    --determine if a partition exists, and if so, data is removed  SELECT COUNT(*) intoV_cnt fromsyscat.datapartitions TWHERETabschema='Db2user'      andTabName='s_clm_rate_pol_aaa_test'     andDatapartitionname=V_partition_name; IFV_cnt=1  Then           --partition data transfer out      EXECUTEIMMEDIATE'ALTER TABLE s_clm_rate_pol_aaa_test DETACH PARTITION'||V_partition_name||'Into Db2user. TEMP_S_CLM_RATE_POL_AAA'; COMMIT; EXECUTEIMMEDIATE'DROP TABLE Db2user. TEMP_S_CLM_RATE_POL_AAA'; END IF ; SETV_location= 'After partition data transfer out'; --An unhealthy DML or DDL needs to call Sysproc.admin_cmd ();  --Collect statistical informationCall Sysproc. Admin_cmd ('RUNSTATS on TABLE db2user. S_clm_rate_pol_aaa_test'); --Add new Partition  EXECUTEIMMEDIATE'ALTER TABLE Db2user. S_clm_rate_pol_aaa_test ADD PARTITION'||V_partition_name||'starting'||SUBSTR (Etl_date,1,6)||'INCLUSIVE Ending'||V_partition_end||'Exclusive'; SETV_location= 'After you add a new partition'; COMMIT;    EXECUTEIMMEDIATE'ALTER TABLE Db2user. S_clm_rate_pol_aaa_test ACTIVATE not logged initially';  forREC as  withTMP (TYPE) as(SELECT 'AAA1'  asTYPE fromSYSIBM. DUALUNION  All                          SELECT 'AAA2'  asTYPE fromSYSIBM. DUALUNION  All                          SELECT 'AAA4'  asTYPE fromSYSIBM. DUAL)SELECT *  fromTMP DoSETV_location= 'Test'||REC.      TYPE; INSERT  intoDb2user. S_clm_rate_pol_aaa_testSELECT      ......END  for; Call Db2user.pro_log (Etl_date,'Tools_partition_table_show','Test partition Table Job mode', V_start_time, Current timestamp,'SUCCESS',"',"');END;

3. Summary:

A.DB2 table partitioning currently supports only range partitions, without the richness of Oracle.

B. partition cannot be deleted directly must first DETACH PARTITION, after DETACH must commit or will report the imperfect structure error.

C. Need to add a new partition just add partition directly.

The D.DDL statement needs to show a commit in procedure.

DB2 partition Table Delete and add partitions