Occupancy Select Statement

We have a handful of "archive" tape pools on our instances.  I needed to know what servers had occupancy in these pools.  Here is the select statement I ran:

select node_name, stgpool_name from occupancy where stgpool_name like 'ARCHIVE%'

Audit License Revealed Lock Issue

Troubles Restoring Your TSM Database?

ANR0259E-Unable to read complete restart/checkpoint TSM Server won't start

Technote (troubleshooting)   Problem(Abstract) Message ANR0259E - "Unable to read complete restart/checkpoint information from any database or recovery log volume" can occur for several reasons.

Cause

Reasons: 1) The DB and or Recovery Log volumes have not been initialized via the DSMSERV FORMAT command in the case of new/first time volume usage. 2) The DSMSERV.DSK file is pointing to an incorrect path, volume or has a typographical error. 3) The disk volumes are unavailable/off-line 4) The volumes don't exist The ANR0259E error message is occurring because the startup process is not able to find/determine the starting point/position of either a DB or Recovery log volume. This was seen in a case where a shark disk array had failed, new volumes having new names were defined for use and a DSMFMT had been performed. The DSMSERV.DSK file had been updated with the correct information. The TSM server would not start as the new volumes had not been initialized for TSM Usage via the DSMSERV FORMAT command.

Resolving the problem

Verify path and volume names are correct. Verify disk volumes are available. Initialize new volumes with the DSMSERV FORMAT command. Note: The DSMSERV FORMAT command is destructive in that it will clear/overwrite any data in the DB or Recovery Log volume being formatted.

Useful SQL Statements for TSM

I stumbled across this link in a Google search this morning.  It does look VERY useful!

http://thobias.org/tsm/sql/index.html

DS4300 Controller Issues - CONTROLLER HEALTH CHECK FAILURE

This past weekend we started encountering some odd messages in the AIX error report.  When inspecting the Storage Manager client we also saw that all paths had moved over to the second controller.

BC669AA7   1212153609 P H dac1           CONTROLLER HEALTH CHECK FAILURE
BC669AA7   1212152409 P H dac1           CONTROLLER HEALTH CHECK FAILURE
483C9D10     1212151109 I H dac0            ARRAY ACTIVE CONTROLLER SWITCH
D5385D18     1212151109 T H hdisk3       ARRAY OPERATION ERROR
C86ACB7E    1212151109 I H hdisk3        ARRAY CONFIGURATION CHANGED
483C9D10      1212151109 I H dac0            ARRAY ACTIVE CONTROLLER SWITCH
D5385D18     1212151109 T H hdisk5        ARRAY OPERATION ERROR
C86ACB7E    1212151109 I H hdisk5         ARRAY CONFIGURATION CHANGED
BC669AA7    1212150309 P H dac1           CONTROLLER HEALTH CHECK FAILURE

After inspecting the controller errors via the Storage Manager client, I Google'd them and saw references to the controller being faulty.  And when looking at the details of the AIX errors, they also seemed to point to the controller as the issue.  All HBA's were also online and available so it wasn't a connection issue in those terms.

After a call to IBM support and some onsite troubleshooting with the CE, we reseated the controller.  This also had the benefit of power cycling it.  Once the controller was reseated and brought online, it functioned just fine and even moved the paths back over automatically.

What’s the Login User Name and Password for VMWare Server 2.0?

from:  www.tipandtrick.net
VMWare Server 2.0 (currently in Beta 2 release) has departed significantly from legacy VMWare Server 1.0. It’s still a free virtualization software to users to create, manage and run virtual machines, but instead of usual standalone desktop (or notebook laptop) based application, VMWare Server 2.0 now runs solely on web-based management user interface, plus many other new features, enhancements and improvements.

To log in to VMWare Server 2.0, users will have to access https://localhost:8333/ui/ or http://localhost:8222/ui/ for non-secure connection (the URL may takes the form of your computer name) with a web browser to come to VMware Infrastructure (VI) Web Access management interface, which is VMWare Server Console, normally simply call Web-UI. Don’t worry about your system doesn’t have a web server such as Apache or Microsoft IIS running, VMWare Server 2.0 install Tomcat web server in the background.


But users will come to a VI Credentials page asking for Login Name and Password, as shown in the screenshot below



