8. DBA Issues


8.50 How do I find column dependencies for a Stored Procedure?

On 19th August 1999 Sujit.Pal@bankofamerica.com (Sujit Pal) wrote:-


#!/usr/local/bin/perl
#
# Routine to output a complete list of table and column dependencies
# for a stored procedure.
#
if ($#ARGV != 0)
{
   die "Usage: ", $0, " database_name\n";
}
$dbname = $ARGV[0];
print "Table and Column Dependencies for Stored Procedures in Database ",
   $dbname, "\n\n";

#
# Find all table names
#
@tabnames = `dbaccess $dbname - 2>/dev/null < 99
   ORDER BY tabname
EOF
`;
#
# Find all unique column names
#
@colnames = `dbaccess $dbname - 2>/dev/null < 99
   ORDER BY colname
EOF
`;

#
# Find all stored procedure names
#
@procnames = `dbaccess $dbname - 2>/dev/null < 0)
         {
            if (index($taboutput{$procname}, $tabname) <= -1)
            {
               $taboutput{$procname} .= $tabname;
               $taboutput{$procname} .= " ";
               last;
            }
         }
      }
      #
      # Find column dependencies
      #
      for ($j = 4; $j <= $#colnames; $j++)
      {
         $colnames[$j] =~ s/ //g;
         $colname = lc($colnames[$j]);
         chop($colname);
         if (index($procline, $colname) > 0)
         {
            if (index($coloutput{$procname}, $colname) <= -1)
            {
               $coloutput{$procname} .= $colname;
               $coloutput{$procname} .= " ";
               last;
            }
         }
      }
   }
}
#
# Print the report
#
print "Table Dependencies\n";
print "------------------\n";
#foreach $keys (sort {$taboutput{$a} <=> $taboutput{$b}} keys %taboutput))
foreach $keys (sort(keys %taboutput))
{
   print $keys, ": ", $taboutput{$keys}, "\n";
}
print "\nColumn Dependencies\n";
print "------------------\n";
foreach $keys (sort(keys %coloutput))
{
   print $keys, ": ", $coloutput{$keys}, "\n";
}

8.51 How do I performance tune Onbar?

On 28th August 1999 rbernste@alarismed.com (Bernstein, Rick) wrote:-

The following website contain tools from Maury Tiller for troubleshooting and tuning onbar performance. By following his step-by-step procedure you can determine where the bottleneck lies.

It also contains programs which he mentioned during a technical session at the Informix Solutions Portal 99.

http://www.informix.com/onbartools/index.html

Another unofficial website

http://www.geocities.com/Heartland/Acres/4927/onbar.html

provides an excellent overview of how onbar functions.

8.52 Why do onspaces and other on-utilitys core dump?

On 31st October 1999 mdstock@mydas.freeserve.co.uk (Mark D. Stock) wrote:-

The onspaces (and probably onmonitor, but I don't use that :) command requires a user stack size of at least 8 Mb or thereabouts. You can either up the kernel parameter for all users, or use ulimit to set it at the session level.

8.53 How should I finish a restore (e.g. ontape -r)?

On 16th August 2000 david@smooth1.co.uk (David Williams) wrote:-

Don't forget to use onmode -m to bring the engine fully online before you next shutdown the engine otherwise your chunks will still be marked as bad!

8.54 What are Codd's rules?

On 12th September 2000 david@smooth1.co.uk (David Williams) wrote:-

I found this on the net and thought it might be useful!

A relational DBMS must use its relational facilities exclusively to manage and interact with the database. The rules:

These rules were defined by Codd in a paper published in 1985. They specify what a relational database must support in order to be relational.

1. Information rule Data are represented only one way: as values within columns within rows. Simple, consistent and versatile. The basic requirement of the relational model.
2. Guaranteed access rule Every value can be accessed by providing table name, column name and key. All data are uniquely identified and accessible via this identity.
3. Systematic treatment of null values Separate handling of missing and/or non applicable data. This is distinct to zero or empty strings Codd would further like several types of null to be handled.
4. Relational online catalog Catalog (data dictionary) can be queried by authorized users as part of the database. The catalog is part of the database.
5. Comprehensive data sublanguage Used interactively and embedded within programs Supports data definition, data manipulation, security, integrity constraints and transaction processing Today means: must support SQL.
6. View updating rule All theoretically possible view updates should be possible. Views are virtual tables. They appear to behave as conventional tables except that they are built dynamically when the query is run. This means that a view is always up to date. It is not always theoretically possible to update views. Codd himself, did not completely understand this. One problem exists when a view relates to part of a table not including a candidate key. This means that potential updates would violate the entity integrity rule.
7. High-level insert, update and delete Must support set-at-a-time updates. eg: UPDATE mytable SET mycol = value WHERE condition; Many rows may be updated with this single statement.
8. Physical data independence Physical layer of the architecture is mapped onto the logical layer. Users and programs are not dependent on the physical structure of the database. (Physical layer implementation is dependent on the DBMS.)
9. Logical data independence Users and programs are independent of the logical structure of the database. i.e.: the logical structure of the data can evolve with minimal impact on the programs.
10. Integrity independence Integrity constraints are to be stored in the catalog not the programs. Alterations to integrity constraints should not affect application programs. This simplifies the programs. It is not always possible to do this.
11. Distribution independence Applications should still work in a distributed database (DDB).
12. Nonsubversion rule If there is a record-at-a-time interface (eg via 3GL), security and integrity of the database must not be violated. There should be no backdoor to bypass the security imposed by the DBMS.
Rule Zero for RDBMS: Many new DBMS claim to be relational plus supporting extended features. eg. Postgres is a RDBMS with extended Object Oriented features. Codd's rule zero specifies a criteria for RDBMS: "For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities, no matter what additional capabilities the system may support." (Codd, 1990)

In Codd 1990, Codd extended the 12 rules to 18 to include rules on catalog, data types (domains), authorisation etc.

Bibliography

8.55 How do I link Informix tables to MS-Access?

On 16th August 2000 wsheldon@airflow-streamlines.co.uk (Wayne Sheldon) wrote:-

Just install and setup the Informix ODBC client to connect to your DB. Then choose File/Get External Data from the MS Access menu

You can then choose Import to download a table or the whole database, providing you have enough disc space.You can also choose link to tables, so you can update them directly.

On 30th August 2000 rbernste@alarismed.com (Rick Bernstein) wrote:-

You can define the Informix tables to MS Access as "linked" tables and connect to the Informix database using ODBC.

Install Informix SDK or Informix Connect on your client machine. Define an ODBC Data Source. In MS Access: File -> Get External Data -> Link Tables. Type of File: ODBC Databases

8.56 How does OnBar compare to Ontape?

On 8th September 2000 dagnew@charlottepipe.com (Doug Agnew) wrote:-

OnBar does see all the backups as being in "one place". It is the responsibility of the storage manager software (ISM, Legato Networker, Veritas NetBackup, Tivoli Storage Manager, etc.) to place the data on the media and keep track of its location.

Some managers, like Tivoli Storage Manager, make it very easy to store the log backups on disk and even provide facilities to automatically move them to tape when the disk gets close to full. The others that I have looked at also allow you to store backups on disk but don't automate the migration to tape.

All the managers I've examined divide the backup media into "storage pools" (whatever terminology they like) and allow the Informix backup process to designate which pool to use for each type of backup. Some even allow you to send dbspaces and/or different backup levels to different pools, if you really want to slice-and-dice.

OnBar's primary advantages, as far as I can tell:

Ontape's primary advantages are:

From what I've seen, ontape is suitable for databases up to 100G or so (depending upon how many tapes drives you want to buy or how much operator work you want).

OnBar is probably the backup of choice once you get much larger, both for operational and administrative reasons.

8.57 What are the main Informix bugs?

On 13th September 2000 david@smooth1.co.uk (David Williams) wrote:-

There is a bug in Informix 4gl 7.30.UC1, fixed in 7.30.UC4 which means that programs can produce core-dumps! This is due to a string-handling problem which means informix adds an additional space when joining strings together and hence can corrupt memory. This is fixed in 7.30.UC4

IDS 9.2x has a problem with datetime handling which can cause strange -1262 errors

On 11th November 2000 rbernste@alarismed.com (Bernstein, Rick) wrote:-

It sounds like you may be encountering Informix defect #115327.

Index pages can be incorrectly shrunk setting both leaf and node flags which will cause excess pages to be placed in the MED_HIGH buffer pool priority. (The fix deals with how the buffer manager sets the priority on the pages with 0xd0 type flag.)

When performance gets bad, run the following command:


onstat -P | tail -8

If the resulting output shows a large pct of the pages as "Btree", this is likely your problem. For example:


$ onstat -P | tail -8

Totals:  131000   113313   17087    600      0        432

Percentages:
Data  13.04
Btree 86.50
Other 0.46

One workaround is "export NOLRUPRIO=1" before starting your engine. It disables an IDS 7.3x feature, which assigns a "med-high" priority to some index pages (as shown by "onstat -R"). Newer IDS releases support a different environment variable "LRUAGE".

Before using the NOLRUPRIO environment variable, review your release notes and ensure that the following defect has been fixed:

111681 - NOLRUPRIO does not work and can hang the system.

I believe that it was fixed in IDS 7.31.UC4. (I can attest that it was not fixed in IDS 7.31.UC3 on AIX).

8.58 How do I debug onarchive failures?

On 2nd October 2000 bb22@uswest.net (Bill Border) wrote:-

Yippee!!! Another onarchive question.

See if there are any backups running now.
Run: onarchive> list/req=* and look for
EXECUTING requests. If there is one that
appears to be bogus:
onarchive> cancel/req=n
and try it again. If you set the
LOG=/opt/informix/etc/onarchive.log parameter
you will sometimes get better diag.
information.

8.59 How do I load blobs using dbaccess?

On 11th October 2000 dignacio@openratings.com (Don Ignacio) wrote:-

In order to load a blob into a table using dbaccess you need to call the function filetoclob or filetoblob, such as:


INSERT INTO candidate (cand_num, cand_lname, resume)
VALUES (0, 'Haven', FILETOCLOB('haven.rsm', 'client'))

INSERT INTO candidate (cand_num, cand_lname, resume)
VALUES (0, 'Haven', FILETOBLOB('haven..jpg', 'client'))

For more information regarding this function consult your Informix SQL Syntax Guide.

8.60 How do I identify outstanding in-place table alters?

On 19th October 2000 rbernste@alarismed.com (Bernstein, Rick) wrote:-

Informix Technical Support (case 200515) provided me with the following script to identify outstanding "in-place ALTERs". It runs quickly, even with 9,000+ SAP tables. I have successfully run it with IDS 7.31.UC3-1 and IDS 7.30.UC7XK.


# ksh script - run from ksh
tblpartnum=1048577

numdbs=`dbaccess sysmaster << !!! 2> /dev/null |grep -v max|awk '{print $0}'
select {+ full(sysdbstab)} max(dbsnum) from sysdbstab
!`

i=1
while (( i <= $numdbs ))
do
dbaccess sysmaster <<!
select hex(t1.pg_partnum), t1.pg_pagenum,t1.pg_physaddr,hex(t2.partnum),t3.
tabname
	from syspaghdr t1, sysptntab t2, systabnames t3
where 	t1.pg_partnum=$tblpartnum
	and t1.pg_flags=2
	and t1.pg_next !=0
	and t1.pg_physaddr=t2.physaddr
	and t2.partnum=t3.partnum
!
let i=i+1
let tblpartnum=tblpartnum+1048576
done

8.61 How do I identify Server Versions from SQL?

On 14th November 2000 Leonids.Voroncovs@dati.lv (Leonid Voroncovs) wrote:-


SELECT FIRST 1 DBINFO( 'version', 'full' ) FROM systables;
SELECT FIRST 1 DBINFO( 'version', 'server-type' ) FROM syscolumns;
SELECT FIRST 1 DBINFO( 'version', 'major' ) FROM sysindexes;
SELECT FIRST 1 DBINFO( 'version', 'minor' ) FROM systables;
SELECT FIRST 1 DBINFO( 'version', 'os' ) FROM syscolumns;
SELECT FIRST 1 DBINFO( 'version', 'level' ) FROM sysindexes;

8.62 How do I tell which session is using the most logical log space?

On 9th November 2000 rferdy@americasm01.nt.com (Rudy Fernandes) wrote:-

Here's a script that determines the session "occupying" the maximum log space. Its driven by the "onstat -x" command


#!/bin/ksh
# Extracts the following
#  1. The earliest log with an open trx.
#  2. The session information of the user who is running it

EARLY_WARNING=100
OPENTRX=`onstat -x | \
   grep "^.............-" | \
   grep -v "^................................0" | \
   awk '{print $5, $3}' | sort -n | head -1`

if [ "$OPENTRX" = "" ]; then
   echo No open transactions found.
   exit 0
fi

set $OPENTRX
EARLIEST_USED_LOG=$1
set `onstat -u | grep $2 | awk '{print $3, $4}'`
CULPRIT_SESSION=$1
CULPRIT_USER=$2
CURRENT_LOG=`onstat -l | grep "^.....................-C" | awk '{print $4}'`
LTXHWM=`onstat -c | grep "^LTXHWM" | awk '{print $2}'`
NO_OF_LOGS=`onstat -l | grep "\-.\-.\-" | wc -l`
LOGS_USED_BY_CULPRIT=`expr $CURRENT_LOG - $EARLIEST_USED_LOG`
PROBLEM_THRESHOLD=`expr $NO_OF_LOGS \* $LTXHWM \* $EARLY_WARNING  / 10000`

clear
echo "
Earliest Log with Open Transaction :  $EARLIEST_USED_LOG
Current Log                        :  $CURRENT_LOG
Logs used by Culprit               :  $LOGS_USED_BY_CULPRIT
User with Culprit Transaction      :  $CULPRIT_USER
Session with Culprit Transaction   :  $CULPRIT_SESSION

High Water Mark                    :  $LTXHWM Percent
Number of Logs                     :  $NO_OF_LOGS
Problem Threshold # of logs        :  $PROBLEM_THRESHOLD
"
if [ $LOGS_USED_BY_CULPRIT -gt $PROBLEM_THRESHOLD ]; then
   echo
   echo
   echo PROBLEMS AHOY!!!
fi

onstat -g sql $1

# Also
select logbeg, nlocks from sysmaster:systxptab
where logbeg > 0
order by logbeg;

8.63 Is the High Performance Loader (HPL) really that quick?

On 24th January 2002 jparker@artentech.com (Jack Parker) wrote:-

HPL writes to multiple output files and uses a light scan. You can get unload rates of 5GB/CPU/HR with it. You can't do that with dbaccess sql.

8.64 Can I connect to the database server without connecting to a database?

On 28th August 2003 jleffler@earthlink.net (Jonathan Leffler) wrote:-

In ESQL/C, you can connect to the database server (and not to a database within the server) using:


EXEC SQL CONNECT TO '@dbserver' [AS ]
     [USER  USING ] [WITH CONCURRENT TRANSACTIONS];

8.65 How do I run HPL job without the GUI?

On 4th July 2001 vze2qjg5@verizon.net (Jack Parker) wrote:-

onpload -p project -j job -f[l|u] (load or unload)

8.66 Are there any tools that can display table relationships visually?

On 27th September 2001 glynbo@blueyonder.co.uk (Glyn Balmer) wrote:-

Try ER/Studio from www.embarcadero.co.uk

8.67 Are there any tools that check SQL for portability?

On 13th October 2003 jarl@mimer.com (Jarl Hermansson) wrote:-

An easy way to find out if an SQL statement is standard compliant is to use the SQL Validator, a free online tool verifying SQL standard compliance:

developer.mimer.se/validator/

8.68 What is the ISO standard date format?

On 2nd December 2002 "Andrew" wrote:-

yyyymmdd / yyyy-mm-dd (NOT yyyy/mm/dd) - ie 20021202; IS the standard international date format as defined by ISO 8601 and is also the ANSI standard, and DOES work with Informix. It should -

I've used it on a fairly big data migration project - and habitually use it when transferring from one system to another.

While I've only used this format with; Informix 9.21, MS-SQLServer 6.5 - 2000, SQLAnywhere, Access and O*acle RdB (and while it's a long time ago I'm also pretty sure it worked with XBase and IDMS) I'd be surprised to find a serious product that did not accept it without issue.

8.69 What does the "mode" column indicate in the sysprocedures table?

On 24th January 2002 prk25@yahoo.com (keith) wrote:-

Mode field indicates procedure type (i.e. How it is created):

O = Owner (who cretated it)
D = DBA (Created as a DBA SP)
P = Protected (cannot be deleted)
R = restricted (Created with a specific user different from actual
creator)

As far as how SPs with O and R modes are executed goes: R routines execute as if like thay are O routines, except for remote databases (uses executor's permissions instead of the owner's)

8.70 How can I drop a database in a down dbspace?

On 31st March 2003 michael.mueller@kay-mueller.de (Michael Mueller) wrote:-

From 9.21 on there is an (undocumented as far as I know) environment variable called FORCE_DB_DROP which would allow you to drop a database in a down or dropped dbspace. But unfortunately this in not implemented in 7.x.

8.71 How can I find the number of index and data pages for a table from sysmaster?

On 9th February 2001 elugtu@my-deja.com (Norman Erickson Lugtu) wrote:-

You can find it in sysptnbit, try this SQL:


select txt,count(pb_bitmap)
from sysptnbit a,
     flags_text b,
     your_database:systables c
where a.pb_bitmap = b.flags and
      b.tabname = 'sysptnbit'
and   a.pb_partnum=c.partnum and
      c.tabname = 'your_table_here'
group by 1;

On 11th May 2005 david@smooth1.co.uk (David Williams) wrote:-

Sample output from IDS 10 under Windows is:-

Data Page with Room for another Row                               1
Data Page without Room for another Row                          343
Index Page or Bitmap Page                                         1

8.72 How can I debug dbschema when it fails?

On 25th April 2003 rsarkar@us.ibm.com (Rajib Sarkar) wrote:-


export SQDEBUG=1
export SQEXPLAIN=1
dbschema ....

You will get an sqexplain.out file generated in the directory you run the command and there will be some *.time files created in the /tmp directories.

8.73 How can I debug onbar when it fails?

On 8th March 2001 "anonymous" wrote:-

You can set BAR_DEBUG in the onconfig file
BAR_DEBUG ValueDescription
2This will basically dump a message to the debug file every time onbar goes into a function. It also dumps a messaage to the log every time is exits a function along with that functions return code.
4This will have Onbar dump information about Onbar doing parallel operations.
5This will have Onbar dump information about objects that are being backed up/restored. Also dump info from the act_node structure which corresponds with the bar_action table.
6Same as Number 5.
7Onbar dump the contents of the ins_node structure, which corresponds with the bar_instance table. Also displays modifications to the bar_action table. Shows info about logical logs that are to be restored. Displays the list of objects that need to be restored. SQL statements that are done on the sysutils database along with some SQLCODES that were returned.
8Onbar will dump that page header of ALL pages that it archives/restores. WARNING This will take quit a bit of space.
9Dumps the contents of the bar_ins structure after it had been initialized. Dumps the contents of the obeject descriptors that are to restored during a cold restore.

8.74 When should I use varchars?

On 29th June 2001 vze2qjg5@verizon.net (Jack Parker) wrote:-

1 - you don't get light scans against a table with varchars. (Unless it's XPS). If you're strictly OLTP then you might not care.

2 - there is some overhead associate with the varchar, so doing a varchar(4) doesn't really buy you much - I don't consider it unless the char in question is at least 10 long (see below for qualification).

3 - You need to pay attention to the min length available in the varchar. When a row containing a varchar is updated, if the data no longer fits into it's original spot, it will be moved to the end of the table (delete/insert). If this is an active table, this can be a severe performance problem. Hence the importance of the MIN value - it reserves space so that as long as your column stays below or at the MIN size, it will fit into the same location.

4 - When a row containing a varchar is written to disk, the engine figures the MAX size of the row before looking for a free space. So even if your row is actually 40 long and there is a space that's 48 long to slip it into, if the max is 52, then it goes onto a new page.

So - my qualification is to use varchars when:

8.75 How does Enterprise Replication do timestamp conflict resolution?

On 26th September 2001 mpruet@home.com (Madison Pruet) wrote:-

There seems to be a bit of confusion with what ER uses for timestamp conflict resolution. It uses the output of time() which is defined as "time in seconds since 00:00:00 UDT, January 1, 1907". It is not timezone sensitive or daylight time sensitive. In order to get the local time (which is timezone/daylight sensitive) you must call localtime().

There is no problem replicating across multiple time zones, or between time zones where one is in daylight savings and the other is not. There are many folks doing that now.

There was a problem with NT a few years ago because VC++ 4.0 was not calculating time values correctly. The main reason for this is because Micorsoft maintains the time using the local time as the base instead of GMT time. This adds a bit of complexity to date calculation that they were mis-handling in the libc routines. The net result was that time() returned a value about 6 hours off for those time zones that do not switch to daylight time. That meant that states such as Arizona in the US, and many of the Asia time zones had problems. This was partially corrected with VC++ 5.0 and totally corrected with VC++ 6.0. Since IDS 7.30 used VC++ 5.0 and IDS 7.2x used VC++ 4.0, those NT platforms had some problems.

8.76 How can I calculate the remaining extents for a table?

On 9th December 2002 stefan@weideneder.de (Stefan Weideneder) wrote:-


select {+ORDERED,INDEX(a,syspaghdridx)}
trunc(pg_frcnt/8) frext, partaddr( dbsnum, pg_pagenum ) partnum from
sysdbspaces b, syspaghdr a
where a.pg_partnum = partaddr( dbsnum, 1 )
and pg_flags = 2 into temp ggg with no log;
select dbsname, tabname, frext from systabnames a, ggg b
where a.partnum = b.partnum
into temp ggg with no log;

{Jack Parker added}
select dbsname[1.25], tabname[1,25], frext
from systabnames a, ggg b
where a.partnum = b.partnum
order by frext;

syspaghdr has an index on "partnum" and I dynamically create the partnum of the TableSpace TableSpace of all dbspaces ( might be optimized to use only real data dbspaces ). To avoid bitmap and free pages you filtered the page flags = 2. The second query retrieves the tablenames and database names from systabnames instead of your "slot2" entry ( systabnames is directly mapped to slot 2, sysptnkey means slot 4, sysptncol = slot 3, sysptnhdr = slot 1, sysptnext = slot 5 )

The time it takes to process the query should be equal to the time it takes to read all partition pages.

8.77 Is it ok to use buffered logging for databases?

On 9th December 2002 mpruet@home.com (Madison Pruet) wrote:-

I would not recommend it. We can not replicate the data until the buffer if flushed. If you use buffered logging, then it could be that the commit log record is still in the buffer. That would prevent us from replicating that transaction.

Also, in pre 9.3 environments, all of the internal CDR transactions always use unbuffered logging. Therefor, if the user database is buffered and the CDR activity is not, then you will experience a significant increase in log consumption when spooling transactions into the stable queue. This can cause an increase in the probability of getting into a DDRBLOCK state.

In 9.3, we are better able to deal with user databases that use buffered logging. But we are still unable to process any transaction until the log buffer containing the commit is flushed. It is necessary to wait until the buffer has been flushed because otherwise, there could be other log records being added to that individual log buffer. We still normally snoop from the log buffer instead of having to read from the disk.

8.78 Should I fragment my indicies?

On 17th April 2001 JParker@engage.com (Jack Parker) wrote:-

Pro's of framgentation -

Con's:

So in other words don't do this for small tables. If you only have one disk - then it's no big deal which you choose.

8.79 Can I see what temporary tables are in use?

On 12th November 2001 geoff.roff@bbslimited.co.uk (Geoff Roff) wrote:-


SELECT SUBSTR(s.name,1,11) dbs 
, TRIM(n.dbsname) || ":" || TRIM(n.owner) || ":" || 
TRIM(n.tabname) table , 
i.ti_nptotal pages 
FROM systabnames n,
systabinfo i,
sysdbspaces s 
WHERE bitval(i.ti_flags, "0x0020") = 1 
AND i.ti_partnum = n.partnum 
AND SUBSTR(HEX(s.dbsnum),8,3) = SUBSTR(HEX(i.ti_partnum),3,3)
ORDER BY 1,3 DESC 

8.80 How can I calculate suggested extent sizes for a table?

On 21th November 2001 abent@aetinc.com (Mark Raiff) wrote:-

The following document was sent to me by Informix. It discusses how to calculate extent sizes based on rows.

Informix 7.31 on HP-UX 11.0

TROUBLESHOOTING AND PROPER SIZING OF EXTENTS

Summary:

Detail:

For example, a schema that has a row size of 196 bytes will require 200 bytes of storage on a data page (196 + 4 bytes for the slot entry). In a 2KB system, ten rows will fit on a page with 20 bytes unused. Twenty rows will fit on a 4KB page with 68 unused bytes per page. Those 68 bytes will never be used. If 50000 rows are expected to be loaded with 10 percent monthly growth anticipated for the table, then 8000 2KB pages are required for the initial load (5000 pages) plus six-months expansion (3000 pages). For a 4KB system the results are 2500 pages initial plus 1500 pages for expansion for a total of 4000 pages. Note that in each case the Extent Size is 16000KB.

Calculating Next Extent Size

The value for Next Size should be calculated according to expected growth patterns, following the same formula for calculating the first extent size.

Calculating Extents for an index

In most indexes on a medium to large table, leaf nodes make up over 95 percent of all of the pages in the index. To simplify our calculations, we'll only consider leaf nodes, then add 5 percent for branch and root nodes. Leaf nodes contain index entries.

Each index entry is comprised of a key value, whose length is determined by adding the lengths of the indexed columns, and a list of one or more pointers to data rows. To calculate the length of an average index entry:

8.81 How can I calculate the memory needed for a hash join?

On 12th January 2001 JParker@engage.com (Jack Parker) wrote:-

You can calculate the amount of memory needed to run a hash join in memory without overflowing to temp as:

So if you are joining 100,000 rows against 1,000,000 rows. The row size of the smaller table is 54, and the key is an integer (length 4) you will need roughly:

	(32 + 4 + 54) * 100,000 = 9,000,000 or 9MB of memory.

If you had 250MB of DS_TOT_MEMORY, this would be roughly 1/25th or a PDQPRIORITY=4.Memory is actually allocated in quantums, in your case 128. So the memory allocated to your query will be a multiple of that quantum.

If your hash table fits into memory, your query will scream. If you hash table is less than 2x the amount of memory you need, the overflow won't be that bad. If your hash table is over 2x, the overflow will seriously degrade your query. That is a rule of thumb pulled out of my experience or nether regions. If you are going to have overflow, make sure you have sufficient TEMP spindles to handle the overflow.

8.82 How do I setup Enterprise Replication (ER)?

On 7th December 2001 "anonymous" wrote:-

Preparing for ER:

Setting up the SQLHOSTS file on UNIX:

All machines where ER will be running on need to have a Trusted relationship. To determine if your host is trusted by the other host(s) in the ER system - use dbaccess -> connection -> connect and connect to the Group Name and the Servername of the otherhost(s).

Before using dbaccess to test this however - you need to set up the SQLHOSTS file on all servers involved. Below is an example of the SQLHOSTS file from the UNIX server 'unix1' before modified for ER:

ol_unix1shm onipcshm unix1 ol_unix1shm
ol_unix1tcp oltlitcp unix1 ol_unix1tcp
ol_unix2tcp oltlitcp unix2 ol_unix2tcp

Also - the SQLHOSTS file on the other UNIX server 'unix2' would look like this:

ol_unix2shm onipcshm unix2 ol_unix2shm
ol_unix2tcp oltlitcp unix2 ol_unix2tcp
ol_unix1tcp oltlitcp unix1 ol_unix1tcp

Currently, (based upon the files above) you can see that the two servers already have connectivity to each other. This means that the /etc/services file has entries for the TCP services listed in the SQLHOSTS file, and there may also be entries in the /etc/hosts or .rhost files for these servers. (Note: the TLI connection method is only supported on certain platforms - please check the Machine Specific Notes for you engine to find out if your platform support TLI, or SOC connections.)

The following modifications will need to be made to enable ER to be defined on each of these servers.

The Unix1 server:

unix1grp group  - -  i=1
ol_unix1tcp oltlitcp unix1 ol_unix1tcp g=unix1grp
ol_unix1shm onipcshm unix1 ol_unix1shm g=unix1grp

The Unix2 server:

unix2grp group  - -  i=2
ol_unix2tcp oltlitcp unix2 ol_unix2tcp g=unix2grp
ol_unix2shm onipcshm unix2 ol_unix2shm g=unix2grp

Verify that the INFORMIXSERVER environment variable is set for the network connection and not the shared memory connection. For this example set INFORMIXSERVER to "ol_unix1tcp". Also, pre-9.20, set the DBSERVERNAME in the onconfig file for both servers to the TCP connection.

Please check out bug #113269 for more info on this.

Setting up the SQLHOSTS registry entries on NT:

There is a demo program in $INFORMIXDIR/demo named ershed.exe - this is the SQLHOSTS editor. This tool can read an SQLHOSTS (that has been copied from a UNIX system) and write it into the registry on NT. If you are just setting up NT boxes however, this tool can still be helpful - as you can create a standard SQLHOSTS file (as you would on UNIX) and use it write all the registry entries needed. If you feel uncomfortable using this tool - then the release notes on NT and UNIX detail two other ways to accomplish the same task - 1) by using the IECC client, and 2) by adding the entries directly to the registry.

After the registry has a group entry for each server - use the SetNet32 utility to populate the host information with a username and password for each host. Then use I-Login to make a connection to each host - to ensure connectivity. If you have an engine installed on NT - you can use dbaccess to insure this connectivity.

Using the Command Line on UNIX or NT:


On 4th May 2001 mpruet@ihome.com (Madison Pruet) wrote:-

7.31 has much better performance characteristics than 7.30. But 9.3 has even better. Since latency (and backlog) have to do with performance, I'm going to discuss this from a performance standpoint.

The big issue is not so much the number of tables being replicated, or even the size of the database. The big issue for replication performance is 1) transaction rate, 2) transaction size, and 3) degree of parallelism. There are significant performance issues with replication which are caused because of things like referential integrety rules and order of operations against a given row. Obviously, the simplest rule is to always apply data on the target in the exact same order that was done on the source. The only problem with this is that if you have a multi-processor on the source, you will be creating transactions at a rate roughly proportional to the number of processors on your system. However, if strict ordering is done on the target, then you will be processing serially by commit order. Needless to say, a one processer replay will not keep up with a multi-processor source instance.

You can define the replicates as parallel. What this means is that they can run in parallel with other replicates defined as paralle. It does not mean that the operations within that replicate can be applied in parallel with each other. This can increase the degree of parallelism on the target instance. Also, you can partition the replicates so that multiple replicates are defined on the same tables. This is most often by using the mod function within the where clause of the replicate definition. However, if you have referential integrety defined between tables, then the parent/child tables should be defined to be within the same replicate group.

This sounds complicated, and it is. In 9.3 we are dynamically managing replay parallelism so that this partitioning and paralle stuff is not necessary. Also, we have added many things which tend to reduce the bottlenecks in the pre-9.3 product.

But your concern is with 7.31, so I need to address that....

It is good that you are using consultants to get this set up. I suspect that they will be defining parallel replicates and partitioning tables by using the mod function. Be sure that you have the patch for Bug 120797 in place.

To monitor:

There are also some tuning things that I've mentioned in previous entries to this news group. I'm mention them again now.

8.83 How do I handle a long transaction?

On 5th April 2001 jsalomon@bn.com (Jacob Salomon) wrote:-

If you are willing to use "onarchive" once, there is an off-line archive facility that will may be able to clear the logs. It's called "ondatartr" (OnLine Data Retrieve) and is described in chapter 9 of the "IDS 7.3 Archive and Backup Guide".

Having done this only once in my entire career (and that was in a training class) I may be shaky about the details but the command should be:

$ ondatartr /logfile /tape=/dev/your-TapeDrive

This command should be executed with the server off line - no shared memory, no recovery in progress. Also, it might be a good idea to change the LOGTAPE parameter to the name of a real device.

You may want to start this with tech support on the line to hold your hand.The idea is that once you have archived the logs, they will be freed up.

On the other hand, since the logs filled up in middle of a transaction, it might just try to roll the logs back, running into the sdame brick wall. You'll never know until you try it.

Also, when it's over and you are back on-line, you will need to catalog the new tape via the CATALOG option in onarchive.

OK, if that fails, you may need to call for Informix Advanced Support and give them dial-in access to your system. After making y'all sign and fax confidentiality agreements, they may download to your system a utility called tbone (Pronounced TB-one; the authors were real sensitive about folks pronouncing it T-Bone, as in a type of steak. ;-) This will create a new log file for you, from off-line, allowing the transaction to roll back cleanly into the new log space. (I have heard rumors of undocumented oncheck options that have obviated the need for a C compiler, but have never seen these in action.)

BTW, do NOT try to grab a copy of the C code before the engineer has removed it; invariably, a customer who does this will use it, mess something up and Informix will find out about it when they call tech support. You will then be sued. Do NOT ask the engineer to leave it there; he will be fired if he does so.

On 14th May 2005 david@smooth1.co.uk (David Williams) wrote:-

Note: onarchive was dropped in 9.30.

Also I recommend LTXHWM 30 and LTXEHWM 40. When a long transaction hits LTXHWM and starts to rollback it starts writing rollback records to the logical log. These logical log records are normally smaller then the equivalnet logical log records that are part of the transaction BUT NOT ALWAYS!. LTXEHWM of 40 means that no other sessions perform logical log activity so even if the rollback records are larger you still have more than the size of the original logical log records free. Informix recommendations were something like 70/80 and went to 50/60 and keep getting smaller.

Up the total size of the logical logs. These days with 72Gb disks allocating 4Gb to logical logs is nothing compared to the disk space we have! I prefer lots of small logical logs (they only get backed up when they are full). Remember you can have up to 32767 logical logs!.