IDS 12.10 - dbimport very slow

Hello, we have a new server (Windows 2012, 8 GB RAM, 4 x 600 GB SAS 15k HDD). On the server we have installed an IBM Informix Dynamic Server Version 12.10.FC1WE. The database that should be moved to this server is not very big. The dbexport size is about 700 mb. The dbimport took more then three days. On another server (with only one SATA 2 HDD and IDS 11.50) the import takes only 45 minutes. I tried to find out why the import takes three days to be completed, but I had no success. I disabled the virus application (the ifmxdata-folder is already excluded), but that had no effect on the dbimport. Is there anything we can change in the onconfig, to speed up the dbimport? ---------------------------------------------------------------------------- ROOTNAME rootdbs ROOTPATH D:\\\\IFMXDATA\\\\ol_company\\\\rootdbs_dat.000 # Path for device containingroot dbspace ROOTOFFSET 0 ROOTSIZE 204800 # Size of root dbspace (Kbytes) MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 ################################################################### PHYSFILE 50000 PLOG_OVERFLOW_PATH PHYSBUFF 128 ################################################################### LOGFILES 106 LOGSIZE 10000 DYNAMIC_LOGS 1 LOGBUFF 64 ################################################################### LTXHWM 70 LTXEHWM 80 ################################################################### MSGPATH D:\\\\informix\\\\ol_company.log # System message log file path CONSOLE D:\\\\informix\\\\conol_company.log # System console message path ################################################################### TBLTBLFIRST 0 TBLTBLNEXT 0 TBLSPACE_STATS 1 ################################################################### DBSPACETEMP temp1dbs, temp2dbs, temp3dbs, temp4dbs, temp5dbs, temp6dbs SBSPACETEMP ################################################################### SBSPACENAME csg_sblob_dat # Default sbspace SYSSBSPACENAME csg_sblob_dat # Default System sbspace ONDBSPACEDOWN 2 ################################################################### SERVERNUM 0 # Unique id corresponding to a server instance DBSERVERNAME ol_company # Name of default Dynamic Server DBSERVERALIASES # List of alternate dbserver names FULL_DISK_INIT 0 ################################################################### onsoctcp,1,200,CPU LISTEN_TIMEOUT 900 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 NS_CACHE host=900,service=900,user=900,group=900 ################################################################### MULTIPROCESSOR 1 VPCLASS cpu,num=3 VP_MEMORY_CACHE_KB 0 SINGLE_CPU_VP 0 ################################################################### #VPCLASS aio,num=1 CLEANERS 8 DIRECT_IO 0 ################################################################### LOCKS 500000 DEF_TABLE_LOCKMODE row ################################################################### RESIDENT 1 SHMBASE 0x80000000L SHMVIRTSIZE 32656 SHMADD 8192 EXTSHMADD 8192 SHMTOTAL 0 SHMVIRT_ALLOCSEG 0,3#SHMNOACCESS 0x70000000-0x7FFFFFFF ################################################################### CKPTINTVL 300 RTO_SERVER_RESTART 0 BLOCKTIMEOUT 3600 ################################################################## CONVERSION_GUARD 2 RESTORE_POINT_DIR $INFORMIXDIR\\\\tmp ################################################################### TXTIMEOUT 900 DEADLOCK_TIMEOUT 900 HETERO_COMMIT 0 ################################################################### TAPEDEV D:\\\\IFMXBKUP\\\\ifmxbkup.bak TAPEBLK 512 TAPESIZE 0 ################################################################### LTAPEDEV D:\\\\IFMXBKUP\\\\logs.bak LTAPEBLK 512 LTAPESIZE 0 ################################################################### BAR_ACT_LOG D:\\\\informix\\\\bar_ol_company.log #Path of log file for onbar.exe BAR_DEBUG_LOG D:\\\\informix\\\\bar_ol_company.log #Path of the debug log for onbar.exe BAR_DEBUG 0 BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 20 BAR_XFER_BUF_SIZE 15 RESTARTABLE_RESTORE ON BAR_PROGRESS_FREQ 0 BAR_BSALIB_PATH BACKUP_FILTER RESTORE_FILTER BAR_PERFORMANCE 0 BAR_CKPTSEC_TIMEOUT 16 ################################################################### PSM_DBS_POOL DBSPOOL PSM_LOG_POOL LOGPOOL ################################################################### DD_HASHSIZE 31 DD_HASHMAX 10 ################################################################### DS_HASHSIZE 31 DS_POOLSIZE 127 ################################################################## PC_HASHSIZE 31 PC_POOLSIZE 127 PRELOAD_DLL_FILE ################################################################### STMT_CACHE 0 STMT_CACHE_HITS 0 STMT_CACHE_SIZE 512 STMT_CACHE_NOLIMIT 0 STMT_CACHE_NUMPOOL 1 ################################################################### USEOSTIME 0 STACKSIZE 64 ALLOW_NEWLINE 1 USELASTCOMMITTED NONE ################################################################### FILLFACTOR 90 MAX_FILL_DATA_PAGES 0 BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default ONLIDX_MAXMEM 5120 ################################################################### MAX_PDQPRIORITY 99 DS_MAX_QUERIES DS_TOTAL_MEMORY DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 128 DATASKIP ################################################################### OPTCOMPIND 2 DIRECTIVES 1 EXT_DIRECTIVES 0 OPT_GOAL -1 IFX_FOLDVIEW 1 STATCHANGE 10 USTLOW_SAMPLE 0 ################################################################### BATCHEDREAD_TABLE 1 BATCHEDREAD_INDEX 1 ################################################################### EXPLAIN_STAT 1 #SQLTRACE level=low,ntraces=1000,size=2,mode=global ################################################################### #DBCREATE_PERMISSION informix #DB_LIBRARY_PATH IFX_EXTEND_ROLE 1 SECURITY_LOCALCONNECTION UNSECURE_ONSTAT ADMIN_USER_MODE_WITH_DBSA ADMIN_MODE_USERS SSL_KEYSTORE_LABEL ################################################################### PLCY_POOLSIZE 127 PLCY_HASHSIZE 31 USRC_POOLSIZE 127 USRC_HASHSIZE 31 ################################################################### SQL_LOGICAL_CHAR OFF ################################################################### SEQ_CACHE_SIZE 10 ################################################################### ENCRYPT_HDR ENCRYPT_SMX ENCRYPT_CDR 0 ENCRYPT_CIPHERS ENCRYPT_MAC ENCRYPT_MACFILE ENCRYPT_SWITCH ################################################################### CDR_EVALTHREADS 1,2 CDR_DSLOCKWAIT 5 CDR_QUEUEMEM 4096 CDR_NIFCOMPRESS 0 CDR_SERIAL 0 CDR_DBSPACE@@

