I'm able to create a new dbspace using the semi-gui tool onmonitor (running a headless Linux server) But how can I do this from cmd-line / script? It seems you can use onspaces like

onspaces -c -d $DBSname -p $DBSfile -o 0 -s 153600

But AFAIK it require the IDS server is running in one of these modes online, quiescent or administration and this seems only possible to start the server if there already exist a dbspace. Or am I missing something? Can you really only setup a new server using the init menu in onmonitor?

I am converting an Informix SE to an IDS database. The programs I have use ODBC System DSNs to communicate with the database. For SE, we use the sesoctcp protocol. The program code, would open a Recordset from the DSN, loop through each record and save each record to a local AccessDB (execute INSERT INTO), and then perform a MoveNext on the Recordset. For IDS, we use the onsoctcp protocol. With the same code, the program throws an error on MoveNext. I believe the Recordset is being closed... I added code to perform a MoveLast/MoveFirst on the Recordset before the loop and the MoveNext does not throw an error. This change will require me to update potentially 400 programs. On the other hand, I can forego the code changes and instead change the DSN Cursor Behavior to "1 - Preserve" and the MoveNext does not throw an error. My limited understanding is that if I leave it on "0 - close", this tells the driver to decide when to close the recordset instead of letting the program decide. The only documentation I could find for Cursor Behavior doesn't explain the values: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.odbc.doc/ids_odbc_069.htm So... what are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN?

I have an Informix database. I want to take a snapshot, make some changes, then restore the database from that snapshot. (If Informix supported nested transactions, I would be using transactions.) The database is of moderate size (it can run on a consumer laptop) and there is no replication. It is critical that this backup/restore procedure is as fast as possible (I would like to do a backup-modify-restore cycle several times per second if possible). Only a small part of the database would be modified between the backup/restore points. Reading around a bit, it sounds like onupload/onload commands are the way to go. I can't quite figure out how to use them. Can you please show me the way to take a snapshot of a running database, and restore that database after some changes? (All on one machine.) Or if there is a better way to achieve this effect? (Perhaps there is a way to fork the process and all its files in a copy-on-write manner and have the child process serve a clone of the database on a different port?) If so please let me know.

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. I have identified that running the below gives close to what I want, but excludes the hours:

select b.run_duration, 
       floor(run_duration / 60) || ':' || lpad(mod(run_duration, 60), 2, '0') as run_duration_time 
from ph_task a, ph_run b 
where a.tk_id = b.run_task_id 
order by run_duration DESC

Output:

-none
24:09

What I would like to see is:

-none
00:24:09

How can I customize my SQL to provide that?

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.DECLARE first_date, last_date DATE; However, I keep getting a syntax error on the declare line.

