• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

Orphaned Records In Occupacy Table

chad_small

ADSM.ORG Moderator
#1
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?
 
Last edited:

DanGiles

ADSM.ORG Senior Member
#2
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.
 

karelbos

ADSM.ORG Member
#4
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

ADSM.ORG Moderator
#5
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.
1562248037431.png

You can test for yourself on your system:
Code:
select sum(num_files) as SUM_NUM_FILES,sum(reporting_mb) as SUM_REPORTING_MB from occupancy where node_name<>''
Code:
select num_files,reporting_mb from occupancy where node_name=''
 

karelbos

ADSM.ORG Member
#6
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:
Code:
select sum(num_files) as SUM_NUM_FILES,sum(reporting_mb) as SUM_REPORTING_MB from occupancy where node_name<>''
Code:
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

ADSM.ORG Moderator
#7
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.
1562249574058.png
 

chad_small

ADSM.ORG Moderator
#9
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

ADSM.ORG Moderator
#10
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
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 14 17.3%
  • Keep using TSM for Spectrum Protect.

    Votes: 51 63.0%
  • Let's be formal and just say Spectrum Protect

    Votes: 9 11.1%
  • Other (please comement)

    Votes: 7 8.6%

Forum statistics

Threads
31,380
Messages
133,653
Members
21,492
Latest member
CamoVan
Top