pajama

Problem

Connections to Informix Dynamic Server (IDS) hang or take a long time to connect. Other users who are already connected do not see a performance problem. Once a user is connected there is no performance problem.

Symptom

Connections to Informix Dynamic Server (IDS) hang or take a long time to connect. Other users who are already connected do not see a performance problem. Once a user is connected there is no performance problem. Users connecting by way of shared memory do not experience a connection hang. Only users using network connections see this problem.

Cause

There are not enough files descriptors allocated for IDS to effectively handle the number of network connections. See the Related information section for a link to more information regarding file descriptors.

Note: This is only one possible cause.

Resolving The Problem

1. Increase the number of file descriptors available to a process. Ask your operating system administrator to increase the number of file descriptors .The suggested are values 2048, 4096, or unlimited. You can temporarily increase the number of file descriptors by running the ulimit -n command:

Problem

Client applications using the TCP/IP protocol to connect to Informix experience poor performance during connection. Once the connection has been established the performance is improved.

Cause

• During connection establishment, the IP address of the client is used by the IDS server to look up its host name. The method used to accomplish this is dependent on the configuration of the operating system. If the Domain Name System (DNS) is used, an operation known as a reverse query is used to map the IP address to host name. If the configuration of the DNS server is incomplete this type of query may take some time.
  
  The command line utility nslookup may be used to perform DNS queries. If a reverse lookup operation takes a long time to complete using nslookup then the IDS server will also take a long time to complete the same operation.
  
  The first example shows a reverse lookup operation for an IP address that is known to the DNS server and completes immediately. The second example uses an IP address that is unknown taking several seconds before returning an error.
  
  C:\>nslookup 1.2.3.4
  Server:  host1.domain.com
  Address:  9.8.7.6
  
  Name:    host222.domain.com
  Address:  1.2.3.4
  
  C:\>nslookup 9.69.1.126
  Server:  host1.domain.com
  Address:  9.8.7.6
  
  DNS request timed out.
  timeout was 2 seconds.
  *** Request to host1.domain.com timed-out

• The lookup for the service name could be taking a while.

• Another cause may by the use of IPv6 addresses with DNS servers that are not configured for IPv6.

Diagnosing The Problem

To isolate the problem to tcp/ip connections try to connect by way of a shared memory connection. If the connection is now completed quickly then the problem outlined above may be the cause of poor connection performance.

Follow this link if you need instructions for setting up shared memory connections:

Resolving The Problem

• Refer to your operating system documentation for details of how to configure DNS. If the DNS server runs on the Microsoft Windows® platform then it is possible that the zone used to implement reverse lookup is not implemented. Details regarding DNS on the Windows operating system can be found in the related URLs below. Ensure that the reverse-lookup zone is populated for all clients that connect to the IDS server.
• Ensure that local hostname resolution is performed before other methods of hostname resolution (NIS, NIS+, DNS, and so on). Files like these are used on various operating systems to determine lookup order:
  
  /etc/netsrv.conf
  /etc/irs.conf
  /etc/nsswitch.conf
• Reconfigure your operating system to not use DNS, for example by including the host names and IP addresses of all client machines in the local hosts file /etc/hosts or %WINDIR%\system32\drivers\etc\hosts on Windows.

• Change the host name in the sqlhosts file to an IP address.
• Change the service name in the sqlhosts file to the port number.
• Connections to IDS hang

https://www.ibm.com/support/pages/slow-connect-time-informix-when-using-tcpip

Problem

A problem with DNS lookup can cause slow connection to database server. This article explains one scenario and a possible solution for that problem.

Symptom

Connection request to database server from dbaccess is trying to access DNS server for hostname resolution, after it found the same in the local host file.

On certain Operating System (for example: Linux) you may noticed dbaccess (and other client applications) always doing DNS lookup while connecting to a database, even after found out the hostname or IP address in the local host file. This behavior sometimes caused slow connection, if you have problem related to DNS. Following is an excerpt of strace output shows the sequence of file accessed by a dbaccess request:

