Friday, July 27, 2018

DB2 Select Example In TSM/SP

While trying to create a specific report that a manager requested I realized I needed something more powerful. Well now that TSM/Spectrum Protect runs on DB2 we have all the power we need. Here is an example of generating a report with an Inner and Outer Join.

Inner and Outer Join Example

Part 1 - Will return the columns from two tables into a third temporary table

Select -
 varchar(o.node_name,45) ENTITY, -
 cast(s.SUM_AFFECTED as dec(12,0)) AFFECTED,
 cast(s.SUM_BYTES/1073741824 as decimal(12,2)) GB_BACKED_UP, -
 cast(o.SUM_NUM_FILES as dec(15,0)) TOTAL_FILES_STORED, -
 cast(o.SUM_LOGICAL_MB/1024 as dec(12,2)) GB_TSM_OCCUPANCY -
 from -

Part 2 - Gather two columns from occupancy

select node_name, -
  from occupancy -
    group by node_name

Part 3 - Gather three columns from summary for BACKUP and ARCHIVES for last 30 days

select varchar(entity,45) ENTITY, -
    sum(bytes/1073741824) SUM_BYTES, -
    sum(affected) SUM_AFFECTED -
  from summary  -
   where -
    end_time>=(current_timestamp - 30 days) -
    and activity in ('BACKUP','ARCHIVE') -
   group by entity

select -
 varchar(o.node_name,45) ENTITY, -
 cast(s.SUM_AFFECTED as dec(12,0)) AFFECTED, -
 cast(s.SUM_BYTES as decimal(12,2)) GB_BACKED_UP, -
 cast(o.SUM_NUM_FILES as dec(15,0)) TOTAL_FILES_STORED, -
 cast(o.SUM_LOGICAL_MB/1024 as dec(12,2)) GB_TSM_OCCUPANCY -
 from -
 (select node_name, -
  from occupancy -
    group by node_name) o, -
 (select entity, -
    sum(bytes/1073741824) SUM_BYTES, -
    sum(affected) SUM_AFFECTED -
  from summary  -
   where -
    end_time>=(current_timestamp - 30 days) -
    and activity in ('BACKUP','ARCHIVE') -
   group by entity) s -
 where ENTITY in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
  and o.node_name=s.entity -
  order by ENTITY

The generated report shows the total amount of files backed up, GB backed up for the last 30 days while also showing the total files stored in TSM and the nodes current total occupancy in GB.

----------   ---------------     ---------------     -------------------     -----------------
DORPORA05                920            47513.00                    8354              73263.60
IXPFLDNAS              60792               27.00                15443947               9434.27
ILDNASJ                12680                5.00                 1972680                715.60
ILDNASK                    8                0.00                   83004                197.48
ILDNASL                10739                4.00                 1649434                574.51
ILDNASM                45448               25.00                 1221367               1722.03
ILDNASM                 4386                7.00                 1122826                557.64
ILDNASN                    5                0.00                   99769                 46.52
ILDNASO                 1688                0.00                  459683                138.16
ILDNASP                 9369               11.00                 1402332               1623.92
ILDNASR                28787               29.00                  462479                457.15
ILDNASS                    5                0.00                   36771                 17.51
ILDNAST                 6329               21.00                  982728               1038.08
ILDNASU                 6848               19.00                 1548369                671.68
ILDNASV                18197               48.00                  999905               1333.92
ILDNASW                    5                0.00                  245366                116.89
RSD1W030S              59979                7.00                 5207060                547.23
RSD1W031S               3444                2.00                 5070450                473.15
RSD1W241S               6879               47.00                  517358               1782.92
RSD1W242S               6478               51.00                  995006               2109.45
RSD1W266S               4377                7.00                  492810                184.69
RSD1W267S               5700                3.00                  328391                100.95
RSD1W320C              40881               12.00                 2692541                576.50
RSD1W341C               8194               55.00                  456567               1120.11
RSD1W342C              17003                7.00                  625487                281.84
RSD1W343C               4628                2.00                  298699                 79.63
RSD1W504SV              1941               12.00                   42444                551.35
RSD1W505SV              1079              105.00                   38524               4033.06
RSD1W5067S               683               64.00                   22389               1343.82
RSD1W506SV               489                0.00                   14349                  4.53
RSD1W508SV              2662               31.00                   79340                699.12
RSD1W509SV              4017               74.00                  139073               1617.35
RSD1W510SV               698              544.00                   18086               9161.23
RSD1W511SV              1365              295.00                   65303               6588.06