Hi Markus, Is your import logged ? Regards, Henri MOUKOURI Le 25 juin 2013 à 10:45, "MARKUS BRINKBäUMER" <markus.brinkbaeumer@gmail.com> a écrit : > Hello, > > we have a new server (Windows 2012, 8 GB RAM, 4 x 600 GB SAS 15k HDD). On the > server we have installed an IBM Informix Dynamic Server Version 12.10.FC1WE. > > The database that should be moved to this server is not very big. The dbexport> size is about 700 mb. > > The dbimport took more then three days. On another server (with only one SATA > 2 HDD and IDS 11.50) the import takes only 45 minutes. I tried to find out why > the import takes three days to be completed, but I had no success. > > I disabled the virus application (the ifmxdata-folder is already excluded), > but that had no effect on the dbimport. > > Is there anything we can change in the onconfig, to speed up the dbimport? > > ---------------------------------------------------------------------------- > > ROOTNAME rootdbs > ROOTPATH D:\\\\IFMXDATA\\\\ol_company\\\\rootdbs_dat.000 # Path for device containing> root dbspace > ROOTOFFSET 0 > ROOTSIZE 204800 # Size of root dbspace (Kbytes) > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored root > MIRROROFFSET 0> > ################################################################### > > PHYSFILE 50000 > PLOG_OVERFLOW_PATH > PHYSBUFF 128> > ################################################################### > > LOGFILES 106 > LOGSIZE 10000 > DYNAMIC_LOGS 1 > LOGBUFF 64> > ################################################################### > > LTXHWM 70 > LTXEHWM 80> > ################################################################### > > MSGPATH D:\\\\informix\\\\ol_company.log # System message log file path > CONSOLE D:\\\\informix\\\\conol_company.log # System console message path > > ################################################################### > > TBLTBLFIRST 0 > TBLTBLNEXT 0 > TBLSPACE_STATS 1> > ################################################################### > > DBSPACETEMP temp1dbs, temp2dbs, temp3dbs, temp4dbs, temp5dbs, temp6dbs > SBSPACETEMP> > ################################################################### > > SBSPACENAME csg_sblob_dat # Default sbspace > SYSSBSPACENAME csg_sblob_dat # Default System sbspace > ONDBSPACEDOWN 2> > ################################################################### > > SERVERNUM 0 # Unique id corresponding to a server instance > DBSERVERNAME ol_company # Name of default Dynamic Server > DBSERVERALIASES # List of alternate dbserver names > FULL_DISK_INIT 0> > ################################################################### > > onsoctcp,1,200,CPU > LISTEN_TIMEOUT 900 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1 > NS_CACHE host=900,service=900,user=900,group=900> > ################################################################### > > MULTIPROCESSOR 1 > VPCLASS cpu,num=3 > VP_MEMORY_CACHE_KB 0 > SINGLE_CPU_VP 0> > ################################################################### > > #VPCLASS aio,num=1 > CLEANERS 8 > DIRECT_IO 0> > ################################################################### > > LOCKS 500000 > DEF_TABLE_LOCKMODE row> > ################################################################### > > RESIDENT 1 > SHMBASE 0x80000000L > SHMVIRTSIZE 32656 > SHMADD 8192 > EXTSHMADD 8192 > SHMTOTAL 0 > SHMVIRT_ALLOCSEG 0,3> #SHMNOACCESS 0x70000000-0x7FFFFFFF > > ################################################################### > > CKPTINTVL 300 > RTO_SERVER_RESTART 0 > BLOCKTIMEOUT 3600> > ################################################################## > > CONVERSION_GUARD 2 > RESTORE_POINT_DIR $INFORMIXDIR\\\\tmp> > ################################################################### > > TXTIMEOUT 900 > DEADLOCK_TIMEOUT 900 > HETERO_COMMIT 0> > ################################################################### > > TAPEDEV D:\\\\IFMXBKUP\\\\ifmxbkup.bak > TAPEBLK 512 > TAPESIZE 0> > ################################################################### > > LTAPEDEV D:\\\\IFMXBKUP\\\\logs.bak > LTAPEBLK 512 > LTAPESIZE 0> > ################################################################### > > BAR_ACT_LOG D:\\\\informix\\\\bar_ol_company.log #Path of log file for onbar.exe > BAR_DEBUG_LOG D:\\\\informix\\\\bar_ol_company.log #Path of the debug log for > onbar.exe > BAR_DEBUG 0 > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 20 > BAR_XFER_BUF_SIZE 15 > RESTARTABLE_RESTORE ON > BAR_PROGRESS_FREQ 0 > BAR_BSALIB_PATH > BACKUP_FILTER > RESTORE_FILTER > BAR_PERFORMANCE 0 > BAR_CKPTSEC_TIMEOUT 16> > ################################################################### > > PSM_DBS_POOL DBSPOOL > PSM_LOG_POOL LOGPOOL> > ################################################################### > > DD_HASHSIZE 31 > DD_HASHMAX 10> > ################################################################### > > DS_HASHSIZE 31 > DS_POOLSIZE 127> > ################################################################## > > PC_HASHSIZE 31 > PC_POOLSIZE 127 > PRELOAD_DLL_FILE> > ################################################################### > > STMT_CACHE 0 > STMT_CACHE_HITS 0 > STMT_CACHE_SIZE 512 > STMT_CACHE_NOLIMIT 0 > STMT_CACHE_NUMPOOL 1> > ################################################################### > > USEOSTIME 0 > STACKSIZE 64 > ALLOW_NEWLINE 1 > USELASTCOMMITTED NONE> > ################################################################### > > FILLFACTOR 90 > MAX_FILL_DATA_PAGES 0 > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > ONLIDX_MAXMEM 5120> > ################################################################### > > MAX_PDQPRIORITY 99 > DS_MAX_QUERIES > DS_TOTAL_MEMORY > DS_MAX_SCANS 1048576 > DS_NONPDQ_QUERY_MEM 128 > DATASKIP> > ################################################################### > > OPTCOMPIND 2 > DIRECTIVES 1 > EXT_DIRECTIVES 0 > OPT_GOAL -1 > IFX_FOLDVIEW 1 > STATCHANGE 10 > USTLOW_SAMPLE 0> > ################################################################### > > BATCHEDREAD_TABLE 1 > BATCHEDREAD_INDEX 1> > ################################################################### > > EXPLAIN_STAT 1 > #SQLTRACE level=low,ntraces=1000,size=2,mode=global> > ################################################################### > > #DBCREATE_PERMISSION informix > #DB_LIBRARY_PATH > IFX_EXTEND_ROLE 1 > SECURITY_LOCALCONNECTION > UNSECURE_ONSTAT > ADMIN_USER_MODE_WITH_DBSA > ADMIN_MODE_USERS > SSL_KEYSTORE_LABEL> > ################################################################### > > PLCY_POOLSIZE 127 > PLCY_HASHSIZE 31 > USRC_POOLSIZE 127 > USRC_HASH

