Below is a list of useful queries run against Amazon Redshift in Strategy DBQuery tool 10.x
-- Finding the owner of a table in redshift
select * from pg_tables where tablename = 'etl_hier_request_id' -- tableowner = 'cotybipetl';
-- Query for list of Users
select * from pg_user where usename = 'cotybiprep';
-- Query for recent query list
select query, pid, elapsed, substring from svl_qlog where userid = 100 order by starttime desc limit 5;
-- Find Schema owner name
select nspname as schema, usename as owner
from pg_namespace, pg_user
where pg_namespace.nspowner = pg_user.usesysid
and usename <> 'rdsdb'
--and pg_user.usename ='cotybipetl'
-- SET SEARCH_PATH of a user
alter user ssurabhi
set search_path = c332_prd_global_wh, c332_prd_global_stg, c332_prd_global_hist, c332_prd_global_cnsmtn
-- Crate super user
create user ssurabhi createuser password 'Test123';
-- Cancel a Query
select pid, trim(user_name), starttime, substring(query,1,20)
from stv_recents
where status='Running'
-- Identify tables with primary key
select table_id,conname,"schema","table" from pg_constraint pgc , SVV_TABLE_INFO pc where
pgc.conrelid = pc.table_id
and pgc.contype ='p' and "schema" ='c332_prd_global_wh'
-- Using array to string
select * from pg_group where array_to_string(grolist,',') like '%165%'
Refer to Technical Document below on Strategy integration with Amazon Redshift and Performance Tuning
KB26614: Integrating MicroStrategy Analytics 9.4.x and newer platform with Amazon Redshift
Refer to Technical Document below on Amazon Redshift Performance Tuning
Amazon Redshift Performance Tuning - Other Considerations
Article Reference Number: KB441568
