November 4, 2010 at 1:55 pm
Basic situation:
I run one complicated set of OPENQUERYs against DB2 and T-SQL queries against my DB to get one set of data. From that set of data, I select a group of ID's to go out and run another set of OPENQUERYs to get another, supporting set of data. Right now I'm doing this via creating a comma-delimited list in a string and creating the OPENQUERY string with that. This works, but it feels very kludgey and is a finite solution due to the nature of string variables.
In pseudo-code, I want to
SELECT data from DB2.Table where ID in (select ID from SQLServer.Table)
[or via inner join syntax...]
I can kind of sort of narrow down the data by doing joins within the DB2 data store, but nowhere near what I really need to do. (will narrow down to tens of thousands when I need hundreds)
Due to version/driver issues beyond my control, we have to use OPENQUERY to our DB2 data store.
Can anyone think of a brilliant way to do this?
November 4, 2010 at 2:02 pm
From what I understand (could easily be wrong), OpenQuery can't do anything a linked server connection can't do. It's just a little more ad hoc than those are. Have you tried using a linked server to the DB2 database? That would simplify the whole thing.
If not, can you add a work-table to the DB2 database that you could insert the values into with a prior OpenQuery command? I don't know what DB2 uses for temp tables (probably uses temp tables), but something like that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 2:10 pm
I have read-only access to the DB2 data store and that's not going to change so no hope of writing a temp table there.
Cannot use a standard/vanilla linked server query, unfortunately.
For reference, here's my query in all its glory:
DECLARE @sqlstr VARCHAR(max)
, @pcpstr VARCHAR(max)
SET @pcpstr = '';
select distinct RTRIM(me.PCP) as PCP
INTO #pcps
from dbo.member_extract me
select @pcpstr = @pcpstr + ',''''' + me.PCP + ''''''
FROM #pcps AS me
LEFT OUTER JOIN ADev.dbo.v_provider AS vp
ON me.PCP = vp.external_provider_id
WHERE
vp.external_provider_id IS NULL
AND
me.PCP is not null;
if @pcpstr is not null and LEN(@pcpstr) > 0
BEGIN
SELECT @pcpstr = SUBSTRING(@pcpstr, 2, LEN(@pcpstr));
SET @sqlstr = '
select distinct
''I'' as TYPCOD, prv.provno,
TRIM(prv.provno) as PVC,
prv.TITLCD as TITLE,
TRIM(prv.FSTNAM) as Name1,
TRIM(prv.LSTNAM) || '' '' || TRIM(prv.TITLCD) as Name2,
''Not For Mailing Use'' AS ADRLN1
from dw.PRVMAS prv
inner join dw.psamas psa
ON prv.provno = psa.provno
WHERE prv.TYPCOD = ''01''
and psa.expdat > date(current_timestamp - 18 MONTHS)
';
SELECT @sqlstr = REPLACE(@sqlstr, '''', '''''');
SELECT @sqlstr =
'SELECT TYPCOD,PROVNO,PVC,TITLE,NAME1,NAME2,ADRLN1 FROM OPENQUERY(DW, ''' + @sqlstr
+ 'and prv.provno IN (' + @pcpstr + ') ' + ''')';
EXEC ( @sqlstr
);
END
DROP TABLE #pcps
November 5, 2010 at 6:33 am
Can you pull partial data sets from the DB2 server easily?
Instead of one query with a string of "IN" values, open a cursor and select the values from the DB2 server per record in your local query, inserting into a local temp table. You end up with a lot of smaller, but well-filtered queries, instead of one massive query with issues because of string limits.
E.g.: If your "provno" values are 1, 3, and 7, you'd do "Insert into #Temp Select from OpenQuery (query Where provno = 1)", then the same with 3, then the same with 7.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2010 at 1:19 pm
Playing off your ideas and the things bouncing around in my head, I've vome up with the following which gets me what I want:
SELECToq.*
FROM OPENQUERY(DW,'
SELECTDISTINCT
''I'' as TYPCOD, prv.provno,
TRIM(prv.provno) as PVC,
prv.TITLCD as TITLE,
TRIM(prv.FSTNAM) as Name1,
TRIM(prv.LSTNAM) || '' '' || TRIM(prv.TITLCD) as Name2,
''Not For Mailing Use'' AS ADRLN1,
e.PROVNO as PCP
FROM dw.ENPMAS e
INNER JOIN dw.PRVMAS prv ON e.PROVNO = prv.provno
INNER JOIN dw.psamas psa ON prv.provno = psa.provno
WHERE
prv.TYPCOD = ''01''
AND (e.EXPDAT > e.EFFDAT OR e.EXPDAT IS NULL)
AND (psa.EXPDAT > psa.EFFDAT OR psa.EXPDAT IS NULL)
AND (e.EXPDAT > date(current_timestamp) or e.EXPDAT is null)
AND (psa.EXPDAT > date(current_timestamp - 18 MONTHS) OR psa.EXPDAT IS NULL)
') oq
LEFT OUTER JOIN dbo.v_provider AS vp
ON oq.PVC = vp.external_provider_id
WHERE vp.external_provider_id IS NULL
I tested out with inserting the DW query into a temp table and joining on that and it near-comparable time-wise but just a hair slower. This gets it all into one pretty set-based query.
Thanks for helping me think this through.
November 5, 2010 at 1:40 pm
Cool biz.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply