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.

 

Advertisements

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 12.10.xC5 is out!

Informix 12.10.xC5 is out

https://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.po.doc/new_features_ce.htm?lang=en-us#newxc5_toc

– 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.


It may take long time for updateable secondary (HDR or RSS) to become available for SQL execution

It may take long time for updateable secondary (HDR or RSS) to become available for SQL execution

http://www-01.ibm.com/support/docview.wss?uid=swg21626164&myns=swgimgmt&mynp=OCSSGU8G&mync=R


Informix 12.10.FC4W1 new feature

Informix 12.10.FC4W1 new feature – onstat -g spf similar to statistics part of select * from syssdblock.


Informix 12 new features.

https://www.ibm.com/developerworks/community/blogs/idsteam/entry/restoring_critical_administrative_files_with_onbar_utility?lang=en

Starting with Informix vesion 12.10.xC2, you can restore the critical files that you backed up with onbar utility. Onbar cold restore has an option now to restore these files or you can only restore these file without perform the storate space restore.

Use ‘onbar -r -cf yes’ to restore critical file during cold restore. Alternatively, you can use ‘onbar -r -cf only’ to extract critical files while Informix server is offline.

https://www.ibm.com/developerworks/community/blogs/idsteam/entry/monitor_resource_contention?lang=en

Two new onstat commands introduced in 12.10.xC2 to view the dependencies between blocking and waiting threads.

Now, you can use the ‘onstat -g bth’ command to display the dependencies between blocking and waiting threads. Next, use the ‘onstat -g BTH’ command to display session and stack information for the blocking threads.

https://www.ibm.com/developerworks/community/blogs/idsteam/entry/after_an_upgrade_query_running_slow?lang=en

A newer costing functionality was added in 11.70.FC3 and higher in the 11.70 family. This functionality was added to account for the seek time required to traverse an index (especially large indexes). There were situations where queries using an index would take longer to return than a sequential scan because of all the I/O involved.

You can control the new costing functionality using the OPT_SEEK_FACTOR configuration parameter. This parameter allows to set the “weight” of an I/O seek cost. The range is 0 to 25 and default is 6. Making it lower causes the seek cost to be lower which lowers the estimated cost of using an index path.

You can revert to the old costing method by setting OPT_SEEK_FACTOR to 0 in the ONCONFIG file.

https://www.ibm.com/developerworks/community/blogs/idsteam/entry/why_dbaccess_always_query_dns_server_while_connecting_to_a_database?lang=en

Traditionally, hostname and service name resolution were performed by functions such as gethostbyname(), getservbyname() etc. These traditional lookup functions are still available, however those are not forward compatible to IPv6. Instead, the IPv6 socket API provides new lookup functions that consolidate the functionality of several traditional functions. These new lookup functions are also backward compatible with IPv4, so a programmer can use the same translation algorithm in an application for both the IPv4 and Ipv6. The getaddrinfo() is the new primary lookup function and a connection request from the dbaccess ultimately calls this socket API. You can pass several parameters to the getaddrinfo(), one of those parameter is addrinfo structure. By default, dbaccess passes value “AF_INET6” for addrinfo.ai_family. The ai_family field indicates the protocol family associated with the request, and will be PF_INET6 for IPv6 or PF_INET for IPv4.

If the ai_family set to AF_INET6 (IPv6) the getaddrinfo() will search the DNS everytime. If the ai_family set to AF_INET, then it don’t query the DNS server. You can consult the ‘man’ page for getaddrinfo() for detailed information.

Beginning with Informix 10.00.xC4 and Client SDK 2.90.xC4, the database server checks, on startup, whether IPv6 is supported in the underlying operating system. If IPv6 is supported it is used. If the underlying operating system does not support IPv6, the IPv4 address is used.

In case of a problem with DNS lookup and encountering slow connection to databse, you may use the environment variable IFX_DISABLE_IPV6 (IFX_DISABLE_IPV6=1) to disable Ipv6 and this will set the ai_family to AF_INET only and will not do subsiquent query to the DNS server.


Informix 12.10 Extending a physical log.

Saw this in the online.log:

15:43:52 Performance Advisory: The physical log size is smaller than the recommended size for a
server configured with RTO_SERVER_RESTART.
15:43:52 Results: Fast recovery performance might not be optimal.
15:43:52 Action: For best fast recovery performance when RTO_SERVER_RESTART is enabled,
increase the physical log size to at least 90536 KB. For servers
configured with a large buffer pool, this might not be necessary.

Well my play server does not have a large bufferpool so:

onstat -l – get chunk from phybegin.

onstat -d – make sure chunk is extendable – flag at position 5 = E

Now extend the chunk:

echo ‘execute function task(“modify chunk extend”,”2″,”25000″)’ | dbaccess sysadmin
Your evaluation license will expire on 2014-11-16 00:00:00

Database selected.

(expression) Chunk 2 has been extended 25000Kb.

1 row(s) retrieved.

You must be in the sysadmin database to use the task() function!

Now extend the physical log:

onparams -p -s 90536
Your evaluation license will expire on 2014-11-16 00:00:00
Do you really want to change the physical log? (y/n)y
Log operation started. To monitor progress, use the onstat -l command.
** WARNING ** Because the physical log has been modified, a level 0 archive
must be taken of the following space before an incremental archive will be
permitted for it: plog
(see Dynamic Server Administrator’s manual)