Yep it is. I will try with dbimport -l now.

Two hours ago, I've started the import with "dbimport testdb -d dbspace -l". Since 1,5 hours the import hangs at an table wich contains 9 columns (including a text-column) and 23250 rows. The size of the unload file is 2241 kb. Logical logs are not full. I have no explanation for this behaviour.

What does onstat -g ses for that session report? Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 25, 2013 at 8:37 AM, MARKUS BRINKBäUMER < markus.brinkbaeumer@gmail.com> wrote: > Two hours ago, I've started the import with "dbimport testdb -d dbspace > -l". > Since 1,5 hours the import hangs at an table wich contains 9 columns > (including a text-column) and 23250 rows. The size of the unload file is > 2241 > kb. > > Logical logs are not full. I have no explanation for this behaviour. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133e414cb0eec04dffb20c4

Hi, What's showing up in instance log ? Regards, Henri MOUKOURI Le 25 juin 2013 à 13:37, "MARKUS BRINKBäUMER" <markus.brinkbaeumer@gmail.com> a écrit : > Two hours ago, I've started the import with "dbimport testdb -d dbspace -l". > Since 1,5 hours the import hangs at an table wich contains 9 columns > (including a text-column) and 23250 rows. The size of the unload file is 2241 > kb. > > Logical logs are not full. I have no explanation for this behaviour. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hello Art, thank you for your reply. Here is the output from onstat -g: IBM Informix Dynamic Server Version 12.10.FC1WE -- On-Line -- Up 05:27:46 --1398720 Kbytes session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 45 administ - VMAPP 3400 VMAPP.bo 1 208896 193456 off Program : D:\\\\informix\\\\bin\\\\dbimport.exe tid name rstcb flags curstk status 93 sqlexec d2aedd78 Y-BP--- 4048 cond wait netnorm - Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 45 V d3d2c040 204800 14656 679 7 45*O0 V d3af5040 4096 784 1 1 name free used name free used overhead 0 6624 scb 0 176 opentable 0 9136 filetable 0 2224 ru 0 608 blobio 0 9200 log 0 16544 temprec 0 33984 blob 0 368 keys 0 1664 ralloc 0 25536 gentcb 0 1808 ostcb 0 3024 sqscb 0 66672 sql 0 80 hashfiletab 0 560 osenv 0 3536 sqtcb 0 9504 fragman 0 1520 cdr 0 480 udr 0 208 sqscb info scb sqscb optofc pdqpriority optcompind directives d366f260 d3459030 0 0 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 45 INSERT csgtest CR Wait 0 0 9.240 Off Current statement name : loadcur Current SQL statement (1509) : insert into "csg".cs_lagerproto values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ); Host variables : address type flags value ----------------------------------------- 0x00000000D3BAE120 CHAR 0x002 0x00000000D3BAE1B0 CHAR 0x002 0x00000000D3BAE240 CHAR 0x002 0x00000000D3BAE2D0 CHAR 0x002 0x00000000D3BAE360 CHAR 0x002 0x00000000D3BAE3F0 CHAR 0x002 0x00000000D3BAE480 CHAR 0x002 0x00000000D3BAE510 CHAR 0x002 0x00000000D3BAE5A0 CHAR 0x002 0x00000000D3BAE630 CHAR 0x002 0x00000000D3BAE6C0 CHAR 0x002 0x00000000D3BAE750 CHAR 0x002 0x00000000D3BAE7E0 CHAR 0x002 0x00000000D3BAE870 CHAR 0x002 0x00000000D3BAE900 CHAR 0x002 0x00000000D3BAE990 CHAR 0x002 0x00000000D3BAEA20 CHAR 0x002 0x00000000D3BAEAB0 CHAR 0x002 0x00000000D3BAEB40 CHAR 0x002 0x00000000D3BAEBD0 CHAR 0x002 0x00000000D3BAEC60 CHAR 0x002 0x00000000D3BAECF0 CHAR 0x002 0x00000000D3BAED80 CHAR 0x002 Last parsed SQL statement : insert into "csg".cs_lagerproto values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );

