Database Performance

[David_FA]

Hi!

I have found that all the queries to my database consume too much CPU. Specially ADMIN_CENTER queries are too CPU intensive.

I have TSM server in a z/OS environment and by database characteristics are:

QUERY DB f=D

Available Space (MB): 99.292
Assigned Capacity (MB): 99.292
Maximum Extension (MB): 0
Maximum Reduction (MB): 952
Page Size (bytes): 4.096
Total Usable Pages: 25.418.752
Used Pages: 24.878.771
Pct Util: 97,9
Max. Pct Util: 97,9
Physical Volumes: 40
Buffer Pool Pages: 38.400
Total Buffer Requests: 8.082.700
Cache Hit Pct.: 99,97
Cache Wait Pct.: 0,00
Backup in Progress?: No
Type of Backup In Progress:
Incrementals Since Last Full: 2
Changed Since Last Backup (MB): 267,09
Percentage Changed: 0,27
Last Complete Backup Date/Time: 02-04-2008
04:40:50
Estimate of Recoverable Space (MB): 21.496
Last Estimate of Recoverable Space (MB): 13-08-2007
18:39:28


Does anyone find anything wrong? I mean, is the database too big? Do I have to add another server?

Any suggestion?

Thanks in advance

 

[moon-buddy]

Your database size is still at the medium-to-high range depending on the size of your environment.

There might be some "open" spaces that you can reclaim within your database. If you run "ESTIMATE DBREORGSTATS" you can find out how much space can be reclaimed.

This should then be followed by DSMSERV UNLOADDB, DSMSERV LOADFORMAT and DSMSERV LOADDB commands while the TSM Server is stopped. Make sure you read on this procedure and understand the steps correctly.

This may help in speeding up DB searches if your database is really fragmented.

Anyway of allocating more CPU processing power? Are you running TSM Server in its own LPAR?

 

[chad_small]

I don't know if I would even attempt a reorg...(i.e. UNLOAD, RELOAD) it can take an excessive amount of time. I would do as moon-buddy states and look into allocating more CPU processing power. Also what type of disk is the DB running on? We had DB issues like this when our disk cache was hosed and we had lost dual pathing due to a bad HBA.

 

[David_FA]

I ran "ESTIMATE DBREORGSTATS" a time ago:

ANR0984I Process 26 for ESTIMATE DBREORG started in the BACKGROUND at 08:46:54.
ANR1782W ESTIMATE DBREORG process 26 started - server performance may be
degraded while this process is running.
ANR1784I A database reorganization would reduce the database utilization by an
estimated 21.496 MB.
ANR0987I Process 26 for ESTIMATE DBREORG running in the BACKGROUND processed
17436644 items with a completion state of SUCCESS at 18:39:28.
ANR0381I Buffer pool statistics were successfully reset.

But it took a lot of time!!! And I was afraid of reorganizing the databse because it could be a too long process, as chad_small said.

We have the TSM Database in IBM DS8100 disks. Model 9 spreaded over differente LCUs.
I think disks are quite fast: 73 GB and 15.000 R.P.M. We do not use Disk Cache.

I have inherited a TSM that have in the same physical volumes Database, recovery log and Disk Storage Pool Volumes.

Could I stop the TSM server and move the VSAM volumes to other physical disks so as to divide them? Also database volumes??

Thanks in advance.

 

[Harry_Redl]

Hi,

for moving TSM DB and RLOG volumes you do not need to stop TSM. Define new DB and RLOG volumes to the new disks (using "def dbvol" and "def logvol" commands - see help) and then delete the existing ones (one at the time) - TSM will move the data from old volumes to new automaticaly.

Hope it helps

Harry

 

[superos390]

Hi,
we have some TSM DB on zOS with 130 GB. But we don´t use the ADMIN Center.
Do you know which queries are dangerous ? Pherhaps I can test them.
Our TSM DB is on a DS8300.
superos390

 

[David_FA]

Hi,

The query of the ADMIN_CENTER are sent on background to the TSM server while you are surfing in the ISC.

for example, when you only want to see a pannel of the ISC, the ADMIN_CENTER or ISC, sends those queries to the TSM:

ANR2017I Administrator ADMIN_CENTER issued command: QUERY DB f=d
ANR2017I Administrator ADMIN_CENTER issued command: QUERY SPACETRIGGER db f=d
ANR2017I Administrator ADMIN_CENTER issued command: QUERY LOG f=d
ANR2017I Administrator ADMIN_CENTER issued command: QUERY LIBR f=d
ANR2017I Administrator ADMIN_CENTER issued command: QUERY DR f=d
ANR2017I Administrator ADMIN_CENTER issued command: QUERY PATH f=d
ANR2017I Administrator ADMIN_CENTER issued command: QUERY EVENT * * begintime=-
now-24:00 endtime=now enddate=today f=d
ANR2017I Administrator ADMIN_CENTER issued command: QUERY EVENT * type=admin
begintime=now-24:00 endtime=now enddate=today f=d
ANR2017I Administrator ADMIN_CENTER issued command: select * from actlog where
originator='SERVER' and (SEVERITY='E' or SEVERITY='S' or SEVERITY='D') and
DATE_TIME > TIMESTAMP(CURRENT_TIMESTAMP - interval '24' hour)

 

[TonyB]

Gotta hand you points for tenacity...2 years!