Customer Service Center

The following SQL commands will provide a count of the qscope_5m_xxx, qscope_1h_xxx, qscope_1d_xxx tables in each of the dbspaces. 

Note: The dbspaces on your instance may differ, check 'onstat -d' for correct dbspace names and substitute as needed.

To get count of the qscope_5m_(xxx) tables in the scout5m(y) dbspaces:

select C.name, count(unique B.tabname) as tabnam from sysmaster:'informix'.sysdbspaces C, OUTER sysmaster:'informix'.systabnames B where C.name in ("scout5m","scout5m1","scout5m2","scout5m3","scout5m4","scout5m5","scout5m6","scout5m7","scout5m8","scout5m9") and tabname like 'qscope_%' and DBINFO('dbspace',B.partnum)=C.name group by 1 into temp tempfile1;

select * from tempfile1 order by 2 DESC;

To get count of the qscope_1h_(xxx) tables in the scout1h(y) dbspaces:

select C.name, count(unique B.tabname) as tabnam from sysmaster:'informix'.sysdbspaces C, OUTER sysmaster:'informix'.systabnames B where C.name in ("scout1h","scout1h1","scout1h2","scout1h3","scout1h4","scout1h5","scout1h6","scout1h7","scout1h8","scout1h9") and tabname like 'qscope_%' and DBINFO('dbspace',B.partnum)=C.name group by 1 into temp tempfile2;

select * from tempfile2 order by 2 DESC;

To get count of the qscope_1d_(xxx) tables in the scout1d(y) dbspaces:

select C.name, count(unique B.tabname) as tabnam from sysmaster:'informix'.sysdbspaces C, OUTER sysmaster:'informix'.systabnames B where C.name in ("scout1d","scout1d1","scout1d2","scout1d3","scout1d4","scout1d5","scout1d6","scout1d7","scout1d8","scout1d9") and tabname like 'qscope_%' and DBINFO('dbspace',B.partnum)=C.name group by 1 into temp tempfile3;

select * from tempfile3 order by 2 DESC;

 The following SQL commands will provide the names of the qscope_xx_yyy tables associated with each of the dbspaces.

Note: The dbspaces on your instance may differ, check 'onstat -d' for correct dbspace names and substitute as needed.

To find the names of the qscope_5m_(xxx) tables in the scout5m(y) dbspaces:

select C.name, B.tabname from sysmaster:'informix'.sysdbspaces C,
OUTER sysmaster:'informix'.systabnames B where C.name in ("scout5m","scout5m1","scout5m2","scout5m3","scout5m4","scout5m5","scout5m6","scout5m7","scout5m8","scout5m9","scout5m10","scout5m11","scout5m12","scout5m13")
and tabname like 'qscope_%' and DBINFO('dbspace',B.partnum)=C.name;

To find the names of the qscope_1h_(xxx) tables in the scout1h(y) dbspaces:

select C.name, B.tabname from sysmaster:'informix'.sysdbspaces C,
OUTER sysmaster:'informix'.systabnames B where C.name in ("scout1h","scout1h1","scout1h2","scout1h3","scout1h4","scout1h5","scout1h6","scout1h7","scout1h8","scout1h9","scout1h10","scout1h11","scout1h12","scout1h13")
and tabname like 'qscope_%' and DBINFO('dbspace',B.partnum)=C.name;

To find the names of the qscope_1d_(xxx) tables in the scout1d(y) dbspaces:

select C.name, B.tabname from sysmaster:'informix'.sysdbspaces C,
OUTER sysmaster:'informix'.systabnames B where C.name in ("scout1d","scout1d1","scout1d2","scout1d3","scout1d4","scout1d5","scout1d6","scout1d7","scout1d8","scout1d9","scout1d10","scout1d11","scout1d12","scout1d13")
and tabname like 'qscope_%' and DBINFO('dbspace',B.partnum)=C.name;