Ingres Q & A
Here is a sample script that you can run against a $ingres owned
database to play with IMA.
--
Jurgen Coetsiers |Tel: + 32 2 773 28 44 _/_/_/ _/_/_/ _/_/_/
Technical Consultant |Fax: + 32 2 762 73 59 _/ _/ _/ _/
Computer Associates n.v.|privé: Waverstraat 2A _/ _/ _/ _/
Woluwedal 34, bus 13 | B-9310 Moorsel _/ _/ _/ _/
1200 Brussels | + 32 53 70 35 04 _/ _/ _/ _/
Belgium | _/_/_/ _/_/_/ _/_/_/
/*
** Table registrations for the IMA sample Schema
**
** History:
**
** 30-Apr-95 (johna)
** Created as a simplified (and clearer) version of the
** schema from the earlier IMA-based IMP tools.
**
** 15-May-95 (johna)
** Used 'set session authorization' to prevent the need
** for the -u flag. Also added ima_gca_connections.
*/
set autocommit on;
\p\g
set session authorization '$ingres';
\p\g
/*
** IMA_MIB_OBJECTS - 'flat' table required for updates/procedure support
*/
drop ima_mib_objects;
\p\g
register table ima_mib_objects (
server varchar(64) not null not default is 'SERVER',
classid varchar(64) not null not default is 'CLASSID',
instance varchar(64) not null not default is 'INSTANCE',value
varchar(64) not null not default is 'VALUE',
perms integer2 not null not default is 'PERMISSIONS'
)
as import from 'objects'
with update,
dbms = IMA,
structure = unique sortkeyed,
key = (server, classid, instance);
\p\g
/*
** IMA_MO_META - 'metadata' cross tab
*/
drop ima_mo_meta;
\p\g
register table ima_mo_meta (
server varchar(64) not null not default is
'SERVER',
classid varchar(64) not null not default is
'exp.glf.mo.meta.classid',
class varchar(64) not null not default is
'exp.glf.mo.meta.class',
oid varchar(8) not null not default is
'exp.glf.mo.meta.oid',
perms integer2 not null not default is
'exp.glf.mo.meta.perms',
size integer2 not null not default is
'exp.glf.mo.meta.size',
xindex varchar(64) not null not default is
'exp.glf.mo.meta.index'
) as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (server, classid);
/*
** IMA_DBMS_SERVERS - those servers which support iimonitor connection
*/
drop ima_dbms_servers;
\p\g
register table ima_dbms_servers (
server varchar(64) not null not default is
'SERVER',
listen_address varchar(64) not null not default is
'exp.gcf.gca.listen_address',
max_connections integer4 not null not default is
'exp.scf.scd.server.max_connections',
num_connections integer4 not null not default is
'exp.scf.scd.server.current_connections',
highwater_connections integer4 not null not default is
'exp.scf.scd.server.highwater_connections',
server_pid integer4 not null not default is
'exp.scf.scd.server.pid'
)
as import from 'tables'
with dbms = IMA,
structure = sortkeyed,
key = (server);
\p\g
/*
** IMA_GCN_REGISTRATIONS - all INGRES processes registered with the IIGCN
*/
drop ima_gcn_registrations;
\p\g
register table ima_gcn_registrations (
name_server varchar(64) not null not default is
'SERVER',
listen_address varchar(64) not null not default is
'exp.gcf.gcn.server.address',
server_class varchar(32) not null not default is
'exp.gcf.gcn.server.class',
server_dblist varchar(32) not null not default is
'exp.gcf.gcn.server.object'
)
as import from 'tables'
with dbms = IMA,
structure = sortkeyed,
key = (name_server);
\p\g
/*
** IMA_REGISTERED_SERVERS - view on IMA_DBMS_SERVERS and
** IMA_GCN_REGISTRATIONS
*/
drop view ima_registered_servers;
\p\g
create view ima_registered_servers as
select
name_server,
ima_dbms_servers.listen_address,
server_class,
max_connections as max_sessions,
num_connections as num_sessions,
server_dblist,
server_pid
from
ima_gcn_registrations,
ima_dbms_servers
where
server_class not in ('IINMSV','COMSVR')
and ima_gcn_registrations.listen_address =
ima_dbms_servers.listen_address
union
select
name_server,
listen_address,
server_class,
max_sessions = 0,
num_sessions = 0,
server_dblist,
server_pid = 0
from
ima_gcn_registrations
where
server_class in ('IINMSVR','COMSVR');
\p\g
/*
** IMA_SERVER_SESSIONS - information about connected and system sessions
*/
drop table ima_server_sessions;
\p\g
register table ima_server_sessions (
server varchar(64) not null not default is
'SERVER',
session_id varchar(32) not null not default is
'exp.scf.scs.scb_index',
effective_user varchar(32) not null not default is
'exp.scf.scs.scb_euser',
real_user varchar(32) not null not default is
'exp.scf.scs.scb_ruser',
db_name varchar(12) not null not default is
'exp.scf.scs.scb_database',
db_owner varchar(32) not null not default is
'exp.scf.scs.scb_dbowner',
db_lock varchar(9) not null not default is
'exp.scf.scs.scb_dblockmode',
server_facility varchar(10) not null not default is
'exp.scf.scs.scb_facility_name',
session_activity varchar(80) not null not default is
'exp.scf.scs.scb_activity',
activity_detail varchar(80) not null not default is
'exp.scf.scs.scb_act_detail',
session_query varchar(1000) not null not default is
'exp.scf.scs.scb_query',
session_terminal varchar(12) not null not default is
'exp.scf.scs.scb_terminal',
session_group varchar(8) not null not default is
'exp.scf.scs.scb_group',
session_role varchar(8) not null not default is
'exp.scf.scs.scb_role',
server_pid integer4 not null not default is
'exp.scf.scs.scb_pid'
)
as import from 'tables'
with dbms = IMA, structure = unique sortkeyed,
key = (server, session_id)
\p\g
/*
** IMA_locklists - each session maintains a locklist of active locks
*/
drop ima_locklists;
\p\g
register table ima_locklists (
vnode varchar(64) not null not default is
'VNODE',
locklist_id_id integer4 not null not default is
'exp.dmf.lk.llb_id.id_id',
locklist_id_instance integer4 not null not default is
'exp.dmf.lk.llb_id.id_instance',
locklist_lkb_count integer4 not null not default is
'exp.dmf.lk.llb_lkb_count',
locklist_status varchar(50) not null not default is
'exp.dmf.lk.llb_status',
locklist_lock_count integer4 not null not default is
'exp.dmf.lk.llb_llkb_count',
locklist_max_locks integer4 not null not default is
'exp.dmf.lk.llb_max_lkb',
locklist_wait_id_id integer4 not null not default is
'exp.dmf.lk.llb_wait_id_id',
locklist_name0 integer4 not null not default is
'exp.dmf.lk.llb_name0',
locklist_name1 integer4 not null not default is
'exp.dmf.lk.llb_name1',
locklist_server_pid integer4 not null not default is
'exp.dmf.lk.llb_pid',
locklist_session_id varchar(32) not null not default is
'exp.dmf.lk.llb_sid',
locklist_timeout integer4 not null not default is
'exp.dmf.lk.llb_timeout'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (vnode, locklist_id_id);
\p\g
/*
** IMA_LOCKS - individual locks on a locklist
*/
drop ima_locks;
\p\g
register table ima_locks (
vnode varchar(64) not null not default is
'VNODE',
lock_id_id integer4 not null not default is
'exp.dmf.lk.lkb_id.id_id',
lock_id_instance integer4 not null not default is
'exp.dmf.lk.lkb_id.id_instance',
lock_request_mode varchar(3) not null not default is
'exp.dmf.lk.lkb_request_mode',
lock_grant_mode varchar(3) not null not default is
'exp.dmf.lk.lkb_grant_mode',
lock_state varchar(20) not null not default is
'exp.dmf.lk.lkb_state',
lock_attributes varchar(30) not null not default is
'exp.dmf.lk.lkb_attribute',
resource_id_id integer4 not null not default is
'exp.dmf.lk.lkb_rsb_id_id',
locklist_id_id integer4 not null not default is
'exp.dmf.lk.lkb_llb_id_id'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (vnode, lock_id_id);
\p\g
/*
** IMA_RESOURCES - things which may be locked (db, table, page etc)
*/
drop ima_resources;
\p\g
register table ima_resources (
vnode varchar(64) not null not default is
'VNODE',
resource_id_id integer4 not null not default is
'exp.dmf.lk.rsb_id.id_id',
resource_id_instance integer4 not null not default is
'exp.dmf.lk.rsb_id.id_instance',
resource_grant_mode varchar(3) not null not default is
'exp.dmf.lk.rsb_grant_mode',
resource_key varchar(30) not null not default is
'exp.dmf.lk.rsb_name',
resource_type integer4 not null not default is
'exp.dmf.lk.rsb_name0',
resource_database_id integer4 not null not default is
'exp.dmf.lk.rsb_name1',
resource_table_id integer4 not null not default is
'exp.dmf.lk.rsb_name2',
resource_index_id integer4 not null not default is
'exp.dmf.lk.rsb_name3',
resource_page_number integer4 not null not default is
'exp.dmf.lk.rsb_name4'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (vnode, resource_id_id);
\p\g
/*
** IMA_LOG_PROCESSES - the INGRES processes active in the logging system
*/
drop ima_log_processes;
\p\g
register table ima_log_processes (
vnode varchar(64) not null not default is
'VNODE',
process_id_id integer4 not null not default is
'exp.dmf.lg.lpb_id.id_id',
process_id_instance integer4 not null not default is
'exp.dmf.lg.lpb_id.id_instance',
process_status varchar(200) not null not default is
'exp.dmf.lg.lpb_status',
process_pid integer4 not null not default is
'exp.dmf.lg.lpb_pid'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (vnode, process_id_id);
\p\g
/*
** IMA_LOG_DATABASES - databases active in the logging system
*/
drop ima_log_databases;
\p\g
register table ima_log_databases (
vnode varchar(64) is
'VNODE',
db_id_id integer4 not null not default is
'exp.dmf.lg.ldb_id.id_id',
db_id_instance integer4 not null not default is
'exp.dmf.lg.ldb_id.id_instance',
db_status varchar(100) not null not default is
'exp.dmf.lg.ldb_status',
db_database_id integer4 not null not default is
'exp.dmf.lg.ldb_database_id',
db_name varchar(32) not null not default is
'exp.dmf.lg.ldb_db_name',
db_owner varchar(32) not null not default is
'exp.dmf.lg.ldb_db_owner'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (vnode, db_id_id);
\p\g
/*
** IMA_LOG_TRANSACTIONS - transactions in the logging system
*/
drop ima_log_transactions;
\p\g
register table ima_log_transactions (
vnode varchar(64) not null not default is
'VNODE',
tx_id_id integer4 not null not default is
'exp.dmf.lg.lxb_id.id_id',
tx_id_instance integer4 not null not default is
'exp.dmf.lg.lxb_id.id_instance',
tx_status varchar(200) not null not default is
'exp.dmf.lg.lxb_status',
tx_db_id_id integer4 not null not default is
'exp.dmf.lg.lxb_db_id_id',
tx_db_name varchar(32) not null not default is
'exp.dmf.lg.lxb_db_name',
tx_db_owner varchar(32) not null not default is
'exp.dmf.lg.lxb_db_owner',
tx_pr_id_id integer4 not null not default is
'exp.dmf.lg.lxb_pr_id_id',
tx_wait_reason varchar(16) not null not default is
'exp.dmf.lg.lxb_wait_reason',
tx_first_log_address varchar (32) not null not default is
'exp.dmf.lg.lxb_first_lga',
tx_last_log_address varchar (32) not null not default is
'exp.dmf.lg.lxb_last_lga',
tx_transaction_id varchar(32) not null not default is
'exp.dmf.lg.lxb_tran_id',
tx_transaction_high integer4 not null not default is
'exp.dmf.lg.lxb_tran_id.db_high_tran',
tx_transaction_low integer4 not null not default is
'exp.dmf.lg.lxb_tran_id.db_low_tran',
tx_server_pid integer4 not null not default is
'exp.dmf.lg.lxb_pid',
tx_session_id varchar(32) not null not default is
'exp.dmf.lg.lxb_sid',
tx_user_name varchar(32) not null not default is
'exp.dmf.lg.lxb_user_name'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (vnode, tx_id_id);
\p\g
/*
** IMA_CLIENT_INFO - information about the client process.
*/
drop ima_client_info;
\p\g
register table ima_client_info (
server varchar(64) not null not default
is 'SERVER',
session_id varchar(32) not null not default
is 'exp.scf.scs.scb_index',
client_host varchar(20) not null not default
is 'exp.scf.scs.scb_client_host',
client_pid varchar(20) not null not default
is 'exp.scf.scs.scb_client_pid',
client_terminal varchar(20) not null not default
is 'exp.scf.scs.scb_client_tty',
client_user varchar(32) not null not default
is 'exp.scf.scs.scb_client_user',
client_connect_string varchar(64) not null not default
is 'exp.scf.scs.scb_client_connect',
client_info varchar(64) not null not default
is 'exp.scf.scs.scb_client_info'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (server, session_id)
\p\g
/*
** IMA_GCA_CONNECTIONS - information about active GCA connections
*/
drop ima_gca_connections;
\p\g
register table ima_gca_connections (
server varchar(64) not null not default
is 'SERVER',
assoc_id integer4 not null not default
is 'exp.gcf.gca.assoc',
assoc_flags varchar(30) not null not default
is 'exp.gcf.gca.assoc.flags',
partner_protocol integer4 not null not default
is 'exp.gcf.gca.assoc.partner_protocol',
session_protocol integer4 not null not default
is 'exp.gcf.gca.assoc.session_protocol',
assoc_userid varchar(32) not null not default
is 'exp.gcf.gca.assoc.userid'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (server, assoc_id)
\p\g
/*
** IMA_DI_SLAVE_INFO - information about active slave processes
*/
drop ima_di_slave_info;
\p\g
register table ima_di_slave_info (
server varchar(64) not null not default
is 'SERVER',
di_slaveno integer4 not null not default
is 'exp.clf.di.di_slaveno',
di_dio integer4 not null not default
is 'exp.clf.di.dimo_dio',
cpu_tm_msecs integer4 not null not default
is 'exp.clf.di.dimo_cpu.tm_msecs',
cpu_tm_secs integer4 not null not default
is 'exp.clf.di.dimo_cpu.tm_secs',
di_idrss integer4 not null not default
is 'exp.clf.di.dimo_idrss',
di_maxrss integer4 not null not default
is 'exp.clf.di.dimo_maxrss',
di_majflt integer4 not null not default
is 'exp.clf.di.dimo_majflt',
di_minflt integer4 not null not default
is 'exp.clf.di.dimo_minflt',
di_msgrcv integer4 not null not default
is 'exp.clf.di.dimo_msgrcv',
di_msgsnd integer4 not null not default
is 'exp.clf.di.dimo_msgsnd',
di_msgtotal integer4 not null not default
is 'exp.clf.di.dimo_msgtotal',
di_nivcsw integer4 not null not default
is 'exp.clf.di.dimo_nivcsw',
di_nsignals integer4 not null not default
is 'exp.clf.di.dimo_nsignals',
di_nvcsw integer4 not null not default
is 'exp.clf.di.dimo_nvcsw',
di_nswap integer4 not null not default
is 'exp.clf.di.dimo_nswap',
di_reads integer4 not null not default
is 'exp.clf.di.dimo_reads',
di_writes integer4 not null not default
is 'exp.clf.di.dimo_writes',
di_stime_tm_msecs integer4 not null not default
is 'exp.clf.di.dimo_stime.tm_msecs',
di_stime_tm_secs integer4 not null not default
is 'exp.clf.di.dimo_cpu.tm_secs',
di_utime_tm_msecs integer4 not null not default
is 'exp.clf.di.dimo_utime.tm_msecs',
di_utime_tm_secs integer4 not null not default
is 'exp.clf.di.dimo_utime.tm_secs'
)
as import from 'tables'
with dbms = IMA,
structure = unique sortkeyed,
key = (server, di_slaveno)
\p\g
/*
** IMA_DMF_CACHE_STATS - information about the DMF cache
*/
drop ima_dmf_cache_stats;
\p\g
register table ima_dmf_cache_stats (
server varchar(64) not null not default
is 'SERVER',
/* cumulative stats */
cache_wait_count integer4 not null not default
is 'exp.dmf.dm0p.bm_bmcwait',
fc_flush_count integer4 not null not default
is 'exp.dmf.dm0p.bm_fcflush',
force_count integer4 not null not default
is 'exp.dmf.dm0p.bm_force',
io_wait_count integer4 not null not default
is 'exp.dmf.dm0p.bm_iowait',
cache_lock_reclaims integer4 not null not default
is 'exp.dmf.dm0p.bm_lockreclaim',
mutex_wait_count integer4 not null not default
is 'exp.dmf.dm0p.bm_mwait',
group_buffer_read_count integer4 not null not default
is 'exp.dmf.dm0p.bm_greads',
group_buffer_write_count integer4 not null not default
is 'exp.dmf.dm0p.bm_gwrites',
fix_count integer4 not null not default
is 'exp.dmf.dm0p.bm_fix',
unfix_count integer4 not null not default
is 'exp.dmf.dm0p.bm_unfix',
read_count integer4 not null not default
is 'exp.dmf.dm0p.bm_reads',
write_count integer4 not null not default
is 'exp.dmf.dm0p.bm_writes',
hit_count integer4 not null not default
is 'exp.dmf.dm0p.bm_hit',
dirty_unfix_count integer4 not null not default
is 'exp.dmf.dm0p.bm_dirty',
pages_still_valid integer4 not null not default
is 'exp.dmf.dm0p.bm_check',
pages_invalid integer4 not null not default
is 'exp.dmf.dm0p.bm_refresh',
/* various cache info */
db_cache_entry_count integer4 not null not default
is 'exp.dmf.dm0p.bm_dbcsize',
buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_bufcnt',
page_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_sbufcnt',
flimit integer4 not null not default
is 'exp.dmf.dm0p.bm_flimit',
mlimit integer4 not null not default
is 'exp.dmf.dm0p.bm_mlimit',
wbstart integer4 not null not default
is 'exp.dmf.dm0p.bm_wbstart',
wbend integer4 not null not default
is 'exp.dmf.dm0p.bm_wbend',
wbflush_count integer4 not null not default
is 'exp.dmf.dm0p.bm_wbflush',
hash_bucket_count integer4 not null not default
is 'exp.dmf.dm0p.bm_hshcnt',
/* group buffer info */
group_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_gcnt',
group_buffer_size integer4 not null not default
is 'exp.dmf.dm0p.bm_gpages',
/* current stats */
connected_server_count integer4 not null not default
is 'exp.dmf.dm0p.bm_srv_count',
cache_status integer4 not null not default
is 'exp.dmf.dm0p.bm_status',
/*
** BM_GROUP 0x0001
** BM_FWAIT 0x0002
** BM_WBWAIT 0x0004
** BM_FCFLUSH 0x0008
** BM_SHARED_BUFMGR 0x0010
** BM_PASS_ABORT 0x0020
** BM_PREPASS_ABORT 0x0040
*/
free_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_fcount',
free_buffer_waiters integer4 not null not default
is 'exp.dmf.dm0p.bm_fwait',
fixed_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_lcount',
modified_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_mcount',
free_group_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_gfcount',
fixed_group_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_glcount',
modified_group_buffer_count integer4 not null not default
is 'exp.dmf.dm0p.bm_gmcount',
/* rapidly changing stats about CPs */
current_cp_id integer4 not null not default
is 'exp.dmf.dm0p.bm_cpcount',
cp_flush_index integer4 not null not default
is 'exp.dmf.dm0p.bm_cpindex',
cp_threads_complete integer4 not null not default
is 'exp.dmf.dm0p.bm_cpcheck'
)
as import from 'tables'
with dbms = IMA,
structure = sortkeyed,
key = (server)
\p\g
/*
** IMA_QSF_CACHE_STATS - information about the QSF cache
*/
drop ima_qsf_cache_stats;
\p\g
register table ima_qsf_cache_stats (
server varchar(64) not null not default
is 'SERVER',
qsf_bkts_used integer4 not null not default
is 'exp.qsf.qsr.qsr_bkts_used',
qsf_bmaxobjs integer4 not null not default
is 'exp.qsf.qsr.qsr_bmaxobjs',
qsf_decay_factor integer4 not null not default
is 'exp.qsf.qsr.qsr_decay_factor',
qsf_memleft integer4 not null not default
is 'exp.qsf.qsr.qsr_memleft',
qsf_memtot integer4 not null not default
is 'exp.qsf.qsr.qsr_memtot',
qsf_mx_index integer4 not null not default
is 'exp.qsf.qsr.qsr_mx_index',
qsf_mx_named integer4 not null not default
is 'exp.qsf.qsr.qsr_mx_named',
qsf_mx_rsize integer4 not null not default
is 'exp.qsf.qsr.qsr_mx_rsize',
qsf_mx_size integer4 not null not default
is 'exp.qsf.qsr.qsr_mx_size',
qsf_mx_unnamed integer4 not null not default
is 'exp.qsf.qsr.qsr_mx_unnamed',
qsf_mxbkts_used integer4 not null not default
is 'exp.qsf.qsr.qsr_mxbkts_used',
qsf_mxobjs integer4 not null not default
is 'exp.qsf.qsr.qsr_mxobjs',
qsf_mxsess integer4 not null not default
is 'exp.qsf.qsr.qsr_mxsess',
qsf_named_requests integer4 not null not default
is 'exp.qsf.qsr.qsr_named_requests',
qsf_nbuckets integer4 not null not default
is 'exp.qsf.qsr.qsr_nbuckets',
qsf_no_destroyed integer4 not null not default
is 'exp.qsf.qsr.qsr_no_destroyed',
qsf_no_index integer4 not null not default
is 'exp.qsf.qsr.qsr_no_index',
qsf_no_named integer4 not null not default
is 'exp.qsf.qsr.qsr_no_named',
qsf_no_unnamed integer4 not null not default
is 'exp.qsf.qsr.qsr_no_unnamed',
qsf_nobjs integer4 not null not default
is 'exp.qsf.qsr.qsr_nobjs',
qsf_nsess integer4 not null not default
is 'exp.qsf.qsr.qsr_nsess'
)
as import from 'tables'
with dbms = IMA,
structure = sortkeyed,
key = (server);
\p\g
/*
** IMA_QSF_DBP - information about database procedures in QSF
*/
drop ima_qsf_dbp;
\p\g
register table ima_qsf_dbp (
server varchar(64) not null not default
is 'SERVER',
dbp_index integer4 not null not default
is 'exp.qsf.qso.dbp.index',
dbp_name varchar(60) not null not default
is 'exp.qsf.qso.dbp.name',
dbp_owner varchar(24) not null not default
is 'exp.qsf.qso.dbp.owner',
dbp_size integer4 not null not default
is 'exp.qsf.qso.dbp.size',
dbp_dbid integer4 not null not default
is 'exp.qsf.qso.dbp.udbid',
dbp_usage_count integer4 not null not default
is 'exp.qsf.qso.dbp.usage'
) as import from 'tables'
with dbms = ima,
structure = unique sortkeyed,
key = (server, dbp_index);
/*
** IMA_QSF_RQP - information about repeat queries in QSF
*/
drop ima_qsf_rqp;
\p\g
register table ima_qsf_rqp (
server varchar(64) not null not default
is 'SERVER',
rqp_index integer4 not null not default
is 'exp.qsf.qso.rqp.index',
rqp_name varchar(60) not null not default
is 'exp.qsf.qso.rqp.name',
rqp_size integer4 not null not default
is 'exp.qsf.qso.rqp.size',
rqp_dbid integer4 not null not default
is 'exp.qsf.qso.rqp.udbid',
rqp_usage_count integer4 not null not default
is 'exp.qsf.qso.rqp.usage'
) as import from 'tables'
with dbms = ima,
structure = unique sortkeyed,
key = (server, rqp_index);
/*
** IMA_MEMORY_INFO - information about memory allocation in the server
*/
drop ima_memory_info;
\p\g
register table ima_memory_info (
server varchar(64) not null not default
is 'SERVER',
bytes_used integer4 not null not default
is 'exp.clf.unix.me.num.bytes_used',
free_pages integer4 not null not default
is 'exp.clf.unix.me.num.free_pages',
get_pages integer4 not null not default
is 'exp.clf.unix.me.num.get_pages',
pages_used integer4 not null not default
is 'exp.clf.unix.me.num.pages_used'
) as import from 'tables'
with dbms = ima,
structure = sortkeyed,
key = (server);
/*
** IMA_RDF_CACHE_INFO - information about the state of the RDF cache
*/
drop ima_rdf_cache_info;
\p\g
register table ima_rdf_cache_info (
server varchar(64) not null not default
is 'SERVER',
state_num integer4 not null not default
is 'exp.rdf.rdi.state_num',
state_string varchar(60) not null not default
is 'exp.rdf.rdi.state_str',
cache_size integer4 not null not default
is 'exp.rdf.rdi.cache.size',
max_tables integer4 not null not default
is 'exp.rdf.rdi.cache.max_tables',
max_qtrees integer4 not null not default
is 'exp.rdf.rdi.cache.max_qtree_objs',
max_ldb_descs integer4 not null not default
is 'exp.rdf.rdi.cache.max_ldb_descs',
max_defaults integer4 not null not default
is 'exp.rdf.rdi.cache.max_defaults'
) as import from 'tables'
with dbms = ima,
structure = sortkeyed,
key = (server);
/*
** DATABASE PROCEDURES:
**
** ima_set_vnode_domain() - set the IMA domain to the current vnode
** ima_set_server_domain() - reset the IMA domain to the current server only** ima_expand_domain() - expand the IMA domain to include the argument
** ima_contract_domain() - contract the IMA domain to exclude the argument
**
** ima_collect_slave_stats() - collect slave performance statistics
*/
drop procedure ima_set_vnode_domain;
\p\g
create procedure ima_set_vnode_domain as
begin
update ima_mib_objects set value =dbmsinfo('ima_vnode')
where classid = 'exp.gwf.gwm.session.control.add_vnode'
and instance = '0'
and server = dbmsinfo('ima_server');
end;
\p\g
drop procedure ima_set_server_domain;
\p\g
create procedure ima_set_server_domain as
begin
update ima_mib_objects set value = ' '
where classid = 'exp.gwf.gwm.session.control.reset_domain'
and instance = '0'
and server = dbmsinfo('ima_server');
end;
\p\g
drop procedure ima_expand_domain;
\p\g
create procedure ima_expand_domain(entry varchar(30) not null) as
begin
update ima_mib_objects set value = :entry
where classid = 'exp.gwf.gwm.session.control.add_vnode'
and instance = '0'
and server = dbmsinfo('ima_server');
end;
\p\g
drop procedure ima_contract_domain
\p\g
create procedure ima_contract_domain(entry varchar(30) not null) as
begin
update ima_mib_objects set value = :entry
where classid = 'exp.gwf.gwm.session.control.del_vnode'
and instance = '0'
and server = dbmsinfo('ima_server');
end;
\p\g
/*
** PROCEDURE IMA_COLLECT_SLAVE_STATS - forces each slave to make a system
** call to update usage statistics
*/
drop procedure ima_collect_slave_stats;
\p\g
create procedure ima_collect_slave_stats(server varchar(30) not null) as
begin
update ima_mib_objects set value = '1'
where classid = 'exp.clf.di.dimo_collect'
and instance = '0'
and server = :server;
end;
\p\g
grant select on ima_mib_objects to public;
\p\g
grant select on ima_mo_meta to public;
\p\g
grant select on ima_dbms_servers to public;
\p\g
grant select on ima_gcn_registrations to public;
\p\g
grant select on ima_registered_servers to public;
\p\g
grant select on ima_server_sessions to public;
\p\g
grant select on ima_locklists to public;
\p\g
grant select on ima_locks to public;
\p\g
grant select on ima_resources to public;
\p\g
grant select on ima_log_processes to public;
\p\g
grant select on ima_log_databases to public;
\p\g
grant select on ima_log_transactions to public;
\p\g
grant select on ima_client_info to public;
\p\g
grant select on ima_gca_connections to public;
\p\g
grant select on ima_di_slave_info to public;
\p\g
grant select on ima_dmf_cache_stats to public;
\p\g
grant select on ima_qsf_cache_stats to public;
\p\g
grant select on ima_qsf_dbp to public;
\p\g
grant select on ima_qsf_rqp to public;
\p\g
grant select on ima_memory_info to public;
\p\g
grant select on ima_rdf_cache_info to public;
\p\g
grant execute on procedure ima_set_vnode_domain to public;
\p\g
grant execute on procedure ima_set_server_domain to public;
\p\g
grant execute on procedure ima_expand_domain to public;
\p\g
grant execute on procedure ima_contract_domain to public;
\p\g
grant execute on procedure ima_collect_slave_stats to public;
\p\g
Ingres Q & A
To William's Home Page
© William Yuan 2000
Email William