Feed items

jfilippi's picture

Customize Saving SQLTRACE Data - Long running SQL's

​Here is a way to save SQLTRACE information, but to only save SQL statement that run greater than "X" number of seconds.

This way you can look at your slowest SQL to look at tuning it.

​You will be implementing a task in the SYSADMIN database and creating the table in that database also.

jfilippi's picture

Custom OAT Plugin - Monitoring and Reporting

Check out my Custom OAT Plugin for improved Monitoring and Reporting. 

It builds on the example plugin.

Download at www.itdataconsulting.com.

jfilippi's picture

Running Informix 4gl 7.50xC6X1 with Informix 12.10 (Missing message files)

I came across an issue when I was trying to run Informix 4gl programs (7.50FC6X1) on an Informix 12.10FC1 engine.

The error I received was "Please install an INFORMIX-4GL Run Time Facility and run this program again".

The only thing I did was to upgrade Informix from 11.70FC7 to Informix 12.10FC1.

After working with tech support, I found out that there were some message files for 4gl that were not in Informix 12.10FC1.

Once I copied them from Informix 11.70FC7 to 12.10FC1, the 4gl programs ran.

jfilippi's picture

RSS_FLOW_CONTROL - Current Restriction (Max 2 gig)

 If anyone is looking to use RSS_FLOW_CONTROL (in 11.50FC8 and 11.70) there is currently one restriction that I uncovered that has been entered as a defect.
When you set the values for RSS_FLOW_CONTROL currently the values have to be under 2 gig due to a limitation in the way it was implemented. 
I came across the issue when I tried to set RSS_FLOW_CONTROL to "40G,1M", it actually internally had turned off RSS_FLOW_CONTROL because of how it interpreted it.
So be aware when setting RSS_FLOW_CONTROL. 
jfilippi's picture

Sample of Presentation for 2010 IIUG Conference

Issue with home grown sequence generators.

I had a customer who we implemented SDS with and ran into a problem with their own sequence number generator.
After go live, the customer noticed in about a ½ % of the time that they were seeing duplicates.
Since SDS uses optimistic locking the table that was keeping the counter had multiple processes updating the table and since the record was not locked during the update it was being reset.
On SDS - Process 1: select seq_cntr from seq_cntr_tbl where cntr_id = 4 - (Returns 1500)
On SDS - Process 2: select seq_cntr from seq_cntr_tbl where c

jfilippi's picture

Presenting at 2010 IIUG Conference - Implementing SDS(Shared Disk Secondary) Tips and Tricks

I will be presenting at the 2010 IIUG Conference on Monday April 26th at 3:30pm.
The topic will be "Implementing SDS (Shared Disk Secondary) Tips and Tricks"
Stop by and learn more about implementing SDS servers.

jfilippi's picture

Informix SQL (Use of Functions Cause Sequencial Scans)

I have saw many times at clients where SQL statements use "functions" in trying to simplify operations.

Sometimes the way that functions are used can cause sequencial scans on tables.

Here is an example where the function caused a sequencial scan.

To resolve the issue we were able to remove the "UPPER" function since the application had always stored characters in upper case.


select distinct business_unit, voucher_id, invoice_id, gross_amt

from ps_voucher_srch_vw

where business_unit = 'GH'

jfilippi's picture

Improving Informix SQL Performance - Use of EXISTS

I run into many cases at clients where improving SQL statements or adding/changing indexes can make a big difference.

One client I was just at I made a few changes to their system that made a big difference.

jfilippi's picture


Welcome to my blog. 

My name is Jeff Filippi, I have been working with Informix for 19 years now, 14 as an Informix DBA. 

My company "Integrated Data Consulting"  is an IBM Business Partner who specializes in Informix Database Services.

My blog will focus on Informix Tips from experiences that I have ran across when at my customer sites.