Hi Henri, this is the last output from the instance log. There are no errors: 12:39:24 Maximum server connections 1 12:39:24 Checkpoint Statistics - Avg. Txn Block Time 0.007, # Txns blocked 0, Plog used 36, Llog used 123 12:44:26 Checkpoint Completed: duration was 0 seconds. 12:44:26 Tue Jun 25 - loguniq 118, logpos 0x41a824, timestamp: 0x1aad21f Interval: 3921 12:44:26 Maximum server connections 1 12:44:26 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 28, Llog used 65 12:49:29 Checkpoint Completed: duration was 0 seconds. 12:49:29 Tue Jun 25 - loguniq 118, logpos 0x456084, timestamp: 0x1aada06 Interval: 3922 12:49:29 Maximum server connections 1 12:49:29 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 17, Llog used 60 12:52:29 Logical Log 118 - Backup Started 12:52:30 Logical Log 118 Complete, timestamp: 0x1aadec4. 12:52:32 Logical Log 118 - Backup Completed 12:54:31 Checkpoint Completed: duration was 0 seconds. 12:54:31 Tue Jun 25 - loguniq 119, logpos 0x1edc4, timestamp: 0x1aae23c Interval: 3923 12:54:31 Maximum server connections 2 12:54:31 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 20, Llog used 67 12:59:33 Checkpoint Completed: duration was 0 seconds. 12:59:33 Tue Jun 25 - loguniq 119, logpos 0x754c8, timestamp: 0x1aaed25 Interval: 3924 12:59:33 Maximum server connections 2 12:59:33 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 50, Llog used 87 13:04:35 Checkpoint Completed: duration was 0 seconds. 13:04:35 Tue Jun 25 - loguniq 119, logpos 0xb5a0c, timestamp: 0x1aaf53f Interval: 3925 13:04:35 Maximum server connections 2 13:04:35 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 26, Llog used 64 13:09:38 Checkpoint Completed: duration was 0 seconds. 13:09:38 Tue Jun 25 - loguniq 119, logpos 0xf0f04, timestamp: 0x1aafd35 Interval: 3926 13:09:38 Maximum server connections 2 13:09:38 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 18, Llog used 59 13:14:40 Checkpoint Completed: duration was 0 seconds. 13:14:40 Tue Jun 25 - loguniq 119, logpos 0x1310a4, timestamp: 0x1ab0568 Interval: 3927 13:14:40 Maximum server connections 2 13:14:40 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 24, Llog used 65 13:19:42 Checkpoint Completed: duration was 0 seconds. 13:19:42 Tue Jun 25 - loguniq 119, logpos 0x16d030, timestamp: 0x1ab0d56 Interval: 3928 13:19:42 Maximum server connections 2 13:19:42 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 19, Llog used 60 13:24:44 Checkpoint Completed: duration was 0 seconds. 13:24:44 Tue Jun 25 - loguniq 119, logpos 0x1a8f5c, timestamp: 0x1ab154e Interval: 3929 13:24:44 Maximum server connections 2 13:24:44 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 13, Llog used 59 13:29:47 Checkpoint Completed: duration was 0 seconds. 13:29:47 Tue Jun 25 - loguniq 119, logpos 0x1e961c, timestamp: 0x1ab1d85 Interval: 3930 13:29:47 Maximum server connections 2 13:29:47 Checkpoint Statistics - Avg. Txn Block Time 0.005, # Txns blocked 0, Plog used 22, Llog used 65 13:34:49 Checkpoint Completed: duration was 0 seconds. 13:34:49 Tue Jun 25 - loguniq 119, logpos 0x2250a8, timestamp: 0x1ab2576 Interval: 3931 13:34:49 Maximum server connections 2 13:34:49 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 21, Llog used 60 13:39:51 Checkpoint Completed: duration was 0 seconds. 13:39:51 Tue Jun 25 - loguniq 119, logpos 0x2612ec, timestamp: 0x1ab2d68 Interval: 3932 13:39:51 Maximum server connections 2 13:39:51 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 15, Llog used 60 13:44:53 Checkpoint Completed: duration was 0 seconds. 13:44:53 Tue Jun 25 - loguniq 119, logpos 0x2a0d38, timestamp: 0x1ab359d Interval: 3933 13:44:53 Maximum server connections 2 13:44:53 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 24, Llog used 63 13:49:56 Checkpoint Completed: duration was 0 seconds. 13:49:56 Tue Jun 25 - loguniq 119, logpos 0x2dbce8, timestamp: 0x1ab3d82 Interval: 3934 13:49:56 Maximum server connections 2 13:49:56 Checkpoint Statistics - Avg. Txn Block Time 0.007, # Txns blocked 0, Plog used 21, Llog used 59 13:54:58 Checkpoint Completed: duration was 0 seconds. 13:54:58 Tue Jun 25 - loguniq 119, logpos 0x317018, timestamp: 0x1ab4576 Interval: 3935 13:54:58 Maximum server connections 2 13:54:58 Checkpoint Statistics - Avg. Txn Block Time 0.007, # Txns blocked 0, Plog used 12, Llog used 60 14:00:00 Checkpoint Completed: duration was 0 seconds. 14:00:00 Tue Jun 25 - loguniq 119, logpos 0x38024c, timestamp: 0x1ab50f0 Interval: 3936 14:00:00 Maximum server connections 2 14:00:00 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 67, Llog used 105 14:05:03 Checkpoint Completed: duration was 0 seconds. 14:05:03 Tue Jun 25 - loguniq 119, logpos 0x3bf3a8, timestamp: 0x1ab5905 Interval: 3937 14:05:03 Maximum server connections 2 14:05:03 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 25, Llog used 63 14:10:05 Checkpoint Completed: duration was 0 seconds. 14:10:05 Tue Jun 25 - loguniq 119, logpos 0x3fb634, timestamp: 0x1ab60fa Interval: 3938 14:10:05 Maximum server connections 2 14:10:05 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 18, Llog used 60 14:15:07 Checkpoint Completed: duration was 0 seconds. 14:15:07 Tue Jun 25 - loguniq 119, logpos 0x43ca5c, timestamp: 0x1ab6932 Interval: 3939 14:15:07 Maximum server connections 2 14:15:07 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 24, Llog used 65 14:20:09 Checkpoint Completed: duration was 0 seconds. 14:20:09 Tue Jun 25 - loguniq 119, logpos 0x478ad0, timestamp: 0x1ab7122 Interval: 3940 14:20:09 Maximum server connections 2 14:20:09 Checkpoint Statistics - Avg. Txn Block Time 0.007, # Txns blocked 0, Plog used 19, Llog used 60 14:25:11 Checkpoint Completed: duration was 0 seconds. 14:25:12 Tue Jun 25 - loguniq 119, logpos 0x4b44b0, timestamp: 0x1ab7920 Interval: 3941 14:25:12 Maximum server connections 2 14:25:12 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 15, Llog used 60 14:30:14 Checkpoint Completed: duration was 0 seconds. 14:30:14 Tue Jun 25 - loguniq 119, logpos 0x4f5960, timestamp: 0x1ab8156 Interval: 3942 14:30:14 Maximum server connections 2 14:30:14 Checkpoint Statistics - Avg. Txn Block Time 0.007, # Txns blocked 0, Plog used 22, Llog used 65 14:35:10 Logical Log 119 - Backup Started 14:35:11 Logical Log 119 Complete, timestamp: 0x1ab8924. 14:35:14 Logical Log 119 - Backup Completed 14:35:16 Checkpoint Completed: duration was 0 seconds. 14:35:16 Tue Jun 25 - loguniq 120, logpos 0x11

