Creating Cross Connection Queries

Toad Data Point
1

Hello Toad World,

I’m trying to create a cross-connection query between two dissimilar databases. I realize that I couldn’t create a query using a DB2 connection, so I connect via ODBC and MS Access database. However, now I’m getting a error message “Can’t address object” from the table pointing from the DB2/ODBC table. Can someone please suggest how to fix or point me in the direction where I can get some information on how to 1). How to properly configure cross-connection queries 2).How to trouble shoot the error message that I’m seeing! Thanks

2

To Jazz Kenney:

Thank you for posting in Toad World!

I encourage using the Query Builder to create queries as our Cross Queries utilize special syntax. The Query Builder is a great resource to augment the Cross Query experience and usually resolves most issues with configuring Cross Queries.

The team will require a little bit more information with the “Can’t address object” error, especially if you are able to reproduce this using the Cross Query feature. Let us know:

  • Which version of TDP are you using?
  • The DB2 platform you’re utilizing (Z/OS or LUW)?
  • A screenshot of your “Can’t address object” error.

Thanks!

-Joshua Liong

3

Joshua,

I’m using TDA 3.0 ( Toad for data analyst) and TDP 3.3 (Data Point). The DB2 platform LUW.

4

I was looking more for a error definition- what exactly does “Can’t address object” means?

5

I’'ve established two ODBC connections, because I know I couldn’t connect to the DB2 client itself.

6

It is no longer necessary to use an ODBC connection for DB2 in cross-query. Please retry with using a native connection to LUW or Z/OS. (I assume you are connecting to one of those and not iSeries)

The “can’t address object” can be caused by different things so I can’t give you a straight answer on that.

Are you using Base XQuery? I suspect you are. We are no longer supporting XQuery in the Base edition starting with TDP 3.4, which was just released. I would recommend downloading a trial version of TDP 3.4 and that XQuery there. The XQuery in the pro edition is far superior to what is in base.

7

I received an error message stating that you cannot create a heterogeneous query that contains objects from a DB2 client connection. As a workaround, create an ODBC connection using a DSN to the DB2 database and select the objects from that connection.

This is getting frustrating- I’ve created a table in the Toad Sample Access database and have also created and DB2/ODBC connection- in hopes of creating an cross connection query. Does it matter the view of the object? currently the objects are being shown as views vs tables in the ODBC connection.

8

With the TDP- I’m trying to establish a cross connection with a DB2/ODBC and SQL Server (Databshare), but I’m getting this error message. “ODBC too few Prameters”.

9

This usually means that you are using a function in your query that exists in both DB2/SQL Server and mySQL but they have different input parameters. Can you post your SQL. I might be able to see which one is causing the problem.

10

I am having the same problem and I am using query builder. My servers are both SQL…

Can’t address object `Server1. server2’ from cross query engine

11

Could you post your query here?

Thanks

Aleksey

12

Here is the query SAMPLE…

SELECT MED_LOON_INFO.LOON_NUM

, MED_LOON_INFO.LOON_MED_STATUS

, MED_LOON_INFO.REFERAL_DATE

, HEARING_SCHD.PERS_APP

, CHMST.NAME1

, CHMST.NAME2

, CHMST.ADDRS

, CHMST.CTYST

, CHMST.ZIP

, HHMST.NAME1

, HHMST.NAME2

, HHMST.ADDRS

, HHMST.CTYST

, HHMST.ZIP

FROM

((SERVER NAME).TRACKING.dbo.HEARING_SCHD HEARING_SCHD

INNER JOIN

SERVER NAME1).TRACKING.dbo.MED_LOON_INFO MED_LOON_INFO

ON (CVAR(HEARING_SCHD.LOON_NUM) = CVAR(MED_LOON_INFO.LOON_NUM)))

INNER JOIN

SEVER NAME2.MR.CHMST CHMST

ON (CVAR(CHMST.ACCT#) = CVAR(MED_LOON_INFO.LOON_NUM)))

INNER JOIN

SERVER NAME2.dbCOORiSeries.MR.HHMST HHMST

ON (CVAR(HHMST.ACCT#) = CVAR(MED_LOON_INFO.LOON_NUM))

WHERE (MED_LOON_INFO.LOON_MED_STATUS = 1)

13

I assume that you manually replace the connection names.

I don’t see anything wrong with the query.

What TDP version do you use?

Could you post the exact message you get?

Thanks

Aleksey

14

You are using the Base Cross-Connection utility which we no longer offer because it was no limited in use. Post the error so we can see. But you would have much better time of using Cross-Connection in the Pro version.

15

Even i get the same error “Can’t address object from cross connection query” . does anyone have resolution for this?

16

Every issue is unique, even if you are seeing the same error. Can I ask a few questions?

  1. Are you performing a Cross-Connection query in the Base edition or Pro. (We no longer support this in Base)

  2. What version of TDP are you using?

The easiest way to debug this is to install the Beta or TDP 3.7. Then build the query, product he error and then generate a support bundle. In these latest versions we parse the SQL and generate DDL for us to replicate your issue.