I'm running Informix SE 7.25 on Red Hat Linux 7.3. All DATETIME variables are in the default YEAR TO FRACTION(3) format. The GLS environment variable setting is 'GL_DATETIME=%Y%m%d %T%F3'.

Since it is an SE environment, none of the ON* utilities are available for backup/restore capability, and I am stuck relying on dbexport and dbimport. (...unless you have an alternate suggestion that does not involve buying some 3rd party tool). The entire section of 'The Informix Handbook' that is devoted to backup presumes you are using Dynamic Server and says nothing about SE (translation: one more way this book is a disappointment and less value than advertised).

Using DBLOAD, I discovered that, regardless of the GL_DATETIME settings, all DATETIME values in CSV load files must be in YYYY-MM-DD HH:MMS.FFF format (hyphens included).

Another notable wrinkle was that, if I added the decimal point to the GL_DATETIME variable (%T.%F3), I get the proper formatting in DB-Access query results, but when stored procedures run, they return double decimal points (funky, but since we use stored procedures to control all access to the DB by the application, we had to adopt the format that allows it to work). I just provide this info in case you run into this feature and wonder what is up.

Anyway, I ran a full dbexport to back up the dev database and then made a copy of it, changed the name of the directory, the schema file, and the line in the schema file that explicitly names the DB, and then tried to use dbimport to rebuild a copy of the database.

[informix@idu2 doppelg.exp]$ dbimport -c doppelg -i /home/informix/export/ -l /var/log/informix/doppelg
{ DATABASE doppelg delimiter | }

grant dba to "root";
grant connect to "svs3500";

{ TABLE "informix".t_node_type row size = 91 number of columns = 5 index size = 12
}
{ unload file name = t_nod00100.unl number of rows = 5 }

create table "informix".t_node_type
(
node_type_id serial not null ,
node_type nchar(3) not null ,
node_type_desc nchar(64) not null ,
node_version nchar(10) not null ,
dt_last_update datetime year to fraction(3)
default current year to fraction(3) not null ,
primary key (node_type_id) constraint "informix".pk_node_type
);
*** put loadcur
1261 - Too many digits in the first field of datetime or interval.

The first input file looks like this:

1|SMC|System Management Console|0.5|20030129 13:51:25000|
2|SSU|Spherical Sensor Unit|0.5|20030129 13:51:25000|
3|IDU|Image Database Unit|0.5|20030129 13:51:25000|
4|MNT|Maintenance Console|0.5|20030129 13:51:25000|
5|BRF|Briefing Console|0.7|20030527 23:20:53539|

I have wrestled with DATETIME data types for almost a year now, and have found that their behavior is pretty inflexible and almost always just a touch different than any of the sources (Informix manuals and the Handbook are my primary sources) claim they do. Trying to find the Truth tends to be trial-and-error and can be extremely time-consuming before you get there.

IMHO, if you cannot just import what you exported, something is wrong with the database. It should just work. However, now that it doesn't, I am seeking guidance from the gurus who have years of experience. What am I doing wrong? Is there a better way to do backups of the DB and transaction logs while using SE without having to go shopping?

Please help if you can. As always, if I get past this, I'll be happy to share the solution so others who follow can avoid the agony.