Sybase IQ

Binary Location

/opt/sybase

export SYBASE=/opt/sybase
. /opt/sybase/SYBASE.sh
. $SYBASE/IQ-15_4/IQ-15_4.sh

cd $IQDIR15/demo
sh ./mkiqdemo.sh

start_iq @iqdemo.cfg iqdemo.db

envvar=" -c 48MB "
export envvar
env $envvar
start_iq @envvar

dblocate --> verify the current db is running
dblocate|grep -i mpx_001
ps -ef|grep -i mpx_001

stop_iq

iqdsn -w "iqdemo" -c "uid=DBA;pwd=sql;dbf=/opt/sybase/IQ-15_4/demo/iqdemo.db"

Basic Commands

cat ~/.odbc.ini

sp_iqstatus
sp_iqspaceused

--working:
export DISPLAY=192.168.1.1:0
dbisql
User ID: DBA
Password: xxxxx 
-- ------------------------------

ps -ef|grep -i iq

/u01/sybase/v160/IQ-16_0/bin64/iqsrv16 @/u01/sybase/v160/params.cfg db1siq.db -gn 55 -hn 5

*** Sybase Central
cd $IQDIR15/shared/sybcentral610
./scjview.sh &

sybase control center:
$SYBASE/SCC-3_2/bin/scc.sh -- port rmi=9991; $SYBASE/SCC-3_2/bin/scc.sh -start

dbisql or dbisqlc or isql

dbisql --help
dbisql --help -nogui

-- where in dbisql, use the set command with no arguments to list the current database connection settings and options

inside dbisql
read xxx.sql ---> load and execute this .sql script file

dbisql -version

dbisql --help

dbisql --help -nogui

dbisql -datasource my_demo -nogui

dbisql -c "UID=DBA;pwd=SybaseMypass"

dbisql -nogui -c "uid=DBA;pwd=SybaseMypass"

dbisql -nogui -c "uid=DBA;pwd=SybaseMypass;eng=db1siq"

dbisql -c "uid=DBA;pwd=SybaseMypass" -nogui
quit

dbisql -c "uid=DBA;pwd=SybaseMypass;eng=db1siq;d=iqmain" -nogui

dbisql -c "uid=DBA;pwd=sql; eng=my_server; dbn=my_db; links=tcpip{port=2638}" -nogui

dbisqlc 

CONNECT
USING 'UID=DBA;PWD=SybaseMypass;DBN=mydb'

dbisql -c "uid=myuser;pwd=mypasswd;eng=mysid;dbn=mysid;links=tcpip{host=myhostname_or_ip;port=myport}"
dbisql -c "uid=myuser;pwd=mypasswd;eng=mysid;dbn=mysid;links=tcpip{host=myhostname_or_ip;port=myport}" -nogui

CONNECT
USING 'UID=DBA;PWD=SybaseMypass;DBN=db1siq'

dbisql -? -nogui
Interactive SQL Version 16.0.807
Usage: DBISQL [options] [sqlStatement|sqlScriptFileName]
   @ expands  from environment variable  or file 

   Options:
    -c "keyword=value;..."        Database connection parameters
    -d "delimiter"                Use the given string as the statement delimiter
    -d1                           Print statements as they are executed
                                    (command-line mode only)
    -datasource              ODBC data source name
    -f                  Open the given file for editing
    -hana                         Connect to SAP HANA databases by default
    -host 
               Network address of the database server
    -iq                           Connect to SAP Sybase IQ databases by default
    -nogui                        Run in command-line mode
    -onerror {continue|exit}      Override ON_ERROR option for all users
    -port             TCP/IP port number of the database server
    -q                            Suppress non-critical messages
    -ul                           Connect to UltraLite databases by default
    -version                      Display the program version number
    -x                            Check SQL syntax but do not execute

>quit
>exit

-- output select result into a file
SELECT * FROM Employees; OUTPUT TO employee.txt FORMAT ASCII
SELECT * FROM Employees; OUTPUT TO employee.txt APPEND VERBOSE
SELECT 'line1\x0aline2'; OUTPUT TO file.txt HEXADECIMAL OFF
SELECT 'line1\x0aline2'; OUTPUT TO file.txt HEXADECIMAL ON

select * from sa_db_properties ();OUTPUT TO xxx.txt FORMAT ASCII
select PropName, value, PropDescription from sa_db_properties ();OUTPUT TO xxx.txt FORMAT ASCII

-- batch file specification
dbisql -c "UID=DBA;PWD=sql" -nogui -onerror exit mysqlcom.sql

-- Log file specification location
-c "UID=dba;PWD=sql;LINKS=SharedMemory;Log=C:\salog" test.sql

dbisqlc

isql -UDBA -Pxxxx -Smyservername

isql -X -S TSS126 -D SAPIQDB -U DBA -P sql
isql -Udba -PSybaseMypass -Sutility_db

using dsedit to setup interdace file

find . -name "interfac*"  -print
interfaces or interfac on UNIX operating systems

isql -Udba -Pxxxx -S SDH1
more interfaces
SDH1
        master tcp ether hdqdlsybase01v 2638
        query tcp ether hdqdlsybase01v 2638

dictionary

-- noexist sysdatabases
select name, type from sysobjects where name like '%d%b%';

describe sysobjects;

sa_db_list
sa_db_info
sa_db_properties

sa_db_info
call sa_db_info
select * from sa_db_info ();

select top 10 * from sa_db_properties ();
select * from sa_db_properties ();

select top 10 PropName, value, PropDescription from sa_db_properties (); 
select PropName, value from sa_db_properties (); 

select PropName, value from sa_db_properties ();OUTPUT to 'xxx.txt'; 



select top 1 * from sysobjects;
select * from sysobjects;

select * from sysobjects where name like '%d%b%';

SELECT distinct type FROM sysobjects;
    C . computed column
    D . default
    F . SQLJ function
    L . log
    N . partition condition
    P . Transact-SQL or SQLJ procedure
    PR . prepare objects (created by Dynamic SQL)
    R . rule
    RI . referential constraint
    S . system table
    TR . trigger
    U . user table
    V . view
    XP . extended stored procedure

SELECT * FROM sysobjects WHERE type = 'U';

select su.name+'.'+so.name from sysobjects so, sysusers su where so.type='U' and so.uid=su.uid order by su.name, so.name;



select * from systable where table_name = 'ARC_ARCHIVEDATA' order by table_name;

select u.user_name, t.table_name from systable t, SYS.SYSUSERPERM u where t.creator=u.user_id and t.table_name = 'ARC_ARCHIVEDATA' order by t.table_name;

