Explore topic-wise InterviewSolutions in .

The “CHUNK” is a file containing informix database DATA (or a subset of that data). In can be a file on a internal disk (COOKED file) or a RAW device (from a SAN PLATFORM or internal disks/arrays).

The “chunk” is a file containing informix database data (or a subset of that data). In can be a file on a internal disk (cooked file) or a raw device (from a SAN platform or internal disks/arrays).

• ONLINE : instance is up & running, allowing connections from all the users (that have the necessary GRANTS)
• OFFLINE: instance is down. No connections to the database allowed
• SINGLE USER: instance is up & running, but only user informix can access the database. Useful when DBA WANTS to CLOSE all the user sessions, perform some TASK and bring the instance in multi user mode
• QUIESCENT: Similar with SINGLE USER mode, useful for maintenance task (recovery / recovery of a corrupt or offline dbspace chunk)

In Informix, the smallest UNIT containing DATA is the “PAGE”. A continuous group of pages FORM an extent

In Informix, the smallest unit containing data is the “page”. A continuous group of pages form an extent

A collection of INFORMIX processes in charge of:

• Informix shared memory structures
• Receive user session connection, and puts them in contact with the data
• Gather data statistics
• Allow backup / RECOVERY actions
• INTERACT with the user sessions
• At the OS level, an instance can be “SEEN” as one ore more oninit processes + memory structures + disk / persistence structures

A collection of informix processes in charge of:

A LIGHT SCAN may occur when INFORMIX internal OPTIMIZER decides that the BEST execution plan will be a sequential scan (bad query selectivity, lack of indexes…). In this case, if the optimizer further decides that the data set resulting from the query will not fit in the buffer pool, it will bypass it. The result is a light scan.

A light scan may occur when Informix internal optimizer decides that the best execution plan will be a sequential scan (bad query selectivity, lack of indexes…). In this case, if the optimizer further decides that the data set resulting from the query will not fit in the buffer pool, it will bypass it. The result is a light scan.

• Any object for whom its binary data FILE can be stored and restored with success into a database can be CONSIDERED a BLOB.
• As an example, an ECOMMERCE site can STORE the product photos and VIDEOS inside a database. But that’s not a best practice.

• The DBA can use:
• The onmonitor utility
• oncheck -PE | egrep “DBspace USAGE REPORT|” | more
• A query JOINING sysdbspaces and sysdatabases tables located in the sysmaster database

If the DBA has a query inside a script / program that connects to an Informix instance through an ODBC CONNECTION, and RUNNING the query results in an error, then he can use the TRACE functionality, and return DBINFO(sqlca.sqlerrd[1,2]).

If the DBA has a query inside a script / program that connects to an Informix instance through an ODBC connection, and running the query results in an error, then he can use the TRACE functionality, and return DBINFO(sqlca.sqlerrd[1,2]).

Whenever Informix sqlexec process searches for a free / clear buffer in ORDER to read a page from the disk into that buffer is called foreground write operation. The performance penalty appears when there are no free / CLEAN buffers left, and sqlexec has to search for a LESS USED buffer, write its content to disk, clean the buffer and reuse the buffer for a foreground write operation – having the DISADVANTAGE of NOT being asynchronous (as chunk writes/lru writes)

Whenever Informix sqlexec process searches for a free / clear buffer in order to read a page from the disk into that buffer is called foreground write operation. The performance penalty appears when there are no free / clean buffers left, and sqlexec has to search for a less used buffer, write its content to disk, clean the buffer and reuse the buffer for a foreground write operation – having the disadvantage of NOT being asynchronous (as chunk writes/lru writes)

• Fragment elimination appears when the following conditions are all satisfied:
• A table is fragmented
• The FRAGMENTATION FORMULA doesn’t create overlapping ? asta oricum nu e PERMIS (overlapping)
• A query uses equality FILTER on a column used in the fragmentation formula
• If the above conditions are MET, the Informix engine can skip fragments (data is not searched there) that don’t comply with the query filter

INNER JOIN, LEFT / RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN

INNER JOIN, LEFT / RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN

• onparams -p -s -d -y
• onmonitor utility
• in newer IDS versions, use the SQL API to ALTER the PLOG LOCATION

• DIRTY READ: Reads the DATA without checking if it’s COMMITTED or not. No LOCKS are held during the read
• COMMITTED READ: Reads only the committed data. No locks held
• REPEATABLE READ: Reads only the committed data. A shared lock is held on the rows. Other sessions can obtain shared locks on those rows, but no ONE can get an exclusive lock.
• CURSOR STABILITY

