SQL MERGE and run out of temp dbs

IDS12.10 FC4. I am running a simple MERGE statement on two big tables ( 12 and 16 millions each), merge into archive_0005 a using archive_0005_me b on a.uuid=b.uuid when matched then update set a.update_dt=b.update_dt; What I got, a) I have Three temp dbs( dbtemp1-3) defined, but it reported " temporary DBspace dbtemp2 is full " when only one temp space(dbtemp2) was used up( see the temp space consumption history I recorded below) . b) PSORT_DBTEMP is set up with big size of free disk space, but it seems the MERGE SQL job did not use PSORT_DBTEMP . Attached some info you might need . Any comments ? Thanks Frank 1) SQL Error : 264: Could not write to a temporary file. 131: ISAM error: no free disk space Error in line 8Near character position 32 2) online log: 19:18:29 Maximum server connections 2 19:18:29 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 9, Llog used 2 19:23:49 WARNING: temporary DBspace dbtemp2 is full 19:23:59 Checkpoint Completed: duration was 0 seconds. 19:23:59 Tue Jun 7 - loguniq 252639, logpos 0x2e7d7018, timestamp: 0x20920c41 Interval: 466018 3) PSORT_DBTEMP setup: PSORT_DBTEMP=/dbbackup/tmp [informix@walter merge_test]$ df /dbbackup/tmp Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/vg.dbbackup-lv.dbbackup 2064229536 615781820 1343590936 32% /dbbackup 4) Onconfig file: DBSPACETEMP dbtemp1:dbtemp2:dbtemp3 5) temp space and usage log: Dbspaces address number flags fchunk nchunks pgsize flags owner name 50c72258 5 0x42001 9 1 2048 N TBA informix dbtemp1 50c72488 6 0x42001 10 1 2048 N TBA informix dbtemp2 50c726b8 7 0x42001 11 1 2048 N TBA informix dbtemp3 ...................................................... Chunks address chunk/dbs offset size free bpages flags pathname ==============usage 1: 50c82028 9 5 0 1000000 948747 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops 50c83028 10 6 0 1000000 410123 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops 50c84028 11 7 0 1000000 999947 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops ==============usage 2: 50c82028 9 5 0 1000000 948747 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops 50c83028 10 6 0 1000000 279051 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops 50c84028 11 7 0 1000000 999947 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops ==============usage 3: 50c82028 9 5 0 1000000 948747 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops 50c83028 10 6 0 1000000 82443 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops 50c84028 11 7 0 1000000 999947 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops ==============usage 4: 50c82028 9 5 0 1000000 948747 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops 50c83028 10 6 0 1000000 0 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops 50c84028 11 7 0 1000000 999947 PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops --94eb2c076228df56570534b5a9b7

PSORT_DBTEMP is only used for sort-work files so that's why it isn't being used for the temp tables needed to process the MERGE. That said, the engine should be using all three temp dbspaces. Make sure that the user's environment running the MERGE doesn't have a different definition for DBSPACETEMP as that will override the ONCONFIG setting. If all is well, time to open a PMR with IBM. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 7, 2016 at 4:02 PM, FRANK <yunyaoqu@gmail.com> wrote: > IDS12.10 FC4. > > I am running a simple MERGE statement on two big tables ( 12 and 16 > millions each), > > merge into archive_0005 a using archive_0005_me b > on a.uuid=b.uuid > when matched then > update set a.update_dt=b.update_dt; > > What I got, > > a) I have Three temp dbs( dbtemp1-3) defined, but it reported " > temporary DBspace dbtemp2 is full " when only one temp space(dbtemp2) > was used up( see the temp space consumption history I recorded below) . > > b) PSORT_DBTEMP is set up with big size of free disk space, but it seems > the MERGE SQL job did not use PSORT_DBTEMP . > > Attached some info you might need . > > Any comments ? > > Thanks > Frank > > 1) SQL Error : > 264: Could not write to a temporary file. > 131: ISAM error: no free disk space > Error in line 8> Near character position 32 > > 2) online log: > 19:18:29 Maximum server connections 2 > 19:18:29 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked > 0, Plog used 9, Llog used 2 > 19:23:49 WARNING: temporary DBspace dbtemp2 is full > 19:23:59 Checkpoint Completed: duration was 0 seconds. > 19:23:59 Tue Jun 7 - loguniq 252639, logpos 0x2e7d7018, timestamp: > 0x20920c41 Interval: 466018 > > 3) PSORT_DBTEMP setup: > PSORT_DBTEMP=/dbbackup/tmp > > [informix@walter merge_test]$ df /dbbackup/tmp > Filesystem 1K-blocks Used Available Use% Mounted on > /dev/mapper/vg.dbbackup-lv.dbbackup > > 2064229536 615781820 1343590936 32% /dbbackup > > 4) Onconfig file: > DBSPACETEMP dbtemp1:dbtemp2:dbtemp3 > > 5) temp space and usage log: > > Dbspaces > address number flags fchunk nchunks pgsize flags > owner name > > 50c72258 5 0x42001 9 1 2048 N TBA > informix dbtemp1 > 50c72488 6 0x42001 10 1 2048 N TBA > informix dbtemp2 > 50c726b8 7 0x42001 11 1 2048 N TBA > informix dbtemp3 > > ....................................................... > > Chunks > address chunk/dbs offset size free bpages > flags pathname > > ==============usage 1: > 50c82028 9 5 0 1000000 948747 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops > 50c83028 10 6 0 1000000 410123 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops > 50c84028 11 7 0 1000000 999947 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops > > ==============usage 2: > 50c82028 9 5 0 1000000 948747 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops > 50c83028 10 6 0 1000000 279051 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops > 50c84028 11 7 0 1000000 999947 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops > > ==============usage 3: > 50c82028 9 5 0 1000000 948747 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops > 50c83028 10 6 0 1000000 82443 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops > 50c84028 11 7 0 1000000 999947 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops > > ==============usage 4: > 50c82028 9 5 0 1000000 948747 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp1_ops > 50c83028 10 6 0 1000000 0 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp2_ops > 50c84028 11 7 0 1000000 999947 > PO-B-- /usr/informix/dev-links/ngdcops/dbtemp3_ops > > --94eb2c076228df56570534b5a9b7 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c05be5654658b0534b71293