Getting data from the BRMS log

BRMS (Backup, Recovery and Media Services) is used by many IBM i customers to manage the way they save data. Control Groups are created to group libraries, folders and files together. When a BRMS save is initiated it is for the Control Group, rather than the individual libraries, folders, etc.

I was recently tasked to create a daily report, that would be emailed to all IT managers, on the status of the previous night's backups. After a quick search through the BRMS menus I found the Display Log for BRM, DSPLOGBRM, command. I was disappointed to find that it will only output to display or printer. Which meant it was not suitable for my needs. I needed a file to extract the information I needed to generate the report.

After "surfing" through the files in the BRMS library, QUSRBRM, I found the BRMS log file, QA1ALG. Which I confirmed with a telephone call to IBM Support. I am not going to list all of the fields in the file, just the ones I needed:

Field	Description
LGMDTA	Log text
LGDATE	Logged date
LGTIME	Logged time
LGSEV	Message severity
LGUSER	User
LGJOB	Job name
LGJNBR	Job number
LGMID	Message id
LGCGNAM	Control Group

By combining LGUSER, LGJOB, and LGJNBR I could group the records by the job that had run the Control Group.

I could now extract the data for a period from the BRMS log file into a work file. I used the method I described in the post Creating a SQL table "on the fly" to create a work file in QTEMP. Below is a snippet of the SQLRPGLE program I created to do this:

01 dcl-s wkDateTime char(13) ; 02 dcl-s wkString char(500) ; 03 wkDateTime = %char(%date() - %days(1):*cymd0) + '200000' ; 04 wkString = 'CREATE TABLE QTEMP/@WORK AS + 05 (SELECT LGCGNAM,LGMID,LGDATE,LGTIME,LGSEV,+ 06 LGUSER,LGJOB,LGJNBR,+ 07 CAST(LGMDTA AS CHAR(200)) AS MSGDATA,+ 08 CONCAT(DIGITS(LGDATE),DIGITS(LGTIME)) + 09 AS DATETIME + 10 FROM QUSRBRM/QA1ALG + 11 WHERE CONCAT(DIGITS(LGDATE),DIGITS(LGTIME)) + 12 >= ''' wkDateTime + ''') + 13 WITH DATA' ; 14 exec sql EXECUTE IMMEDIATE :wkString ;

Lines 1 and 2 show that I am starting to dabble with the new TR7 totally free RPG, we need to come up with a name for this, perhaps RPG5/RPG5? I am not going to go into much details as to what these lines do, except to say that the dcl-s is used to define stand alone fields.

On line 3 I am making a pseudo-timestamp field I can use to compare with data in the Log file. The date field, LGDATE is seven long and is in the *CYMD format (CYYMMDD). Thus I take today’s date, %date(), subtract one day, %days(1), to get yesterday’s date, convert it to *CYMD without a date seperator character, and append 200000, or 8:00 PM, to it.

Lines 4 – 13 are where I create the SQL statement I will execute later. I am not going to describe in detail the CREATE TABLE AS as I did so in the Creating a SQL table "on the fly" post. I am only describe any special things I put into the statement.

The LGMDTA field is a VARCHAR, variable length character, field that takes up 32002 characters in the Log file. I do not care for most of those characters, only the first 200. If I used substring function thus: SUBSTRING(LGMDTA,1,200), I do get the first 200 characters of the data, but it is still a VARCHAR field. By using the CAST function, on line 7, I can define a new column (field) MSGDATA that is a 200 long CHAR field, which just takes the first 200 characters of the LGMDTA.

Having created a comparison pseudo-timestamp, wkDateTime, I need to be able to compare it to the same in the SQL statement. I can use the CONCAT two columns together, even if they are numeric as LGDATE and LGTIME are. But it removes any leading zeroes, therefore, the value "022435" becomes "22435 ", that is 22435 followed by a blank. If I used the CHAR function, as in CONCAT(CHAR(LGDATE),CHAR(LGTIME)) also removed the leading zeroes. After some investigation I found that if I used the DIGITS is returned the alphanumeric version of the number with the leading zero.

Having the desired SQL statement in the field wkString I then execute it using the EXECUTE IMMEDIATE, on line 14, to create the work file.

Now I can create any indexes I need to sort the data contained within the work file. For example:

CREATE INDEX QTEMP/@INDEX1 ON QTEMP/@WORK (LGCGNAM,LGUSER,LGJOB,LGJNBR,DATETIME,LGMID) RCDFMT INDEX1 ;

For more information on building indexes read the Add key to SQL table in QTEMP post.

When I look in the created work file I use the Message id, LGMID, column to determine if there was an error or not. The following BRM messages are the ones I found most useful:

Message id	Description
BRM1380	Processing started
BRM1049	Completed without errors
BRM10A1	Completed with errors
BRM1657	Cancelled
BRM1970	Completed abnormally
BRM1046	Power down and IPL started before save completed

You can get the description for any message id using the following commands:

BRM messages, for example BRM1970:

DSPMSGD RANGE(BRM1970) MSGF(QBRM/Q1AMSGF)

"CPF" messages, for example CPC3701:

DSPMSGD RANGE(CPC3701)

WARNING: IBM Support did warn me that BRMS file structures will be changing with the next release, 7.2. I was told to check the “What is new in this Release" when 7.2 is released to see what changes would need to be made.

 

You can learn more about these on the IBM website:

• SQL: Casting between data types
• SQL: CONCAT
• SQL: DIGITS
• SQL: EXECUTE IMMEDIATE

 

This article was written for IBM i 7.1, and it should work with earlier releases too.