What login user name and password to use? This probably your first installation of VMWare Server 2.0, and even if you have installed VMWare Server 1.0 before, it never ask for creation of any user account or its user ID or password during installation of whatever versions. And searching up and down in Start Menu’s VMWare Server program folder doesn’t reveal any program to create or manage user’s login name for VMWare console too.

Actually, VMWare Infrastructure Web Access, and hence VMWare Server 2.0, uses user account of the operating system, i.e Windows XP, Windows Vista, Windows Server 2003, Windows Server 2008 and Linux distro. So to login to VMWare Server 2.0 Web Console, logon with an administrative account’s user name of Windows or Linux (Administrator or root) and the corresponding password. Note the password is a must. In Windows, most built-in Administrator account does not have password by default even after been enabled, and so a password must be assigned.

It’s possible to create and add another user account with administrator’s privileges specially for VMWare Server login purpose.

TSM Select Queries - Updated 1-4-2010

This list will continue to grow as I add more queries over time. Newest additions are at the bottom.

List Out Activity Log by Message Number, Message and Date and Time
select DATE_TIME,MSGNO,MESSAGE from ACTLOG where MESSAGE like '%PROD_DISK%' and date_time>timestamp(current date -4 day,'00:00:00')and (msgno=1210 or msgno=1214 or msgno=2753)

List Scratch Volumes:

select volume_name from libvolumes where status='Scratch'

Number of Files grouped by node name:

select node_name, sum(num_files) from occupancy where node_name like 'HAIM%' group by node_name

DSMADMC command for command line example, exports to file:

dsmadmc -optfile=tsmaprod01.opt -id= -password= -tab select node_name, sum(num_files) from occupancy where node_name like 'AX%' group by node_name >c:\temp\occupancy.txt

dsmadmc -optfile=tsmblibm.opt -id=-password= -tab select volume_name from libvolumes where status='Scratch' >c:\temp\scratchb.txt

dsmadmc -optfile=tsmblibm.opt -id= -password= -tab all:q stg old >c:\temp\oldstg.txt

dsmadmc -optfile=tsmblibm.opt -id= -password= -tab select * from archives where node_name='PRDAUSRVS01' and ll_name='a4200.rpt' >c:\temp\a4200.rpt.txt ***This is case sensitive***

Total Tapes Used by Node for all Storage Pools

select node_name,stgpool_name,count(distinct volume_name) as TOTAL_TAPES from volumeusage where node_name='A4ID3P01' and stgpool_name in (select stgpool_name from stgpools where pooltype='PRIMARY') group by node_name,stgpool_name

***This was also handy in a restore situation to tell how many tape TSM needed to mount to restore the data needed.

Locate Individual File

select * from backups where node_name='NODENAMEINALLCAPS' and ll_name='file.txt'

TSM Script Containing Select Statement to Gather Client Error Messages - use DSMADMC to run the script and export to a file

/*-----
*/
/* Script Name: Q_Client_Errors */
/* Description: Find client errors since 18:00 */
/* yeesterday */
/*-----
*/
set sqldisplaymode wide
select date_time, msgno, nodename, substr(message,27) as MESSAGE from actlog where date_time>timestamp(current date - 1
day,'18:00:00') and (msgno=4005 or msgno=4007 or msgno=4037 or msgno=4987)

Using ABC Client and Showing Backup Statistics

select cast(sum(cast(substr(message,31,12) as float(12)))/1024/1024 as decimal(12,2)) as "GB" from actlog where date_time>'2009-01-20 06:00:00' and date_time<='2009-01-21 06:00' and msgno=4990 and message like '%Data transferred%' and nodename like 'AV%' Show GB/Week for all servers select entity, sum(bytes)/1024/1024/1024 as "GB/week" from summary where start_time>'2009-01-15 18:00:00' and activity='BACKUP' group by entity order by 2 desc

Select Servers on a particular subnet

select node_name, tcp_address from nodes where tcp_address like '192.168.50.%'

Show Objects Backed Up on a Node in Last 24 Hours

select backup_date, hl_name, ll_name from backups where node_name='NODENAME' and backup_date>=current_timestamp-24 hours

List All Volumes for a Specific Storage Pool

select volume_name,stgpool_name from volumes where stgpool_name like 'ST06_TAPEC_01_OLD%'

