dbload with "datetime year to second" datatype
Marc Demhartner could not load a datetime year to second value like 2019.10.03 05:24:24 with dbload on 14.10.FC3 (error: non-numeric character in datetime). SangGyu Jeong solved it by setting the GL_DATETIME format and USE_DTENV=1. Paul Watson quoted the manual that USE_DTENV is needed for localized DATETIME values in load and unload operations.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello,
we have some issue loading a flat file via DBload.
Problem is a column with this date and time format:
|2019.10.03 05:24:24|
Error message with 14.10FC3 is:
(dbload): Non-numeric character in datetime or interval.
We´re using DBDATE env: "Y4MD." - and the data type is "datatime year to seond" for that column.
Any idea? Do we need to set the "DBTIME" env. variable ?
------------------------------
Marc Demhartner
------------------------------
#Informix
Hello Marc,
You can do this by setting the USE_DTENV and GL_DATETIME environment variables.
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.glsug.doc/ids_gug_085.htm
[informix@db2 ids1410fc3]$ onstat -
IBM Informix Dynamic Server Version 14.10.FC3IE -- On-Line -- Up 15 days 21:10:35 -- 2631304 Kbytes[informix@db2 ids1410fc3]$ echo "create table test (v1 char(10), dt datetime year to second)" | dbaccess stores_demo
Database selected.
Table created.
Database closed.
[informix@db2 ids1410fc3]$ cat test.unl
|2019.10.03 05:24:24|
[informix@db2 ids1410fc3]$ cat test.cmd
FILE test.unl DELIMITER '|' 2;
INSERT INTO test;[informix@db2 ids1410fc3]$ export GL_DATETIME="%Y.%m.%d %H:%M:%S"
[informix@db2 ids1410fc3]$ export USE_DTENV=1
[informix@db2 ids1410fc3]$ dbload -d stores_demo -c test.cmd -l errlog
DBLOAD Load Utility INFORMIX-SQL Version 14.10.FC3
Table test had 1 row(s) loaded into it.
[informix@db2 ids1410fc3]$ unset GL_DATETIME
[informix@db2 ids1410fc3]$ echo "select * from test" | dbaccess stores_demo
Database selected.
v1 dt
2019-10-03 05:24:24
1 row(s) retrieved.
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Oh, yes, that solved my problem. Thank you! One question... what is that USE_DTENV=1 for? Never saw that before ------------------------------ Marc Demhartner ------------------------------
As per manual In a database where the GL_DATETIME environment variable has a nondefault setting, you cannot process localized DATETIME values correctly in some operations that load or unload data unless USE_DTENV is enabled.
As Paul mentioned, you can find a description of USE_DTENV in the link. I've seen similar question from another community, so I remember that environment variable. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.glsug.doc/ids_gug_085.htm ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------