Borland Delphi and Informix

Dear sirs, Which way can I use Borland Delphi 5.0 (+informix sdk2.20) (ids 9.20 on database server) for select BLOB and/or CLOB fields. f.e.: CREATE TABLE foo ( id INTEGER, memo BLOB ) // then SELECT * FROM foo // result is: Capability not supported BDE Error 12289 Please, why?

Hello everybody, we have huge performance problems on our 2-node Sun-Cluster with Informix: Environment: Sun E4500, 6 CPU's, 1GB Memory (primary host for database) Sun E3500, 4 CPU's, 1GB Memory (secondary host for takeover) 4 A5000-Diskarrays with 14 9,2GB disks each connected via FCAL Solaris 2.6, Sun Cluster 2.1 disk mirroring using Veritas Volume Manager (Raid 0/1) Gigabit Network Adapters Informix Dynamic Server 7.30UC9 Workload: for example: 400.000 inserts and updates per day in one non-fragmented table, row size 265 bytes via continuous batch processes on server. 400.000 inserts of blob data (average blob size 10K) (total sum per month are >2 Mio data rows and blobs) 100 OLTP users selecting small portions of data and blobs, updating data table several batch programs for statistic analysis and invoice generation, mainly selecting from data table All dbspaces are located on raw devices. Root-, physical log and logical log dbspaces are on separated disk. Table dbspace is separated from index dbspaces on a stripe with 3 disks each ( stripe interleave is 16K which seems to be the same as Informix' BIGREADS). The blobs are stored in their own blobspaces with blob page size = 10K. Extract from onconfig: ============================================================= NETTYPE tlitcp,2,100,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor # on E4500 5 CPUs # on E3500 3 CPUs NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 1 # Affinity start processor AFF_NPROCS 5 # Affinity number of processors # Shared Memory Parameters LOCKS 1000000 # Maximum number of locks BUFFERS 100000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 1024 # Physical log buffer size (Kbytes) LOGBUFF 1024 # Logical log buffer size (Kbytes)LOGSMAX 30 # Maximum number of logical log files CLEANERS 10 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 32768 # initial virtual shared memory segmentsize SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 900 # Check point interval (in sec) LRUS 20 # Number of LRU queues ## lowered for better checkpoint time LRU_MAX_DIRTY 5 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 3 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 ======================= Problems: it seems not possible to get more than 300K/s throughput from individual disks (more than 1.2MB/s from a 3 disk stripe) if we do a sequential scan from the data table with a result set of very few rows (<10). But... monitoring the i/o with iostat shows that all disks are only 10-20% busy and doing just 70 reads per second. The whole system shows 70 to 80% idle time, each CPU is only 8% busy !! But.. a level 0 or level 1 backup of data dbspaces and blobspaces with onbar and Legato Networker results in a throughput between 10MB/s and 36MB/s for each stripe !!!! In the last months we had several dates with Sun and Informix consultants. Sun says: there is no problem with the hardware, Informix read requests are too small, because best performance with A5000 arrays is achieved with 64K requests. Informix says: there are no problems neither with Informix Server nor with our configuration. You have to do more reads in parallel (on the client side). Is there anybody having similiar problems AND a solution ????? For us, one solution to this problem may be fragmention of the data table by round robin policy. This involves more questions: - would that be the right choice for our OLTP environment ? - our client software (written with C++-Builder from Borland) requires an explicit rowid, no chance to refer to the existing primary key. What about performance degradation using a virtual rowid (create table .... WITH ROWID ) ? - shall we fragment over several individual disks or over stripes ? - detach indexes or not (I would prefer detaching without index fragmentation)? - what about PDQPRIORITY on the client side and on the server side ? I have checked all archives from comp.databases.informix back to Jan 1998 and I didn't find any hints which may help to solve our problems. Many thanks for any help Thomas Vogt Firmengruppe Dr. Gueldener

I bet your problem is mostly the A5000. Buy a decent RAID system. Here are some Sybase benchmark results from one of our customers. Disk Array Trials - elapsed time in seconds Raid"X" A5000 Disk Init 47 1GB Devices 600 720 Create 6 GB Database 360 4140 BCP in 1M rows 371 2415 BCP out 1M rows 63 117 Recreate clustered index on 1M rows 39 343 Recreate non- clustered index on 1M rows 39 120 BCP in 5M rows w/ 75 processes 709 814 BCP in 4M rows w/ 8 processes 728 1519 Run 24K select queries 384 453 Delete 500k rows 193 507 NB: Lowest is best! email me if you want details ---------- In article <38A97E52.5CA7D052@drgueldener.de>, Thomas Vogt <t.vogt@drgueldener.de> wrote: Hello everybody, we have huge performance problems on our 2-node Sun-Cluster with Informix: Environment: Sun E4500, 6 CPU's, 1GB Memory (primary host for database) Sun E3500, 4 CPU's, 1GB Memory (secondary host for takeover) 4 A5000-Diskarrays with 14 9,2GB disks each connected via FCAL

1GB for 6cpu is pretty small RAM. Not much info to go on, I/O problems? CPU? IPC resources??? Send a sar of 20 min intervals, describe the application, show us the informix config files or something. -- --------------------------------------------------------- Steven Hauser email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 ---------------------------------------------------------

Do you have PDQ enabled? If so you could try disabling it. . . . (This is only a very vague guess - I'm an analyst programmer, not a DBA but we run an E4000 Solaris/Informix machine and have also had problems of this nature recently - turns out you need a more powerful machine to run PDQ queries etc properly). Dave. Thomas Vogt <t.vogt@drgueldener.de> wrote in message news:38A97E52.5CA7D052@drgueldener.de... > Hello everybody, > > we have huge performance problems on our 2-node Sun-Cluster with > Informix: > > Extract from onconfig: > ============================================================= > NETTYPE tlitcp,2,100,NET # Configure poll thread(s) for nettype > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> > Thomas Vogt > Firmengruppe Dr. Gueldener