• A dbspace can have one or more partitions. Each partition has a special “HEADER” info in the first extent.
• The partition header keeps a list of the partition’s extents. If the object HOLDING the space in the partition (table / index) has a great number of extents, the header MAY not be able to keep the full list.
• Workarounds:
• increase first / next extent size and recreate the object
• change the PAGE size -> will result in a LOWER number of extents
• recreate the index in a dbspace having a larger page size

• Index (ALONG with database and table) consistency can be verified with oncheck -CDI database_name:table_name. Preferably with the instance put in quiescent mode.
• If just the index is CORRUPTED (and not fixable by oncheck), an OPTION will be to recreate the index.

• Running update STATISTICS will update the system CATALOG information about the database.
• This information is USED by the Informix optimizer to calculate costs and execution PLANS for day to day queries. Lack of up-to-date information about data distributions MAY result in severely bad plans.

A DBA can use DIRECTIVES MOSTLY to change / test / evaluate the execution plan (costs / benefits) for a QUERY, by FORCING an index / change JOIN order / etc.

A DBA can use directives mostly to change / test / evaluate the execution plan (costs / benefits) for a query, by forcing an index / change join order / etc.

• Monitor impact on system’s CPU:
• Instance sys vcpu + Instance user vcpu
• Locks requests / waits
• Active locks

• Monitor impact on system’s storage (IO) / memory:
• Buffer reads / writes / waits
• Page reads / writes /
• Disk reads / writes

• LOGICAL contention:
• lock requests / waits
• deadlocks
• EVENTUALLY rollbacks

• Monitor instance health & status
• Dbspaces TOTAL / used / free SPACE
• Checkpoint intervals & duration
• Sequential scans number
• Number of not yet backup-ed logical logs
• Number of active sessions

• And of course the instance ONLINE log.

By setting PDQ PRIORITY the DBA can change the query EXECUTION priority and increase the NUMBER of internal THREADS spawned by Informix to satisfy the query. Also, some MEMORY allocation STRATEGIES differ upon setting PDQPRIORITY.

By setting PDQ PRIORITY the DBA can change the query execution priority and increase the number of internal threads spawned by Informix to satisfy the query. Also, some memory allocation strategies differ upon setting PDQPRIORITY.

• On a platform with FAST cpus, write low performance and compresable data, the ontape output may be piped and compressed before getting on disk (less IO write OPERATIONS).
• With onbar, greater backup SPEEDS can be achived by:
• Increase the number of onbar threads -> BAR_MAX_BACKUP
• Increase the buffers used by onbar (if memory is available) ? BAR_NB_XPORT_COUNT
• Use a fast storage for the storage pool (We use onbar + IBM Tivolli Storage Manager)
• Consider taking incremental backups

• You are still using 9.4 ? It’s out of support for 3 years … . We upgraded from 10.00FC6 to 11.50FC7 last year.
• The first Informix database I started to administrate was IDS 10.00FC4. So my BEST guess is that 9.4 introduced partially non blocking checkpoints (FUZZY checkpoints ?), these being superseded in the latter versions.
• The checkpoint is the MOMENT when Informix will flush the CONTENTS from the bufferpool to disks.

• EXECUTE FUNCTION task(“HA SDS primary”,”srv_slave”);
• If it is HDR and the SECONDARY BECOMES the new standalone server: onmode -d standard

foreground writes (FG) should always be zero. A foreground write is CAUSED when a session needs to have a page from disk placed into the buffer pool, but there are no clean/free buffers available. In this case, the SQLEXEC thread will PICK a page off the least RECENTLY used side of an LRU queue, write it out, and mark the page as clean. This process is time consuming for a session and should be avoided.

foreground writes (FG) should always be zero. A foreground write is caused when a session needs to have a page from disk placed into the buffer pool, but there are no clean/free buffers available. In this case, the sqlexec thread will pick a page off the least recently used side of an LRU queue, write it out, and mark the page as clean. This process is time consuming for a session and should be avoided.

The most COMMON page SIZE used is 2KB, though some systems USES 4KB page.

The most common page size used is 2KB, though some systems uses 4KB page.

Informix Universal SERVER was the BRAND name given to the 9.X versions of the product; the upgrade from the Informix Dynamic Server (IDS) 7.X products, SKIPPING the XPS 8.X release numbers. The 9.X server contained a number of functional advances (data type extensibility, DataBlades etc), as WELL as improvements to the storage manager and SQL processing. The most recent versions of the Informix line -- 10.X -- are an evolutionary PROGRESSION over the 9.X engine.

Informix Universal Server was the brand name given to the 9.X versions of the product; the upgrade from the Informix Dynamic Server (IDS) 7.X products, skipping the XPS 8.X release numbers. The 9.X server contained a number of functional advances (data type extensibility, DataBlades etc), as well as improvements to the storage manager and SQL processing. The most recent versions of the Informix line -- 10.X -- are an evolutionary progression over the 9.X engine.

