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";
}
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.htmlAnother unofficial website
http://www.geocities.com/Heartland/Acres/4927/onbar.htmlprovides an excellent overview of how onbar functions.
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.
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!
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.
In Codd 1990, Codd extended the 12 rules to 18 to include rules on catalog, data types (domains), authorisation etc.
Bibliography
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
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.
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.
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.
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
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;
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;
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.
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];
On 4th July 2001 vze2qjg5@verizon.net (Jack Parker) wrote:-
onpload -p project -j job -f[l|u] (load or unload)
On 27th September 2001 glynbo@blueyonder.co.uk (Glyn Balmer) wrote:-
Try ER/Studio from www.embarcadero.co.ukOn 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/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.
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)
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.
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
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.
On 8th March 2001 "anonymous" wrote:-
You can set BAR_DEBUG in the onconfig file
| BAR_DEBUG Value | Description |
|---|---|
| 2 | This 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. |
| 4 | This will have Onbar dump information about Onbar doing parallel operations. |
| 5 | This 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. |
| 6 | Same as Number 5. |
| 7 | Onbar 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. |
| 8 | Onbar will dump that page header of ALL pages that it archives/restores. WARNING This will take quit a bit of space. |
| 9 | Dumps 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. |
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:
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.
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.
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.
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.
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
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:
Determine the number of pages needed by dividing the result of the previous step into the anticipated number of rows for the table. Remember to leave room for growth!
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:
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.
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:
UNIX: cdr define server -c unix1grp -s rootdbs -r
rootdbs \
-A /tmp -R /tmp -I unix1grp
NT: cdr define server -c ai_airprt1 -A /tmp -R /tmp \
-I ai_airprt1 -S ai_unix1grp
Update Anywhere Syntax: cdr define repl -c unix1grp -C"timestamp" -S tran -A repA \ "database@ol_unix1tcp:informix.tablename" "select * from tablename" \ "database@ol_unix2tcp:informix.tablename" "select * from tablename" Primary/Target Syntax: cdr define repl -c unix1grp -C"timestamp" -S tran -A repA \ "P database@ol_unix1tcp:informix.tablename" "select * from tablename" \ "R database@ol_unix2tcp:informix.tablename" "select * from tablename"
cdr start repl repA unix1grp unix2grp
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.
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!.
On 14th February 2014 yunyaoqu@gmail.com (FRANK) wrote:-
Apparently, 11.50 does not have in-place Alter capability.
On 14th February 2014 marcus.haarmann@midoco.de (Marcus Haarmann) wrote:-
Add a new int8 column, fill it in multiple transactions with the original value using a stored procedure and at the end rename the columns and drop the original one. We have done that in multiple cases to avoid too long locks on the table.