Select Management Classes that are associate with backups. Allows you to see if there are any longer retention backups.
select distinct class_name from backups where node_name='AWSQLP23'

Total Backed Up in 24 hours by node, platform and affected.
SELECT node_name as NODE,platform_name as PLATFORM, activity,sum(cast(bytes/1024/1024/1024 as decimal(6,2))) as GB, affected FROM nodes, summary WHERE (end_time between current_timestamp - 24 hours and current_timestamp) and activity='BACKUP' and ((node_name=entity)) GROUP BY node_name, platform_name, activity, affected ORDER BY platform, GB, node asc


Total Size from Filespaces
select sum(pct_util*capacity/100) from filespaces

Total GB's Per Node from Backup Activity
select node_name, type, sum (logical_mb)/1024 as logical_gb from occupancy where stgpool_name in (select stgpool_name from stgpools where pooltype='PRIMARY') group by node_name, type

Filespace Reporting - Sorted by Node name showing the last backed up date
select node_name, filespace_name, backup_end from filespaces order by node_name

Total GB's Per Node from Backup Activity in Last 24 Hours - can be used for Archive, just change activity= from backup to archive.
SELECT entity, activity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as "GB" FROM summary WHERE end_time>current_timestamp-24 hours and activity='BACKUP' GROUP BY entity, activity

Report Start Time of a Backup for a Particular Node
all:Select entity, start_time from summary where entity like '%ALWDM%' and start_time >{ts '2009-10-2 22:00:00'} AND start_time <{ts '2009-10-03 06:30'}

Report on Schedule Associations, Domain, Setup Time and other various things - to be used as dsmadmc with XL's concatenate
dsmadmc -optfile=instancename -id= -password= -tabdelimited -dataonly=yes select * from associations, client_schedules where associations.node_name='nodename' and client_schedules.domain_name=associations.domain_name and client_schedules.schedule_name=associations.schedule_name >>c:\temp\sox\scheds.txt

Find Backups from a Specific Node
select * from backups where node_name='NODENAME'

Find a File from Backups of a Specific Node
select * from backups where node_name='NODENAME' and ll_name='file_name'

Find all backup objects in a given Filespace from Backups of a Specific Node
select * from backups where node_name='NODENAME' and filespace_name='/filespace'

List out the Filespace Name, Directory, and the File from Backups of a Specific Node
select filespace_name,hl_name,ll_name from backups where node_name='NODENAME'

List out the Filespace Name, Directory, File, and Archive Date from Archives of a Specific Node
select filespace_name, hl_name, ll_name, archive_date from archives where node_name like 'NODENAME' order by ll_name

List out the Filespace Name, Directory, File, and Deactivation Date from Backups of a Specific Node
select deactivate_date, filespace_name, type, ll_name, hl_name from backups where node_name='NODENAME'

List out the Filespace Name, Directory, File, and Deactivation Date from Backups of a Specific Node grouped by active/inactive version.
select deactivate_date, filespace_name, type, ll_name, hl_name, deactivate_date, class_name, state from backups where node_name='NODENAME' order by state

List out the Filespace Name, Directory, File, and Deactivation Date from Backups of a Specific Node grouped by backup date
select deactivate_date, filespace_name, type, ll_name, hl_name, deactivate_date, class_name, state, backup_date, node_name from backups where node_name='NODENAME' order by backup_date

List what volumes contain active file data for a particular node.
select node_name, filespace_name, stgpool_name, volume_name from volumeusage where node_name='NODENAME' and node_name in (select node_name from backups where state='ACTIVE_VERSION' group by node_name)

List what volumes contain a particular file for a particular node
select volume_name from contents where file_name='/ .list_filesets.out' and node_name='NODENAME'

List what archives have been created within the last 8 hours.
select node_name, hl_name, ll_name from archives where archive_date>(current_timestamp-8 hours) order by node_name

Generates a count of archives by nodename.
select node_name, count(*) from archives group by node_name

Generates a list of all full volumes that are checked into the library.
select distinct volumes.volume_name, volumes.status, volumes.pct_reclaim,
libvolumes.library_name from volumes, libvolumes where volumes.status='FULL' and
volumes.volume_name in (select volume_name from libvolumes where
library_name='LIBRARYNAME')

Generates a count of volumes by type, private or scratch, for a particular library.
select status, count(*) from libvolumes where library_name='LIBRARY' group by status