Cause

Traditionally, hostname and service name resolution were performed by functions such as gethostbyname(), getservbyname() etc. These traditional lookup functions are still available, however those are not forward compatible to IPv6. Instead, the IPv6 socket API provides new lookup functions that consolidate the functionality of several traditional functions. These new lookup functions are also backward compatible with IPv4, so a programmer can use the same translation algorithm in an application for both the IPv4 and Ipv6. The getaddrinfo() is the new primary lookup function and a connection request from the dbaccess ultimately calls this socket API. You can pass several parameters to the getaddrinfo(), one of those parameter is addrinfo structure. By default, dbaccess passes value “AF_INET6” for addrinfo.ai_family. The ai_family field indicates the protocol family associated with the request, and will be PF_INET6 for IPv6 or PF_INET for IPv4.

If the ai_family set to AF_INET6 (IPv6) the getaddrinfo() will search the DNS every time. If the ai_family set to AF_INET, then it don't query the DNS server. You can consult the 'man' page for getaddrinfo() for detailed information.

Beginning with Informix 10.00.xC4 and Client SDK 2.90.xC4, the database server checks, on startup, whether IPv6 is supported in the underlying operating system. If IPv6 is supported it is used. If the underlying operating system does not support IPv6, the IPv4 address is used.

Diagnosing The Problem

You can use the attached 'C' program to reproduce and verify the problem outside Informix.

How to use the attached 'C' program?

1. Save the attached file as 'gt_adr_info.c'.

2, Compile the 'C' program using following command:

Resolving The Problem

In case of a problem with DNS lookup and encountering slow connection to database, you may use the environment variable IFX_DISABLE_IPV6 (IFX_DISABLE_IPV6=1) to disable Ipv6 and this will set the ai_family to AF_INET only and will not do subsequent query to the DNS server.

https://www.ibm.com/support/pages/possible-cause-slow-connection-database-server

Problem

This article describes a problem which occurs when CURRENT does not recalculate the actual date and time when called more than once in a stored procedure.

Resolving The Problem

PROBLEM

Repeated calls to the CURRENT function within a stored procedure always returns the same value.

This problem is typically seen when the stored procedure uses a cursor that loops through large quantities of data, such that by the time the final row is reached, the actual time has moved on and CURRENT no longer hold the true system datetime.


CAUSE

If using CURRENT in a stored procedure, the datetime value is set to the current system datetime at the time the procedure is called. CURRENT is not updated while the stored procedure is running.


SOLUTION

The system table sysmaster:sysshmvals.sh_curtime stores the exact system datetime. Run the following query within your stored procedure to return the actual time and store it into the local variable current_time.

select DBINFO('utc_to_datetime', sh_curtime) into current_time
from sysmaster:sysshmvals;

The following example demonstrates the different results returned when comparing the use of the CURRENT function with that of the sysshmvals query shown above.


Example:

create database currdemo;

create table dates (text char(20),
                    time datetime year to second);

create procedure test()
define i int;
define current_time datetime year to second;


insert into dates values ("current 1st run", CURRENT);

select DBINFO('utc_to_datetime', sh_curtime) into current_time
from sysmaster:sysshmvals;
insert into dates values ("realtime 1st run", current_time);

system "sleep 5" ;

insert into dates values ("current 2nd run", CURRENT);

select DBINFO('utc_to_datetime', sh_curtime) into current_time
from sysmaster:sysshmvals;
insert into dates values ("realtime 2nd run", current_time);

end procedure;

execute procedure test();

select * from dates order by text;


The output looks as follows:

text                 time

current 1st run      2006-10-31 17:02:11
current 2nd run      2006-10-31 17:02:11
realtime 1st run     2006-10-31 17:02:11
realtime 2nd run     2006-10-31 17:02:16

출처 : https://www.ibm.com/support/pages/how-obtain-actual-date-and-time-stored-procedure