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.

Tekcins
  • 21
  • 1
  • 1
  • 3

1 Answers1

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.

peterh
  • 2,137
  • 8
  • 28
  • 41
Jun Su Lee
  • 9
  • 1