RE: Multiple Temp dbspaces

We had this same problem, and got an answer from someone on the IIUG. It's something to do with implicit and explicit tables. If you create a temp table using the SELECT * FROM [tabname] INTO TEMP [temptabname] the table is fragmented across all available temp dbspaces. However if you use the CREATE TEMP TABLE you need to specify the FRAGMENT BY ROUND ROBIN IN clause. There should be a thread somewhere in the archives that it explains it a bit better - but this is the general gist. -----Original Message----- From: Jay [mailto:remove:jay@td.ca] Sent: Wednesday, 16 July, 2003 9:04 pm To: informix-list@iiug.org Subject: Multiple Temp dbspaces Hi, I'm running 7.24, and have listed 3 temporary dbspaces in DBSPACETEMP, but when I load a temporary table only the first dbspace is being used. I have set PDQPRIORITY to 100, and all temp dbspaces reside on separate disks. Theonly way I can make Informix use all 3 dbspaces is if I use the "FRAGMENT BY ROUND ROBIN IN tempdbs1. tempdbs2, tempdbs3" clause of the "CREATE TEMP TABLE" statement. I was under the impression that all I have to do for parallelism against temp dbspaces was list them in the onconfig file, am I wrong and set PDQPRIORITY >0, am I wrong? Thanks in advance j. onstat -dDbspaces address number flags fchunk nchunks flags owner name 500380ec 1 1 1 1 N informix rootdbs 50039384 2 1 2 1 N informix fragdbs1 500393f0 3 2001 3 1 N T informix tempdbs1 5003945c 4 1 7 2 N informix testdbs 500394c8 5 1 10 4 N informix datadbs 50039534 6 2001 4 1 N T informix tempdbs2 500395a0 7 1 18 1 N informix fragdbs2 5003960c 8 1 19 1 N informix fragdbs3 50039678 9 1 20 1 N informix fragdbs4 500396e4 10 1 21 1 N informix fragdbs6 50039750 11 1 22 1 N informix fragdbs5 500397bc 12 1 23 1 N informix fragdbs7 50039828 13 1 24 1 N informix fragdbs8 50039894 14 1 25 1 N informix residentdbs 50039900 15 2001 5 1 N T informix tempdbs3 15 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname 50038158 1 1 0 256000 167517 PO- /dev/prod2_rootdbs 5003849c 2 2 0 167936 16359 PO- /dev/prod2_fragdbs1 50038570 3 3 0 442368 420261 PO- /dev/prod2_mtdbs1 50038644 4 6 0 496000 477243 PO- /dev/prod2_testdbs3 50038718 5 15 0 51200 32443 PO- /dev/prod2_tempdbs4 500387ec 7 4 0 496000 217487 PO- /dev/prod2_testdbs1 500388c0 8 4 0 496000 495997 PO- /dev/prod2_testdbs2 50038994 10 5 0 496000 338945 PO- /dev/prod2_datadbs1 50038a68 11 5 0 496000 495997 PO- /dev/prod2_datadbs2 50038b3c 12 5 0 496000 495997 PO- /dev/prod2_datadbs3 50038c10 13 5 0 496000 495997 PO- /dev/prod2_datadbs4 50038ce4 18 7 0 167936 16359 PO- /dev/prod2_fragdbs2 50038db8 19 8 0 167936 16359 PO- /dev/prod2_fragdbs3 50038e8c 20 9 0 167936 16359 PO- /dev/prod2_fragdbs4 50038f60 21 10 0 221184 110971 PO- /dev/prod2_fragdbs6 50039034 22 11 0 221184 110971 PO- /dev/prod2_fragdbs5 50039108 23 12 0 221184 110971 PO- /dev/prod2_fragdbs7 500391dc 24 13 0 221184 110971 PO- /dev/prod2_fragdbs8 500392b0 25 14 0 258048 257755 PO- /dev/prod2_residentdbs Onconfig #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: INFORMIX-OnLine Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/prod2_rootdbs # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 1024000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 80000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 6 # Number of logical log files LOGSIZE 45000 # Logical log size (Kbytes) # Diagnostics MSGPATH /isgprod2/inf7dev/online.log # System message log file path CONSOLE /isgprod2/inf7dev/console.log # System console message path ALARMPROGRAM /isgprod2/inf7dev/etc/no_log.sh # Alarm program path # System Archive Tape Device TAPEDEV /dev/rmt1 # Tape device path TAPEBLK 512 # Tape block size (Kbytes) TAPESIZE 20971520 #Maximum amount of data to put on tape (kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 512 # Log tape block size (Kbytes) LTAPESIZE 12582412 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a OnLineinstance DBSERVERNAME aixprod2_on # Name of default database server DBSERVERALIASES rt_aixprod2_on # List of alternate dbservernames DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 4 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone NOAGE 0 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 10000 # Maximum number of locks#BUFFERS 8000 # Maximum number of shared buffers BUFFERS 100000 NUMAIOVPS 6 # Number of IO vps PHYSBUFF 64 # Physical log buffer si