Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  dbload with "datetime year to second" datatype

    Posted 07/09/20 06:45 AM
    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


  • 2.  RE: dbload with "datetime year to second" datatype

    Posted 07/09/20 07:32 AM
    Edited by System Admin 01/20/23 04:51 PM

    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
    ------------------------------



  • 3.  RE: dbload with "datetime year to second" datatype

    Posted 07/09/20 09:03 AM
    Oh, yes, that solved my problem. Thank you!

    One question... what is that 

    USE_DTENV=1

    for? Never saw that before

    ------------------------------
    Marc Demhartner
    ------------------------------

    Original Message


  • 4.  RE: dbload with "datetime year to second" datatype

    Posted 07/09/20 09:08 AM

    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.

     




    Original Message


  • 5.  RE: dbload with "datetime year to second" datatype

    Posted 07/09/20 09:43 AM
    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
    ------------------------------

    Original Message


Related Content