Problem with Pushdown Optimization

dataservices job design
1

Hi There!

I’m trying a very simple scenario.

SourceSchema in Database DB has table_source
TargetSchema in Database DB has table_target

Both table_source and table_target have the same definition.

I’m trying to move the data from table_source to table_target.

Since DI is quite intelligent, it’s doing a pushdown to the target and generates a beautiful SQL which has both the schemas in it.

But in reality, the two schemas have no privileges to access each other. When I run the job, obviously it fails (since it can’t execute the pushdown sql).

How do I disable the pushdown optimization feature and let the ETL job server actually do the data transfer instead of executing the sql at a database level.

I don’t like to grant select on both the schemas which is very primitive way of doing things.

Hope I made my point. Please suggest. Thanks!

vsantoshkumar :uk: (BOB member since 2008-06-02)

2

Have you tried placing the source and target schemas in seperate datastores?

This will trick BODI into thinking they are in seperate databases and therefore shouldn’t pushdown the whole dataflow.

Which DB are you using? Sounds like Oracle?

bungy :us: (BOB member since 2007-10-04)

3

add a merge transform before the target table, this will prevent the pushdown to the database

something like below
source -> Query->Merge->target

manoj_d (BOB member since 2009-01-02)

4

Bungy, I already tried that way…using different Oracle datastores but it’s far too intelligent. Still it does a pushdown to the database.

I like the Informatica way. It gives you the flexibility but I know DI is better in some areas. Well, I need best of both the worlds :slight_smile:

Manoj, your ideas works like a charm but that would involve changing many jobs. I was expecting if there’s a flag to disable pushdown optimization.

Do you guys have any more ideas? Do people always grant privileges to the target to access the source tables. Doesn’t that look like a bad approach?

vsantoshkumar :uk: (BOB member since 2008-06-02)

5

Which DI sub-forum should this post be moved to?

Nick Daniels :uk: (BOB member since 2002-08-15)

6

Thinking out loud - What about setting the target option to Bulk Load? Does it help?

tijimathew :us: (BOB member since 2006-08-24)

7

Got a reply from Werner on this,

In DS 3.2, the “use database links” property of a dataflow if disabled, it does not do “implicit DB links”. Another solution is to create two TNSNAMEs aliases in Oracle SQL*Net for the same database and each datastore is using one alias. DS has no insight that these two connection names actually point to the same database.

Since we’re using a version less than 3.2, the second solution is useful.

I don’t think the bulk load option will work in all scenarios.

vsantoshkumar :uk: (BOB member since 2008-06-02)