Useful Sybase IQ commands
Connect with:
You could also use isql, e.g. isql -Usybadmin -P******** -SSYBEMA_FDA_PRD_IW02.
Another way to connect locally is by using dbisql as follows: dbisql -c “uid=dba;pwd=*********;eng=SYBEMA_FDA_UAT_IW02;dbn=SYBEMA_FDA_UAT_IW02? -nogui
Commands:
List active users
sp_iqwho
IQ Server info
sp_iqstatus
IQ Servername
select @@servername
Physical disk devices
sp_iqfile
Currently running statements
sp_iqcontext
Currently active transactions
sp_iqtransaction
dbspaces
sp_iqdbspace
dbfiles
sp_iqfile
Table columns and Indexes info
sp_iqhelp
How to create new user in Sybase IQ
create user identified by ;
grant connect to identified by ;
How to change/reset user password in Sybase IQ
Alter user identified by
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=”
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
How to show SQLText of specific Connection Handle in Sybase IQ
sp_iqcontext
How to Check Version space in Sybase IQ
select * from sp_iqstatus() where name like ‘%Other Versions:%’
How to unlock user in Sybase IQ
Alter user RESET LOGIN POLICY
How to reset user password in Sybase IQ
grant connect to identified by
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 add file ” size(MB/GB)