Informix

nested-group-icon.png

DB2

Expand all | Collapse all

dbload with "datetime year to second" datatype

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

    Posted Thu July 09, 2020 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
    ------------------------------


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

    Posted Thu July 09, 2020 07:32 AM
    Edited by SangGyu Jeong Thu July 09, 2020 07:33 AM

    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 Thu July 09, 2020 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
    ------------------------------



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

    Posted Thu July 09, 2020 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.

     






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

    Posted Thu July 09, 2020 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
    ------------------------------