select u.user_name, t.table_name from systable t, SYS.SYSUSERPERM u where t.creator=u.user_id and u.user_name = 'OASIS_SKY' order by t.table_name;

select substring(u.user_name,1,30) + '.' + substring(t.table_name,1,30) xxx from systable t, SYS.SYSUSERPERM u where t.creator=u.user_id and u.user_name = 'OASIS_SKY' order by t.table_name;

Control Center

./scc.sh -h
./scc.sh --help

./scc.sh -v
./scc.sh --version

$SYBASE/SCC-3_3/bin/scc.sh --start --> Start Sybase Control Center
$SYBASE/SCC-3_3/bin/scc.sh -stop --> or Control-C to Stop Sybase Control Center

IQ Servers : IQ Servers name and version
IQ Servers  --> Schema Objects --> Databases : database file location
IQ Servers  --> Schema Objects --> Tables    : List of user table names
IQ Servers  --> Security --> Login Policies  : List of user defined Login Policy Names
IQ Servers  --> Space Management --> DBspaces
IQ Servers  --> Space Management --> DB Files

alias

which scc.sh
cd /u01/sybase/IQ16.08/SCC-3_3/bin

export SCC_HOME=/u01/sybase/IQ16.08/SCC-3_3

$SCC_HOME/bin/scc.sh --start

nohup ./scc.sh 2>&1 > scc-console.out &

$SCC_HOME/bin/scc.sh --stop

/bin/sh ./scc.sh --start
scc-console> help, status, info, shutdown

scc

   SCCADMIN     SybaseIQSRV
   sccagent     SybaseAGT

iqdsn

iqdsn
iqdsn: list syntax
iqdsn -f : display .dbbc.ini location
iqdsn -l

find script location

ksh
find `echo $PATH|sed 's/:/ /g'` -name dbisql -print 2>/dev/null

