Hi,We have an application team planning for a feature and have requested a table to be redesigned to use for high frequency, 24/7, single row inserts by multiple transactions in parallel.This particular table will function as sort of a logging table where you in certain cases can backtrack the feature. So the main workload for the table will be single inserts (with rare selects). No updates or deletes.Some table details- The first column is a DEC(16,0) and will be a application controlled sequence, counting upwards 1, 2, 3....- There is only a few additional columns, no XML/LOB/CLOB etc.- There is a unique and clustering index on the first column- There is no good partition key on current columnsSome considerations I have is regarding performance, locking contention and whether to go for PGB or PBR.PBG or PBR?My take is that PBR should have an advantage over PBG in regard to parallelism.One could create a sequence object, add a new column as a partitioning column and in the insert use the sequence objects number to generate a unique value that's also good for partitioning (you reformat the sequence number a little). Would this validate using PBR over PBG? It there any other methods or preferences to take into consideration in this scenario?Locking contentionAs I understand it, when Db2 does an insert it looks for a suitable page, puts it into the BP, X lock it on row level and writes to that page inside the BP. Later, depending on some stuff, it will be written to disk.Since it will be a lot of parallel inserts, all wanting to be placed at the end of the table (or possible end of partition in case of PBR), is there a danger of page locking contention or lock escalation? I'm pondering if LOCKSIZE ROW is a good idea to avoid lock escalation.Also I'm thinking about APPEND YES. Due to the unique index on first column, who happen to act as a sequence, all new columns will want to end up at the end. So is it more efficient to use APPEND YES to avoid Db2 looking for a suitable place to put the row and just have it forcefully put in the end?PerformanceAll design decisions have some performance implication and LOCKSIZE ROW, as well as APPEND YES, could be considered an expensive in a lot of cases. Always the risk-reward...Looking forward to some great insights!
UPS6756, #1544403 Oleander Drive, Suite CWilmington, NC 28403 Phone: (910) 660-8649Fax: (910) 523-5504