pajama

• 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.

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:
A shared-memory connection (UNIX)

• 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

Transactions are able to continue working during checkpoints. To avoid blocked transactions,the database server must have enough physical log space to contain all of the transaction activity that occurs during checkpoint processing. Checkpoints can be triggered when the physical log is 75 percent full and checkpoint processing must complete before the remaining 25 percent of the physical log is used. Transaction blocking occurs as soon as the system detects a potential for a physical log overflow, because every active transaction could generate physical log activity.

The optimal size of the physical log depends on two factors:

1.The rate at which transactions generate physical log activity.

[1]Buffer Pool Space less than 4G.
For systems with less that four gigabytes of buffer pool space, the physical log can
be sized at 110 percent of the combined size of all the buffer pools.

[2]For larger buffer pools (Commonly larger than 4G)
Start with four gigabytes of physical log space and then monitor checkpoint activity. If checkpoints occur too frequently and appear to impact performance, increase the physical log size,and look into the Trigger column of the onstat -g ckp output. If the trigger events occur frequently by PLOG, then increase the size of the physical log.

2.Whether or not you set the RTO_SERVER_RESTART configuration parameter.

If RTO_SERVER_RESTART is enabled, the physical log size must be less than all buffer pools to maintain the RTO_SERVER_RESTART policy.

INTRODUCTION

The Scheduler is a component of IBM Informix® Dynamic Server™ (IDS) that executes tasks defined in the sysadmin database. These tasks are coordinated by the dbScheduler and dbWorker threads within the server. There may be occasions when it is required to prevent the startup of these threads, for example if an assert failure occurs during initialization of the server. This article describes a method to temporarily prevent the startup of the threads used by the Scheduler.


STEPS

1. As the root or informix user create the empty file $INFORMIXDIR/etc/sysadmin/stop. one way to do this is to use the Unix command touch.
2. Stop and restart IDS.
3. During initialization the following message will appear in the message log:

SCHAPI: "INFORMIXDIR/etc/sysadmin/stop" file is present.
Bypassing dbScheduler and dbWorker threads startup.

High Performance Loader: Mode Comparison for Load Jobs

While running a dbexport command to unload a database, dbexport stops and you get the following errors:

-425 - Database is currently opened by another user.
-107 - ISAM error:  record is locked.

The database cannot be locked using dbexport, because there is at least one session still connected and holding a lock on an object.

Find all sessions that are still connected to the database and holding a lock, then disconnect them using onmode -z before running dbexport again.

You can get the sessions and the lock holders still connected to the database by using onstat commands.

You can also run this SQL statement to find out the sessions that hold locks.


Note: In the following query, you have to replace the two occurrences of database_name by the name of the database you want to export.


database sysmaster;

SET ISOLATION TO DIRTY READ;

SELECT "database lock" table_name,
l.type lock_type, l.keynum index_num, HEX(l.rowidlk) rowid,
s.sid session_id, s.username, s.pid, s.hostname, 
q.sqs_statement statement
FROM syslocks l, sysdatabases d, syssessions s, syssqlstat q
WHERE d.name='database_name' AND l.rowidlk = d.rowid
AND l.owner = s.sid
AND dbsname = 'sysmaster' AND tabname = 'sysdatabases'
AND s.sid = q.sqs_sessionid

UNION ALL

SELECT l.tabname,
l.type, l.keynum, HEX(l.rowidlk),
s.sid, s.username, s.pid, s.hostname,
q.sqs_statement
FROM syslocks l, syssessions s, syssqlstat q
WHERE l.dbsname='database_name' AND l.owner = s.sid
AND s.sid = q.sqs_sessionid AND dbsname != 'sysmaster'
AND tabname != 'sysdatabases'
ORDER BY 5;

database_name
Replace it by the name of the database you want to export.

table_name
Is either the name of the table that has an object locked (row, index, table itself) or it will be the phrase 'database lock' if the whole database was locked.

lock_type
Is the type of lock, for example: X for Exclusive, S for Shared, U for Update. Seeonstat -k information in IBM® Informix® Dynamic Server™ (IDS) Administrator's Reference or Administrator's Guide for further information on types of lock.

index_num
the index key number if it is an index lock.

rowid
the rowid (in hexadecimal) for the record locked in case it is a record was locked. If a database was entirely locked, this is the rowid (in hexadecimal) for the record corresponding to this database locked in sysmaster:sysdatabases table.

session_id
the session id for the database session that locked the object.

username
Is the user id associated to the session that locked the object.

pid
the process id associated to the session that locked the object.

hostname
the machine name for the workstation or client host that created the session that locked the object.

statement
the SQL statement that generated the lock in the object.

$ onstat -g ses session_id

session_id
The id of the session locking the object given as result by the previous query.

$ onstat -g ses 5

$ onmode -z session_id

session_id
The id of the session locking the object.

$ onmode -z 5

PROBLEM

Depending upon how you create primary/foreign key constraints, IDS will generate names for them and the indexes that are used to enforce them. These names are not intuitive (like customer_pkind, customer_fk1), which causes confusion when viewing or dropping them.

Example:

If you create a table like this:

create table customer
      ( customer_num            serial(101),
        fname                   char(15),
        lname                   char(15),
        company                 char(20),
        address                 char(200),
        phone                   char(18),
       primary key (customer_num)
      );

Then you will end up with an index called ' 100_1' (note the leading space), and the constraint name is 'u100_1'. These names are generated based on the tabid (customer is tabid 100) and a sequence number. The index can't be dropped directly, because the name has a leading space character.


Adding a primary key after creating the table (with alter table) hits the same generated name issue:

  alter table customer add constraint primary key (customer_num) ;

The index name is now ' 100_26' and the constraint name is 'u100_26'.

Example:

alter table customer add constraint 
primary key (customer_num) constraint cust_pk ;

create table customer
      ( customer_num            serial(101),
        fname                   char(15),
        lname                   char(15),
        company                 char(20),
        address                 char(200),
        phone                   char(18),
       primary key (customer_num) constraint cust_pk
      );

Example:

create unique index cust_pki on customer(customer_num) ;
alter table customer add constraint
  primary key (customer_num) constraint cust_pkc ;

Q. How can I resolve various "unknown terminal" errors I receive when running dbaccess?

A. Set the environment variables TERM and TERMCAP to point to $INFORMIXDIR/etc/termcap.

Example:

For csh:
setenv TERM vt100
setenv TERMCAP $INFORMIXDIR/etc/termcap

For ksh :
export TERM=vt100
export TERMCAP=$INFORMIXDIR/etc/termcap

You may use this SQL query to identify all indices that have been marked as "bad" in your IDS instance:

(SELECT st.tabname, si.idxname, hex(spt.flags) flags
FROM  sysindexes si, systables st, sysmaster:sysptnkey spt
WHERE st.tabid = si.tabid AND
st.partnum = spt.partnum AND
sysmaster:bitval(spt.flags, 64) = 1 AND
st.partnum !=0 AND
si.idxname NOT IN  (
         SELECT sfs.indexname
         FROM sysfragments sfs
         WHERE sfs.tabid = st.tabid)
GROUP BY 1,2,3
)
UNION ALL
(SELECT st.tabname, sf.indexname idxname , hex(spt.flags) flags
FROM  systables st, sysmaster:sysptnkey spt, sysfragments sf
WHERE st.tabid = sf.tabid
AND sf.fragtype = 'I'
AND sf.partn = spt.partnum
AND sysmaster:bitval(spt.flags, 64) = 1
GROUP BY 1,2,3
)
ORDER BY 1