IBM Red Brick attach segment requires moving existing data

Technote (FAQ)


Question

How do you avoid "** ERROR ** (859) The attach or change range would require movement of data from segment"?

Cause

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.

Answer

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

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Red Brick Warehouse

Software version:

6.3

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1470988

Modified date:

2012-04-11

Translate my page

Machine Translation

Content navigation