SAP IQ NOTES

SAP Sybase IQ

1.     SAP Sybase IQ useful commands

• How to create new user in Sybase IQ
  create user <username> identified by <password>;
  grant connect to <username> identified by <password>;

• How to change/reset user password in Sybase IQ
  Alter user <username> identified by <password>

• How to check database space usage in Sybase IQ
  select substr(DBSpaceName,0,15),DBSpaceType,Usage,TotalSize from sp_iqdbspace()

 

 

• How to list down tables in Sybase IQ
  select distinct table_name from sp_iqtable()

• How to list down tables in Sybase IQ of specific schema
  select distinct table_name from sp_iqtable() where table_owner='<schema_username>’

• How to check server startup time in Sybase IQ
  select convert(char(30),@@servername),convert(char(30),property(‘StartTime’))

• How to check list of users with their last login time and locked status in Sybase IQ
  select convert(char(30),(user_name)),convert(char(30),(last_login_time)),convert(char(6),(locked)),convert(char(30),(reason_locked)) from sa_get_user_status()

• How to check blocking on Sybase IQ
  select ConnHandle,IQconnID,BlockedOn,BlockUserid from sp_iqwho() where BlockUserid != ‘NULL’

• How to check Active transaction running on Sybase IQ
  select substr(Userid,0,10) as UserID,substr(ConnHandle,0,5) as ConnHandle,substr(state,0,10) as STATE,substr(TxnCreateTime,0,20) as RunningTime from sp_iqtransaction();

• How to kill specific connHandle in Sybase IQ
  drop connection <ConnHandle>

• How to show SQLText of specific Connection Handle in Sybase IQ
  sp_iqcontext <ConnHandle>

• How to Check Version space in Sybase IQ
  select * from sp_iqstatus() where name like ‘%Other Versions:%’

or

select convert(varchar(30),name),value from sp_iqstatus() where name like ‘%Version%’;

• How to unlock user in Sybase IQ
  Alter user <username> RESET LOGIN POLICY

• How to reset user password in Sybase IQ
  grant connect to <username> identified by <password>

• How to Check Login Locked Status for all logins in Sybase IQ
  select user_id,convert(char(25),user_name),locked from sa_get_user_status()

• How to Check Login Locked Status for all specific user in Sybase IQ
  select user_id,convert(char(25),user_name),locked from sa_get_user_status() where user_name='{user_name}’

• How to Check Login policy and options of a user in Sybase IQ
  select a.user_id,convert(char(25),a.user_name),a.login_policy_id,convert(char(25),b.login_policy_name),convert(char(15),c.login_option_name),convert(char(15),c.login_option_value) from sysuser a,sysloginpolicy b,sysloginpolicyoption c where a.user name='{user name}’

• How to Check temp space usage details in Sybase IQ
  select Top 5 ConnHandle,IQconnID,name,IQCmdType,LastIQCmdTime,ConnCreateTime,NodeAddr,(TempTableSpaceKB+TempWorkSpaceKB) as TempSpaceUsed from sp_iqconnection() order by TempSpaceUsed desc

• How to Check DB Options in Sybase IQ
  select convert(char(15),User_name) as User_Name,convert(char(35),Option_name) as Option_Name,convert(char(15),Current_value) as Current_values,convert(char(15),Default_value) as Default_value,convert(char(25),Option_type) as Option_type from sp_iqcheckoptions() order by User_Name

• How to Check Multiplexing details in Sybase IQ
  select substring(server_name,1,20) as server_name,substring(connection_info,1,30) as connection_info,mpx_mode,inc_state,status,substring(coordinator_failover,1,20) as coordinator_failover,substring(db_path,1,40)as db_path from sp_iqmpxinfo();

• How to Check remote/external logins details in Sybase IQ
  select convert(char(15),s.user_name) as username,convert(char(20),ss.srvname) as srvname,remlogin from sysuser as s,sysserver as ss,sysexternlogin as el where el.srvid=ss.srvid and el.user_id=s.user_id

• How to Check remote/external logins details in Sybase IQ
  select convert(char(15),s.user_name) as username,convert(char(20),ss.srvname) as srvname,remlogin from sysuser as s,sysserver as ss,sysexternlogin as el where el.srvid=ss.srvid and el.user_id=s.user_id

• How to add temp store file in Sybase IQ
  alter DBSPACE <IQ_SYSTEM_TEMP> add file <new file name> ‘<path>’ size(MB/GB)