2

When doing the following merge (tempchoices is a temporary table created with DECLARE GLOBAL TEMPORARY TABLE tempchoices LIKE CHOICES NOT LOGGED IN importtemp ON COMMIT PRESERVE ROWS) all my diskspace gets used and I receive the "The file system is full" error.

MERGE into CHOICES ar  
USING (SELECT * from session.tempchoices ) ac  
ON ar.CHOICE_NAME = ac.CHOICE_NAME AND ar.CHOICE_LIST_NAME = 
'account' AND ar.LOCALE = 'de_CH'
WHEN MATCHED and ar.DISPLAY_NAME != ac.DISPLAY_NAME THEN 
UPDATE SET DISPLAY_NAME = ac.DISPLAY_NAME, DEPENDENT_VALUE = 
ac.DEPENDENT_VALUE 
WHEN NOT MATCHED THEN 
INSERT (CHOICE_LIST_NAME, CHOICE_NAME, DISPLAY_NAME, VALUE, LOCALE, 
SORT_ORDER, DEPENDENT_VALUE) 
VALUES (ac.CHOICE_LIST_NAME, ac.CHOICE_NAME, ac.DISPLAY_NAME, ac.VALUE, 
ac.LOCALE, ac.SORT_ORDER, ac.DEPENDENT_VALUE)

How can I avoid this error?

Resizing the disk is not an option.

What I have tried is setting the DB2_OPT_MAX_TEMP_SIZE option but with no observable result.

Both tables contain about 600'000 entries.

DB2 Version is 10.5 and I observe this both on Linux and Windows servers.

closed as unclear what you're asking by mustaccio, Michael Green, Colin 't Hart, Vérace, Marco Jan 10 at 14:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Start with the detailed access plan from db2exfmt tool. What indexes exist on table CHOICES ? Did you also index tempchoices on CHOICE_NAME before running the merge? What is the size of the bufferpool(s) allocated to the tablespace(s) containing CHOICES and tempchoices? Is STMM enabled? – mao May 9 '18 at 9:35
0

Assuming the merge statement needs to use some amount of temp table size, you should increase the file system size where the related user temporary tablespace resides.

To check the path of user temporary tablespace,

  • check db2diag message that may indicate which file system is related.
  • And also, db2pd -db <dbname> -tablespace

You may add new user temp tablespace on new path that has enough space for the same page size of related tables.

  • That would be system temporary tablespace(s), not user. And the page size required is not related to the "related tables", but to the width of the row set being sorted. – mustaccio Aug 25 '18 at 10:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.