This discussion is archived
1 Reply Latest reply: Jul 27, 2012 6:54 AM by ttiede RSS

Partition name length problem at DML in Table operator

951379 Newbie
Currently Being Moderated
Hello experts

In our datawarehouse we have a table with a high number of partitions (each partition corresponding to a different business object) for solving an issue with data quality. Every time the data quality mapping is run, a truncate on the corresponding partition is performed. But Insert operations on that table are not set on a particular partition and, therefore, lock the table and have a conflict with the truncates if parallel operations are run.

We would like to used the Partition DML functionality at the Table Operator in order to insert just into one partition and prevent locking all of them. Although none of the partition names are longer than 30 characters, OWB (even at the GUI) adds the string "partition: " in front of each name, which causes the length limit to be surpassed.

Any idea about how could we use this functionality without renaming the partitions?
  • 1. Re: Partition name length problem at DML in Table operator
    ttiede Newbie
    Currently Being Moderated
    Hey there,
    I'm a colleague of the thread starter and like to jump in as this is an issue for us which we need to solve.
    Some more infos which might help:

    We are running the OWB 11.2.0.3.0 on Windows 32 Bit
    We have the following patches installed:
    13257502
    13473205

    None of the later patches has been installed, since none of the Bugs that were fixed affected us.

    In a Mapping on a table-Operator we'd like to use "PARTITION DML" for an Insert.
    On the GUI when selecting the Partition the data should be inserted to (Field: DML Partiton Name), we get the following error (in this instance the partition name is 24 characters long)

    "API0407: The minimum length of this filed is 0 and its maximum length is 32. You have 35 characters."

    In the Drop-down Menu of DML Partition Name it says "Partition: " in front of every partition there is. Together with a Partition name of 24 Characters it sums up to 35 characters.
    So it appears that the maximum length of the partition is limited to 21 characters only because "Partition: " is put in front of it.

    This error also appears when editing the Properties of the table operator by OMB*PLUS (from tcl) with

    OMBALTER MAPPING 'mapname' MODIFY OPERATOR 'operatorname' SET PROPERTIES (DML_PARTITION_NAME, DML_PARTITION_TYPE, PARTITION_KEY_VALUE_LIST, IS_PARTITION_INDEXED_BY_NAME) VALUES ('$partition', 'PARTITION', '$someEmptyList', 'true')

    where it does not make a difference whether $partition is of the form
    set partition "Partition: SOMEPARTITIONNAME"
    or
    set partition "SOMEPARTITIONNAME"

    Renaming the Partitions is not an option for us, since our warehouse is running in multiple productive systems and became quite large in the last years.

    Is it necessary, that "Partition: " is put infront of the partition name? Is there a way around this? Isn't the field DML_PARTITION_TYPE supposed to store the Information whether it is a partion or a subpartition?
    If you wanted to use subpartitions, does the name of the subpartition then need to be even shorter since "Subpartiton: " is put infront?

    Thanks in advance for any help or suggestions. If you need further Information, feel free to ask, I am more than happy to supply it.

    Best Regards

    Thorsten

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points