Dear Marcus: I would suggest not using the "-l" option for the import. I have always had issues trying to run and import with the logging option and hence have always imported with no logging and turned the transaction logging on afterward. Most of my instances are running on Windows servers. Is the instance trying to add more shared memory segments during the import? Best regards, Martin M. Graney Queues Enforth Development, Inc. 14 Summer Street 2nd floor Malden, MA 02148 781-870-1131 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, June 25, 2013 10:13 AM To: ids@iiug.org Subject: Re: IDS 12.10 - dbimport very slow [30638] What does onstat -g ses for that session report? Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 25, 2013 at 8:37 AM, MARKUS BRINKBäUMER < markus.brinkbaeumer@gmail.com> wrote: > Two hours ago, I've started the import with "dbimport testdb -d > dbspace -l". > Since 1,5 hours the import hangs at an table wich contains 9 columns > (including a text-column) and 23250 rows. The size of the unload file > is > 2241 > kb. > > Logical logs are not full. I have no explanation for this behaviour. > > > > ************************************************************************** ***** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133e414cb0eec04dffb20c4 ************************************************************************** ***** Forum Note: Use "Reply" to post a response in the discussion forum.

No, it isn't. I agree to you about the -l parameter. Normally I don't use it.

I don't know if this applies to Windows, but setting environment variable FET_BUF_SIZE=32000 may help. It helps on Linux. On 6/25/2013 5:45 AM, MARKUS BRINKBäUMER wrote: > Hello, > > we have a new server (Windows 2012, 8 GB RAM, 4 x 600 GB SAS 15k HDD). On the > server we have installed an IBM Informix Dynamic Server Version 12.10.FC1WE. > > The database that should be moved to this server is not very big. The dbexport> size is about 700 mb. > > The dbimport took more then three days. On another server (with only one SATA > 2 HDD and IDS 11.50) the import takes only 45 minutes. I tried to find out why > the import takes three days to be completed, but I had no success. > > I disabled the virus application (the ifmxdata-folder is already excluded), > but that had no effect on the dbimport. > > Is there anything we can change in the onconfig, to speed up the dbimport? > > ---------------------------------------------------------------------------- > > ROOTNAME rootdbs > ROOTPATH D:\\\\IFMXDATA\\\\ol_company\\\\rootdbs_dat.000 # Path for device containing> root dbspace > ROOTOFFSET 0 > ROOTSIZE 204800 # Size of root dbspace (Kbytes) > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored root > MIRROROFFSET 0> > ################################################################### > > PHYSFILE 50000 > PLOG_OVERFLOW_PATH > PHYSBUFF 128> > ################################################################### > > LOGFILES 106 > LOGSIZE 10000 > DYNAMIC_LOGS 1 > LOGBUFF 64> > ################################################################### > > LTXHWM 70 > LTXEHWM 80> > ################################################################### > > MSGPATH D:\\\\informix\\\\ol_company.log # System message log file path > CONSOLE D:\\\\informix\\\\conol_company.log # System console message path > > ################################################################### > > TBLTBLFIRST 0 > TBLTBLNEXT 0 > TBLSPACE_STATS 1> > ################################################################### > > DBSPACETEMP temp1dbs, temp2dbs, temp3dbs, temp4dbs, temp5dbs, temp6dbs > SBSPACETEMP> > ################################################################### > > SBSPACENAME csg_sblob_dat # Default sbspace > SYSSBSPACENAME csg_sblob_dat # Default System sbspace > ONDBSPACEDOWN 2> > ################################################################### > > SERVERNUM 0 # Unique id corresponding to a server instance > DBSERVERNAME ol_company # Name of default Dynamic Server > DBSERVERALIASES # List of alternate dbserver names > FULL_DISK_INIT 0> > ################################################################### > > onsoctcp,1,200,CPU > LISTEN_TIMEOUT 900 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1 > NS_CACHE host=900,service=900,user=900,group=900> > ################################################################### > > MULTIPROCESSOR 1 > VPCLASS cpu,num=3 > VP_MEMORY_CACHE_KB 0 > SINGLE_CPU_VP 0> > ################################################################### > > #VPCLASS aio,num=1 > CLEANERS 8 > DIRECT_IO 0> > ################################################################### > > LOCKS 500000 > DEF_TABLE_LOCKMODE row> > ################################################################### > > RESIDENT 1 > SHMBASE 0x80000000L > SHMVIRTSIZE 32656 > SHMADD 8192 > EXTSHMADD 8192 > SHMTOTAL 0 > SHMVIRT_ALLOCSEG 0,3> #SHMNOACCESS 0x70000000-0x7FFFFFFF > > ################################################################### > > CKPTINTVL 300 > RTO_SERVER_RESTART 0 > BLOCKTIMEOUT 3600> > ################################################################## > > CONVERSION_GUARD 2 > RESTORE_POINT_DIR $INFORMIXDIR\\\\tmp> > ################################################################### > > TXTIMEOUT 900 > DEADLOCK_TIMEOUT 900 > HETERO_COMMIT 0> > ################################################################### > > TAPEDEV D:\\\\IFMXBKUP\\\\ifmxbkup.bak > TAPEBLK 512 > TAPESIZE 0> > ################################################################### > > LTAPEDEV D:\\\\IFMXBKUP\\\\logs.bak > LTAPEBLK 512 > LTAPESIZE 0> > ################################################################### > > BAR_ACT_LOG D:\\\\informix\\\\bar_ol_company.log #Path of log file for onbar.exe > BAR_DEBUG_LOG D:\\\\informix\\\\bar_ol_company.log #Path of the debug log for > onbar.exe > BAR_DEBUG 0 > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 20 > BAR_XFER_BUF_SIZE 15 > RESTARTABLE_RESTORE ON > BAR_PROGRESS_FREQ 0 > BAR_BSALIB_PATH > BACKUP_FILTER > RESTORE_FILTER > BAR_PERFORMANCE 0 > BAR_CKPTSEC_TIMEOUT 16> > ################################################################### > > PSM_DBS_POOL DBSPOOL > PSM_LOG_POOL LOGPOOL> > ################################################################### > > DD_HASHSIZE 31 > DD_HASHMAX 10> > ################################################################### > > DS_HASHSIZE 31 > DS_POOLSIZE 127> > ################################################################## > > PC_HASHSIZE 31 > PC_POOLSIZE 127 > PRELOAD_DLL_FILE> > ################################################################### > > STMT_CACHE 0 > STMT_CACHE_HITS 0 > STMT_CACHE_SIZE 512 > STMT_CACHE_NOLIMIT 0 > STMT_CACHE_NUMPOOL 1> > ################################################################### > > USEOSTIME 0 > STACKSIZE 64 > ALLOW_NEWLINE 1 > USELASTCOMMITTED NONE> > ################################################################### > > FILLFACTOR 90 > MAX_FILL_DATA_PAGES 0 > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > ONLIDX_MAXMEM 5120> > ################################################################### > > MAX_PDQPRIORITY 99 > DS_MAX_QUERIES > DS_TOTAL_MEMORY > DS_MAX_SCANS 1048576 > DS_NONPDQ_QUERY_MEM 128 > DATASKIP> > ################################################################### > > OPTCOMPIND 2 > DIRECTIVES 1 > EXT_DIRECTIVES 0 > OPT_GOAL -1 > IFX_FOLDVIEW 1 > STATCHANGE 10 > USTLOW_SAMPLE 0> > ################################################################### > > BATCHEDREAD_TABLE 1 > BATCHEDREAD_INDEX 1> > ################################################################### > > EXPLAIN_STAT 1 > #SQLTRACE level=low,ntraces=1000,size=2,mode=global> > ################################################################### > > #DBCREATE_PERMISSION informix > #DB_LIBRARY_PATH > IFX_EXTEND_ROLE 1 > SECURITY_LOCALCONNECTION > UNSECURE_ONSTAT > ADMIN_USER_MODE_WITH_DBSA > ADMIN_MODE_USERS > SSL_KEYSTORE_LABEL> > ################################################################### > > PLCY_POOLSIZE 127 > PLCY_HASHSIZE 31 > USRC_POOLSIZE 127 > USRC_HASHSIZE 31> > ####

