How can I find what db2_fix_pack _adjustment, db2_shmem_size and db2_shmem_client_position to use for Guardium for DB2 on AIX?

Diagnosing The Problem

The parameters are the same for same version of DB2 and AIX version. So if you calculate it correctly for DB2 v9.7 for AIX 6.1 then it's same for all DB2 v9.7 on AIX 6.1 etc.

Here's example on how to calculating DB2 Shared Memory Values:

1) Make sure DB2 is currently running.

2) Calculate db2_shmem_size manually. As DB2 user Run:

3) Using the value returned, use the following formula (ASLHEAPSZ + 1) * 8192. In this case, (15 + 1) * 8192 = 131072 db2_shmem_size.

3b) Alternatively, you can calculate db2_shmem_size empirically. The value should match the value above. If it doesn’t , try them both to see what works.

ipcs –ma | sort -n -2 +3 > /tmp/before.txt
db2 connect to SAMPLE
ipcs -ma | sort -n -2 +3 > /tmp/after.txt
db2 terminate
diff /tmp/before.txt /tmp/after.txt | awk '{if ($10 == 2) print $11}'
131072

4) Calculate db2_shmem_client_position (ASLHEAPSZ * 4096). In this case (15 * 4096) = 61440.

5) The db2_fix_pack_adjustment is obtained by trial and error. Start with 20 and we’ll see how to verify.

6) Configure the 3 parameters in the inspection engine.

7) If STAP is running, stop it by commenting it in /etc/inittab and running the init q command. You can also use chitab to change the utap entry from “respawn” to “off”.

8) Run STAP manually in debug level 4. As root, run:

If you get tons of output and things become unreadable, consider using “dummy” ports in the inspection engine to temporarily disable TCP traffic interception.

9) At the beginning of the STAP debug output, you should get some messages that indicate stap was able to locate the DB2 “ftok” file (<db2_install_dir>/sqllib/.ftok). In particular, you want to see the DB2 Token line:

10) Connect to DB2 using a SHARED MEMORY connection and run some SQL:

11) You should see corresponding packets in the stap debug output:

Even though you can see the SQL statement here, most likely this statement will not log properly because the third byte is 00. On a valid packet, the third byte will be d0

12) Now let’s try changing the db2_fix_pack _adjustment from 20 to 16. Run the stap in debug mode again, terminate current DB2 connection, and log back in with shared memory. Run a new command:

13) Check the STAP debug output, you’ll see a packet with your SQL:

Third byte is d0 , this value has a good chance of working. One more thing to note: the source port (sport) and destination port (dport) in the header should be random values instead of the actual DB2 TCP ports. That’s expected and indicates this is shared memory traffic.

Other troubleshooting tips:

1) As mentioned previously, if running STAP in debug mode produces unmanageable output, change the inspection engine to dummy ports and try again. Optionally, you can temporarily set intercept_types=shmem in the inspection engines, which will tell ktap to only capture shared memory traffic. This is new functionality so avoid using it if possible.

2) If problems persist, please capture all output from running STAP in debug mode and send it to support. In addition, send the output of all commands used to calculate DB2 values.