bash:
find ${PATH//:/ } -name dbisql -print 2>/dev/null

linux: 
locate dbisql 

multi_dirs finding:
find /u01/sybase/shortcuts /u01/sybase/scripts /u01/sybase/bin  -name dbisql -print 2>/dev/null

ps -ef|grep -i $$ --> .bash_profile

# SYBASE.sh --> /u01/sybase/v160/SYBASE.sh
more `locate SYBASE.sh`

env|grep -i "^syb"

# SYBASE="/u01/sybase/v160"
# SYBROOT="/u01/sybase/v160"

more /u01/sybase/v160/IQ.sh

more IQNLS.sh

dbisql -c "UID=DBA;pwd=Sybasepass"

dbisql -nogui -c "uid=DBA;pwd=SybaseMypass"

dbisql -nogui -c "uid=DBA;pwd=SybaseMypass;eng=db1siq"

ps -ef|grep -i iq

/u01/sybase/v160/IQ-16_0/bin64/iqsrv16 @/u01/sybase/v160/params.cfg db1siq.db -gn 55 -hn 5
more /u01/sybase/v160/params.cfg

env|grep -i "^iq"
# export IQWORK=/u01/sybase/work
# export IQSCRIPT=/u01/sybase/scripts

# export CONNECT="@/u01/sybase/v160/hidden_connect_string.cfg"
more /u01/sybase/v160/hidden_connect_string.cfg

more /jobs/DBA/prod/do_dump.ksh
more /jobs/DBA/prod/p__dodump.sql

technotes SP Service Pack
Getting Started with SAP Sybase IQ Column Store Analytics Server 
IQ 16

Start Server, Engine, Instance

ps -ef|grep -i iqsrv  #find current running sybase iq instance
ps -ef|grep -v root|grep -v -i scc
ps -ef|grep iqsrv|grep -v grep
sybase   27890     1  2 12:26 ?        00:00:05 /u01/sybase/IQ16.08/IQ-16_0/bin64/iqsrv16 @/u01/sybase/config/db1siq_params.cfg db1siq.db -gn 105 -hn 5

start_iq -?
start_iq -n mydemo -x tcpip{port=2770} iqdemo.db

start_iq @iqdemo.cfg iqdemo.db

stop_iq  ---> interactive
stop_iq -stop all - To stop all database in the Linux server
stop_iq -stop one  - Will stop the first instance.

envvar = " -c 40m -gc 6000 -gm 10 -gp 4096 -ti 4400 -iqmc 100 -iqtc 150 "
echo $envvar
start_iq @envvar -x 'tcpip{Host-vmi103;ServerPort=1700} customer.db

start_iq -n utility_db  --> util_db.ini   located in $IQDIR15/bin64  Sybase/SYSAM-2_0/bin/ --> Default user: DBA, default password: sql

The .db extension for the database file may be omitted in the start_iq command 

-n tpch
-c  32m
-gc 20
-gd all
-gl all
-gm 10
-gp 4096
-ti 4400
-iqmc 400
-iqtc 600
-o message.out
-zr sql
-zo iq.sql.out
-x tcpip{port=7878}

startup option: 
-n xxxx: Sybase IQ Server Name or Engine Name
-c  : Cache Size, Defaults: 48M, -c 64m,  -cl lower limit, -ch high limit

-gp : Catalog Store page size: Typically 4096 or 4k

-gm : Number of user connections permitted, default = 10

-gc: Checkpoint Interval ( minutes) default 20

-gr: Max recovery interval ( miniutes) Default: 2

-ti: client timeout ( minutes ), Default: 4400 minutes ( ~ 72 hours )

-tl: Liveliness packets sent every xxx seconds, default network timeout ( seconds ), Default: 120 seconds

-iqmc: Main cache size in MB, Default 32 MB

-iqtc: Temporary cache size in MB, Default 24 MB

-x: Communication Protocals, -x tcpip(port=2640),  -x tcpip(MyIP=192.75.209.12:2367). -x tcpip ( Use only TCP/IP with default prot

-iqgovern: Maximum number of queries to execute at once, suggested values: 2*numCPU + 10 or 2*numCPU + 4

Server Configuration File: params.cfg, default: Located in the same directory as .db file

sp_iqstatus
sp_iqstatus;OUTPUT to 'xxx.txt';

select value from sp_iqstatus() where name like '%RLV memory used%';
select name, value from sp_iqstatus();

Select From Store Procedure

select < col1>, < col2> from < sp_name>('< sp_arg>') where < predicate>
select Object, DbspaceName, ObjSize from sp_iqindexinfo ('table xyz') where Object like '%col1_indx%'

sp_iqwho 'DBA'
select ConnHandle,IQconnID,UserID from sp_iqwho ('DBA', 'user');
select ConnHandle,IQconnID,UserID from sp_iqwho('DBA', 'user') where ConnHandle=1;

DBSpace

sp_iqdbspace
sp_iqdbspace IQ_SYSTEM_MAIN
sp_iqdbspaceinfo

select name, type from sysobjects where name like '%d%b%space%';
sp_iqdbspace
select DBSPACENAME from sp_iqdbspace();
sp_iqdbspaceinfo

CREATE DBSPACE "NLSDBSPACE01" USING  
FILE "nlsdbspace01_001" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_001.iqmain' SIZE 10240 MB RESERVE 0 KB, 
FILE "nlsdbspace01_002" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_002.iqmain' SIZE 10240 MB RESERVE 0 KB, 
FILE "nlsdbspace01_003" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_003.iqmain' SIZE 10240 MB RESERVE 0 KB, 
FILE "nlsdbspace01_004" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_004.iqmain' SIZE 10240 MB RESERVE 0 KB, 
FILE "nlsdbspace01_005" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_005.iqmain' SIZE 10240 MB RESERVE 0 KB, 
FILE "nlsdbspace01_006" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_006.iqmain' SIZE 10240 MB RESERVE 0 KB, 
FILE "nlsdbspace01_007" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_007.iqmain' SIZE 10240 MB RESERVE 0 KB, 
FILE "nlsdbspace01_008" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_008.iqmain' SIZE 10240 MB RESERVE 0 KB 
IQ STORE STRIPING ON STRIPESIZEKB 128;

CREATE DBSPACE mydb_tmp_2 as '/../sysdb_2.iqtmp' IQ TEMORARY STORE SIZE 200 RESERVE 50;

ALTER DBSPACE IQ TEMPORARY STORE ADD 50;

ALTER DBSPACE NLSDBSPACE01 READONLY;

ALTER DBSPACE IQ_SYSTEM_MAIN SIZE 10MB; -- incease or reduce size

ALTER DBSPACE "NLSDBSPACE01" ADD FILE "nlsdbspace01_009" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_009.iqmain' SIZE 1024 MB RESERVE 0 KB;

ALTER DBSPACE "NLSDBSPACE01" ALTER FILE "nlsdbspace01_009" '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_009.iqmain' SIZE 10240 MB;

ALTER DBSPACE "NLSDBSPACE01" DROP FILE "nlsdbspace01_009";

DROP DBSPACE "NLSDBSPACE01X";

Database

CREATE DATABASE '/mydb/mydb.db' 
 IQ PATH        '/iqmain/mydb01.iq'    IQ SIZE 200 
 MESSAGE PATH '/iq/data/big.iqmsg' 
 TEMPORARY PATH '/iqtemp/mydb01.iqtmp' TEMPORARY SIZE 200
IQ PAGE SIZE 65536;

DROP DATABASE '/mydb/mydb.db';

sp_iqfile
sp_iqstatus

sp_iqdbspaceobjectinfo
sp_iqobjectinfo

sp_iqrelocate 'my_table_name|my_index_name'  --> need more info

select * from sysdbfile;

Current Connections

sp_iqconnection 
sa_conn_info
sa_conn_activity

select ConnHandle,Userid,LastReqTime from sp_iqconnection();

sp_iqwho [ { connhandle | user-name } [, arg-type ] ]

sp_iqwho "DBA"
sp_iqwho "DBA", "user"

sp_iqwho argparam=>"DBA"

describe sp_iqwho;
select * from sp_iqwho( 'DBA');
select * from sp_iqwho( 'DBA', 'user');

select ConnHandle,IQconnID,UserID from sp_iqwho('DBA');

select ConnHandle,IQconnID,UserID from sp_iqwho ('DBA', 'user');

select ConnHandle,IQconnID,UserID from sp_iqwho('DBA', 'user') where ConnHandle=1;

sp_iqwho 1

sp_iqshowpsexe
DROP CONNECTION

drop connection 

sp_iqcontext 
sp_iqwho 'DBA' 


sp_iqcontext '614'
sp_iqcontext 614 --> ConnHandle

SELECT connection_property ( 'Number' )  -->self 
sp_iqversionuse

DISCONNECT

sp_iqtransaction ;

select distinct
a.connHandle, a.connOrCurCreateTime, a.Userid, a.IQThreads,
CONNECTION_PROPERTY( 'DiskRead', Number ) AS DiskRead,
CONNECTION_PROPERTY( 'DiskWrite', Number ) AS DiskWrite,
CONNECTION_PROPERTY( 'CacheRead', Number ) AS CacheRead,
CONNECTION_PROPERTY( 'CacheHits', Number ) AS CacheHits,
a.CmdLine
from sp_iqcontext() a, sa_conn_properties() b
where a.ConnOrCursor = 'CONNECTION'
and cmdLine != 'NO COMMAND' and a.ConnHandle=b.Number and b.PropName ='DiskRead';

Transactions And Locks

sp_iqlocks;
call sp_iqlocks()

sp_iqtransaction

system and users option

SET [TEMPORARY] OPTION [user_id. | PUBLIC. ] option-name = [ option-value]


select * from sysoption;

select * from sp_iqcheckoptions() where user_name='DBA';
select * from sp_iqcheckoptions() order by 1;

SET OPTION public.Force_No_Scroll_CUrsors = 'On'

SET OPTION public.Query_Temp_Space = 0  --> Infinity, default 2000MB 

SET OPTION public.Query_Plan = 'Off'  --> default 'on' Turns off printing of user query plans into the IQ Message File

set option public.Query_Plan = 'ON';
set option public.Query_Detail = 'ON';
set option public.Query_Plan_After_Run = 'ON';
set option public.Query_Plan_As_HTML = 'ON';
set option public.Query_Plan_As_HTML_Directory = '/opt/sybase/TPCHDB/QueryPlans';
set option public.Query_Plan_Text_Access = 'ON';
set option public.Query_Plan_Text_Caching = 'ON';
set option public.Query_Timing = 'ON';
set option public.Index_Advisor = 'ON';

Query Plan

SET TEMPORARY OPTION Index_Advisor 'ON';
SET TEMPORARY OPTION Query_Plan = 'ON';
SET TEMPORARY OPTION Query_Detail = 'ON';
SET TEMPORARY OPTION Query_Timing = 'ON';
SET TEMPORARY OPTION Query_Plan_After_Run = 'ON';
SET TEMPORARY OPTION Query_Plan_As_HTML = 'ON';
SET TEMPORARY OPTION Query_Plan_As_HTML_Directory = '/YOUR/DESIRED/DIRECTORY/PATH';
SET TEMPORARY OPTION Query_Name = 'YOUR__DESIRED_QUERY_NAME_HERE';
SET TEMPORARY OPTION dml_options10='on';

set temporary option index_advisor = on
set temporary option query_plan = on
set temporary option query_detail = on
set temporary option query_timing = on
set temporary option query_plan_after_run = on
set temporary option row_counts = on
set temporary option QUERY_PLAN_AS_HTML = 'ON';

SET TEMPORARY OPTION QUERY_PLAN_AS_HTML_DIRECTORY = '/system1/users/DBA/html_plans';
set option public.Query_Plan_As_HTML_Directory = '/opt/sybase/TPCHDB/QueryPlans';
set temporary option public.Query_Plan_As_HTML_Directory = '/opt/sybase/TPCHDB/QueryPlans';

set     option .query_plan_as_html_directory = ''

SELECT col1 FROM tab1;

http://www.petersap.nl/SybaseWiki/index.php?title=Capturing_IQ_query_plans
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1601/doc/html/san1281565184521.html

SELECT USER, today(*) FROM SYS.DUMMY
SELECT NOW() FROM sys.dummy

CREATE TABLE iq_dummy (dummy_col INT NOT NULL);
insert into iq_dummy values ( 1 );
SELECT NOW() FROM iq_dummy

select db_id();
select db_name();
select @@servername;
select @@servername;  -- db1siq
SELECT PROPERTY('TempDir'); -- Find out which directory where the temporary file was created in:

select db_id(),  db_name(), @@servername, PROPERTY('TempDir'), PROPERTY ( 'MainHeapPages' ), DB_PROPERTY( 'PAGESIZE' );

sa_db_info(0)
sa_db_info
call sa_db_info
select Alias, File, LogName from sa_db_info ();
select * from sa_db_info ();

call sa_db_properties
CALL sa_eng_properties;
CALL sa_conn_properties( );

sp_iqstatus
call sp_iqstatus
select name, value from sp_iqstatus() where name like '%Version%';

select * from sysfile;
SELECT dbfile_name, file_name FROM sys.sysdbfile  WHERE dbfile_name not in ( 'system', 'temporary' ) order by dbfile_id;

SELECT DB_PROPERTY( 'PAGESIZE' );
SELECT PROPERTY ( 'MainHeapPages' );

-- Determine the temporary file name:
sa_db_properties
select * from sa_db_properties();
select PropName, Value from sa_db_properties();

SELECT Value from sa_db_properties() WHERE propname = 'TempFileName';

sa_eng_properties;
select * from sa_eng_properties();
select top 10 * from sa_eng_properties();
select top 10 PropName, Value from sa_eng_properties();

sa_table_page_usage;
select top 3 * from sa_table_page_usage();

sp_iqindexinfo;
sp_iqspaceinfo;

sp_iqdbspaceinfo;
select * from sp_iqDBSpaceinfo();

sp_iqdbstatistics

-- list all user status
select * from sys.sysuserlist;
select user_group, dbaauth, name from sys.sysuserlist order by user_group, dbaauth, name;

sa_get_user_status

describe sysuser;
describe sys.sysuser;

select su.name+'.'+so.name from sysobjects so, sysusers su where so.type='U' and so.uid=su.uid order by su.name, so.name;

select * from sysuser;
select top 3 * from sysuser where user_name is not null;
select user_name from sysuser where user_name is not null;

describe  table sys.sysuser
describe  index for table sys.sysuser

select * from sysoptions;

SELECT * FROM sys.sysevent;

select * from sp_iqstatus();

sp_iqtable;

-- ----------------------------------------------


http://www.sybase.com/detail?id=1067282

-- Obtain the current size of the temporary file, in bytes:
SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'temporary') * PROPERTY('PageSize');

-- The foloowing two queries return too many rows
SELECT top 1 *    FROM SYSDEPENDENCY,      SYSOBJECT,      SYSPROCPARM ORDER BY 1, 2, 3, 4, 5; 
SELECT * FROM SYSDEPENDENCY, SYSOBJECT, SYSPROCPARM;

describe dbisql 

select * from sysobjects so where so.name='spt_collation_map';
select sc.cname from sys.syscolumns sc where sc.tname = 'spt_collation_map';

SELECT sc.cname, sc.coltype,sc.length FROM sys.syscolumns sc INNER JOIN sysobjects so ON sc.tname = so.name WHERE so.name = 'spt_collation_map';

select * from systable;
select * from sys.systable;

sp_tables;

select top 3 * from systable;
select top 3 creator,table_name from systable;
select creator,table_name from systable;

-- ----------------------------------------------

systemtable

sysuasages
syssegments
sysroles

sp_sysmon
sp_dboption

sysusers
sysprotects
sysaliases

dbcc tablealloc
dbcc checkalloc

dbcc indexalloc
dbcc CheckTable

dbcc checkdb

sp_plan_dbccdb

dbccdb

-----------------------------------------------------------------------------------------------------------------
To modify the Sybase license

db1siq:> ls -l
-rw-rw-r-- 1 sybase sybase      1449 Jan 15 19:09 db1siq.lmp
vi db1siq.lmp
change parameter to SF
LT=SF

Backup database

-- backup syntax:
backup database full to '/dbbackup'
backup database incremental to '/dbbackup'
backup database incremental since full  to '/dbbackup'

-- parallel
backup database full to '/dbbackup/xxx01', '/dbbackup/xxx01', '/dbbackup/xxx02', '/dbbackup/xxx03'

To extract the backup header:
db_backupheader db1siq_20131210_1312.1.full.1

-- in script
/jobs/DBA/prod/do_dump.ksh FULL 4 >>/jobs/DBA/output/fulldump.out
do_dump.ksh
-- --------------------------------------------------------------------------------------------------------------
dbisql @${CONNECT_STRING} -nogui "call sp__dodump ('${TARGETDIR}', ${STRIPES}, '${TYPE}')"
dbisql @${CONNECT_STRING} -nogui "call sp__dodump ('/dbbackups', 4, 'FULL')"
dbisql @${CONNECT_STRING} -nogui "call sp__dodump ('/dbbackups', 4, 'INCR')"
-----------------------------------------------------------------------------------------------------------------
create or replace procedure sp__dodump (
  in dumpdir varchar(255) default '/dump',
  in stripes int default 1,
  in type varchar(4) default 'FULL'
)
begin
  declare sqlCmd varchar(1024);
  declare x int;
  declare currdate varchar(14);
  declare backuptime datetime;
  declare restorefile varchar(255);

  create table #t1 (seq int default autoincrement, cmd varchar(255));

-- Validate incoming parameters

  if ( stripes < 0 or stripes > 36 ) then
    message 'Invalid number of stripes' to client;
    return;
  end if;

  if (type != 'FULL' and type != 'INCR' and type != 'ISF' ) then
    message 'Invalid backup type' to client;
    return;
  end if;

  set backuptime = current timestamp;
  select cast(dateformat (backuptime, 'yyyymmdd') as varchar)
       + '_' + cast(datepart(hh, backuptime) as varchar) + cast(datepart(mi,backuptime) as varchar) into currdate;

-- Start construction of the restore command
   insert #t1 ( cmd ) values ( '-- To restore to a different server/database uncomment the rename clauses and edit the file names and path
s');
   insert #t1 ( cmd )
   select 'restore database ''' + file_name + ''''
     from sys.sysdbfile
    where dbfile_name = 'system';

-- Construct the backup command

  set sqlCmd = 'backup database ';
  set restorefile = dumpdir + '/restore_' + db_name() + '_' + lower(type) + '_' + currdate + '.txt';


  case type
  when 'FULL' then set sqlCmd = sqlCmd + ' full '
  when 'INCR' then set sqlCmd = sqlCmd + ' incremental '
  when 'ISF'  then set sqlCmd = sqlCmd + ' incremental since full '
  else  set sqlCmd = sqlCmd + ' full '
  end case;

  set x = 1;

  while x <= stripes loop
    set sqlCmd = sqlCmd + ' to ''' + dumpdir + '/' + db_name() + '_'
               + cast(currdate as varchar)
               + '.' +cast(x as varchar) + '.' + lower(type) + ''' ';
          insert #t1 (cmd)

          select 'from ''' + dumpdir + '/' + db_name() + '_'
               + cast(currdate as varchar)
               + '.' +cast(x as varchar) + '.' + lower(type) + ''' ';
    set x = x + 1;
  end loop;

  for renames as c1 cursor for
   SELECT dbfile_name, file_name
     FROM sys.sysdbfile
    WHERE dbfile_name not in ( 'system', 'temporary' )
  order by dbfile_id
  do
        insert #t1 (cmd)
        select '--rename ' + dbfile_name + ' to ''' + file_name + '''';
  end for;

  execute (sqlCmd);
  set sqlCmd = 'set temporary option temp_extract_name1 = ''' + restorefile + '''';
  execute (sqlCmd);
  select cmd from #t1 order by seq;
  set temporary option temp_extract_name1 = '';
end;

Restore database

To restore db1siq database:

1. stop iq instance.
   stop_iq           -Will ask for the instance number that you want to stop.
   stop_iq -stop all - To stop all database in the Linux server
   stop_iq -stop one  - Will stop the first instance.

2. OPT-Remove the database files if you are doing a full restore.
   catalog(/u20) - (.db and log files)
   iq_main(/u03)
   iq_temp(/u30)


2. start the utility server or utility instance.
   start_iq -gp 8192 -n utility_db    - This a idle instance with 8192 page size

3. Start the restore of the database and replace member.sql with the correct input member that contain the parameters
   dbisql -c "uid=DBA;pwd=sql;dbn=utility_db" -nogui member.sql (restore member is located in current dir) or
                                                                  provide full path)
-- member.sql 
-- To restore to a different server/database uncomment the rename clauses and edit the file names and paths,
restore database '/u20/sybase/data/db1siq/db1siq.db',
from '//BKrep/sybase/db1siq/F201301012399/db1siq_20160504_2315.1.full' ,
from '//BKrep/sybase/db1siq/F201301012399/db1siq_20160504_2315.2.full' ,
from '//BKrep/sybase/db1siq/F201301012399/db1siq_20160504_2315.3.full' ,
from '//BKrep/sybase/db1siq/F201301012399/db1siq_20160504_2315.4.full' ,
--rename IQ_SYSTEM_MAIN to '/u03/sybase/data/db1siq/db1siq_main01.iqmain',
--rename IQ_SYSTEM_TEMP to '/u30/sybase/data/db1siq/db1siq_temp01.iqtmp',
--rename IQ_SYSTEM_MSG to '/u20/sybase/data/db1siq/msg/db1siq.iqmsg',
--rename NLSDBSPACE01_001 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_001.iqmain',
--rename NLSDBSPACE01_002 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_002.iqmain',
--rename NLSDBSPACE01_003 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_003.iqmain',
--rename NLSDBSPACE01_004 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_004.iqmain',
--rename NLSDBSPACE01_005 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_005.iqmain',
--rename NLSDBSPACE01_006 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_006.iqmain',
--rename NLSDBSPACE01_007 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_007.iqmain',
--rename NLSDBSPACE01_008 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_008.iqmain',
--rename NLSDBSPACE01_009 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_009.iqmain',
--rename NLSDBSPACE01_010 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_010.iqmain',
--rename NLSDBSPACE01_011 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_011.iqmain',
--rename NLSDBSPACE01_012 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_012.iqmain',
--rename NLSDBSPACE01_013 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_013.iqmain',
--rename NLSDBSPACE01_014 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_014.iqmain',
--rename NLSDBSPACE01_015 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_015.iqmain',
--rename NLSDBSPACE01_016 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_016.iqmain',
--rename NLSDBSPACE01_017 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_017.iqmain',
--rename NLSDBSPACE01_018 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_018.iqmain',
--rename NLSDBSPACE01_019 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_019.iqmain',
--rename NLSDBSPACE01_020 to '/u03/sybase/data/db1siq/db1siq_nlsdbspace01_020.iqmain',
--rename IQ_SYSTEM_TEMP_002 to '/u30/sybase/data/db1siq/db1siq_temp02.iqtmp',
--rename IQ_SYSTEM_TEMP_003 to '/u30/sybase/data/db1siq/db1siq_temp03.iqtmp',
--rename IQ_SYSTEM_TEMP_004 to '/u30/sybase/data/db1siq/db1siq_temp04.iqtmp',
--rename IQ_SYSTEM_TEMP_005 to '/u30/sybase/data/db1siq/db1siq_temp05.iqtmp',
--rename IQ_SYSTEM_TEMP_006 to '/u30/sybase/data/db1siq/db1siq_temp06.iqtmp',
--rename IQ_SYSTEM_TEMP_007 to '/u30/sybase/data/db1siq/db1siq_temp07.iqtmp',
--rename IQ_SYSTEM_TEMP_008 to '/u30/sybase/data/db1siq/db1siq_temp08.iqtmp',
--rename IQ_SYSTEM_TEMP_009 to '/u30/sybase/data/db1siq/db1siq_temp09.iqtmp',
--rename IQ_SYSTEM_TEMP_010 to '/u30/sybase/data/db1siq/db1siq_temp10.iqtmp',
--rename IQ_SYSTEM_TEMP_011 to '/u30/sybase/data/db1siq/db1siq_temp11.iqtmp',
--rename IQ_SYSTEM_TEMP_012 to '/u30/sybase/data/db1siq/db1siq_temp12.iqtmp',
--rename IQ_SYSTEM_TEMP_013 to '/u30/sybase/data/db1siq/db1siq_temp13.iqtmp',
--rename IQ_SYSTEM_TEMP_014 to '/u30/sybase/data/db1siq/db1siq_temp14.iqtmp',
--rename IQ_SYSTEM_TEMP_015 to '/u30/sybase/data/db1siq/db1siq_temp15.iqtmp',
--rename IQ_SYSTEM_TEMP_016 to '/u30/sybase/data/db1siq/db1siq_temp16.iqtmp',
--rename IQ_SYSTEM_TEMP_017 to '/u30/sybase/data/db1siq/db1siq_temp17.iqtmp',
--rename IQ_SYSTEM_TEMP_018 to '/u30/sybase/data/db1siq/db1siq_temp18.iqtmp',
--rename IQ_SYSTEM_TEMP_019 to '/u30/sybase/data/db1siq/db1siq_temp19.iqtmp',
--rename IQ_SYSTEM_TEMP_020 to '/u30/sybase/data/db1siq/db1siq_temp20.iqtmp',
--rename RLVDBSPACE01_001 to '/u03/sybase/data/db1siq/db1siq_rlvdbspace01_001.rlv',
--rename IQ_SYSTEM_LOG to 'IQ_SYSTEM_LOG',
--
4. OPT-Create the log for the instance if you are doing a rename of the instance
   dblog -t db1siq.log(instanceName.log) db1siq.db(databaseName)

5. verify the database
   dbisql -c "uid=DBA;pwd=SybaseMypass" -nogui "sp_iqcheckdb'verify database'"

-----------------------------------------------------------------------------
 To stop the control center(agent and server)
   scc.sh
         shutdown
-----------------------------------------------------------------------------

Creation of a database.
Setup profile:
vi .bash_profile

1. Create the database catalog.
   iqinit @iqinit_prod.parms /u20/sybase/data/db1siq/db1siq.db
   start_iq -STARTDIR '/u20/sybase/data/db1siq' @/u01/sybase/v160/db1siq_params.cfg db1siq.db  - start instance
   dbisql -c "uid=DBA;pwd=SybaseMypass" -nogui /u01/sybase/db1siq/ddldb/extend_db_prd.sql create the user db space
   dbisql -c "uid=DBA;pwd=SybaseMypass" -nogui /u01/sybase/db1siq/ddldb/config_opts.sql  - setup database options

-----------------------------------------------------------------------------
Utility
When renaming a database you must recreate the log.
dblog -t db1siq.log db1siq.db

-----------------------------------------------------------------------------
Start the database

 start_iq -STARTDIR '/u20/sybase/data/db1siq' @/u01/sybase/config/db1siq_params.cfg db1siq.db

 start_iq -STARTDIR '/u20/sybase/data/db1siq' @/u01/sybase/config/db1siq_params.cfg db1siq.db
-----------------------------------------------------------------------------

http://infocenter.sybase.com/help/index.jsp

http://www.digitaltechpoint.com/topic/1527-all-sap-books-list/

EDB785 - Sybase IQ Administration Vol I.pdf
EDB785 - Sybase IQ Administration Vol II.pdf

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00170.1510/pdf/iqapgv1.pdf

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.iq.15.1/title.htm

https://ebooksgenius.com/pdf/sybase-iq-15-4

http://download.sybase.com/pdfdocs/iqg1121e/iq_qr.pdf
-- ---------------------------------------------------------------------------------------------

Reference

sql anywhere
The Sybase IQ Survival Guide By Trevor Moore
https://www.google.com/search?q=check+sybase+iq+lock+and+transaction&ie=utf-8&oe=utf-8#q=site:books.google.com+The+Sybase+IQ+Survival+Guide+By+Trevor+Moore
https://books.google.com/books?id=fjU3AgAAQBAJ&pg=PA331&lpg=PA331&dq=%2B%2Bsp_iqwho&source=bl&ots=9sSyZGdoFC&sig=_pyBz4HeIgUAzRCNvS5optg92O8&hl=en&sa=X&ved=0CDoQ6AEwBGoVChMI0pDa3O2IxwIVBRYeCh0lfQZ0#v=onepage&q=%2B%2Bsp_iqwho&f=false

http://leonid-gvirtz.typepad.com/blog/sap-sybase-iq/
http://leonid-gvirtz.typepad.com/blog/sp_lg_print_table/

Best: SAP Sybase IQ Developer Center 

Sybase IQ Best Practice 
technotes:
http://www.sybase.com/detail_list?id=9808

database

create database

drop database 'usr/sap/home//sybase/customer_db/dev/customer.db'

sp_iqcheckdb
sp_iqcheckdb 'verify database' >& filename
sp_iqcheckdb 'verify database/table/index'

sa_db_info
select * from sa_db_info ();

sp_iqdbstatistics

http://www.sybase.com/detail?id=1067282

Determine the temporary file name:
SELECT Value from sa_db_properties() WHERE propname = 'TempFileName';
db_property('ConnCount');
sa_db_properties ># sa_db_properties.out Database property information

Obtain the current size of the temporary file, in bytes:
SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'temporary') * PROPERTY('PageSize');

sp_dboption
sp_iqcheckoptions --> display the database options that have been modified from their default values

sa_db_properties;
call sa_db_properties;
select * from sa_db_properties();

select PropName, Value from sa_db_properties();

sa_eng_properties ># sa_eng_properties.out Server property information
sa_eng_properties;
call sa_eng_properties;
select * from sa_eng_properties();


sa_server_option

variables and identity function

select @@version;
select @@servername;
select db_name();

SET OPTION "PUBLIC".MAIN_CACHE_MEMORY_MB = 200

sp_iqstatus --> view the current DBSpaces in a database
sp_iqspaceinfo

SET [ EXISTING ] [ TEMPORARY ] OPTION ... [ userid. | PUBLIC. ]option - name = [ option - value ]

set option quoted_identifier=off

set option public.WINIMIZE_STORAGE = 'ON';
set option public.Query_Plan = 'ON';
set option public.Query_Detail = 'ON';
set option public.Query_Plan_After_Run = 'ON';
set option public.Query_Plan_As_HTML = 'ON';
set option public.Query_Plan_As_HTML_Directory = /opt/sybase/TPCHDB/QueryPlans';
set option public.Query_Plan_Text_Access = 'ON';
set option public.Query_Plan_Text_Caching = 'ON';
set option public.Query_Timing = 'ON';
set option public.Index_Advisor = 'ON';

set temporary option Query_Plan = 'ON';
set temporary option Query_Plan_After_Run = 'ON';
set temporary option Query_Detail = 'ON';
set temporary option Query_Timing = 'ON';
set temporary option Query_Plan_As_HTML = 'ON';
set temporary option Index_Advisor = 'ON';
set temporary option Query_Name = '';
set temporary option Query_Plan_As_HTML_Directory = '';

SET temporary option Query_Plan_As_HTML = ;
SET option DBA.Query_Plan = ;
SET option PUBLIC.Query_Plan = ;

SET OPTION public.Force_No_Scroll_Cursors='on' ;
SET OPTION public.Force_No_Scroll_Cursors='off' ;

SET OPTION public.Query_temp_Space_Limit='0' ;

sp_iqcheckoptions --> display the database options that have been modified from their default values

select * from DBA.SYSOPTIONDEFAULTS

dbspace

sp_iqdbspaceinfo
sp_iqdbspace

sp_iqdbspaceinfo;
select * from sp_iqDBSpaceinfo();

system dbspace:
IQ_SYSTEM_MAIN
IQ_SYSTEM_TEMP 
IQ_SHARED_TEMP DBSpace

logical server policy option TEMP_DATA_IN_SHARED_TEMP
If TEMP_DATA_IN_SHARED_TEMP is 'OFF', objects go in IQ_SYSTEM_TEMP.
If TEMP_DATA_IN_SHARED_TEMP is set 'ON', objects go in IQ_SHARED_TEMP.

IQ_USER_MAIN
CREATE DBSPACE "IQ_USER_MAIN" USING FILE "IQ_USER_MAIN_1" '/dev/raw/raw1' SIZE 0 MB Resever 200 MB IQ STORE STRIPING OFF;
ALTER DBSPACE IQ_SYSTEM_TEMP ADD FILE customer_mpx_002_Temp_01 'mpx002_Temp_01.iqtmp' SIZE 500

ALTER DBSPACE UserDBSpace1
ADD
FILE UserFile2 '/data/file2' SIZE 6GB,
FILE UserFile3 '/data/file3' SIZE 6GB,
FILE UserFile4 '/data/file4' SIZE 6GB

sp_iqcheckdb 'allocation dbspace CUSTOMER_DBS'
sp_iqdbspaceobjectinfo

SET OPTION PUBLIC.DEFAULT_DBSPACE = 'user_DBSpace'
GRANT CREATE ON user_DBSpace TO PUBLIC
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM PUBLIC

table

describe nation 
sp_iqhelp nation ---> determine the structure of the nation table 
sp_iqspaceused 

select table_id, table_name from systab where create = '1'
select table_id, table_name from systab where creator = '1'

sp_iqtable
sp_iqtable;
sa_table_page_usage;

select * from systable;

sp_iqcheckdb 'allocation table nation' --> check a specific table

index and index type

Index Type: Fast Projection (FP): Default index generated by a CREATE TABLE statement. In Sybase
IQ, the data is stored as an FP index.

Compare (CMP): Stores the binary comparison (<, >, =, <=, > =, or !=) of two columns.

DATE: An index on columns of data type DATE.

Datetime (DTTM): An index on columns of data type DATETIME or TIMESTAMP.

High Group (HG): Used to process equality and group by operations on high cardinality data.

create HG index L_SHIPDATE_HG on TPCD.LINEITEM(L_SHIPDATE) in IQ_USER_MAIN;

High Non Group(HNG)Used for high cardinality data involving ranges or aggregates.

Low Fast (LF): Similar to the HG index , but for low cardinality data

TIME: An index on columns of data type TIME.

WD: Used to index distinct terms within the contents of a CHAR, VARCHAR, or LONG VARCHAR columns.

TEXT: Stores positional information for the terms in a character LOB data type. 

select Object, DbspaceName, ObjSize from sp_iqindexinfo ('table xyz') where Object like '%col1_indx%'
sp_iqrelocate 'my_index_name'  --> need more info
sp_iqindexinfo;
describe  index for table sys.sysuser
dbcc indexalloc
sp_iqcheckdb 'verify database/table/index'

select index_type, max_key from sysiqidx where table_id=733

sp_iqindexinfo;
sp_iqindexinfo

sp_iqindex
sp_index table_name='orders'

sp_iqindexmetadata 'ASIQ_IDX_T739_C4_FP'

dbcc indexalloc

sp_iqcheckdb 'check index n_regionkey_hg' --> check a specific index 
sp_iqcheckdb 'check indextype FP table nation resources 50' --> check  all indexes of a specific table

Users And Permission

# find user account status
call sa_get_user_status;


# user password
CREATE USER joe IDENTIFIED BY passwd;

-- change password
ALTER USER joe IDENTIFIED BY mypassword;

grant connect to joe identified by passwd;


-- unlock is user
1):
sp_locklogin 'dba', 'unlock'

2):
ALTER USER myusername RESET LOGIN POLICY

reset login policy to change password 


SELECT USER, today() FROM SYS.DUMMY

CREATE USER "OASISV12_EPC" LOGIN POLICY "MY_PROFILE_DEFAULT";

GRANT ROLE "PUBLIC" TO "OASISV12_EPC" WITH NO ADMIN OPTION;

grant select on my_schema.my_table to myuser;
grant delete on my_schema.my_table to myuser;
grant insert on my_schema.my_table to myuser;
grant update on my_schema.my_table to myuser;

SCC --> Security ==> Role-Based ==> Users

-- list all user status
select * from sys.sysuserlist;
select user_group, dbaauth, name from sys.sysuserlist order by user_group, dbaauth, name;

sa_get_user_status

describe sysuser;
describe sys.sysuser;

select su.name+'.'+so.name from sysobjects so, sysusers su where so.type='U' and so.uid=su.uid order by su.name, so.name;

select * from sysuser;

select top 3 * from sysuser where user_name is not null; --> nondetermisc warning
select top 3 * from sysuser where user_name is not null order by 1;

select user_name from sysuser where user_name is not null;

describe  table sys.sysuser
describe  index for table sys.sysuser

Step 1): find user account status

call sa_get_user_status;

step 2): unlock the user account

ALTER USER MVADLAPALLY RESET LOGIN POLICY

unlock user 

user

sysusers
select * from sysuser;

sa_get_user_status

-- list all user status
sa_get_user_status

EXEC sp_iqaddlogin 'my_user', 'pa55word'

GRANT CONNECT TO my_user IDENTIFIED BY 'password'
REVOKE CONNECT FROM my_user

CREATE USER my_user
EXEC sp_iqpassword 'DBA_PASSWORD','USERS_NEW_PASSWORD',my_user

CREATE USER my_user IDENTIFIED BY 'pa55word'

EXEC sp_iqlogin 'my_user'
grant connect to TPCD IDENTIFIED BY "":

create user Sheri identified by sybase;
grant connect to Lester identified by sybase;
sp_iqaddlogin 'Arnold', 'sybase';

grant membership in group readers to sheri
grant membership in group writers to Lester
grant membership in group Query_Dev to Arnold 

select * from sysuserperms; --> perms means permissions
select * from sysgroups;

sp_iqmpxvalidate

CREATE LOGIN POLICY locked_user_ids locked=ON
ALTER USER my_user LOGIN POLICY locked_user_ids
SP_IQMODIFYLOGIN 'user_id', 'login policy name'

sp_iqcopyloginpolicy '90day' 'developer'
ALTER LOGIN POLICY developer maxconnections=5;

SELECT su.user_name,lp.login_policy_name
FROM SYSUSER su
JOIN SYSLOGINPOLICY lp
ON (lp.login_policy_id = su.login_policy_id)

SELECT ID,
ct.score, Description
FROM MarketingInformation
CONTAINS ( MarketingInformation.Description,
'stretch* | comfort*' )
AS ct ORDER BY ct.score DESC;

load table TPCD.SUPPLIER (
S_SUPPKEY,
S_NAME,
S_ADDRESS,
S_NATIONKEY,
S_PHONE,
S_ACCTBAL,
S_COMMEN
T
)
from '/opt/sybase/TPCH_Data_Queries/TPCH/Data/supplier.tbl'
quotes off
escapes off
format ascii
delimited by '|'
row delimited by '\n'

create procedure sp_test()
begin
select * from my_table;
end;

call sp_test();

select * from sp_test();

begin
declare c1 int;
declare local temporary table #tmp1(ar bigint, lp bigint) in SYSTEM;
set c1 = 0;
lp: loop
set c1 = c1 + 1;
if c1 > 100 then
leave lp
end if;
insert #tmp1 select property('ActiveReq'), property('LockedHeapPages');
waitfor delay '00:00:05';
end loop;
select max(lp) as MaxLockedHeapPages,
max(ar) as MaxActiveReq ,
max(lp)/max(ar) as AvgPagesPerRequest
from #tmp1;
end;

sp_iqspaceinfo;

sp_iqdbstatistics

-- ----------------------------------------------


select * from sysfile;


Find out which directory where the temporary file was created in:
SELECT PROPERTY('TempDir');

SELECT top 1 *    FROM SYSDEPENDENCY,      SYSOBJECT,      SYSPROCPARM ORDER BY 1, 2, 3, 4, 5; 

SELECT * FROM SYSDEPENDENCY, SYSOBJECT, SYSPROCPARM;

sp_iqstatus

sp_iqwho

sp_iqconnection

sa_conn_info

sa_conn_properties

select * from SYSOPTIONS

select connection_property('Ansinull')

select connection_property('LOAD_MEMORY_MB')

select connection_property('DEDICATED_TASK')
set temporary option dba.dedicated_task = 'ON'

set option dba.load_memory_mb = 2

set temporary option isolation_level = 1
select connect_property('isolation_level')

select property('RequestLogFile'), property('RequestLogging');
call sa_server_option('request_level_logging', 'SQL');
call sa_server_option('request_level_log_file', 'sqllog.txt')

-- reset
call sa_server_option('request_level_log_file', '')
call sa_server_option('request_level_logging', '');

call sa_get_request_profile('sqllog.txt');

call satmp_request_profile;
call sa_get_request_times ( 'sqllog.txt' );

select * from satmp_request_time;

call sp_iqtransaction;

select * from sysobjects;

select * from sysobjects where name like '%d%b%';

select * from sysoptions;

SELECT * FROM sys.sysevent;

select * from sp_iqstatus();

systemtable

sysdatabases
sysuasages
syssegments
sysroles

sp_sysmon

sysprotects
sysaliases

dbcc tablealloc
dbcc checkalloc

dbcc CheckTable

dbcc checkdb

sp_plan_dbccdb

dbccdb

getiqinfo --> utility script
sp_iqstatus
select * form sp_iqstatus() where name like '%Backup%'
sp_iqcheckoptions

sp_iqlocks
sp_iqtransaction

sp_iqsysmon
sp_iqsysmon start_monitor, 'filemode', "-interval 5 -sections bufpool"
sp_iqsysmon stop_monitor

sa_dependent_views

sp_iqbqckupdetails

sysiqbqckuphistory
sysiqbackuphistorydetail

sp_iqbackupsummary
select * from SYSIQBACKUPHISTORY

backup database full to 'iqdemo_backup'
restore database 'iqdemo' from 'iqdemo_backup'

backup database incremental to 'iqdemo_backup_incremental'

backup database full virtual encapsulated 'dd if=iqdemo.iq of=iqdemo.iq.copy' to 'iqdemo_backup.full_virtual_encapes'
stop engine xxx
dd if=iqdemo.iq.copy of=iqdemo.iq
restore database 'iqdemo' from 'iqdemo_backup.full_virtual_encaps'

db_backupheader: Reads the backup archive and displays the DBspaces and DBFiles that existed when the backup was done.
sp_iqbackupsummary: Shows all the DBFiles included in a particular backup.
sp_iqbackupdetails: Summarizes the backup operations performed by a particular backup.
sp_iqrestoreaction : Lists the restore actions needed to bring a database to a consistent state for a given date.

dbbackup -y -d -c :uid=DBA;pwd=sql;links=tcpip{port=27-1};eng=wdlfbmt7029_customer_mpx_001" .

-- Reset the transaction log pointers in the DB file
dblog -r -t customer.log customer.db

sp_iqcopyloginpolicy
sp_iqemptyfile
sp_iqfile
sp_iqobjectinfo
sp_iqrestoreaction
sp_iqlmconfig

sp_iqmpxincconnpoolinfo
sp_iqmpxincheartbeatinfo

SET TEMPORARY OPTION QUERY_PLAN = 'ON'
SET TEMPORARY OPTION QUERY_DETAIL = 'ON'

SET TEMPORARY OPTION QUERY_PLAN_AFTER_RUN = 'ON'


sp_iqconnection 
sp_iqtransaction

sp_iqcheckoptions

sp_iqstatus 
select @@version

sa_conn_properties ># sa_conn_properties.out Connection information
sa_conn_info ># sa_conn_info.out Connection information

sp_iqstatus ># sp_iqstatus.out Database status information
sp_iqconnection ># sp_iqconnection.out Connection information
sp_iqtransaction ># sp_iqtransaction.out