Just a few quick notes: At this very instance the dbim= port is waiting on data to be read from the unload file. How fast= are the disks which the data sits on, are they the same disk which you = are inserting the data to?? In version 12.10 you can set a large cur= sor buffer. Since dbimport uses insert cursors this can really hel= p. FET=5FBUF=5FSIZE is an environment variable and is set in byte= s and you should set it to 64K or 1MB. Prior to version 12 the lar= gest it could be set to was 32K (again the units is bytes) Set th= e environment variable FET=5FBUF=5FSIZE=3D1000000 I woul= d set these onconfig as they can help DS=5FMAX=5FQUERIES 2 DS=5FTOTAL= =5FMEMORY 100000 DS=5FNONPDQ=5FQUERY=5FMEM 5000 J= ohn F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-578= -5645 IBM Informix Dynamic Server (IDS) -----ids-bounces@iiug.org wrote: ----- >To: ids@iiug= .org >From: "MARKUS BRINKB=C3=A4UMER" >Sent by: ids-bounces@ii= ug.org >Date: 06/25/2013 07:43AM >Subject: Re: IDS 12.10 - dbim= port very slow [30641] > >Hello Art, thank you for your repl= y. Here is the output from onstat >-g: IBM Informix Dynamic Server = Version 12.10.FC1WE -- On-Line -- >Up 05:27:46 -- 1398720 Kbytes s= ession effective #RSAM total used >dynamic id user user tty pid host= name threads memory memory explain >45 administ - VMAPP 3400 VMAPP.bo= 1 208896 193456 off Program : >D:\\\\informix\\\\bin\\\\dbimport.exe tid = name rstcb flags curstk status 93 >sqlexec d2aedd78 Y-BP--- 4048 con= d wait netnorm - Memory pools >count 2 name class addr totalsize f= reesize #allocfrag #freefrag 45 >V d3d2c040 204800 14656 679 7 45*O= 0 V d3af5040 4096 784 1 1 name >free used name free used overhead = 0 6624 scb 0 176 opentable 0 9136 >filetable 0 2224 ru 0 608 blobio= 0 9200 log 0 16544 temprec 0 33984 > blob 0 368 keys 0 1664 ralloc= 0 25536 gentcb 0 1808 ostcb 0 3024 >sqscb 0 66672 sql 0 80 hashfil= etab 0 560 osenv 0 3536 sqtcb 0 9504 >fragman 0 1520 cdr 0 480 udr = 0 208 sqscb info scb sqscb optofc >pdqpriority optcompind directiv= es d366f260 d3459030 0 0 2 1 Sess >SQL Current Iso Lock SQL ISAM F= .E. Id Stmt type Database Lvl Mode >ERR ERR Vers Explain 45 INSERT = csgtest CR Wait 0 0 9.240 Off >Current statement name : loadcur Cur= rent SQL statement (1509) : >insert into "csg".cs=5Flagerproto values= (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, >?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?= , ? ); Host variables : >address type flags value ---------------= -------------------------- > 0x00000000D3BAE120 CHAR 0x002 0x000000= 00D3BAE1B0 CHAR 0x002 >0x00000000D3BAE240 CHAR 0x002 0x00000000D3BA= E2D0 CHAR 0x002 >0x00000000D3BAE360 CHAR 0x002 0x00000000D3BAE3F0 C= HAR 0x002 >0x00000000D3BAE480 CHAR 0x002 0x00000000D3BAE510 CHAR 0x= 002 >0x00000000D3BAE5A0 CHAR 0x002 0x00000000D3BAE630 CHAR 0x002 >0x00000000D3BAE6C0 CHAR 0x002 0x00000000D3BAE750 CHAR 0x002 >0= x00000000D3BAE7E0 CHAR 0x002 0x00000000D3BAE870 CHAR 0x002 >0x00000= 000D3BAE900 CHAR 0x002 0x00000000D3BAE990 CHAR 0x002 >0x00000000D3B= AEA20 CHAR 0x002 0x00000000D3BAEAB0 CHAR 0x002 >0x00000000D3BAEB40 = CHAR 0x002 0x00000000D3BAEBD0 CHAR 0x002 >0x00000000D3BAEC60 CHAR 0= x002 0x00000000D3BAECF0 CHAR 0x002 >0x00000000D3BAED80 CHAR 0x002 = Last parsed SQL statement : insert >into "csg".cs=5Flagerproto valu= es (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, >?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?= , ? ); >*************************************************************= ******** >********** Forum Note: Use "Reply" to post a response in= the >discussion forum. =

