Oninit cdi / Archive
Talk to an expert →

Re: DBINFO parameter values

Posted in 2006

Topics: Stored Procedures & SPL, Data Types & Schema Design, Versions, Editions & End-of-Life

Richard Harnden — — source: Usenet: comp.databases.informix

Doug Lawry wrote: > From classics@iiug.org "convert integer representation of datetime": > > Mladen Jova.... said: > >>On 01/09/2006 11:01 PM, Henry Wang .... wrote: >> >>>Is there any function to convert an integer value representing the >>>datetime since 1970/1/1 to a DATETIME data type in stored procedure. >>>I'm running IDS 10.00.UC3X9 on Linux. >> >>DBINFO('utc_to_datetime', <UTC_time>)>> >>where <UTC_time> is UTC time value >>or integer column name with UTC time value. > > > Top marks! This also works on 9.4, despite not being in the InfoCenter or > PDFs. How many other undocumented DBINFO parameter values are there? Could > anyone supply a complete list? > That will work for 7.x too. Look at sysmaster.sql (sysdatabases) Probably easiest to get dbinfo wrong and then "finderr 728"

↪ replying to Richard Harnden
Doug Lawry — — source: Usenet: comp.databases.informix

"Richard Harnden" <richard.harnden@lineone.net> wrote in message news:v4OdnQNCK6SIb1neRVnyhQ@pipex.net... > > Doug Lawry wrote: >> >> From classics@iiug.org "convert integer representation of datetime": >> >> Mladen Jova.... said: >> >>> On 01/09/2006 11:01 PM, Henry Wang .... wrote: >>> >>>> Is there any function to convert an integer value representing the >>>> datetime since 1970/1/1 to a DATETIME data type in stored procedure. >>>> I'm running IDS 10.00.UC3X9 on Linux. >>> >>> DBINFO('utc_to_datetime', <UTC_time>)>>> >>> where <UTC_time> is UTC time value >>> or integer column name with UTC time value. >> >> Top marks! This also works on 9.4, despite not being in the InfoCenter >> or PDFs. How many other undocumented DBINFO parameter values are there? >> Could anyone supply a complete list? > > That will work for 7.x too. Look at sysmaster.sql (sysdatabases) > > Probably easiest to get dbinfo wrong and then "finderr 728" Good thinking! On 9.4, this generates: -728 Unknown first argument of dbinfo(<argument>). Check that the first argument to dbinfo() is a quoted string that corresponds to one of the following values: 'dbspace', 'version', 'sqlca.sqlerrd1', 'sqlca.sqlerrd2', 'sessionid', 'coserverid', 'utc_to_datetime', 'utc_current', 'get_tz', or 'dbhostname'. So there are two more undocumented ones: DBINFO('utc_current') -- returns an INTEGER, presumably a UTC time DBINFO('get_tz') -- appears to return the TZ environment variable I have discovered that you can get a full listing using: strings $INFORMIXDIR/bin/oninit | grep DBINFO This includes the documented 'serial8' missed by 'finderr 728': DBINFO ('dbspace', DBINFO ('sqlca.sqlerrd1') DBINFO ('sqlca.sqlerrd2') DBINFO ('utc_to_datetime', DBINFO ('utc_current') DBINFO ('get_tz') DBINFO ('serial8') DBINFO ('sessionid') DBINFO ('dbhostname') DBINFO ('version', 'server-type') DBINFO ('version', 'major') DBINFO ('version', 'minor') DBINFO ('version', 'os') DBINFO ('version', 'level') DBINFO ('version', 'full') Jonathan: who maintains the documentation at IBM? -- Regards, Doug Lawry www.douglawry.webhop.org

↪ replying to Doug Lawry
Jonathan Leffler — — source: Usenet: comp.databases.informix

