This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

DB2 10.1 fp4 ICC Error

Scenario: After applying DB2 10.1 fp4 and trying to backup the database, we were seeing an ICC Error in the log:

2014-11-03-15.31.41.864652+000 I15689A2279 LEVEL: Error
PID : 18481304 TID : 1 PROC : nsrdb2ra
INSTANCE: db2sid NODE : 000
HOSTNAME: db01
EDUID : 1
FUNCTION: DB2 Common, Cryptography, cryptContextRealInit, probe:60
MESSAGE : ECF=0x90000403=-1879047165=ECF_CRYPT_UNEXPECTED_ERROR
Unexpected cryptographic error
DATA #1 : Hex integer, 4 bytes
0x00000002
DATA #2 : Hex integer, 4 bytes
0x00000002
DATA #3 : Hex integer, 4 bytes
0x00000000
DATA #4 : String, 32 bytes
Invalid data value: icclib.c:989
CALLSTCK: (Static functions may not be resolved correctly, as they are
resolved to the nearest symbol)
[0] 0x090000007ECEDB34 pdOSSeLoggingCallback + 0xC0
[1] 0x090000007DFB0224 oss_log__FP9OSSLogFacUiN32UlN26iPPc + 0x1C4
[2] 0x090000007DFB0024 ossLog + 0xC4
[3] 0x090000007F37A960 cryptLogICCErrorWithStatus + 0xF8
[4] 0x090000007F37A2D0 cryptContextRealInit + 0x544
[5] 0x090000007F37B160 cryptContextCheckAndInit + 0x58
[6] 0x090000007F37B5E4 cryptDHInit + 0x1BC
[7] 0x090000007F35E3C4 sqlexSlcServerEncryptAccsec + 0xD0
[8] 0x090000007F35E260
sqlexSlcServerEncryptAuthenticate__FP14db2UCinterfacelPUi + 0x130
[9] 0x090000007E50B258 sqlexAppAuthenticate__FP14db2UCinterface +
0x3550
[10] 0x090000007E6DCC80 sqljrDrdaArAttach__FP14db2UCinterface + 0x84
[11] 0x090000007E6DC26C sqleUCdrdaARinit__FP14db2UCconHandle + 0x4B4
[12] 0x090000007E56A39C sqleUCappAttach + 0x92C
[13] 0x090000007E6A1F28 sqleatin__FPcN41sP5sqlca + 0x23C
[14] 0x090000007E6A1530 sqleatcp_api + 0x1C0
[15] 0x090000007E6A127C sqleatin_api + 0x54
[16] 0x00000001002FB550 InstAttach + 0xC0
[17] 0x00000001002DD104 validate_db2_credentials + 0x128
[18] 0x00000001002DEE38 db2ra_verify_credentials + 0xC8
[19] 0x00000001000008C0 ra_message_handler + 0xF0
[20] 0x0000000100055134 ra_default_message_handler + 0x308
[21] 0x0000000100054BE0 ra_callback + 0x17C
[22] 0x00000001002D93A4 ssncommon_do_callback + 0x70
[23] 0x00000001002D6908 msgssn_get_expected_msg_varp + 0x674
[24] 0x00000001002D7DFC ssn_getmsg_poll_varp + 0xEC
[25] 0x0000000100054980 ra_startagent + 0x128
[26] 0x0000000100055478 ra_main + 0x278
[27] 0x0000000100000604 main + 0x114
[28] 0x0000000100000318 __start + 0x90

The quick resolution was that we needed to adjust the environment of the AIX database backup user to include the following:

> export ICC_IGNORE_FIPS=YES

This was put into the .profile of the AIX backup user.

Drop a SAP DB2 10.1 Database & Remove Instance

In case you have installed an IBM DB2 database instance using the SAP Software Provisioning Manager, and you would now like to remove this database and the DB2 software installation (SAP DB instance), then here’s a quick method:

As db2<sid>:

db2<sid> # db2stop
db2<sid> # db2 drop database
Then as root:

# cd /db2/db2<sid>/db2_software/instance
# ./db2idrop db2<sid>

# cd /db2/db2<sid>/db2_software/install
# ./db2_deinstall -a

Finally, remove DB related directories (if necessary):

