Thursday, 8 May 2008
|
| I want a String from Position N until End Matthias 05:20:15 |
| | Hello - i need your brain...
Is there a function that select a column from pos a until max
(length of the column) ?
I need something like this:
select concat("new-string", substr(column_name,10, max-length-of-col ))
from mytable;
- i have to change the fist n characters of a column.
and this:
select concat("new-string", substr(column_name,10,100)) from table;
is not working because the column can be more than 100 characters long.
is there someone with a idea ?
|
| Options |  7 answers | Add comment |
Thursday, 13 December 2007
|
| Performance tuning: troubling long checkpoints and other issues Jim Kenedy 09:22:17 |
| | Hello All,
Here's some quick history... Recently the number of users has
increased to over 500 sessions. All our users are now complaining of
poor performance. Our checkpoints are slow to complete (4 seconds and
up to 13 seconds during peak activity) and the CPU activity is
constantly over 95%. I've been tweaking LRU, CLEANERS, BUFFERS and
I'm now more perplexed than ever.
This is an OLTP system with about 5 batch jobs that are constantly run
during the day. No other processes other than these batch jobs are run
on the database server. The system is not swapping and memory used is
sitting at 95%. At one point, a number of years ago, I tried kaio but
on this SCO platform it actually performed worse; but perhaps I should
try it once again.
Informix Dynamic Server Version 7.31.UD8
SCO OpenServer 5.0.6
Dual Pentium III Xeon 1GHz, 4GB ram, 12 * 18GB hard drives arranged in
RAID 1 pairs (the first pair is dedicated to the O/S, the rest to
Informix)
Update statistics is run nightly using one of the very good IIUG 4GL
programs.
We only have one temp dbspace since we cannot allocate more because of
an application incompatibility (it uses table rowids).
I've exhausted input from a number of performance tuning books and
searches through this newsgroup.
Any help would be greatly appreciated...
Thanks,
Ron
--------------------------------------------------
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /dev/rdbs_root # Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 1024000 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored
root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS rootdbs # Location (dbspace) of physical log
PHYSFILE 240000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 60 # Number of logical log files
LOGSIZE 1000 # Logical log size (Kbytes)
# Diagnostics
MSGPATH /u/informix7/online.log # System message log file path
CONSOLE /u/informix7/online.log # System console message path
ALARMPROGRAM /u/informix7/etc/log_full.sh # Alarm program path
SYSALARMPROGRAM /u/informix7/etc/evidence.sh # System Alarm program
path
TBLSPACE_STATS 1
# System Archive Tape Device
TAPEDEV secondary:/dev/rStp0 # Tape device path
TAPEBLK 64 # Tape block size (Kbytes)
TAPESIZE 50000000 # Maximum amount of data to put on tape
(Kbytes)
# Log Archive Tape Device
LTAPEDEV /u/informix7/db-logs/logtape # Log tape device path
LTAPEBLK 64 # Log tape block size (Kbytes)
LTAPESIZE 25000000 # Max amount of data to put on log tape
(Kbytes)
# Optical
STAGEBLOB # Informix Dynamic Server/Optical
staging area
# System Configuration
SERVERNUM 0 # Unique id corresponding to a Dynamic
Server instance
DBSERVERNAME local_on # Name of default database server
DBSERVERALIASES primary # List of alternate dbservernames
NETTYPE ipcshm,2,50,CPU # Configure poll thread(s) for nettype
NETTYPE tlitcp,2,300,NET # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in
distributed env.
RESIDENT 1 # Forced residency flag (Yes = 1, No =
0)
MULTIPROCESSOR 1 # 0 for single-processor, 1 for
multi-processor
NUMCPUVPS 2 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps
to one
NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 2 # Affinity number of processors
# Shared Memory Parameters
LOCKS 150000 # Maximum number of locks
# Jan 18-06...
#BUFFERS 700000 # Maximum number of shared buffers
BUFFERS 600000 # Maximum number of shared buffers
# END Jan 16-06
# Jan 19-06: Try to decrease maxlen in onstat -q ioq and have one
# aio io/s in onstat -g iov ~1
#NUMAIOVPS 16 # Number of IO vps
NUMAIOVPS 18 # Number of IO vps
# END Jan 19-06
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
LOGSMAX 60 # Maximum number of logical log files
# Jan 19-06: set CLEANERS >= LRUS
#CLEANERS 32 # Number of buffer cleaner processes
CLEANERS 127 # Number of buffer cleaner processes
# END Jan 19-06
SHMBASE 0x10000000 # Shared memory base address
# Jan 18-06
#SHMVIRTSIZE 153600 # initial virtual shared memory
segment size
SHMVIRTSIZE 235520 # initial virtual shared memory segment
size
#SHMADD 8192 # Size of new shared memory segments
(Kbytes)
SHMADD 16384 # Size of new shared memory segments
(Kbytes)
# END Jan 18-06
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
# Jan 19-06: Pump this up and avoid = 64, 96 to lower bufwaits
# and reduce the buffer turnover rate
# and potentially lower checkpoints
#LRUS 64 # Number of LRU queues
LRUS 127 # Number of LRU queues
# END Jan 19-06
LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning
limit
LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark
percentage
LTXEHWM 60 # Long transaction high water mark
(exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # Stack size (Kbytes)
# System Page Size
# BUFFSIZE - Dynamic Server no longer supports this configuration
parameter.
# To determine the page size used by Dynamic Server on your
platform
# see the last line of output from the command, 'onstat -b'.
# Recovery Variables
# OFF_RECVRY_THREADS:
# Number of parallel worker threads during fast recovery or an offline
restore.
# ON_RECVRY_THREADS:
# Number of parallel worker threads during an online restore.
OFF_RECVRY_THREADS 10 # Default number of offline worker
threads
ON_RECVRY_THREADS 1 # Default number of online worker
threads
# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
DRAUTO 0 # DR automatic switchover
DRINTERVAL 30 # DR max time between DR buffer flushes
(in sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /u/informix7/etc/dr.lostfound # DR lost+found file path
# CDR Variables
CDR_LOGBUFFERS 2048 # size of log reading buffer pool
(Kbytes)
CDR_EVALTHREADS 1,2 # evaluator threads
(per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR
queue (Kb
ytes)
CDR_LOGDELTA 30 # % of log space allowed in queue
memory
CDR_NUMCONNECT 16 # Expected connections per server
CDR_NIFRETRY 300 # Connection retry (seconds)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0
none, 9 ma
x)
# Backup/Restore variables
BAR_ACT_LOG /u/informix7/bar_act.log # ON-Bar Log file - not in
/tmp please
BAR_DEBUG_LOG /u/informix7/bar_dbug.log
# ON-Bar Debug Log - not in /tmp please
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
# Informix Storage Manager variables
ISM_DATA_POOL ISMData # If the data pool name is changed, be
sure to
# update $INFORMIXDIR/bin/onbar. Change to
# ism_catalog -create_bootstrap -pool <new name>
ISM_LOG_POOL ISMLogs
# Read Ahead Variables
RA_PAGES # Number of pages to attempt to read
ahead
RA_THRESHOLD # Number of pages left before next
group
# DBSPACETEMP:
# Dynamic Server equivalent of DBTEMP for SE. This is the list of
dbspaces
# that the Dynamic Server SQL Engine will use to create temp tables
etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the Dynamic Server system is brought online. If not specified,
or if
# all dbspaces specified are invalid, various ad hoc queries will
create
# temporary files in /tmp instead.
DBSPACETEMP tempdbs # Default temp dbspaces
# DUMP*:
# The following parameters control the type of diagnostics information
which
# is preserved when an unanticipated error condition (assertion
failure) occurs
# during Dynamic Server operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
DUMPDIR /tmp # Preserve diagnostics in this
directory
DUMPSHMEM 0 # Dump a copy of shared memory
DUMPGCORE 0 # Dump a core image using 'gcore'
DUMPCORE 0 # Dump a core image (Warning:this
aborts Dynamic
Server)
DUMPCNT 1 # Number of shared memory or gcore
dumps for
# a single user's session
FILLFACTOR 90 # Fill factor for building indexes
# method for Dynamic Server to use when determining current time
USEOSTIME 0 # 0: use internal time(fast), 1: get
time from O
S(slow)
# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES # Maximum number of decision support
queries
DS_TOTAL_MEMORY # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support
scans
DATASKIP off # List of dbspaces to skip
# OPTCOMPIND
# 0 => Nested loop joins will be preferred (where
# possible) over sortmerge joins and hash joins.
# 1 => If the transaction isolation mode is not
# "repeatable read", optimizer behaves as in (2)
# below. Otherwise it behaves as in (0) above.
# 2 => Use costs regardless of the transaction isolation
# mode. Nested loop joins are not necessarily
# preferred. Optimizer bases its decision purely
# on costs.
OPTCOMPIND 2 # To hint the optimizer
ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1
= ABORT,
2 = WAIT
LBU_PRESERVE 1 # Preserve last log for log backup
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)
# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT 0
# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL -1
# Optimizer DIRECTIVES ON (1/Default) or OFF (0)
DIRECTIVES 1
# Status of restartable restore
RESTARTABLE_RESTORE on
--------------------------------------------------
IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line (Prim) --
Up 07:14:23 -- 1527808 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
28338861 43035627 934277718 96.97 2014044 12510577 11428871 82.38
isamtot open start read write rewrite delete commit
rollbk
485287136 5433269 20156634 395955853 3811044 702170 376400 312198
426
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 13083.09 6945.83 86 173
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
3461043 591 240729382 0 0 534 90232 1131751
ixda-RA idx-RA da-RA RA-pgsused lchwaits
5164804 173605 14163938 19493127 177765
ReadAhead Utilization (UR): 99.9527 %
Bufwaits Ratio(BR): 6.35468 %
Average Buffer Turnover Rate (BTR): 12.5388/HR
Stats reset 07:14:22 ago.
--------------------------------------------------
Segment Summary:
id key addr size ovhd class blkused blkfree
202 1381386241 10000000 1312817152 39768 R* 160061 195
203 1381386242 5e400000 243269632 4352 V 27719 1977
204 1381386243 6cc00000 4194304 704 M 311 201
205 1381386244 6d000000 4194304 704 M 309 203
Total: - - 1564475392 - - 188400 2576
(* segment locked in memory)
--------------------------------------------------
Fg Writes LRU Writes Chunk Writes
0 652682 630723
--------------------------------------------------
Dbspaces
address number flags fchunk nchunks flags owner name
5e45c150 1 1 1 1 N informix rootdbs
5e487f20 2 1 2 2 N informix logsdbs
5e493f30 3 2001 4 1 N T informix tempdbs
5ef44b18 4 1 5 1 N informix miscdbs
5ef44bd8 5 1 6 2 N informix ser1dbs
5ef44c98 6 1 8 3 N informix std1dbs
5ef44d58 7 1 10 3 N informix std2dbs
5ef44e18 8 1 12 3 N informix std3dbs
5ef44ed8 9 1 14 3 N informix std4dbs
5ef44f98 10 1 16 3 N informix std5dbs
5ef45058 11 1 18 3 N informix std6dbs
5ef45118 12 1 20 2 N informix std7dbs
5ef451d8 13 1 22 2 N informix std8dbs
13 active, 2047 maximum
Chunks
address chk/dbs offset size free bpages flags pathname
5e45c210 1 1 0 512000 389963 PO-
/dev/rdbs_root
5e492e30 2 2 0 1024000 23947 PO-
/dev/rdbs_logs_1
5e492f30 3 2 0 512000 11997 PO-
/dev/rdbs_logs_2
5e493030 4 3 0 512000 453380 PO-
/dev/rdbs_temp
5e493130 5 4 0 512000 457696 PO-
/dev/rdbs_misc
5e493230 6 5 0 1024000 110700 PO-
/dev/rdbs_ser1_1
5e493330 7 5 0 1024000 532469 PO-
/dev/rdbs_ser1_2
5e493430 8 6 0 1024000 228 PO-
/dev/rdbs_std1_1
5e493530 9 6 0 1024000 33980 PO-
/dev/rdbs_std1_2
5e493630 10 7 0 1024000 2 PO-
/dev/rdbs_std2_1
5e493730 11 7 0 1024000 192568 PO-
/dev/rdbs_std2_2
5e493830 12 8 0 1024000 22409 PO-
/dev/rdbs_std3_1
5e493930 13 8 0 1024000 55258 PO-
/dev/rdbs_std3_2
5e493a30 14 9 0 1024000 145473 PO-
/dev/rdbs_std4_1
5e493b30 15 9 0 1024000 36165 PO-
/dev/rdbs_std4_2
5e493c30 16 10 0 1024000 67826 PO-
/dev/rdbs_std5_1
5e493d30 17 10 0 1024000 28078 PO-
/dev/rdbs_std5_2
5e493e30 18 11 0 1024000 54 PO-
/dev/rdbs_std6_1
5ef44018 19 11 0 1024000 18787 PO-
/dev/rdbs_std6_2
5ef44118 20 12 0 1024000 17948 PO-
/dev/rdbs_std7_1
5ef44218 21 12 0 1024000 275227 PO-
/dev/rdbs_std7_2
5ef44318 22 13 0 1024000 435972 PO-
/dev/rdbs_std8_1
5ef44418 23 13 0 1024000 0 PO-
/dev/rdbs_std8_2
5ef44518 24 6 0 1024000 783893 PO-
/dev/rdbs_std1_3
5ef44618 25 7 0 1024000 1023997 PO-
/dev/rdbs_std2_3
5ef44718 26 8 0 1024000 802958 PO-
/dev/rdbs_std3_3
5ef44818 27 9 0 1024000 1023997 PO-
/dev/rdbs_std4_3
5ef44918 28 10 0 1024000 913533 PO-
/dev/rdbs_std5_3
5ef44a18 29 11 0 1024000 857653 PO-
/dev/rdbs_std6_3
29 active, 2047 maximum
--------------------------------------------------
Sar:
00:00:00 %usr %sys %wio %idle (-u)
08:00:02 11 3 4 83
08:20:01 14 6 44 36
08:40:00 25 9 51 15
09:00:04 29 12 49 11
09:20:01 30 14 45 11
09:40:00 35 17 32 16
10:00:04 24 18 33 25
10:20:01 50 26 22 3
10:40:00 39 24 30 7
11:00:05 33 20 31 15
11:20:01 34 19 32 15
11:40:00 44 25 26 5
12:00:04 37 24 30 8
12:20:00 26 18 48 7
12:40:00 35 19 34 12
13:00:04 38 23 32 7
13:20:00 50 30 16 4
13:40:00 52 24 22 2
14:00:03 63 34 2 1
--------------------------------------------------
AIO I/O vps:
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup
errors
msc 0 i 4.6 123371 0 0 0 123053 1.0
0
aio 0 s 282.5 7554428 6156928 1357672 0 6764843 1.1
0
aio 1 i 153.9 4116383 3385321 701987 0 4450497 0.9
0
aio 2 s 83.4 2229827 1806420 411309 0 2431384 0.9
0
aio 3 i 46.5 1244465 1013423 225073 0 1297927 1.0
0
aio 4 i 28.5 761015 615322 142551 0 769406 1.0
0
aio 5 i 18.7 500875 395848 103281 0 499687 1.0
0
aio 6 i 13.1 350703 264049 85473 0 343267 1.0
0
aio 7 i 9.4 251367 177146 73490 0 243967 1.0
0
aio 8 i 6.7 179619 116167 62928 0 174696 1.0
0
aio 9 i 4.8 129113 72863 55880 0 125591 1.0
0
aio 10 i 3.6 97510 46817 50498 0 93095 1.0
0
aio 11 i 2.9 76595 30429 46053 0 71724 1.1
0
aio 12 i 2.3 61692 20557 41049 0 58064 1.1
0
aio 13 i 1.9 51396 13779 37544 0 47839 1.1
0
aio 14 i 1.6 43143 9470 33624 0 39522 1.1
0
aio 15 i 1.4 38578 7120 31419 0 33127 1.2
0
aio 16 i 1.2 32924 5427 27469 0 27174 1.2
0
aio 17 i 1.1 29137 4209 24900 0 20466 1.4
0
pio 0 i 1.1 29090 0 29090 0 29084 1.0
0
lio 0 i 12.9 343649 0 343649 0 343161 1.0
0
--------------------------------------------------
AIO I/O queues:
q name/id len maxlen totalops dskread dskwrite dskcopy
adt 0 0 0 0 0 0 0
msc 0 0 2 123463 0 0 0
aio 0 0 17 2293347 53399 2144598 0
pio 0 0 1 29116 0 29116 0
lio 0 0 1 343923 0 343923 0
gfd 3 0 4 4495 4199 296 0
gfd 4 0 2 39992 39992 0 0
gfd 5 0 1 1 1 0 0
gfd 6 0 476 1477481 436586 1040895 0
gfd 7 0 8 11600 11593 7 0
gfd 8 0 8 31031 31026 5 0
gfd 9 0 1 1 1 0 0
gfd 10 0 16 2438089 2430462 7627 0
gfd 11 0 652 2114460 1900772 213688 0
gfd 12 0 43 463897 459983 3914 0
gfd 13 0 133 270922 249039 21883 0
gfd 14 0 24 2140186 2129933 10253 0
gfd 15 0 169 4364015 4236755 127260 0
gfd 16 0 250 2178948 2079900 99048 0
gfd 17 0 18 488508 473094 15414 0
gfd 18 0 18 1915670 1902514 13156 0
gfd 19 0 24 1159433 1150469 8964 0
gfd 20 0 16 702189 700656 1533 0
gfd 21 0 18 636072 628427 7645 0
gfd 22 0 24 5622262 5611241 11021 0
gfd 23 0 24 2106650 2096195 10455 0
gfd 24 0 17 410803 402257 8546 0
gfd 25 0 8 800966 800966 0 0
gfd 26 0 39 839544 787077 52467 0
gfd 27 0 1 1 1 0 0
gfd 28 0 71 461593 433817 27776 0
gfd 29 0 1 1 1 0 0
gfd 30 0 16 3951 3903 48 0
gfd 31 0 16 90865 86742 4123 0
--------------------------------------------------
127 buffer LRU queue pairs priority levels
# f/m pair total % of length LOW MED_LOW MED_HIGH HIGH
0 f 4720 99.8% 4710 0 4582 117 11
1 m 0.2% 10 0 10 0 0
2 f 4717 99.7% 4704 0 4581 107 16
3 m 0.3% 13 0 13 0 0
4 f 4721 99.8% 4710 0 4584 111 15
5 m 0.2% 11 0 11 0 0
6 f 4723 99.8% 4713 0 4544 157 12
7 m 0.2% 10 0 10 0 0
8 f 4723 99.7% 4711 0 4583 110 18
9 m 0.3% 12 0 12 0 0
10 f 4720 99.9% 4714 0 4592 107 15
11 m 0.1% 6 0 6 0 0
12 f 4719 99.8% 4710 0 4585 111 14
13 m 0.2% 9 0 9 0 0
14 f 4721 99.7% 4708 0 4594 108 6
15 m 0.3% 13 0 13 0 0
16 f 4723 99.7% 4710 0 4590 111 9
17 m 0.3% 13 0 13 0 0
18 f 4724 99.6% 4706 0 4571 121 14
19 m 0.4% 18 0 18 0 0
20 f 4720 99.7% 4705 0 4586 107 12
21 m 0.3% 15 0 15 0 0
22 f 4720 99.8% 4709 0 4580 115 14
23 m 0.2% 11 0 11 0 0
24 f 4724 99.9% 4718 0 4603 109 6
25 m 0.1% 6 0 6 0 0
26 f 4721 99.8% 4710 0 4560 139 11
27 m 0.2% 11 0 11 0 0
28 f 4721 99.5% 4698 0 4585 104 9
29 m 0.5% 23 0 23 0 0
30 f 4722 99.9% 4715 0 4588 115 12
31 m 0.1% 7 0 7 0 0
32 f 4721 99.9% 4714 0 4599 103 12
33 m 0.1% 7 0 7 0 0
34 f 4722 99.8% 4711 0 4586 113 12
35 m 0.2% 11 0 11 0 0
36 f 4721 99.7% 4707 0 4578 119 10
37 m 0.3% 14 0 14 0 0
38 f 4720 99.8% 4710 0 4569 124 17
39 m 0.2% 10 0 10 0 0
40 f 4719 99.8% 4709 0 4589 112 8
41 m 0.2% 10 0 10 0 0
42 f 4720 99.8% 4709 0 4580 120 9
43 m 0.2% 11 0 11 0 0
44 f 4722 99.8% 4711 0 4589 110 12
45 m 0.2% 11 0 11 0 0
46 f 4720 99.8% 4710 0 4587 109 14
47 m 0.2% 10 0 10 0 0
48 f 4721 99.8% 4710 0 4588 114 8
49 m 0.2% 11 0 11 0 0
50 f 4722 99.7% 4709 0 4588 111 10
51 m 0.3% 13 0 13 0 0
52 f 4722 99.6% 4701 0 4534 151 16
53 m 0.4% 21 0 20 1 0
54 f 4721 99.8% 4713 0 4588 116 9
55 m 0.2% 8 0 8 0 0
56 f 4721 99.6% 4702 0 4568 128 6
57 m 0.4% 19 0 19 0 0
58 f 4723 99.6% 4706 0 4582 116 8
59 m 0.4% 17 0 17 0 0
60 f 4722 99.6% 4705 0 4584 113 8
61 m 0.4% 17 0 17 0 0
62 f 4722 99.8% 4712 0 4598 106 8
63 m 0.2% 10 0 10 0 0
64 f 4723 99.7% 4710 0 4584 116 10
65 m 0.3% 13 0 13 0 0
66 f 4723 99.9% 4716 0 4599 106 11
67 m 0.1% 7 0 7 0 0
68 f 4722 99.7% 4706 0 4572 121 13
69 m 0.3% 16 0 16 0 0
70 f 4720 99.6% 4703 0 4582 110 11
71 m 0.4% 17 0 17 0 0
72 f 4726 99.8% 4717 0 4586 117 14
73 m 0.2% 9 0 9 0 0
74 f 4720 99.9% 4713 0 4590 112 11
75 m 0.1% 7 0 7 0 0
76 f 4724 99.7% 4712 0 4589 107 16
77 m 0.3% 12 0 12 0 0
78 f 4724 99.8% 4714 0 4586 113 15
79 m 0.2% 10 0 10 0 0
80 f 4723 99.8% 4712 0 4588 117 7
81 m 0.2% 11 0 11 0 0
82 f 4721 99.8% 4710 0 4580 124 6
83 m 0.2% 11 0 11 0 0
84 f 4722 99.5% 4697 0 4587 106 4
85 m 0.5% 25 0 25 0 0
86 f 4722 99.7% 4707 0 4588 109 10
87 m 0.3% 15 0 15 0 0
88 f 4724 99.7% 4708 0 4586 116 6
89 m 0.3% 16 0 16 0 0
90 f 4721 99.8% 4710 0 4594 113 3
91 m 0.2% 11 0 11 0 0
92 f 4721 99.6% 4704 0 4583 115 6
93 m 0.4% 17 0 17 0 0
94 f 4725 99.7% 4712 0 4576 126 10
95 m 0.3% 13 0 13 0 0
96 f 4721 99.7% 4705 0 4578 117 10
97 m 0.3% 16 0 16 0 0
98 f 4722 99.7% 4710 0 4585 109 16
99 m 0.3% 12 0 12 0 0
100 f 4721 99.8% 4713 0 4597 106 10
101 m 0.2% 8 0 8 0 0
102 f 4718 99.7% 4706 0 4603 92 11
103 m 0.3% 12 0 12 0 0
104 f 4723 99.6% 4704 0 4594 98 12
105 m 0.4% 19 0 19 0 0
106 f 4721 99.7% 4707 0 4577 120 10
107 m 0.3% 14 0 14 0 0
108 f 4719 99.7% 4707 0 4579 120 8
109 m 0.3% 12 0 12 0 0
110 f 4723 99.7% 4710 0 4579 121 10
111 m 0.3% 13 0 13 0 0
112 f 4722 99.6% 4705 0 4575 118 12
113 m 0.4% 17 0 17 0 0
114 f 4722 99.8% 4712 0 4589 113 10
115 m 0.2% 10 0 10 0 0
116 f 4721 99.8% 4710 0 4583 120 7
117 m 0.2% 11 0 11 0 0
118 f 4721 99.7% 4709 0 4566 130 13
119 m 0.3% 12 0 12 0 0
120 f 4722 99.8% 4714 0 4590 111 13
121 m 0.2% 8 0 8 0 0
122 f 4722 99.7% 4708 0 4579 119 10
123 m 0.3% 14 0 14 0 0
124 f 4720 99.8% 4709 0 4581 113 15
125 m 0.2% 11 0 11 0 0
126 f 4721 99.7% 4709 0 4576 117 16
127 m 0.3% 12 0 12 0 0
128 f 4723 99.7% 4709 0 4579 120 10
129 m 0.3% 14 0 14 0 0
130 f 4720 99.6% 4700 0 4590 100 10
131 m 0.4% 20 0 19 1 0
132 f 4723 99.7% 4711 0 4594 104 13
133 m 0.3% 12 0 12 0 0
134 f 4722 99.8% 4712 0 4580 120 12
135 m 0.2% 10 0 10 0 0
136 f 4726 99.7% 4711 0 4579 118 14
137 m 0.3% 15 0 15 0 0
138 f 4723 99.8% 4712 0 4587 107 18
139 m 0.2% 11 0 11 0 0
140 f 4720 99.6% 4703 0 4572 117 14
141 m 0.4% 17 0 17 0 0
142 f 4722 99.7% 4709 0 4575 123 11
143 m 0.3% 13 0 13 0 0
144 f 4722 99.6% 4704 0 4568 124 12
145 m 0.4% 18 0 18 0 0
146 f 4725 99.7% 4711 0 4598 106 7
147 m 0.3% 14 0 14 0 0
148 f 4724 99.8% 4716 0 4577 127 12
149 m 0.2% 8 0 8 0 0
150 f 4718 99.7% 4705 0 4577 120 8
151 m 0.3% 13 0 13 0 0
152 f 4716 99.7% 4704 0 4575 118 11
153 m 0.3% 12 0 12 0 0
154 f 4720 99.8% 4710 0 4574 122 14
155 m 0.2% 10 0 10 0 0
156 f 4721 99.7% 4706 0 4579 117 10
157 m 0.3% 15 0 15 0 0
158 f 4723 99.7% 4711 0 4596 103 12
159 m 0.3% 12 0 12 0 0
160 f 4723 99.7% 4709 0 4585 115 9
161 m 0.3% 14 0 14 0 0
162 f 4722 99.7% 4709 0 4571 121 17
163 m 0.3% 13 0 13 0 0
164 f 4720 99.8% 4709 0 4589 111 9
165 m 0.2% 11 0 11 0 0
166 f 4723 99.6% 4706 0 4577 117 12
167 m 0.4% 17 0 16 1 0
168 f 4722 99.7% 4709 0 4564 132 13
169 m 0.3% 13 0 13 0 0
170 f 4723 99.7% 4710 0 4562 138 10
171 m 0.3% 13 0 13 0 0
172 f 4723 99.7% 4710 0 4585 114 11
173 m 0.3% 13 0 13 0 0
174 f 4722 99.8% 4711 0 4569 131 11
175 m 0.2% 11 0 11 0 0
176 f 4719 99.7% 4705 0 4580 111 14
177 m 0.3% 14 0 14 0 0
178 f 4721 99.8% 4713 0 4580 125 8
179 m 0.2% 8 0 8 0 0
180 f 4722 99.7% 4709 0 4578 120 11
181 m 0.3% 13 0 13 0 0
182 f 4723 99.8% 4715 0 4590 114 11
183 m 0.2% 8 0 8 0 0
184 f 4723 99.7% 4711 0 4595 102 14
185 m 0.3% 12 0 12 0 0
186 f 4723 99.7% 4708 0 4575 124 9
187 m 0.3% 15 0 15 0 0
188 f 4722 99.7% 4710 0 4576 124 10
189 m 0.3% 12 0 11 1 0
190 f 4721 99.8% 4710 0 4567 127 16
191 m 0.2% 11 0 11 0 0
192 f 4723 99.8% 4714 0 4588 115 11
193 m 0.2% 9 0 9 0 0
194 f 4722 99.7% 4708 0 4594 108 6
195 m 0.3% 14 0 14 0 0
196 f 4721 99.8% 4712 0 4585 115 12
197 m 0.2% 9 0 9 0 0
198 f 4723 99.7% 4711 0 4598 100 13
199 m 0.3% 12 0 12 0 0
200 f 4724 99.7% 4710 0 4569 123 18
201 m 0.3% 14 0 14 0 0
202 f 4721 99.7% 4706 0 4570 122 14
203 m 0.3% 15 0 15 0 0
204 f 4722 99.7% 4709 0 4592 105 12
205 m 0.3% 13 0 13 0 0
206 f 4723 99.8% 4715 0 4590 116 9
207 m 0.2% 8 0 8 0 0
208 f 4724 99.7% 4712 0 4569 133 10
209 m 0.3% 12 0 11 1 0
210 f 4721 99.7% 4707 0 4579 113 15
211 m 0.3% 14 0 14 0 0
212 f 4723 99.7% 4708 0 4597 105 6
213 m 0.3% 15 0 15 0 0
214 f 4723 99.8% 4714 0 4591 116 7
215 m 0.2% 9 0 9 0 0
216 f 4724 99.7% 4709 0 4570 127 12
217 m 0.3% 15 0 15 0 0
218 f 4723 99.5% 4701 0 4585 106 10
219 m 0.5% 22 1 21 0 0
220 f 4721 99.7% 4707 0 4588 107 12
221 m 0.3% 14 0 14 0 0
222 f 4722 99.7% 4708 0 4584 115 9
223 m 0.3% 14 0 14 0 0
224 F 4721 99.7% 4707 0 4596 102 9
225 m 0.3% 14 0 14 0 0
226 f 4724 99.6% 4707 0 4571 128 8
227 m 0.4% 17 0 17 0 0
228 f 4720 99.7% 4704 0 4584 111 9
229 m 0.3% 16 0 16 0 0
230 f 4720 99.6% 4700 0 4566 126 8
231 m 0.4% 20 0 20 0 0
232 f 4724 99.8% 4713 0 4611 88 14
233 m 0.2% 11 0 11 0 0
234 f 4722 99.7% 4709 0 4565 136 8
235 m 0.3% 13 0 13 0 0
236 f 4720 99.7% 4706 0 4588 106 12
237 m 0.3% 14 0 14 0 0
238 f 4722 99.7% 4707 0 4593 102 12
239 m 0.3% 15 0 15 0 0
240 f 4722 99.9% 4716 0 4589 119 8
241 m 0.1% 6 0 6 0 0
242 f 4724 99.7% 4710 0 4590 107 13
243 m 0.3% 14 0 14 0 0
244 f 4720 99.7% 4704 0 4598 100 6
245 m 0.3% 16 0 16 0 0
246 f 4716 99.8% 4707 0 4583 115 9
247 m 0.2% 9 0 9 0 0
248 f 4720 99.7% 4705 0 4563 128 14
249 m 0.3% 15 0 15 0 0
250 f 4724 99.7% 4710 0 4578 117 15
251 m 0.3% 14 0 14 0 0
252 f 4719 99.8% 4708 0 4567 132 9
253 m 0.2% 11 0 11 0 0
1625 dirty, 599656 queued, 600000 total, 1048576 hash buckets, 2048
buffer size
start clean at 2% (of pair total) dirty, or 94 buffs dirty, stop at 1%
0 priority downgrades, 0 priority upgrades
|
| Options | 5 answers | Add comment |
Monday, 9 July 2007
|
| rshd: 0826-813 Permission is denied Frank 10:46:47 |
| | just running an input screen and doing some updating... got following
error msg:
rshd: 0826-813 Permission is denied
Any help/suggestion is greatly appreciated... Thank you.
|
| Options | 3 answer | Add comment |
Saturday, 11 November 2006
|
| fragmentation Dooku Maul 17:45:04 |
| |
Hi: It wanted to know that type of fragmentation is but the optimal one:
1. Using line of commandos
2. Recreating totally the table
And it wanted to know as of of these two types of fragmentation he is but
the optimal one:
example1:
Begin work;
set lock mode to not wait;
lock table tabla_ejemplo1 in exclusive mode;
alter index idx_campo1 to cluster;
alter index idx_campo1 to not cluster;
commit work:
example2:
Begin work;
set lock mode to not wait;
lock table tabla_ejemplo2 in exclusive mode;
alter fragment on table tabla_ejemplo2 init fragment by round robin in
dbspacesA, dbspacesN;
-- index clustering will not be performed because above statement
-- will re-organize table's data as side effect
commit work;
thank you very much
_________________________________________________________________
Las mejores tiendas, los precios mas bajos, entregas en todo el mundo,
YupiMSN Compras: http://latam.msn.com/compras/
sending to informix-list
|
| Options | 8 answers | Add comment |
Wednesday, 8 November 2006
|
last ddl date  mozartny 18:29:34 |
| | Is it possible to find out the last date/time that a database object has been modified (ie column added to a table). |
| Options | Add comment |
Wednesday, 1 November 2006
|
| Probably a dumb question but... Everett Mills 09:37:16 |
| | Guys (particularly IBMers)-
Since Apple is porting its OS to Intel processors and since Mac
OS is really just a GUI sitting on top of UNIX... It shouldn't be much
of a stretch to take the Linux port of IDS and create a Mac port. Is
there a snowball's chance in Hades that IBM would do it? It would open
up a whole new server environment for a minimal investment.
--EEM -----Original Message----- From: Paul Watson [mailto  aul@oninit.com] Sent: Monday, January 16, 2006 10:48 AM To: informix-list@iiug.org Subject: Re: IDS cluster My company is talking about moving IDS on SUN, to IDS on Linux - and also clustering the servers. The only failover I know (on the Informix side), is ER and HDR. Is clustering possible on Linux ? (they mentioned having 2 DB servers and 2 application servers acting
as failover etc. for each other) Got a client running Suse Linux without a problem on HP hardware and the HP clustering software Paul Watson Oninit Ltd Tel : +44 1414161772 Cell : +44 7818 003457 _______________________________________________ Informix-list mailing list Informix-list@iiug.org
|
| Options | 6 answers | Add comment |
Monday, 21 August 2006
|
| DB stuck in Fast recovery mode Guest 11:57:39 |
| | My db is stuck in fast recovery mode and giving no errors. How can i skip or bypass this to go into quiscent mode |
| Options | Add comment |
Monday, 14 August 2006
|
| Contiguous shared memory segment allocation failed at 0xd0000000 Yunyao Qu 01:17:44 |
| | I was doing a mass update, delete ( big transaction) and found the info
in online log,
16:10:21 Maximum server connections 21
16:10:23 Requested shared memory segment size rounded from 8604KB to
8608KB
16:10:23 Contiguous shared memory segment allocation failed at 0xd0000000.
Allocation successful at 0x30000000.
Check SHMBASE is consistent with the value in
$INFORMIXDIR/etc/onconfig.std.
If you are using the correct SHMBASE value in your ONCONFIG file, then
consider this message informational only.
16:10:23 Dynamically allocated new virtual shared memory segment (size
8608KB)
16:10:23 dynamically allocated 100000 locks
16:10:25 Requested shared memory segment size rounded from 8604KB to
8608KB
16:10:25 Contiguous shared memory segment allocation failed at 0xd0000000.
Allocation successful at 0xe0000000.
Check SHMBASE is consistent with the value in
$INFORMIXDIR/etc/onconfig.std.
If you are using the correct SHMBASE value in your ONCONFIG file, then
consider this message informational only.
(1) why "Contiguous shared memory segment allocation failed at 0xd0000000.
Allocation successful at 0x30000000." ?
(2) whay "Check SHMBASE is consistent with the value in
$INFORMIXDIR/etc/onconfig.std"? Since I used a different onconfig file:
onconfig.dev1
INFO in my onconfig:
informix@dolly $ more onconfig.dev1 | grep SHM
SHMBASE 0x40000000 # Shared memory base address
SHMVIRTSIZE 32768 # initial virtual shared memory segment
size
SHMADD 8192 # Size of new shared memory segments
(Kbytes)
EXTSHMADD 8192 # Size of new extension shared memory
segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
DUMPSHMEM 1 # Dump a copy of shared memory
informix@dolly $ more onconfig.std | grep SHM
SHMBASE 0x30000000L # Shared memory base address
SHMVIRTSIZE 8192 # initial virtual shared memory segment
size
SHMADD 8192 # Size of new shared memory segments
(Kbytes)
EXTSHMADD 8192 # Size of new extension shared memory
segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
DUMPSHMEM 1 # Dump a copy of shared memory
informix@dolly $ onstat -version
Program Name: onstat
Build Version: 10.00.UC4
Build Number: N204
Build Host: ibm6c1b
Build OS: AIX 5.2
Build Date: Fri Nov 18 23:37:26 CST 2005
GLS Version: glslib-4.00.UC7
Thank you for your time!
Frank
--
Yunyao "Frank" Qu
Computer Sciences Corporation(CSC)
NOAA/CLASS, (301)817-4696
|
| Options | 1 answer | Add comment |
Sunday, 13 August 2006
|
| Informix business grew by double digits in 2006 (Q1+Q2) Tambi Dude 07:11:16 |
| | Looks like our dear ol Informix is alive and kicking well. Per IBM site,
license
revenue grew by double digits in 2006 (Q1+Q2).
http://www-03.ibm.com/developerworks/blogs/page/gbowerman/20060802
"
The rise of Informix
The Q2 numbers are in and Informix Dynamic Server experienced another large
increase in revenue to build on the growth of Q1. As usual the details are
not released publicly but I am officially allowed to say, and I quote..
IDS license revenue grew by double digits in the first half of 2006 "
|
| Options | 83 answer | Add comment |
Saturday, 12 August 2006
|
| Informix in the press... Jean Georges Perrin 16:26:46 |
| | Hi,
You all want to see Informix in the press, so help IIUG achieving this goal by
sending the following information to me:
Name of journalist,
e-mail,
name of newspaper.
Thanks
--
jgp
|
| Options | 2 answer | Add comment |
|
| Onpload error -- onpload exited abnormally during initialization Vratesh Chopra 12:16:55 |
| | encounter stated initialization error while using onpload for Informix
9.4.
Sample scpirt i used was:
####################################################################
export testDB="testDB"
dbaccess <<EOF
drop database onpload
EOF
#dbaccess<<EOF
#create database $testDB with buffered log;
#EOF
dbaccess $testDB<<EOF
create table test_tab
(
a char(10),
b char (10)
);
EOF test.unl
echo "1|2|" >> test.unl
echo "3|4|" >> test.unl
onpladm create job test_job -d test.unl -D $testDB -t test_tab -flN test.log
onpladm run job test_job -fl
onpladm delete job test_job -fl -R
dbaccess $testDB<<EOF
select * from test_tab
EOF
dbaccess $testDB<<EOF
drop table test_tab
EOF
#dbaccess <<EOF
#drop database $testDB
#EOF
dbaccess <<EOF
drop database onpload
EOF
#################################################################
Pls advice...
Cheers 
Vratesh
|
| Options | 1 answer | Add comment |
|
| Checkpoint Question - need feedback Madison Pruet 12:14:07 |
| | I've got a question for the user community.
Checkpoints are a real pain because there is a period of time in which
user threads are going to be blocked.
I've been playing around with an idea in which I think I could do
non-blocking checkpoints. The cost of doing this, however, would be
that there would probably need to be a significant increase in the size
of the physical log file.
I know that implementing the fractional LRU min/max has helped reduce
the impact of the checkpoint, but the fractional LRU min/max is not a
guarantee - since it is possible that the LRU page writers won't be able
to keep up with the current activity.
Also - this idea would eliminate fuzzy checkpoints, which is probably a
good thing since fuzzy checkpoints do impact the recovery time of the
server.
So - basic question --- Is the cost of the increased physical log file
(maybe 3-4 times larger in some cases) totally outweigh the benefit of
non-blocking checkpoints?
|
| Options | 15 answers | Add comment |
|
| Re: oncheck -cc hangs Guest 12:07:31 |
| |
Floyd Wellershaus wrote: Any idea why we can't get an oncheck -cc on our big database ? We have 5 instances on this box, one of them is a terabyte, with about 80,000 tables. Oncheck -cc runs perfectly on all of them except the big one. It seems to just hang there. After 2 days, it has given zero output, is still showin up in the ps -ef, and the onstat -u, but is gaining no time, as if it is just hung. floyd
Which IDS version?
onstat -g ath ..what state are the oncheck threads in?
onstat -g stk on each thread. what does that give? does it change?
onstat -g tpf - do any values for the threads change?
select sid from sysrstcb where tid = <thread id>
onstat -g ses <sid> what does that give?
select * from syssesprof where sid = <sid> does that change?
onstat -s and onstat -g lmx.. any mutexes /latches begin held for > 5
minutes?
Is an archive running?
If nothing appears to be moving then contact IBM Tech Support.
NOTE: With that many tables consider increasing DS_ and DD_ onconfig
parameters to cache the table info.
They are documented in the IDS 10 docs and available (sometimes
undocumented) in all versions.
David.
|
| Options | Add comment |
|
| onperf.....who uses it? Mark.Scranton@Gmail.Com 01:04:39 |
| | Gang -
I'm just curious.....any "onperf" fans? I am onsite using it for query
analysis (the xtree part that is), and would hope we don't ever get rid
of it (although I know it has been discussed many times), and would
wonder if it should be enhanced.
Any thoughts?
Mark Scranton
|
| Options | 6 answers | Add comment |
|
| Re: query using cpu Guest 00:59:57 |
| |
Floyd Wellershaus wrote: Any idea why a query that does a join of a table on itself, doing a count and a group by, would cause the cpu usage to go drastically up, almost instantly ? Not pdq, just a query, on a non fragged table.
It is doing multiple sequential scans of the table. All the table is
cached in the buffers hence no disk i/o hence cpu usage
flies up.
1. Query sysmaster:syssesprof to find the session causing the problem
and watch its bufreads count flying up!
OR
2. onstat -g ses , get the thread id for the thread and check the
output from onstat -g tpf for the thread
!!
|
| Options | Add comment |
Friday, 11 August 2006
|
| Ontape security when shipping tapes Kxk0912 20:51:59 |
| |
We have a need to ship a level-0 ontape backup that will contain
sensitive data. How secure is the data when backed up using ontape?
Could a potential threat use a UNIX utility such as dd to read the
tape?
Thanks,
Kevin
--
kxk0912
------------------------------------------------------------------------
kxk0912's Profile: http://www.dbtalk.net/m636
View this thread: http://www.dbtalk.net/t324905
|
| Options | 7 answers | Add comment |
|
| Informix customer list Gm 19:55:14 |
| | Hi Informix Group:
Can any one help me out in getting the list of
Informix customers with thier version info, OS,
Application info etc...
Thanks in advance
GMkris
Informix DBA
__________________________________________________________
Yahoo! India Answers: Share what you know. Learn something new
http://in.answers.yahoo.com/
|
| Options | 10 answers | Add comment |
|
| fixed: dostats fails on indexkeyarray_out Dcp 19:21:11 |
| | "Douglas Palmer" <dcp_news@nyed.uscourts.gov> wrote in message
news:ebgg17$hlk$1@coronis.nyed.circ2.dcn... Procedures: indexkeyarray_out...Cannot Update statistics for procedure: indexkeyarray_out. SQL=-674, ISAM=0.
A walk home and some distance gave the obvious answer -- get the updated
dostats from IIUG, make and install.
All better now.
Thank you, Art Kagel, for a great utility and for keeping it up.
--
|
| Options | 1 answer | Add comment |
|
| Index Levels Roy Mercer 18:39:23 |
| | Query to find index levels.
set isolation to dirty read;
select tabname[1,18], idxname[1,18], levels
from sysindexes, systables
where sysindexes.tabid = systables.tabid
and levels > 2
order by 1 asc,3 desc
|
| Options | Add comment |
|
| Question for the group.... Madison Pruet 16:17:27 |
| | I'm kinda dumb about shared file usage and would like to have some idea
what folks are using. Appreciate any feedback/comments/etc...
|
| Options | 3 answer | Add comment |
|
| dostats fails on indexkeyarray_out Douglas Palmer 03:38:17 |
| | We just moved to 9.40.UC8 from 9.40.UC5 and can no longer run dostats on any
database. It always bombs just as it starts to update the stored procedures
with this error:
Procedures:
indexkeyarray_out...Cannot Update statistics for procedure:
indexkeyarray_out. SQL=-674, ISAM=0.
I looked online and only found one other person seeing this error from a few
years ago -- no solution. The stored procedure *does* exist and running this
command works fine:
update statistics for procedure indexkeyarray_out;
Thanks for any help.
--
|
| Options | Add comment |
|
| OS Upgrade broke makefile Guest 02:51:06 |
| | Hi all,
We had an OS upgrade from AIX 5.1 -> AIX 5.3 yesterday and now our 4GL
makefiles are dying. We cannot figure out what to fix though. Any
help would be appreciated!
(One note: make fails during the call to c4gl, but if I run c4gl at
command line, it works fine!)
Platform: IBM servers running AIX 5.3
Informix: Informix v 9.40C2, I4GL v. 7.32.UC2 (c4gl, isql same version)
Here is the error:
-------------------------------------------
$ make
make all1
c4gl my_program.4gl -c -O
exec(): 0509-036 Cannot load program
/local_apps/informix/lib/tools/i4glc1 because of the following errors:
0509-150 Dependent module libifsql.so could not be loaded.
0509-022 Cannot load module libifsql.so.
0509-026 System error: A file or directory in the path name
does not exist.
make: 1254-004 The error code from the last command is 255.
Stop.
make: 1254-004 The error code from the last command is 2.
Stop.
-------------------------------------------
Here are the relevevant portions of my environment:
$ set | grep INF
INFORMIXDIR=/local_apps/informix
INFORMIXSERVER=adm_dev
INFORMIXSQLHOSTS=/usr/remote/etc/sqlhosts
LD_LIBRARY_PATH=/local_apps/informix/lib:/local_apps/informix/lib/esql:/local_apps/informix/lib/tools:/usr/lib:/local_apps/cobol/coblib
LIBPATH=/local_apps/informix/lib:/local_apps/informix/lib/esql:/local_apps/informix/lib/tools:/usr/lib:/local_apps/cobol/coblib
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java14/jre/bin:/usr/java14/bin:/usr/local/bin:/usr/remote/bin:/local_apps/informix/bin:/local_apps/informix/bin:/local_apps/cobol/bin:/local_apps/cobol/drivers:/home/mhoffman/bin:.
I added the LD_LIBRARY_PATH as a first effort, but it had no effect!
All the library files exist (the *if* and *ix* and isql*) in the esql
subdirectory under informix/lib and seem to be hard-linked correctly.
Any idea what else needs to be done???
Thanks,
Michael Hoffman
|
| Options | 1 answer | Add comment |
|
| Columns in each table Mehl@Cyvest.Com 02:48:04 |
| | Hello --
I am a newbie to Informix, and will start a contract tomorrow to write
Crystal Reports on an Informix db.
Can anyone suggest SQL code to obtain the following information from an
Informix database?:
Table, Column, DataType, Length, Description (?)
I use the following code in MS SQL Server
SELECT
SUBSTRING(table_name,1,20) AS "Table",
SUBSTRING(column_name,1,30) AS "Column",
SUBSTRING(data_type,1,13) AS "DataType",
ISNULL(character_maximum_length, ' ') AS "Length",
STR(numeric_precision) AS "Precision",
numeric_scale AS "Scale",
CONVERT(nvarchar(150), ISNULL(s.value, ' ')) AS Description
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME),
'IsMsShipped')=0 AND
table_schema = 'DBO' AND
table_name NOT LIKE 'sys%' AND
table_name NOT LIKE 'dtprop%'
ORDER BY table_name, ordinal_position
Can you also tell me in what Informix component to enter such SQL code?
In SQL Server, for example, I could enter it in Query Analyzer or
create a query in the Management Console.
Thanks for any help.
Larry Mehl
|
| Options | 3 answer | Add comment |
|
| Re: index width Jonathan Leffler 02:38:29 |
| | On 8/9/06, Floyd Wellershaus <fwellers@yahoo.com> wrote: I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes.
That's funny. Where are you reading that? In IDS 10, we increased
the maximum index key size to about 3 KB on 16 KB pages. I'm trying to find out the width of all our indexes. I can't come up with a query to do this, any help ? I know I have to join systables, syscolumns and sysindexes, and join on sysindexes.part1 etc to syscolumns.colno and then get the collength. I can pretty much get there with this, but it doesn't group them properly to be able to add up the columns for a multi-column index. select si.idxname,st.tabname,sc.colname,sc.collength from sysindexes si,systables st,syscolumns sc where st.tabid>99 and st.tabname not matches '*[0-9]*' and st.tabid=si.tabid and si.tabid=sc.tabid and (si.part1=sc.colno or si.part2=sc.colno or si.part3=sc.colno) and st.tabname='proc_stats' order by sc.collength desc
Apart from repeating for each of the 16 possible index parts, you need
to dcode the collength field for many types. For example, DECIMAL
columns are encoded with the scale and precision combined with shift
and OR operators. You need to extract the scale and precision from
the recorded values to determine that actual data space used by the
column. DATETIME and INTERVAL are more complex (and, while similar,
also subtly different from each other). And so on.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
|
| Options | 8 answers | Add comment |
|
| Short checkpoint durations Neil Truby 02:35:42 |
| | One of customers wants to use SAN replication between remote sites for DR
purposes. As recently discussed, and explained at length and in detail by
Martin Fuerderer, this will have the effect of allowing the DR database to
its state at the last checkpoint.
Actually the customer wants minimal data use, but doesn't want to use HDR
for this. To do this we could set very short checkpoints - say every 60
seconds, or even less.
I wondered what experiences others have with checpoint intervals as low as
this?
|
| Options | 1 answer | Add comment |
|
