Notes in Informix

#in squirrel you can disable the colon character of parameters inline request by unloading the plugin sqlparam

#enter in interactive mode only commands
#ctrl+d for closing the program
dbaccess - -

#command to connect a DB inside to dbaccess - -:
database your_db;


#import only the schemas of the database
dbschema -t all -d sicobf3 -nw -q -ns dboutput3.txt

#schema data model of sysmaster
#see http://www.informix.com.ua/articles/sysmast/sysmast.htm

#list databases
select * from sysmaster:sysdatabases;


#create database in informix with support of autocommit in jdbc
#see https://bugs.eclipse.org/bugs/show_bug.cgi?id=218190
#see https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0368.htm
#see https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0369.htm
create database sicobf3 with log;


#see http://stackoverflow.com/questions/36165976/invalid-default-value-for-column-variable-ed-fec-sol
#Below are to know how was set the variables from your client, especially the date format
SELECT      envses_name, envses_value
FROM        sysmaster:sysenvses
WHERE       envses_name IN (
                            'DBDATE', 
                            'GL_DATE', 
                            'CLIENT_LOCALE'
                            )
            AND envses_sid = DBINFO('sessionid')
ORDER BY    DECODE(envses_name,
                'DBDATE',           0,
                'GL_DATE',          1,
                'CLIENT_LOCALE',    2
            );
            
SELECT * FROM        sysmaster:sysenvses WHERE   envses_sid = DBINFO('sessionid');
SELECT  env_name, env_value FROM    sysmaster:sysenv WHERE   env_name = 'DBDATE';
SELECT  CURRENT::DATE FROM    sysmaster:sysdual;
SELECT  TODAY FROM    sysmaster:sysdual;

#set properties in the jdbc driver of informix, in this case GL_DATE since DBDATE is deprecated
#see https://groups.google.com/forum/#!msg/comp.databases.informix/iHH3LGuiP1o/AA5czvKNYSAJ
#see https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_263.htm
#see https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_266.htm
jdbc:informix-sqli://192.168.32.128:9088/sicobf3:informixserver=ol_informix1210;GL_DATE=%m/%d/%Y
#or with DBDATE=MDY4

#to know the current database used
select * from sysmaster:syssqlcurses

#to know the version of the DB
SELECT  DBINFO('version','full') FROM    sysmaster:sysdual;

#To know the schema of a table in informix:
dbschema -d DB_sicobf3 -t Table_t5641rstockestab
Advertisements