We can use the SQLCA structure to get the ISAM Error.

sqlca.sqlerrd[1] holds the ISAM error RETURN CODE.

We can also use the rgetlmsg() and rgetmsg() library functions to obtain ISAM error MESSAGE TEXT.

We can use the SQLCA structure to get the ISAM Error.

sqlca.sqlerrd[1] holds the ISAM error return code.

We can also use the rgetlmsg() and rgetmsg() library functions to obtain ISAM error message text.

Yes, you can start Informix in verbose MODE:

oninit -v (this works on NT as well)

Informix will print everything that happens.

To close the verbose mode: start a second terminal session (UNIX) or COMMAND PROMPT (NT) and execute:

onmode -KY

N.B. Don't close your terminal session (UNIX) or command prompt (NT) while in verbose mode. This will take Informix offline immediately.

Yes, you can start Informix in verbose mode:

oninit -v (this works on NT as well)

Informix will print everything that happens.

To close the verbose mode: start a second terminal session (UNIX) or command prompt (NT) and execute:

onmode -ky

N.B. Don't close your terminal session (UNIX) or command prompt (NT) while in verbose mode. This will take Informix offline immediately.

If Informix is still online, you can get a NEW onconfig with:

onstat -c >onconfig

If Informix is offline, you can retrieve the settings from the database with:

oncheck -PR
or
oncheck -pP 1 1

If Informix is still online, you can get a new onconfig with:

onstat -c >onconfig

If Informix is offline, you can retrieve the settings from the database with:

oncheck -pR
or
oncheck -pP 1 1

Yes, but you need to install VERSION 7.30 TC 6 or HIGHER.

Yes, but you need to install version 7.30 TC 6 or higher.

You MAY ASK questions about Perl, DBI and DBD::Informix, but only if an IBM or Informix database is involved, somehow -- it is not a GENERAL forum for arbitrary questions about Perl. HOWEVER, there are alternative forums that you should also use, described in the product. 

You may ask questions about Perl, DBI and DBD::Informix, but only if an IBM or Informix database is involved, somehow -- it is not a general forum for arbitrary questions about Perl. However, there are alternative forums that you should also use, described in the product. 

Light SCANS occur under the following conditions: The optimizer CHOOSES a SEQUENTIAL scan of the table. The number of pages in the table is greater than the number of buffers in the buffer pool. The isolation LEVEL OBTAINS no lock or a shared lock on the table: Dirty Read (including non logging databases) isolation level Repeatable Read isolation level if the table has a shared or exclusive lock Committed Read isolation if the table has a shared lock

Light scans occur under the following conditions: The optimizer chooses a sequential scan of the table. The number of pages in the table is greater than the number of buffers in the buffer pool. The isolation level obtains no lock or a shared lock on the table: Dirty Read (including non logging databases) isolation level Repeatable Read isolation level if the table has a shared or exclusive lock Committed Read isolation if the table has a shared lock

A binary large object, also known as a blob, is a COLLECTION of binary data stored as a single entity in a database management system. Informix invented an alternative backronym, Binary Large Object. Blobs are typically IMAGES, AUDIO or other multimedia OBJECTS, though sometimes binary EXECUTABLE code is stored as a blob.

A binary large object, also known as a blob, is a collection of binary data stored as a single entity in a database management system. Informix invented an alternative backronym, Binary Large Object. Blobs are typically images, audio or other multimedia objects, though sometimes binary executable code is stored as a blob.

If a table is DESIGNED without specifying EXTENT sizes, Informix uses a DEFAULT of 16 pages for the FIRST and 8 pages for each subsequent extent. Extent SIZING is done when the table is initially created.

If a table is designed without specifying extent sizes, Informix uses a default of 16 pages for the first and 8 pages for each subsequent extent. Extent sizing is done when the table is initially created.

Informix IDS provide following modes:-

1. Online MODE: This is the normal operating mode of the database server.
2. Offline mode?: The database server is not running. Shared memory is not allocated.
3. Quiescent mode: This is the normal operating mode of the database server.

Administrators use this mode to perform maintenance functions that do not require the execution of SQL and DDL statements. Only the administrator (user informix) can access the database server.

4. Single-User mode: This mode is an intermediary mode between Quiescent mode and Online mode?

Administrators use this mode to perform any maintenance TASK, INCLUDING tasks requiring the execution of SQL and DDL statements. Administrators can also perform all other functions available in Online mode. You can see the CURRENT database server mode by executing the onstat - utility from the command.

Informix IDS provide following modes:-

Administrators use this mode to perform maintenance functions that do not require the execution of SQL and DDL statements. Only the administrator (user informix) can access the database server.

