How to determine unused databases and objects.
OS: SunOS 5.7 IDS: 7.31.UC2 We're looking at migrating and upgrading to current Informix version and Linux OS, but I don't want to move obsolete or unused databases. Question: Is there a way to tell when the last time a database or it's object(s) have been accessed and by whom? Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings - BU: I & T Group 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com <mailto:2244650553@messaging.sprintpcs.com> Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com> Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com> Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com> " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU *******************************************************************
onstat -g ppf will show the basic profile information ( isam reads, isamwrites, isam rewrites, lock requests, etc.) for every partition in the
server. The SMI table is sysmaster:sysptprof. This query may help, every
user has to read systables when connecting to any database. So:
select dbsname, lockreqs, isreads
from sysptprof
where tabname = 'systables'
order by 1;
Any database mentioned with a zero lockreqs and isreads has not been
accessed since the server was last started or stats were zero'd. Unusually
low values may indicate a database that is only accessed by batch jobs.
Here's output from my server at home:
> select dbsname, lockreqs, isreads
from sysptprof
where tabname = 'systable> > s'
order by 1;>
dbsname adtc_monitoring
lockreqs 146
isreads 70
dbsname art
lockreqs 1972
isreads 902
dbsname big_test
lockreqs 29498
isreads 16047
dbsname martins
lockreqs 0
isreads 0
dbsname martins2
lockreqs 0
isreads 0
dbsname sales_demo
lockreqs 0
isreads 0
dbsname stores_demo
lockreqs 76
isreads 76
dbsname superstores_demo
lockreqs 0
isreads 0
dbsname sysadmin
lockreqs 260404
isreads 121753
dbsname sysmaster
lockreqs 23323
isreads 17505
dbsname sysuser
lockreqs 0
isreads 0
dbsname sysutils
lockreqs 0
isreads 0
12 row(s) retrieved.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Jun 1, 2011 at 2:49 PM, Knox, Ernest <Ernest.Knox@searshc.com>wrote:
> OS: SunOS 5.7
>
> IDS: 7.31.UC2
>
> We're looking at migrating and upgrading to current Informix version and
> Linux OS, but I don't want to move obsolete or unused databases.
>
> Question: Is there a way to tell when the last time a database or it's
> object(s) have been accessed and by whom?
>
> Thanks,
>
> *******************************************************************
>
> Ernie Knox
>
> IT Database Administrator Specialist
>
> Sears Holdings - BU: I & T Group
>
> 3333 Beverly Rd., B4-266A
>
> Hoffman Estates, IL. 60179
>
> Office: (847) 286-5735
>
> Email: Ernest.Knox@searshc.com
>
> Blackberry: <2244650553@messaging.sprintpcs.com>2244650553@
> messaging.sprintpcs.com
> <mailto: <2244650553@messaging.sprintpcs.com>2244650553@
> messaging.sprintpcs.com>
>
> Page via Skytel: <2244650553@sprint.skytel.com>2244650553@
> sprint.skytel.com
> <mailto: <2244650553@sprint.skytel.com>2244650553@sprint.skytel.com>
>
> Informix or MySQL Primary: 9110210@skytel.com
> <mailto:9110210@skytel.com>
>
> Informix or MySQL Secondary: 7276872@skytel.com
> <mailto:7276872@skytel.com>
>
> " Yes we can make a Change! "
>
> " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
> "
>
> " Lets not forget - GO Pistons and Red Wings! "
>
> GSU
>
> *******************************************************************
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5186af48b2f9004a4ab2eff
Excellent. This helps.
dbsname lockreqs isreads
assoc_cat 68 32
boiseadc 0 0
bootcamp 0 0
business 0 0
business_stage 0 0
c704www_old_db 0 0
career_mgmt 0 0
changeman 0 0
cmsa 10878 84245
cmsa_common 0 0
commercial 0 0
concessions 0 0
crwqdct 0 0
d2k_coa 0 0
d704www 0 0
d824dev 0 0
dbethernet 0 0
dgoetz 0 0
dscs 0 0
esm 0 0
excmddev1 0 0
findirectory 0 0
fwstats 0 0
han 0 0
hipo 0 0
home_services 0 0
hrcc 0 0
insidehrdev 0 0
intraddev1 1113158 518506
Thanks,
*******************************************************************
Ernie Knox
IT Database Administrator Specialist
Sears Holdings - BU: I & T Group
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Email: Ernest.Knox@searshc.com
Blackberry: 2244650553@messaging.sprintpcs.com
Page via Skytel: 2244650553@sprint.skytel.com
Informix or MySQL Primary: 9110210@skytel.com
Informix or MySQL Secondary: 7276872@skytel.com
" Yes we can make a Change! "
" It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
"
" Lets not forget - GO Pistons and Red Wings! "
GSU
*******************************************************************
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Wednesday, June 01, 2011 3:04 PM
To: ids@iiug.org
Subject: Re: How to determine unused databases and objects. [23896]
onstat -g ppf will show the basic profile information ( isam reads, isam
writes, isam rewrites, lock requests, etc.) for every partition in the
server. The SMI table is sysmaster:sysptprof. This query may help, every
user has to read systables when connecting to any database. So:
select dbsname, lockreqs, isreads
from sysptprof
where tabname = 'systables'
order by 1;
Any database mentioned with a zero lockreqs and isreads has not been
accessed since the server was last started or stats were zero'd.
Unusually
low values may indicate a database that is only accessed by batch jobs.
Here's output from my server at home:
> select dbsname, lockreqs, isreads
from sysptprof
where tabname = 'systable> > s'
order by 1;>
dbsname adtc_monitoring
lockreqs 146
isreads 70
dbsname art
lockreqs 1972
isreads 902
dbsname big_test
lockreqs 29498
isreads 16047
dbsname martins
lockreqs 0
isreads 0
dbsname martins2
lockreqs 0
isreads 0
dbsname sales_demo
lockreqs 0
isreads 0
dbsname stores_demo
lockreqs 76
isreads 76
dbsname superstores_demo
lockreqs 0
isreads 0
dbsname sysadmin
lockreqs 260404
isreads 121753
dbsname sysmaster
lockreqs 23323
isreads 17505
dbsname sysuser
lockreqs 0
isreads 0
dbsname sysutils
lockreqs 0
isreads 0
12 row(s) retrieved.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
organization with which I am associated either explicitly, implicitly,
or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Jun 1, 2011 at 2:49 PM, Knox, Ernest
<Ernest.Knox@searshc.com>wrote:
> OS: SunOS 5.7
>
> IDS: 7.31.UC2
>
> We're looking at migrating and upgrading to current Informix version
and
> Linux OS, but I don't want to move obsolete or unused databases.
>
> Question: Is there a way to tell when the last time a database or it's
> object(s) have been accessed and by whom?
>
> Thanks,
>
> *******************************************************************
>
> Ernie Knox
>
> IT Database Administrator Specialist
>
> Sears Holdings - BU: I & T Group
>
> 3333 Beverly Rd., B4-266A
>
> Hoffman Estates, IL. 60179
>
> Office: (847) 286-5735
>
> Email: Ernest.Knox@searshc.com
>
> Blackberry: <2244650553@messaging.sprintpcs.com>2244650553@
> messaging.sprintpcs.com
> <mailto: <2244650553@messaging.sprintpcs.com>2244650553@
> messaging.sprintpcs.com>
>
> Page via Skytel: <2244650553@sprint.skytel.com>2244650553@
> sprint.skytel.com
> <mailto: <2244650553@sprint.skytel.com>2244650553@sprint.skytel.com>
>
> Informix or MySQL Primary: 9110210@skytel.com
> <mailto:9110210@skytel.com>
>
> Informix or MySQL Secondary: 7276872@skytel.com
> <mailto:7276872@skytel.com>
>
> " Yes we can make a Change! "
>
> " It's always a great day to watch Sports - GO LIONS, TIGERS, and
BEARS!
> "
>
> " Lets not forget - GO Pistons and Red Wings! "
>
> GSU
>
> *******************************************************************
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5186af48b2f9004a4ab2eff
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.