How to move a partitioned table IQ 15.4?

[former_member213461]

Hi

I need to move a partitioned table from one dbspace to other dbspace. I tried the follow syntax:

ALTER TABLE USER1.TABLE  MOVE TO  NEW_DBSPACE;

But, when I query the objects on original dbspace I still see some FP indexes of this table.

Obviously, I tried to move manually these FP indexes with follow syntax:

ALTER TABLE USER.TABLE1 ALTER COLUMN1 MOVE TO NEW_DBSPACE;

But, I Obtained a error message: "Could not execute statement. Table 'TABLE1' is already partitioned SQLCODE=-1251, ODBC 3 State "HY000"

Kindly suggest

Best regards

Jairo

[Gisung]

Hi,

A partitioned table should be moved to a new dbspace in a partition level.

Here are steps to move a partitioned table to new dbspace.

1. Move the table partition one by one to the target dbspace as below.
But, you also have to execute "ALTER TABLE ... MOVE TO ... " command in order to move metadata of the table to the specified dbspace.
Otherwise, the metadata of the table still exists in the original dbspace.
This will also move any user created indexes including HG indexes created by primary key or UNIQUE constraints.

(Example )

ALTER TABLE p_lineitem MOVE TO dsp2; commit;
ALTER TABLE p_lineitem MOVE PARTITION p1 TO dsp2; commit;
ALTER TABLE p_lineitem MOVE PARTITION p2 TO dsp2; commit;
ALTER TABLE p_lineitem MOVE PARTITION p3 TO dsp2; commit;
ALTER TABLE p_lineitem MOVE PARTITION p4 TO dsp2; commit;

2.  Check the usage of dbspaces. The partitoned table has been successfully moved to the new      dbspace.

1) SELECT Table_name, dbspace_id, isPartitioned FROM sp_iqtable('p_lineitem') ;
  Table_name     dbspace_id     isPartitioned
    p_lineitem        16388             Y

2) Select DbspaceName, Usage from sp_iqdbspace() ;
  DbspaceName   Usage
    DSP1               49
    DSP2               51

Please refer to associated KBA 2035031 for details.

https://i7p.wdf.sap.corp/sap/support/notes/2035031

==

Gi-Sung Jang

[former_member213461]

Thank you Gi-SUng

But my problem is when I try to delete the original dbspace (DSP1).

I followed the steps on KBQ 2035031, and the table seems has been moved to the other dbspcace:

Originally I had:

When I execute:

ALTER TABLE p_lineitem MOVE TO dsp2; commit;

ALTER TABLE p_lineitem MOVE PARTITION p1 TO dsp2; commit;

ALTER TABLE p_lineitem MOVE PARTITION p2 TO dsp2; commit;

ALTER TABLE p_lineitem MOVE PARTITION p3 TO dsp2; commit;

ALTER TABLE p_lineitem MOVE PARTITION p4 TO dsp2; commit;

I had this result:

The table seems has been moved, but when I try to delete the dbspace DSP1 I had this message:

At this point, if I query the contents on DSP1 with the follow sentence:

select t.table_name, i.index_name, d.dbspace_name

from sysindex i, sysdbspace d, systable t

where d.dbspace_id = i.dbspace_id

and t.table_id = i.table_id

and t.file_id > 0

and t.table_name='p_lineitem'

I can see, DSP1 still has objects related with table p_lineitem

How can I resolve this issue??

Thank you for your advise

[Gisung]

Hi,

Have ever tried to use a sp_iqemptyfile in order to drop the dbspace?

Here is an explanation about sp_iqemptyfile.

SyBooks Online

Please refer to the following example in order to drop/change the dbsapce.

SyBooks Online

==

Gi-Sung Jang