cancel
Showing results for 
Search instead for 
Did you mean: 

partititon1,2,3 in group ODS,FAC status not change after data redistribute

Former Member
0 Kudos

Hello,

first thanks for your help. By now the db2 data redistribute has been sucessful. but another issue occurs , could you give me some suggestion?

1. run Tcode dbacockpit-->db2 for linux, unix, and windows database > configuration>database partiton group

i found the stutus in db partion group NGRP_FAC_AB7 and NGRP_ODS_AB7 and partion 1,2.3. is partion not in partitioning map containers created. I know if we want to change the status from this to partion in partioning map status , we should do a redistribute.

i have made a redistribute action like this

1. double click what i want to distribute

2. select the relevant partition , for example partition 0001

3 click the execute immediatelly button .

when i finished , the status is not changed . so i change the redistribute log located in /db2/db2ab7/sqllib/redist/ AB7.NGRP_FACT_AB7.2008022808582 .

the log message is

Data Redistribution cannot be continued.

Error: Redistribution failed with SQLCODE=-6047 (rc=-2145779603).

the SQL 6047 detail information is like the following. I don't know what happens.

it's a SAP bug just like the previous issue ? Could you give me some suggestion ? Thanks

db2 => ? sql6047

SQL6047N Database partition group cannot be redistributed

because table "<name>" does not have a partitioning

key.

Explanation:

At least one table in the single-node database partition does not

have a partitioning key. All tables in the single-node database

must have a partitioning key before the database partition group

can be redistributed to a multiple-node database partition

group.

The operation was not performed.

User Response:

Use the ALTER TABLE command to specify partitioning keys for

tables that do not have one. Then try the request again

Accepted Solutions (0)

Answers (1)

Answers (1)

malte_schuenemann
Active Participant
0 Kudos

To distribute a table across multiplepartitions, it must be created with a partitioning key. The key helps the database to decide on which partition a particular row is to be inserted, or looked for. SAP creates fact and ods data with partitioning key.

You cannot add a partitioning key after table creation. For this, the table would have to be re-created. I guess that the online table move described in SAP note # 1039544 could be used for this.

Malte

0 Kudos

Before redistribution, do your tables reside on only one partition? Does the error message you get list the table or tables that do not have a partitioning key?

All fact tables, ODS tables and PSA tables are created with a partitioning key in a SAP BI system. However, in the FACT and ODS tablespace, there are a few BI management tables (name starting with 'RS') that do not get a partitioning key by default if created in a tablespace on only one partition. The BI data distribution wizard in the DBA Cockpit takes care of some of these tables, for example RSDMFACTAB and RSDMODSTAB. Maybe there are some additional ones.

Please list the names of the tables without partitioning key. If the error message does not contain them, you can get them with the following SQL statement:

select varchar(tabname,30) 
from syscat.tables a 
where tbspace='<your_tablespace>' 
  and not exists (select partkeyseq from syscat.columns b 
                         where a.tabschema=b.tabschema and a.tabname=b.tabname 
                                  and b.partkeyseq > 0)

Brigitte