I have inherited an Informix installation that contains a large amount of business logic in the form of procedures. The company also uses SQL Server, MySQL and Postgres for various roles. We're very interested in moving as far away from Informix as possible. Preferably to SQL server or Postgres. Migrating the data isn't something I'm worried about, but the business logic that is contained in thousands of very complex, Informix-dialect procedures is something that would be prohibitively expensive to migrate. The idea I have, which may be somewhat unhinged, is to use Informix as an "application server" of sorts -- the business logic can't move, but perhaps the data can. So that would involve not rewriting any of these stored procedures, and leaving them running on Informix, but moving the data so that the tables that they reference can be accessed transparently, even though they reside on a different RDBMS. MySQL has pluggable storage engines, SQL Server [has linked servers (supporting ODBC)](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver16) , and there is a PostgreSQL [Foreign Data Wrapper](https://github.com/credativ/informix_fdw) that specifically connects to Informix. However, this would only allow those servers to query Informix... what I want to do is have Informix query any of those servers so that the Informix procedures don't have to be changed, but the data can be moved. So those "foreign data methods" have the right idea but the wrong direction. The [Informix Documentation here](https://www.ibm.com/docs/en/informix-servers/14.10?topic=database-specifying-object-in-cross-server-query) specifies that a cross-server query can be made, and it mentions ANSI compliance, but I'm inclined to believe that they are referring to foreign servers *running Informix in ANSI-compliant mode* rather than any (mostly?) ANSI compliant database. There does seem to be a mechanism for "user-defined access methods" which seems a bit like a "foreign data wrapper" in PostgreSQL: > You can create user-defined primary access methods that provide SQL access to non-relational and other data that does not conform to built-in access methods. For example, a user-defined access method might retrieve data from an external location or manipulate specific data within a smart large object. However I can't find any open-source (or other) user-defined access methods that will allow me to query a different RDBMS from Informix. It would be a huge help if anyone could point me in the right direction, or can recommend a tool that will correct translate Informix stored procedures to any other dialect (but I know that's a long shot!). The speed of these queries is less relevant than in most applications because it's mostly done in batch processes, not interactively, so I'm not concerned about the performance hit from requiring Informix to query these other servers.

I have an example query below that I am running that is only on one column, but multiple rows can be displayed: SELECT dbspace FROM tab_info_script WHERE database = 'dwh_vodac' AND tabname = 'ft_import_lock' Example output: dbspace2 dbspace4 idxdbs2 dbspace3 idxdbs3 dbspace1 dbspace5 My goal is try and convert the multiple rows into a single row output such as below: dbspace2 dbspace4 idxdbs2 dbspace3 idxdbs3 dbspace1 dbspace5 This is an informix database I am working with, but many examples I see online are for MySQL or SQLServer, which is very different. So basically I am only working with one column that can generate multiple rows, but instead of multiple rows I want the output to be one row. Is that possible?

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 | +----+-----------+ | a | b | | b | c | | c | d | | d | e | | e | f | | f | x | +----+-----------+ Expected Result: +----+-----------+ | Parent | Child | +----+-----------+ | a | b | // As b is the child of a, all the descendants of b | a | c | // are also descendants of a. | a | d | | a | e | | a | f | | a | x | | b | c | // As c is the child of b, all the descendants of c | b | d | // are also descendants of b. | b | e | | b | f | | b | x | | c | d | | c | e | | c | f | | c | x | | d | e | | d | f | | d | x | | e | f | | e | x | | f | x | +----+-----------+ Informix 11.50 doesn't have recursive CTEs (common table expressions). CONNECT BY can help about recursion levels, paths etc., but I can not get expected result using CONNECT BY. Any idea?

Assuming a user has sufficient privileges on the server, is it possible to copy a database in its entirety within the same server/host, using SQL only (i.e. without having shell access to the Linux server running Informix)? Not just tables and views but also stored procedures? On postgresql, it would be something like CREATE DATABASE copy_db WITH TEMPLATE original_db; I ask because I am interested in creating a 'master' copy of a database that can be replicated on demand when running integration tests. I am on Informix Dynamic Server 14.

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* FROM table_name limit 100 - I also tried SELCT* FROM table_name first 100 None of these methods works for Informix. **What I found:** - When looking at the documentation (https://www.ibm.com/docs/en/informix-servers/12.10?topic=programming-retrieve-multiple-rows) I only find explanations how it works internally, but not how to do it on the user side. - This question (https://stackoverflow.com/questions/119278/row-numbers-for-a-query-in-informix) only covers the issue that rum numbers shall be added to a table. **One side note:** the programm I use will do the SQL call on multiple databases and combine the resulting tables in one table.

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 connection in a test environment first. **What I have Done** - Installed the latest Informix OLE DB Driver on the SQL Server host machine - Configured a System DSN for the IBM Informix ODBC Driver Setup - Tested the connectivity of the DSN settings (test is good) after changing the locale settings so the client and server match, and server is correct. - Attempted to create the linked server in two ways below. - Googled and read everything I could to resolve this without success. 1) Using IBM Informix instructions with OLE DB Provider in which I get the following error (regardless if I use a DNS in provider string): > Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "UCCX". OLE DB provider "Ifxoledbc" for linked server "UCCX" returned messazge "EIX000: (-23197)" Database locale information mismatch" (Microsoft SQL Server, Error: 7303) *The DB Locale is en_US.57372* 2) Using Microsoft OLE DB Provider for SQL Server as explained in this blog which returns the error: > Named Pipes Provider: Coould not open a connection to SQL Server. OLE DB provider SQLNCLI11 for linked server UCCX returned message "Login timeout expired" > OLE DB provider SQLNCLI11 for linked server UCCX retunred a message "A network-related or instance-specific error has occured while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. SQLNCLI11 for linked server UCCX returned message "Invalid connection string attribute" I have very little experience with Informix which should be apparent and this isn't a local installation, it's a Cisco instance as you probably gleaned from the blogs I was using. Any advice would be appreciated.

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 procedure results about 27 columns and I want only few columns of it for reporting. I try this syntax: select AGENT_NAME, AGENT_LOGIN_ID from TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00')) This Result such Error: java.sql.SQLException: Illegal SQL statement in SPL routine. Next I try this syntax: select AGENT_NAME, AGENT_LOGIN_ID from TABLE(MULTISET{sp_agent_call_summary ('2014-02-04 04:00:00', '2014-02-04 23:00:00')}) Result: java.sql.SQLException: Function (informix.sp_agent_call_summary) returns too many values. then I tried this: select AGENT_NAME, AGENT_LOGIN_ID from TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00')) (AGENT_NAME, AGENT_LOGIN_ID, other columns that are returned by SP) Result: java.sql.SQLException: Illegal SQL statement in SPL routine.

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 = b.dbsnum GROUP BY 1,2,3,4 ORDER BY 1,2 Output: dbsnum 1 chknum 1 name rootdbs fname /dev/informix/rootdbs01 dbsnum 1 chknum 344 name rootdbs fname /dev/informix/chunk1081 dbsnum 1 chknum 965 name rootdbs fname /dev/informix/chunk1280 dbsnum 1 chknum 1099 name rootdbs fname /dev/informix/chunk1281 What I want is: dbsnum 1 chknum 1 name rootdbs fname /dev/informix/rootdbs01 dbsnum 1 chknum 344 name rootdbs fname /dev/informix/chunk1081 I am trying to put logic together to select the first 2 chunks created for example from each dbspace that exists. rootdbs has a total of 4 chunks I only want the first 2. I can achieve this using a korn shell script: for i in echo "output to pipe cat without headings select unique(dbsnum) from sysdbspaces" | dbaccess sysmaster 2> /dev/null | sed '/^$/d' do fname=`echo "output to pipe cat without headings select a.fname from (select first 2 a.dbsnum, b.chknum, a.name, b.fname FROM sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum AND a.dbsnum = '$i' --AND a.is_temp = 1 GROUP BY 1,2,3,4 ORDER BY 1,2) a" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'` echo "$fname" done

