답변1.

일단 isql 로 iq에 접속하기 위하여 iq 접속정보가 $SYBASE/interfaces

file 에 등록이 되어있어야 합니다.

vi 로 직접 입력하셔도 되고 dscp 라는 util을 사용하시면 됩니다.

 

 

$ dscp

>> open
ok
Session 1 InterfacesDriver>> add  asiqdemo -- iq db이름
Service: [ASE]   --- enter
Transport Type: [tcp]   -- enter  ( sun soraris 인경우 tli tcp)
Transport Address: 111.111.111.111 2638     -- iq 의 ip 및 port number
Transport Type: [tcp]    -- enter
Transport Address:   -- enter
Security Mechanism [] :   -- enter
HA Failoverserver:   -- enter
Retry Count:   -- enter
Retry Delay:   -- enter
Added asiqdemo2   -- enter

Session 1 InterfacesDriver>> quit
$

또는 vi

=======================================================

 vi $SYBASE/interfaces

asiqdemo

        master tcp ether 111 .111.111.111 2638
        query tcp ether 111.111.111.1111 2638

 

접속방법

isql -UDBA -PSQL -Sasiqdemo

* IQ 에접속하실때는 dbisql 또는 dbisqlc 사용을 권장합니다....

 

============================================================

답변 2

일단 sp_iqtable 은 하나의 table에대해서만 볼수 있지요 이것을

여러Table을 볼려면 sp_iqtable stored procedure를 수정해서

새로 하나 만들어서 사용하면 되는데

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

drop procedure ap_tablesize;

create procedure ap_tablesize
(
owner_pattern char(128),
table_pattern char(128)
)
 
begin
 
declare table_name char(128);
declare user_name char(128);
declare end_of_cursor exception for sqlstate value '02000';
 
 // List of the table concerned by the procedure
 declare local temporary table spt_iqtablesize_tablelist(
 user_name char(128) null,
 table_name char(128) null,
) in SYSTEM on commit preserve rows;
 
 // Cursor on the previous list
 declare magic_cursor dynamic scroll cursor for select user_name,table_name from spt_iqtablesize_tablelist;
 
 // Temporary table to store the number of blocks used by each table
 declare local temporary table spt_iqtablesize_size(
 Ownername varchar(128) null,
 Tablename varchar(128) null,
 Columns char(20) null,
 KBytes char(20) null,
 Pages char(20) null,
 CompressedPages char(20) null,
 NBlocks char(20) null,
) in SYSTEM on commit preserve rows;
 

 // Temporary table to store the number of rows in each table
 declare local temporary table spt_iqtablesize_count(
 Ownername varchar(128) null,
 Tablename varchar(128) null,
 NRows integer null,
) in SYSTEM on commit preserve rows;

 
 //  Fill spt_iqtablesize_tablelist table
execute immediate 'insert into spt_iqtablesize_tablelist
SELECT user_name, table_name
FROM sys.systable, sys.sysuserperm
WHERE sys.systable.creator = sys.sysuserperm.user_id
AND user_name <> ''SYS''
AND server_type = ''IQ''
AND table_name LIKE ''' || table_pattern || '''
AND user_name LIKE ''' || owner_pattern || ''''
;

  IF @@Rowcount =0 THEN
     select ' No table found -     usage : ap_tablesize ''table_pattern(p%)'' ' Error_message
  ELSE
 
 open magic_cursor;
 
magic_loop:
loop
   fetch next magic_cursor into user_name,table_name;
   if sqlstate = end_of_cursor then leave magic_loop
   end if;
  
   execute immediate 'IQ UTILITIES MAIN INTO spt_iqtablesize_size TABLE SIZE ' || user_name || '.' || table_name;

 
   execute immediate 'INSERT INTO spt_iqtablesize_count (Ownername, Tablename, NRows)
                    SELECT ''' || user_name || ''',''' || table_name || ''',count(*) FROM ' || user_name || '.' || table_name

 end loop magic_loop;
 
 close magic_cursor;

 
 // Here are finally the results
 select
    spt_iqtablesize_size.Ownername,
    spt_iqtablesize_size.Tablename,
    Columns,
    KBytes,
    Pages,
    CompressedPages,
    NRows
from
 spt_iqtablesize_size,spt_iqtablesize_count
where spt_iqtablesize_size.Ownername = spt_iqtablesize_count.Ownername
    and spt_iqtablesize_size.Tablename = spt_iqtablesize_count.Tablename
order by     spt_iqtablesize_size.Ownername,    spt_iqtablesize_size.Tablename

 

/* table row 수까지 보시려면 밑에 부분을 풀어서 사용하세요 */
/*
 select
    spt_iqtablesize_size.Ownername,
    spt_iqtablesize_size.Tablename,
    Columns,
    KBytes,
    Pages,
    CompressedPages
from
 spt_iqtablesize_size
order by     spt_iqtablesize_size.Ownername,    spt_iqtablesize_size.Tablename
*/

 

END IF;
 
 //  Clean up the mess
drop table spt_iqtablesize_size;
drop table spt_iqtablesize_count;
drop table spt_iqtablesize_tablelist
 
end ;

사용방법 ap_tablesize 'owner_pattern','tablename_pattern'

예)  dba가 생성한 모든 table의 size 를 보고자 할경우

  ap_tablesize 'dba','%'