Skip to main content

Questions tagged [informix]

Informix is an SQL-based object-relational, OLTP/OLAP, NoSQL DBMS produced by IBM. This tag is appropriate for questions about any of the products with Informix in the product name.

Filter by
Sorted by
Tagged with
25 votes
1 answer
17k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...
newenglander's user avatar
  • 1,055
20 votes
3 answers
20k views

What does NVL stand for?

What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
newenglander's user avatar
  • 1,055
13 votes
2 answers
8k views

How to delete a procedure which name is ambigous?

I am using informix... I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a DROP PROCEDURE myProc; then i get a error message ...
PeterP's user avatar
  • 163
11 votes
1 answer
11k views

SQL Server --> Informix DB Linked Server

After about 3 days of trying, I am unable to create a linked server from SQL Server 2016 to Informix 11.5. I haven't tried this with other versions (2008R2, etc) since I wanted to set up the ...
S3S's user avatar
  • 3,578
6 votes
2 answers
1k views

How should I best design the tables and relationships, given the following rules?

In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
Joe R.'s user avatar
  • 127
5 votes
2 answers
1k views

Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

Informix 11.70.TC4DE: CREATE TABLE cluster_tbl ( fk_id INT, data CHAR(2048) ); LOAD FROM "presorted.ld" INSERT INTO cluster_tbl; CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id); {#...
Joe R.'s user avatar
  • 127
5 votes
1 answer
3k views

Informix DB export to Microsoft SQL Server 2016

I have an Informix Database and a Microsoft SQL Server 2016. My task is to dump the whole Informix Database and export it to the Microsoft SQL Server Database. I installed the Informix SDK 4.5. I ...
Daniel's user avatar
  • 61
4 votes
4 answers
7k views

Filter Stored Procedure Result (Informix)

IBM Informix Dynamic Server Version 11.50.UC5XA. I have a Informix database. This database contain a stored procedure with name of 'sp_agent_details' that get two DateTime Parameter. This stored ...
Hojjat Jashnniloofar's user avatar
4 votes
2 answers
971 views

Informix - Getting all descendants of a parent

I have a table with two columns, Parent and Child. Need to get the list of all descendants associated with the parent records. Source Table: +----+-----------+ | Parent | Child | +----+-----------+ |...
Zoran's user avatar
  • 43
4 votes
3 answers
25k views

What's a good (free) client for Informix on Windows? [closed]

I'm looking for a GUI client for Informix on Windows (mainly to view data and execute queries), free would obviously be nice but I know Informix is used mostly in corporate environments and free may ...
newenglander's user avatar
  • 1,055
4 votes
0 answers
3k views

How to declare and use variables in Informix

How can I declare and use variables in Informix Database. I'm using version 9.40uc7 and I would like to create two date variables read values into them then compare them using an if statement. eg....
Kefash's user avatar
  • 175
3 votes
2 answers
2k views

What are the various Informix column data types?

I am writing a query where I need to know if a column is of the "BLOB" type of column. According to this documentation 41 is the number for "BLOB" types of columns, in general. ...
Vanity Slug - codidact.com's user avatar
3 votes
5 answers
13k views

How to join the latest previous record with SQL

I have a table whose schema is like this: CREATE TABLE product_shipping( product_id CHAR(10), product_name CHAR(10), deal_dt DATETIME, deal_reason_no SMALLINT, cancel_dt DATETIME, ...
Marcus Thornton's user avatar
3 votes
2 answers
756 views

Convert seconds into HH:MM:SS in Informix

How is it possible to convert a float number of seconds to HH:MM:SS in Informix. I have a column that has a run duration of 1449.448520410. I want to convert this to a human-readable format. There are ...
Christopher Karsten's user avatar
2 votes
3 answers
3k views

Creating a MySQL db from an Informix database

There is an existing database that was created in Informix. I would like to export it and then import it into MySQL. I tried to export it using dbschema and it created the dbase.sql file and several ...
Torey's user avatar
  • 21
2 votes
1 answer
1k views

Extract SYNONYM and SEQUENCE from Informix metadata

We are migrating Informix database to oracle. Please help us to extract SYNONYM and SEQUENCE from Informix metadata using query.
user avatar
2 votes
2 answers
11k views

Multiple table rows in one row Informix

I have problem just like this one but in Informix. I have table with 2 attributes ID, email like this one: ID email 1 [email protected] 2 [email protected] 2 [email protected] 2 my_mail4@...
Cikson's user avatar
  • 23
2 votes
1 answer
6k views

How to get all tables in informix database and their sizes in GB?

I can get a crude estimate of table sizes with SELECT tabname, (rowsize*nrows)/1024/1024/1024 AS tabsize FROM informix.systables ORDER BY tabsize DESC Is there a way to get a more accurate ...
stevec's user avatar
  • 123
2 votes
1 answer
3k views

How to check if a table exists in Informix

What would be the query to check that a table exists in Informix ?
Developer Marius Žilėnas's user avatar
2 votes
2 answers
724 views

32 bit vs 64 bit system - Informix Client SDK

If I create an Informix SSIS package in my 32 bit system using 32 bit Client SDK and deployed it in 64 bit server. Will it create issue?
Sathiya Kumar V M's user avatar
2 votes
1 answer
916 views

How do I convert a BIGINT to a DATETIME in Informix 12.1

I'm new to Informix, and I'm trying to convert a bigint to a datetime. SELECT sdatetime FROM CallDetail; Results: sdatetime ---------- 1572509662678 1572518550704 1572519033540 This seems to be the ...
Leezard's user avatar
  • 123
2 votes
2 answers
3k views

Connection Issues with Informix OLEDB Provider

I've been trying to establish a OLEDB Connection to an Informix Dynamic Server. (Version IBM Informix Dynamic Server Version 12.10.FC12WE) Background Info: A program, which will be used by my ...
kastner_bsa's user avatar
2 votes
1 answer
182 views

informix rest api not startable

I am currently trying to configure the informix REST API, as specified here. Overall, I came across two error messages: Unsupported listener type of rest specified OP Code is not valid: Check ...
Martin's user avatar
  • 23
2 votes
1 answer
2k views

ODBC Connection As Clustered Resource

First step in a long process of creating a Linked Server to an Informix database on a Sql 2008 (Win 2003 x64) cluster. I was wondering how to go about creating an ODBC connection as a clustered ...
swasheck's user avatar
  • 10.7k
2 votes
1 answer
548 views

Group records with same value in one row informix

I have 3 records in a table below called systables_growth: tabname aactn total_size_kb 32 date_added 2019-08-26 13:00:00 tabname aactn total_size_kb 37 date_added 2019-...
Christopher Karsten's user avatar
2 votes
1 answer
79 views

Informix help - SQL DBA

I am a SQL Server DBA with minimal Informix 11 experience. I'm tasked with extracting an entire Informix database (circa 8GB according to systables in Informix) into a SQL Server database. I plan to ...
Hpk's user avatar
  • 304
2 votes
1 answer
1k views

Can't Select a View on Informix

I'm using Informix IDS 11.50 Innovator-C edition running on Slackware Linux 12.0. I've been running a very small Database on it(less than 10000 records on the biggest table). I'm trying to select a ...
user avatar
2 votes
1 answer
266 views

OVERLAPS for Informix

Does Informix have a function equivalent to OVERLAPS, which finds out whether or not two time intervals have any time in common?
newenglander's user avatar
  • 1,055
2 votes
0 answers
47 views

Informix Innovator-c and SAN storage: How can we increase Innovator-C parallelism in high latency disk IO situations

When using Innovator-C against SAN storage (which is unavoidable in some cloud infrastructure situations) on a Linux (CentOS 6.x) system, write latency becomes the bottleneck in performance (we've ...
user3546411's user avatar
1 vote
1 answer
221 views

Microsoft Distributed Transactions from Informix with Linux

I need to create an extraction job from an Informix database, the requirement is that I need to enable transactions in SSIS. All of the MSDTC settings of the target machine and the SQL Server database ...
Rafael Lima's user avatar
1 vote
3 answers
1k views

Why can't I create a database in a 107GB dbspace I created for my instance?

11.70.FC6 Developer Edition on Windows 7 Pro 64-bit: Downloaded via IIUG link and installed it. I created a custom DSS instance named "server" with a 128GB dbspace named "datadbs". Install wizard ...
Joe R.'s user avatar
  • 127
1 vote
2 answers
725 views

Rownum equivalent in Informix SQL?

I want to explore (=looking at the first 100 rows) databases using the Informix SQL dialect. In Oracle SQL I would use SELCT* FROM table_name WHERE ROWNUM < 100 In Postgress SQL I would use SELCT* ...
Qaswed's user avatar
  • 121
1 vote
2 answers
2k views

serial field issue

Q: I use a serial as a datatype for my primary keys in my tables. I insert a lot of data then truncate these data one after one many times to test the data entry. My question is: Is there any ...
Anyname Donotcare's user avatar
1 vote
2 answers
728 views

case statement using characters for sum value

I have a table that I am trying to pull a single return value on a select saying that either compressions on table is enabled or disabled by using a sum calculator for column value compressed must be ...
Christopher Karsten's user avatar
1 vote
1 answer
74 views

Insert constraint issue

Using IBM Informix database. If I specify order_no manually, the statement works but if I try to increment it within the query, it fails. Table structure patkey serial notetype char(4) order_no int ...
Tsukasa's user avatar
  • 319
1 vote
1 answer
2k views

Informix Older than 15 Minutes

How can I do this MySQL statement in an IBM Informix? select type, channel, teilnr, starttime, endtime, usedtime, host from online_time where starttime < DATE_SUB(NOW(),INTERVAL 15 MINUTE) order ...
LStrike's user avatar
  • 149
1 vote
1 answer
3k views

Informix Server Rejected Connection

What are possible causes for the following error? DB Error: connect failed [nativecode=08004 [unixODBC][Informix] [Informix ODBC Driver [Informix]Server rejected the connection.] I have doubled ...
user avatar
1 vote
1 answer
314 views

Informix: is it necessary to update statistics on views?

In Informix I have a partitioned table for performance reasons. One contains the latest data and another the historical data. Also, there is a view which contains data from both. When I update ...
ctutte's user avatar
  • 69
1 vote
1 answer
141 views

How can I control the order of a query regarding to inner join

I have 3 tables. They have table_a 5m records table_b 20m records table_c 20m records I'm performing the query below. SELECT * FROM table_a a INNER JOIN table_b b ON a.a_id = b.a_id AND b....
Marcus Thornton's user avatar
1 vote
1 answer
5k views

What joins are available in Informix 9.2?

I'm testing some queries with an old version of Informix (9.2.1) and can't seem to execute even the most basic queries containing RIGHT OUTER JOIN or FULL OUTER JOIN. Tried several variations (RIGHT ...
newenglander's user avatar
  • 1,055
1 vote
3 answers
348 views

count duplicate records and select first 2

I have troubling issue where I want to only select the first 2 chunks of every dbspace created. query used: SELECT a.dbsnum, b.chknum, a.name, b.fname FROM sysdbspaces a, syschunks b where a.dbsnum = ...
Christopher Karsten's user avatar
1 vote
1 answer
609 views

Informix create procedure to query records and drop if value does not exist using select

I am trying to create a procedure that will query a table column to retrieve table names and using this in proc to run a query to do a select if it exists. If it does not exist, delete row(s) from ...
Christopher Karsten's user avatar
1 vote
1 answer
665 views

Informix trigger to delete records before insert

I am trying to create a trigger to simply delete records from systables_growth table before an insert happens based on the where clause used on the date_added column for records older than a certain ...
Christopher Karsten's user avatar
1 vote
1 answer
2k views

Create column for values between a range sql

What I want to achieve is to create a new column based on the num_extents between a range and only print the table name below. This is what I have [table1]: This is what I want [table2]: I used a ...
Christopher Karsten's user avatar
1 vote
1 answer
2k views

Informix instance not starting

When I tried to start the Informix instance in Linux using oninit, it says WARNING: server initialization failed, or possibly timed out (if -w was used). Check the message log, online.log, for errors....
Manohar S's user avatar
1 vote
1 answer
2k views

Informix Specific Table export

I'm busy trying to migrate data between informix databases using the dbexport and dbimport utilities. I know that in PostgreSQL I can use the following pg_dump –a –t table database > /destination/...
TheLovelySausage's user avatar
1 vote
1 answer
334 views

Informix Migrate Schema

I'm busy trying to migrate an informix database schema (with no data) to an informix database running on a different server. I have exported the schema from the database using dbschema -t all -d ...
TheLovelySausage's user avatar
1 vote
2 answers
993 views

SSRS and Informix connection

I have the IBM drivers installed, created a data source that successfully tests from SSRS front end. Create a report from my machine which also has the drivers installed and the ODBC testing ...
Tsukasa's user avatar
  • 319
1 vote
1 answer
37 views

Retrieve one of two possible values from 1 table in INFORMIX

I have a table which for the same key, it will always have one record with value 'P' but sometimes that same key could have two records one with value 'P' and one with value 'L'; I need to create a ...
Mitra's user avatar
  • 11
1 vote
1 answer
1k views

Moving the IBM Informix IFMXDATA directory to another machine

I'm a total newbie when it comes to Informix DBs, and I'm stuck with what looks like files that contain an Informix database. I'd like to know if it's possible to add or import these files into an ...
r41n's user avatar
  • 111