I would like to knwo if the MAX_LOG is set to 60 and NUM_LOG_SPAN is set to 80 which parameter does the optimizer choose to cut off
log running transactions.
LOGFILSIZ - 26620
LOGPRIMARY = 50
LOGSECODARY = 80
log space is 100GB
I read the defination of both paramters from DB2 manual as
MAX_LOG : this checks for the percentage of the log space as cut off
NUM_LOG_SPAN : this check the number of logs before cutting transaction.
Whichever occurs first. MAX_LOG will fire when a transaction uses 60% of the primary log space, roughly 30 files worth, but this could be in all 50 files. NUM_LOG_SPAN will fire when a transaction spans 80 files. It can consume very little space in those 80 files.
So just to confirm when you say "MAX_LOG will fire when a transaction uses 60% of the primary log space, roughly 30 files worth, but this could be in all 50 files. "
Does that mean a transaction will not fill up and one single log completely before moving to the next log file and then go on to consume entire 50 files.
No, that is not what I meant. Assuming that you have multiple connections performing work at the same time, those separate transactions will fill up the current transaction log together, then a new log file will be used. In order for the MAX_LOGS trigger to fire, a single transaction has to use 60% of the total primary space. It does not necessarily need to be 30 complete files. It could be 60% in all 50 files, or several full and some not full. The point is that 60% of the total available primary log space is consumed by a single transaction, how that is done is irrelevant.