Orphaned Records In Occupacy Table

[chad_small]

When querying the occupancy table I came across something odd when it appeared the results were way off.

Protect: TSM50>select varchar(node_name,45)as NODE_NAME, varchar(filespace_name,20)as FS_NAME, -
cont> sum(reporting_mb) as REPORTING_MB from occupancy group by node_name, filespace_name -
cont> order by reporting_mb desc

NODE_NAME FS_NAME REPORTING_MB
---------------------------------------------- ----------- ------------------
1046718101.64
AD00018.CLT01.ULX.DEC1.AS19229.NET.ORA /adsmorc 115977163.30
AD00055.CLT01.ULX.DEC1.AS19229.NET.ORA /adsmorc 75026959.52

Notice the first line shows no NODE_NAME and no FILESPACE_NAME

Protect: TSM50>select * from occupancy where node_name=''

NODE_NAME:
TYPE:
FILESPACE_NAME:
STGPOOL_NAME: STG-CNTR-ARCH-DISK
NUM_FILES: 47118
PHYSICAL_MB:
LOGICAL_MB:
REPORTING_MB: 4570827.69
FILESPACE_ID:

NODE_NAME:
TYPE:
FILESPACE_NAME:
STGPOOL_NAME: STG-CNTR-DISK
NUM_FILES: 113703772
PHYSICAL_MB:
LOGICAL_MB:
REPORTING_MB: 1042147273.95
FILESPACE_ID:

Anyone seen this before?

 

[DanGiles]

I remember seeing something like this, but I don't have a system to verify. I believe that first line is some sort of summary; may be related to replication.

 

[LED888]

It sounds like what is being describe in the following techdoc.

REPAIR OCCUPANCY (Repair reporting occupancy for storage pool)

Good Luck,
Sias

 

[karelbos]

In 2013 I had something like this. Contacted IBM support and got told:
Verify:
select * from occupancy where node_name is NULL
select * from occupancy where filespace_name is NULL

db2 "select count(*) from af_clusters where srvid=0 and ck1 not in (select nodeid from nodes)"

NOTE: The count from the above select should match the number of records returned from the dsmadmc command "select * from occupancy where node_name is NULL"

cleanup:
db2 "delete from af_clusters where srvid=0 and ck1 not in (select nodeid from nodes)"

after cleanup 1st 2 selects should give "No match found using this criteria".

 

[marclant]

cleanup:
db2 "delete from af_clusters where srvid=0 and ck1 not in (select nodeid from nodes)"

DON'T ISSUE THIS COMMAND.

It's a summary record. The values of the blank node = the sum of the non-blank nodes.


You can test for yourself on your system:

select sum(num_files) as SUM_NUM_FILES,sum(reporting_mb) as SUM_REPORTING_MB from occupancy where node_name<>''

select num_files,reporting_mb from occupancy where node_name=''

 

[karelbos]

DON'T ISSUE THIS COMMAND.

It's a summary record. The values of the blank node = the sum of the non-blank nodes.
View attachment 1562

You can test for yourself on your system:

select sum(num_files) as SUM_NUM_FILES,sum(reporting_mb) as SUM_REPORTING_MB from occupancy where node_name<>''

select num_files,reporting_mb from occupancy where node_name=''

You responded to the 'select varchar(node_name,45)' part, I responded to the '
select * from occupancy where node_name='' '.

 

[marclant]

You responded to the 'select varchar(node_name,45)' part, I responded to the '
select * from occupancy where node_name='' '.

My response is to the select * from occupancy as well. The null/blank node is a summary record, not an orphan node. See my screenshot that proves it. You may have had orphan nodes in 2013 and that was the procedure. But I would not recommend that @chad_small deletes anything from his database before checking with IBM Support.

 

[marclant]

It's documented too:
https://www-01.ibm.com/support/docview.wss?uid=swg21988530

 

[chad_small]

After I posted the question and then mulling over it for a few hours I started to wonder if it was a summary for the storage pools. The concern is that I didn't know it was there until just the other day which explains why my occupancy vs auditocc was so way off. When querying occupancy the NODE_NAME value is not NULL so if you don't put WHERE NODE NAME IS NOT '' you will unfortunately return the data when summing occupancy.

Protect: TSM50>select * from occupancy where node_name is NULL
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

Protect: TSM50>select * from occupancy where node_name=''

NODE_NAME:
TYPE:
FILESPACE_NAME:
STGPOOL_NAME: STG-CNTR-ARCH-DISK
NUM_FILES: 47236
PHYSICAL_MB:
LOGICAL_MB:
REPORTING_MB: 4572856.20
FILESPACE_ID:

NODE_NAME:
TYPE:
FILESPACE_NAME:
STGPOOL_NAME: STG-CNTR-DISK
NUM_FILES: 114177588
PHYSICAL_MB:
LOGICAL_MB:
REPORTING_MB: 1052343300.44
FILESPACE_ID:

 

[chad_small]

This all came about because I was trying to build a consolidated occupancy list of VM nodes and Standard Nodes using the occupancy table. Since the Occupancy table lists all nodes under NODE_NAME and all VM nodes under FILESPACES I was building a script to bring it all into one report. I noticed that there was a sum of large data with no node name in the list. (Feel free to resize the values to whatever you need)

select -
varchar(server_name,16) as SERVER_NAME, -
case -
when filespace_name like '\VMFULL-%' then VARCHAR( LTRIM ( FILESPACE_NAME , '\VMFULL-' ) , 45 ) -
else -
varchar(node_name,45) -
end as NODE, -
case -
when filespace_name like '\VMFULL%' then 'VM NODE' -
when filespace_name not like '\VMFULL%' then 'STANDARD NODE' -
end as TYPE, -
sum(REPORTING_MB) as OCCUPANCY_MB, -
sum(NUM_FILES) as FILES_STORED -
from occupancy, status -
where node_name not like '' -
group by -
server_name, -
case -
when filespace_name like '\VMFULL-%' then VARCHAR( LTRIM ( FILESPACE_NAME , '\VMFULL-' ) , 45 ) -
else -
varchar(node_name,45) -
end, -
case -
when filespace_name like '\VMFULL%' then 'VM NODE' -
when filespace_name not like '\VMFULL%' then 'STANDARD NODE' -
end -
order by -
server_name, -
case -
when filespace_name like '\VMFULL%' then 'VM NODE' -
when filespace_name not like '\VMFULL%' then 'STANDARD NODE' -
end, -
case -
when filespace_name like '\VMFULL-%' then VARCHAR ( LTRIM ( FILESPACE_NAME , '\VMFULL-' ) , 45 ) -
else -
varchar(node_name,45) -
end