-2

Can you help me understand the pros and cons of using extended_row_sz parameter in DB2 settings level?

Is that something can we set for a specific tablespace only?

Without extended_row_sz setting enabled we are not allowed to create a table with total column size more than 32K i.e. we are not allowed to create with VARCHAR(5000) data type. We can create using CLOB data type though; but CLOB is consuming lot of space even when sometime data is not there.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Koushik Chandra
  • 1,565
  • 12
  • 37
  • 73

2 Answers2

1

extended_row_sz is for lazy people who do not want to spend time designing their database for best performance. It was intended to enable the Oracle compatibility feature: the Oracle database does not have the row size limit and one would need to rewrite DDL for tables where row sizes exceeded the limit determined by the page size. After enabling the extended row size one would be able to run the Oracle DDL statements as is.

However, the row size limit in Db2 did not go anywhere. What happens after enabling the extended row size is that Db2 implicitly converts the data types of long character columns to CLOB if the total declared row width of a table exceeds the limit. It does so without telling you and without any performance optimisations that a careful DBA would be able to apply if they were to explicitly design tables to use CLOBs where necessary (LOB inlining and the LOB tablespace properties come to mind).

So, to answer your question about pros and cons of extended_row_sz: it saves you some effort at the database design time, at the cost of potential performance problems, which are not very easy to diagnose and require database redesign anyway, in the future.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
0

Extended row size is an ability to bypass the Row Size Limit (it depends on a page size) for a given table.
Briefly: each table row in Db2 must physically reside in a single page. LOBs (not inlined) are not stored in that page physically, only pointers to them are stored on data pages. LOBs itself are stored in separate objects inside tablespaces.
Such an ability is turned on at the database (not at the table or tablespace) level.

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16