Is the analyzer discussed here same as Lucene analyzers? I am confused because most of them show similar properties to Lucene analyzer but the blog posts don't say a word about Lucene; instead they talk about something made by IBM called Informix. https://www.ibm.com/docs/en/informix-servers/12.10?topic=analyzers-snowball-analyzer I just want to know about these 5 types of analyzer: stopword, simple, standard, whitespace and snowball. Are their properties the same as Lucene? It looks like they're same although their names aren't exact.

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. However, when I query the syscolumns table I see that columns, which store BLOB data, actually have values of 297, in my case. That page linked above does not even have number "297" anywhere on its page. I found another page, titled "Data Type Constants" but here again, number "297" is not even present anywhere on the page. It appears that documentation's list of data types is not complete. Is there a (more) complete list of column types in Informix?

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 company, requires OLEDB and doesn't support ODBC. The colleague,who is currently tasked with managing the implementation, has a lot of other work piled up and so the implementation will be delayed. In the meantime, I've been tasked to figure out how to properly configure the Windows 10 Client PC (which will run the program) for the connection. I have very little experience with databases, so I've searched for about 2 weeks for answers in various documentations and forums. So far, I had mixed success. **What has already been done:** - The coledbp.sql- script has been run against the sysmaster table on the database - Informix Client SDK and Microsoft Data Access Components have been installed on the Client PC. - Using setnet32 the registry entries for host:port have been set. - The driver ifxoledbc has been registered. - ODBC-Datasources (both 64-Bit and 32-Bit) have been created and tested (they work fine). Various Tests ------------- For all my tests I've been using the Connection String: > "Provider=ifxoledbc;Data Source=mydatabase@myserver;User ID=myuser;Password=mypassword; First I tried to test the connection with an .udl-File. I select the IBM Informix OLE DB Provider and enter the data source and userdata. When I test the connection I get an Error like: > "Initialisation of the provider failed. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." From what I could find, there are many possible reasons for this error, and I don't know if can get a more specific error message somehow. With the Microsoft OLE DB Provider for ODBC Drivers and the ODBC-datasource for the Server the test connection worked. In Microsoft Excel (32-Bit) I also tried both the first mentioned Connection String and the Connection String, which is built when entering the infos in the .udl settings. Same Error with both cases. For my next test I used a .vbs - script, which was presented in the IBM Informix Developers Handbook: On Error Resume Next set conn=createobject("ADODB.Connection") conn.connectionstring = "Provider=ifxoledbc;DataSource=mydatabase@myserver;UserID=myuser;Password=mypassword;" conn.open If Err Then WScript.Echo "Error!! Open"+conn.Errors(0).Description Else WScript.Echo "Connected" conn.close end if This results once again results in the "multi-step"-Error. The last thing I tried was using the Rowset Viewer of MDAC. I used the exact same input as with the .udl - File. But to my surprise it managed to connect! (It returned the error "Unknown Interface", but still worked) It correctly read the entries I set with the setnet32-Tool. (I checked this by temporarily removing one of the Entries, which resulted in an additional error and no connection) **Now I have a few questions:** - Is there anything additional to configure on the Client PC? - How does the Viewer succeed, where every other test fails? - Is there any way to get a more accurate error code? - Are there major flaws in my tackling of the problem? I appreciate every suggestion anybody can give me.

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, cancel_reason_no SMALLINT ); Once the shipping date is arranged, the deal_dt and deal_reason_no will be set and cancel_dt and cancel_reason_no is null. However product_id is not unique in the table since the every product shipping can be cancelled. Once cancelled cancel_dt and cancel_reason_no will be set. If the cancelled product is bought by other customer then the shipping is recorded in the new row, and product_name can be changed in the new shipping. I want to query the product_id and deal_dt whose deal_reason_no is 1, 3 or 5 and deal_dt is between '2014-04-01 00:00:00' and '2014-04-11 00:00:00' and the **latest previous** deal_dt and latest previous cancel_dt whose cancel_reason_no is 2 or 4 for the product_id. I think the SQL could be like this: SELECT C.product_id as product_id, C.deal_dt as deal_dt, R.deal_dt as previous_deal_dt, R.cancel_dt as previous_cancel_dt, FROM product_shipping C LEFT JOIN product_shipping R ON (???) WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5') AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00' But I'm not sure how to join to get latest previous record. I'm using Informix 11.70. Edit: Add my idea. Is this reasonable? SELECT C.product_id as product_id, C.deal_dt as deal_dt, MAX(R.deal_dt) as previous_deal_dt, MAX(R.cancel_dt) as previous_cancel_dt, FROM product_shipping C LEFT JOIN product_shipping R ON (C.product_id = R.product_id and TO_CHAR(R.cancel_reason_no) MATCHES ('2', '4') and R.cancel_dt <= C.deal_dt) WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5') AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00' GROUP BY 1,2

I have some necro-project using Informix Dynamic Server Version 7.31.UD1. I need to backup-restore database between servers. The thing is, the onload just silently exits and doesn't create a database, giving no errors. I've successfully used this method on the same server to perform a backup/restore routine, so I'm almost sure I'm doing it right:

[root@s10-zone informix]# rm -rf /tmp/.dbld                                 
[root@s10-zone informix]# mkfifo /tmp/.dbld                                 
[root@s10-zone informix]# cat /opt/oracle/exchange/perms32_unl > /tmp/.dbld


[informix@s10-zone ~]$ onload -t /tmp/.dbld -b 100 -d default rist
Please mount tape and press Return to continue ...

The only explanation so far is that the onunload backup was don on a i386 arch, and I'm doing onload import on an amd64 server, - am I right assuming it fails because of this ? The official documentation says *"If the page sizes are different, onload fails. If the alignment or numeric data types on the two computers are different (for example, with the most significant byte as last instead of first, or different float-type representations), the contents of the data page could be misinterpreted."*, but the page size of identical (4K), not so sure about float representation though.