JDBC Lookup - Import table data from a different schema in same DB

[Former Member]

Hi XI Experts,

We are facing an issue while importing a Database table into the external definition in PI 7.1.

The details are as below:

I have configured user 'A' in PI communication channel to access the database. But the table that I want to access is present in schema "B". Due to this, I am unable to view the table that I have to import in the list available.

In other words, I am trying to access a table present in a different schema in the same database. Please note that my user has been given all the required permissions to access different schema. Even then, I am unable to access the table in different schema.

Kindly provide your valuable suggestions as to how I can import table which is present in another schema but in the same Database.

Regards,

Subbu

[Former Member]

Hai!

I have faced the same scenario and i got a sccessfull result.

Create a JDBC Receiver Channel and while Giving the connection detail give the User name and password for the Schema B.

Sure u can see the table list when you import the extenal definition.

If you not getting kimdly check whether the channel is active or stop channel and start it again.

Regard's

Preethi.

[Former Member]

Hi Preethi,

I sure do see after giving user id and password for Schema B, but as per the security team we are not supposed to use this Schema B user id and password.

Is there any other way that I can access the table defination?

Thanks,

Hetal Shah

[Former Member]

Hi,

If you are using DB tab as the category while creating the External Definition. The above is the only way i think the user id which is used in the JDBC Receiver channel should have the Authorization to access the Schema B. Otherwise if you have the XSD file for the table structure you can select the Category as xsd and you can import the same in the External Definition.

Regard's

Preethi.

[Former Member]

In JDBC channel configurations, how have you written the query parameter?

You need to write:

SELECT ***** FROM   <TABLE>.<SCHEMA>

[Former Member]

Hi,

This query is used to fetch data from the table. This is fine.

But, firstly I want to import the table structure from a different schema into PI so that I can map that field to the target structure.

When I look for the tables available with Database UserID, the required table is not available in the list as it belongs to different schema.

How do I access / import a table structure present in "A" schema into PI with a user which belongs to schema "B".

We have provided all the necessary permissions to the user in schema "B" to access schema "A".

Please suggest.

Thank you.

Regards,

Subbu

[baskar_gopalakrishnan2]

If you are using PI 7.1, then you can do JDBC Lookup to import JDBC meta data (table structures from DB). Configure a jdbc receiver communication channel where you specify username and password which has permission to access schema A and Schema B of database. Specify database name in the connection string. Then you might have access to import both schema.

Please refer these links

[Former Member]

I am also facing the same issue.

I tried giving database name into connection string, but it gives me error saying that wrong connection string.

Is there any other way to do this?

Thanks,

Hetal