Oninit® Archive
Results for “BATCHEDREAD_INDEX”
Not enough room in ROOT DBspace
Reduce the size of your PHYSFILE. You probably moved it to another dbspace after setting up the first server. Start it smaller, then create the plog dbspace and move it there and resize. j. On 1/4/17 4:09 PM, STEVEN AB wrote: > Hi, > > I'm trying to setup a new server with the …
Large memory segment vs smaller ones
Hi Dennis. Yes: " vmo -p -o v_pinshm=1". You must have RESIDENT 2 or more for large pages as they must be pinned. The "rsam" pool is in the dynamic segment. You need RESIDENT 2 or more. It doesn't sound like the problem is with the buffer pool but the "rsam" pool. Your BTR is fin…
Changing DBSPACETEMP run-time
Hi, IBM Informix Dynamic Server Version 11.70.FC5XE Please, is there a way to change DBSPACETEMP for a session inside 4GL? ------------------------------ Sincerely, Dennis ------------------------------…
SQL query cost is very high
Query plan would be useful On 11/2/2023 4:06 AM, Indika Jinadasa via IBM TechXchange Community wrote: 0100018b8f4925bb-37054389-e021-4f73-91b4-f0b956ba7f90-000000@email.amazonses.com"> Dear Friends, We are using IDS 14X and the below SQL query is taking hig…
Replay of my Informix TechTalk: Informix Sysmaster Database Queries
The Replay of my Informix TechTalk: Informix Sysmaster Database Queries for monitoring and performance tuning is available on: YouTube: https://youtu.be/C9zk8FmuUFQ #informix #ibmchampion ------------------------------ Lester Knutsen lester@advancedatatools.com Advanced DataTools…
Buffer wait occurs when performing INSERT in multiple sessions
You have to only peripherally related problems. The issue about not enough KAIO resources is an operating system tuning parameter. You can read the maximum value and set it here: $ cat /proc/sys/fs/aio-max-nr 65536 echo 131072 >/proc/sys/fs/aio-max-nr # set new maximum And see …
How to detect if table has old-fashioned attached indexes (index pages in non-fragmented table partition)
Hello, does any one knows, if there is a way how to detect tables with attached indexes (in old meaning – index pages are together with data pages in one partition). Curently system catalogs have such indexes, but we can configure such indexes for user tables via DEFAULT_ATTACH e…
Index in Temp table
There are no issues using indexes on temp tables in particular - they can improve performance just like any index on any table if they are used correctly. In this case, I would suggest testing the performance of the query below: DELETE FROM optim_cs_csdi cb WHERE cust_numb IN (s…
Informix 14 - ALTER JOIN -
INFORMIX OUTER JOIN - We upgrade server to Version 14.10 FC5 , we are facing a performance issues in queries who uses OUTER JOIN . Comparing in the same machine / same configuration, between verions 12.10 FC14 and 14.10 FC5, queries who uses OUTER join are 4 times slower, other…
Index page size recommendations
16 k for indices is a good starting point. After that is depends on the data. If I am using TS then I would tend to have dedicate spaces as well. And yes you need a buffer pool for each page size. Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trade…
Adding primary to a table having duplicate rows
another possibility is to have create unique index stmts default to a non-unique index if the uniqueness fails. That way you get the benefit of having a defined index in place.…
ADO - activex data object
What do I require, to connect "ado activex data object" to Informix ? AIX 7.2 IBM Informix Dynamic Server Version 12.10.FC9W1 Regards Dirk…
Problem with backup
Original post: Hello, every time we release a copy with onbar , the Database server gets blocked one to three minutes. Is this normal ? Our version is 12.10.FC3WE and the command is onbar -b -w Thank Response: The answer is possibly. At the start of every backup, the server…
Performance Tuning
I am busy going through Mike Walker's slide show for Performance Tuning Basics. From this I created a script to display the long running queries on my system. Are there any other links related to this that you can point me to please ? Dirk…
Bufferpool configuration
Hi informix community, on a SPARC M10-1 Server we have a informix 12.10FC10 instance. Currently this instace use a buffer pool with around 7GB. Now i have the chance to configure the buffers up to 40GB. I would like to use the following configuration of the buffer pool: BUFFER…
SQL using the index
If I find one index has high buffer read, How to know which SQL use this index?…
BUFFERPOOL Configuration
Oh, one more reason to use multiple page size buffer pools: - To be able to isolate the IOs against some objects from those of other objects so that, for example, index IO activity does not flush data pages from the cache.…
listener-thread@soc_be.c:3073: err = -25582:
Adding Version details Informix Version : IBM Informix Dynamic Server Version 12.10.FC6 O/S Version : Red Hat Enterprise Linux Server release 6.9 Thanks & Regards, Karthik Rajan…
index compression on Version 12.10
If i want to compress the detached index, Do the create index command lock the whole user table ? it will affect the business. thanks.…
Informix and speculative execution
Hi all, I have seen speculative execution patches for Firefox and SQL Server. Are there speculative execution patches for Informix in progress? There is no IBM Flash for Informix Dynamic Server and speculaative execution! Regards, David.…
Informix 12.1 Blocking Checkpoints
> On 7 Nov 2017, at 10:48, NATYURAL HORACIO <horacio.natyural@gmail.com> wrote: > > Hi, > > > Based on the current workload, the physical log might be too small > to accommodate the time it takes to flush the buffer pool during > checkpoint processing. The server might block tran…
Next Webcast - Informix Query Performance Tuning B
Folks, Next Webcast - Informix Best Practice - Informix Query Performance Tuning Basics by IBM Champion Mike Walker Thursday - Thursday, October 26, 2017 at 2:00pm EST This session will cover some of the basics of Informix Tuning, with a focus on monitoring and tuning of querie…
Rovoke insert permission but still can insert
I am doing it at the command line in the server directly in the directory C:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50\\\\bin…
identify what is allocating memory
original post: Hi , How identify what/who is consuming memory in a specific virtual memory segment ? With onstat -g ses , there is no session with high memory allocated. The amount of users sessions is keeping at regular average... No buffers pool was allocated automatically (I…
Performance loss for a query after 11.50 >12.10 up
Hi all, we just completed an offplace migration from 11.50 to 12.10 FC8W1. This instance is mainly intended for DWH, size reaching 0.7 Tb Migration ran well, but we now have a severe performance loss on one very complex query, which ran formerly in about 120 seconds on 11.50, a…
UPDATE STATISTICS low in parallel?
Hi Ben, > On 10 Apr 2017, at 17:47, BENJAMIN THOMPSON <benjamin.thompson@skybettingandgaming.com> wrote: > > Hi Andrew, > > I played around with DBUPSPACE (my usual value is "0:50:0") by turning on > indices for sorting but this only normally affects MEDIUM and HIGH modes and > …
Buffer pools in 11.50.FC5
IDS 11.50.FC5 Solaris 10 Sparc I need some guidance on BUFFERPOOL. I am receiving the WARNING: Buffer pool size may cause Dynamic Server to get into a locked state. Recommended minimum buffer pool size is 4 times maximum concurrent user threads. I am not sure how that translat…
Number of isread dropped
What was the settings for BATCHEDREAD_TABLE / BATCHEDREAD_INDEX in comparison with current version? This is a bit of a wild guess, but it' possible it was turned off (0) and now it's turned on. Considering this implements optimization at ISAM calls level it may explain that. Yo…
Odd Bufferpool Behaviou
Original post: AIX 6.1 IDS 11.70.FC8X8 I have a new machine with a lot of extra memory. Given that, I added a bunch of new buffers (4k, 8k, & 16k) and went to town testing away. Long story short, with all those new buffers, I get virtual segments added at startup. In onstat -g …
Forein key constraints and indexes
Hi, I have many constraints of foreign key on a table, I notice that this constraints created an implicit index, is that index slow performance when insert or update, If yes can I deactivate this index and what is consequences if I deactivate the index.…
select statement making long transaction
There is no trigger on that table. That's why query return result immediately on proper index hit.…
12.10.FC6 Mac port released
IBM Informix 12.10.FC6 release for Mac OS is officially available! John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS)…
new features about dynamic parameter added in 12.1
There may be a better approach, but if not you can do something like: 11.7: onmode -wf dummy=1 Configuration Parameter to be changed is not valid or not supported with this option. The following documented configuration parameters and environment variables can be modified on t…
Performance issue after migrate to IDS from Online
Questions: 1. After the database was recreated and loaded did you run the recommended suite of Update Statistics commands as described in the Performance Guide manual (or as implemented by my dostats utility) or are you depending on Auto Update Statistics (AUS)? 2. So, you m…
Could not do a physical ..in a query group by
Can you share the ISAM error? We return two error codes. The SQLCODE which is more generic and and a more detailed one called the ISAM error. (in fact the same ISAM error may cause different SQL codes). Usually that sqlcode you mentioned is associated with the lock error which is…
index compression
IDS12.10 FC4. I am going to try to compress some big indexes with long keys , e.g., VARCHAR(250), with considerable length of duplicate sub-strings among millions of index keys. Want to get some existing experiences if possible. --- after an index got compressed, its performan…
Query performance
I'm reviewing the SQL's that our applications are running against the database (11.70 Running on AIX) and was wondering which of the following two SQL statements is more efficient or less demanding on the Informix Engine. SQL TYPE 1: SELECT olintref AS invoice_number, ollineno A…
RSS not working
-bash-4.2$ onstat - IBM Informix Dynamic Server Version 11.50.FC9 -- On-Line --…
Physical Log Size
IDS 11.70.FC8 AIX 1 6 00CC96664C00 I received the following message in the online.log file ..... is it something to be concerned about ? Based on the current workload, the physical log might be too small to accommodate the time it takes to flush the buffer pool during checkpoi…
new bufferpool
original post This concerns ids12.10TC3IE . These are the old lines in my onconfig... #BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50 #BUFFERPOOL size=4K,buffers=100000,lrus=8,lru_min_dirty=20.00,lru_max_dirty=30.00 #BUFFERPOOL size=16K,buffers=…
Index Question
Hi All, I would like to seek your comments on the below questions. I've already answered some, but not all. thanks is advance. What's the purpose of creating indices on tables? ANSWER: Creating indices on tables is done for the purpose of making the sorting faster. On the oth…
Truncated table still occupied many pages
Hi Jacob. In that case, you'll need to run the following for each index: ALTER FRAGMENT ON INDEX index-name INIT IN dbspace-name; This will lock the table, though. Regards, Doug…
problem creating idnex on spatial table with scri
I dropped the index - arccatalogue created one . then recreated. the index is created I checked with dbschema. The problem is it is not used. I have used this method for years.…
Informix upgrade
Can we upgrade informix from Dynamic Server Version 11.70 to 12.1 workgroup edition directly?…
Buffer Size and Use
Folks, We recently had a conversation with a well-respected name in the Informix world, recently, concerning our bufferpool configuration. When exported, our database takes up only about 18 GB. We had a buffer pool of 12 GB, figuring that the more memory resident, the better. Al…
when use IfxDataReader.GetIfxDateTime to NULL valu
Hello, The version of Informix products in my environment is: Database server: AIX IBM Informix Dynamic Server Version 10.00.FC5 IBM Informix CSDK Version 2.90 IBM-ESQL Version 2.90.FC4 Client: PC Windows IBM Informix-ESQL 3.70.TC5DE.…
onstat -g ckp Question
When we are transferring an index from the primary to the secondary we = have a problem. All the work and maintain a clean buffer pool on the primary= is done all the index is being built. However on the secondary there is no= way to keep the buffer pool clean. If we do not keep …
Continuous Informix to Postgres data replication
All of those suggestions imply latency and the possibility of data being out of sync. You stated that performance and data integrity are a priority, so why not just query the Informix data directly from Postgres using ODBC? That would be the fastest and most reliable method!…
IDS 12.10 - dbimport very slow
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 Inf…
Req help - identifying applns using given database
Please find the options tried for fetching the details of the queries and applications below. Thanks a lot for your time. Option 1 : Query with statement using syssqlcurses. Issue : The query takes a really long time to execute and yet doesnt give the result set and I had to ca…
SCHAPI post_alarm_messages
With a support call, we were able to figure out what was going on with = deadlock errors, with post_alarm_messages when logical logs fill and are = backed up. This is a know issue. Here=92s the response from Informix = support: --- APAR#: IC78848=20 CONCURRENT DELETE WITH BATCHE…
SubQuery Optimization Difference in 10.x and 11.7
So what to do to achieve the same performance? Lots of existing query using subquery. It took time to convert it to join friendly query. Thanks.…
Extract indexes
Hello is there a way to extract only the indexes of a table ? non the whole schema. thanks in advance…
DEFRAGMENT API
Couple questions about the DEFRAGMENT API. How do you do indexes? Has anyone run into any issues with it? Can you make it run the indexes with the table?…
Performance Tuning Discussion
Ok it seems you have a big hardware, what´s your Informix version? What´s your box architecture? For performance tips, there is a good article in Andrew Ford´s blog, that should clarify your thoughts.... http://www.informix-dba.com/p/informix-innovator-c-tuning-basics.html This…
Re: query performance
Laurie: So to apply FORCE in dostats add the --force-run option -- only necessary if you are using 11.70 and AUTO_STAT_MODE is enabled (1) in your ONCONFIG file. By fragmentation, I meant the number of extents in each table on one server versus the other. Also, since you are u…
Re: query performance
Thanks all - I had missed something in the sqexplain - I found the table at fault... the indexes were the same, but knowing where the error was, I was able to build one more index and the query runs super fast now. I did learn a bunch from your questions :) THANKS!!! Laurie…
Re: query performance
Sounds interesting... we'd need: - confirmation that the Informix version is the same - differences in $ONCONFIG, particularly OPT_GOAL - How is data different? Different "age"? - Query plans No SAN issues would explain 2s against 4H (none I've heard about until now at least) On…
Re: query performance
Fragmentation is the same... Indexes are all detached. We are using cooked files on a SAN so who know what the structure is... although I did re-create one of the main indexes in the same space as the 'good' database, but that didn't change anything. The SET EXPLAIN query plan …
Re: query performance
Table and/or index fragmentation? Does one have attached indexes and the other one detached? (Attached indexes will show up in the output from dbschema -ss with the "IN TABLE" clause). Are the tables/indexes located on different disk structures that may either be configured di…
query performance
I have two databases that appear to be identical on the same server. The data is only slightly different. I run the same query on both databases and on one it finishes in 2 seconds... the other takes over 4 hours. I have rebuilt indexes, and updated statistics but nothing s…
DB_locale or local naming convention
Hi, I already mention the informix version. IBM Informix Dynamic Server Version 11.70.TC1IE…
Is compression feature available on 11.50 at no co
I'm eager to try this feature in the very near future to see how much it will improve IO, backup performance, query performance, etc. but would like to know if it is free. Thanks in advance for any info. Kern --…
Creating Indexes
I have what I hope is a simple question. When creating indexes, If I create an index on a column that has null values, are those values included in the index and is it a good idea to create indexes on columns that contain null values? Listed below are the versions of Informix we…
Indexes on Null values
For oracle, I have read that nulls are not indexed. How does Informix handle null values on a column that has an index? Thanks…
Index reduction
Hi Just wanted to clarify I am correct in the assumption that it is not possible to create an index that doesn't contain all rows. I am currently compressing data using the informix compression, repack, shrink tools to reduce the size of our SAP database. However, we still end u…
Error -556 on @ reference
In IDS_A have a stored procedure do_some that has a insert into a local table. In IDS_B have a stored procedure do_some that does: call base@IDS_A:do_some(); Calling this sp works ok. In IDS_C i try to run: call base@IDS_B:do_some(); Fails with error -556 Cannot create, drop,…
Bufferpool max size in Windows Server 2008 64bit
Hi, I restarted engine once more and now it looks right. So, probable user error! Are there other memory parameters that would benefit from very large values? Thanks, Lauri d:\\\\Program Files\\\\IBM\\\\Informix\\\\11.70>onstat -X IBM Informix Dynamic Server Version 11.70.FC…
Indexes contain multiple extents after reorg
Fernando, I should have mentioned, the index in this case is unique. I understand how a highly duplicate index could cause the engine to over-allocate space, but at least that would cause the index to end up with a single extent, compared to what I'm seeing. Still trying to wor…
Re: Fwd: Testing [28650]
Curious, there seems to be a problem with posting replies and r3loading this forums index.…
buffer pool and dbimport (11.70FC5)
Hi, I'm facing a problem and i don't understand how is it possible ... I'm loading (dbimport) a big table (> 3 Million records) and my buffer pool becomes quickly full (700000 buffers with LRU min = 0.50 and max 1.00). Checkpoint is not trigger until CKPINTVL (180) and you can im…
shared memory
This concerns IDS 11.70.TC5IE on Windows Server 2008 . This article http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm .perf.doc%2Fids_prf_750.htm defines buffer_value = (BUFFERS * pagesize) + (BUFFERS * 254) + 250000and goes on to say: âIf you hav…
Disabling LRU flush
Hi, IFX 11.70 FC 5 Innovator-C , Linux 64bits OpenSuse. I'm working with database load. Basically I'm using HPL, unfortunately some tables have huge rowsize0 (1 row size = 2 pages) and lvarchar datatype which force use deluxe mode. Consequently use buffer pool , and then LRU fl…
BTR on each buffer pool?
This concerns IBM Informix Dynamic Server Version 11.70.TC5IE . I have a 16k page size space where I put a temp space and all primary and foreign key indexes. Since I can select nbuffs, bufwaits, bufwrites, pagreads from sysmaster:sysbufpool I can get the BTR on each pool. Should…
Correct method of sizing resident pool...
Hi all, I have a script that reads an onconfig file and outputs an estimated memory footprint of the instance. However, I am finding it hard to accurately predict the resident section size. According to http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com…
Query Performance on HDR instance
Recently we have faced a scenario, a query was running quite fast on primary instance but performing poorly on HDR instance. The execution plan on both instances were different as well. After running "update stats" on tables involved in query, the execution plan on HDR become sam…
242: Could not open database table
Nope, I haven't tried lock mode to wait. The index create job does create 2-3 indexes and then start failing. regards, Nitin…
error in alter fragment
Thanks. It works. The table has two detached indexes in the same dbspace create unique index idx1.. in dbspaceA create index idx2 .. in dbspaceA I dropped the indexes, run the alter fragment and recreated. This time, I used "create index ...in table" to avoid this issues. Roger…
doubt about parameters (PDQ)
Art, if you set that parameter to 100 in the onconfig file, but only do inserts, updates and deletes, would there be a performance penalty even when no queries are being executed?.. I would think that leaving it at 100 would be more advantageous for any queries, especially comple…
What are best onconfig parameters for a DSS app
Frank.... first question: Is your database installation being made for a production system???? Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional …
IDS 7.31 UD5 data(base) recovery
Informix Dynamic Server Version 7.31.UD5 I understood that IDS 7.31 entered End Of Support in September 2009 ( 2 and a half years ago) I hope this is not a business critical system…
update - locking
The where-clause are the index fields. So normally no sequential scan.…
Informix Erorr - Key Value Locked
so the page locks may be on index pages? since when i dropped all indexes and primary keys, the error wa gone.…
enterprise replication time to replicate data
IBM Informix Dynamic Server Version 11.50.FC6 Question for enterpise experts Does anyone know a command that will give me time that it takes to replicate data to a node? for example i update on server a and it replicates to server b . How long did it take? cdr view profile gives …
ZFS and Informix
I'm well aware that the buffer pool has nothing to do with the underlying file system. We're just trying to figure out what scales better. We know that ZFS's ARC scales very well (the algorithms come from IBM !!). I trust though the people that have been out in the trenches worki…
Best way to move table contents to another
do i need to reindex if I do this? or will the indexes be applied. thanks…
Informix Status Explanations
hi, can anyone shed light on this for me. what does this mean? status yield bufwait- does it mean i'm lacking buffer pools? what does this indicate? cond wait netnorm - session is not doing anything. running- does it mean the sql is currently running. yield lockwaits - does th…
Informix Locks On Select count(*)
hi, so during the point when my select is happening my index is possibly locked. (date index). therefore, preventing inserts? you mentioned before about index key locking thanks…
Drop Index
I am trying to drop an index on a table prior to compression. However, the process is taking considerably longer than expected, and too long for the outage I have. Does anyone know what parameters I could change to speed up the index drop - it will be the only process running. I …
Increase buffer pool size
Dear All, i found the following line in the message log of an IDS 10.00 on a Linux Box having 3GB memory: WARNING: Buffer pool size is too small for read ahead operation I would like to increase this parameter, as obviously it has the default size of 5000 buffers with a 2k page…
Informix question
Original Post: Hi, I have two confused question. 1 When a update SQL will update totally 3 pages in bufferpool? How many mutex IDS will allocate for this thread in the buffer-head entry in the buffer table? 2. What the temporary sbspace will be used for? thanks for your time.…
Re: Upgrade to 11.70 from 11.50 results in low Read Ahead Utilization
Hi, In 11.70.xC3, the new AUTO_READAHEAD ONCONFIG parameter was introduced. This parameter reflects a new read-ahead algorithm in the database server. Basically, in mode 1 setting, auto read-ahead will turn off read-aheads for a scan if it detects that the pages are already ca…
Over 1,000 days of 24x7 database uptime!
On 29.07.2011 20:38 Lester Knutsen wrote: > From one of our customers, their Informix server hit 1,000 days today > .... congratulations;) > > IBM Informix Dynamic Server Version 10.00.FC8 -- On-Line -- Up > 1000 days 00:31:45 -- They beat mine just bit ... IBM Informix D…
Upgrade to 11.70 from 11.50 results in low Read Ahead Utilization
I recently upgraded from 11.50.FC3 to 11.70.FC3. As expected, the upgrade was painless and there are no apparent issues as a result of the upgrade. One thing that I do notice is my Read Ahead Utilization (RA-pgsused / (ixda-RA + idx-RA + da-RA)) is in the toilet after the upgra…
Fragmented index question
IDS 11.70.fc2 Aix 6.1 I have a question for the experts...When fragmenting a non unique index, you can fragment the index by a column that isn't part of the index .. So, when it's an unique index, why does the column being fragmented by .. have to be part of the index? Peter P…
IFX_FOLDVIEW
anyone experience any negative effects turning on this config parameter? thanks tom You can significantly improve the performance of a query that involves a view by enabling view folding with the IFX_FOLDVIEW configuration parameter. When the IFX_FOLDVIEW configuration paramete…
SQL Tuning Assistance Needed
Try to create the index on table trailer_audit (trailer_number, trailer_prefix, audit_code, audit_dt) Gary…
Setting up HDR- First Time -11.70 Innovator-C
Hi, I am setting up HDR for the first time. onstat - on primary shows IBM Informix Dynamic Server Version 11.70.UC2IE -- On-Line (Prim) -- Up 0:11:5s onstat - on secondry shows IBM Informix Dynamic Server Version 11.70.UC2IE -- Fast Recovery (Sec) -- Up 00s onstat -m on sec…
ISAM error 197: Partition recently appended to
Informix Dynamic Server Version 9.40.UC5 SUSE Linux Enterprise Server 10 (i586) I have done a level 0 backup directed to /dev/null and tried again. still it is throwing the same error.…
Yahoo! Answers - Web host offering IBM Informix?
Anyone knows ? http://malaysia.answers.yahoo.com/question/index?qid=20110316225108AA3RLex…
duplicate value for a record with unique key
appl_execution_id column is having the unique index…
Bitmap index
Hi, How can i create bitmap index on a table in IDS. I want the statement for bitmap index creation. We are using 11.50.FC6. Thanks…
index question
Current: O/S: HP/UX IDS: 9.4FC3 New (soon): O/S RHEL 5 IDS 11.1FC3 (can't go any newer) We are trying to get some 'expert' answers on creation of indexes and how they are actually used in our queries. The main indexes were setup by the app developers (not us) and we have added a…
Tool to estimate the size of an index
Sorry, I forgot to provide the version of the installation: IBM Informix Dynamic Server Version 11.50.FC4 Operative System: AIX Version 6.1…
AUTO INDEX
hello i am seeing more queries in version 11 that use the auto index feature than i ever recall seeing in version 10. it seems like the optimizer behaves differently but have not seen much in the literature about it. what is strange is that it is auto indexing on tables that are…
index filter
What is the difference between PostIndex Filter and Lower Index Filter in Explain plan.…
Can't access sysindexes remotely
Hi, We are using IBM Informix Dynamic Server Version 11.50.FC6 using Platform: SunOS 5.10 Generic_125100-09 sun4v sparc SUNW,Sun-Fire-T200 Thanks…
fragmented indexes?
v11.50.fc5 Aix 6.1 A general question... We have a table that will be fragmented by a fiscal week id, ie. 201101 ... We are also going to fragment the index. If the index is also fragmented by fiscal week, is it best to have that as part of the index column list. Obviously it do…
SQL Tunning
Hello Madison, Thanks for answering. Here is the data you asked me. QUERY: ------ select ag.nu_inscripcion from agrupado ag, abonado ab where ag.nu_inscripcion = ab.nu_inscripcion Estimated Cost: 2288701 Estimated # of Rows Returned: 2312068 1) informix.ag: INDEX PATH (1) I…
RE: Long Checkpoints with HDR and 11.5.
Art Kagel wrote: No, Jacques, IDS secondaries do not share buffer pool updates with the primary. Only the logical log buffers are transferred, so only the server's transaction rate would affect the data transfer rate between the servers and that would be about the same regardles…
Indexes Usages
Good Morning, Im having some issues with available free space on my instance! However I have a lot of indexes that im afraid that are unused on a daily bases even monthly! Can anyone help me on building a sql statement that searches the SYSMATER database in order to retrieve…
Network Buffer Pools
IDS 11.50.FC5 AIX 5.3 For HDR replication between two AIX 5.3 servers, is there a suggested value for the following environment parameters: 1. IFX_NETBUF_PVTPOOL_SIZE 2. IFX_NETBUF_SIZE 3. Sqlhosts option b (client buffer size) The primary has 64GB of memory while the second…
Indexes usage
Hi People !! Please, a need some help how to find in my database the indexes with high percent of usage. Anybody knows if exist a function or if I have to develop a query ? Thanks, Best Regards, Eduardo…
Index creation progress
Index space caluculation used for the above index: (20+254+4+9) * 128867926 * 1.25 = 287 * 128867926 * 1.25 = 43 GB However the actual index space after rebuild of index was 8.7 GB. Why there is this difference? Only reason I can think of is because of Varchar. Is my analysis …
Informix Dynamic Server Software Downloads
It appears IBM has cleverly hidden the IDS downloads again on the Passport Advantage Website, at least from my prying eyes. I can find the Solid DB and the DB2 links just fine. Does anyone out there in IIUG-land with a Passport Advantage account know where IBM has stashed the dow…
OPTCOMPIND and perl
Anyone know if I can change OPTCOMPIND after establishing a DB connection or does it need to be set before connecting? ta SLES10 SP1 IBM Informix Dynamic Server Version 10.00.UC5…
physical log might be too small
Hello, We are getting following error while checking checkpoints "Based on the current workload, the physical log might be too small to accommodate the time it takes to flush the buffer pool during checkpoint processing. The server might block transactions during checkpoints. I…
decimal
Hello, Im performing a query that results in a number like 2.93485723 But i just want that my query always return a number like PPP.DD. How can i do that?…
Find Index & its dbspaces
Hi, How to find Index & its dbspace name? (IN systabnames,indexes & tables are combined,but here i need only index) rgds Schillache…
Index fragmentation
IDS 10.0FC9 on Solaris 10 I have a site where an index has hot the 16g limit for a tablespace. It seems from the fine manual that you cannot fragment an index across dbspaces round-robin (I suppose this is meaningless), only by expression. But I don't really want to do this; I j…
Corrupted index
Hi, We were validating the indexes on a table, an error raised telling about index corruption. Here it is: -------------------------------------------------------------------------------- # oncheck -cDI hrms:org.messages Validating indexes for hrms:org.messages... Index tr0035…
Informix Dynamic Server Scheduler question
Hi! I'm working on a project at my faculty. It's a java desktop application with an informix database. I have a problem using informix dynamic server scheduler. I have a particular table in my database. This table contains records that are inserted by the desktop aplication I'm …
indexing varchars
I have a table that has a varchar(40) column on it. This is likely to contains sequential integers. I want to index this column, but given that the beginning of the index will have little variance, will my index be any good? Will Informix hash the values in the index? Am I worryi…
IDS 9.40 End of Support Imminent
I have seen a number of posts on CDI recently related to IDS 9.40 As per the announcement on September 7th 2007, effective on the dates listed below, IBM will withdraw support from the following product releases licensed under the IBM International Program License Agreement: 9…
Heterogenous Queries
I have to perform a heterogenous query over two databases from the same IDS using the informix ODBC-Driver and Delphi 5 IDE. The versions are 9.6 for Informix server and ODBC-driver 2.900. I placed two database components with connections to the two databases. The following stat…
Buffer Pool
Hi, We are running informix 9.4 FC5xs with SAP. Our SAP admin recently advised that the INFORMIX buffers were set to low. Based on some SAP recommendation, we increased the buffers parameter from 301049 to 501049 (about 600Mb to a little les than 1Gb. 2 days later I found the fo…
Creating index with upper function
Informix 11 Why doesn't Informix allow creating indexes with upper function? Something like create index idx_last on table(upper(column));…
defer updating indexes on inserts
Is there any way to postpone updating indexes when doing a large # of inserts until all of the inserts are done without dropping & recreating the indexes?…
Fatal error
Dear Friends, When i am try to onlining the informix i got this messages informix@DBSERVER> oninit -v -y Checking group membership to determine server run modesucceeded Reading configuration file '/export/home/informix/IDS0/etc/onconfig.voms'...succeeded Creating /INFORMIXTMP/.…
index usage
Hi, I have a table with at least 16 indices. I need to identify all the ones that are being used or identify the ones that are not being used by the application. Is there a script or any other method to determine the indices usage? Please let me know Thanks! Informix Dynamic Se…
Re: Best Use of Free Memory
David, I see the confusion now. The English phrase, "Needs to acquire a buffer," is too vague. There are really two major cases, but the manual is only addressing one (the rarer one). The cases are: 1. The required data is already in the buffer pool. In this case the thread nee…
Best Use of Free Memory
BUFFERS is generally a good place place to use extra memory, subject to a couple caveats: 1. If the working set already fits in the current buffer pool, adding still more buffers will not produce any noticeable performance increase. Check "onstat -g buf" output for your %cached.…
use stored proc that return multiple columns in a query
forgot to include version info: IBM Informix Dynamic Server Version 10.00.UC6W5 -- On-Line -- Up 207 days 22:14:39 -- 1441100 Kbytes…
installing informix dynamic server in linux
hi, I'm new to informix... I tried to install informix dynamic server(iif. 11.50.UC2E.Linux-RHEL4.tar) in my linux machine. I'm getting an error like this... One or more prerequisite system libraries are not installed on your computer. Install libpam.so.0, libncurses.so.5…
Informix Dynamic Server v 11.50 Developer's Edition
Does everyone know that the Informix Dynamic Server Developer's Edition v11.50 that you can download from the IIUG Web site (www.iiug.org) is NOT timebombed, is completely and fully functional (including HDR, MACH11, ER etc.). Do you know that the only limitations on this versio…
Informix Dynamic Server v 11.50 Developer's Edition
Does everyone know that the Informix Dynamic Server Developer's Edition v11.50 that you can download from the IIUG Web site (www.iiug.org) is NOT timebombed, is completely and fully functional (including HDR, MACH11, ER etc.). Do you know that the only limitations on this versio…
CONFIGURATION ONAUDIT-IBM Informix Dynamic Server
Hello, I need to configure onaudit and onshowaudit able to register for each of the consultations that are made to the database, either through dbaccess, ODBC or another application. I've been reviewing the IBM Informix Trusted Facility Guide, but I was not working. What param…
How to Store Table in RAM in Informix
Hello All, I want to know that is there any funtionality of Informix that would allow me to store some of the tables in the RAM. I want to do this to increase the query performance? Is there any way, is it possible? I will be waiting for the response. Thanks. Omer Saeed Khan…
How to use Informix Dynamic Server
I have installed trial version of "Informix IDS Dynamic Server Developer Edition 11.5 for Windows 2003, XP, Vista, 32bit Version 1150TC1DE". But i am new to informix. Can anyone explain how to use IDS in detail.…
index creation
Hi, I'm creating index on "PPOIX" table. But it is taking too much time. What might be the reasons Pls help me ASAP…
Functional Indexes?
Ok sports fans, here's a really fun problem. Suppose you have a table with the following columns: Table Foo: Column a: id (Integer) Column b: type (single character) Column c: sequence # Column d: description (varchar up to 128 characters wide) Now there are 3 types of records…
unused indexes
howdy does informix have system views/meta data that indicates the last time an index was used? thanks tom…
Optimizer Issues with 10.00.xC8
RoB wrote: > Thomas, > > We've recently seen a query that showed exactly the same symptoms > after upgrading from FC5 to FC8 on AIX 5.3. The query was rewritten as > it was poorly written to start with but it had never showed any > performance problems on the previous version. Ha…
Index on a synonym?
Hi everyone. Kind of a sanity check question. On IDS 9.4...If we have tableA in DB1 (which is on server S1) and the a SYNONYM for tableA defined in DB2 (on server S2) and someone were to create an INDEX on the SYNONYM...what do expect would happen to the original tableA? I would …
locking error (problem)
Keith, I even dropped my indexes. Still the problem.... Danny…
What is IPA abbreviation ?
Hi all IBM INFORMIX DYNAMIC SERVER 10.00 PRODUCT RELEASE bug_number 168928 description WITH NON-DEFAULT PAGE SIZE AND ACTIVE IPA'S, TABLE LEVEL RESTORES FAIL product_code ONUTILS component_code ARCHECKR What is IPA ? Thanks…
Activate dynamic explain
Marcus, Art, Thanks for your prompt answer. I did some testing and indeed it turns out that I can find the sqexplain.out.ses file in /home/<user>/ when I turn on dynamic explain before the query is prepared. My idea was to use this to examine queries which are executed by an ap…
Index Fragmentation
I've come across a table which has index fragementation as follows: create table "blah".tabwhatever ( pk_id serial not null , ...... fragment by expression (mod(pk_id , 10 ) = 0 ) in data_dbs01 , (mod(pk_id , 10 ) = 1 ) in data_dbs02 , (mod(pk_id , 10 ) = 2…
Fragmenting Indexes
Version: IDS 10 I am currently looking at various fragmentation strategies for Indexes that have keys of type CHAR. I have following questions: 1. Columns that have value of "000987" but is a CHAR, which strategy makes more sense, doing the fragmentation by using SUBSTR or usin…
Index strategy
Scenario: IDS9.4 A table is fragmented (by expression) across 10 dbspaces. What is the best strategy for building a unique index? Should you create it like a regular non-unique index, that is, where it is created within the same dbspaces as the table. Or, perhaps it should be cr…
Functional index
Hi All, We are on IDS 9.40.FC6 and IDS 10.00.FC5, HP-UX 11.11 I need to solve a problem with how integers stored as characters ar sorted. I thought to use lpad, but is doesn't seem to work on a character field. So I have a simple function that pads the left of a character field…
text data type condition
I'm using Aqua Data Studio to query an informix idx 10 database and I'm trying to figure out how to perform a like type condition on a text column. Anyone have any ideas on this, nothing seems to come up in the documentation other than using the SDK to work against the db. Thanks…
Re: 9.40FC9 BUFFERS limit
On Jul 25, 4:41 pm, <eshkambx-i...@yahoo.com> wrote: > Hi > > Does anyone know what is real maximum number (not from release notes) of BUFFERS parameter for IDS > 9.40FC9 for Solaris 10? Exact Solaris version is: Solaris 10 11/06 s10s_u3wos_10 SPARC. > > Situation: > > We have ne…
description of onstat -g ntt
Hi Karl, From IBM Performance Guide v9.4, we can find this explanation on the q-exceed field: << output output field Description -------------------------------------------------------------------------------- -------------- onstat -g ntm q-exceeds Number of times the free buff…
Python Corner: Filling In The Blanks
Dear list: I have just completed another article in my "Python Corner" blog that discusses the topic of Informix application development in Python. This edition covers how to perform queries with variable parameters. Head on over to http://informixdb.blogspot.com/2007/07/filling…
Re: Strange behaviour of the buffer pool
On Jul 17, 8:57 am, RoB <pluma...@gmail.com> wrote: > >My post to the news group is taking forever. In any case, I looked > >around a bit and it seems like the easiest way to get your resident > >section to become larger, other then BUFFERS in the $ONCONFIG, is the > >LOCKS para…
Re: Strange behaviour of the buffer pool
On 17 Jul, 12:10, RoB <pluma...@gmail.com> wrote: > Hi all, > > IDS: 9.40.FC3W2 > OS: HP-UX 11.11 > > What am I missing here? An instance is unexplainably allocating 224 MB > of buffer pool in addition to the amount configured. > > The BUFFERS onconfig parameter is set to 6250 (=…
Re: Strange behaviour of the buffer pool
Looking at a larger instance on the same versions I can see the "resident" pool being 354MB while the "res-buff" being the same the BUFFERS setting: onstat -g mem|grep " R " res-buff R c00000001669d040 1280004096 2848 2 2 resident R c000000000491040 …
Strange behaviour of the buffer pool
Hi all, IDS: 9.40.FC3W2 OS: HP-UX 11.11 What am I missing here? An instance is unexplainably allocating 224 MB of buffer pool in addition to the amount configured. The BUFFERS onconfig parameter is set to 6250 (= 12.2MB): onstat -c|egrep "BUFF|SHM" BUFFERS …
IIUG TV
Great job on the video work!! I look forward to seeing more! -t- -- -- Informix Dynamic Server Database Engine -- http://www.informix.com --…
Re: Slow database creation and loading
On Jun 18, 10:50 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > If you are using HPL, that grabs it's own memory. Data does not go through > the normal buffer pool (unless you are using deluxe mode). You might not > want to increase buffers. I need to double check but I…
create indexes vs enable indexes
if somebody could throw some light, if creating indexes are better than enabling indexes. Also does update stats work better with creating indexes or when indexes are enabled.…
Count on concatenated fields
Hi you can use count on distinct concatenated fields like select count(distinct id_num||sdate) as fld1 from a_ms_test But this will cause more overhead ( cost of query if you check using set explain on will be more) Its recommended to use temp table for better performance of su…
Index inquiry
IDS: 9.40.FC3 OS: HPUX 11.11 We have several hundred tables from a 3rd party application that was created in the 7.x (at least that old) era and many tables have what I think are redundant and unnecessary indices, but wanted to pose the question to the group to see if I understa…
Re: admin tools like PC client
Mike, What version of Informix are you using? Informix SE? Informix OnLine? Informix Dynamic Server? Most Informix versions these days come with a product call I-Connect that needs to be installed on the PC and rovides the Informix ODBC drivers.…
Error when initializing
06:36:20 IBM Informix Dynamic Server Version [b]10.00.UC6TL[\\b] I believe this is likelt ot be a new ish instance to a newish user of the software…
Index down
Hi all:It is possible to activate and to deactivate the one indexes tablaA without lowering the instance.As serious the syntax and the risksThanks The freedom of Linux ... The power of Informix _________________________________________________________________ Exprésate - diseña t…
query execution plan
As Jack Parker mentioned earlier, there may not be any problem, optimizer just choosing query path based on number of rows. If you are seeing performance problem with one plan versus other, may consider using optimizer directive to enforce the correct path. -Sanjit Chakraborty…
moving indexes
I am using IDS 9.4 TC2 I would like to move all indexes to a new dbspace on another drive and, at the same time give them all meaningful names. This includes primary keys, foreign keys, and unique constraints. There are about 1400 indexes in all, so I don't wish to do them one a…
detached indexes
I know I can detach indexes from tables to another dbspace; CREATE UNIQUE INDEX "prod".rlidx_db0_10 ON "prod".db0_10 ( rec_loc ASC ) in maintdbs2; Can I do the same thing with the 'alter table' statement for PKs? ALTER TABLE db0_10 ADD CONSTRAINT PRIMARY KEY ( sta_code ) CON…
Index Size
Is there a convenient way to find out the actual size -- the size in (k|m|g)bytes of indexes? I mean without doing checking or rebuilding, just show me the name of the index and how big it is? This is in IDS 7.31.FS6 if that matters. Thanks. -- Jus' livin' in the Dilbert Zone.…
problems with Informix dynamic server 9.4 @ WIndows 2003
I have problems with connecting the server. I use correct user/password to connect to the server, but the server do not known the server. I'm sure the user exist and is one of the user in Informix_Admin group. I have installed Informix dynamic server 9.4 in a windows xp system in…
error initializing
Hi: when initializing my INFORMIX leaves the following message to me error: $ oninit -v Checking group membership to determine server run mode...succeeded Reading configuration file '/usr/informix/etc/onconfig.nazca'...succeeded Creating /INFORMIXTMP/.infxdirs...succeeded Creat…
Total length of columns in constraint is too long
Hi, We recently migrated our application from MySQL to IDS. While the migration (post DDL and few other code changes) worked just fine in a dev. environment on windows, while executing DDL scripts on Linux, IDS throws this error. Basically I have a unique constraint on 3 columns…
BIG big table ....
On 27 Nov 2006 17:19:18 -0800, "Henry" <ggk517@gmail.com> wrote: >Hi all, > >In informix, how do we define a big table? In other words, normally a >big table should have how many rows? I have a table that consists of 2M >rows. Do you think that a continuous sequential scan over …
problem with select count(distinct
maybe not the same problem as your index is not a composite…
How calculate number of extents of a table
Hi to all. After 3 years working fine the performance of Informix is not fine. I want to check the query to calculate the number of extents for every table of the database and I don't find it on cdi archive. Can anyone post me this query, please. TIA. DJMoralesM…
Definition of DSS/OLTP queries ?
Hi all, IBM talked in the Informix performance guide about DSS and OLTP queries, but I missed a clear definition of these two types of queries. Is a join of 2,3 or 4 tables a DSS query already ? In general I would define a DSS query as complex and long running query, but what …
License Extension
Help.. I need a license extension for the IBM Informix Dynamic Server Version 10.00.FC3R1TL which I downloaded to do a evaluation. How can I get the license extened..my project is in the final stages .. Dhaya Commercial bank…
attached index
We have a query that runs kind of slow, which is exacerbated by the fact that it has to run millions of times. It's searching on partial firstname and lastname. It is using a composite index but I notice that this index as created with a storage parameter of "in table". Wouldn'…
Index Levels
Query to find index levels. set isolation to dirty read; select tabname[1,18], idxname[1,18], levels from sysindexes, systables where sysindexes.tabid = systables.tabid and levels > 2 order by 1 asc,3 desc…
Re: Security Vulnerabilities Addressed in Informix Dynamic Server
Clive Eisen wrote: > Ian Michael Gumby wrote: > > Yes the buffer overflow issues are a pain and frankly they shouldn't occur > > if IBM did formal code reviews. Or rather they should be caught and > > corrected prior to release. > > > > "Paul Watson" <paul@oninit.com> wrote in m…
index width
I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes. I'm trying to find out the width of all our indexes. I can't come up with a query to do this, any help ? I know I have to join systables, syscolumns and sysindexes, and join on…
Security Vulnerabilities Addressed in Informix Dynamic Server
Not sure if this has posted here or not but .... http://www-1.ibm.com/support/docview.wss?rs=630&context=SSGU8G&dc=D600&u id=swg21242921&loc=en_US&cs=UTF-8&lang=en Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THER…
question on sqexplain output
A question about the below sqexplain file from this long query: Basically, I'm trying to understand the output of sqexplain. In this section below, ( the join on s.scsublog_token with sc.token ), does this output mean that the only index being used is the sc.token index , or is…
Indexing A Function???
Hello All, Wondering about the reasoning behind doing something like..... create index some_index on employee ( toupper (department_name) ); 1. Any idea why, not in this specific case, but is this standard ? 2. Would this not be wastefull as opposed to just indexing the de…
HDR and Indexes
Most of the folks using HDR know about the create index issue. Can I have honest reaction on this issue? Thx…
IFX_FOLDVIEW
This is what is in the release notes for 9.40.FC8: 1.5.2.1 Enabling View Folding to Improve Query Performance For certain situations where a view is involved in a query, view folding can significantly improve the performance of the query. In these cases, views are folded into…
IFX_FOLDVIEW
presentation from IDUG/IIUG conference - to improve query performance - IFX_FOLDVIEW set to 0 or 1 Fold Union ALL and multiple table join views…
index on nulls
If we create an index on a field that is mostly containing null values ( 90% nulls ), will the index still use the same amount of space whether the field is null or if it has data ? Thanks ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administra…
Index Rebuild
Is there a way to determine if an index would be a good candidate to be rebuilt, for example, on a table that has a very large number of inserts and deletes? In a case like this, would dropping and recreating an index make it more efficient? Thanks for your thoughts ... Mary Gr…
Index Report
Hi, Does someone have any easy way to generate a basic report listing just the indexes of all tables within a database? Example: Table: customer Indexes: 1-customer_id 2-customer_city Thank you. Tony…
locking and index
Can someone explain me the locking mechanism on indexes ? If the table is on row lock mode, are the indexes always row locked ? Or are there other considerations ? We are in ID7.3, we plan to migrate to IDS10. Is there a difference about locking with indexes attached or detached…
Initialising Informix
I am trying to initialize informix on a new server and I am getting this error. The server is HP UX 11.11 and Informix IDS 7.31 What could be the problem / This action will initialize Informix Dynamic Server; any existing Informix Dynamic Server databases will NOT be accessible …
Informix Connection From WIN CE.NET Emulator?
How do I connect to informix dynamic server from WIN CE.NET Emulator? Is there a WIN CE informix driver?…
SET ROLE and SYSROLEAUTH
Which Informix version? Log a support call. Any indexes on the table?…
How do it get writes form the lrus in V10
> -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Bill > Marshall > Sent: Wednesday, May 03, 2006 7:21 AM > To: ids@iiug.org > Subject: How do it get writes form the lrus in V10 [6648] > > > I have a new HP Intel 32 bit chip mach…
Re: Table Editor for Multiplatforms
Enhancements in Version 4.3 ..... also supports the Informix Dynamic Server 9.x. http://www.ibm.com/software/data/db2imstools/db2tools/db2te/…
INDEXES
Can someone provide me with some instruction on how to rebuild informix indexes on a single table? I droped 256000 rows of data and now wanted to re-index the table and update statistics. Thnaks in advance…
Rebuild indexes
What is the method of rebuilding indexes in 9.4? Could you possible include some systax? Thanks in advance…
insert duplicate values into unique index column
I answered yes to all the secheck prompts and the index was dropped and recreated. This fixed the problem and now I was able to insert the missing data. Thanks for the help. ERROR: 17 bad data record pointer(s) Delete index ? y Remake index ? y Checking data record and index…
Fragmentation Elimination Problem
I am on version IBM Informix Dynamic Server Version 10.00.FC4.…
Re: Informix Dynamic Server 9.30 - Shared Memory
Ronald Schwingel wrote: > Can someone please tell me how much Shared Memory (gigabytes?) > Informix Dynamic Server 9.30 (32-bit version) for AIX 5L can use > before asserting with a Shared Memory access error? Can you provide > documentation that I can pass on also? It looked to…
RE: Informix Dynamic Server 9.30 - Shared Memory
First off you shouldn't be getting shared memory access errors. That sounds more like a bug that technical support should address. I wouldn't be surprised if your getting can't allocate additional shared memory segment messages. Informix 32-bit on AIX is a bitch. The problem …
Informix Dynamic Server 9.30 - Shared Memory
Can someone please tell me how much Shared Memory (gigabytes?) Informix Dynamic Server 9.30 (32-bit version) for AIX 5L can use before asserting with a Shared Memory access error? Can you provide documentation that I can pass on also? We are looking at providing one of cust…
create a DB on Linux
Hi! I have installed Informix dynamic server on Linux. Now I want to create a data base. What should I do? Thanks…
Index space
Just quick question, what will happen if informix runs out of index space? Cause we are facing such problem soon. We are trying to clean the space. Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged …
Re: flagship sunk
Funny I have been able to download IDS 10 for Solaris, Windows and Linux am I just smarter than everyone else? www.informix.com Support and downloads In the middle trials and betas Oh look Informix Dynamic Server (IDS) V10.0 easy!…
Check INFORMIXDIR and DBLANG.FAILED
Hill, what could have caused it ? angel:/opt/informixids/msg/en_us/0333}oninit -v Checking group membership to determine server run modesucceeded Reading configuration file '/opt/informixids/etc/onconfig.angel'...succeeded Creating /INFORMIXTMP/.infxdirs ... succeeded Creating …
RE: Problem with the sysmaster database.
Yes, don't forget to index and update stats on the temp tables.…
Re: IDS 10 - documentation questions
Oops! I mean IBM Informix Dynamic Server Version 10.00.TC3ET Althought the release notes (idse_win_release_notes_10.0.html) start with:- "Release Notes for IBM Informix Dynamic Server Express Edition for Windows Platforms 10.00.TC3E, 1 July 2005 " so it is TC3E or TCE3T? and …
Re: Sql to concatenate multiple rows to a single row / column
Thank you SaltTan - it works beautifully. BTW, we have IBM Informix Dynamic Server Version 9.40.FC6.…
IFX_FLAT_UCSQ
Good question. Reading the note it appears that this happens automatically if the optimizer thinks it will be faster but this flag forces it to choose this path. To see if it will help try flattening sample queries from your system yourself and test unflattened against flattened …
oninit: Dictionary Cache and SPL Routine Cache error
I am having some trouble initializing shared memory on Solaris 9 with Informix IDS 9.4. --------------- $ oninit -ivy Checking group membership to determine server run modesucceeded Reading configuration file '/dbms/informix/etc/onconfig.site'...succeeded Creating /INFORMIXTMP/…
Index utilization
I do not know I-spy and the other products like Server Studio (never used them before) - but I have a question that might be related to them. I would like to see, looking at an entire instance, which INDEXES are being used, and which aren't. Is there a tool that can show / anal…
Add Constraint
maybe the database is building some indexes and checing there are no violations?…
index usages
Hi, If there are multiple indexes on a table and these indexes are used in a select query.Does informix choose the best index out of it or it can choose all the indexes and perform filtering at this level and do the remaining processing. For example if i have a table Tab1(a,b,c)…
Re: RE: Distributed Query Performance in IDS 10
Maybe an optimizer hint would be usefull? I don't know, I'm just guessing. J. -----Original Message----- From: DL Redden <redden96@yahoo.com> To: David Buchholz <David.Buchholz@vitalps.com>, Informix List <informix-list@iiug.org> Date: Fri, 26 Aug 2005 13:34:08 -0700 (PDT) Sub…
RE: Distributed Query Performance in IDS 10
--- David Buchholz <David.Buchholz@VITALPS.COM> wrote: Dave, Thanks for the bug number. I've opened a case with IBM as well so we'll see if they match it to the byg number you provided. Our remote server is IDS 7.31. DL > DL, > > We reported this issue to IBM and they have op…
RE: Distributed Query Performance in IDS 10
DL, We reported this issue to IBM and they have opened bug #173922 for this issue. We only noticed this if the remote server is XPS. What is the remote server in your environment? -Dave David Buchholz Vital Processing Services david.buchholz@vitalps.com -----Original Messag…
Distributed Query Performance in IDS 10
I have a query that runs fast (1 second) in our IDS 9.30 environment but runs long (I quit waiting after 11 minutes) in our IDS 10 environment. The cause is a remote query. The explain from a 9.3 instance: .... 2) psoft.ps_bi_line: REMOTE PATH Remote SQL Request: sel…
getting space used by indexes
I have a database with tables and indexes in one dbspace. Is there a way to find out how much space the indexes are taking?…
New IDS Forum on IBM Developerworks website
This forum provides a place for Informix Dynamic Server developers and users to share their questions, thoughts, and ideas with others. http://www-128.ibm.com/developerworks/forums/dw_forum.jsp?forum=548&cat=19…
Re: Re: oninit error .. [5496]
$ oninit -ivy Checking group membership to determine server run modesucceeded Reading configuration file '/opt/informix/etc/onconfig.padbv2'...succeeded Creating /INFORMIXTMP/.infxdirs ... succeeded Creating infos file "/opt/informix/etc/.infos.padbv2" ... "/opt/informix/etc/.co …
general index question
I have a question about indexes in general. We have several tables that have an index, say, on columns a,b,c then an index on a, or an index on a,b. It has always been my understanding that the second index is redundant as the first index can be used to satisfy queries on a, or a…
Re: Index problem
col6 isn;t in the old index decinfitions at all. so quite probably they have found a better way to declare the uniquness using a much smaller index which is easier to travers…
Index problem
Hello, we have a database table with 7 columns and approximately 10 million rows. There is a unique index on 6 columns. The customer with the 10 million rows had problems with his system because the INSERTs were very slow. Yesterday, his database administrators added 2 new indiz…
Installation problem
I am attempting to upgrade an Informix installation from 7.3x on Sun 5.7 to 9.4 on Sun 5.9. When I run oninit -ivy I get the following: Creating buffer pool 200002 kbytes...succeeded Initializing rhead structure...succeeded init_alarm failed fyi: I installed a new version of IS…
Re: IDS 10 Express Edition
Note 10.00.xC3 = Enterprise Version 10.00.xC3E = Express Edition http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.relnotes.doc/uc3E/idse_win_release_notes_10.0.html Then click on "IBM Informix Dynamic Server Express Edition" in the right pane:…
Index question
Hi, I have a lookup function that works with two tables. The function has been placed as a calculated attribute (call to the function) within a user application. When a user selects table A (1 million rows) and choses the calculated attribute with a count(*), the function does a…
Re: vanilla index
Ray wrote: > Hi All, > > Does anyone know what non vanilla index and this message means? Thanks > in advance! Sounds like the index stat_loc_ix1 is not a B+Tree index but an RTREE, CopperEye, or other VII based index which oncheck -cD cannot crosscheck to verify that all rowids…
vanilla index
Hi All, Does anyone know what non vanilla index and this message means? Thanks in advance! $ oncheck -cDI hoc:stat_loc Validating indexes for hoc:informix.stat_loc... Index stat_loc_ix1 Index fragment in DBspace dbidx01 Index…
Find out less used index?
dear all, how to find out the less used indexes in a table? how many indexes are there for a table in a normal performance?…
Index question
We have a new query, with a where-clause: where a < b (a & b are date fields) and c = 0 (c can have values from 0 to 270) and d = "2" (d can have 4 different values) What is the best way to write this where clause, and which are the best indexes to use ?…
setup Informix Dynamic Server Version 9.40.UC1E1 to linux error
I setup Informix Dynamic Server Version 9.40.UC1E1 to linux execcute oninit -ivy is failed the online.log is write /// 16:31:32 Dynamically allocated new virtual shared memory segment (size 8192KB) 16:31:32 Informix Dynamic Server Version 9.40.UC1E1 Software Serial Number AA…
FOREIGN KEY Problem
Just a note: While investigating another problem about an index, I found in the manuals (9.40 IDS) that the optimizer will not use an index on a varchar column. That may only apply to indexes where the varchar is the root of an index; I did not pursue it any further. (I just cur…
error the running INSTANCIA
Hi: Cuando inicio la instancia me sale el siguiente mensaje de error.... alguna idea, se los agradecere. Me pueden dar una pista ??? saludos ============================================================= Checking group membership to determine server run modesucceeded Reading co…
error the running INSTANCIA
Hi: Cuando inicio la instancia me sale el siguiente mensaje de error.... alguna idea, se los agradecere. Me pueden dar una pista ??? saludos ============================================================= Checking group membership to determine server run modesucceeded Readin…
Composite index
If I have a composite index, I assume that the best performance gain would be to create the index in order of column size - smallest column to biggest column Create index i1_index on tab1 (col1, col2, col3) Where col1 is the smallest, and col3 the largest. Is my assumption cor…
insert from select (table scan) with no join
sometimes an index exisist on the table and sometimes it doesn't?…
RE: is this a problem?
You have a table and index named the same. They both show up. Use some naming conventions for tables and indexes. uidx_tabname_colname for the index.…
Re: Need help!! Sql to Informix Migration
no idea, do the same indexes exist? is it using the indexes in the same way it does in sql server…
Need help!! Sql to Informix Migration
no idea, do the same indexes exist? is it using the indexes in the same way it does in sql server…
How to determine optimum buffer size?
Hello, how do I (or how do you) determine the optimum buffer size (on IDS 9.40, but I think it's a generic question) ? I have a machine with almost 'unlimited' memory (96 GB RAM). Therefore I could define a large number of buffer pages. But I'm not sure if at some point the adm…
What counts sysbufhdr.reusecnt ?
Hi, I found the table sysbufhdr in the sysmaster database apparently containing information about the database buffer pages. One column has the name 'reusecnt' - does anybody know what exactly that column counts? Most entries have reusecnt=0 and I have one system (with a very la…
Re: RESIDENT and BUFFERPOOL
David E. Grove wrote: >>On Solaris the 4MB page size is used for any segment made resident, no >>matter how many there are. > > Since page size is now settable in IDS 10, does the above have any > implication on specifying a BUFFERPOOL page size? I'm no sure whether you understa…
Re: RESIDENT and BUFFERPOOL
David E. Grove wrote: >>On Solaris the 4MB page size is used for any segment made resident, no >>matter how many there are. > > Since page size is now settable in IDS 10, does the above have any > implication on specifying a BUFFERPOOL page size? I'm no sure whether you understa…
Re: Expert advice needed on Buffer management
jpierrot@chubb.com wrote: > Hi gang, His per-session buffer pool requirement calculation is completely bogus since most sessions will be sharing the use of the same pages of data and which pages are shared and which are uniquely used by a single session will differ from server t…
Expert advice needed on Buffer management
Hi gang, I received this below from the architecture Dept. trying to simulate Production like performance in QC and development envs. Assuming performance testing would be equal in all those environments if buffers are adjusted to a certain value. Please advise! My question to yo…
Expert advice needed on Buffer management
Hi gang, I received this below from the architecture Dept. trying to simulate Production like performance in QC and development envs. Assuming performance testing would be equal in all those environments if buffers are adjusted to a certain value. Please advise! My question to yo…
index updates
Hi, If i have several indexes on a table and i perform a update on the table, does all the indexes get updated parallely or they get updated one after the other( sequentially). Bye,…
FanOut of Index
Hi, I want to know how the fanout of an index is calculated for Btree index. Bye.…
indexes
hello stull fuzzy on index retrieval and the order of the indexed fields.... if i have a table with these columns indexed (col1, col3, col4) and the sql says col1, col4, col3 does the index still get used or does it stop at col1 because of the order? i assume always using th…
Informix Dynamic Server information Centre
Great new utility just posted under IDS 10 on the docs site has uses for other products and versions than just IDS 10 so take a look even if you are not using 10 yet!! http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp…
IDS 10 Download?
So many questions... I have played with multiple buffer pools and different page sizes... creating a dbspace with an 8k page size..oh wow! Make size is only 16K though...oracle (where I guess this idea came from) allows 64K! onstat -h (buffer hash chain info) gives two outputs …
Cannot find message file. Check INFORMIXDIR and DBLANG.
im trying to run the oninit -i in the command prompt when the message appear... we installed... IBM Informix Dynamic Server 9.40 IBM INFORMIX-4GL Rapid Development System Version 7.32.UC2 IBM INFORMIX-4GL Version 7.32.UC2 set up sqlhosts, services, onconfig, path... but when tr…
Unique index ??
I am having an argument with one of our developers. He wants me to create the following (unique) index on a new table. This just sounds ridiculous to me. Any comments / suggestions ? create unique index "stanley".iu_dao on "stanley".done_and_out (dao_scheme_chk,dao_categ…
Index extents
All, Is there a way to size the index extents if they are detached? I'm seeing alot of extents allocated to indexes (150+ extents). -Terrence…
renaming indexes
People, We have some tables that have primary and foreign keys and we want to rename the index name to something meaning full. How can we do the rename without dropping and creating the tables. Regards Kenneth…
Index corruption
Hello, Env:AIX 5.2 IDS 9.4 FC4. Created new index for the entire table and within 8 hours while it was sending index to HDR secondary(while synchronising) the entire system freezed with block checkpoint, I had to stop the server on secondary to keep production moving. Did anyb…
compressed indexes
Does anybody know how to lower the number of levels in a btree index ? We have some that have 4 and 5 levels, and I read that is a performance hit. Thanks, Floyd…
compressed indexes
Does anybody know how to lower the number of levels in a btree index ? We have some that have 4 and 5 levels, and I read that is a performance hit. Thanks, ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work…
compressing indexes
Does anybody know how to lower the number of levels in a btree index ? We have some that have 4 and 5 levels, and I read that is a performance hit. Thanks, ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work…
chunk capacity
we are using BM Informix Dynamic Server Version 9.40.UC2X3 on Linux Kernel 2.4.21 the command 'onstat -d' shows ... Expanded chunk capacity mode: disabled how can I change this mode to 'enabled'?…
Re: indexing
"If there are no suitable indexes, then it will use information like B-tree depth, number of rows in each table, distribution of column values to decide which column (s) to do a sequential search on." No indexes means there is no B-tree so there is no B-tree depth to worry about…
Re: indexing
tomcaml@yahoo.com (tomL) wrote in message news:<3158a6a2.0408301033.2b1ab17b@posting.google.com>... > hello all! > > a few indexing questions: > > If the columns I filter on in my query are a subset of the columns in > an index, will the index be used? > > Also -- does the order …
Re: indexing
Thanks, Art. How about this scenario? What if the index contains fields I'm not specifying? will the index still be used? Tom "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.08.30.17.16.08.846438.1355@bloomberg.net>... > On Mon, 30 Aug 2004 14:33:39 -0400…
indexing
hello all! a few indexing questions: If the columns I filter on in my query are a subset of the columns in an index, will the index be used? Also -- does the order in which the columns are specified in my filter matter? thank you tom…
IDS V7.30 and Windows 2000
Can Informix Dynamic Server Version 7.30.TC6 be run on a Windows 2000 host system? TIA Erwin…
index creation timestamp
Hi how to find index createion timestamp? thanks Regards sending to informix-list…
problems of Inztallation's
Hi: I have problems with thle installation of IDS. Plataform: HP-UX 11.11 IDS: 7.31 FD8 I cannot initialize !!! If I want to iniciarlize via onmonitor .... the error is: shared memory not initialized for INFORMIXSERVER 'crazy_tcp If i want to inicialize vi command ..... the err…
Re: Error Initialize Informix Dynamic Server
Art S. Kagel wrote: > On Wed, 04 Aug 2004 05:05:41 -0400, madiego wrote: >>I install Informix Dynamic Server and when I initialize 'oninit -iv' : >> >>This action will initialize Informix Dynamic Server; any existing Informix >>Dynamic Server databases will NOT be accessible - Do…
Error Initialize Informix Dynamic Server
Hello, I install Informix Dynamic Server and when I initialize 'oninit -iv' : This action will initialize Informix Dynamic Server; any existing Informix Dynamic Server databases will NOT be accessible - Do you wish to continue (y/n)? y Checking group membership to determine ser…
Intitialization problem
Hi, I have a problem with IDS 7.31 on SCO 5.0.5 When trying to initialize IDS and it hangs. Here is the oninit -v command: # oninit -v Checking group membership to determine server run modesucceeded Reading configuration file '/usr/informix/etc/onconfig'...succeeded Creating …
Re: Informix Dynamic Server and DB Tuning
I am experiencing similar problems with users migrating from Standard Engine to IDS 9.30. It appears that some applications are more susceptible than others. What application(s) are you running? We also have experience of adding more BUFFERS, LOCKS, etc and not improving the sit…
RE: Informix Dynamic Server and DB Tuning
Muthana Increase BUFFERS in onconfig file. UPDATE STATISTICS for each table (as a minimum) rerun the query and see what improvements you get. If you still have problems post the results of:- onstat -p onstat -m onstat -R and sqexplain.out of any particularly slow query or updat…
Informix Dynamic Server and DB Tuning
Dear Sir, The question how can make DB Tuning for Informix Dynamic Server version 9:30 to get the query faster. I used IDS 9:30 and it's not quick enough like our old Standard Engine. which Parameter can i change to make that or what shall i do? Regards Muthana AL-Temimi…
Index question
Hi, I need know what SMI tables are involved in indexes. With sysindexes I can know Ids and Tabid of each index, but, which tables are necessary to know index columns names? Dbaccess shows this information, but I need to do a query .... Thanks Paola…
Can IDS accessing Informix SE Tables.
Hello, Now we are using Informix SE. We are planning to go Infomix Dynamic Server 9.x. Before migrating our current applications to IDS 9.x., first we have to develop a new application in IDS 9.x, in that we need to use some of the tables from informx SE. Could you please answer…
Re: query performance tools
tomL wrote: > > hello all, > > we are in dire need of optimizing sql for our system. Are there > techniques or (ESPECIALLY!) tools we can use that will monitor sql > sent and report on the worst performing sql utilized in our system? > > We are on informix 9.40 FC3 on hp-UX 11i >…
Re: query performance tools
"June C. Hunt" wrote: > > "Obnoxio The Clown" <obnoxio@serendipita.com> wrote: > > > > tomL said: > > > hello all, > > > > > > we are in dire need of optimizing sql for our system. Are there > > > techniques or (ESPECIALLY!) tools we can use that will monitor sql > > > sent and r…
Re: query performance tools
"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<2i9ei9FkgvisU1@uni-berlin.de>... > "Obnoxio The Clown" <obnoxio@serendipita.com> wrote: > > > > tomL said: > > > hello all, > > > > > > we are in dire need of optimizing sql for our system. Are there > > > techniques…
Re: query performance tools
June C. Hunt said: > "Obnoxio The Clown" <obnoxio@serendipita.com> wrote: >> I don't know what I could have contributed that "Noons" hasn't, apart >> from >> charm, grace, style, wit, good looks ... ah. > > You forgot to throw in modesty, perhaps? :-) "Humility". I like humili…
Re: query performance tools
June C. Hunt said: > "Obnoxio The Clown" <obnoxio@serendipita.com> wrote: >> >> Oh, sorry. "Fool". Apparently I'm a fool. Is that the same thing? > > Not exactly. But if I had to pick one... :-) > >> Have you considered onstat -g sql? > > You must have missed the "onstat -g s…
Re: query performance tools
Clive Eisen said: > June C. Hunt wrote: > >> "Obnoxio The Clown" <obnoxio@serendipita.com> wrote: > >> >> [P.S. Thanks for the giggle. It has been rather, um, dry here without >> you.] > > That, IMHO is the understatement of the week, at least. I don't know, I see opinionated …
Re: query performance tools
tomL said: > hello all, > > we are in dire need of optimizing sql for our system. Are there > techniques or (ESPECIALLY!) tools we can use that will monitor sql > sent and report on the worst performing sql utilized in our system? > > We are on informix 9.40 FC3 on hp-UX 11i I c…
query performance tools
hello all, we are in dire need of optimizing sql for our system. Are there techniques or (ESPECIALLY!) tools we can use that will monitor sql sent and report on the worst performing sql utilized in our system? We are on informix 9.40 FC3 on hp-UX 11i thanks in advance.…
Re: opinions on this SQL statement
Art S. Kagel wrote: > > Uh, NO! If the sub-query is not correlated, then Informix executes it once > only. If the sub-query IS correlated, then Informix unwinds the sub-query > performing a simple join instead (this is called sub-query flattening) again > only performing the s…
Informix IDS problem
Hi All, When I try to start the IDS 9.40 on HP PA-RISC machine, I get the following error. If I reinstall the IDS it works properly. If I shut down & restart the database it fails with the below message.. Checking group membership to determine server run modesucceeded Reading …
table or index
this is the structure of the table systabnames in the database sysmaster |------------------------------------------|-------------------|----------|---| |Column Name |Column Type |Null |Key| |------------------------------------------|--…
RE: Clearing Buffer Pool
Informix (unlike MS SQL) keeps all pages in the buffer pool 'forever' - until page becames replaced by another page... ------------------------------------------ Alexey Sonkin > From: Dave Griffen [mailto:dgriffen@nospam.finishline.com] > > I do mean to remove all non essential…
RE: Clearing Buffer Pool
To clear the buffer pool from dirty pages, generate a chackpoint with 'onmode -c' If You mean to remove all pages from it, then it's impossible. ------------------------------------------ Alexey Sonkin > -----Original Message----- > > Does anyone know if there is a way to clea…
Clearing Buffer Pool
Does anyone know if there is a way to clear the buffer pool without bouncing the engine?…
Dose anybody have the dbtop script in the Troubleshooting informix course?
Dears, I study the Troubleshooting ibm informix dynamic server course. At the module 4 exercise need the dbtop script. Dose anybody has this dbtop script and post for me? Thanks. Philip Yeh.…
cannot drop index
I am having problems dropping an index. The system complains that I don't have exclusive access, but there are no locks on the index or the table. Any ideas? Malcolm…
Work group edition - Unlimited edition??
Taken from IBM web site:- =========================================================================== The following Informix Dynamic Server Workgroup Unlimited editions have been added to the IBM Data Management Gold Transaction Processing Bundle for SMB Unlimited: IBM Informix…
Performance is getting worst after turn on the LRUAGE
On Sun, 28 Mar 2004 22:40:01 -0500, miyaki wrote: Other is usually 'unused pages'. Look at the top of the onstat -P report. On the line for address zero (0), is the value under Other very large? Those are unused buffer pages, ie no data pages have been loaded into them. There…
Re: [Informix Dynamic Server (IDS):] Digest 2671
For a course in the US, check out: http://www-306.ibm.com/services/learning/ites.wss?pageType=course_description&co urseCode=L1760&country=us&language=en (IBM course L1760). There is probably a similar course offered in Europe somewhere. Hope this helps. Kevin Doiron Toronto,…
FW: Whatcha' wanta have?????
Some indexing additions: 1) Inclusion of columns in the index that are not part of the index key but help speed processing. This feature is available in a number of other rdbms's. 2) Use more than one index on a table to filter the result set. sending to informix-list…
FW: Whatcha' wanta have?????
Some indexing additions: 1) Inclusion of columns in the index that are not part of the index key but help speed processing. This feature is available in a number of other rdbms's. 2) Use more than one index on a table to filter the result set. sending to informix-list sending …
Re: Buffers and LRU.
Madison, the standard interpretation of LRU is Least Recently Used. The standard usage of such a queue is as follows: Each queue has an MRU (Most Recently Used) end and an LRU end. When a buffer is touched (either read or written) it is moved to the MRU end of the queue. When a…
Detaching Indexes
Hi All, IDS 7.31.UD6 AIX 4.3 I recently began experimenting with detaching indexes. I was under the impression that moving the indexes to a different dbspace would decrease the space used in the main data dbspace. However, this is what I found: (4K Pgs) …
Re: Transaction support in informix dynamic server(IDS)
chaithra wrote: > i wanted to know, if in IDS we can get to know the status of a > transaction at any given time. or once any transaction commits or > rollsback we should be able to know the status. > is it possible through c++ interface provided by the IDS. I'm not quite s…
Transaction support in informix dynamic server(IDS)
hi all, i wanted to know, if in IDS we can get to know the status of a transaction at any given time. or once any transaction commits or rollsback we should be able to know the status. is it possible through c++ interface provided by the IDS. thanx in advace chaithra…
Buffers and LRU.
The buffer pool is the page cache of the engine. The LRUs are simply a means of chaining the buffers together. There are two chains per LRU - the free and the dirty. (Actually a free buffer has a page in it, but it is not dirty.) There are multiple LRUs so that if a thread tri…
configure computer for using informix
what shell I do to make my computer work with informix. I have installed winXP on my computer, informix 4 i informix dynamic server? THANKS…
RE: query performance SE vs IDS
-----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Obnoxio The Clown Sent: Friday, January 02, 2004 1:13 PM To: informix-list@iiug.org Subject: RE: query performance SE vs IDS Dirk Moolman wrote: >> OB wrote: > …
RE: query performance SE vs IDS
-----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Obnoxio The Clown Sent: Wednesday, December 31, 2003 3:35 PM To: informix-list@iiug.org Subject: Re: query performance SE vs IDS Jack wrote: > "Art S. Kagel" <ka…
Re: query performance SE vs IDS
vomaringo@yahoo.com (Jack) wrote in message news:<682ae665.0312300319.5f59a97b@posting.google.com>... > Hi, > > i'm looking for arguments to explain why several dbaccess on a single > table are 2 times slower with ids than se. i understand this test > isn't quite pertinant.The ta…
Re: query performance SE vs IDS
On Tue, 30 Dec 2003 06:19:42 -0500, Jack wrote: > Hi, > > i'm looking for arguments to explain why several dbaccess on a single table > are 2 times slower with ids than se. i understand this test isn't quite > pertinant.The table is organize differently. The sqexplain shows an e…
Re: query performance SE vs IDS
Hi, I agree with "Obnoxio" and I would add: a) Have you runned "update statistics" (high, medium ? ) b) Do you are talking about "select" tables, or "update", or "delete", or "load" ? c) Have you try using "directives" ?? Regards. H N Y (happy new year ) Obnoxio The Clown <obn…
query performance SE vs IDS
Hi, i'm looking for arguments to explain why several dbaccess on a single table are 2 times slower with ids than se. i understand this test isn't quite pertinant.The table is organize differently. The sqexplain shows an estimate cost equal to 1. i don't think so it could be less…
Re.Extents & Indexes
Hello Everybody, Env : 9.30 uc3 AIX 5.1 1) Is there any limitation on the first extent ? This is the current scenario. Dbspace name - xyz Data dbspaces - 8 (each 2gb * 2 chunks) Index dbspaces - 4 (each 3 gb) Fragmented by round robin. First extent size 1000000 Now if I chan…
Where can I get the client API?
Hi, does anyone knows where to get the client API for Informix Dynamic Server 7.x? Perhaps we want to access the server via api directly. Kind regards, Joachim Engel.…
Informix optimizer
Hi, Does anyone know if the Informix Dynamic Server (7.31 or 9.4) database optimizer uses star schema optimizer techniques? Thank you, Tony…
TRUNCATE TABLE command in 9.3?
I am aware that Informix Dynamic Server previously has not included the TRUNCATE TABLE command. Does anyone know if 9.3 or 9.4 include this command?…
Linux fgl_getenv
Informix Dynamic Server Version 9.30.UC3 on Linux. Do I have to do something before I can use the fgl_getenv call in a stored procedure? When I try I get 674: Routine (fgl_getenv) can not be resolved.…
Recreate Index
Hi, I want to migrate indexes from one dbs to another, where the name and the odrer to create indexes are stored, so i can generate all sql odrers to do that using awk tool. One way is : 1 - Dbschema of the database to obtain : dbname.sql 2 - G�n�rate un sql file to drop indexes …
an alternative to PERFORM
Still have several apps in use that are primarily PERFORM screens, ACE reports and simple menus. Has anyone found a solution that requires as little code as PERFORM and offers query by example on any field? I want to move on but coding is not my strong point. Any backend will …
Indexes
hello, here is my question. if you have a table called related_to and it has these fields: id active from_class_id from_id type_cid status_cid seq_num to_class_id to_id created_by create_date updated_by, update_date with this index: create index "informix".related_t…
Re: informix alter table with syntax error in unix
Iztok Bofulin wrote: >> If you add the NULL yourself your problem will be solved: >> check ((field_a IS NULL ) OR (field_a IN ('A' ,'B' ,'C' ))) >> > > But remember that using an OR operator may significantly decrease > query performance! Not on a constraint especially in this c…
New index
Hi, If you create a new index on a table already having some indexes is it necessary to run the 'update statistics' on everything again or for just the new index. (using IDS 7.31) Thank you, Tony…
Re. Indexes
Hi Everybody, How do we know the hit on a particular index, example : Table A has index named Z and I want to know how many times "index-Z" was hit by the SQL or it was not hitting at all. Thanks in advance, Sushil..... _________________________________________________________…
Index extents
Using IDS 9.30FC2 on an HP-UX 11.0 box. in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example: prddwh:'dw_admin'.fl_keylinks 943776 40 prddwh:'dw_admin'.fl_keylinks_n1 139338 8 prddwh:'dw_admin'.fl_keylin…
RE: Dynamic Server versus Universal Server ??
Originally, Version 7.x was called 'Informix Dynamic Server', Version 9.0x was called 'Universal server' Starting with 9.1, Universal server became 'Dynamic server with Universal data option'. With 9.2, new names 'Internet Foundation 2000 ' and 'Dynamic server 2000' ware given…
Creating Index
Hi all. I have a problem when creating index. After build a index I can't build other on same table after 3 o 4 minutes. I receive an error "the table is locked". Environment: Solaris 2.6 IDS With J/Foundation 9.21.UC3 HDR running. Any idea about that? PS Sorry for my engl…
Informix Dynamic Server 2000 startup problem
Hi, While starting up informix in multi user mode , we wait for the database to come up by checking the onstat - o/p . But if the system is loaded, even after onstat shows status as online , the connection is rejected by the database . The wait loop that we do is as follow…
index duplicates PK, is it redundant?
I came across a database that has an index with the same columns, in the same order, as the primary key. Is this index likely to be useless and redundant? I expect that if the index had different sort orders it could be useful. How about one index that duplicates another - if …
Drop Index
Dear All, Is there anyways to speed up the drop index action? I have an index which is no longer used and want to drop it but it took me more than 4 hours. Does the lock table exculsive command help?? Thanks in advance Franklin…
why I can't see the output of "onstat -g wts"
test:/informix>onstat -g wst Informix Dynamic Server 2000 Version 9.21.FC7 -- On-Line -- Up 5 days 13:23:03 -- 11132960 Kbytes Wait statistics not enabled How to enable the wait statistics???…
Re: Query Performance
identical databases on box A and C are onbar restores from Box B >>> John Carlson <john_carlson@whsmithusa.com> 06/18/03 09:38AM >>> On Wed, 18 Jun 2003 08:59:31 -0400, "Brandt Edwin" <bedwin@yankeecandle.com> wrote: Are your table schemas the same? I didn't see the index ke…
Re: Query Performance
quick correction, i meant to say three identical copies of our databases, on three different boxes. >>> "Brandt Edwin" <bedwin@yankeecandle.com> 06/18/03 08:59AM >>> Ok, stumped here. please excuse the long post, but i thought the sqexplain info was important. I have three ide…
Query Performance
Ok, stumped here. please excuse the long post, but i thought the sqexplain info was important. I have three identical boxes with two Informix servers and four databases a piece (one production box, that was just restored to two test boxes) Box A is our fastest box and screams,…
informix SDK 2.81 ifxoledbc OLEDB and ADO
When Updating or deleting a non existing row in a table, the informix oledb provider produces an "UnSpecified error". Environment: Informix Dynamic Server 7.31 Informix SDK 2.81 ADO 2.6 Any help will be appreciated. Thanks in advance…
index
hello group this is my select : distretto and numero is not primary key select * from tt_master where distretto=0444 and numero=00563921 ---------------------------------------------------------------------------- ------------------- i have created the this index : CREATE inde…
Functional Indexes?
Ullo chaps. Each table in our db has a set of columns at the end, two of which indicate record status - they hold an integer value and a char(20) code which are ALWAYS related, i.e. the ref and code are a pair. Crap design but unfortunately historical (hysterical?) and the coders…
[Fwd: Perfomance problem [547]]
Sorry, the attachment is here... > Hi everybody, > I have performance problem. A query have time proccesing very high but > in another machine this is not. I send you the query plan. > Thanks Paola…
Perfomance problem
Hi everybody, I have performance problem. A query have time proccesing very high but in another machine this is not. I send you the query plan. Thanks Paola…
Set Explain On
Hi, I have a sql statment which takes a long time to run, and i tried the explain on and have the following output: 1) informix.cut: INDEX PATH Filters: informix.cut.status != 'X' (1) Index Keys: settled_at Lower Index Filter: informix.cut.settled_at >= datetime(2003-01-01 08:…
regarding concept of physical logging??
hello, i have a serious confusion regarding tha concept of physical logging, this might sound silly for an expert kindly bear with me and explain.. according to what the book says.. physical logbuffer contains the before images of the data that taken from disk for modification..…
RE: what is light scan and look aside??
All of these 'advanced' questions. Are you retreading from some other vendor? You should consider a course. Light Scan: Normally when you read a table, you go through the buffer pool. That is your row is retrieved from disk, stuck into a buffer, an LRU entry is made to track…
a question about sysindexes
According to the schema of sysindexes, a unique composite index is present on idxname + owner + tabid. Then why does the server disallow duplicate index name to be created even when the tables are different. create index test1 on table1(fld1); create index test1 on table2(fld1)…
Informix disk performance problems with archives
> When performing backups (using ontape) or initializing dbspaces, the > system slows to a crawl -- literally. Well, not literally. Sorry, I don't have much insight to your actual query, except to point out the obvious, that archives and dbspace init are both highly i/o intensiv…
What requires an IDS Linux?
Hello What requires an Informix Dynamic Server Linux a desktop and/or a network version of Linux? Thanks in advance Jacqueline Romero Miranda…
Informix v Oracle
Hi Is there a function in Informix Dynamic Server 2000 that is like Oracles CREATE SEQUENCE that uses NEXTVAL/CURVAL and %ROWCOUNT, ect? Thanks for all contributions Jordan jbruce@asaglobalserv.com…
Cross instance query performance tuning
I have two instances of Informix running. On some limited situations I need to access a database running on the second instance from the first instance utilizing the database@aliasname:tablename <mailto:database@aliasname:tablename> syntax. I'm having severe performance proble…
RE: Non-vanilla index
From: Watson Paul <Paul.Watson@fefc.ac.uk> > > > From: "Watson, Paul" <Paul.Watson@aggregate.com> > > > > > >Simple question really what is a 'Non vanilla Index'? > > > > > >$ oncheck -cDI ireach:cw_doc_rend > > > > > >Validating indexes for ireach:informix.cw_doc_rend... > > > …
RE: Non-vanilla index
> From: "Watson, Paul" <Paul.Watson@aggregate.com> > > > >Simple question really what is a 'Non vanilla Index'? > > > >$ oncheck -cDI ireach:cw_doc_rend > > > >Validating indexes for ireach:informix.cw_doc_rend... > > Index 240_1481 > > Index fragment in DBspace datad…
Re: Non-vanilla index
From: "Watson, Paul" <Paul.Watson@aggregate.com> > >Simple question really what is a 'Non vanilla Index'? > >$ oncheck -cDI ireach:cw_doc_rend > >Validating indexes for ireach:informix.cw_doc_rend... > Index 240_1481 > Index fragment in DBspace datadbs01 > Index 24…
Non-vanilla index
Simple question really what is a 'Non vanilla Index'? $ oncheck -cDI ireach:cw_doc_rend Validating indexes for ireach:informix.cw_doc_rend... Index 240_1481 Index fragment in DBspace datadbs01 Index 240_1613 Index fragment in DBspace datadbs01 Index cw…
Retrieving product name
Hi all, Is their a system table somewhere on an informix server that stores the product name. For example 'Select owner from systables where tabname = � VERSION� will return the version of the server. I would like to be able to tell if a server is a Universal server or a Dynamic …
IDS 7.3x on Win2000?
Does Informix Online Dynamic Server 7.3x runs on Windows 2000 (Professional)? Thanks for your help. Guy Dillen…
Query Optimization: Wrong execution plan?
Xavi Tarafa Mercader wrote: > Any suggestion about how to improve the query performance? Set OPTCOMPIND parameter ( in onconfig file ) to 0. > Xavi. Leonid.…
Perfformance issue with SQL Queries involving more than one blob field
I am running into a performance problem with an Informix database using text blobs. The issue is queries that include text blobs are REALLY slow even when they don't contain any data. It seems to be a networking issue somehow because the server is in another state and the perfo…
Re: How to search for a string in a TEXT field
Hi, :-( SELECT name, REPLACE(text,"ZNOTREADY","found") AS your_output FROM tablewithtextfield; Blobs are not allowed in this expression. I use Informix Dynamic Server Version 7.31.UC2A…
Re: Performance problems with large SELECTS in 7.3x ?
Stefan Weideneder wrote: > > Hi, > > I tried to select some pages of a table that was a bit larger > than my buffer pool. I estimated that my statement will take > about 5 seconds but it took 12 seconds. > I found out, that the server spent approx. 7 seconds for > "buffer waits".…
Create user in Informix Dynamic Server
Hello all I`m new in INS. Could you help me? I have Database server with one user Informix. How to create another user to Database?…
Informix ODS on Win 2000?
Does Informix Dynamic Server V7.31.TC5 for Intel Pentium Windows NT 4 also run on Windows 2000? Thanks for any help. Guy Dillen ITP…
Informix cost???
Someone can said how much does Informix cost? I need to know: -Informix costs -cost for client Thanks very much. I need the last version of dynamic server for Unix. Ciao, Berna…
After CREATE INDEX
Raymond Chui wrote: > > If I > > CREATE INDEX table_ind ON tableA (col1, col2); > > That means will index col1, col2 in tableA. > But after I index tableA, I do INSERT/UPDATE/DELETE rows > Will also index the col1, col2 in tableA for me? Or I have to > DROP INDEX and CREATE INDEX…
Replication
Hello everybody. I would like to know if it is possible to replicate an Informix database between a UNIX-based 7.32UC2 Informix Dynamic Server and an linux-based IDS2000 without problems. Thanks in advance. jhernandez@geuropa.com…
getting user information in a trigger
im building a trigger to audit user activity in certain tables. Is there a way to query the system to determine a user id that is performing some activity on a table?…
rebuilding indexes
David Palmer wrote: > In 7.3, are there other ways to rebuild indexes other than dropping and > re-creating them? > DISABLE then ENABLE index(es) will cause them to be rebuilt: SET INDEXES FOR <tablename> DISABLED; -- All indexes on a table -or- SET INDEXES <indexname1>[, <in…
utility to time queries
Greg Starnes wrote: > > Does Informix have a SET TIMING ON function? SET TIMIMG ON is the Oracle > function. > I'm trying to tune a bunch of queries, and would like the db to time the how > long the query takes . No but SET EXPLAIN ON will report its cost calculation which is a…
Re: How do I check LRU contention - onstat -g spi ????
On Tue, 11 Apr 2000 13:30:59 -0400, "Art S. Kagel" <kagel@bloomberg.net> wrote: >bothwellno@spam.duke.edu wrote: > >> Art S. Kagel & Family <kagel@erols.com> wrote: >> : InfoJones wrote: >> : [output SNIPPED] >> >> : To determine whether buffer contention or LRU contention is in…
Re: How do I check LRU contention - onstat -g spi ????
bothwellno@spam.duke.edu wrote: > Art S. Kagel & Family <kagel@erols.com> wrote: > : InfoJones wrote: > : [output SNIPPED] > > : To determine whether buffer contention or LRU contention is involved > : try to calculate your buffer turnover rate: > > : Buffer Turnovers = (dskread…
dynamic server with Informix distant database
We have this problem : - write of a html page (this pages will contain many many fields). - Receiving of the page data on a http server based on a M1 machine. - Using of 4gl/informix programs based on the M2 machine with the pages data - 4GL programs will set up a � result � tabl…
HELP! Informix Dynamic Server 7.3
I'm trying to install a new IDS on Linux (RedHat 6.1) on a cooked file. Everything looks right, but upon oninit, I receive the following: oninit: Cannot open chunk '/u1/naf_chunk'. errno = 2 oninit: Fatal error in shared memory initialization Log reflects: 09:11:07 Informi…
Re: How do I check LRU contention - onstat -g spi ????
Art S. Kagel & Family <kagel@erols.com> wrote: : InfoJones wrote: : [output SNIPPED] : To determine whether buffer contention or LRU contention is involved : try to calculate your buffer turnover rate: : Buffer Turnovers = (dskreads + bufwrits) / BUFFERS : BTR = (Buffer Turnove…
UDR Support in Informix Dynamic Server.
Can someone tell me what exact version of Informix Dynamic Server that first supported C User Defined Routines. I've tried searching for this on their website with no joy. (their advanced search doesnt work at all !) Rgds, Peter. Sent via Deja.com http://www.deja.com/ Before y…
OFFICE Connect V.1
Informix Office Connect, Version 1.0, is compatible with the following Informix servers: Informix Dynamic Server, Version 7.3 or later Informix Dynamic Server with Universal Data Option, Version 9.14 Informix Dynamic Server 2000, Version 9.2 or later e Informix-SE 7.23 n�o func…
Index
Hi all, Is it possible to drop and create an new index while there are users on the system and using that table? If yes, how ? Thanks in advance, Alex…
Syncsort with Informix
Would a product like Syncsort Sort package for Unix, as a replacement for the Unix sort, give better performance for DSS type queries that do a lot of group by or order by in an Informix Online 7.31 environment? (We have a growing need for report writing needs querying our Inform…
no index used
Have you tried an order by xxx ? where xxx is the indexed column.…
Re: Why sequential scan???
Have you tried an order by xxx where xxx is the indexed column?…
unique index implications
Does a unique index mean that no nulls are allowed in the columns for that index? How can this be so if null is by definition undefined and has no equivalence?…
System catalogue
Hello! Can anybody explain me how I can create trigger on "informix".systables? (The server I deal with is Informix Online Dynamic Server 7.3.) Max…
Keeping Indices Efficient
I have some questions regarding index rebuilds. It is recommended that we rebuild indexes and constraints on highly volatile tables on a regular basis. 1) What does "set indexes disable/enable" command do to an index. Can this be used as an alternative to "drop and create" an in…
Compatability between Informix products
Hi, I need some version compatability information for the following products. Hardware Platform: Hewlett Packard OS Platform: HP-UX version 10.20 Informix s/w: Informix Universal Server 9.20 Informix Dynamic Server 7.31 Informix Gateway to DRDA vers…
Index on IUS :-)
In IUS all indexes are detached index by default. How can I create a attached index? Thanks, Sash…
RE: checkpoints taking as much as 25 secs
Scott Huppert wrote: > > It's logical that turning the entire buffer pool every 10 minutes is > probably bad, but what is a reasonable time frame? Is there any rule of > thumb? On a busy server that is NOT primarily a DSS/DW server I'd like to see the buffer pool turnover rate …
Open 4GL form with record displayed. . .
I posted a similar message before, but only got an answer on the trigger part of the question. I would like to open up a 4GL form, but instead of querying for a record after the form opens, I would like to perform the query first, and then open up the 4GL form with the record in…
informix dynamic server on linux help
Hi all, I am an informix newbie, and i have problems running it on redhat linux 6.0. The installation went just fine, but now when i start dbaccess and try to create a new database i get this error message: 25584: Network driver can not access server program /opt/informix/lib/sq…
Questions on Query Performance (IDS7.3)
I have a table, let's say STATABLE, which is expression-fragmented on date. Every fragment holds one day's data. I want to access a certain day's data, such as 1999-11-10. I have 2 ways to to do this. I can write query like: select ... from STATABLE, ANOTHERTABLE wher…
Informix Dynamic Server on IBM SSA
Can any one share their experiences, recommendations, pit-falls, comments about creating an IDS db engineOLTP environment on an IBM SSA Raid system? We have 2 SSA adapters and have 4 loops with 4 drives each. The IBM techs for the Smoothstart services will be coming next Tuesday…
ovbuff vs. fgwrites question
Bobby Medus <jeepin@eatmydirt.com> wrote in message news:382C304D.90240C61@texas.net... > This may be a stupid question but you know the old saying..... > > I noticed an increase in 'ovbuff' on the onstat -p. This is a very > small database running on NT and so I increased my b…
Re: LOAD FROM... stmt is very slow
You should also post your onconfig file. You indicate that you need to test a large queries performance. It could be that the instance is not configured properly. The first instance I brought up worked fine until I had inserted 50,000 transactions - then hit the floor. After …
Please help me!
Hello! 1, Can somebody give me information, what is the new features for Version 7.24 of Informix Dynamic Server according to Version 7.20 of Informix Dynamic Server? 2, Are Version 7.20 of Informix Dynamic Server and 7.20 INFORMIX-ESQL/C 2000 Year Compatible? 3, W…
Re: installing Informix dynamic server
From: "Joerg_Mauz@abs.org" <Joerg_Mauz@abs.org> > Is there something wrong the the gateway, Hotmail or are there just loads of idiots posting today. (Apart from me, that is! :-) ______________________________________________________ Get Your Private, Free Email at http://www.hot…
installing Informix dynamic server
hi all, can anybody give me an URL where to find a good description of how to setup and install an Informix Dynamic Server with using RedHat Linux ??? I've searched a whole day but founf nothing useful.. :( thx ind advance joerg…
Help! Cant start database service
Hello All I had my informix dynamic server (7.3) running fine till a few days back. Now the dynamic server service and the informix messenger service won't start automatically and if I try to start the server, it does start but I'm unable to connect to it. If I try to start the…
Dropping/Recreating Index vs Setting Disable/Enabled
Hi, I need to drop/create index for a very large volatile table(> 1 million rows) frequently to enhance performance. If the index has become inefficient, does disabling and enabling the index rebuild the entire index tree like what dropping and creating the index do or doe…
Informix tools
If Informix version 5 uses the same connectivity as IDS 7.X, then the Zero Impact Sql Monitor will capture all SQL queries with their performance. The Zero Impact Service Level Monitor will also report the service levels by user (average end user response time, average database…
Informix Online Dynamic Server DBA Book
Hello Could anyone recommend a good Informix Online Dynamic Server book. I did a search in Amazon.com but I could not decide on which one to choose. Thanks in advance. Ajay…
mirroring
Generally mirroring degrades performance, if it impacts on it at all. The only situation where I can think of performance benefits from mirroring us the rather arcane one whereby intensive queries are directed to a mirror rather than the primary disks. Neil Jim Riffe wrote in …
pricing for informix info?
Hello, Was wondering what other people out there paid for informix dynamic server 7.3... Basically, i wanted to know if they had a web server licensing schem like microsoft. or ibm's db2 (per cpu license). and would the licenses be transferable if I decided to move from le…
Re: Determining version dynamically
Hi, If you have Informix Dynamic Server then use the following sql-statement ' select DBINFO('version', 'full') from systables where tabid = 1' result from the sql is : Informix Dynamic Server Version 7.30.UC5 Take a look in Informix Guide to SQL: Syntax for more about DBINFO…
KAIO: out of OS resources error
Hi~ My informix dynamic server write "KAIO: out of OS resources error .." in online.log. This error log is written during ontape task. How can I solve this problem? platfom : SunOs 5.5.1 Informix Dynamic Server 7.24 uc6 Thanks.…
Help! Query Performance
I am trying to find a way to improve performance on the following query. From the optimizer output (shown below), it appears that the MATCH condition is causing the most drag on performance. I'd like to get the optimizer to filter on "alert_partnum.family" (2.1 below) BEFORE the…
Index on foreign key constraint
Why does Informix create a duplicate index on columns used in foreign-key constraints?…
64bit IDS for Solaris 7/SPARC ?
Is there a 64bit Informix Dynamic Server 7.3x for Solaris 7/SPARC available? tia, joerg…
Posting from the Informix-list
what is the last version of Informix Dynamic Server for HP-UX 10.20, where can i find more Information about this.…
Latest versions for various Informix products ???
Which are the latest versions for Informix Dynamic Server Informix Dynamic Server, Workgroup Edition Informix 4GL Informix SQL on Solaris 7/SPARC ? Where can I find such informations on Informix's website ? tia, joerg…
cdr command line utility in informix 7.24
Hi, Is there a cdr command line utility in informix dynamic server 7.24? I tried to locate it, but the executable is nowhere to find. Thanks, Lu…
[Q] What does Error -244 mean
I'm tyring to perform a delete query via ODBC to Informix using the Intersolv drivers. I get the following error: Could not do physical order read to fetch next row. (Error -244). Does anyone know what it means and how to correct it? Thanks Steve…
Problem Installing Informix Dynamic Server
I downloaded a Try&Buy version of Informix Dynamic Server V7.30 for NT. I installed it on an NT Server. The install seemed to go ok, but after a short time of investigating IECC, I got a blue screen. After this when I started the Command Center, a task seemed to start, but it w…
Indexing
Greetings all, Following some poor response times during recent tests of a new application, I've taken a look at the SQL generated and have a simple question for all you Informix gurus out there. If we have : (i) a composite index on a table defined on columns (A,B,C) (ii) a que…
NEW ERA
What is the perfomance of Informix New Era?. It has better performance in queries than Standart Engine or Dinamic Server. Is diferent his language source? Could we migrate the programs 4GL of Standard Engine to New Era? If someone know something please write to me Thanks…
Index.
Donde puedo encontrar algun manual o articulo sobre el manejo de los = indices de Informix 7.20 , sobre todo para el tema de optimizacion... Gracias. jhernandez@geuropa.com…
Problems starting IDS 7.30.TC7 on Windows NT SP4.
When i try to start informix dynamic Server, the following message appears "Only informix user can start informix dynamic server" this message appears when i logged on with the informix's user. When i try to start the service on the control panel , error 2140 appears.…
Re: Out of buffer?
On Wed, 26 May 1999, Mark D. Stock wrote: > Xavier wrote: > > > > Hi, > > > > I got the following error message from an Informix IDS 7.30: > > > > <<Informix Dynamic Server>>> WARNING! Buffer pool size may cause > > Dynamic Server to get into > > a locked state…
Re: Out of buffer?
Xavier wrote: > > Hi, > > I got the following error message from an Informix IDS 7.30: > > <<Informix Dynamic Server>>> WARNING! Buffer pool size may cause > Dynamic Server to get into > a locked state. Recommended minimum buffer pool size is > 4 time…
Out of buffer?
Hi, I got the following error message from an Informix IDS 7.30: <<Informix Dynamic Server>>> WARNING! Buffer pool size may cause Dynamic Server to get into a locked state. Recommended minimum buffer pool size is 4 times maximum concurrent user thre…
Re: Performance problem with simple query
From: Willaert Doris <doris.willaert@suzuki.be> > >AIX 4.2.1 >Online 7.3 UD6 > >This weekend we converted our database from version 5 to version 7.3. We >have noticed the following problem trying to execute the following select : >The estimated cost is to high. (The select takes …
Performance problem with simple query
AIX 4.2.1 Online 7.3 UD6 This weekend we converted our database from version 5 to version 7.3. We have noticed the following problem trying to execute the following select : The estimated cost is to high. (The select takes 26 secs) We have run UPDATE STATISTICS !!! Number of ro…
Next Extend For Index
I have a large table with 22,000,000 records and 10 indexes. The indexes are very large. can I change the next extent for indexes like "ALTER TABLE... MODIFY NEXT SIZE". Thanks in advance.…
Linux VS NT4.0
Hi, Is it true that Informix Dynamic Server workgroup edition 7.30 is ported for Linux, and if it is so does anyone can tell me what to chose between Linux and NT4.0 for running Informix Dynamic Server workgroup edition 7.30. Also I'd like to know for which version of Linux, Inf…
SortMerge->HashJoin
Hello All, I have a query that on 5.0 is doing a 'sort merge', and 7.3 doing a 'dynamic hash join'. The 5.0 instance is about twice as fast. Is there a way for me to force 7.3 to do a Sort Merge? Also, I have a couple of off-topic questions: 1) If I have my physical and logica…
Re: RESIDENT and PDQ
Chuck Renaud wrote in message <7ddp91$hpf$1@news.xmission.com>... > >As for light scans--those only happen if the table being read is >larger than the buffer pool. So if you max out your buffers, you >won't get light scans unless they're huge tables... So, would this mean that …
Starting Informix Dynamic Server !
Hi Gurus, Just wondering is there any difference in starting the informix engine as root user versus as Informix user. ? The reason why I ask this question is , the IDS 7.23 UC6 on HP-UX 10.2 crashes due to a bug (declared as bug by Informix Technical support)when we start in…
Informix Dynamic Server Archive/Restore
Hello All: We have a large scale Informix DB resident on an HP 9000, K460, with AutoRaid Disk Subsystem (218gb). And we are running IDS 7.30. Currently, we are using the Informix ontape archive/restore utility to archive the DB and to perform continuous logical logging. We u…
Any one know this?
Hi folks: Is there a script that can check informix on-line dynamic server is up and running or not? If not it will e-mail a note to D.B.A. groups Thanks in advance Garry…
Using Views - Do they imfluence performance
FProse wrote: > > I'm Using v 7.3 on Aix 4.3. I've been asked to provice views that represent some rather complex queries; 8 tables with > outer joins. Will I see any performance imprevement in the use of a View vs. straight execution of the query? I've > tried to run both thr…
Delete
Hi. I've a question. When execute a delete by where on field not indexes, it sequential scan table' s page. Before to delete records, it casche pages in buffer pool ? Tank you.…
Re: Buffer Waits
> hmm, could you go into a bit detail on the light-scan? I have a DSS system > but I am not sure how to make it use light scan. I read something on the > book about it, but it wasn't very clear on exactly how to utilize it or > how to check it. thanks yan Here's some more inform…
converting Informix Online 5.1 esql/c to Dynamic Server esql/c?
Hi converting Informix Online 5.1 esql/c to Dynamic Server esql/c? What is the easiest way to do this? Is there a parser that could do this? edward…
RAID Technolgy
We are using Informix Online Dynamic Server 7.2. We are going in for a new DEC UNIX Server with RAID Technolgy. It incorporates mirroring at the hardware level. So is it not necessary to do mirroring using Informix?. Can anybody give suggestions regarding mirroring and other feat…
Informix on Linux
Does anybody know how much a copy of Informix Dynamic Server would run me for use on Linux?? Thanks in advance -steve…
Upgrade Dynamic Server 7.13 - 7.24
I need to upgrade Informix Dynamic Server version 7.13 to version 7.24. Can anyone advise of any problems they may have had whilst doing this?…
Any perfrmant solution for this type of Query ?
Hello, we are seeking a high performance query that allows us to retrieve rows by using in it two incompletely filled fields. Can you help us ? Here is the problem : we have an Informix table that contains the two following fields : field1 & field2. We would like to perform a q…
Informix Dynamic Server 7.31?
Is any information available regarding the release of IDS 7.31 for Unix? We have Sun E3000 and E4000 machines and are experiencing problems with both 7.30.UC3 and 7.30.UC5 and are curious about 7.31. Thanks. Carleen Avant Naval Safety Center Internet < cavant@safecen.navy.mil …
STRING FUNCTIONS
Are there functions for manipulating varchar type, like upper() or lower() in Informix Online Dynamic Server 7.2? Thanks in advance.…
DB performance : 45 seconds for a query ?
Hi, I have a frustrating question concerning Informix performance (on a UNIX system). The basic database structure is pretty simple : there is 1 type of table with 6 (significant) fields : - reference nr. - date - from - to - amount - currency There are 12 tables of this type …
Re: DB performance : 45 seconds for a query ?
Michel Dalle wrote: >Hi, > >I have a frustrating question concerning Informix performance (on a UNIX >system). > >The basic database structure is pretty simple : there is 1 type of table with >6 (significant) fields : >- reference nr. >- date >- from >- to >- amount >- currency >…
Netscape Enterprise Connection for Informix Dynamic Server
Hi: Does anyone know the procedure for setting up a Netscape Enterprise Server to connect to an Informix Dynamic server on a remote machine? Michael…
oninit: shared library problem
I'm having problems when initialising the DB using oninit. Config: INFORMIX Dynamic Server Ver 7.23, on a UNISYS-UNIX SVR4. When Initializing, I get the error ...dynamic linker: oninit: symbol not found: gettimeofday The LD_LIBRARY_PATH is set to $INFORMIXDIR/bin and �INFORMIXDIR…
Initi DB
I'm having problems when initialising the DB. Config: INFORMIX Dynamic Server Ver 7.23, on a UNISYS-UNIX SVR4. When Initializing, I get the error ...dynamic linker: oninit: symbol not found: gettimeofday The LD_LIBRARY_PATH is set to $INFORMIXDIR/bin and �INFORMIXDIR/esql help wo…
Index on Informix Online 7.22 ?
I have installed a new Informix Online Dynamic Server 7.22UC3 on our server (HP-UX 10.20). I find it is much much slower than the Informix 7.12 Online DS server if I do not create index for a same program. Why and what is the difference between the two version of Dynamic serv…
Re: 7.3 Performance with PeopleSoft application
Tim Jones wrote: [SNIP] > Since going to 7.3 the performance of the PeopleSoft databases have > improved in some cases but severely degraded in other cases. We have > also seen poor performance in our internal custom app which has been > fine for years in regards to some joins …
Re: esqlvers statement.....results are different on 2 DB servers ???
Tim Jones wrote: > > I've installed Informix 7.30UC3 with the ClientSDK 2.01 software on 2 > servers. Let's call them Server A and B. > > Both A and B had a prior ESQL/C versions. > > After the install, logged in as informix userid on A and ran "esqlvers" > and got this : > I…
esqlvers statement.....results are different on 2 DB servers ???
I've installed Informix 7.30UC3 with the ClientSDK 2.01 software on 2 servers. Let's call them Server A and B. Both A and B had a prior ESQL/C versions. After the install, logged in as informix userid on A and ran "esqlvers" and got this : INFORMIX-ESQL Version 7.22.UC1 …
Re: Bitmap Index in Informix ?
> Are there bitmap index in Informix or any thing that performs like it ? > > We are bilding a data warehouse with Online 7.23 8.2 has bitmap indexes.…
Informix Dynamic Server Personal Edition
What development apps are known to work with IDS-PE for 95? Infrmx wants to sell me licenses for Dynamic 4gl, esql-c, ddvb for 150usd. Do these work and are there others? 150 sounds ok but the catch is is that the Open-Line Support is outrageous for this (3K usd for unlim. suppor…
Will informix 7.24.uc5 compatiable with solaris 7?
Hi there, I was wondering if Informix Dynamic Server 7.24.uc.5 will be compatiable with solaris 7 when it comes out? Thanks advance, Pat Militzer pmilitzer@ultracom.com…
Informix Online Dynamic Server 7.2.2
When I try to iniailize the shared memory I get an error "oninit: Critical error initializing shared memory" I checked the log and it says the base memory address is 0x80080000 and should be 0x80400000 when I change it it reverses. this is on a SCO open server 5. nay ideas.…
Re: query performance
Hi Henk, how can we say that it makes sense to use an index, if we do not know how many rows will be returned by the query ? Normally it makes sense if the query will return 10 percent or less of the total rows. Additionally I would recommend to detach the indexes from the tabl…
[Fwd: query performance]
This is a multi-part message in MIME format. --------------27DEDA0AECB35305C8758837 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit problems to re-transmit message, forward will be ok , --------------27DEDA0AECB35305C8758837 Content-Type: message/rfc8…
query performance
Some people have already reacted, many thanks. To give some more information: SQLEXPLAIN shows that the index lst_date is taken. All other queries perform excellent, only this one is in trouble. To whom we do not really understand the problem: fst_date <= "1998-4-1" ...... L…
[Fwd: query performance]
This is a multi-part message in MIME format. --------------EA7489952CA6377365DF8168 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit --------------EA7489952CA6377365DF8168 Content-Type: message/rfc822 Content-Transfer-Encoding: 7bit Content-Disposition:…
Re: ODS v.7.3 on NCR
What is sub-query flattening with performance ?…
Re: ODS v.7.3 on NCR
Dave McNight wrote: > >[SNIP] Have you had any problems with the new > feature called sub-query flattening with performance? > Do you have any performance tips for this platform? Any suggestions > would be most appreciated:-)) Just a general comment on the correlated sub-query…
Informix Dynamic Server on Linux NOW!
Check out the Informix WEB Site. There is an announcement that IDS 7.3 is shipping on Linux TODAY! http://www.informix.com/ Art S. Kagel…
Problem installing to NT4.0 server
I'm trying to install Informix dynamic server workgroup edition to Primary domain Nt 4.0 Server. I have created Informix-Admin group and Informix account and grant it all Administrators rights and also Domain administrator rights. The server setup runs ok, but when setup is tryi…
Re: Best database for web backend on Linux?
On Wed, 21 Oct 1998 13:03:49 +0000, Evan Carew <ecarew@yahoo.com> wrote: >Simply put, The main reason I have selected Sybase over Oracle in the >recent past has to do with cache management. In every case, if >you try to use Oracle stored procedures in the same environment with >…
Peoplesoft V6/V7 and informix tuning
Have any admins fixed problems with Peoplesoft performance, I have spend 12 months tweaking indexes etc to get this package to work quickly on an 8-way IBM RS6000 and an 4-way Sequent. Is it just due to correlated sub-queries causing the problem??? 7.30UC3 sorts some of the major…
need performance help: avoid starvation of short queries by long-running queries
We are running Informix Online on a 4-processor HP machine. Our application uses primarily short update transactions and short queries, but some user requests can generate queries that retrieve thousands of records. Our problem is that each long-running query appears to (approx…
Deployment Licenses of Informix Dynamic Server
I need a free Deployment Licenses of Informix Dynamic Server!…
Re: Informix Dynamic Server blocks
In article <3609EB18.2AB1B1CD@bull.net>, Helmut Leininger <Helmut.Leininger@bull.net> writes >Hi, > >Sporadically, the Informix server seems to block (every try to connect >or onstat times out and no reaction on onmode -ky). In the MSGPATH I >find the following message: > >09:03:…
Informix Dynamic Server blocks
Hi, Sporadically, the Informix server seems to block (every try to connect or onstat times out and no reaction on onmode -ky). In the MSGPATH I find the following message: 09:03:42 mt.c, line 8362, thread 2139, proc id 17096, mt_notifyvp timed out. Informix server is 7.22.UC2…
Informix Dynamic Server blocks
Hi, Sporadically, the Informix server seems to block (every try to connect or onstat times out and no reaction on onmode -ky). In the MSGPATH I find the following message: 09:03:42 mt.c, line 8362, thread 2139, proc id 17096, mt_notifyvp timed out. Informix server is 7.22.UC2…
Re: Bug ? No rows returned from a view
Martin Brassard wrote: > > In the folowing problem, it seems that Informix doesn't return the same > data depending of the query plan it uses. > > We issue this query et don't get any data. [SNIP] > Then, if we issue that other query that is the same, but with a bigger > interval…
Re: Reducing checkpoint intervals...
Hi, in my opinion you must reduce LRU_MAX_DIRTY and LRU_MIN_DIRTY. It's not a good idea to reduce the time interval between the checkpoints because each checkpoint costs a lot of overhead ( thread-synchronisation and checkpoint-waits ). How about: http://www.weideneder.de/info…
Re: Performance problems with large SELECTS in 7.3x ?
Stefan Weideneder wrote: > > Hi, > > I tried to select some pages of a table that was a bit larger > than my buffer pool. I estimated that my statement will take > about 5 seconds but it took 12 seconds. > I found out, that the server spent approx. 7 seconds for > "buffer waits".…
Performance problems with large SELECTS in 7.3x ?
Hi, I tried to select some pages of a table that was a bit larger than my buffer pool. I estimated that my statement will take about 5 seconds but it took 12 seconds. I found out, that the server spent approx. 7 seconds for "buffer waits". Now I added a few buffers to the buffer…
RE: shell scripts
>I run an ISP, and we have user information stored in our Informix database. >hat I want to be able to set up is a script that will enable the user to log >n, give their username and password and a date range for their query, save >their username and the date range to environmen…
Re: OnLine Dynamic Server 7.2 Vs INFORMIX Dynamic Server 7.3
On Tue, 28 Jul 1998 14:40:44 -0500, Ben Jazi <bjazi@fallschurch.esys.com> wrote: >What are the major differences between INFORMIX-OnLine Dynamic Server >7.2 and INFORMIX Dynamic Server 7.3? Is it ORDBMS vs RDMBS? No, IDS 9.x is the ORDBMS. This autumn the 7.3 and 9.x code lin…
Re: OnLine Dynamic Server 7.2 Vs INFORMIX Dynamic Server 7.3
Ben Jazi wrote: > > What are the major differences between INFORMIX-OnLine Dynamic Server > 7.2 and INFORMIX Dynamic Server 7.3? Is it ORDBMS vs RDMBS? > > Thanks, > Ben I don't know the differences in detail but you could check the following pages, where IDS 7.3 is described …
Query performance monitoring
Hi all, I am currently trynig to determine a means of monitoring the performance of individual queries within ESQL/C programs. Does anyone know how I might best approach this? Are there any tools/utilities which might be incorporated into the code? Also, can Informix store quer…
Re: Threading ESQL - please help me
Silviamt wrote: > > I am currently getting involved with ESQL and Threads with Informix 7.2 > I am trying to create an application that may receive many simultaneous > queries. For efficiency I want to create a connection per thread with > 20 threads to perform upto 20 concurrent…
Re: Views
>Does anyone know the performance hit (or not) I may take by putting a datbase >that consists soley of views in a dbspace with another database soley >consisting of views? > > I'm not sure that I understand this. Are you wanting to create a database consisting of nothing but vi…
Threading ESQL - please help me
I am currently getting involved with ESQL and Threads with Informix 7.2 I am trying to create an application that may receive many simultaneous queries. For efficiency I want to create a connection per thread with 20 threads to perform upto 20 concurrent queries. Thus I do not wa…
OnLine Dynamic Server 7.2 Vs INFORMIX Dynamic Server 7.3
What are the major differences between INFORMIX-OnLine Dynamic Server 7.2 and INFORMIX Dynamic Server 7.3? Is it ORDBMS vs RDMBS? Thanks, Ben…
Index
Hi...Users..... I am trying to create an dynamic index in a SPL. The sequence is...I select a unique numeric value from a table & concat it with a string and store it in a variable. Now how do i use this variable to create the index. I could do the same using ESQL/C…
Informix computer Base classes
Has any body used the Informix computer base training classes. I am wondering if any of the computer based training classes: "Informix Dynamic Server System Administration" or the "Managing and Optimizing Informix Dynamic Server Databases" is good. Thank You…
Re: Informix Online Dynamic Server 7 on Linux
Piet le Roux wrote: > > If anyone has succeeded to run Informix ODS 7 (SCO) on Linux help would > be appreciated. > I have installed iBCS and the shared libraries and the binaries work, > except that the engine cannot initialize shared memory: Error message > semctl: errno 22…
Informix Online Dynamic Server 7 on Linux
If anyone has succeeded to run Informix ODS 7 (SCO) on Linux help would be appreciated. I have installed iBCS and the shared libraries and the binaries work, except that the engine cannot initialize shared memory: Error message semctl: errno 22 create_vp: cannot allocat m…
Problems with Informix Dynamic Server for NT
Hi Has anyone tried downloading and running the dynamic server product for NT? I have it installed, but get the following error when I boot the system: AdmAgent.exe - Applicatioin error The instruction at "0x10087c69 referenced memory at "0x00000000". The memory could not be r…
Vague Informix Error
We are using Rogue Wave DBTools to interact with the INFORMIX D-SQL server. As multiple queries are simultaneously performed (only 8). One of the queries returns an error -67 too many processes. This is very vague and we have not found anything that can be associated with that er…
Re: onstat -g ses
>Hi, >In the onstat -g ses output , what is the basis for allocation of 'total >memory' ? >There is also the figure 'used memory' , why not just allocate required >memory ? Well, we could do that but then that would 1) fragment memory and 2) create bottlenecks. Memory in shared…
Group by half-hour intervals?
Any clue as to how I might query a table containing a datetime field and perform statistical functions on rows grouped by half hour intervals? TIA! Roger Tomas AG Communication Systems…
optimizing search routines on large tables..??
I have a table that contains roughly 100,000 records. To perform a simple query on the table can take several minutes. I have been given the task of determining what search techniques, if any exists, can minimize the amount of time it takes to query sets of data from this table…
Bi-directional traversal
Does bi-directional traversal apply to composite indexes? Cheers, Tony…
RE: What are the differences between Informix Dynamic Server
It is confusing! =20 Here's what I know: Prior to renaming: 7.2x =3D ODS 8.0 =3D XPS 9.1 =3D IUS After renaming: 7.2x =3D IDS 8.0 =3D IDS/AD/XP 9.1 =3D IDS/UDO=20 After the 7.3 release: 7.3 =3D IDS 9.1 =3D IDS/UDO After the 8.2 release: 8.0 =3D…
RE: What are the differences between Informix Dynamic Server and
This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------ =_NextPart_001_01BDA3AF.9242A58C Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Good quest…
Re: Index for Informix Dynamic Server
William Lai wrote: > > Hello, > > We alreay upgrade our database server from SE5.X to IDS 7.3. After we > convert all the old data to the new server, we have some problems on the > index search. For example, a table: > > table : ith ( ithitno, ithname ), total 1,500,000 records…
Re: Index for Informix Dynamic Server
William Lai <rwie@netvigator.com> offerred: +We alreay upgrade our database server from SE5.X to IDS 7.3. After we +convert all the old data to the new server, we have some problems on the +index search. For example, a table: + +table : ith ( ithitno, ithname ), total 1,500,000…
Index for Informix Dynamic Server
Hello, We alreay upgrade our database server from SE5.X to IDS 7.3. After we convert all the old data to the new server, we have some problems on the index search. For example, a table: table : ith ( ithitno, ithname ), total 1,500,000 records index : ith_1 on ithitno, ithn…
Informix Dynamic Server 7.3
Hello, Since I'm planning a Data Warehouse, I would to do it with the new version of Dynamic Server. Impressions, stability and on which platform? Regards. Fernando _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Fernando Collalti Phone: +…
renaming an index
Does anyone know of a way to rename an index ?…
Re: Performance Question on queries per second
On 7 May 1998 02:41:54 GMT, "Pat Militzer" <pmilitzer@ultracom.com> wrote: >Hi there, > >Does Informix have any rating how many queries per minute that its database >can do? What is a "query"??? >Specifically we have Informix 7.23 Dynamic Online Server running on a Sun >UltraE…
Re: Performance Question on queries per second
In article <6ir71i$g1d@newsops.execpc.com>, Pat Militzer <pmilitzer@ultracom.com> writes >Hi there, > >Does Informix have any rating how many queries per minute that its database >can do? > >Specifically we have Informix 7.23 Dynamic Online Server running on a Sun >UltraEnterpris…
Performance Question on queries per second
Hi there, Does Informix have any rating how many queries per minute that its database can do? Specifically we have Informix 7.23 Dynamic Online Server running on a Sun UltraEnterprise 4000 with 1.2gig of memory and 4 250mhz Sparc processors. The vendor we have states the syst…
Informx-SE (7.13) Basic DBA question
If I issue : CREATE INDEX INDEX_NAME ON TABLE_NAME(COL_NAME) command, would this be a Clustered Index or Non-Clustered ?? TIA -asim…
Informix Dynamic Server & Text columns
Hi everybody, Can you help me ? I've created a table with the following structure : column type -------- ---- code smallint obs text How can I insert into this table with Java program ? I'm using Informix Java AP…
Index on float field ?
Can an index be created on a float field in Informix, or is it not advisable ? Dirk…
Intermittent Problem - error 529 Cannot attach to transaction
We have a HP-UX box (Ver 10.20) running Informix Online Dynamic Server Ver 7.13 and a NT Server (NT4.0) running Informix Online Dynamic Server Ver 7.23. We are trying to access some of the database tables resided in the HP-UX box from the NT Server via synonyms. But we faced inte…
Re: How is disk space allocated to Indexes?
apatankar@s1.com wrote: > > My questions are as follows: > Assume that you create a table and allocate it an initial extent of some size. > Then you create an index on that table in the same dbspace. Then you add rows > to the table, so that nodes get created in the index. Is the…
DRDA (Informix <-> DB2 mainframe) Performance Q.
Hi all, I've got DRDA version 6.1 running under AIX on an sp2 node and pointing to ou.r DB2 mainframe db The problem is that queries running on the mainframe take under a minute, while the same query running under Informix(to hit thouse mainframe tables) take 20 mins and more…
How can I read the header tape of the archive created by ontape?
I have Informix Dynamic Server 7.23.UC1. I would like know the tape header structure created by ontape.…
Re: Informix indexes
>I have to decide to mirror the indexes yes or no. If I don't mirror >the indexes then I can use two disks for the indexes to increase the >performance. > >The thought behind is that if a disk with indexes chrashes then it >is simple to regenerate > >the indexes in another …
Informix indexes
<HTML> I have to decide to mirror the indexes yes or no. If I don't mirror the indexes then I can use two disks for the indexes to increase the performance. <BR>The thought behind is that if a disk with indexes chrashes then it is simple to regenerate <BR>the indexes in ano…
Re: Fragments vs location of tables
"Irwin Goldstein" <irwinNOSPAM@NOSPAMobjectsoft.comNOSPAM> writes: >When doing round-robin, the indexes do NOT get fragmented along with >the table. This would make index lookups very inefficient. Round robin >fragmentation cannot be used for an index. I will create an unique…
Re: Upgrading Informix from Online engine to Dynamic Server
Seth Grimes wrote: > > Jane Vohden wrote: > > > > I have been talking with Informix about upgrading our site from the > > 5.xx Online engine to 7.2x Dynamic Server. They said there was no cost > > I don't think that anyone has pointed out the mistaken terminology > h…
Re: Question: Performance of a view as opposed to a table
> What's the performance hit on selecting, deleting, inserting, etc. >on a view compared to working with the table directly? This question has >to do with views with no operation (e.g.: columnA - columnB) on columns, >just straight selection. In 7.23 + the query against a view …
What's the difference between ODS 7.22 and 7.23?
What's the difference between Informix Online Dynamic Server version 7.22 and version 7.23? Thanks in advance. Bill Westphal Rodney District Council mis@rodney.govt.nz…
ODBC unix and dynamic SQL
Hi, Can someone give me some help on using informix odbc drivers (unix) with dynamic SQL on Informix-online dynamic server (7.22 unix) when I use "SQLprepare" I receive the message "too many or too few host variables given" but I think all is Ok R. Gueye…
DS_TOTAL_MEMORY & performance questions
Question. Does the setting of DS_TOTAL_MEMORY affect ALL queries, or only queries running as a PDQ ( with the pdqpriority explicitly set for that session ). Also, if I have a very large query, and my DS_TOTAL_MEMORY has been set to something lower than what the query needs, w…
Re: Query performance
Alain Martin wrote: > > Hi > > I have a simple query that takes about 5 minutes to display is result. The > problem is the order by in the query. It use a temporary file. Whitout it, > it`s a lightning. Did you 'update statistics' for the table? The optimizer must have figured t…
Query performance
Hi I have a simple query that takes about 5 minutes to display is result. The problem is the order by in the query. It use a temporary file. Whitout it, it`s a lightning. Look in attachment file sqlexpl.out, it containts the query with is query plan. I don't understand why the…
Re: distinct function syntax question
Try using this : select 1 from table_x group by col1,col2 into temp temp_table; select count(*) from temp table; This could be quite fast, depending on an index on col1,col2 and the size of the table. Ps. Don't forget that the temp table will exist until your session ends o…
distinct function syntax question
I'd like to perform the query: "select count (distinct col1, col2)" but I get a syntax error. I also get a syntax error from "select count (distinct col1 || col2)". How do I perform such a query? I am able to perform the query using the second syntax in Sybase but I'm guessing…
Query performance: 16 secs vs 5+ hrs
<HTML> I can run the following query against a table with one minor (!?!) change and vary the return times from 5+ hours to <16 secs: <P>select field1, field2, field3, field4, field5, field6, field7 from "dba".table where field2 < "number" and field1 >= "date" order by fie…
Re: Puzzling ODBC Error ...
Another nice feature of MS-ACCES 2 is that it freaks out on indexes wich are not names. When you issue a create index statement, informix generates a name that starts with a blank. Give your indexes a name! Koen…
Re: detached index vs attached index
> > Is there any easy way to detach indexes in OnLine 5.x and upwards? > I don't think detached indexes were available prior to 7.1x (well, maybe 6.x). Anyway, the only way is to drop the existing index (if there is one) and then CREATE INDEX ... IN <dbspace>. Is that easy? Ma…
detached index vs attached index
Greeting... Can anyone comment on detached index vs attached index? Thanks in advance! Andy Chang…
Re: Q: is DS 7.x using index?
georget wrote: > > Query looks like: > > SELECT * from table WHERE fld > N > > fld is UNIQUE indexed. > > Pls, help, because performance is dead slow. You can check this yourself. Just "SET EXPLAIN ON;" before running the query then run your query. After it runs look in t…
Next Extent Calc.
Hi, I have INFORMIX-OnLine Dynamic Server Version 7.14.UD2. Does anyone know of a script or program to calculate the next extent size ? Thanks in advance Manuel…
Q: is DS 7.x using index?
Query looks like: SELECT * from table WHERE fld > N fld is UNIQUE indexed. Pls, help, because performance is dead slow.…
Java and Informix Dynamic Server 7.x
Hi friends! I am trying to do server-side java cgi programming to connect to I-ODS 7.x. Do you know how I can do that? I heard it is possible to use the jdbc-odbc bridge( intersolve or openlink's), but the performance is too slow, because of jdbc to odbc and odbc to jdbc convert…
Re: Detached Index
There are two main advantages to seperating the indexes from the data chunks that I can think of. 1) By placing the data on seperate physical devices from the indexes, you might be able to get a performance boost by avoiding some potential disk contention. 2) Since on attache…
Indexes
If I have following table: table A customer char(10), inv_num int, bill_data date, ship_date date, bill_amt real, print_flag char(1) cust_index on (customer) cust_index2 on (customer, inv…
ODBC and Tuxedo
I want to perform some simple queries on a database in Informix 7.20. The database is designed and used by an app written for Tuxedo also running on the HP/Unix server. Will using ODBC interfere with Tuxedo? Thanks, Jeff Stevens…
root dbspace
I have a test machine running Online 7.1. On performing a VERY large query I received an error message saying that there was no free disk space to write sorted rows into a temporary table. On examining the space available in the rootdbs 32252 pages remained out of 575000. I sh…
Re: when to 'update statistics' ???
The standard reply to this question is when your query performance is degraded. If you wish to be a proactive administrator and you have off hours to do update statistics then by all means put a script in cron and run against the tables that are have record manipulation.…
Re: Bit-Mapped Indexing Routines---Beta Testers Wanted
I thought it was "bitWISE" indexing not "bitmapped" indexing. Please correct me on this. Bill Gfroehrer...…
Re: Online V7 and Parallel Queries
> > Is it true that PDQ, parallel queries in OnLine V7.x will use both > primary chunks and mirror chunks to retrieve data concurrently when > performing a query expected to return large volumes of data??? > It is true Michael Strother…
To index or not to index ? that is the (constraint) question.
Suppose you have a master table and a detail table. And let's suppose you have a unique constraint on the primary key of the master table and a foreign key constraint on the detail table, corresponding to this primary key. I may not have explained it very well, but the idea is …
Is Informix SE best than Informix DSA?
We have Informix DSA 7.12, we not get performance with it. Simples queries not get the same or best performance with DSA than with STANDARD-ENGINE when we processed a complex query of bigs tables. The machine is paralyze. If theres anybody who want to talk about it. Thank.…
java and informix version 5.06
HELP I have a pc running windows95 which has the jdk 1.0 installed on it , I would like to perform simle(at first) queries on an Online Engine running on a HP platform under the 10.10 operating system any clues ?…
Indexing issues, help
Hi, Can anyone clear me up about index, clustered index. 1. What is the best timing to use clustered index? 2. index A and index B has the same indexing columns but different order, such as (a, b, c) and (c, b, a), where a, b, c are columns. Will ithe proformance make d…
Re: Query using Union: performance
In article <Dvwr72.LAt@eskimo.com>, cseoanem@wport.com (Carlos Seoane) wrote: >Hi, >I want to retrieve information from two identical tables, one is >current info and the other one has historical information. I am doing >a query of both tables using a UNION, with two identical SE…
Re: Query using Union: performance
cseoanem@wport.com (Carlos Seoane) wrote: :Hi, :I want to retrieve information from two identical tables, one is :current info and the other one has historical information. I am doing :a query of both tables using a UNION, with two identical SELECT, but :refering each one to a d…
Re: Query using Union: performance
Carlos Seoane wrote: > > Hi, > I want to retrieve information from two identical tables, one is > current info and the other one has historical information. I am doing > a query of both tables using a UNION, with two identical SELECT, but > refering each one to a different table.…
Query using Union: performance
Hi, I want to retrieve information from two identical tables, one is current info and the other one has historical information. I am doing a query of both tables using a UNION, with two identical SELECT, but refering each one to a different table. My question is: what about the p…
Informix Certified Professional Program Update
Informix offers two certification paths. Each path requires the successful completion of two exams. Each exam is US $250 and is between 60-90 minutes long. * Database Specialist for INFORMIX-OnLine Dynamic Server 7.x 111 - Database Fundamentals Exam 112 - Managin…
Re: Query Performance Problem SE 5.06.UC1
> mickey.mestel@etak.com (Mickey Mestel) writes: > check the OPTCOMPIND parameter in your onconfig file. A little hard to check when the engine is SE 5.x :-) Nils.Myklebust@ccmail.telemax.no NM Data AS, Postbox 9090, Gronland, 0133 Oslo, Norway My opinions are those of my c…
Re: Query Performance Problem SE 5.06.UC1
tolis, } is facing following problem: } The optimizer doesn't select the appropriate index and the response time } varies from seconds to 30 minutes as also the no of rows found in the same } SQL statement. } The update statistics statement has been run. } The index is checked w…
Re: Query Performance Problem SE 5.06.UC1
On May 10, 4:13pm, Apostolos Varnas wrote: } Subject: Query Performance Problem SE 5.06.UC1 } One of our clients with following environment: } Hardware: Olivetti SNX 400 with RAID } OS: UnixWare 2.01 } INFORMIX SE 5.06.UC1 } ISQL 4.14.UC1 } } is facing following pro…
Query Performance Problem SE 5.06.UC1
One of our clients with following environment: Hardware: Olivetti SNX 400 with RAID OS: UnixWare 2.01 INFORMIX SE 5.06.UC1 ISQL 4.14.UC1 is facing following problem: The optimizer doesn't select the appropriate index and the response time varies from seconds to 30 m…
Re: Deleting Question
drop the index before the delete, then recreate it afterwards Sean Bradley…
Indexes....
Hello Everybody: My problem is like follow: I have a table with many thousands of rows, and that has indexes, if I drop the indexes and then re-create them, I get the following error: -103 ISAM error: illegal key descriptor (too many parts or too long). The ISAM proc…
Hopeful No-brainer !!!
If you select from a table (with or without indexes) and don't include an order by clause, what is the default order of the rows returned? Is this order affected by the presece or absence of indexes?…
Comparison of Database Vendors Reporting Tools
Hello: I need some information on various pros and cons of query and reporting tools offered by Informix, Oracle, Sybase (PowerSoft) and Progress. I have been chartered with evaluating the reporting and query segments of application development suites, and am wondering if anyo…
Re: MS Access -> ODBC -> OnLine 5.0
It is a nuisance init. We sidestepped the problem by using query aliasing. There doesn't seem to be any performance overhead for this. Aliasing also gives the option of extending the 18 char column-name limit of Informix should one wish to. Hope this helps Best Regards -- Ti…
Re: Good Buffer Size For Tuning Informix?
Chris Jenkins (chrisj@informix.com) wrote: : The only really important factor in determining the number of buffers is : the read and write cache hit ratios reported by tbstat -p. Keep : increasing the number of buffers until these cache hit rates do not : increase when the system…
Indexes
Hi: What problems, if any, on performance, storage etc. one may face with an index on a field which can take NULL. Let us assume that the field is an int or a char type. Thanks, -ajay -- Ajay Dankar (ajay@ios.ho.att.com) (908 949-6513 (V)) (908 949-8415 (F))…
Re: Extents and query performance
> Recently I was adjusting a performance test, trying to keep it in a > reasonable amount of time (for a test) and at a time one of the querys > started to take much less time than before. The people that were doing the > test told me that the only change made was in the extent…
Extents and query performance
Recently I was adjusting a performance test, trying to keep it in a reasonable amount of time (for a test) and at a time one of the querys started to take much less time than before. The people that were doing the test told me that the only change made was in the extent size o…
INDEX
Hello again people, I have many columns that had about 500 or more rows, so I would like o create a index by every one. How to create index in informix.4gl?? Thanks in advanced German from Mexico…
PERFORM and fragmentation
I am trying to query a table in 7.01 using PERFORM in isql 4.10. When I try to generate a form a use it for a query, I receive the following errors: Rowids do not exist on the table The cursor or statement is not available Is this error caused by fragmenting the table across …
Re: Problem with coltype in syscolumns
Puru Balakrishnan (pbalakrishnan@symantec.com) wrote: : This query performed on db-access returns the correct results as: : colname coltype collength : field1 256 10 : field2 …
Re: INFORMIX-Online Dynamic Server intallation
copy the standard onconfig file and make as few changes as possible to the different parameters. after you are sure you can bring informix online, should you begin to make changes to the onconfig parameters. Use ONMONITOR to make the changes. It will protect you from many mista…
INFORMIX-Online Dynamic Server intallation
I'm about to install INFORMIX-Online Dynamic Server V.7.1 on a SPARC 1000. Has anyone installed this database on a sparc before? If yes...do you have any suggestions or could you give me any advice on certain aspects of the installation you felt might be important to take notice?…
Is there such a bird?
I need a relational database that can be queried through the web. It needs free text and boolean searching capabilities. It must be able to incorporate new fields as they develop. It must produce usable output; it should be able to generate web pages. The cost and performance sho…
Re: Performance
In article <3kt1jh$hui@dodge.eng.sc.rolm.com>, Peter Levine <peterl@minerva.robadome.com> wrote: >Thanks Helen and Jack for the quick response. > >I restructured the query and have considerably >improved its performance. Query time has gone from over 8 minutes to less >than 8 sec…
Re: Keeping clean pages in shared memory
> If you have a DSS app, the buffer pool does not become dirty, so the > LRU_MAX_DIRTY will not have any impact. ...unless of course you're generating temp tables. Which just about all apps do at some time or another. akent@cix.compulink.co.uk (Andy Kent) ----------------------…
Re: Optimizing Many-Table Joins
Michael Mueller RD writes: -> ->Can anyone tell me the tactics that the query optimizer uses for ->optimizing joins, especially queries joining a large number (e.g. >4) ->of tables? What is the max number of tables allowed in a query? ->How does this compare to Sybase's handling…
Re: DUPLICATE KEYS
I have often found that changing the index to use the highly duplicate field as the first field of a 2 field composite index is useful. It will still use the index to access the field but the presence of the second field in the index reduces the duplication. Malcolm Weallans On…
Re: Predefined queries in a PERFORM screen?
Dave Clough (clough@hyster.rain.com) wrote: : Is there a way to send a string of characters to a PERFORM screen that : cause it to query up a record (or add, remove, whatever)? I've tried : redirecting the standard input from a string i.e., : isql <database> -fr <perform_scree…
Predefined queries in a PERFORM screen?
Is there a way to send a string of characters to a PERFORM screen that cause it to query up a record (or add, remove, whatever)? I've tried redirecting the standard input from a string i.e., isql <database> -fr <perform_screen> << ! q12<ESC> ! -OR- sperform <perform_screen> <…
5.01 SE - Q: How to alter indexes like ' 152_56' to cluster
Hi, trying to improve performance of some queries, we are looking for a possibility to alter indexes, which are resulting from primary and foreign key constraints and having names like ' 152_56', to cluster. Any ideas? Martin Berns (martin.berns@materna.de)…
Re: query plan
Diane Lombardi in Beach Lab (lombardi@cshl.org) wrote: : I am trying to improve the performance of a particular : query. I am using Sybase sql server v4.9.2. : I noticed that the first time I execute the query : the performance is a little slow. The second time : I execut…
Re: query plan
In <2thui0INN84u@phage.cshl.org> lombardi@cshl.org (Diane Lombardi in Beach Lab) writes: | I am trying to improve the performance of a particular | query. I am using Sybase sql server v4.9.2. | I noticed that the first time I execute the query | the performance is a little s…
query plan
I am trying to improve the performance of a particular query. I am using Sybase sql server v4.9.2. I noticed that the first time I execute the query the performance is a little slow. The second time I execute the same query, with different search values, the performance i…
Indexing
Hello all wizards, Could someone tell me how Informix pick and choose which index to use if I have a table with 2 indexes as following: table t_a ( f_1 integer, f_2 integer, f_3 integer, ..... ) index i_1(f_1, f_2) index i_2(f_3) and the where …
Slow viewpoint query
I have a question regarding general speed of queries using Viewpoint. My customer performs a query on their Sun Sparc 10 using ISQL to a local online database and it takes 3 minutes. They perform the same query using Microsoft Quick Access on DOS, to the same database on the SU…
SQL Query Optmizer
The following two queries QUERY 1 and QUERY 2 are the output of SET EXPLAIN ON. I do not understand why the query optimizer does a sequential scan on root.alujobsh in QUERY 2. The only difference in the two queries are these lines: QUERY 1: atkliquh.inv_num = '12316' and QUER…
Query Optimizer
The following two queries QUERY 1 and QUERY 2 are the output of SET EXPLAIN ON. I do not understand why the query optimizer does a sequential scan on root.alujobsh in QUERY 2. The only difference in the two queries are these lines: QUERY 1: atkliquh.inv_num = '12316' and QUER…
Grouping queries in Wingz
How does one perform a "group by" query using the Datalink feature in Wingz without resorting to the Direct Entry (SQL) box? RTFM is returning null. :) Thanks. -- Jason Foster-Bey (jasonfb@panix.com) Senior Technical Support Analyst National Multiple Sclerosis Society New York, …
Performance of Uniface with Informix Online
The company, where i work, is thinking about switching to a application build in Uniface with Informix Online as the database-server. One of the questions is the performance of Uniface compared to the same query that is runned on the database-server itself. I already read some m…
Query on Views
A query on views : What is the performance overhead on using views ? I have heard of a couple of problems where using views changes the optimizer's approach to queries - is this a general problem or isolated bug(s) ? Thanks in advance, Richard Ridley DHL Asia Pacific…
Re: Query Performance through Views
In <CG8wLt.Myy@vcd.hp.com> kenm@vcd.hp.com (Ken Miles) writes: >It appears that Informix Online 5.00 makes some rather mindless assumptions >about query optimization under the "create view" statement. It essentially >ignores any indexes it might be able to use based upon what c…
Query Performance through Views
It appears that Informix Online 5.00 makes some rather mindless assumptions about query optimization under the "create view" statement. It essentially ignores any indexes it might be able to use based upon what columns are selected when accessing the view. With "set explain on"…
informix
We are in need of Informix expertise in two areas: 1) query optimization and the use of indexes, and 2) performance tuning using self-joins and outer joins. 1) We have a query that should use an index, but doesn't. When a second index is added to the table, the query now re…
Unique Indexes
I have a table in which there's a unique index created, and it involves 9 columns. I'm having a problem when I goto $select from it in one of my *.ec files. The sqlca.sqlcode returned is 100 (SQLNOTFOUND). When I looked it up in the ESQL manual, the ISAM error reads: "there is …
Unique Indexes
I have a table in which there's a unique index created, and it involves 9 columns. I'm having a problem when I goto $select from it in one of my *.ec files. The sqlca.sqlcode returned is 100 (SQLNOTFOUND). When I looked it up in the ESQL manual, the ISAM error reads: "there is …
Indexing strategy
Hi Informix Gurus, this question may well be a FAQ, but I'm rather new to this group and haven't read about it before. BTW: Is there a FAQ somewhere? I have several large tables under ONLINE 4.0 (soon to be upgraded to 4.1), whose structure is similar to: patient_n…
index selection
i think this may have been posted previously, but here it goes. how does informix sql determine which index to use when executing a query? the problem we are having is we have a table that has several indexs which include a given column (lets say column_001); all but one are com…
Corrupted Index
Hi, We use OnLine 4.00 on SCO box. Using tbcheck -cI we found that one of the index was bad. My options are to: 1. Let tbcheck repair the index 2. Drop the index and recreate it What are the tradeoffs involved in deciding between the two ? Thanks in advance for the re…
Form Command Options
is there any way to rearrange the command options (add, query, update, exit, etc.) in perform? specifically i would like to rearrage the options so that 'add' option comes first, then 'query', 'update', and 'exit' in that order. thanx in advance. -- ----------------------------…
Index Size
Hi netter, Does anyone know exactly how to calculate the total index size of a particular table like you see when you do dbschema. According to the Informix Training 'Book', add 8 bytes to the length of each key, and then sum up all index entries, this should be the total index …
systable indexes
Quoting Dave Snyder... > Quoting Nick Rumble... >> I have to perform frequent validation against the tabname >> column within systables. This column is *not* indexed and >> therefore can be quite costly if a database has a large number >> of tables (200+). > Someone correct me…
Cluster index or not ?
Cluster index or not ??? Do any of you know when I may use a cluster index ? I have a 400Mbyte table with about 400000 rows in an Informix Online system version 4.1. How often may I recluster the index (alter...). Deletes will be v…
Rebuilding Indexes
Path: emory!europa.asd.contel.com!uunet!psinntp!nynexst.com!matrix From: matrix@nynnexst.com (Howard Greenblatt) Newsgroups: comp.databases.informix Message-ID: <1991Nov11.193952.16934@nynexst.com> Date: 11 Nov 91 19:39:52 GMT Sender: news@nynexst.com (For News purposes) Reply-To…
Informix Indexes
Hello, I have a question regarding a limitation on indexes that I read in the INFORMIX-4GL 4.0 Users Guide. The text from the guide is a follows... . Do not index columns that contain a large number of duplicate values, such as Y (for yes) or N (for no). Here …