# rm -rf /db2/<SID>/db2dump/*
# rm -rf /db2/<SID>/log_dir/*
# rm -rf /db2/<SID>/*archlogs/*

IBM DB2 10.1 Statistics Replication

Scenario: You would like to test a new index in an IBM DB2 10.1 database.  Unfortunately your development system doesn’t have anywhere near the same number of records in the database table on which you are creating the new index.

Like many other RDBMSs, DB2 uses table and column statistics to influence the optimiser’s decision as to which access path to choose at execution time.
By replicating only the statistics, it’s possible to fool the optimiser into thinking the table has more records present, than it really does.  This means that it’s likely to choose a different access path.

When performance tuning a database, it’s useful to use this method of fooling the optimiser, because you can emulate a larger table in a small development system with little actual data.

The process in DB2 is like this:
– Generate (or export) the statistics for a table in a production database system (PRD) schema DBA.
– Modify the export file.
– Upload the contents of the export file into a development database system (DEV) schema DBB.
– Test.

Step 1 – Export the statistics for a table in production.

Connect into the production database (DBA), then use the db2look command to create an export file containing the UPDATE commands for adjusting the statistics:

db2prd> db2 connect to PRD

db2prd> db2look -d PRD -e -c -m -r -t DBA.TABLE1 -o table1_STATS.sql

The output will be written to the table1_STATS.sql file in the current directory.

Step 2 – Modify the export file.
You should edit the output file to remove all lines before the line “– Mimic table TABLE1”, failure to do this could mean dropping the TABLE1 table in development.

You must also edit the file and replace all instances of schema “DBA” with “DBB” to ensure that the correct development database schema is found.
The modified file will look like:

— Mimic table TABLE1

UPDATE SYSSTAT.TABLES
SET CARD=2341434,
NPAGES=14636,
FPAGES=14645,
OVERFLOW=9473,
ACTIVE_BLOCKS=0
WHERE TABNAME = ‘TABLE1’ AND TABSCHEMA = ‘DBB’;

UPDATE SYSSTAT.COLUMNS
SET COLCARD=1,
NUMNULLS=0,

Step 3 – Upload the statistics into the development database.

db2dev> db2 connect to DEV
db2dev> db2 -tf ikpf_STATS.sql

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

You’re now ready to test.

To reset (re-gather) the statistics on the development database table, you simply need to re-collect statistics using the RUNSTATS command: “db2 RUNSTATS ON TABLE TABLE1 WITH DISTRIBUTION AND INDEXES ALL”.

IBM WebSphere IHS Plugin Hostname Cookies Conundrum

Whilst attempting to set up IBM WebSphere with a remote IBM HTTP Server (IHS) using the WAS Plugin, I was hitting an issue whereby cookies would only be served to the client browser when accessing the IHS server using the real host name and not the DNS alias I had created.

After 2 days of trying different combinations of configuration, I finally found this article here:
The host name DNS alias I had created used underscores.
Whilst this is permitted in the system, it is not a valid host name accord to the IETF RFCs, and therefore no cookies are served/retrievable by the WebSphere application server.
In short, always use hyphens instead of underscores in your WAS or IHS DNS aliases.

IBM WebSphere 6.1.0 GUI Install on RHEL 4.5

If you have been given the opportunity to install IBM WebSphere 6.1.0 on RedHat Enterprise Linux 4.5, then you may be wondering why you can’t get the GUI installer to launch using the X-Windows terminal you’ve painstakingly enabled.

Well, the answer is simple; unless you are on the console, then you can’t.  That is, you must be on DISPLAY “:0.0”.
If your sys admin is denying you access, or it’s a VM and you’re not allowed console access, then you’re also stuck.

The only other solution is to use the response files provided in the install directory of the installation media.
Customise the response file and then run the install.sh in silent mode.
There is a very helpful IBM help page on how to do all of this here: https://publib.boulder.ibm.com/infocenter/wasinfo/v6r1/topic/com.ibm.websphere.base.doc/info/aes/ae/tins_runSilent.html

Good luck.

ps. It presents a more systematic, repeatable approach to installing an environment if you use response files.  No mistakes, same every time.
What’s more, you can also silently install the “UpdateInstaller” and also the FixPacks.