PDA

View Full Version : Cannot Import Tables outside of the current user



cruel_world
01-02-2007, 03:57 PM
When importing tables from outside the schema of the logged user, Metadata cannot import the table for use.

E.g When logged in as usera. MetaData allows you to see UserB.Table but appendes it as

Select * from UserA.Table

MattCasters
01-02-2007, 05:37 PM
What is the value of the schema name property for the imported table?

cruel_world
01-02-2007, 07:02 PM
Says...


Under Features?
use schema name to get table list? = false



How to change thisa to true?

MattCasters
01-03-2007, 05:14 AM
You should use "Import from explorer..." to import tables from a different schema.
In that case, the property "Target schema" will be imported as well.
However, you can also add this property manually in the Physical Table dialog.

Matt

cruel_world
01-03-2007, 12:36 PM
Using Import from Explorer does not work. Results below. I Added the Option Target Schema, with no luck ( DB2). Unfortunitly in this system users log into a schema and have to prefix every table statement with (User.Table). How to get the Schema Prefix?


Works great in Kettle! Any other Ideas?



Couldn't get field info from [SELECT * FROM SERVICE_EVENT_REFERENCE]
Location: exec query
DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2USER.SERVICE_EVENT_REFERENCE


be.ibridge.kettle.core.exception.KettleDatabaseException:
Couldn't get field info from [SELECT * FROM SERVICE_EVENT_REFERENCE]
Location: exec query
DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2USER.SERVICE_EVENT_REFERENCE

MattCasters
01-03-2007, 12:49 PM
You need to browse to the target schema of-course. I'll check if this still works tomorrow. I don't have a DB2 running, but it's pretty much the same on all schema-using databases like Oracle.

> How to get the Schema Prefix?

I hate to repeat myself, but it's in the Physical Table dialog, use the "Add property" button to add the "Target Schema" property.

Matt

cruel_world
01-03-2007, 01:06 PM
Thanks Matt. Let me provide more information for you.

Step
1. Create Connection. Connection is OK (DB2)
Loged in as DB2USER
2. Add Table from Explorer
Explorer Finds all tables in all schemas (Different users) but does not describe the various schemas.

( I connected to An Oracle Instance to see if there was a diference ) Oracle lists the schema and uses it in the table description ( DB2 does not ) This may be the bug

3. Select tablea.
tablea is actually in the schema "eomdata"
4. System Error Report
Couldn't get field info from [SELECT * FROM TABLEA]
Location: exec query
DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2USER.TABLEA.
5. Notice how DB2USER is added on to the table name above ( what we loged in as ) . when obveously when table is in eomdata (a different schema).

Now yes I can add a table by hand. and change the schema prefix by hand. as you suggest. but it has to be done 1 by 1.




Do we have an FTP for the latest on this yet?

cruel_world
01-03-2007, 02:07 PM
Found a solution. When using the generic connection and setting up the exact same info as in the DB2 setup it works. We get the schema information. and tables can now be added

Hope this is of use to you.

MattCasters
01-03-2007, 03:41 PM
Interestingly, using Concepts you can set the Target Schema information in a PhysicalTableConcept and have all physical table inherit that.
You should rarely have to do anything one-by-one in Pentaho Metadata :)