Hi All,
I am getting the below error while i am running a job for transfering data from a source to target. This is a simple data transfer. One source,one target and a querry transform.
I am using
Database Oracle 10g
Data services XI 3.0
The Job is working if i am loadin exernal files to target in Oracle. If I am transferring data from one schema to another schema , getting the below error.
Can anobody help me?
ERROR MESSAGE
3564 2632 DBS-070300|Dataflow df3demo|Reader Query
3564 2632 DBS-070300 SQL submitted to Oracle Server resulted in error <ORA-00942: table or view does not exist
3564 2632 DBS-070300 The SQL submitted is <INSERT /*+ APPEND / INTO “T”.“T_CUST_DIM” ( “CUSTOMER_ID” , “CUST_CLASSF” , “NAME1” , “ADDRESS” ,
3564 2632 DBS-070300 “CITY” , “REGION_ID” , “ZIP” )
3564 2632 DBS-070300 SELECT “CUSTOMER”.“CUSTOMER_ID” CUSTOMER_ID , “CUSTOMER”.“CUST_CLASSF” CUST_CLASSF , “CUSTOMER”.“NAME1” NAME1 ,
3564 2632 DBS-070300 “CUSTOMER”.“ADDRESS” ADDRESS , “CUSTOMER”.“CITY” CITY , “CUSTOMER”.“REGION_ID” REGION_ID , “CUSTOMER”.“ZIP” ZIP
3564 2632 DBS-070300 FROM “S”.“CUSTOMER” “CUSTOMER”
3564 2632 DBS-070300 >.
3076 3296 DBS-070300 |Dataflow df3demo|Reader Query
3076 3296 DBS-070300 SQL submitted to Oracle Server resulted in error <ORA-00942: table or view does not exist
3076 3296 DBS-070300 >. The SQL submitted is <INSERT /+ APPEND */ INTO “T”.“T_CUST_DIM” ( “CUSTOMER_ID” , “CUST_CLASSF” , “NAME1” , “ADDRESS” ,
3076 3296 DBS-070300 “CITY” , “REGION_ID” , “ZIP” )
3076 3296 DBS-070300 SELECT “CUSTOMER”.“CUSTOMER_ID” CUSTOMER_ID , “CUSTOMER”.“CUST_CLASSF” CUST_CLASSF , “CUSTOMER”.“NAME1” NAME1 ,
3076 3296 DBS-070300 “CUSTOMER”.“ADDRESS” ADDRESS , “CUSTOMER”.“CITY” CITY , “CUSTOMER”.“REGION_ID” REGION_ID , “CUSTOMER”.“ZIP” ZIP
3076 3296 DBS-070300 FROM “S”.“CUSTOMER” “CUSTOMER”
3076 3296 DBS-070300 >.
DI is generating a complete pushdown for the load. DI will not validate if objects of schema B can be accessed by objecs of schema A
to make sure its a permission issue, add a Merge transform before target table, this will prevent DI from generating a complete pushdown, instead it will read data from source table and load data to the target table, if that works, then if you want to take advantage of complete pushdown then give permission to scheam A to access objects of schema B
Hi Manoj
I think I have to explain more about the scenario,
I am a fresher in DI. I was practising the tutorials only. After the fresh installation I have created the same job ( simple source to target) and it worked without any failure. After that I have created some jobs with external files. Again I created one job for transferring data from Source to target, cased the given error. The company’s database policy will not allow to give access on source tables for the target schema.
The transfer will work if I am transferring data from Source to Source and Target to Target. When I am transferring across the schema, the job ended in error.
And I don’t understand the use of merge here. I am picking from one table and transferring to one table.
DI engine is trying to optimize the database operation, and generating a INSERT … SELECT pushdown SQL, since this will give better performance compared to first reading data and then loading to another table
the complete pushdown may be generated in following scenario
both source and target are in same schema
there is a db link between source and target
the source and target can be implicitly linked
In your case since both source and target schema are in same database, DI is generating a complete pushdown. If you add a Merge transform it will prevent the complete pushdown (since Merge will be evaluated by DI, so it has to load the data). If you are not familiar with Merge, then try the following to preven the pushdown
select the dataflow, right click and open the properties of DF
there will be a check box Use Implicit database link, which is checked by default, uncheck that
…and in DataServices 3.2 you can uncheck the dataflow property “use database links” with the same effect of not getting a pushdown when different datastores for the same database as used.
The reason why we still do a pushdown is because usually you use one datastore per database, hence the one user of the datastore has permissions for the tables of both owners.
Hence from a performance point of view the best thing would be to leave all as is and execute the command
grant select on S.CUSTOMER to T;
in SQLPlus once. Then the user T of the target datastore will have the permission to read the table S.CUSTOMER.
I have tried with uncheked use database link check box. it failed with the same message.
I have tried after giving select access for the source table to the target schema, it worked. But i don’t want to do this and I want to stop the pushdown SQL.
can you give a brief about merge transform ( note that one source table and one target table)
If I am giving grants, it will work. But I want to stop the pushdown SQL.
How can I do this?
and another thing
Instead of Grant select on s.customer to T , can I use Grant select on T.cust_dim to s. If not What is the reason?
A full pushdown uses the datastore of the target table and therefor it does not matter what grants the source user has.
Why don’t you like the full pushdown? Anyway, as Manoj said, in your version the easiest will probably be to add a map transform. It does not do anything but prevents a full pushdown.
Full pushdown will work only if the taget schema can access the source table. Our database policy will not allow this. Anyway, after adding a merge transform, it worked fine.
(BOB member since 2004-12-17)