pajama

Q. How can I determine the number of hours, minutes or seconds difference between two datetime values?


A. This can be accomplished with SQL using the following methods:

INTRODUCTION

The values in the onCONFIG configuration file may not be the settings current in effect by the database server. This is true when:

• One of the parameters in the file is changed after the database server is started

• A parameter has been changed dynamically with the onparams utility

• A parameter that has been change dynamically with the onmode utility

SELECT cf_name, cf_default, cf_effective, cf_original FROM   syscfgtab;

INTRODUCTION

Most customers using Informix products are used to having an option which gives them the version of the product being used. This is usually done at the command line using the "-V" option or using a special utility.

For example: esql -V would give the version of esql or the utility ifx_getversion can be used to display the version of the ClientSDK.

It is also possible to obtain the version of the JDBC driver contained in the ifxjdbc.jar file from the command line.


BEFORE YOU BEGIN

Make sure you have set the CLASSPATH variable correctly to include the location of ifxjdbc.jar ( or ifxjdbc-g.jar ) and that your PATH has the JDK in it.


STEPS

Execute the following

java com.informix.jdbc.Version

% java com.informix.jdbc.Version 
IBM Informix JDBC Driver Version 2.21.JC5

To obtain a list of the tables in a particular chunk, below sql can be used:
select dbinfo( "DBSPACE" , pe_partnum ) dbspace,

dbsname[1,12],

tabname,

pe_chunk,

pe_offset start,

pe_size size

from sysptnext, outer systabnames

where pe_partnum = partnum

and pe_chunk = <chunk number>

order by start, dbsname, tabname;

Replace chunk number with the number of the chunk for which you want to list the table names.

The partition number (partnum) contains information about a table.

It is 4-byte hexadecimal value (0x12345678) that is composite of two values, the most significant 3 digits indicates which dbspace contains the table (tblspace) and the least 5 significant digits indicates the logical page number that contains information about the table.

It means that 0x123 indicates which dbspace contains the table and 0x45678 indicates a logical page number that keeps information about the table.

For example, if the partnum of table customer is 0x0050015f, it means that dbspace 5 (0x005) contains the table and the logical page 0x0015f keeps information about the customer table.

Note 1: The 'tblsnum' in 'onstat -k' and 'tblnum' in 'onstat -t/-T' corresponds to the part number (partnum) of the table.

Note 2: The value of partnum stored in systables and reported in oncheck -pt is a decimal value, you need to convert it to hexadecimal in order to get the information of which dbspace contains the table and the logical page that keeps information of the table.

You may run into locking issues when running oncheck -pT tablename on a high use table. You can run a query using the sysmaster database instead to avoid encountering locking issues.
The following select statement from the sysmaster database will provide free page information similar information to oncheck -pt:

select count(pb_bitmap), pb_bitmap
  from sysptnbit p,systabnames t
 where t.tabname = "tablename"
   and t.dbsname = "databasename"
   and p.pb_partnum = t.partnum
 group by pb_bitmap;

This select will not place any locks on the base table tablename . The query produces output similar to:

         (count)   pb_bitmap

            2841          12
             196           0
              26           4
               1           8

4 row(s) retrieved.

The pb_bitmap column is the decimal value of the bitmap for each page in the table. The values mean the following:

pb_bitmap Description
========= =========
  0       Unused
  4       Data Page with room for another row
  8       Index Page
 12       Full Data Page

Note: You could use the query to help estimate the number of pages still available within the table. You should estimate on being able to use only 30% of the pages with the value of '4', meaning 'a data page with room for another row'.

Using the example data: The engine would need another extent after using the remaining space in the partially used pages:
(26 * 30%) = 7.8 pages

The partnum column in systables indicates the identification of the table inside a dbspace, when the value of partnum is zero, this means that it is a :

• fragmented table
• view
• synonym

You see messages in online.log. something similar to these:

04:15:08  sid 2011976 username sanjju@linux12 pid 2566 terminated.
04:15:08   killed(MCMD_KILL)

You will see such messages in online.log when someone kills session(s) either manually, or through some maintenance script, by running onmode -z.

Other scenarios where we can see these messages in online.log is when informix server kills sessions internally. Situations where server could be killing sessions internally are like, when the server is switching to a single user mode, or when it is shutting down.

Also, with newer version of Informix server (11.50.xC9, or 11.70.xC4 and higher versions) that supports Low Memory Management feature and has the feature enabled with a defined threshold, you can see sessions getting killed internally after the instance enters the defined threshold for low memory.

You can run 'onstat -g lmm' and review the output. This should list the sessions, if any were killed due to enabling of low memory management feature. For more information on this command, please check the link given in the "Related Information' section of this document.