Administrators use this mode to perform any maintenance task, including tasks requiring the execution of SQL and DDL statements. Administrators can also perform all other functions available in Online mode. You can see the current database server mode by executing the onstat - utility from the command.

IBM RELEASED INFORMIX

IBM released Informix

from COMMAND LINE# onmonitor ->Logical-Logs->DATABASES.

from command line# onmonitor ->Logical-Logs->Databases.

The Informix DATABASE management system was conceived and DESIGNED by ROGER Sippl in the late 1970s. Informix was founded in 1980, went public in 1986, and for a period during the 1990S Informix was the second most popular database system, after ORACLE.

The Informix database management system was conceived and designed by Roger Sippl in the late 1970s. Informix was founded in 1980, went public in 1986, and for a period during the 1990s Informix was the second most popular database system, after Oracle.

The database server uses a concept called key-value locking to lock the deleted row. When the database server DELETES a row, key values in the indexes for the table are not REMOVED immediately. Instead, each key value is MARKED as deleted, and a lock is placed on the key value. One of the most IMPORTANT uses for key-value locking is to assure that a unique key remains unique through the end of the TRANSACTION that deleted it.

The database server uses a concept called key-value locking to lock the deleted row. When the database server deletes a row, key values in the indexes for the table are not removed immediately. Instead, each key value is marked as deleted, and a lock is placed on the key value. One of the most important uses for key-value locking is to assure that a unique key remains unique through the end of the transaction that deleted it.

An Informix instance is a single connection to shared MEMORY. It is represented by ONE or more on init PROCESSES. When Informix is started or stopped, these processes APPEAR and DISAPPEAR. Informix used to use the term "server" when referring to an instance, hence the environment variable DBSERVERNAME.

An Informix instance is a single connection to shared memory. It is represented by one or more on init processes. When Informix is started or stopped, these processes appear and disappear. Informix used to use the term "server" when referring to an instance, hence the environment variable DBSERVERNAME.

A chunk is a physical portion of DISK on which Informix STORES its DATA. A chunk can be either a raw partition or a file system file. Informix suggests that a chunk's NAME be a symbolic link to the actual chunk. For example, if you are using /dev/rdsk/c6t0d0s1 as a chunk, you could create a symbolic link named /dev/informix/chunk1 that points to /dev/rdsk/c6t0d0s1. That way, if there are ever any PROBLEMS with that disk, you could replace the disk with another one and just change the symbolic link.

A chunk is a physical portion of disk on which Informix stores its data. A chunk can be either a raw partition or a file system file. Informix suggests that a chunk's name be a symbolic link to the actual chunk. For example, if you are using /dev/rdsk/c6t0d0s1 as a chunk, you could create a symbolic link named /dev/informix/chunk1 that points to /dev/rdsk/c6t0d0s1. That way, if there are ever any problems with that disk, you could replace the disk with another one and just change the symbolic link.

None; this is a news GROUP! That is, there are no rules enforced by the software. There are periodically discussions on c.d.i about what should and should not be said. This is my FAQ, so it gets my SPIN on the rules that should be followed.

* Format: HTML postings are strongly discouraged; so are MIME postings. Both cause problems with MANY frequently used news READERS. That means that attachments aren't encouraged either. ALSO, sending the same information twice, once in HTML and once in plain text, is downright wasteful.

None; this is a news group! That is, there are no rules enforced by the software. There are periodically discussions on c.d.i about what should and should not be said. This is my FAQ, so it gets my spin on the rules that should be followed.

* Format: HTML postings are strongly discouraged; so are MIME postings. Both cause problems with many frequently used news readers. That means that attachments aren't encouraged either. Also, sending the same information twice, once in HTML and once in plain text, is downright wasteful.

The informix-list@iiug.org mailing list is a bi-directional gateway to the comp.databases.informix (c.d.i) USENET news group. Messages posted to the news group are forwarded by mail to subscribers of the mailing list; responses sent to the informix-list@iiug.org email address are also posted to the news group; and the gateway manages to keep track of things so that postings do not repeat themselves. The mailing list is 'closed' in that you cannot post directly to the list unless you also SUBSCRIBE to the list. However, you can ALWAYS post to the news group instead; that is FULLY open and unmoderated.

The informix-list@iiug.org mailing list is a bi-directional gateway to the comp.databases.informix (c.d.i) UseNet news group. Messages posted to the news group are forwarded by mail to subscribers of the mailing list; responses sent to the informix-list@iiug.org email address are also posted to the news group; and the gateway manages to keep track of things so that postings do not repeat themselves. The mailing list is 'closed' in that you cannot post directly to the list unless you also subscribe to the list. However, you can always post to the news group instead; that is fully open and unmoderated.