Allowing access to IQ tables from ASE is indeed a powerful option, as it lets you take advantage
of the various features of both platforms.
Here's how to get started:
-- as SA on the ASE server
sp_addserver 'PROD_IQ', 'ASIQ', 'PROD_IQ'
go
sp_configure 'cis rpc handling',1
go
-- as the DB User, on the ASE system
exec sp_addexternlogin 'PROD_IQ', 'jsmith', 'report_user', 'tiger'
go
Here the local ASE user "jsmith" will be known as "report_user" when executing remote SQL within IQ.
-- Next, as the IQ schema owner, create a stored procedure
-- Assume the schema owner here is STAR_SYS
create procedure P_IQ_galaxy_list ( @sector_cd varchar(10) ) as
select glx_id, glx_name
from galaxy_audit
where sector_cd = @sector_cd
return
go
grant execute on P_IQ_galaxy_list to report_user
go
Then, on the ASE server, you should be able to call the remote procedure
-- as jsmith
exec PROD_IQ..STAR_SYS.P_IQ_galaxy_list 'DEEP_SPACE47'
go
That's it!
Now, what if you wanted access to the ASE data from IQ, within the called proc?
First, you need to add a login to the ASE server (the schema owner on IQ):
-- as SA, on ASE server
sp_addlogin 'STAR_SYS','python','star_db'
go
use star_db
go
sp_addalias STAR_SYS,dbo
go
Then, on the IQ side, create a Global Temporary Table, to contain the ASE data
(this is best when doing joins, as performance is excellent)
-- As the schema owner in IQ
create global temporary table gtt_galaxy_alien_planet (
glx_id integer,
star_dt datetime,
population integer
)
on commit preserve rows
go
-- Then create the new proc
create procedure P_IQ_galaxy_time ( @sector_cd varchar(10) ) as
insert into gtt_galaxy_alien_planet (glx_id,star_dt,population)
location 'PROD_ASE.star_db'
packetsize 2048
{ select glx_id,star_dt,population from alien_planet_sys }
select t1.glx_id, t1.glx_name, t2.population
from galaxy_audit t1, gtt_galaxy_alien_planet t2
where t1.sector_cd = @sector_cd and
t2.glx_id = t1.glx_id
return
go
grant execute on P_IQ_galaxy_list to report_user
go
In the above example, the IQ dataset is joined to a dataset from ASE (which would typically be smaller).
Our tests have shown this is the fasted method - superior over the use of proxy tables .. and easier to implement.
|
|
