IBM Red Brick attach segment requires moving existing data

Red Brick segment range modification does not permit the movement of existing row data from one segment to another as a result of the change.

This example, on Windows, shows how to attach a new segment to the SALES table of the aroma database delivered with Red Brick. The ranges of the new segment require movement of existing row data; therefore this operation requires data EXPORT and LOAD into the new segment. The new segment range is (MIN:200). The current segmentation of the SALES table is:

...
data in ( DAILY_DATA1, DAILY_DATA2 )
segment by values of ( PERKEY )
ranges( MIN : 415, 415 : MAX )
...

Perform the following steps:

1. Make a backup of your current database.

2. Export the data in the first segment , DAILY_DATA1. Enter this SQL from RISQL:

  EXPORT TO 'c:\temp\sales' (SELECT * FROM SALES WHERE PERKEY < 415);

3. Bulk delete data from the first segment,. Enter this SQL from RISQL:

 ALTER SEGMENT  DAILY_DATA1 OF TABLE SALES CLEAR;

4. Create the new segment DAILY_DATA_NEW; enter this SQL from RISQL:

  CREATE SEGMENT DAILY_DATA_NEW
 storage 'DAILY_DATA_NEW_PSU_1' maxsize 1024 initsize 104 extendsize 104;

5. The SALES table only allows 2 segments. Increase the number of segments permitted. Enter this SQL from RISQL:

  ALTER TABLE SALES CHANGE MAXSEGMENTS TO 4;

6. Attach the new segment to the SALES table. Enter this SQL from RISQL:

  ALTER SEGMENT DAILY_DATA_NEW ATTACH TO TABLE SALES RANGE ( MIN:200);

7. Edit the c:temp\sales.TMU file generated by the EXPORT. Replace INSERT with APPEND and GENERATED_TABLE with SALES. The contents are:

Original content:

  LOAD DATA INPUTFILE
 'c:\temp\sales'
 RECORDLEN 83
 INSERT
 NLS_LOCALE 'English_UnitedStates.US-ASCII@Binary'
 INTO TABLE GENERATED_TABLE (
   PERKEY POSITION(2) INTEGER EXTERNAL(11) NULLIF(1)='%',
   CLASSKEY POSITION(14) INTEGER EXTERNAL(11) NULLIF(13)='%',
   PRODKEY POSITION(26) INTEGER EXTERNAL(11) NULLIF(25)='%',
   STOREKEY POSITION(38) INTEGER EXTERNAL(11) NULLIF(37)='%',
   PROMOKEY POSITION(50) INTEGER EXTERNAL(11) NULLIF(49)='%',
   QUANTITY POSITION(62) INTEGER EXTERNAL(11) NULLIF(61)='%',
   DOLLARS POSITION(74) DECIMAL EXTERNAL(9) NULLIF(73)='%');

Modified content:

  LOAD DATA INPUTFILE
 'c:\temp\sales'
 RECORDLEN 83
 APPEND
 NLS_LOCALE 'English_UnitedStates.US-ASCII@Binary'
 INTO TABLE SALES (
   PERKEY POSITION(2) INTEGER EXTERNAL(11) NULLIF(1)='%',
   CLASSKEY POSITION(14) INTEGER EXTERNAL(11) NULLIF(13)='%',
   PRODKEY POSITION(26) INTEGER EXTERNAL(11) NULLIF(25)='%',
   STOREKEY POSITION(38) INTEGER EXTERNAL(11) NULLIF(37)='%',
   PROMOKEY POSITION(50) INTEGER EXTERNAL(11) NULLIF(49)='%',
   QUANTITY POSITION(62) INTEGER EXTERNAL(11) NULLIF(61)='%',
   DOLLARS POSITION(74) DECIMAL EXTERNAL(9) NULLIF(73)='%');

8. Run the Table Management Utility (TMU) from the command line.

cd c:\temp
rb_ptmu -d aroma c:\temp\sales.TMU system manager