Oninit® Archive
Results for “OPTCOMPIND”
Query using temp table is much faster then join/subquery
Thanks for suggestion. I tried various settings , in sql: SET ENVIRONMENT OPTCOMPIND '0/1/2'; SET OPTIMIZATION HIGH/LOW; (total 7) I tried with : onmode -wf OPTCOMPIND=0 I run with different settings for "update statistics high/medium/low " I always get same time (50s+-3s) Execut…
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 ------------------------------…
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…
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…
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.…
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…
Performance loss for a query after 11.50 >12.10 up
Thanks Cesar! unfortunately no functional index to remote queries involved here only 15 LEFT joins written the 'Oracle way' (just to help...) If I were the Informix optimizer, I would be lost in such a query *lol* Trying to find out where the engine is spending time...…
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)…
RSS/HDT Sec - Different Query Plans???
In particular OPTCOMPIND?…
RSS not working
-bash-4.2$ onstat - IBM Informix Dynamic Server Version 11.50.FC9 -- On-Line --…
Informix upgrade
Can we upgrade informix from Dynamic Server Version 11.70 to 12.1 workgroup edition directly?…
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.…
Poorly Optimized SQL
So here's an interesting little nugget that I just realized: The Explain Plan doesn't always print out the "Query Statistics" portion. The plan of tables and joins is printed, but not the stats breakdown. This section prints a long time after the query is started, so if I kill th…
optimizer not working properly
What is the isolation level that you're using? That should be a bug.... I recall something similar on 11.50.... On Jul 8, 2013 11:59 AM, "JUAN ROCA" <jluis.roca@hotmail.com> wrote: > The solution: > > OPTCOMPIND 0 ##-- Works fine. USES INDEX > > OPTCOMPIND 2 ##-- Uses SEQUENTIAL…
DB_locale or local naming convention
Hi, I already mention the informix version. IBM Informix Dynamic Server Version 11.70.TC1IE…
Physical log size
Art, Thanks for the info. We do have enough buffer cache to effectively buffer most of what will be active at any point, so it really does come down to monitoring the various checkpoint-related stats. It was just really confusing that the message indicated that it would be fas…
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,…
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…
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 …
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…
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.…
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…
limit on query length in Informix IDS 11.50
Is there a limit on query length in Informix IDS 11.50? If there is one, can it be increased? Also can anyone suggest some information on how to optimize IDS 11.50 for longer queries to make them run faster? We have some Cognos reports that send very long queries to IDS 11.50 …
Query works fine in IDS 10 but crashes 11.5 engine
Jonathon wrote: I'd post what the optimizer has to say, but sadly it never gets that far. It prints out the last query right before this one (which completes successfully) and then the oninit process hangs and the optimizer information for the query in question never gets printed…
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…
Re: Query-Optimizer on HDR-Secondary
Hi folks, I talked to my informix-supporter: Rebuilding the hdr-mirror solved the problem. Regards Markus Bschorer "Markus Bschorer" <mb@worxbox.com> schrieb im Newsbeitrag news:4bd44874$0$6892$9b4e6d93@newsspool2.arcor-online.net... > Hi Art, > > thanks a lot for your answers…
Query-Optimizer on HDR-Secondary
IDS 11.5 FC4 Linux 64 Bit Hi everybody, it seems, that the IDS-Optimizer works different on primary and (hdr-)secondary servers, having identical IDS-Versions. Example: create table wbx_permission ( per_object_id char(20), per_client_id char(10), per_user_type i…
ids query optimizer.
As I understand it, most query optimizers are cost-based. Some can be influenced by hints like FIRST_ROWS(). Others are tailored for OLAP. Is it possible to know more detailed logic about how Informix IDS and SE's optimizers decide what's the best route for processing a query, ot…
Doubt in Update statistics, resolution and confide
Hi Celso, Just for better understand , what update exactly have the performance problem? med ,high or both? what values of resolution/confidence used? The PDQ make all differ with update statistics high, use more memory and they intend to use light scan and avoid the indexes...…
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…
Re: OPTCOMPIND and perl
On 11 Aug, 23:25, Nog <n...@gfm.co.uk> wrote: > On Aug 11, 10:45 pm, Obnoxio The Clown <obno...@serendipita.com> > wrote: > > > > > Nog wrote: > > > Anyone know if I can change OPTCOMPIND after establishing a DB > > > connection or does it need to be set before connecting? > > > …
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…
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 …
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…
move data between 2 instance
Try adjusting OPTCOMPIND. I noticed a significant difference in the way this parameter is utilized starting with IDS 11.1.…
Re: On performance using views
you have to run upd stats for your procedures/routines. assume that OPTCOMPIND = 0 in onconfig and not set in env. what does sqexplain tell you??? Superboer. On 5 nov, 23:29, Omar Muñoz <omar...@yahoo.com> wrote: > I had tried setting OPTCOMPIND environment variable to zero…
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…
Re: Please help optimize a query:
First cut: 1. Update statistics to the recommended levels (see the Performance Guide and John Miller III's white paper) 2. Save SET EXPLAIN output for this original query and all subsequent attempts to optimize it. 3. Why are you forcing the use of a particular index? Let th…
Please help optimize a query:
//QUERY IS AS FOLLOWS: select --+index (s ix_sr2) s.trandt ,sub_min as min ,s.s_class ,s.status ,s.active_dt ,s.exp_dt ,s.canc_dt from status_records s where s.canc_dt >= '08/04/2008' and s.canc_dt < '09/04/2008' and s.canc_dt = (select --+index(y ix_sr2) max(canc_dt)…
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…
Informix external directives ?
Dirk Gunsthï¿œvel schrieb: > Hi, > > first you should be able to set the optimizer goal per session using > SET OPTIMIZATION FIRST_ROWS > > secondly how do you see that the optimizer hints (that is what you > mean, right?) are not used? Do you have any helpfull output? > > (I h…
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.…
Caching Data to Buffers
Regarding Update Stats - short answer It synchs meta data with your real data, providing the SQL optimizer with the best possible query plan(s). MM…
RE: drop distributions
Problem may be btree cleaner threads, not the drop distributions. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Habichtsberg, Reinhard Sent: Tuesday, Ma…
drop distributions
Hi. After upgrading from IDS 9.40.FC4W2 to 9.40.FC9W2 some queries run extremly slow and I'm under the impression that the optimizer chooses the wrong way especially when OPTCOMPIND is 1 or 2. My idea is to drop distributions. The problem is "update statistics low on table xxx …
Re: unkillable sid's
On Feb 29, 4:58 pm, Darren_Jac...@carmax.com wrote: > Here is the stack from the onmode -X: > > 17:51:37 Stack for thread: 309 sqlexec > > base: 0xc000000249ffd000 > len: 69632 > pc: 0x0000000000000000 > tos: 0xc000000249ffecd0 > state: running > vp: 3 > > ( 0) 0x4…
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…
4gl/esql syntax
On Wed, 2007-08-08 at 13:35 -0400, sergio kapul wrote: > looking for correct syntax to create directives inside > queries to overwrite optimizer query execution plan, > to be used in 4gl/esql, I got something that works in straight sql, but it's > ignored when I put it inside of …
IIUG TV
Great job on the video work!! I look forward to seeing more! -t- -- -- Informix Dynamic Server Database Engine -- http://www.informix.com --…
IDS Optimizer Query Plan, declaring and naming indexes
I'm seeing differences in performance for the same SQL being executed on two slightly different databases. Using Set Explain, the difference is when database1 exectues part of the WHERE, it goes down the Index Path for that particular table and it's fast. On database2, the query …
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…
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…
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…
Re: Update Statistics Guidelines
> Sometimes not even this has been enough as we have one frequently-used > transient table that can contain anything from 0 to 50000 rows. If > update statistics was last ran when there were 0 rows the engine favours > sequential scans which are very inefficient when the table ge…
Re: very bad behavior
tomcaml@gmail.com wrote: > Hi all, > We finished a major migration a few weeks ago but the new system is > really misbehaving. Any thoughts are appreciated. Here is the low down. > The new dev system behaves fine but not on primary and secondary. We > have tried tuning many diffe…
Re: query optimize
Devaraj Takhellambam said: > Thanks for replying...The query has been working well and takes just about > 20-30 minutes in the past but recently it has been degraded and took about > 3-4 hours and sometimes 5 hour..so i thought it needs to be > optimized....Thanks.. OK, so how a…
query optimize
Hi, How can I optimize the informix query below- the query uses an OUTER join... select col1, col2, '' col3, col4, A.col5, A.col6, count(*) cout from table A, table B, table C, OUTER table D where A.col9 = B.col9 and A.col7 = D.col7 and A.col8 = 'Y' and …
Re: Let me be the first
Lukas Barton wrote: > I mean I'm facing the problem described on: > > http://www-1.ibm.com/support/docview.wss?rs=0&context=SSGU5D&context=SSHMMC&context=SSGU8G&context=SSGKNY&context=SSGU5Y&context=SSCRW7&context=SSGHZP&context=SSVT2J&q1=ISAM+error%3a+record+is+locked.&uid=swg21…
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…
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…
Re: Sequential Scan on TEMP table
A reader e-mailed me with a suggestion that worked. Our OPTCOMPIND was set to 2 on our test system. I changed it to 0 and the performance problem cleared up. Weird thing was this setting was 2 on our old test system running Informix 7.3 and we didn't have a performance problem…
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…
Re: OPTCOMPIND
tomcaml@yahoo.com wrote: > Hi all, > >>From a post by Superboer. : > > OPTCOMPIND 2 # To hint the optimizer > > for oltp do not set it to 2 set it to 0 instead!!! Basically - when we implemented hash joins, we had issues with the optimizer costing of hash joi…
OPTCOMPIND
Hi all, >From a post by Superboer. : OPTCOMPIND 2 # To hint the optimizer for oltp do not set it to 2 set it to 0 instead!!! Superboer. =================================== We currently have it set to 2 and very much an OLTP system - can anyone shed light,…
Re: Select Query hangs up on IDS 9.40.
> > It _might_ be. Probably not, but running the new one is worth a shot. > > Art S. Kagel We can reproduce the problem on our DEV server which is 9.4UC5. I ran with the latest dostats (it works no more core dump) and it did not resolve the problem. I just want to make clear …
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?…
Problem of Performance
Query sysmaster:syssessprof. Find which session are doing most buffered reads... Run the sql from these sessions with set explain on and find if they are using indexes... and the right indexes exist... Get Art Kagel's utils2_ak from www.iiug.org Software Section and run it to …
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/…
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…
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!…
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.…
Re: IFX_FLAT_UCSQ
>> I am sure flattening of uncorrolated subqueries (all, some types? don't know!) is off by default. This is not correct. I am sure that any query can be flattened if the cost says to do it that way. Also read the excellent link that Bill Dare included: http://www.iiug.org/waiug…
Performance problems/System generated temp tables
Jeff wrote: > I've got an OLTP system that has developed poor performance. I > eventually tracked it down to excessive DBSPACETEMP I/O by monitoring > onstat -D. The pages read/written to the temp space far exceed the I/O > on our data spaces. > > It's been suggested in a previou…
Re: OPTCOMPIND
I've seen cases to support this. Not much ones I admit. J. david@smooth1.co.uk escribis: > ... > Personally I think the optimizer is buggy ... sending to informix-list…
RE: OPTCOMPIND
-----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Superboer Sent: 17 August 2005 11:46 AM To: informix-list@iiug.org Subject: Re: OPTCOMPIND >oltp use optcompind=0!!! > >if small table with indexes and current stats …
RE: OPTCOMPIND
-----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Colin Dawson Sent: 17 August 2005 09:57 AM To: informix-list@iiug.org Subject: OPTCOMPIND >Apologies if this has been discussed before..... > >I'm interested in peopl…
OPTCOMPIND
Apologies if this has been discussed before..... I'm interested in people's opinions on what they have set this parameter to for OLTP systems and why they used that setting. I'm not interested in what the parameter means, I know that, I can RTFM as well as anybody. FYI: I am us…
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: 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:…
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…
Cost Estimation for queries
Again optimizer considering BUFFERS/RA_PAGES while considering estimated no of I/Os is not a good idea since buffers are part of volatile memory where at a point of time we cannot be sure how many pages reside in memory satisfying the query for which optimizer is evaluating the b…
Re: purpose of optcompind and optgoal
calyanram@hotmail.com wrote: > Hi > > what is purpose of these two parameters ? > > regards > s.kalyan I'll answer since I was one of the users pressing for something like OPT_GOAL to be implemented back in the 7.24 days. OPT_GOAL tells the optimizer whether to optimize for the…
purpose of optcompind and optgoal
Hi what is purpose of these two parameters ? regards s.kalyan…
purpose of optcompind and optgoal
Hi what is purpose of these two parameters ? regards s.kalyan…
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…
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…
Re: Upgrade AIX and IDS at same time - bad idea?
On 12 Jan 2005 06:40:50 -0800, "Darryl Priest" <darryl.priest@piperrudnick.com> wrote: > >We upgraded from 4.3.3 to 5.2, and 7.31 to 9.40 without too much pain. That's encouraging. >Ditto on the OPTCOMPIND issue and the onconfig settings. And RTFM too! >The optimizer >just s…
Running 4GL on table that changes a lot
The question is not really clear; what is the problem; is it slow due to seq scans?? try to get a explain and analyze it or when the program runs or do a onstat -g tpf <threadid> and check for seqscans; or look in sysmaster:sysptprof for these tables assuming it's seq scans: Ha…
Re: SPL DATETIME bug ????
This performance fall off is only on datetimes, if the where clause uses integers then it's fine. If I drop the datetime select into the exec blade the performance is back to 0.3 Art S. Kagel wrote: > Paul Watson wrote: > > The difference is that effectively the second query/s…
RE: SPL DATETIME bug ????
check the explain for both query, I believe they are deferent. In the first query the optimizer know the range of date at optimization time, but in the second it do not. In each query he build deferent query plan. You should force the optimizer to work as the first query using di…
Scope change.
Leopold, If most of your tasks are reporting only and you are using HDR as the log you posted shows, why not run the read only reporting tasks on the DR secondary and reduce the load on your primary server? On a point of information HDR, which you call transactional replication…
Re: New set environment optcompind command
"Rob Prop" <rob.prop@xs4all.nl> asked: > I've read that with the command: SET ENVIRONMENT OPTCOMPIND <level> > you coudl change the OPTCOMPIND setting for a specific session. However > in my windows server (9.40.TC4) a get a syntax error. Following the release > notes this should…
New set environment optcompind command
Hi, I've read that with the command: SET ENVIRONMENT OPTCOMPIND <level> you coudl change the OPTCOMPIND setting for a specific session. However in my windows server (9.40.TC4) a get a syntax error. Following the release notes this should work from 9.40.xC3 or higher. Has someon…
Re: Single row update - assert failed
To find outstanding in-place alters, execute the following SQL statements: set OPTCOMPIND to 0; database sysmaster; set index to dirty read; select pg_partnum + pg_pagenum - 1 partn from syspaghdr, sysdbspaces a where pg_partnum = 1048576 * a.dbsnum + 1 and pg_next != 0 …
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'?…
IDS V7.30 and Windows 2000
Can Informix Dynamic Server Version 7.30.TC6 be run on a Windows 2000 host system? TIA Erwin…
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…
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…
9.40 query optimiser problem
A client has IDS 9.40.FC1 on HP-UX with: OPTCOMPIND 0 # Nested loop joins preferred OPT_GOAL 0 # FIRST_ROWS UPDATE STATISTICS (no distributions) has been run. According to "sqexplain.out", queries involving several tables, with a fixed condition on one table for whic…
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…
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.…
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…
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,…
Re: Re: Query Permormance improvement: URGENT
>Nonsense. The order in which the optimiser decides >to do the processing is critical. I Agree, but as long as the optimizer give a plan wich has some _sense_ , which is certain almost all the the time... But in the cases when the optimizer plan is _nonsense_ (statistics et all…
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…
Re: Query Optimization ... Is this all I can get??
The reason your original solution didn't work well is that You needed to have the correct indexes, which you have added and then the outer join query should be better and faster. > Although your first option would work in my case, it would be a mess, > as they would have to be O…
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 Optimization ... Is this all I can get??
Anthony Presley wrote: > > Although your first option would work in my case, it would be a mess, > as they would have to be OUTER joins of the address and phone table, > and the joining of some 100K with 300K with another 150K takes some > time [although, this was my original sol…
Re: Query Optimization ... Is this all I can get??
Yes - (amendment to my own response) - it's usually best to head an index with the most selective column, unless you have lots of queries that only reference the other column(s) or have an ORDER BY or GROUP BY that requires the index to be in a particular sequence. As for UPDATE…
Re: Query Optimization ... Is this all I can get??
Thanks for everyone's advice. Unfortunately, I am using a combination of Java and I4GL (moving everything to Jasper Reports at the moment), and was just using the "FOREACH" as an example. In reality, it is a for loop. I first added the (type, type_id) index, and then did an up…
Query Optimization ... Is this all I can get??
Hello all .... Always with the query questions .... so I come to you once again. 1st -- my textbooks on this subject are rather sparse, and a bit ... un-useful. Anyone have any good links / articles / books on query optimization? 2nd -- the actual problem .... I have a SIMPL…
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.…
Redbrick Query Optimizer behaving funny.
Hi All, Today I saw very funny behaviour of Redbrick query optimizer where the order in which tables are listed in the from clause is controlling the indexes being used. I am sending the results of explain for both the conditions: CONDITION 1: ============= RISQL> explain …
Re: FW: OPTCOMPIND
The release of IDS 9.40.UC3 should be around 12/09/2003 - EGA (Electronic General Availability), and 12/22/2003 (GA). Thanks and REgards, Abhi. -------------------------------------------------------------------- There are 10 kinds of people,,,,, .... those who understand binary…
Re: FW: OPTCOMPIND
When will that be? (9.40.uc3 release) Andy Bent -----Original Message----- From: Abhishek Mathur [mailto:abhishek@us.ibm.com] Sent: Monday, September 15, 2003 10:58 AM To: bil Cc: informix-list@iiug.org; owner-informix-list@iiug.org Subject: Re: OPTCOMPIND With IDS 9.40.UC3, y…
Re: OPTCOMPIND
With IDS 9.40.UC3, you should have the facility of setting the OPTCOMPIND dynamically, using a SQL like SET OPTCOMPIND <> You could wait till then! Thanks and Regards, Abhi. -------------------------------------------------------------------- There are 10 kinds of people,,,,, .…
Re: OPTCOMPIND
With IDS 9.40.UC3, you should have the facility of setting the OPTCOMPIND dynamically, using a SQL like SET OPTCOMPIND <> You could wait till then! Thanks and Regards, Abhi. -------------------------------------------------------------------- There are 10 kinds of people,,,,, .…
OPTCOMPIND
some of my jobs are very faster with OPTCOMPIND at 0 and others are faster with OPTCOMPIND at 2. is it possible to dynamically change this value ???…
Re: Tuning IDS 9.30 after migration from IDS7.31
Hi Dave, We migrated from informix 5.x to IDS 9.21 . We also had a lot of performace issues. The main reasons for our problems were - 1) Informix 5 did not have OPTCOMPIND param . In 9 we used 2 , a lot of queries started doing sequential scans instead of indexed …
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…
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…
massive deadlocks after upgrade from 7.3 to 9.4
"Jason" <jasonperrone@pricechopper.com> wrote in message news:XV9Ua.1390$vX1.9146@news.uswest.net... > My OPTCOMPIND is 0. > All tables are row level locking? Update stats done correctly? SET EXPLAIN ON shows all sql is using the correct index and that index is unique?…
OPT_GOAL setting at 4gl level
Hi, I have checked the manuals about where I can set the OPT_GOAL parameter. Below are the IBM docs, but they do not specify if I can set the OPT_GOAL in the 4gl code. Can anyone confirm if I can set this in the 4gl code? If I must do this at the session/user level, I assume this…
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???…
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…
Re: Search by index vs. sequential scan
Quoting Jonathan Leffler <jleffler@earthlink.net>: > Clive Eisen wrote: > > Quoting "Mark D. Stock" <mdstock@MydasSolutions.com>: > >>Robin Munn wrote: > >>>Do you mean something like this? > >>> > >>> bash$ OPTCOMPIND=2 cat batch_query.sql | dbaccess > >> > >>Yes. Don't forg…
Re: Search by index vs. sequential scan
Quoting "Mark D. Stock" <mdstock@MydasSolutions.com>: > Robin Munn wrote: > > Mark D. Stock <mdstock@mydassolutions.com> wrote: > > > > Thanks also for the environment variables suggestion. Do you mean > > something like this? > > > > bash$ OPTCOMPIND=2 cat batch_query.sql |…
Re: OPT_GOAL
On Tue, 20 May 2003 10:00:30 -0400, Greg Mitchell wrote: Setting OPT_GOAL to zero (or using FIRST_ROWS optimization in SET OPTIMIZATION or optimizer hints) is useful for internactive applications where records will be displayed a few at a time. It makes the app appear to be mor…
Re: Search by index vs. sequential scan
Robin Munn wrote: > Mark D. Stock <mdstock@mydassolutions.com> wrote: > >>Robin Munn wrote: >> >>>Ladies and gentlemen, >>> >>>I've been running into a very interesting problem, and I'd appreciate >>>your advice. When is it better to have a sequential scan vs. using an >>>index i…
Re: Search by index vs. sequential scan
Mark D. Stock <mdstock@mydassolutions.com> wrote: > > Robin Munn wrote: >> Ladies and gentlemen, >> >> I've been running into a very interesting problem, and I'd appreciate >> your advice. When is it better to have a sequential scan vs. using an >> index in a query? To be specifi…
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…
How do I set FIRST_ROWS for client session?
Set the environment variable OPT_GOAL=0 or add a SET OPTIMIZATION FIRST_ROWS in the apps. Art S. Kagel bafarwell@earthlink.net wrote: > > Does anyone know how to set the FIRST_ROWS optimization goal for a > client session (i.e. Windows client using native libraries, setnet) so …
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…
RE: Optcompind
Bill, If you have upgraded to a newer version recently, setting it to �0� ensures that your join methods remain same as in the earlier version. Other wise you have to set OPTCOMPIND, based on the nature of your data /table schema/queries. OPTCOMPIND influences the query plan in…
Optcompind
I still consistently see the suggestion on the list that optcompind should be set to 0 rather than 2. I've run with 2 for quite a long time and I saw a suggestion today that said to change it to 2 rather than 0. Is that something that was "fixed" in a later version (the questio…
IDS2000
OPTCOMPIND is set to 0 I can't remember what sort of joins were being made before (sorry!), but I'm fairly sure they weren't nested loop joins, and it is definetly slower than before. Queries that used to take 40 - 50 seconds now take 2 or 3 minutes. Thanks for any help! Scott…
Re: OPTCOMPIND
From: "Neil Truby" <ntruby@netcomuk.co.uk> > >IDS 9.20 on HP-UX 11.0 > >Does anyone have wisdom to impart on the optimal setting of OPTCOMPIND for >an OLTP system? Boy, I've seen some leading questions in my time... :-) You don't have a "normal" OLTP environment, do you Neil? :…
OPTCOMPIND
IDS 9.20 on HP-UX 11.0 Does anyone have wisdom to impart on the optimal setting of OPTCOMPIND for an OLTP system?…
Re: Query Optimization: Wrong execution plan?
From: Leonids.Voroncovs@dati.lv > >Xavi Tarafa Mercader wrote: > > > Any suggestion about how to improve the query performance? > >Set OPTCOMPIND parameter ( in onconfig file ) to 0. I'm not sure that will help. The query is going to probably scan both tables anyway, so a nested…
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.…
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…
Performance comparison btn Informix and SQL Server
Thanks for all the suggestions, I will try them out, and give a summary about the results. P.S. One small question I have now is: optimizing one query brings another one down! :-( At least I have one query which takes double so long as it took before I run UPDATE STATISTICS.…
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…
Data type conversion in query
Does anyone out there know how to force the optimizer/engine to convert data types in a query? I have a query that is joining a CHAR(20) column to an INTEGER column. Right now it is trying to convert the CHAR to an INTEGER which won't work. If I could make it convert the INTEGE…
RE: Slowness
From: "COOPER, Joseph" <Joseph.COOPER@sema.co.uk> > >I mainly read this to see what your comments would be because I had already >replied but this is priceless :-)))) > > > >OPTCOMPIND 2 # To hint the optimizer > > > > If this is an OLTP system, try setting thi…
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…
first 'n' rows
sveiga@my-deja.com wrote: > > In article <0cefcfc2.92a34d23@usw-ex0106-048.remarq.com>, > Esther <estherNOesSPAM@ts.es.invalid> wrote: > > I would like to know how can I use the "FIRST_ROWS" optimizer > directive > > and the "SELECT FIRST 'n' ROWS" directive. I don't know the s…
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…
Left join ignores optimizer directive
Hey, I'm wondering if anyone out there has had difficulties with left joins? Our query goes incredibly slow with optimize set high and fairly quickly with it set low. In order to improve performance, we brought in a performance expert who specified some optimizer directives. W…
OPT_GOAL
matur_suksema@my-deja.com wrote: > Has anyone experimented with changing the default for OPT_GOAL from all > row to first rows? Would anyone recommend using first rows for a 200+GB > database? Does anyone have any good/bad experiences that they can > share? You use FIRST_ROWS…
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…
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…
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…
Query Optimizer Weirdness in Stored Procedure
(Environment: Sun Enterprise/450, SunOS 5.6, Informix 7.30UC5) Greetings All! Has anyone out there encountered this same problem, and figured out a solution? I have a single table with over 10 million records in it. I am trying to retrieve a small number of records from it ba…
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…
OPTIMIZER DIRECTIVES IN 4GL
Hello. I can use optimizer directives from dbaccess / Query Language, but they don�t work in a 4gl-program. The engine is IDS 7.30.UC2-1 and RDS-4GL is 7.20.UD7 on a SCO OpenServer 5.0.5. Example: select {+INDEX (tablename indexname)} * …
SQL - Optimizer issues
Hi! Try setting OPTCOMPIND to zero in onconfig. You have to stop/start engine for this to take effect. You can also set the environment variable OPTCOMPIND=0 for individual user (and of course export it). HTH Michael Rajesh wrote: > I have an SQL query in a 4GL program tha…
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…
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…
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…
query optimizer (set explain) question
i've run set explain on three different systems (all hpux 10.20 with informix ds 7.3 uc2) the databases in question are the same schema (tables and indexes, same onconfigs, etc.) on each machine (however number of rows in each table varies. I ran set explain on each machine with…
Re: URGENT! How many rows will be returned?
I smell a troll here... Different databases use different technologies for query optimization and some are far better than others. Some 'so-called' optimizers need subtle hints to steer queries while others produce cost estimates based on statistics. Each database on your cros…
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…
RE: Prepared statement vs Exec sql insert/update/select
> Is there any benefit to preparing and executing sql statements > rather than just having them in an exec sql insert/update/select.... [Bill Raper] In the Informix world, there is a very significant benefit. This is in the fact that the optimizer processes are run only once …
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…
One HUGE Query - any optimizers?
This is a multi-part message in MIME format. ------=_NextPart_000_0082_01BEF4B3.58C19EC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've developed a program which constructs the following query and then = executes it. INSERT INT…
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.…
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…
Re: Help optimizing query (newbie)
Just a quick thought. If you are using transation logging it will be worth will creating the temp table with the no log option so it does not to any transaction logging. Nayan Jain wrote in message <7lapvi$3im$1@news.xmission.com>... > >Hi ! > >Just thought of correcting one thi…
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…
Optimizer question
Hi all, Informix Dynamic Server Version 7.30.UC6 Can someone please tell me if the optimizer takes into account the number of extents for tables when deciding on a query plan? Thanks Steve _______________________________________________________________ Get Free Email and Do …
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…
Online index usage in one table query
In article <37861A36.3B9B6FAB@bloomberg.net>, kagel@bloomberg.net wrote: ..snipped > To the first > question, if your query is prepared with replaceable parameters the > optimizer does not know at prepare time when it is deciding on a query > plan what the actual values will be…
Re: Help optimizing query (newbie)
Hi ! Just thought of correcting one things here On Mon, 28 Jun 1999, Peter Sloboda wrote: > Frank T. Ramb=F8l wrote: >=20 > > Hi. > > > > I am trying to run the following statements on a rather huge database. > > > > =3D=3D > > begin work; > > set lock mode to wait; > > > > up…
Help optimizing query (newbie)
Hi. I am trying to run the following statements on a rather huge database. == begin work; set lock mode to wait; update tracked_ids set chk_count=-1 where image_id in (select image_id[16,26] from image_ids); delete from image_ids where image_id[16,26] in (select image_id fr…
Re: Indexing
John: The optimizer is a cost based optimizer and the order of items in the query will not matter unless you use optimizer directives. Now if the filter created by "a=? and b=? and c=? is not very selective, and the optimizer estimates that a large number of rows will be returne…
SQL performance
Shaun Campbell wrote: > > I have a query regarding something we have noticed using New Era and > wondered whether it a bug in New Era or is related to the database. > > In New Era we have been preparing SQL Select statements with ? > placeholders so that we could use the same pre…
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.…
7.30UC2 - Subquery uses Autodindex instead of Index
Richard Peterson wrote: > > Hello, > > I have this problem after upgrading a server from 7.20 to 7.30UC2. The > optimizer appears to process subquerys diffrently when the in clause is > used. Rather than using the index on the table used in the subquery it uses > an autoindex ins…
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…
OPTCOMPIND
All, This may seem like a silly question, but what would be a good reason to ever set OPTCOMPIND to anything but 2? If 0 and 1 each bind the optimizers options to various degrees, what could possibly be the advantage of that? I'm curious. Any real life experiences where 0 or …
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…
RE: INDEX IS NOT USED
Probably your server has in the ONCONFIG the value OPTCOMPIND=2 or 1 You can set this variable in your own session --> export OPTCOMPIND=0 therefore you are telling the optimizer to use indexes when it can If you have Ver. 7.30 or later you can use hints in the query and also inf…
OPTCOMPIND = 0 : Why is this (always?) faster?
does the cost based optimizer take too much time to make up it's mind? Russ Evans Department of Health Information Resource Management Senior DBA 413-8490…
Re: Arghh! Please Help.
OK, so my question to all of you is: Why do you prefer Nested Loop Joins? Isn't this begging things to go slow??? I can refer to Informix training where NLJ's are strongly discouraged. I'd say set the OPTCOMPIND=2, and NEVER go for nested loop joins. If there is some magical…
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…
Re: Severe Performance Problem - Infomix 7.3 WGS & HPUX 10.20
eric wrote: > > We're in the process of upgrading from Informix 5 to Informix 7.3 Workgroup > Server but are experiencing severe performance problems. Some programs > which used to take 5 hours to complete are now taking 19 hours, on identical > hardware with identical indexing …
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…
Re: anyway to optimize this query?
>query only. Well, then, why not create all possible index combinations? :-) >Obnoxio The Clown wrote: > >> Sometimes it helps to treat a 2 CPU box as a single CPU box. Have you >> tried this? >> >> >PHYSBUFF 32 # Physical log buffer size (Kbytes) >> >LOGBUF…
Re: anyway to optimize this query?
query only. Obnoxio The Clown wrote: > >let's see... > > > >there are 61772773 rows in the table. > > > >The table is indexed on three fields, let's call it f1,f2,f3, it is a > >composite index, the reason I do that is because > >there is also a where clause in the query which…
Re: anyway to optimize this query?
>let's see... > >there are 61772773 rows in the table. > >The table is indexed on three fields, let's call it f1,f2,f3, it is a >composite index, the reason I do that is because >there is also a where clause in the query which uses all three fields as >criterias. > >update satis…
Re: anyway to optimize this query?
let's see... there are 61772773 rows in the table. The table is indexed on three fields, let's call it f1,f2,f3, it is a composite index, the reason I do that is because there is also a where clause in the query which uses all three fields as criterias. update satistics was p…
Re: anyway to optimize this query?
>hi all: > I have a 16 gig database, each record is contains 50 fields. > I am running a query like this: > > select f1,f2....f15, sum(f40),sum(f41),sum(f42), sumf(43) > group by f1,f2...f15 > > as you see, i have to group about 15 fields and sum 4 other fields >ba…
anyway to optimize this query?
hi all: I have a 16 gig database, each record is contains 50 fields. I am running a query like this: select f1,f2....f15, sum(f40),sum(f41),sum(f42), sumf(43) group by f1,f2...f15 as you see, i have to group about 15 fields and sum 4 other fields based on th…
performance hit in upgrade: 4.10 -> 7.20
David Dudas wrote: > snip > > Aside from rewriting the code, is there something that can be done to improve > performance? I can't believe that the upgrade would cause such degradation, > but I see it with my own eyes. There must be some factor that we have not > considered... …
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…
composite index question
Yan Zhu wrote: > > hi all: > If I put a composite index on three fields, a,b,c, what kind of > query would this benefit? > > I made a few queries with differenct criterias, and set explain on, > this is what I discovered. > 1. if the criteria is only on a or b or c, i…
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?…
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.…
Re: Way to determine UPDATE STATISTICS
Hi Jung-Hui, I got the following from a informix-training. Run UPDATE STATISTICS: - in MEDIUM mode with the DISTRIBUTIONS ONLY argument for colunms that have no indexes - in HIGH mode for columns that are the first key of a composite index - in LOW mode for each column in all m…
Re: Flushing Cache
Bryan Hughes wrote: > > Does anyone know a way to flush cache in Online 7.2x short of a) writing a > huge query, and b) cycling the engine? I am trying doing to query > optimization and need to do raw timing of the same query written in > different ways. Shut the engine down an…
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…
Re: Problem with Informix 7.3 I need a good Idea very soon
Bundesarchiv wrote: > > E posibile > to mark a table (a priori) as "AVOID_FULL"(scan) ?? > > or (i.e. with UPDATE STATS) "near fullscan disabled" ?? > > (in Informix 7.3) > > I need a good Idea very soon See other posts. Also have you tried OPT_GOAL=0 which can be set in the ap…
Re: Problem with Informix 7.3 I need a good Idea very soon
Guten Tag, Herr Brust. What is OPTCOMPIND set to? If it's set to 1 or 2, it is much more likely to do a full table scan than if it were set to 0. If it is not zero, try setting to zero. Bundesarchiv wrote in message <73ggjp$eeg$1@goof.de.uu.net>... > >E posibile >to mark a ta…
Re: Problem with Informix 7.3 I need a good Idea very soon
Hi Klaus, in this case you will have to deal with the different levels of update-statistics, until you get what you want. Try to do "typical" queries after 'set explain on;' in dbaccess and try to figure out, why the optimizer takes a full-table-scan. hth martin Bundesarchiv sch…
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: User thread status help
What, specifically, is the process trying to do? The "Y" means that it's "Waiting on a condition" such as a user response. I'd also check to see how OPTCOMPIND is set. gquian wrote in message <911819846.930921@proxy.inode.es>... >I'm running an application process that works a…
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 …
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…
Re: select without indexes?
Billy Wheeler wrote in message <71bnb7$4o5$1@news.xmission.com>... >Set OPTCOMPIND to 2 in your onconfig file. Make sure you've run >all the variations of UPDATE STATISTICS that are needed, and the >engine should work it out for itself. :-) > Alternatively, you can set OPTCOMPIND…
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.…
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: Minimizing bufwaits
In article <vI1++AAsRSJ2EwsS@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > >3005610 936 3233912260 2 0 2109 7633098 3122129 > > 3122129 sequential sc…
Re: Distributions
Stefan Weideneder wrote: > > Jay Aymond wrote: [SNIP other stuff] > Do you think it's a good idea to give the optimizer detailled > informations for columns, if the information will not cause > a better query plan ? Don't you believe that it will slow down > those queries that d…
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: SET EXPLAIN / SQL
Stefan Weideneder wrote: > > Hi David, > > as Sujit mentioned already, the results of SET EXPLAIN are > available as soon as you prepared your query. > This is not always true, but in most cases. > > If the query contains no question-mark "?", your query will > be optimized at th…
Re: 7.23 UC4 vs. 7.30 UC3
snip... > >> Also, Oyvind, we have experienced the wrong query path being selected by the > >> optimizer is 7.x for some time. We understand that 7.30 allows the user (us) > >> to control the query optimizer more directly. > > > >Yes, we have used this feature to verify that the …
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. > <skipped> I've had such problems on IDS 7.11 It was solved by setenv OPTCOMPIND=0 before connection Hope this helps Leonid…
Re: SET EXPLAIN / SQL
I am not sure but you can use Optimizer directives to select adhoc query plan. For more information about optimizer directives, you review the presentation slides about informix performance optimization by Kevin Fennimore on the following URL location (thank you Kevin). Good luck…
Re: Infromix & Data Warehousing Solutions
Doug Johnson wrote: > > Tim, > > Thanks for the info but we're not on the same page. I'm not talking about > the Informix query optimizer (access plan shown by 'explain plan'). There's > tons of info on www.informix.com on that optimizer. I'm talking about the > new MetaCube Ware…
Re: Infromix & Data Warehousing Solutions
Tim, Thanks for the info but we're not on the same page. I'm not talking about the Informix query optimizer (access plan shown by 'explain plan'). There's tons of info on www.informix.com on that optimizer. I'm talking about the new MetaCube Warehouse Optimizer. My understanding…
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 …
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…
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…
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: query optimizator
>Something strange happened to me (is not the first time) with >Informix OnLine 7.2 on UnixWare 2.1.1 with dual Pentium 133. >A query that we use frequently, suddendly stopped working. >The query optimizer decided to change something, and the query now >do not use anymore the rig…
Re: query optimizator
Marco Traini wrote: > > Something strange happened to me (is not the first time) with > Informix OnLine 7.2 on UnixWare 2.1.1 with dual Pentium 133. > A query that we use frequently, suddendly stopped working. > The query optimizer decided to change something, and the query now >…
query optimizator
Something strange happened to me (is not the first time) with Informix OnLine 7.2 on UnixWare 2.1.1 with dual Pentium 133. A query that we use frequently, suddendly stopped working. The query optimizer decided to change something, and the query now do not use anymore the right in…
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…
Re: I can't think of the subject name (have not words).
David Williams wrote: > > In article <35633efd.202294607@nntp.news.netcom.net>, David Kosenko > <davek@summitdata.com> writes > >Leonid Vorontsov <Leonids.Voroncovs@dati.lv> offerred: > >+OPTCOMPIND 0 > >+OPT_GOAL 0 > > What is OPT_GOAL ??? New 7.3 CONFIG file parameter to s…
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: +…
Re: I can't think of the subject name (have not words).
David Kosenko wrote: > "Art S. Kagel" <kagel@bloomberg.net> offerred: > +> > An annoyed Informix user wrote: > +> Do You see index3 on table1(field1, field2)? There isn't sorting > needed. > +WRONG WRONG WRONG! > +> There isn't table reading needed too. index3 contains all > …
Re: I can't think of the subject name (have not words).
"Art S. Kagel" <kagel@bloomberg.net> offerred: +> > An annoyed Informix user wrote: +> Do You see index3 on table1(field1, field2)? There isn't sorting needed. + +WRONG WRONG WRONG! + +> There isn't table reading needed too. index3 contains all information +> queried and in righ…
Re: Data binding
"Art S. Kagel" <kagel@bloomberg.com> offerred: +Yes. Versions before 7.3 resolved the query path at PREPARE time so, +except for an EXECUTE IMMEDIATE (which prepares and executes at the +same time), these two queries MUST be treated differently. Since the +actual value of the…
Re: I can't think of the subject name (have not words).
In article <35633efd.202294607@nntp.news.netcom.net>, David Kosenko <davek@summitdata.com> writes >Leonid Vorontsov <Leonids.Voroncovs@dati.lv> offerred: >+OPTCOMPIND 0 >+OPT_GOAL 0 What is OPT_GOAL ??? -- David Williams Maintainer of the Informix FAQ Primary site (Beta V…
Re: Question about cost estimated by Informix engine
Art S. Kagel wrote: +> Set OPTCOMPIND=0 in your ONCONFIG file and those nasty NESTED LOOP +> joins go away in favor of indexes. NESTED LOOP joins *are* index joins, typically. It means you read one table, by an index if possible, then use values from each row read from that tab…
Re: Poor performance after upgrade from Online 7.1 to 7.23
In article <6j0de2$2cm@news2.tds.net>, Robert Griffin <rag4@mead.com> writes >OPTCOMPIND is = to 0. What does that do? >David Williams wrote in message ... It favours the use of indexes over dynamic hash joins i.e. sequential scans of tables. >>In article <35520AA3.557581C1@c…
Re: Poor performance after upgrade from Online 7.1 to 7.23
OPTCOMPIND is = to 0. What does that do? David Williams wrote in message ... >In article <35520AA3.557581C1@cornell.edu>, Vic Goldberg ><vg19@cornell.edu> writes >>Check in the Administrator's guide, volume 2 - in the back, there's a section >>on >>oncheck. >> > Also check OPTC…
Re: Question about cost estimated by Informix engine
Leonid Vorontsov wrote: > > Hi, All! > I didn't receive any comments or suggestions about subj NVARCHAR posted > by me sometime ago. Sorry... But OK, life continues. Next question. > I have ODS for NT and simple query: [SNIP] Set OPTCOMPIND=0 in your ONCONFIG file and those nast…
Re: Poor performance after upgrade from Online 7.1 to 7.23
David Williams <djw@smooth1.demon.co.uk> wrote: >In article <35520AA3.557581C1@cornell.edu>, Vic Goldberg ><vg19@cornell.edu> writes >>Check in the Administrator's guide, volume 2 - in the back, there's a section >>on >>oncheck. >> > Also check OPTCOMPIND=0 in your ONCONFIG fil…
Re: Optimizing a select query
Sujata, Try creating the following index on tab2 (col2, col1). This way it will only need to do an index read. Also run update statistics high (if SQL/Server has the concept). Regards David Ashby ______________________________ Reply Separator _…
Optimizing a select query
Hi everyone, Here is a question for you guys. I am facing a problem with a simple select query running toooo slow. Before I proceed, let me explain that I am facing this problem in SQL Server not in Informix. I know the die-hard Informix fans will say "Well... there is your a…
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…
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: Optimizing..
In article <6gc0fc$qe1$1@news.xmission.com>, "ABEL ROJAS GARCIA" <abelrg@bital.com.mx> writes: > > Hi, > > What can I do for Optimizing queries? > > > Thanks How about giving an example of query you'd like optimized.…
Re: Optimizing..
Go to the Informix Advanced Development and OnLine Administration classes. They're a definite investment. Either that or do some SERIOUS reading. Vij. ABEL ROJAS GARCIA wrote in message <6gc0fc$qe1$1@news.xmission.com>... > >Hi, > >What can I do for Optimizing queries? > > >Th…
Optimizing..
Hi, What can I do for Optimizing queries? Thanks…
Re: Update statistics of the informix.xxx tables.
>Is the update statistics scenario of the informix.systables alike tables >of a database the same as for the users tables. ? For the most part the access to these tables use a "fixed plan". Since this information is mainly needed by the optimizer to generate the query plan, we …
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: Prepare a Statement that uses a temporary table?
Georg Winkens wrote: > > Is there a way in ESQL/C to prepare a Statement that uses a temporary > table (e.g. "Select * from Temp_Table ...") if this temporary table is > not already created at prepare time? No there is not. The purpose of preparing a statement is to save the co…
Re: Optimizer Info
>Are there any ways to know how the optimizer is evaluating all the >possible query plans and how it decides which one it chooses. > >I know about 'set explain' and data distributions and such, but is there >a way to see the process >the optimizer went through. Basically, no. T…
Optimizer Info
I am running 7.1 on Solaris 2.5 (soon to be upgrading to 7.24). Are there any ways to know how the optimizer is evaluating all the possible query plans and how it decides which one it chooses. I know about 'set explain' and data distributions and such, but is there a way to see…
Re: Query Optimizer
>Help me please! >I need information about algorithm of Query Optimizer. >Informix Engine - OnLine Dynamic Server 7.10 One can write a book on this topic. Basically, the optimizer looks at all possible ways of scanning every table in a query. The cost for each path is calculated…
Query Optimizer
Help me please! I need information about algorithm of Query Optimizer. Informix Engine - OnLine Dynamic Server 7.10 .…
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: ANY IDEA ON USING INDEX FOR ORDER BY
Peter Tashkoff wrote: } Hello Gokhan } I am reading between the lines a bit, because the information you have posted is a bit sketchy, so bear with me if I am wrong. } } Are you saying that this table has an indexes as follows. } } scenario 1. } Index1 } Duplicates allowed (file…
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…
Re: DELETE does not use index
>Sorry for my stupid question, but what does UPDATE STATISTICS do in detail? No such thing as a stupid question. UPDATE STATSTICS updates information in the database catalogue, such as systables, sysindexes, sysdistributions. Information from these catalogue tables is read by …
Re: Stored Procedures and querries.
From "Incorporating Stored Procedures and Triggers into Your Informix Databases Training Manual" Version 01-95 Page 6-128: When a stored procedure is created all optimization will be attempted at that time. If the tables cannot be examined at compile time (they may not exist o…
Re: Running UPDATE STATISTICS on a table being used
sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote: > Since the update statistics was going on parallelly with the query, will the > query use the new plan once the update statistics is over or does it still go > with the old plan ?? The query is just taking for eve…
<No Subject Supplied>
David Williams respond: >>>7. OPCOMPIND is ok to use with 7.2x?? I heard this was a showstopp= er. > >> Set it to 0 to allow index usage otherwise you get dynamic hash jo= ins >> and sequential scans. > > Can you tell more about this. (I have OPCOMPIND=3D2) OPTCOM…
Re: Very Severe Performance Problems on my 7.22 UC2
David Williams wrote : >>> OPTCOMPIND 2 > OPTCOMPIND=2 thus favouring sequential scans, Well, not really, at least that's not the way its *supposed* to work. With a value of 2 for OPTCOMPIND, the optimizer is supposed to consider both indexed access and dynamic hash…
Re: Set explain use
>Not considering indexes, does it make any difference to the informix >optimizer > if we switch the order of conditions in a where clause. For example, which >one > is better: > >select * from A where p = q and r = s or >select * from A where r = s and p = q . > >None of p,q,r,…
Re: Very Severe Performance Problems on my 7.22 UC2
sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote: <snip> > OPTCOMPIND 2 <snip> Hi-Sujata, Change above optimization indicator to 0 (zero), it could get you to a speed equal or better than your previous version. -- Have a nice day Felix K. Mathews mailto:…
optimizing - sqexplain
Hi, We are developing an application with New Era 2.20 using Online 7.10 (about to upgrade to 7.23). My goal is to optimize the response times and I'm new in that function. The first idea is to search through the access plan for high cost queries, the problem is that I don't kn…
Re: Query Optimizer
Dies ist eine mehrteilige Nachricht im MIME-Format. --------------7BAFAD1E691FD235D6CAD43F Content-Type: text/plain; charset=iso-8859-1 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Transfer-Encoding: 8bit Hello Mauro, but there still exis…
Re: Query Optimizer
Mauro G. Pisano wrote: > > HI all: I recently heard about the possibility of upgrade de query optimizer. > I 've got ON-LINE 5.0 and at Informix Seminary, It was listened taht by > upgradin only the query optimizer, somes exposed queries at the seminary, > had down its times fro…
Query Optimizer
HI all: I recently heard about the possibility of upgrade de query optimizer. I 've got ON-LINE 5.0 and at Informix Seminary, It was listened taht by upgradin only the query optimizer, somes exposed queries at the seminary, had down its times from 30 minutes to seconds. I want …
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…
Re: First N records in SELECT
Slavik Anosov wrote: > > Does anybody know something in Informix ODS 7.1 that perform functions of > Oracle's ROWNUM pseudo-column? I need to retrieve only first N records in > select. > No, there is no such feature. Assuming that you are using ESQL-C or 4GL you can simply close…
Re: Query Optimization question
Ignatius In version 7.x (not in 5.x) you can do: UPDATE STATISTICS MEDIUM FOR table(column) DISTRIBUTIONS ONLY The MEDIUM will do data sampling whether or not you create an index on the column. The DISTRIBUTIONS ONLY update the sysdistrib table in your database. In case you …
Re: Query Optimization question
On Thu, 10 Jul 1997, Ignatius Cristina Michelle wrote: > Hi. > > What statistical information can Informix draw upon to determine how > many rows will be satisified by a query coondition of the form "column = > value", for example, "department = 'D'" where "department" may or ma…
Query Optimization question
Hi. What statistical information can Informix draw upon to determine how many rows will be satisified by a query coondition of the form "column = value", for example, "department = 'D'" where "department" may or may not be an "indexed" column. Where is this statistical informati…
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: Force index usage
Richard Stanford wrote: > > > In article <5mhoc2$g0c@news.informix.com>, Amitabh B Sinha > > <amitabh@informix.com> writes > > >The next release of ODS and IUS will include a feature called > > >directives, which will be a non-ANSI compliant way of directing > > >the optimizer to…
Re: OWS performance problem
Guy Germonpre wrote: > > Hi > > I have an Informix Online Workgroup server 7.20.UC2 on SCO Unix 5.0.2 > After first install, it was dead slow. I did an update statistics high > and it worked much quicker. > > One problem stays : > if I have a composed index (A,B) and i do a selec…
Re: Set explain utility
>The sqexplain.out file exists immediately after the prepare. >Please, try it again. How can this be? At the PREPARE stage (for any real-life query) crucial information - values to use - are still missing. It is only at OPEN CURSOR when we tell the engine the values, and only t…
OWS performance problem
Hi I have an Informix Online Workgroup server 7.20.UC2 on SCO Unix 5.0.2 After first install, it was dead slow. I did an update statistics high and it worked much quicker. One problem stays : if I have a composed index (A,B) and i do a select "where A="X" and B matches "*Y*" it…
Re: Upgrading Informix 7.10 to 7.14
In article <33a5a53f.27105316@pegasus>, Audie Wright <audiew@thisco.com> writes >I will be upgrading Informix Soon. > >I am currently running Informix 7.10 UD2 on a Pyramid Nile 150. > >If any one has any major concerns, tips, tricks, or suggestions that >could be of benefit, I w…
Re: DSA follies
> Correct - hold cursor declared with hold are not parallelized... > Several other types 'queries' are not parallelized - can't remember > which... Wow, what a coincidence! I just read a page on this in the Performance Tuning Training manual. Queries NOT parallelized: * Th…
Re: Shared Memory allocation error
In message <8525647D.0065C7E6.00@gty02.homedepot.com>, Kate_Tomchik@HomeDepot.COM writes } } } } }Why does setting OPTCOMIND to 2 use more memory? } } OPTCOMPIND = 2 lets the optimizer deciede the best query path by cost alone and these costs are often wrong. It tends to do se…
Re: Shared Memory allocation error
Why does setting OPTCOMIND to 2 use more memory? Please respond to djw@smooth1.demon.co.uk To: informix-list @ rmy.emory.edu cc: (bcc: Kate Tomchik/IS/SSC/THD) Subject: Re: Shared Memory allocation error > > Make sure OPTCOMPIND = 0 (NOT 2)! This should reduce memory …
Re: OPTCOMPIND explanation req'd
Peter Whittletion <whittle@ssax.com> wrote in article <5gpauu$qem@cssun.mathcs.emory.edu>... > Am I right in assuming that the OPTCOMPIND parameter is only > relevant to the performance of joins. This is what I thought > it was for, but the ODS 7.2 Performance Guide doc talks abo…
Re: OPTCOMPIND explanation req'd
In article <5gpauu$qem@cssun.mathcs.emory.edu>, Peter Whittletion <whittle@ssax.com> writes >Am I right in assuming that the OPTCOMPIND parameter is only >relevant to the performance of joins. This is what I thought >it was for, but the ODS 7.2 Performance Guide doc talks about >…
OPTCOMPIND explanation req'd
Am I right in assuming that the OPTCOMPIND parameter is only relevant to the performance of joins. This is what I thought it was for, but the ODS 7.2 Performance Guide doc talks about it in relation to single-table scans. Anyone tried it? Peter Whittleton (whittle@ssax.com)…
Re: DSA 7.22.UC1/HPUX 10.20
In article <332C5837.EB5@netcomuk.co.uk>, Ian Goddard <igoddard@netcomuk.co.uk> writes >David Coburn wrote: >> >> Hi. I have an odd problem here. DSA 7.22.UC1 is demonstrably >> slower on my HP 9000/K420. I have an EMC disk array with 56Gb >> attached, 4 c…
Re: Informix -208 error
In article <$wNonDAUGOGzEwgD@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> writes >In article <331733be.39980879@gate.idg.no>, Nils Myklebust ><Nils.Myklebust@idg.no> writes >>Tim McCrea <tim@mccltd.demon.co.uk> wrote: >> >>:Hi, >>: >>:Hopefully there is an Infor…
Access to Optimizer results
We are using version 7.2 on HP-UX 10.10, and would like to know if there is any way to access to "cost" (from the optimizer) of a query while the query is executing without having the explain plan written out to a dataset. We are trying to develop a quey govenor that can be used…
Re: understanding On-Line's Optimizer
Steve Romankiw wrote: > > I am new the Informix On-Line engine. I am looking for information on > On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from > SQLBase and the optimizers work differently. In informix, we often get > sequential scans on queries that w…
Re: understanding On-Line's Optimizer
At 10:05 AM 19-12-96 -0500, you wrote: >I am new the Informix On-Line engine. I am looking for information on >On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from >SQLBase and the optimizers work differently. In informix, we often get >sequential scans on que…
Re: understanding On-Line's Optimizer
sromankiw@execrisk.com (Steve Romankiw) wrote: :I am new the Informix On-Line engine. I am looking for information on :On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from :SQLBase and the optimizers work differently. In informix, we often get :sequential sca…
Re: understanding On-Line's Optimizer
On 19 Dec 96 at 10:05, Steve Romankiw wrote: > I am new the Informix On-Line engine. I am looking for information on > On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from > SQLBase and the optimizers work differently. In informix, we often get > sequential s…
understanding On-Line's Optimizer
I am new the Informix On-Line engine. I am looking for information on On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from SQLBase and the optimizers work differently. In informix, we often get sequential scans on queries that we feel should be using an index.…
Re: update stats resolution
Manoj Shroff wrote: > > Hi All, > > Does anyone have any guide lines for the use of the > resolution clause in "update statistics" ? Hi All, data distribution is an additional overhead for the optimizer. Therefore you should try to avoid data distributions whereever pos…
Re: Problems with Informix DSA
In article <53e4hn$j7l@artemis.ibernet.es>, jicg <1812j30c@sendanet.es> writes >Hello. > >We are migrated from Informix STANDARD ENGINE to INFORMIX On-Line >DSA 7.12.We have a machine IBM G-30 with two processors, 128 MG RAM, >2 Disks. We get less performance with DSA than with S…
Re: Optimize A Simple Query -- Help
Chao Y. Din wrote: > > I have a simple select count statement that takes three minutes to > execute, yet I only have about 300,0000 rows. Here is the SQL. > > SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 > > Is it possible to force the query to …
Re: Optimize A Simple Query -- Help
Chao Y. Din wrote: > > I have a simple select count statement that takes three minutes to > execute, yet I only have about 300,0000 rows. Here is the SQL. > > SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 > AND col2 = 10; > [SNIP] > Is it possible…
Re: Optimize A Simple Query -- Help
At 08:29 AM 9/16/96 -0400, you wrote: }I have a simple select count statement that takes three minutes to }execute, yet I only have about 300,0000 rows. Here is the SQL. } } SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 }AND col2 = 10; } }The "key" field …
Re: Optimize A Simple Query -- Help
tvarnas@compulink.gr (Apostolos Varnas) wrote: :Chao Y. Din wrote: :} :} I have a simple select count statement that takes three minutes to :} execute, yet I only have about 300,0000 rows. Here is the SQL. :} :} SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND …
RE: Optimize A Simple Query -- Help
Chao Try creating an index on table (col1, col2, key). This should make the = optimizer utilize the index path. Also since col1 and col2 are highly = duplicate values, this will give the index some degree of uniquenss so = the optimizer finds it worth giving the new index a try.…
Re: Optimize A Simple Query -- Help
Chao Y. Din wrote: } } I have a simple select count statement that takes three minutes to } execute, yet I only have about 300,0000 rows. Here is the SQL. } } SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 } AND col2 = 10; } } The "key" field is th…
Re: Optimize A Simple Query -- Help
Chao - have you tried "update statistics medium..." or high...? That might induce the optimizer to choose the more discriminating index. If you're running "low", the optimizer is flying pretty blind when it comes to data distributions. Greg Chao Y. Din wrote: > > I have a s…
Optimize A Simple Query -- Help
I have a simple select count statement that takes three minutes to execute, yet I only have about 300,0000 rows. Here is the SQL. SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 AND col2 = 10; The "key" field is the primary key with data type char(11). …
Re: Explaination of OPTCOMPIND
In article <51c1gc$ng2@cssun.mathcs.emory.edu>, Carol Davies <cdavies@csc.com> writes >Hi Folks, > >We are running Online7.1 UC1 on RS/6000, AIX 3.2.5 and are trying to >optimize a process that builds a table with 6-10 million rows from other >large data tables and currently runs…
RE: Explaination of OPTCOMPIND
Carol In general, I would suggest that you: 1. SET EXPLAIN ON for these queries in the real (not development) = database and see what query path the optimizer takes. In case there are = sequential scans maybe you can add some indexes which make the SELECT do = an indexed path re…
Explaination of OPTCOMPIND
Hi Folks, We are running Online7.1 UC1 on RS/6000, AIX 3.2.5 and are trying to optimize a process that builds a table with 6-10 million rows from other large data tables and currently runs over 1-2 days. We have done all the indexing stuff, but was wondering if setting the OPTC…
Re: Update Statistics Low, Medium & High
billy@west.co.za (Billy Wheeler) wrote: :> >In our system, we discover sometimes some tables in index searching is much :> >faster while we update their statistics low or medium instead of high. :> I heard a discussion the other day which indicated that when you update :> stati…
Re: Update Statistics Low, Medium & High
Clem Akins wrote: > > >In our system, we discover sometimes some tables in index searching is much > >faster while we update their statistics low or medium instead of high. > > > >We have an idea that update all tables high statistics with reference to the > >server admin. guide.…
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: DSA 7.12 Performance Problems
>> we are hitting bug #49459 which causes the optimizer >> to pick a sequential scan regardless of OPTCOMPIND settings, or >> what permutation of update statistics is run. Tech support noted >> that this bug is not fixed until 7.14 or 7.2 for our platform. So >> back to 7.11 …
Re: DSA 7.12 Performance Problems
dave, > we are hitting bug #49459 which causes the optimizer > to pick a sequential scan regardless of OPTCOMPIND settings, or > what permutation of update statistics is run. Tech support noted > that this bug is not fixed until 7.14 or 7.2 for our platform. So > back to 7.…
Set Explain with SP's
In attempting to determine how the optimizer is processing queries in stored procedures, we've tried turning 'set explain on' within a procedure. However, when executing the procedure, I get an empty file. Is there anything special which needs to be done, to get set explain outp…
Re: EXPLAIN result diff. in TEST and REALITY
: In our TEST DB the result of a joined query and SET EXPLAIN is INDEX PATH : for the two tables. On our REAL enviroment this result is for one table : is SEQUENTIAL SCAN, and for the other one is INDEX PATH. Is this normal ?? : The query is awful slow, maybe thatsway ?? Hi, am…
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?…
Re: Distributed Join
>I remain very sceptical. To optimize a join across multiple vendor databases >requires that your 'joining' product must fully familiar with all >the individual optimizers in each database to understand how they will process >their portion of the query. Next it must be familiar…
about INDEX
Good day people, somebody could tell me which is the use of INDEX and one example. I think that with INDEX, I can to optimize my querys in 4gl, thanks in advanced. Atte. German Velazquez…
Re: Query Optimising Problem !!
Optimizing a query can be very quick once you type set explain on and then run your query. The output will tell you how the optimizer has decided to process your request: it will show you what indexes it uses, what filters and when it is doing a sequential read. It can be su…
Re: Query Optimizing Problem !!
In article <3i16po$c1s@raffles.technet.sg>, Alan Chen <sgs@technet.sg> wrote: >I have never done any query optimization practise in Informix Online >before, can anyone provide me some pointers ?? Any good reference book ?? >Any idea will be appreciated. > >Thanks in Advance. > >A…
Re: Optimizing Many-Table Joins
mmueller@calvin.bellahs.com (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 comp…
Optimizing Many-Table Joins
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? Thanks! --Michael mmueller@bella…
RE: Optimize Query for slow responce
> 26-Oct-1994 > >I can not understand the Informix Query Optimizer ! >--------------------------------------------------- > > >I have a fine large table and I create a query, where I select all fields from >the table. To lookup the records I have an unique index on a field…
Re: Optimize Query for slow responce
In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) says: > > 26-Oct-1994 > >I can not understand the Informix Query Optimizer ! >--------------------------------------------------- > > >I have a …
Re: Optimize Query for slow responce
In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) writes: |> Now look at the Optimizer Query Plan: |> |> |> QUERY: (LOW) |> ------ |> select * from ks_gdkonto |> where gd_kontonr = "0003573508" |> and gd_prod_kode < 90000 |> |> |> Estimated Cost: 2 |> Es…
RE: Optimize Query for slow responce
} 26-Oct-1994 } }I can not understand the Informix Query Optimizer ! }--------------------------------------------------- } } }I have a fine large table and I create a query, where I select all fields from }the table. To lookup the records I have an unique index on a field…
Optimize Query for slow responce
26-Oct-1994 I can not understand the Informix Query Optimizer ! --------------------------------------------------- I have a fine large table and I create a query, where I select all fields from the table. To lookup the records I have an unique index on a field called '…
Re: Query Optimizer
In article <dennisp.762623729@puma>, Dennis Pimple <dennisp@informix.com> wrote: >root@perk.jpr.com (Joseph A. Miele) writes: > > >>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 sca…
Re: Query Optimizer
root@perk.jpr.com (Joseph A. Miele) writes: >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 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…
Re: Optimizing queries info
Greg Sheppard (imop%uspfo.wa-ngnet.army.mil@wa-ngnet.army.mil) wrote: : We've recently written a couple of reports which take FOREVER (hours) : to run...and are getting very interested in streamlineing our queries : for speed and efficiency. Is there a book or useful info on ho…
Re: Optimizing queries info
}From: bt@irl3b2c.att.com }Date: Thu Dec 16 9:15:34 GMT 1993 }Subject: Optimizing queries info }X-Informix-List-Id: <list.3228> } }Greg Shepard wrote } }> We've recently written a couple of reports which take }>FOREVER (hours) to run...and are getting very interested in streamlin…
Re: Optimizing queries info
Greg, The best way to speed up queries is temp tables. If you use set explain on you might find informix is creating its own temp tables but not making good choices. You know you data better that the engine so you should be able to build better (smaller) temp tables. -- Greg …
Query Optimizer or Lack Thereof
Yet another example of a slooooooow query. This does get frustrating because there are too many times that a perfectly good query has to be split into parts (ie. temp tables) in order to get some decent access time. In the query described below, Does anybody have a clue as to …
Re: Optimizing queries info
> > > } > }I suggest that Jonathan Leffler's book - "Using Informix-Sql" [ISBN > }0-201-51240-8] is *required* reading if you want to do anything with isql. > > Thank you very much. > Well deserved I might add (the compliment - not the thanks). j. _____________________________…
Re: Optimizing queries info
}From: bt@irl3b2c.att.com }Date: Thu Dec 16 9:15:34 GMT 1993 }Subject: Optimizing queries info }X-Informix-List-Id: <list.3228> } }Greg Shepard wrote } }> We've recently written a couple of reports which take }>FOREVER (hours) to run...and are getting very interested in streamlin…
Re: Optimizing queries info
Greg Sheppard (imop%uspfo.wa-ngnet.army.mil@wa-ngnet.army.mil) wrote: : We've recently written a couple of reports which take FOREVER (hours) : to run...and are getting very interested in streamlineing our queries : for speed and efficiency. Is there a book or useful info on ho…
Optimizing queries info
Greg Shepard wrote > We've recently written a couple of reports which take >FOREVER (hours) to run...and are getting very interested in streamlineing >our queries for speed and efficiency. Is there a book or useful info on >how best to get the quickest results with queries using…
Optimizing queries info
We've recently written a couple of reports which take FOREVER (hours) to run...and are getting very interested in streamlineing our queries for speed and efficiency. Is there a book or useful info on how best to get the quickest results with queries using iSQL? Or is it just a…
Re: Query Optimiser
John B writes: |Has anyone noticed the 5.X optimizer making bad choices when |choosing a query plan? choosing a query plan? In my case the |optimizer insists in performing a sequential read of a detail table |ignoring the unique index used to join to the master. Performance, …
Query optimizer in 5.01 Informix
Has anyone noticed the 5.X optimizer making bad choices when choosing a query plan? In my case the optimizer insists in performing a sequential read of a detail table ignoring the unique index used to join to the master. Performance, as you can imagine is very, very poor. Infor…
Informix query optimizer info please
I am looking for detailed information on how the Informix query optimizer operates (i.e. makes its decisions, what it looks for, good tips on how to formulate tricky queries, etc.) I have a reasonable amount of experience in the land of Sybase, but I'm new to Informix. After I …
Re: SQL query
Bob Baskett writes: |netters, |can someone explain why the optimizer doesnt short circuit on the |following query: | QUERY: | ------ | select * | from book_bill_back | where 1=2 | Estimated Cost: 370832 | Estimated # of Rows…
False queries and optimizer
Is there any way to slip fake queries past the online5 optimizer? E.g., select * from foo where field1=42 works fine, uses the index on field1, while select * from foo where 1=0 or field1=42 has the optimzer doing a sequential scan on all of foo... (As for "why do I care", I…
Informix Online 5.0 Query Optimizer
We have a problem concerning Informix Online 5.0 query optimization. Informix seems to be choosing extremely inefficient paths for no apparent reason. The part_ref table originally had only one index, a composite of partnumber and option. When a query was performed that joined …
Re: Indexing strategy
1rk2ciINNrhr@emory.mathcs.emory.edu writes: >: Stuff deleted.. >: If you define the unique 3-part index before the >: non-unique 2-part index, the query optimizer may already be using the 3-part >: index (so I have heard), since the optimizer finds the 3-part index first, >: an…
Re: re:choosing idx to use
Ann Barnes@letterkenn-emh1.army.mil writes: |> |> When you create a composite index, the first field is also, effectively an |> index of its own. This was mentioned in the query optimization article here |> yesterday, but perhaps not specifically stated. Actually we have found i…
Re: Query Optimism
%UNIPLEX %TO informix-list@rmy.emory.edu %FROM rridley %SYSTEM DHLNET %SUBJECT Re: Query Optimism %VERIFY y %DATE 29/03/93 16:30 %REFERENCE 17053 Organization: Informix Software, Inc. Re: >In article <billd.733006990@cheetah…
Re: Help: SQL query optimizer
In article <billd.733006990@cheetah>, billd@informix.com (William Daul) writes: |> |> From: Spring 1987 TechNotes |> |> Query Optimization |> ================== |> Uh, Bill? This article is on the Rule-Based optimizer, which we have not used since 1989 (the 4.0 release of p…
Re: Help: SQL query optimizer
Anique Van der Vlugt (anique@sun-4.cs.uct.ac.za) wrote: : : Hi : : I am presently involved in a project for a SQL query optimizer for INFORMIX. : Has anyone done any research in this field or knows of anyone who does? I : am also interested in any articles related to the field o…
Re: Help: SQL query optimizer
In <anique.732967452@sun-4> anique@sun-4.cs.uct.ac.za (Anique Van der Vlugt) writes: >I am presently involved in a project for a SQL query optimizer for INFORMIX. >Has anyone done any research in this field or knows of anyone who does? I >am also interested in any articles rela…
Help: SQL query optimizer
Hi I am presently involved in a project for a SQL query optimizer for INFORMIX. Has anyone done any research in this field or knows of anyone who does? I am also interested in any articles related to the field of query optimization. If you can help me in any way please email m…
Re: Two index query optimization problem
Devin Wala writes: > > >I'm having some problems with Informix over optimizing my query. >I have a table with > 500000 rows and I can't get Informix to >use what I deem to be the "correct" index. > >There are two indexes on the table: > > index1: unique index on A, B; > index…
Re: Two index query optimization problem
> I'm having some problems with Informix over optimizing my query. > I have a table with > 500000 rows and I can't get Informix to > use what I deem to be the "correct" index. > > There are two indexes on the table: > > index1: unique index on A, B; > index2: unique index o…
Two index query optimization problem (fwd)
} I'm having some problems with Informix over optimizing my query. } I have a table with > 500000 rows and I can't get Informix to } use what I deem to be the "correct" index. } } There are two indexes on the table: } } index1: unique index on A, B; } index2: unique index o…
Re: Two index query optimization problem
In article <1992Dec12.000653.7004@anasaz> devin@anasaz (Devin Wala) writes: > > >There are two indexes on the table: > > index1: unique index on A, B; > index2: unique index on C, D, A, B; > >The problem I'm having is that I can't run a query with A and B mentioned >in the WH…
Two index query optimization problem
I'm having some problems with Informix over optimizing my query. I have a table with > 500000 rows and I can't get Informix to use what I deem to be the "correct" index. There are two indexes on the table: index1: unique index on A, B; index2: unique index on C, D, A, B; …
Re: QUERY Optimizer
> Hi netters > > I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is > very often guessing wrong, even if we run update statistics. > > The estimated number of rows returned does very much depend on the > sequence in which you enter the SELECT statement. In one …
QUERY Optimizer
Christian Hansen write: >I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is >very often guessing wrong, even if we run update statistics. > >The estimated number of rows returned does very much depend on the >sequence in which you enter the SELECT statement. …
QUERY Optimizer
Hi netters I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is very often guessing wrong, even if we run update statistics. The estimated number of rows returned does very much depend on the sequence in which you enter the SELECT statement. In one case we had …
query optimization
jmn@pdx.csd.mot.com (Jim Nielsen) write : >Can anyone tell me where I might find some definitive information on >how to optimize wildcard searches. > >I have a table which contains 300,000 records and I need to look up >records based on 6 possible fields. Some of the fields can…
Query Optimizer
What follows are some excerts from my previous posting and an update: > > In running the query described below on my OLD version 2.10.03K performance > was completely acceptable. On version 4.0, ouch!!!! Queries that took seconds > were now taking lots........ of minutes like 15…
Re: Update Statistics
> > Does anyone have a recommendation on how often the update statistics > command should be run? 'update statistics' only updates the system catalogs with the correct number of rows and the 'next-to-highest' and 'next-to-lowest' values so that the optimizer can make an educated…
(no subject)
rhlab!kuhn@uunet.uu.net writes: > In running the query described below on my OLD version performance was > completely acceptable. On version 4.0, ouch!!!! Queries that took seconds > were now taking lots........ of minutes like 15,20,25 minutes. > > After sending tech support a s…