See John Miller's reply, he's covered what I would say. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 25, 2013 at 10:33 AM, MARKUS BRINKBäUMER < markus.brinkbaeumer@gmail.com> wrote: > Hello Art, > > thank you for your reply. Here is the output from onstat -g: > > IBM Informix Dynamic Server Version 12.10.FC1WE -- On-Line -- Up 05:27:46 > --> 1398720 Kbytes > > session effective #RSAM total used dynamic > id user user tty pid hostname threads memory memory explain > 45 administ - VMAPP 3400 VMAPP.bo 1 208896 193456 off > > Program : > D:\\\\informix\\\\bin\\\\dbimport.exe > > tid name rstcb flags curstk status > 93 sqlexec d2aedd78 Y-BP--- 4048 cond wait netnorm - > > Memory pools count 2 > name class addr totalsize freesize #allocfrag #freefrag > 45 V d3d2c040 204800 14656 679 7 > 45*O0 V d3af5040 4096 784 1 1 > > name free used name free used > overhead 0 6624 scb 0 176 > opentable 0 9136 filetable 0 2224 > ru 0 608 blobio 0 9200 > log 0 16544 temprec 0 33984 > blob 0 368 keys 0 1664 > ralloc 0 25536 gentcb 0 1808 > ostcb 0 3024 sqscb 0 66672 > sql 0 80 hashfiletab 0 560 > osenv 0 3536 sqtcb 0 9504 > fragman 0 1520 cdr 0 480 > udr 0 208 > > sqscb info > scb sqscb optofc pdqpriority optcompind directives > d366f260 d3459030 0 0 2 1 > > Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers Explain > 45 INSERT csgtest CR Wait 0 0 9.240 Off > > Current statement name : loadcur > > Current SQL statement (1509) : > insert into "csg".cs_lagerproto values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ); > > Host variables : > > address type flags value > > ----------------------------------------- > > 0x00000000D3BAE120 CHAR 0x002 > > 0x00000000D3BAE1B0 CHAR 0x002 > > 0x00000000D3BAE240 CHAR 0x002 > > 0x00000000D3BAE2D0 CHAR 0x002 > > 0x00000000D3BAE360 CHAR 0x002 > > 0x00000000D3BAE3F0 CHAR 0x002 > > 0x00000000D3BAE480 CHAR 0x002 > > 0x00000000D3BAE510 CHAR 0x002 > > 0x00000000D3BAE5A0 CHAR 0x002 > > 0x00000000D3BAE630 CHAR 0x002 > > 0x00000000D3BAE6C0 CHAR 0x002 > > 0x00000000D3BAE750 CHAR 0x002 > > 0x00000000D3BAE7E0 CHAR 0x002 > > 0x00000000D3BAE870 CHAR 0x002 > > 0x00000000D3BAE900 CHAR 0x002 > > 0x00000000D3BAE990 CHAR 0x002 > > 0x00000000D3BAEA20 CHAR 0x002 > > 0x00000000D3BAEAB0 CHAR 0x002 > > 0x00000000D3BAEB40 CHAR 0x002 > > 0x00000000D3BAEBD0 CHAR 0x002 > > 0x00000000D3BAEC60 CHAR 0x002 > > 0x00000000D3BAECF0 CHAR 0x002 > > 0x00000000D3BAED80 CHAR 0x002 > > Last parsed SQL statement : > insert into "csg".cs_lagerproto values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ); > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c365a673bcf504dffe6a3d

I have changed the onconfig but there is no effect. It's still very slowly. I have created a dbschema from the source database and created a database manually, based on the dbschema. After that I tried to load some unload files manually. That's even very very slowly (only 125 datasets per minute). The harddisks are fast. I've created a new 8 gb chunk. This was finished in 10 seconds.

Something doesn't add up. Yoi should be getting many thousands of rows per second throughput. Art On Jun 26, 2013 7:09 AM, "MARKUS BRINKBäUMER" < markus.brinkbaeumer@gmail.com> wrote: > I have changed the onconfig but there is no effect. It's still very slowly. > > I have created a dbschema from the source database and created a database > manually, based on the dbschema. After that I tried to load some unload > files > manually. That's even very very slowly (only 125 datasets per minute). > > The harddisks are fast. I've created a new 8 gb chunk. This was finished > in 10 > seconds. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c365a63a001704e00cd4bb

The server is running on RAID5. My informix support told me, that this could be a reason for the slow performance. But why are large chunks being created so fast?

