Informix 14.10.FC9 reverse scans
Posted: February 6, 2023 Filed under: Informix, Informix 14 | Tags: Informix Leave a commentSo with some help from new features in Informix 14.10.FC7 I found that reverse scans happen by default.
Reverse scans can be bad as under contention they may have to be retried and require more cpu time.
[informix@localhost IDS.14.10.FC9W1]$ onstat -g ppf | awk ‘($13!=0) { print $0}’
IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 06:57:25 — 185996 Kbytes
2023-02-05 23:17:27
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rvscn rs_rt rhitratio name
0x100195 462 0 0 0 116 160 0 0 355 174 0 28 0 100 sysadmin:informix.idx_mon_ckpt_1
So the index sysadmin:informix.idx_mon_ckpt_1 is having reverse scans, why is this?
[informix@localhost IDS.14.10.FC9W1]$ grep idx_mon_ckpt_1 $INFORMIXDIR/etc/sysadmin/*.sql
/opt/IBM/IDS.14.10.FC9W1/etc/sysadmin/sch_tasks.sql:”create table informix.mon_checkpoint (ID integer, intvl integer, type char(12), caller char(10), clock_time int, crit_time float, flush_time float, cp_time float, n_dirty_buffs int, plogs_per_sec int, llogs_per_sec int, dskflush_per_sec int, ckpt_logid int, ckpt_logpos int, physused int, logused int, n_crit_waits int, tot_crit_wait float, longest_crit_wait float, block_time float);create unique index idx_mon_ckpt_1 on mon_checkpoint(intvl);grant select on mon_checkpoint TO ‘db_monitor’ as informix;”,
So the index is on mon_checkpoint, ok what accesses that table?
[informix@localhost IDS.14.10.FC9W1]$ grep mon_checkpoint $INFORMIXDIR/etc/sysadmin/*.sql | grep intvl | tail -1
/opt/IBM/IDS.14.10.FC9W1/etc/sysadmin/sch_tasks.sql:”insert into mon_checkpoint select $DATA_SEQ_ID, intvl, type, caller, clock_time, crit_time, flush_time, cp_time, n_dirty_buffs, plogs_per_sec, llogs_per_sec, dskflush_per_sec, ckpt_logid, ckpt_logpos, physused, logused, n_crit_waits, tot_crit_wait, longest_crit_wait, block_time FROM sysmaster:syscheckpoint WHERE intvl > (select NVL(max(intvl),0) from mon_checkpoint)”,
set explain on;
select NVL(max(intvl),0) from mon_checkpoint
gives
QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2023 23:31:32)
select NVL(max(intvl),0) from mon_checkpoint
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.mon_checkpoint: INDEX PATH
(1) Index Name: informix.idx_mon_ckpt_1
Index Keys: intvl (Key-Only) (Aggregate) (Serial, fragments: ALL)
Query statistics:
Table map :
Internal name Table name
type rows_prod est_rows rows_cons time
group 1 1 0 00:00.00
[informix@localhost IDS.14.10.FC9W1]$ onstat -g ses 47
Your evaluation license will expire on 2023-05-05 17:08:46
IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 07:15:45 — 185996 Kbytes
2023-02-05 23:35:46
session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
47 informix – 1 1264330 localhost 1 90112 73664 off
Program :
/opt/IBM/IDS.14.10.FC9W1/bin/dbaccess
tid name rstcb flags curstk status
123 sqlexec 45a8eac0 Y–P— 3808 cond wait netnorm –
Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
47 V 47d81040 86016 15704 77 11
47*O0 V 48b52040 4096 744 1 1
name free used name free used
overhead 0 6704 scb 0 144
opentable 0 4904 filetable 0 744
log 0 16536 temprec 0 2208
gentcb 0 1592 ostcb 0 2992
sqscb 0 26512 hashfiletab 0 552
osenv 0 2504 sqtcb 0 7792
fragman 0 336 sapi 0 144
sqscb info
scb sqscb optofc pdqpriority optcompind directives
45c421c0 48bf6028 0 0 2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
47 – sysadmin CR Not Wait 0 0 9.24 On
Last parsed SQL statement :
select NVL(max(intvl),0) from mon_checkpoint
[informix@localhost IDS.14.10.FC9W1]$ onstat -g tpf 123
Your evaluation license will expire on 2023-05-05 17:08:46
IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 07:15:58 — 185996 Kbytes
2023-02-05 23:35:59
Thread profiles
tid lkreqs lkw dl to lgrs totll totpl isrd iswr isrw isdl isct isrb lx drd bfr bfw lsus lsmx seq rvscn rs_rt
123 151 0 0 0 0 0 0 72 0 0 0 0 0 0 0 152 0 0 0 0 1 0
So we have a reverse scan!
I guess we would need a descending index as we to avoid the reverse scan!
Informix 14.10.FC9W1 is out!
Posted: February 5, 2023 Filed under: Informix, Informix 14 | Tags: Informix, Informix14 Leave a commentInformix write listener – Rest API setup issues
Posted: April 25, 2017 Filed under: Informix, Informix 12, Informix wire listener | Tags: Informix, Informix 12, Informix wire listener Leave a commentFor my talk at IIUG 2017 I was working with the wire listener on Informix.
When setting up a wire listener for the REST API I found some Informix packaging issues and issues with the DeveloperWorks article.
I was following the developerWorks article Informix and NoSQL: First Steps with the REST API
NOTE: This series of articles REALLY helped when getting my talk ready!
I was working with the latest Informix server version 12.10.FC8 on CentOS 7 and found:
a) When unpacking nosql_sdk.zip this unpacks to folder nosql-sdk-1.2.2 – there was no explict step with the command to add a link called nosql_sdk to make the examples work, I add this below.
cd $INFORMIXDIR
unzip bin/nosql_sdk.zip
ln -s nosql-sdk-1.2.2 nosql_sdk
b) The library ${INFORMIXDIR}/nosql_sdk/tomcat-embed-core.jar is missing!
Noticing that the other libaries were for version 8.0.38 I went to
and on the “Files” line clicked the download link.
I then copied tomcat-embed-core-8.0.38.jar into $INFORMIXDIR/nosql_sdk/lib
and added this to the classpath.
3. In order to get class com.ibm.nosql.server.ListenerCLI I also had to add ${INFORMIXDIR}/nosql_sdk/com.ibm.nosql.informix-1.2.2.jar to the classpath.
The final result was:
cat rest_listener_start.sh
#!/bin/sh
${INFORMIXDIR}/extend/krakatoa/jre/bin/java \
-cp ${INFORMIXDIR}/bin/jsonListener.jar:${INFORMIXDIR}/nosql_sdk/lib/tomcat-embed-core-8.0.38.jar:${INFORMIXDIR}/nosql_sdk/com.ibm.nosql.informix-1.2.2.jar \
com.ibm.nosql.server.ListenerCLI \
-config ${INFORMIXDIR}/etc/restListener.properties \
-logfile /work/martinfu/731/restListener.log \
-start &
exit
cat rest_listener_stop.sh
#!/bin/sh
${INFORMIXDIR}/extend/krakatoa/jre/bin/java \
-cp ${INFORMIXDIR}/bin/jsonListener.jar:${INFORMIXDIR}/nosql_sdk/lib/tomcat-embed-core-8.0.38.jar:${INFORMIXDIR}/nosql_sdk/com.ibm.nosql.informix-1.2.2.jar \
com.ibm.nosql.server.ListenerCLI \
-config ${INFORMIXDIR}/etc/restListener.properties \
-stop
exit
I hope this helps and am talking to IBM about the issues I found.
Finding System Requirements for IBM Software Products.
Posted: November 14, 2016 Filed under: DB2 LUW, Informix | Tags: DB2 LUW, Informix Leave a commentIBM has a Software Product Compatibility Reports facility.
http://www-969.ibm.com/software/reports/compatibility/clarity/softwareReqsForProduct.html
Managing GSKit Master Keys
Posted: May 2, 2015 Filed under: DB2 LUW, Informix | Tags: DB2 LUW, Informix Leave a commenthttp://www.ibm.com/developerworks/data/library/techarticle/dm-1504-master-encrypted-keys/index.html
Managing GSKit Master Keys
Huge Pages for Informix on SPARC Solaris
Posted: April 30, 2015 Filed under: Informix, Solaris | Tags: Informix, Solaris Leave a commentTo get hugh pages for Informix on Solaris
1. Set RESIDENT to a value other than 0, >0 means the first N shared memory segments will be pinned in memory.
https://informixdba.wordpress.com/2013/03/08/huge-pages/
2. Make sure IFX_LARGE_PAGES is not set to 0.
http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_404.htm
3. To get pages >4MB in size the hardware must have support for 2GB pages, run pagesize -a to check what the hardware supports
https://blogs.oracle.com/mandalika/entry/enabling_2_gb_large_pages
4, Either use ppgsz or mpss.so.1
The three ways to change the default page size for an application are:
- Use the Oracle Solaris OS command ppgsz(1).
- Compile the application with the -xpagesize, -xpagesize_heap, and -xpagesize_stack options. See the compiler man pages for details.
- Use MPSS specific environment variables. See the mpss.so.1(1) man page for details.
http://docs.oracle.com/cd/E24457_01/html/E21996/aewda.html
ppgsz is documented at
https://docs.oracle.com/cd/E19683-01/816-0210/6m6nb7mht/index.html
mpss.so.1 is documented at
http://docs.oracle.com/cd/E19253-01/816-5165/6mbb0m9lf/index.html
5. Note
“Solaris has had large parges for many years. Initially, a large page on SPARC was 4MB. Then, much later, Solaris 9 added new APIs for Multiple Page Size Support. At that time, the maximum supported by hardware was 256MB. Today, the T4 processor supports page sizes up to 2GB.”
https://www.linkedin.com/groups/Solaris-Huge-pages-60651.S.78812448
6. UltraSPARC II/III/III+/IV/IV+ and UltraSPARC T1 (aka Niagara) only support large pages of 4MB
7. Check none of these to disable large pages are being used
https://blogs.oracle.com/mandalika/entry/solaris_disabling_out_of_the
set exec_lpg_disable = 1
This parameter prevents large pages from being used when the kernel is allocating memory for processes being executed. These constitute the memory needed for a processes’ text/data/bss.set use_brk_lpg = 0
This parameter prevents large pages from being used for heap. To enable large pages for heap, set the value of this parameter to 1 or remove this parameter from/etc/system
completely.Note:
brk()
is the kernel routine that is called whenever a user level application invokesmalloc()
.set use_stk_lpg = 0
This parameter disables the large pages for stack. Set it to 1 to retain the default functionality.set use_zmap_lpg = 0
This variable controls the size of anonymous (anon
) pages.set use_text_pgsz4m = 0
This tunable disables the default use of 4M text pages on UltraSPARC-III/III+/IV/IV+/T1 platforms.set use_text_pgsz64k = 0
This tunable disables the default use of 64K text pages on UltraSPARC-T1 (Niagara) platform.set use_initdata_pgsz64k = 0
This tunable disables the default use of 64K data pages on UltraSPARC-T1 (Niagara) platform.
8.The T4 processor was the first to add “Support for a much larger Memory Management Unit page size (2GB)”
Click to access o11-090-sparc-t4-arch-496245.pdf
https://blogs.oracle.com/mandalika/entry/enabling_2_gb_large_pages
“Prerequisites:
OS: Solaris 10 8/11 (Update 10) or later
Hardware: SPARC T4. eg., SPARC T4-1, T4-2 or T4-4”
Informix Chat with Lab
Posted: April 26, 2015 Filed under: Informix | Tags: Informix Leave a commentInformix Chat with the Lab
Simple Informix C UDR on Centos 6.6
Posted: April 26, 2015 Filed under: Informix, Informix 12 | Tags: Informix, Informix 12 Leave a commentSimple Informix C UDR on Centos 6.
#include "dmi/mi.h" mi_integer bigger_int(mi_integer left,mi_integer right) { if ( left > right ) return(left); else return(right); }
To compile
gcc -I$INFORMIXDIR/incl -I $INFORMIXDIR/incl/esql -c -fPIC -DMI_SERVBUILD -g bigger_int.c gcc -shared -fPIC -o /home/informix/bigger_int.so bigger_int.o
To load and run
dbaccess sysmaster create database justdave; CREATE FUNCTION bigger_int (arg1 integer, arg2 integer) RETURNING integer EXTERNAL NAME '/home/informix/bigger_int.so(bigger_int)' LANGUAGE C; select bigger_int(1,2) from systables where tabid=1; DROP FUNCTION bigger_int;
Only when you execute the function is the shared library loaded
05:20:41 Loading Module
05:20:41 pid 3217: ELF .eh_frame section missing in /opt/IDS.12.10.FC5/gls/dll/64-libicudata.so.48
05:20:41 The C Language Module loaded
Informix Client SDK support position
Posted: March 31, 2015 Filed under: Informix | Tags: Informix Leave a commentInformix Client SDK support position
Informix 12.10.xC5 is out!
Posted: March 27, 2015 Filed under: Informix, Informix 12 | Tags: Informix, Informix 12 Leave a commentInformix 12.10.xC5 is out
– AUTO_REPREPARE changes,
– Rolling upgrades for high-availability clusters
– Support for Java 7
– Improved installation logging and debugging
– Easier silent installations
– Tenant databases session_limit_memory,session_limit_logspace,session_limit_txn_time,tenant_limit_space
– Limit access to tenant databases in OAT
– Limit session resources SESSION_LIMIT_MEMORY,SESSION_LIMIT_TEMPSPACE,SESSION_LIMIT_LOGSPACE,
SESSION_LIMIT_TXN_TIME
– Larger maximum tape size for backups TAPEDEV/LTAPEDEV 9 ZB
– Informix JDBC Driver now supports the Estonian and Lithuanian locale, et_ee
– Correlated aggregate expressions
– Control repreparation IFX_AUTO_REPREPARE new values
– Manipulate JSON and BSON data with SQL statements
– High availability for MongoDB and REST clients
– Wire listener configuration enhancements
– Wire listener query support, Join/Array queries on JSON data
– Enhanced account management through the wire listener,JSON lockAccount and unlockAccounts commands
– Load pure JSON documents into time series, TSL_PutJson
– Faster loading of time series data files
TSL_Put function can now contain JSON or BSON documents as values
for columns other than the primary key and time stamp columns
– Improved logging for the time series loader
You can choose to retrieve loader messages from a queue instead of logging the
messages in a message log file. Retrieving messages from a queue results in less
locking contention than logging messages in a file.
TSL_GetFmtMessage/TSL_GetLogMessage/TSL_MessageSet
– Create new time series while loading data,TSL_SetNewTS/TSCreateVirtualTab
– Display time series storage space usage,TSInfo
– View active time series loader sessions,TSL_ActiveHandles
– Analyze time series data for matches to patterns,TSPatternMatch/TSCreatePatternIndex
– Clip selected columns of time series data,ProjectedClip
– Track moving objects
The spatiotemporal search extension depends on the TimeSeries and spatial extensions.
You store the spatiotemporal data in a TimeSeries data type with columns for longitude and latitude.
You index and query the spatiotemporal data with the new spatiotemporal search functions.
You can also query spatiotemporal data with time series and spatial routines.
IBM Informix Spatiotemporal Search for Moving Objects User’s Guide.
https://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sts.doc/sts.htm?lang=en-us
– Enhancements to Informix Warehouse Accelerator
– accelerate queries that include these scalar functions: CURRENT, SQRT, and SYSDATE.
– ondwa listmarts command
– load data marts faster by adding a second DWAVP virtual processor
The DWAVP virtual processor runs Informix Warehouse Accelerator administrative
functions and procedures.