Thursday, July 19, 2018

Personal Back Ups!

I have been doing this backup/archive work for almost 20 years and I can't go a week without some acquaintance asking me about backing up their personal data. It used to be large portable USB drives were the best option but as home internet connections have increased there throughput to the point of some offering giga-bit speeds, it makes more sense to use a cloud based backup provider. This article is based off of my experience and I only offer my opinion here so take it for what it is, but I recommend everyone look into a good backup solution or else you could loose all the important memories you have accumulated as life has become more digital. 

So what do I use to protect my memories? I used to use Dropbox, then when Google Drive became available I used it, and then I started using Microsoft SkyDrive now known as OneDrive. I began to exclusively use OneDrive several years ago when I was able to get more than the paltry 5 - 10GB that the others allowed. At one point I freely acquired 100GB by using Bing and gathering Bing points. I am cheap and didn't want to pay for my cloud space. I had portable hard drives with my data but knew it wasn't the solution. So when my free OneDrive space was due to expire I was offered an Office365 family plan with 1TB of space for up to 5 people in the family. With my wife and I each having 128GB iphone that my wife has actually filled with video and pictures during trips, I knew it was the best solution for us. Not only did I get 1TB for each of us, it could cover our personal home computer and our daughter phone also. Oh, and did I mention it comes with access to download Office to our home and daughters laptop?  All this is good but it would be useless if the OneDrive app was terrible. Truthfully, I find it quite easy to use. Microsoft has continued to fix bugs and add features it has become a great tool I use almost daily. 

I write this article because it's important you think about how you will protect memories. Sure you can upload to Google Photo's or put all your items on Facebook, but each has it's own drawbacks. Google Photo's does not keep the photo at its original resolution, and Facebook is not designed to be a backup solution. I have lost personal photos and documents from when I was younger due to losing them during moves or becoming damaged by mishandling. I will never get them back, had I scanned/digitzed them I would have been able to keep them safe. I also understand that nothing is 100% protected or safe, but by putting my files in the cloud they are more safe than on a portable drive or sitting in a drawer. 

Tuesday, July 17, 2018

Spectrum Protect Server 8.1.4 DB Issue

Recently we ran into a situation where our Spectrum Protect 8.1.4 server started to crash. We reviewed the db2diag.log file and identified the error as ANR0131E Server DB space exhausted. Upon review of our DB file systems the OS showed 7GB available in the 5 file systems assigned for DB space.

/dev/tsm20log 111616.00   9784.93   92%      205     1% /tsmserver/tsm20log
/dev/tsm20arch 102400.00  66821.52   35%      100     1% /tsmserver/tsm20arch
/dev/tsm20fail 101376.00 100964.16    1%       21     1% /tsmserver/tsm20fail
/dev/tsm20dblv01 101376.00   7622.45   93%      104     1% /tsmserver/tsm20dblv01
/dev/tsm20dblv02 101376.00   7622.48   93%      104     1% /tsmserver/tsm20dblv02
/dev/tsm20dblv03 101376.00   7622.45   93%      104     1% /tsmserver/tsm20dblv03
/dev/tsm20dblv04 101376.00   7622.46   93%      105     1% /tsmserver/tsm20dblv04
/dev/tsm20dblv05 101376.00   7622.45   93%      105     1% /tsmserver/tsm20dblv05

The server had been crashing from the previous night and we had at first thought it was due to the active log filling and not writing out to the archive log fast enough. What was weird is we could bring TSM up and it would run for an hour or two then crash again. When we identified it was not the active log but the DB space (earlier the next morning) we were confused. Why would we get a space notification when the DB has 35GB of space still available. My co-worker created an additional file system of the same size as the other DB file systems and ran the dsmserv extend dbspace command and the file systems almost immediately jumped to 99% utilized. So it appears Spectrum Protect needed to write 35+GB of DB data and could not due to space available? We also noticed that our offshore people had added tsm20dblv05 a few months back but had used a different disk array (slow SATA) and had not informed us, which we think might have contributed to the performance issues we had been seeing on this server recently and maybe was related to the DB space issue??? Not sure, but be aware that this can occur. I will need to research how DB2 allocates space and in what chunk size it does so.