Washington Area Informix User Group

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

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

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

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