If they are RAW chunks, Informix only writes to the first few reserved pages and the last page (using fseek) to verify the size of the chunk. It only writes to the entire chunk if the chunk is a filesystem file so that it is pre-allocated as contiguous as the OS can make it and not allocated as a sparse file initially. So, creating a RAW chunk, regardless of size, is nearly instantaneous. RAID5 is not only SLOW for inserts but it is not a safe place to put your database! See my BLOG entry on the subject and my presentation at the 2012 IIUG Conference entitles "Doing Storage Right" which you can download from the members section on the IIUG web site for details. The BLOG entry is at: http://informix-myview.blogspot.com/2010/06/raid5-rant.html Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Jun 26, 2013 at 8:17 AM, MARKUS BRINKBäUMER < markus.brinkbaeumer@gmail.com> wrote: > The server is running on RAID5. My informix support told me, that this > could > be a reason for the slow performance. But why are large chunks being > created > so fast? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d174269c5f904e00dc3d9

Hi there, Can help to check the size of the rootdbs ? Probably the size is too small as compare to actual db space where the database resides. hope this help :-) Best Regards, NG BEE YEAN Technical Support Specialist CTC Global Sdn Bhd (Formerly known as CSC ESI Sdn Bhd) Level 6, Menara AMCORP, No 18 Persiaran Barat, 46050 Petaling Jaya, Selangor. Malaysia |CSC Asia Group | t: N/A | m: +019.396.5837 | f: +603.7955.0403 | beeyean.ng@ctc-g.com.my Toll Free : 1-800-88-7838 Please consider the environment before printing this e-mail. CSC • This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose • CSC Malaysia Sdn. Bhd • Registered Office: No.10A Jalan Bersatu 13/4, Section 13, Petaling Jaya 46200, Selangor Darul Ehsan, Malaysia• Registered in Malaysia No: 25110P From: "John Miller iii" <miller3@us.ibm.com> To: ids@iiug.org Date: 25/06/2013 10:59 PM Subject: Re: Re: IDS 12.10 - dbimport very slow [30646] Sent by: ids-bounces@iiug.org Just a few quick notes: At this very instance the dbim= port is waiting on data to be read from the unload file. How fast= are the disks which the data sits on, are they the same disk which you = are inserting the data to?? In version 12.10 you can set a large cur= sor buffer. Since dbimport uses insert cursors this can really hel= p. FET=5FBUF=5FSIZE is an environment variable and is set in byte= s and you should set it to 64K or 1MB. Prior to version 12 the lar= gest it could be set to was 32K (again the units is bytes) Set th= e environment variable FET=5FBUF=5FSIZE=3D1000000 I woul= d set these onconfig as they can help DS=5FMAX=5FQUERIES 2 DS=5FTOTAL= =5FMEMORY 100000 DS=5FNONPDQ=5FQUERY=5FMEM 5000 J= ohn F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-578= -5645 IBM Informix Dynamic Server (IDS) -----ids-bounces@iiug.org wrote: ----- >To: ids@iiug= .org >From: "MARKUS BRINKB=C3=A4UMER" >Sent by: ids-bounces@ii= ug.org >Date: 06/25/2013 07:43AM >Subject: Re: IDS 12.10 - dbim= port very slow [30641] > >Hello Art, thank you for your repl= y. Here is the output from onstat >-g: IBM Informix Dynamic Server = Version 12.10.FC1WE -- On-Line -- >Up 05:27:46 -- 1398720 Kbytes s= ession effective #RSAM total used >dynamic id user user tty pid host= name threads memory memory explain >45 administ - VMAPP 3400 VMAPP.bo= 1 208896 193456 off Program : >D:\\\\informix\\\\bin\\\\dbimport.exe tid = name rstcb flags curstk status 93 >sqlexec d2aedd78 Y-BP--- 4048 con= d wait netnorm - Memory pools >count 2 name class addr totalsize f= reesize #allocfrag #freefrag 45 >V d3d2c040 204800 14656 679 7 45*O= 0 V d3af5040 4096 784 1 1 name >free used name free used overhead = 0 6624 scb 0 176 opentable 0 9136 >filetable 0 2224 ru 0 608 blobio= 0 9200 log 0 16544 temprec 0 33984 > blob 0 368 keys 0 1664 ralloc= 0 25536 gentcb 0 1808 ostcb 0 3024 >sqscb 0 66672 sql 0 80 hashfil= etab 0 560 osenv 0 3536 sqtcb 0 9504 >fragman 0 1520 cdr 0 480 udr = 0 208 sqscb info scb sqscb optofc >pdqpriority optcompind directiv= es d366f260 d3459030 0 0 2 1 Sess >SQL Current Iso Lock SQL ISAM F= .E. Id Stmt type Database Lvl Mode >ERR ERR Vers Explain 45 INSERT = csgtest CR Wait 0 0 9.240 Off >Current statement name : loadcur Cur= rent SQL statement (1509) : >insert into "csg".cs=5Flagerproto values= (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, >?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?= , ? ); Host variables : >address type flags value ---------------= -------------------------- > 0x00000000D3BAE120 CHAR 0x002 0x000000= 00D3BAE1B0 CHAR 0x002 >0x00000000D3BAE240 CHAR 0x002 0x00000000D3BA= E2D0 CHAR 0x002 >0x00000000D3BAE360 CHAR 0x002 0x00000000D3BAE3F0 C= HAR 0x002 >0x00000000D3BAE480 CHAR 0x002 0x00000000D3BAE510 CHAR 0x= 002 >0x00000000D3BAE5A0 CHAR 0x002 0x00000000D3BAE630 CHAR 0x002 >0x00000000D3BAE6C0 CHAR 0x002 0x00000000D3BAE750 CHAR 0x002 >0= x00000000D3BAE7E0 CHAR 0x002 0x00000000D3BAE870 CHAR 0x002 >0x00000= 000D3BAE900 CHAR 0x002 0x00000000D3BAE990 CHAR 0x002 >0x00000000D3B= AEA20 CHAR 0x002 0x00000000D3BAEAB0 CHAR 0x002 >0x00000000D3BAEB40 = CHAR 0x002 0x00000000D3BAEBD0 CHAR 0x002 >0x00000000D3BAEC60 CHAR 0= x002 0x00000000D3BAECF0 CHAR 0x002 >0x00000000D3BAED80 CHAR 0x002 = Last parsed SQL statement : insert >into "csg".cs=5Flagerproto valu= es (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, >?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?= , ? ); >*************************************************************= ******** >********** Forum Note: Use "Reply" to post a response in= the >discussion forum. = ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.