Using SQL Server 2008 Linked Server to Access DB2 z/OS V8

Larry D'Agostino

Using SQL Server 2008 Linked Server to Access DB2 z/OS V8
I have a client who is proposing, as an option, to use SQL Server 2008 Linked Server option to issue 'queries' against DB2 order and invoice data on a z/OS V1.10 platform. I have been trying to research the subject. Most of the discussion relating the Lined Server is on the Microsoft side and the general tone is "can do", obviously. I also searched this site and found a few hits, suggesting the connection is practicable. Order and invoice data reside in multiple tables (which would have to be joined -join keys and indexes exist to do this). The largest tables are just under 300 M rows.

First let me relate that the customer runs their z/OS box flat out .. that is they are running it consistently close to, or even above, 100% utilization during a 24-hr period. At most, there are maybe 5 - 10 MIPs, which could be redirected to this requirement. And they are reluctant to increase the size of the box, because they don't want to pay for more MIPs. Accordingly, I have concerns and questions and would like some feedback from anyone out there with some background:

1) It appears the remote 'user' in this link requires SYSDAM authority to access DB2 catalog for required processing. Is this correct ?

2) It is not clear whether the customer's intent is to issue queries specific to single customer/single order/invoice, i.e., bringing back small amount of data, or they want to issue a large query against our tables to bring back data for all orders, which have changed since last pull. Either way, my concern is for the optimization of these queries on DB2. Since they aren't statically bound, I am not sure what to expect.

3) Do I have to link, from SQL Server, to DB2 z/OS via a DB2 Connect ? If it is not required, what would be the justification for routing or not routing through DB2 Connect ?

4) It appears the Linked Server interface can call a DB2 stored procedure, as opposed to issuing queries. I can imagine the benefit here would be my stored procedures can be statically bound, providing better performance, and better controlled as objects. Not cetain as to how passing of parameters would work. Any other thoughts ?

5) Any recommendations or references available ?


Thanks,


Larry

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: Using SQL Server 2008 Linked Server to Access DB2 z/OS V8
(in response to Larry D'Agostino)
Why in the world does the remote user require SYSADM authority? Granting
specific read access or as many shops have it - grant public should be more than
enough.

If they need to create dynamically temp tables, then just granting the create
authority for that authid should be enough. SYSADM is very powerful and way
more than most remote users ever need.

And why can't you control the amount of CPU used via DDF and WLM? Certainly, you
can give the remote user a "TSO" period like performance criteria such that
quick hit queries get good CPU response and larger more complex or less
optimized get much lower priority and can have the priority drop as the query
uses more resources. This is pretty simple to do. In addition, if you are
using DB2 Connect, then there is a way to capture the queries and "bind them"
statically. It's a bit cumbersome, but could be very useful. But the prepare
overhead for simple queries using Dynamic caching may not be that high, I
suspect that the auth checking will be much higher overhead than the prepare
time.

Just my opinion for what it's worth.

Myron



________________________________
From: Larry D'Agostino <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 29, 2010 11:50:31 AM
Subject: [DB2-L] Using SQL Server 2008 Linked Server to Access DB2 z/OS V8

I have a client who is proposing, as an option, to use SQL Server 2008 Linked
Server option to issue 'queries' against DB2 order and invoice data on a z/OS
V1.10 platform. I have been trying to research the subject. Most of the
discussion relating the Lined Server is on the Microsoft side and the general
tone is "can do", obviously. I also searched this site and found a few hits,
suggesting the connection is practicable. Order and invoice data reside in
multiple tables (which would have to be joined -join keys and indexes exist to
do this). The largest tables are just under 300 M rows.

First let me relate that the customer runs their z/OS box flat out .. that is
they are running it consistently close to, or even above, 100% utilization
during a 24-hr period. At most, there are maybe 5 - 10 MIPs, which could be
redirected to this requirement. And they are reluctant to increase the size of
the box, because they don't want to pay for more MIPs. Accordingly, I have
concerns and questions and would like some feedback from anyone out there with
some background:

1) It appears the remote 'user' in this link requires SYSDAM authority to access
DB2 catalog for required processing. Is this correct ?

2) It is not clear whether the customer's intent is to issue queries specific to
single customer/single order/invoice, i.e., bringing back small amount of data,
or they want to issue a large query against our tables to bring back data for
all orders, which have changed since last pull. Either way, my concern is for
the optimization of these queries on DB2. Since they aren't statically bound, I
am not sure what to expect.


3) Do I have to link, from SQL Server, to DB2 z/OS via a DB2 Connect ? If it is
not required, what would be the justification for routing or not routing through
DB2 Connect ?

4) It appears the Linked Server interface can call a DB2 stored procedure, as
opposed to issuing queries. I can imagine the benefit here would be my stored
procedures can be statically bound, providing better performance, and better
controlled as objects. Not cetain as to how passing of parameters would work.
Any other thoughts ?

5) Any recommendations or references available ?


Thanks,


Larry

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA
*
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the
home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv