Smartblob cdr sync Vs cdr check repl

Informix ER has a number of ways to move data in replicates.
I was doing performance testing of transferring smartblob data from an old server to a new one. The target table was empty for this test. It was about 5gb composed of 100K sized smartblob records.

Logging Modecdr synccdr check repl
Logging Off5:349:30
Logging OnLogical Log Rollback (DNF)9:54

Obviously having smartblob logging on for important data is the preferred way to do it. You *can* lose data with an engine crash if smartblob logging is off, even if the table itself is logged. However for copies, the fastest way may be with it disabled.

Next Virtual WAIUG Meeting

We have another amazing WAIUG meeting coming up in November 2024. It will be another virtual one partnering with the IIUG.
We are currently in need of speakers. If you have an interesting topic you would be interested in presenting please reach out to us using the contact form.

Stay tuned for more information!

Remove Newlines From an Informix Unload File

Informix character fields can contain special characters and even newlines. This allows for formatted data, e.g. a list spanning multiple lines within a single field. A newline is also an end of record character when a table is unloaded to a file, e.g. with the UNLOAD command, a dbexport, or optionally in an external table. To distinguish a literal newline character in the unload file, Informix escapes the newline with a backslash (“\”) to indicate that the next character does not have any special meaning. Note that the escape character can be changed by setting DEFAULTESCCHAR in the Informix configuration file ($ONCONFIG), or as a session environment variable.

When a file is loaded into a table with Informix, the escape character itself is not inserted into the table but instead directs the load to insert the following newline as a literal newline and to not treat it as the end of the record.

When loading such a file into a non-Informix database, the special significance of an escape character is not supported, and instead the loading utility will attempt to load the escape character itself and will then treat the embedded newline as the end of the record. This will usually result in an error because the record will have too few fields. Loading into Oracle for example may show as:

ORA-01722: invalid number

The usual solution for this condition is to convert the unload file and strip out the 2-character combination of backslash + newline. Most Unix utilities will also treat newlines as an end of record characters so you cannot just use a simple “search and replace” regular expression to identify and remove these combinations. There are several ways to tackle the problem, but I use the “sed” command with the “N” operator to pull up the following line when a line ends in a backslash, then remove the backslash + newline and replace it with a “space”. I choose to substitute a space for the newline to preserve readability in cases where the field may have contained a list.

The following is an example of a script that will perform this conversion. It also:

  • Strips return characters from the unload file
  • Replaces literal “\” characters, shown as “\\” in the unload file with a single backslash

The script assumes that the default character of “\” is used to escape newlines in the file.

INFILE=$1

if [[ ! -s "${INFILE}" ]]
then
   echo "File $INFILE does not exist or is empty"
   exit 1
fi

cat ${INFILE} | tr -d "\015" | sed '
:loopy
/\\$/{
N
s/\\\n/ /g
/\\$/b loopy
};
s/\\\\/\\/g
'


Input file (tstfile):

AAA|BBB|CCC|
DDD\
D\
DDD|EE\
EE|F\
F|
GGG|HHH|I\
II\
I|
JJJ|KKK|LLL|
MMM|List #1 - 1\\2 a thing\
List #2\
List #3|NNN|


Results:

./remove_nl.sh tstfile

AAA|BBB|CCC|
DDD D DDD|EE EE|F F|
GGG|HHH|I II I|
JJJ|KKK|LLL|
MMM|List #1 - 1\2 a thing List #2 List #3|NNN|

Mike Walker (mike@xdbsystems.com)
IBM Data Champion

Using Informix SSL With External CA and OpenSSL

Informix SSL is an important piece of your security setup when running a relational database.
The Informix server is still set up to use GSKit from IBM to handle the internal server keystore. Clients are free to use openssl. In this article we are going to cover step by step how to set up SSL using a key generated from an external certificate authority (CA) rather than doing self-signed ones generated by the local server. All of the examples here are assuming a 64bit Linux system. Windows will use the same method and 32bit systems will have a different gsk8capicmd tool.

Settings for this example:

INFORMIXSERVER=informix4
INFORMIXDIR=/opt/informix
Primary listener: informix4  onsoctcp 9088
SSL Listener: informix4_ssl onsocssl 9089
SSL_KEYSTORE_LABEL: test_ssl_label
Keystore Password: my_password

The first step is to go to $INFORMIXDIR/ssl and create your empty keystore using GSKit. Note that it has to be named the same as the DBSERVERNAME

gsk8capicmd_64 -keydb -create -db informix4.kdb -pw my_password -type cms -stash

Next use GSKit to generate a CSR (certificate request file)

gsk8capicmd_64 -certreq -create -db informix4.kdb -stashed -label test_ssl_label -dn "CN=xdbsystems.com" -size 2048 -sigalg SHA256_WITH_RSA -target informix4.csr


Use that file to request a new public/private keypair from your CA. Different systems will handle this differently so talk to your security administrators.

Next Add the management chain certificates to the keystore

gsk8capicmd_64 -cert -add -db informix4.kdb -pw my_password -file ManagementCA-chain.pem

Next import the new server certificate you received from the certificate request

gsk8capicmd_64 -cert -receive -db informix4.kdb -stashed -file informix4.pem

Validate the keys are there

gsk8capicmd_64 -cert -list -db informix4.kdb -stashed
Certificates found
* default, - personal, ! trusted, # secret key
!       "O=EJBCA Container Quickstart,CN=ManagementCA,UID=c-0fno0f82c4y4p6umb"
-       test_ssl_label

Bring the engine up, you should see the following

Forking 1 'soctcp' listener threads...succeeded
Forking 1 'socssl' listener threads...succeeded

Next set up a client, on the client system create a keystore, make sure clientsdk is installed with the option to use openssl. This creates the keystore and loads in the root CA chain

openssl pkcs12 -export -nokeys -in /opt/informix/ssl/ManagementCA-chain.pem -caname "O=EJBCA Container Quickstart,CN=ManagementCA,UID=c-0fno0f82c4y4p6umb"  -passout pass:my_password -out client1.p12

Create the stash file

onkstash client1.p12 my_password

Set up the client to have conssl.cfg in $INFORMIXDIR/etc:

SSL_KEYSTORE_FILE  /opt/informixcsdk/ssl/client1.p12
SSL_KEYSTORE_STH /opt/informix/ssl/client1.sth

Now you can validate that the client only needs the CA information to validate the certificates, note there is no Informix server specific key, just the CA chain.

openssl pkcs12 -nokeys -info -in client1.p12 -passin pass:my_password
MAC: sha256, Iteration 2048
MAC length: 32, salt length: 8
PKCS7 Encrypted data: PBES2, PBKDF2, AES-256-CBC, Iteration 2048, PRF hmacWithSHA256
Certificate bag
Bag Attributes
    friendlyName: O=EJBCA Container Quickstart,CN=ManagementCA,UID=c-0fno0f82c4y4p6umb
subject=UID = c-0fno0f82c4y4p6umb, CN = ManagementCA, O = EJBCA Container Quickstart
issuer=UID = c-0fno0f82c4y4p6umb, CN = ManagementCA, O = EJBCA Container Quickstart
-----BEGIN CERTIFICATE-----
MIIEszCCAxugAwIBAgIUZ3v7iIk5DxAxtF8jMELOef9Lp3cwDQYJKoZIhvcNAQEL
BQAwYTEjMCEGCgmSJomT8ixkAQEME2MtMGZubzBmODJjNHk0cDZ1bWIxFTATBgNV
BAMMDE1hbmFnZW1lbnRDQTEjMCEGA1UECgwaRUpCQ0EgQ29udGFpbmVyIFF1aWNr
c3RhcnQwHhcNMjQwOTEwMjEwNDE4WhcNMzQwOTEwMjEwNDE3WjBhMSMwIQYKCZIm
iZPyLGQBAQwTYy0wZm5vMGY4MmM0eTRwNnVtYjEVMBMGA1UEAwwMTWFuYWdlbWVu
dENBMSMwIQYDVQQKDBpFSkJDQSBDb250YWluZXIgUXVpY2tzdGFydDCCAaIwDQYJ
KoZIhvcNAQEBBQADggGPADCCAYoCggGBAJv+MpjjAHh5msYkw8KwSJ5HSW79jofS
Mt9ruk7A85Ac+xFEA3IM9i1T9PISdwWzopCz/d1T3JYEpngKyUex9DANOLoKqTyp
XDm6Yyj6YH0vxNLEroQJBpvw6SCHKBO6EjZkAAecCOnYmT95QPdF2w5u5Kk1X7yg
VV59HIYxVd3xZVW2llrHH+u4pIeNruSotvRalpEddXdve1Ym0uoexRc5q4z44wwz
0uWwOaQZv7vGmTkfNT6nUqCqw/M6STJpyadNVMEyUeXHfuNNQbJ6UqyWAKH4fFGZ
V6jjKMENEC2f5I8BR5xB1hjWbVsOHuGCKLeRuP0Hq14B3JCaUFK7CLtpXVlLEWJ1
3Cm2SbNWUmVveEMXEN6L8BVwGdyyFtM2AsP/RNcKWSMiInGe39Hr4XpVN1tPqHCm
gh8ug/7v3n96aC2138oIjcrWVtGdAuo4Cb9N9/cOBnnyOp/Yof4Hlru9wyTSxMTo
yDQToNw6xk3/w5ueJDkAlMQD715lknEp8QIDAQABo2MwYTAPBgNVHRMBAf8EBTAD
AQH/MB8GA1UdIwQYMBaAFG4HtnbBN31dyfzLpmqyLgveCnl+MB0GA1UdDgQWBBRu
B7Z2wTd9Xcn8y6Zqsi4L3gp5fjAOBgNVHQ8BAf8EBAMCAYYwDQYJKoZIhvcNAQEL
BQADggGBACHAeek2OHKhrHW80t0hWWa83sXNFbKFc1hxb5H9GcT4UueTeKirfy+U
mW3sY5L5MzFTVUoqHpdg7mYH7GGszyUZPnZL6iCZJwZs4QDraDl1cuxHa4JQRQtV
LLMDVGL40Qc2p3bhkw3HOcJHD4wDVg4bazoLizyrsMk1rKNWvjQnHI7Ci/AcN7r0
8Yt8OWQjYdlSHLKBj0H03vZXPGzlr5j+aJGEXorGRHXkiMEntaUM2XZeyL5y1uSo
NnegwX7WvrT/LFx7EYN3LCNbnlph7BCoeF8TNdPVFOIFx/zxJDiIdYuB7nop3ZHN
SABgCxVKTzetHlt//DC/NIa5pfVXwRTKFuMvqjk/Zri4bXGjf/GaS9hRqLI43KoF
Urq3oLtIJPTbF8AxgFXxEaa863nNAta8yEO05ulDrQF9uGiGKjenTlsTY+bor8nr
FM/jwVNwGTFaddsWPcVNak5ayMQioSNn2/GWE52LyWhNaIBAZy05/5VrAJTnVWZL
whKy/ea4hQ==
-----END CERTIFICATE-----

Note with the Informix CSDK. As of newer Linux versions, the older libssl.1.1 it expects is no longer available. You may need to install a libssl compat library to get the libraries needed. You may also need to symlink $INFORMIXDIR/lib/libisi_o11.so.3.0 to $INFORMIXDIR/lib/libisi.so.3 if it returns an error that the file is missing. Hopefully this will be corrected in the next edition of Informix to support the newer openssl libraries that are standard.

Thomas Beebe
tom@xdbsystems.com
IBM Data Champion

Installing Informix 14.10FC11 on Ubuntu 22.04

Each version of Informix has its own specifics for what is required. Below are the steps needed for the operating system to get Informix 14.10FC11 to work correctly.

Create the informix group and user:

groupadd informix
useradd -g informix -m informix

Install Packages: (Note that rpm is required for the current gskit install, deb files are missing)

apt-get install ksh libaio-dev libncurses5 default-jre rpm 

Install Informix as normal, assuming /opt/informix is the target location

Set up global settings in /etc/informix.conf:

INFORMIXDIR=/opt/informix
INFORMIXSERVER=ids_system_tcp
ONCONFIG=onconfig.ids_system_tcp
PATH=/bin:/usr/bin:/usr/local/bin:/sbin:/usr/sbin:/opt/informix/bin

Set up global libraries (getting away from LD_LIBRARY_PATH)

/etc/ld.so.conf.d/informix.conf:
/opt/informix/lib
/opt/informix/lib/cli
/opt/informix/lib/esql

Run: ldconfig

Edit /home/informix/.profile add:

. /etc/informix.conf
export INFORMIXDIR INFORMIXSERVER ONCONFIG PATH

Set up Informix config as normal and initialize the engine. Once it is fully initialized and sys* databases are created, bring it back offline.

Next, set up Informix to be controlled by systemd:

/etc/systemd/system/informix.service:
[Unit]
Description=Informix server
Wants=basic.target
After=basic.target network.target

[Service]
Type=forking
User=informix
EnvironmentFile=/etc/informix.conf
ExecStart=/opt/informix/bin/oninit
ExecStop=/opt/informix/bin/onmode -kuy
TimeoutStartSec=1000
TimeoutStopSec=1000

[Install]
WantedBy=multi-user.target

Set up auto start/stop:

systemctl daemon-reload
systemctl enable informix
systemctl start informix

At this point you should be ready to go to start working with Informix.

By: Thomas Beebe (xDB Systems, Inc) tom@xdbsystems.com. IBM Champion.

How To Download Informix

This may be a simple seeming task but when pointing someone unfamiliar with how IBM handles software to the site there is often confusion. Hopefully this article may be helpful for people who have not had to get the software before.

If you are downloading the free versions, including the Client SDK, use the link at the top “Informix Information > Informix Downloads” which will take you to the IBM download site. You will need a free IBM ID to proceed.
If you are coming to download a licensed version, sign in to the passport advantage site with login credentials that are authorized to access the software you need.

Either way once you get to the software download section you will see a lot of options for different versions of Informix and tools.

You will see a naming convention for each download. For the server you will see it start with a 12 or a 14 (eventually a 15 as well) which is the primary version of Informix.
Next is the subversion of ‘.10’. Right now all current versions of informix are .10. This may change in the future.
The next is a letter, above it is all ‘F’. This is FC – 64bit, UC- 32bit, TC – 32bit Windows
Then it is the release information. In this case most of them are XC11 for v14, and XC12 for version 12. New releases will mostly be increases in the subrelease number. These tend to come out every few months.
The drivers (CSDK, JDBC) are going to be a slightly different numbering system. With 4.50 = V14, V4.10 = V12, V3X for Version 11 releases, etc.

The different pieces of software you may see:

  • IBM Informix Developer Edition – This is the server install package. With V14 all installs will be the developer edition until a license file is applied
  • Informix Enterprise Time Limited Edition – This is a full server install with all of the features that will only be usable for a limited time before it stops working. This is used to evaluate and test enterprise features before purchasing it
  • Informix JDBC drive is the standalone JDBC Driver
  • IBM Informix CSDK is the client SDK which contains all of the different drivers as well as the SDK to compile against. Included in this is some basic utilities including ‘dbaccess’ Version 12 has this bundled with the server, V14 has the engine not include the CSDK so you need to install it as well
  • Informix <Version> Edition Installer – This is the license file that needs to be installed with the engine to change the V14 developer edition into that full featured version. The public site only has the “Innovator-C” edition of this, but in passport advantage it should have all of the ones you are entitled to. Note that this file should be 10-15MB in size.

So when you go to download, get the latest release number for the operating system and CPU architecture that you need. If you are running Linux you probably want to get “Linux x86 64” versions.

Once you download the file and extract it you will see another .tar file in the directory, this is a little odd but there is a reason for it

root@alpaca:/home/tom/Downloads/Informix14/FC11# ls
ibm.csdk.4.50.FC11.LNX.tar
root@alpaca:/home/tom/Downloads/Informix14/FC11# tar -xpf ibm.csdk.4.50.FC11.LNX.tar 
root@alpaca:/home/tom/Downloads/Informix14/FC11# ls
ibm.csdk.4.50.FC11.LNX.tar  ibm.csdk.4.50.FC11.LNX.tar.cosign.sig  informix.pem  README.codesign

The file ibm.csdk.4.50.FC11.LNX.tar is actually a new file, just of the same name. You can use the sig files included to validate that the files are genuine and have not been tampered with. Read README.codesign for information on how to do that.

If you untar the new .tar file you will see the contents you are expecting:

root@alpaca:/home/xilet/Downloads/Informix14/FC11/t# tar -xvf ibm.csdk.4.50.FC11.LNX.tar
installclientsdk
doc/
doc/csdk_lux_relnotes.html
doc/csdk_win_relnotes.html
doc/Gls_relnotes_4.50.html
doc/Glsapi_machine_notes_4.50.txt
doc/ESQLC_machine_notes_4.50.txt
doc/Odbc_machine_notes_4.50.txt
doc/Libcpp_machine_notes_4.50.txt
csdk.properties
README_csdk.txt

Then just run the installclientsdk as normal.

Often it is asked why the Informix drivers are not included with Linux distributions repos. Informix includes encryption pieces that have limitations from the US gov on being exported to specific countries.

Thomas Beebe (tom@xdbsystems.com)
IBM Data Champion

WAIUG Meeting Dec 7th 2023

The next WAIUG meeting will occur virtually on Dec 7th 2023. 9am – 1pm EST. It will be an exciting set of technical talks from presenters with the developers of Informix from HCL as well as user speakers.

