Re: BRMS - how to see objects actually backed up? -- MIDRANGE-L

Thx Joe. By the way, I'm a big fan of yours going back several years. I've learned a lot from your blogs and tips!
I rarely if ever use STRSQL.

However, you piqued my curiosity and here's the deal ...
In ACS, using BKHSIZ without the SUM genned an error which pointed to BKHSIZ NOT being in the
Group By command.

To remedy, obviously removed the SUM from the Select stmt on BKHSIZ AND added BKHSIZ to the Group by.

So now the ACS SQL looks like this:

Select Row_Number() over (Order by BKSSVT) as "Row #",
SUBSTR(BKHSVT, 2, 6) AS "Bkup Date", BKVOL AS "Tape #", BKHSYS AS "System",
BKHLIB AS "Lib", BKHSEQ AS "Seq#", BKHSAV AS "Obj Sv", BKHERR AS "Obj N Sv", BKHSTA AS "Sts 0=N 1=S",
BKHSIZ AS "Blk Sz Bytes", BKHMSG AS "Error Msg ID", BKTYPS AS "Save Type", BKHGRP AS "Ctl Grp", BKHCMD AS "B/Up Cmd",
BKSAVT AS "Tape Y/N", SUBSTR(BKSSVT, 8, 6) AS "Start Time", SUBSTR(BKESVT, 8, 6) AS "End Time", BKHTIM AS "Bkup Time",
BKHTXT AS "Text"
FROM QUSRBRM.QA1AHS
WHERE SUBSTR(BKHSVT, 2, 6) = '200722'
GROUP BY BKHSVT, BKHPRC, BKHTIM, BKVOL, BKHSYS, BKHLIB, BKHSEQ, BKHSAV, BKHERR, BKHSTA,
BKHMSG, BKHSIZ, BKTYPS, BKHGRP, BKHCMD, BKSAVT, BKHPRC, BKSSVT, BKESVT, BKHTXT

The results look like this:

Row # Bkup DateTape # System Lib Seq# ObjSv Obj Sv St Blk Sz Bytes Error Msg SaveType Ctl Grp B/Up Cmd Tape Start T End T Bkup Time Text
1 200722 00885B FLABARP PDPDDB 1 98 0 1 28526407680 *CUM BKUP_DAILY SAVCHGOBJ Y 003641 003839 3913
2 200722 00885B FLABARP YAFRDTA 2 4 0 1 89370624 *CUM BKUP_DAILY SAVCHGOBJ Y 003839 003841 3913
3 200722 00885B FLABARP TFBDB 3 32 0 1 2039324672 *CUM BKUP_DAILY SAVCHGOBJ Y 003842 003851 3913

-------------------------------------

I ran the same SQL in STRSQL 5250 and it ran fine.
Results copied off of the green screen:

Row #" "Bkup Date" "Tape #" "System" "Lib" "Seq#" "Obj Sv" "Obj N Sv" "Sts 0=N 1=S" "Blk Sz Bytes"
1 200722 00885B FLABARP PDPDDB 1 98 0 1 28,526,407,680
2 200722 00885B FLABARP YAFRDTA 2 4 0 1 89,370,624
3 200722 00885B FLABARP TFBDB 3 32 0 1 2,039,324,672

---------------------------------------

Thank you for pointing that out.
My apologies to those who use both ACS SQl and/or STRSQL in 5250.

Very Respectfully,
Michael Mayer
IBM i Support / System Admin.
IT Operations.
The Florida Bar
651 E. Jefferson St
Tallahassee, Florida 32399-2300
mmayer@xxxxxxxxxxxxxx
https://www.floridabar.org
Office: 850.561.5761
Cell: 518.641.8906

4. Re: BRMS - how to see objects actually backed up? (Joe Pluta)

----------------------------------------------------------------------

message: 4
date: Wed, 22 Jul 2020 12:39:08 -0500
from: Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
subject: Re: BRMS - how to see objects actually backed up?

Michael, I ran this in STRSQL but I ran into a problem because of the "SUM(BKHSIZ)".? With that in place, STRSQL didn't like the RowNumber because BKSSVT was invalid.? If I changed SUM(BKHSIZ) to BKHSIZ, the SQL worked nicely.? Am I missing the reason for the SUM(BKHSIZ)?

Thank you!

On 7/22/2020 7:24 AM, Mayer, Michael via MIDRANGE-L wrote:

Run DSPLOGBRM, F4 and plug in the parms as needed is 1 way to go about it although that's the long route.

You can also run an SQL over file QA1AHS.QUSRBRM. I run this daily on
all of our BRMS backups using ACS Run SQL Scripts.

Select Row_Number() over (Order by BKSSVT) as "Row #",
SUBSTR(BKHSVT, 2, 6) AS "Bkup Date", BKVOL AS "Tape #", BKHSYS AS "System",
BKHLIB AS "Lib", BKHSEQ AS "Seq#", BKHSAV AS "Obj Sv", BKHERR AS "Obj N Sv", BKHSTA AS "Sts 0=N 1=S",
SUM(BKHSIZ) AS "Blk Sz Bytes", BKHMSG AS "Error Msg ID", BKTYPS AS "Save Type", BKHGRP AS "Ctl Grp", BKHCMD AS "B/Up Cmd",
BKSAVT AS "Tape Y/N", SUBSTR(BKSSVT, 8, 6) AS "Start Time", SUBSTR(BKESVT, 8, 6) AS "End Time", BKHTIM AS "Bkup Time",
BKHTXT AS "Text"
FROM QUSRBRM.QA1AHS
WHERE SUBSTR(BKHSVT, 2, 6) = '200722'

The output will look like this:

Row # Bkup Date Tape # System Lib Seq# Obj Sv Obj N Sv St Blk Sz Bytes Error Msg Save Type Ctl Grp B/Up Cmd Tape Start T End Tim Bkup Time Text
1 200722 00885B FLABARP PDPDDB 1 98 0 1 28526407680 *CUM BKUP_DAILY SAVCHGOBJ Y 003641 003839 3913
2 200722 00885B FLABARP YAFRDTA 2 4 0 1 89370624 *CUM BKUP_DAILY SAVCHGOBJ Y 003839 003841 3913
3 200722 00885B FLABARP TFBDB 3 32 0 1 2039324672 *CUM BKUP_DAILY SAVCHGOBJ Y 003842 003851 3913

Using this SQL as abase, I can adjust it for what was saved, what was not saved, what type of save, etc. It's very flexible.

----------------------------------------

Or write a 5250 WRKQRY over QA1AHS/QUSRBRM and adjust the record selections as needed.

Note; There is a difference between what regular commands see backed
up in BRMS and using the files in BRMS. I ran into This a few years ago, was a bit confused and had IBM confirm that fact.

QA1AHS is also over the top of BRMS command WRKMEDIBRM, which is also a nice way of looking at your backup.

------------------------------

ublic disclosure.

________________________________
Please note: Florida has very broad public records laws. Many written communications to or from The Florida Bar regarding Bar business may be considered public records, which must be made available to anyone upon request. Your e-mail communications may therefore be subject to public disclosure.