MVSFORUMS.com

abhayasahoo · Beginner Joined: 04 Dec 2002 Posts: 8 Topics: 6

Is it possible for one DB2 program to access tables from two different sub-systems ?

Manas Biswal · Posted: Wed Dec 04, 2002 4:03 am Post subject:

I really don't think you can do so. Whenever we execute any COBOL-DB2 program, we specify the subsystem in SYSTSIN of IKJEFT01.
I would really be interested to know if there is any way of really accessing two tables from two different subsystems.

Regards,
Manas

kolusu · Posted: Wed Dec 04, 2002 4:22 am Post subject:

abhayasahoo,

Your shop must be setup to access tables between subsystems.The DBA enables it by via DB2's Distributed Request Data Access architecture.There are settings in the DB2 Catalog which allow this feature. SYSIBM.LOCATIONS table will have information for every accessible remote server.

You can try this sql.

raj051076 · Beginner Joined: 05 Dec 2002 Posts: 64 Topics: 21

I think it can be done if DDF is operational and you have an alias defined for your remote table.
_________________
Rajib

Rajeev_jha · Beginner Joined: 20 Dec 2002 Posts: 5 Topics: 0

Yes you can do this. You need to know the location names for the tables. Suppose TABLE A exits is the location TISDB2P and TABLEB exists in TISDB2N then you can refer to the tables as

TISDB2P.ownername.TABLEA

and

TISDB2N.ownername.TABLEB

Manas Biswal · Posted: Fri Dec 20, 2002 5:57 am Post subject:

Hi Kolusu and Rajiv,

I have a small doubt in this context. Let us suppose that we have an application program that has both local and remote queries. While running the program, we give the local DB2 subsystem name. Now, we give a location name alongwith the owner and table name while referencing the remote DB2 subsystem. The location has to be present in SYSIBM.LOCATIONS table and corresponding entries also in SYSIBM.LUNAMES and SYSIBM.IPNAMES tables.(Thanks to Kolusu for the link). That all is quite understandable.
My question is where will the query actually run?. I mean in which DB2 subsystem's address space, will the query run?. It cannot be totally on the local DB2 subsystem as the table exists on the remote subsystem and it cannot be totally on the remote subsystem as the query has to be resolved locally to know that it is accessing a remote system and the exact location of the remote system has to be retrieved from the catalog tables(LOCATION,LUNAMES AND IPNAMES). So, will there be a partial running of the query at both the local as well as remote subsystems?

Regards,
Manas

kolusu · Posted: Fri Dec 20, 2002 6:20 am Post subject:

Manas Biswal,

Check the following link which will answer your queries.

Introduction to accessing distributed data

You can read this to get an overall view about accessing distributed data

Hope this helps...

cheers

kolusu

Manas Biswal · Posted: Fri Dec 20, 2002 7:00 am Post subject:

Thanks a lot Kolusu. Now, it seems quite logical. Basically, the IBM manual whose link you had provided says that you have to bind your program at both the local and remote subsystems. That is what I was hinting at. Basically, you need to have the SQL executable at both the local and remote subsystems. The rest of it is quite logical.

Regards,
Manas

raj051076 · Beginner Joined: 05 Dec 2002 Posts: 64 Topics: 21

Manas

In our shop we have lot of remote databases. To access those databases what we do is we make sure DDF is installed and running. If DDF is stopped we start it with the command START DDF. Also while bringing up DB2 ,DDF automatically starts and one more thing we consider is whether TCPIP is started before or after DB2 starts. TCPIP should come up first and then DB2.

We access the remote tables by creating aliases as follows:
CREATE ALIAS <ALAISNAME> FOR LOCATION.DATABASE.TABLE

I am not 100% sure of the syntax. I will have to check.

Thanks
_________________
Rajib

Manas Biswal · Posted: Sun Dec 22, 2002 2:25 am Post subject:

Hi Rajib,

Thats all fine. But what I was asking was not how to access the table(Through an alias - as you explained). What I was worried about was the query. How and Where will it actually run and where is it present(In the local or remote system). Kolusu's links provided the answer - We have to basically bind the SQLs in both the local and remote systems. You can go through Kolusu's links for more details.

Regards,
Manas

Nila · Posted: Fri Dec 27, 2002 6:52 am Post subject:

Hi
The points given are really worthful. I want to add more and elaborate.

Assume we have an existing plan in which we have to include the package list in which the Remote location DBRM is bound.

the steps followed are:
1.Choose a name for collection to contain all packages in the plan, say PACK1.
2.At remote location we have to execute

anjanaachan · Beginner Joined: 26 Dec 2002 Posts: 3 Topics: 0

8)
Just one more point in this context.
Whatever was mentioned above was in the context of programs and binds.
Regarding Manas's initial query, it is possible to access remote DB2 subsystems froma local system if u have the reqd access.
For this u need to get more access than the regular select access(as this is a part of DDF). For this , u need ur DBA to GRANT u access AT ALL LOCATIONS.

Then u need not logon to every production site separately if you have tables all over the place in a network that supports DDF.

Manas Biswal · Posted: Mon Dec 30, 2002 2:51 pm Post subject:

Hi Nila,

You have basically segregated your packages into local and remote packages. What if a single application program contains queries for both the local and remote system. In such a case, what do we do?. I suppose, we basically have to bind the DBRM both at the local and remote subsystems. I did not have the time to go through Kolusu's links in detail but that is what they basically say - we do have to bind the DBRM at both the local as well as remote DB2 subsystem.

Regards.
Manas