Presentations by:

  • HCL – Pavan Gummalla – Ensuring Data Integrity and Confidentiality in Informix Database
  • HCL – Shubham Kapoor – Sneak peek at updates to InformixHQ with Demo
  • Laurent Galais and Delane Freeman with FourJs – 4GL and Genero
  • Mike Walker – Check your backups with archecker.
    You ARE testing your backups aren’t you? The Informix archecker utility can be used to verify that your Informix backups are ‘good’, and also to perform table-level restores from a backup…a handy feature after a ‘whoops, I shouldn’t have run that in production’ moment! Mike will explain how to use this utility to verify backups and perform table-level restores, and demonstrate these features.

If you are new to the WAIUG, we are a free community drive user group focused on educating and sharing experiences for Informix users of all skill levels and backgrounds.

Please RSVP here to attend (Webex)

Tech Tip – CSDK Install 4.50FC10 on Win11

Some users may find that when installing 4.50FC10 on Windows 11 the installer crashes with:

Flexeraaw7$aaa: Windows DLL failed to load
at Flexeraaw7.af(Unknown Source)
at Flexeraaw7.aa(Unknown Source)
at com.zerog.ia.installer.LifeCycleManager.init(Unknown Source)
at com.zerog.ia.installer.LifeCycleManager.executeApplication(Unknown Source)
at com.zerog.ia.installer.Main.main(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:90)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
at java.lang.reflect.Method.invoke(Method.java:508)
at com.zerog.lax.LAX.launch(Unknown Source)
at com.zerog.lax.LAX.main(Unknown Source)

To solve this issue, open a windows command window and

set JAVA_TOOL_OPTIONS="-Dos.name=Windows 7"
installclientsdk.exe

WAIUG December 2022 Meeting Replay

Thank you for everyone who attended! For those of you that missed it or want more information below is the replay and slides from the meeting. Our next meeting will be somewhere in middle 2023, so please stay tuned. If you wish to present or have ideas for topics please reach out and let us know.

WAIUG Part 1 – Welcome and Doing Storage Better by Art Kagel
Replay on YouTube: https://youtu.be/JE70r-2chOs
Slides
Welcome by Thomas Beebe and Doing Storage Better – Exploring the best and worst options by Art Kagel

WAIUG Part 2 – Informix HQ by Shubham Kapoor
Replay on YouTube: https://youtu.be/vxaIHHEujSQ

WAIUG Part 3 – Explaining Explain Plans by Mike Walker
Replay on YouTube: https://youtu.be/8UIVIQBFzoU
Slides
Query execution plans are the best tool to give a detailed insight into exactly how a query is being run by Informix. They are the first stop when trying to understand just why a query is running slower than expected.

In this presentation, Mike Walker will show how to obtain a query plan and how to interpret it.
He will also describe what he looks for in the plan that shows where their tuning opportunities are hiding.

WAIUG Part 4 – Conclusion, Oracle Style Global Temp Tables by Art Kagel and Informix Certification by Rhonda Hackenburg
Replay on YouTube: https://youtu.be/0CPNA6N82kk

Extra presentation by Art Kagel on Oracle Style Global Temp Tables. Informix Certification by Rhonda Hackenburg and Meeting Conclusion by Thomas Beebe

Thank you again to the support of the IIUG for making this happen.
The next IIUG Tech Talk will be Feb 2nd:
Informix Sysmaster Database Queries for monitoring and performance tuning by Lester Knutsen

WAIUG Meeting Dec 8th Coming Up

The next WAIUG meeting will occur virtually on Dec 8th 2022. 9am – 1pm EDT. It will be an exciting set of technical talks from presenters with the developers of Informix from HCL as well as user speakers. Topic descriptions will be posted shortly.

Presentations by:

  • Art Kagel – Doing Storage Better – Exploring the best and worst options
  • Tom Girsch
  • Shubham Kapoor – Informix HQ
  • Mike Walker – Explaining Explain Plans
    • Query execution plans are the best tool to give a detailed insight into exactly how a query is being run by Informix.
      They are the first stop when trying to understand just why a query is running slower than expected.

      In this presentation, Mike Walker will show how to obtain a query plan and how to interpret it.
      He will also describe what he looks for in the plan that shows where there tuning opportunities are hiding.

With additional information on the Informix 14 Badge Exam

If you are new to the WAIUG, we are a free community drive user group focused on educating and sharing experiences for Informix users of all skill levels and backgrounds.

Please RSVP here to attend (Webex)