Author Topic: db2 cleanup against system log  (Read 1756 times)

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 573
    • View Profile
db2 cleanup against system log
« on: May 27, 2021, 08:08:00 AM »
Just wondering if its a common practice for folks to run arsmaint against the system log.

Ours is fairly large I guess, 1B+ rows. I am not sure in the scheme of things if that is "large" but it's taking up alot of space.

It's difficult for our client to get space which is.. strange.

We're considering doing a cleanup against the system log, unused storing, 84/85/83/82/81..226/65/66 (Need to check with compliance), arsmaint, etc.

Wondering if anyone does this? Is it common? we'd probably make a backup of all the tables, and compress it, then store it in TSM or our sharepoint.

Thanks gang
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: db2 cleanup against system log
« Reply #1 on: May 27, 2021, 12:28:25 PM »
Nope, nope, nope...  Adjust your retention settings for the System Log, and use arsmaint.  :)

-JD.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 573
    • View Profile
Re: db2 cleanup against system log
« Reply #2 on: May 28, 2021, 08:50:51 AM »
Got it, I figured. that's what our dba suggested.

I'd like to take a backup of all those tables either way to a flat file, compress it compress it compress it.

Have you seen it as a practice to run arsmaint against the system log? I have not even with 7-8 clients.

I reached out to the business folks and told them all we are logging. And they were shocked. haha
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: db2 cleanup against system log
« Reply #3 on: May 28, 2021, 01:55:38 PM »
Well, logging absolutely everything is a best practise.

Why?
  • It's way easier to diagnose problems when all the logs are at your disposal.
  • Having lots of logs can help you determine when a problem started happening, and what changes happened around that time.
  • Reporting is way more accurate when you have logs of everything that happens on the server.
  • Detecting bad behaviour/breaches is possible when you have logs for everything.
  • You may have legal/regulatory requirements to log everything.
  • Being able to pass an audit is way easier when you can just give auditors access to the System Log.

... and probably many more that I can't think of.  :)

Most large sites that I've been at have almost as much database storage allocated to the System Log as they do the rest of their Application Groups.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 573
    • View Profile
Re: db2 cleanup against system log
« Reply #4 on: September 08, 2021, 10:37:40 AM »
Bumping this back to life.

After trying some variations of the arsmaint command in my test environment,  nothing seems to give the expected results.

1) I set the retention of the system log to 365 days.
2) Ran this command:

Code: [Select]
arsmaint -cd -g 'System Log' -h archive -u user -p pw -t 2021-09-08
3) system log looks untouched

Anyone have any suggestions?

#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: db2 cleanup against system log
« Reply #5 on: September 09, 2021, 11:20:05 AM »
Let me take a wild guess:

Your system log table is expiration type segment.

Expiration by segment means you have to drop a whole table at a time, not just clean out some rows.

Because that table presumably has newer rows you can't drop the whole thing.

So close that table:

https://www.ibm.com/docs/en/cmofz/9.5.0?topic=arstblsp-examples

The following shows how to close the System Log table and forces the system log to a new tablespace:
    arstblsp -I instance_name -a 1 -g "System Log"

Presumably now you can drop that whole old system log table.

Ed
« Last Edit: June 23, 2022, 10:51:06 AM by Ed_Arnold »
#zOS #ODF

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 573
    • View Profile
Re: db2 cleanup against system log
« Reply #6 on: September 09, 2021, 01:40:24 PM »
I actually think I got this working.

From my dev env:
Code: [Select]
SA2                             ARCHIVE         T     2016-10-27-13.24.20.294169
SL2                             ARCHIVE         T     2016-10-11-16.02.31.098125
SL3                             ARCHIVE         T     2020-02-22-08.40.37.363118
SL4                             ARCHIVE         T     2020-05-22-08.19.27.671807
SL5                             ARCHIVE         T     2021-06-28-10.33.44.943286

Last row From SL2: (Or I guess just use date created for SL3)
Code: [Select]
2020-02-22-14.40.33.802989
2020-02-22-14.40.33.965463
10000000 record(s) selected.

2020-02-22 was 565 days ago. Set app group retention to, 564 days, or 2020-02-22 + 1 day.

Issue arsmaint command:
Code: [Select]
arsmaint -cd -g 'System Log' -h archive -u jeff -p jeff.stash
Code: [Select]
Application Group Segment Expire: Name(System Log) Agid(5006) Seg(SL2)
Check the SL* Tables
Code: [Select]
SA2                             ARCHIVE         T     2016-10-27-13.24.20.294169
SL3                             ARCHIVE         T     2020-02-22-08.40.37.363118
SL4                             ARCHIVE         T     2020-05-22-08.19.27.671807
SL5                             ARCHIVE         T     2021-06-28-10.33.44.943286

Check the system log. Verified that the first entry was from, you guessed it. 02-22 (After the hh:mm:ss of the date in SL2..)
Code: [Select]
[b]2020-02-22 09:40:34.128011[/b]
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING