Informix Server 14.10.xC9W1 and Client SDK 4.50.xC9W1 Fix Lists

Informix Server 14.10.xC9W1 Fix List

https://www.ibm.com/support/pages/node/6954419

Informix 4.50.xC9W1 Fix List

https://www.ibm.com/support/pages/node/6954415


Informix 14.10.FC9 reverse scans

So 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!

Advertisement

Informix 14.10.FC9W1 is out!


Informix write listener – Rest API setup issues

For 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

Tomcat Embed Core » 8.0.38

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.

IBM has a  Software Product Compatibility Reports facility.

http://www-969.ibm.com/software/reports/compatibility/clarity/softwareReqsForProduct.html


Managing GSKit Master Keys

http://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

To 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

http://www.solarisinternals.com/wiki/index.php/Multiple_Page_Size_Support#Default_page_sizes.2C_and_tunables_on_different_platforms

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 = 1This 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 = 0This 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 invokes malloc().

  • set use_stk_lpg = 0This parameter disables the large pages for stack. Set it to 1 to retain the default functionality.
  • set use_zmap_lpg = 0This variable controls the size of anonymous (anon) pages.
  • set use_text_pgsz4m = 0This tunable disables the default use of 4M text pages on UltraSPARC-III/III+/IV/IV+/T1 platforms.
  • set use_text_pgsz64k = 0This tunable disables the default use of 64K text pages on UltraSPARC-T1 (Niagara) platform.
  • set use_initdata_pgsz64k = 0This 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

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Informix%20Chat%20With%20the%20Lab

Informix Chat with the Lab


Simple Informix C UDR on Centos 6.6

Simple 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

Informix Client SDK support position

http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&appname=iSource&supplier=877&letternum=ENUSZP15-0265