StarQuest Technical Documents

SQDR Plus on AIX

Last Update: 6 July 2021
Product: SQDR & SQDR Plus
Version: 5.22 and later
Article ID: SQV00PL054

Abstract

SQDR Plus can be installed on Windows, Linux or IBM AIX.

Using SQDR Plus on AIX is similar to Linux, with the following differences.

Contents:

• Password expiration for sqdr user
• Check umask of Installing User
• Avoid automatic StarSQL package binding
• Use stored procedure for Db2 LUW source
• Starting and stopping SQDR Plus services
• Network Tuning
• Disk I/O Tuning
  • Location of Db2 tablespaces & logs
  • File system caching
  • pv_pbuf_count - LVM performance tuning
    

Password expiration for sqdr user

* After creating the AIX user sqdr, connect as that user to make sure that the password is not expired. The default behavior on recent AIX systems is to create users with expired passwords.

You can avoid the expired password situation by creating the user with the -e parameter:

# useradd -e 0 sqdr

If the sqdr user has already been created, you can change it to non-expiring with:

# chuser expires=0 sqdr

This adds the attribute expires=0 to user's entry in /etc/security/user.

If the sqdr user is being forced to change its password (due to expiration or an action by an administrator), clear the attribute flags = ADMCHG from the user's entry in /etc/security/passwd with:

# pwdadm -c sqdr

We recommend using a non-expiring password for db2inst1 as well:

# chuser expires=0 db2inst1

Check umask of Installing User

Before installing or updating SQDR Plus, enter the command umask  to determine how file permissions are set for newly created files. We recommend using umask 022 to ensure that the SQDR Plus program files are readable by the sqdr user.

Avoid Automatic StarSQL Package Binding

In order to prevent Launch Agent from trying to rebind StarSQL packages when restarting an agent, do the following:

Creating and starting the first agent will create the table SQPKGINFO in the Derby control database for Launch Agent..

Use ij or the built-in Query Tool to add a row for each staging database:

select * from SQPKGINFO
insert into SQPKGINFO values('SQDRP0',11)
insert into SQPKGINFO values('SQDRP1',11)
insert into SQPKGINFO values('SQDRP2',11)

(use a value of 10 for SQDR Plus 5.22; 11 for SQDR Plus 5.23)

and restart the sqdr-capagent service:

# /etc/sqdr-capagent restart

If you do not do this, restarting the agent will fail with the following error:

Did not find StarSQL JNI Library: starbinder
starbinder (Not found in java.library. path): (Will try a hardcoded path)
java.lang.UnsatisfiedLinkError: starbinder (Not found in java.library.path)

Use Stored Procedure for Db2 LUW source

In the case of a Db2 LUW source, you must install the SQDR.SQ_READLOG stored procedure on the host and configure udbReadLogUsingSP=true in the agent configuration.

Starting and stopping SQDR Plus Services

The SQDR Plus installer creates three symbolic links in /etc:

• sqdr-derby
• sqdr-jetty
• sqdr-capagent

Any of these scripts can be invoked (as root or user sqdr) with the options start, stop, and status. e.g.

# /etc/sqdr-derby start

The installer also creates entries for the three services in /etc/inittab so the services start automatically at boot time. Note that Db2 should also be started automatically; the Db2 installer creates an entry for the DB2 Fault Monitor Coordinator (db2fmcd) that ensures Db2 is running.

Network Tuning

The following settings may improve network performance. These changes are dynamic.

# no -o tcp_nodelayack=1
# ifconfig enN thread
(where enN is the network interface of interest; use -p to persist changes across reboots )
# no -o tcp_fastlo=1

After setting tcp_fastlo=1, the topas command showed improved performance for loopback traffic, which consists mostly of communication between the Capture Agent JRE processes and the local Db2 LUW database.

Disk I/O Tuning

Location of Db2 tablespaces & logs

We recommend using dedicated file systems for Db2 data (tablespaces) and for Db2 logs & other Db2-related objects. Before installing SQDR Plus, edit setup.conf to specify where newly created staging databases reside:

e.g.
DB2DFTDBPATH="ON /db2data DBPATH ON /db2logs"

If you have already installed SQDR Plus but have not created any agents or staging databases, edit the file /var/sqdrplus/conf/sqagent.properties to specify the locations of future databases (i.e. the parameters that will be included on the DB2 RESTORE command)

e.g. change
DB2iTemplateDB_DB2='/opt/StarQuest/sqdrplus/db2' ON /home/db2inst1
to
DB2iTemplateDB_DB2='/opt/StarQuest/sqdrplus/db2' ON /db2data DBPATH ON /db2logs

If you have already created staging databases, you will need to do a BACKUP and RESTORE of the existing databases to change the locations.

File System Caching

On AIX with a jfs2 filesystem (default), the file system containing the tablespaces (e.g. /db2data) should be mounted as Concurrent I/O (CIO). Edit /etc/filesystems, stop Db2, modify /etc/filesystems, and umount/mount the file system:

/db2data:
dev = /dev/db2datalv
vfs = jfs2
log = INLINE (or log = /dev/hd8 etc)
mount = true
type = db2datavg
options = cio,rw
account = false

Use lsfs to verify the setting.

see the IBM Db2 documentation File system caching configurations for details.

pv_pbuf_count - LVM performance tuning (dedicated physical disks)

Use performance analysis tools to determine if increasing the pv_pbuf_count from its default of 256 will improve performance. You will need to stop Db2 and vary off and on the volumen group to make a change.

# lvmo -v db2logvg -o pv_pbuf_count=2048
# varyoffvg db2datavg
# varyonvg db2datavg
# lvmo -a -v db2datavg
vgname = db2datavg
pv_pbuf_count = 2048
total_vg_pbufs = 2048
max_vg_pbufs = 524288
pervg_blocked_io_count = 0
pv_min_pbuf = 512
max_vg_pbuf_count = 0

see the IBM AIX documentation LVM performance tuning with the lvmo command for details.