Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Sybase » Administration » DBA » Sybase IQ

Sybase IQ - Remote Access From ASE

     




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.









Sybase : Related Topics

Sybase : Administration : Post-installation check
Sybase : Administration : Device initialization
Sybase : Administration : Database Creation
Sybase : Administration : A backup routine
Sybase : Administration : Striping Dump Devices
Sybase : Administration : Moving the transaction log to another device
Sybase : Administration : Adding a segment to a database
Sybase : Administration : Configuring the cache
Sybase : Administration : Apply multiple transaction dumps
Sybase : Administration : Database maintenance procedure
Sybase : Administration : Database maintenance script
Sybase : Administration : Server configuration
Sybase : Administration : Dealing with a Corrupted Database
Sybase : Administration : Dealing with a Server Failure
Sybase : Administration : DBCC Notes
Sybase : Administration : Create proxy tables and external logins
Sybase : Administration : Renaming a database
Sybase : Administration : Reorg: rebuilding a table
Sybase : Administration : Sybase ASE 15 Enhancements
Sybase : Administration : Setting the thresholds
Sybase : Administration : Apply a transaction dump
Sybase : Administration : Security Tasks
Sybase : Administration : Setting Process Priorities
Sybase : Administration : Sybase ASE Architecture Overview
Sybase : Administration : Drop an alias, with objects linked to login
Sybase : Administration : Display grants, effective rights to objects
Sybase : Administration : Displaying access information from sysprotects
Sybase : Administration : Database Engines: Status and Settings
Sybase : Administration : Replication Server 15 Configuration
Sybase : Administration : Loading data into Sybase IQ from a Remote Server
Sybase : Administration : Sybase IQ Overview
Sybase : Administration : Sybase IQ Functions
Sybase : Administration : Sybase IQ : Procedure Profiling / Monitoring
Sybase : Administration : Sybase IQ: Restoring a Database from Dump Files
Sybase : Administration : Sybase IQ: Minimize Storage option
Sybase : Administration : Sybase IQ: Index Types
Sybase : Administration : Sybase IQ: HUGEPAGE Support
Sybase : Administration : Sybase IQ: Database Creation (Raw Devices)
Sybase : Administration : Sybase IQ : Repairing Problems / Troubleshooting Corruption
Sybase : Administration : Sybase IQ : Moving / Re-allocating / Re-org
Sybase : Administration : Sybase IQ: Sysmon / System Performance Analysis
Sybase : Administration : Sybase IQ: Monitoring Connections / Processes
Sybase : Administration : Sybase ASE: Installing a new server via srvbuildres utility
Sybase : Administration : Sybase IQ: Copying users/logins between databases
Sybase : Administration : Sybase IQ: Managing User Accounts
Sybase : Administration : Sybase ASE: Database Schema Compare
Sybase : Administration : Sybase IQ: Listing tables, ordered by size allocated
Sybase : Administration : Sybase IQ : Comparing grants / permissions between servers
Sybase : Administration : Sybase ASE: Database page size
Sybase : Administration : Sybase ASE: Managing users and roles
Sybase : Administration : Sybase ASE: Setting resource limits for users
Sybase : Administration : Sybase ASE: Setting up user assigned custom tempdbs
Sybase : Administration : Sybase ASE: Setting up the transaction log cache using logiosize
Sybase : Administration : Sybase IQ: Server Startup Switches
Sybase : Administration : Sybase ASE: Managing Identity Columns
Sybase : Administration : Sybase IQ: Working with options and server settings
Sybase : Administration : Sybase IQ: Monitoring and Troubleshooting via System Procedures

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.