This discussion is archived
6 Replies Latest reply: Nov 8, 2012 6:23 AM by jaap van Dijk RSS

What criteria does an UPDATE mapping have to satisfy to be SET_BASED?

jaap van Dijk Newbie
Currently Being Moderated
Hi,

We are migrating from OWB 10.1 to OWB 11.2. OWB 10.1 generates an UPDATE mapping as ROW_BASED, but in the OWB Data Modeling, ETL, and Data Quality Guide 11.2 (E10935-02) it says:

"Warehouse Builder supports UPDATE loading in set-based mode only with Oracle Database is 10g or later."

So we installed the latest OWB 11.2 client and server configuration, with the server a 10gR2 database, and designed the simplest of mappings:

- a table TAB1, with columns C1 (pk) and C2.
- a table TAB2, a design copy of TAB1
- a mapping in which TAB2 is updated with TAB1, C1 of TAB2 match when update = yes, and C2 load when update = yes

When I tried to deploy this mapping in SET_BASED mode, I got the error:

"VLD-1142: This mapping cannot be generated with generation mode SET_BASED. PL/SQL set based operating mode: Operator TAB2 does not support SQL generation."

What is preventing this mapping from being deployed SET_BASED?

Regards,
Jaap.
  • 1. Re: What criteria does an UPDATE mapping have to satisfy to be SET_BASED?
    Peter Verhaegen Newbie
    Currently Being Moderated
    Dear Jaap

    Owb 11.2 is only certified to run in 11.2. It comes together with The db software on the server.
    Probably, your problem is situated somewhere there

    Regards

    Peter
  • 2. Re: What criteria does an UPDATE mapping have to satisfy to be SET_BASED?
    jaap van Dijk Newbie
    Currently Being Moderated
    Hi Peter,

    OWB 11.2 is also certified for Oracle 10g (which is also indicated by my quote from the manual, that mentions 10g), Oracle provides a stand-alone version of OWB 11.2 for this purpose. Besides, row-based update-mappings are already a feature in OWB 10.1.

    So my question remains.

    Regards,
    Jaap.
  • 3. Re: What criteria does an UPDATE mapping have to satisfy to be SET_BASED?
    Peter Verhaegen Newbie
    Currently Being Moderated
    Jaap,

    sorry to disagree, it is certified to use it as source, not as target and repository
    look at

    http://www.oracle.com/technetwork/developer-tools/warehouse/overview/certification/index.html

    it clearly says that 11.2 is only certified to be used in a 11.2

    Peter
  • 4. Re: What criteria does an UPDATE mapping have to satisfy to be SET_BASED?
    jaap van Dijk Newbie
    Currently Being Moderated
    Peter,

    We're getting off-topic here, but allright. I read the page you referred to, and I can't find the text that supports you assertion. It says among others

    "Compatibility with Oracle Database
    (...)
    Database Release Compatible Warehouse Builder Releases
    (...)
    DB10gR2 Warehouse Builder 10.2, 11.1, and 11.2
    (...)
    Enterprise Edition Certified for use of all OWB functionality including additional cost licenses" [this is our database version]

    For me this implies that OWB 11.2 can be used on a 10.2 database.What other part of the text says or implies that OWB 11.2 is only certified for database 11.2?
    Furthermore, In the "Oracle® Warehouse Builder Installation and Administration Guide 11g Release 2 (11.2) for Windows and Linux", chapter "Working with the Standalone Install Package" it reads:

    "There are several situations when you must download and install the
    OWB 11g Release 2 Standalone Install Package:
    ■ You are installing OWB 11.2 and hosting an OWB Repository on one of:
    Oracle Database 10g Release 1, Oracle Database 10g Release 2, or
    Oracle Database 11g Release 1
    ..."

    To get on-topic again: have you ever succeeded in generating an SET_BASED UPDATE-mapping on a configuration that is, to your opinion, certified?

    Regards,
    Jaap.
  • 5. Re: What criteria does an UPDATE mapping have to satisfy to be SET_BASED?
    Peter Verhaegen Newbie
    Currently Being Moderated
    Jaap,

    to get on topic again, I use set-based mappings exclusively because row-based is too slow

    all simple mappings like you have described should be set-based per default unless you have specified in the configuration of the mapping that it should generate something else.

    And I can only stress the fact that you should not try to run OWB 11.2 in another DB than 11.2.

    sorry if that can not help you solving the issue

    regards

    Peter
  • 6. Re: What criteria does an UPDATE mapping have to satisfy to be SET_BASED?
    jaap van Dijk Newbie
    Currently Being Moderated
    I found the limiting factor. As mentioned before we are migrating from a previous version. Thios version deployed in a 9i database. To enable OWB 11gR2 to generate a SET_BASED mapping the module configuration property 'PL/SQL Generation Mode' has to be set to 'Oracle 10gR1' or higher. It was 'Oracle 9i', preventing OWB from generating the mapping SET_BASED. When I changed the property to 'ORAcle 10gR2' the mapping genreated SET_BASED without a problem.

    Regards,
    Jaap.

Legend

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