Work File usage questions

Tushar Jha

Work File usage questions

Esteemed Experts,

 I had a few questions regarding usage of tablespaces of DSNDB07 by DB2 SQLs :

 

  • Is a CTE, like the DGTT ( and scrollable static cursor not too sure about that as well ), limited to only one tablespace inside DSNDB07. I believe the usage for CGTT, views, nested table expressions, subquery, joins and of course sorts required for the SQL and pretty much anything else can span more than one work file tablespace.

 

  • Also, if one has all the tablespaces in DSNDB07 as PBG with PRIQTY,SECQTY as -1,-1 and zparm WFDBSEP ( the hard separator zparm ) set to no , how would DB2 separate the work of DGTT ( and CTE , if they are also confined to one tablespace ) with sorts or anything else

 

  • According to DB2 knowledge center :

             

“After the selection based on the table space attributes, Db2 allocates the work files based on the overall record length. When the record length (data + key + prefix) is greater than 100 bytes, Db2 attempts to create the work file in a table space with 32 KB page size. If the record length is 100 bytes or less, Db2 prefers a table space with the 4 KB page size.”

 

Calculating the record length for a DGTT or a CTE is probably easy, but I am not too sure how do I do that for the sorts involved. Do I go through the plan_table to find all the sorts being done and then calculate the data that for each sort.

 

We are investigating an issue, where we had reason code 00C90085 error due to extent limit reached on 4K DSNDB07 tablespace. The SQL causing the error, is a rather huge sql with many CTEs ( each having joins, CASE expressions ) and a group by on a huge number of columns.

 

I would greatly appreciate, if you can shed insights on anything related to this.

 

Thanks a lot, in advance.

 

Regards,

Tushar Jha