Doug Lawry wrote: > "Richard Harnden" <richard.harnden@lineone.net> wrote in message > news:v4OdnQNCK6SIb1neRVnyhQ@pipex.net... > >>Doug Lawry wrote: >> >>>From classics@iiug.org "convert integer representation of datetime": >>> >>>Mladen Jova.... said: >>> >>> >>>>On 01/09/2006 11:01 PM, Henry Wang .... wrote: >>>> >>>> >>>>>Is there any function to convert an integer value representing the >>>>>datetime since 1970/1/1 to a DATETIME data type in stored procedure. >>>>>I'm running IDS 10.00.UC3X9 on Linux. >>>> >>>>DBINFO('utc_to_datetime', <UTC_time>)>>>> >>>>where <UTC_time> is UTC time value >>>>or integer column name with UTC time value. >>> >>>Top marks! This also works on 9.4, despite not being in the InfoCenter >>>or PDFs. How many other undocumented DBINFO parameter values are there? >>>Could anyone supply a complete list? >> >>That will work for 7.x too. Look at sysmaster.sql (sysdatabases) >> >>Probably easiest to get dbinfo wrong and then "finderr 728" > > > Good thinking! On 9.4, this generates: > > -728 Unknown first argument of dbinfo(<argument>). > > Check that the first argument to dbinfo() is a quoted string that > corresponds to one of the following values: 'dbspace', 'version', > 'sqlca.sqlerrd1', 'sqlca.sqlerrd2', 'sessionid', 'coserverid', > 'utc_to_datetime', 'utc_current', 'get_tz', or 'dbhostname'. > > So there are two more undocumented ones: > > DBINFO('utc_current') -- returns an INTEGER, presumably a UTC time > DBINFO('get_tz') -- appears to return the TZ environment variable> > I have discovered that you can get a full listing using: > > strings $INFORMIXDIR/bin/oninit | grep DBINFO > > This includes the documented 'serial8' missed by 'finderr 728': > > DBINFO ('dbspace', > DBINFO ('sqlca.sqlerrd1') > DBINFO ('sqlca.sqlerrd2') > DBINFO ('utc_to_datetime', > DBINFO ('utc_current') > DBINFO ('get_tz') > DBINFO ('serial8') > DBINFO ('sessionid') > DBINFO ('dbhostname') > DBINFO ('version', 'server-type') > DBINFO ('version', 'major') > DBINFO ('version', 'minor') > DBINFO ('version', 'os') > DBINFO ('version', 'level') > DBINFO ('version', 'full')> > Jonathan: who maintains the documentation at IBM? The Information Development (ID) team - aka Tech Pubs. As the manuals say, you can send them messages about manual content at docinf.antispam@us.antispam.ibm.com (obviously, remove the antispam and the preceding dot) - and they are usually pretty responsive about it. They're under a deadline crunch this week, though. I've cc'd this message to them. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

↪ replying to Jonathan Leffler
Doug Lawry — — source: Usenet: comp.databases.informix

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:43C5347F.9030403@earthlink.net... > Doug Lawry wrote: >> "Richard Harnden" <richard.harnden@lineone.net> wrote in message >> news:v4OdnQNCK6SIb1neRVnyhQ@pipex.net... >> >>>Doug Lawry wrote: >>> >>>>From classics@iiug.org "convert integer representation of datetime": >>>> >>>>Mladen Jova.... said: >>>> >>>> >>>>>On 01/09/2006 11:01 PM, Henry Wang .... wrote: >>>>> >>>>> >>>>>>Is there any function to convert an integer value representing the >>>>>>datetime since 1970/1/1 to a DATETIME data type in stored procedure. >>>>>>I'm running IDS 10.00.UC3X9 on Linux. >>>>> >>>>>DBINFO('utc_to_datetime', <UTC_time>)>>>>> >>>>>where <UTC_time> is UTC time value >>>>>or integer column name with UTC time value. >>>> >>>>Top marks! This also works on 9.4, despite not being in the InfoCenter >>>>or PDFs. How many other undocumented DBINFO parameter values are there? >>>>Could anyone supply a complete list? >>> >>>That will work for 7.x too. Look at sysmaster.sql (sysdatabases) >>> >>>Probably easiest to get dbinfo wrong and then "finderr 728" >> >> >> Good thinking! On 9.4, this generates: >> >> -728 Unknown first argument of dbinfo(<argument>). >> >> Check that the first argument to dbinfo() is a quoted string that >> corresponds to one of the following values: 'dbspace', 'version', >> 'sqlca.sqlerrd1', 'sqlca.sqlerrd2', 'sessionid', 'coserverid', >> 'utc_to_datetime', 'utc_current', 'get_tz', or 'dbhostname'. >> >> So there are two more undocumented ones: >> >> DBINFO('utc_current') -- returns an INTEGER, presumably a UTC time >> DBINFO('get_tz') -- appears to return the TZ environment variable>> >> I have discovered that you can get a full listing using: >> >> strings $INFORMIXDIR/bin/oninit | grep DBINFO >> >> This includes the documented 'serial8' missed by 'finderr 728': >> >> DBINFO ('dbspace', >> DBINFO ('sqlca.sqlerrd1') >> DBINFO ('sqlca.sqlerrd2') >> DBINFO ('utc_to_datetime', >> DBINFO ('utc_current') >> DBINFO ('get_tz') >> DBINFO ('serial8') >> DBINFO ('sessionid') >> DBINFO ('dbhostname') >> DBINFO ('version', 'server-type') >> DBINFO ('version', 'major') >> DBINFO ('version', 'minor') >> DBINFO ('version', 'os') >> DBINFO ('version', 'level') >> DBINFO ('version', 'full')>> >> Jonathan: who maintains the documentation at IBM? > > The Information Development (ID) team - aka Tech Pubs. > > As the manuals say, you can send them messages about manual content at > docinf.antispam@us.antispam.ibm.com (obviously, remove the antispam and the > preceding dot) - and they are usually pretty responsive about it. They're > under a deadline crunch this week, though. > > I've cc'd this message to them. Thanks.

Glossary