Paul Bastide

Db2 Tips

Db2: Tools

The following is a compendium of the tools I use with Db2.

  • db2top – handy tool for interactive analysis of the db (works on a console, and handy to diagnose lock issues). An example command is db2top -d fhirdb and then shift+U.

db2top image

  • db2pd to get realtime statistics.

  • db2expln and db2batch to understand and test the performance of specific queries and db2advis to recommend indexes and database artifacts.

Db2: Reorg/RunStat

All you need to know is on dba stackexchange, and the useful process to follow is:

  • RUNSTATS
  • REORG
  • RUNSTATS
  • REBIND packages
  • FLUSH PACKAGE CACHE DYNAMIC

Check FUN STATS TIME

SELECT TABSCHEMA || '.' || TABNAME AS TABLEWITHSCHEMA, (TIMESTAMPDIFF(16, CHAR(LASTUSED - stats_time))) AS DIFF_TIME, DATE(stats_time) AS RUNSTATS_TIME, DATE(LASTUSED) AS LAST_USED, CARD
FROM SYSCAT.TABLES
WHERE type='T'
AND TABSCHEMA LIKE 'FHIRDATA'
AND CARD != 0
AND (TIMESTAMPDIFF(16, CHAR(LASTUSED - stats_time))) > 0
ORDER BY DIFF_TIME DESC, CARD DESC
WITH UR;

** Outputs**

FHIRDATA.QUESTIONNAIRE_TOKEN_VALUES 67 2019-10-09 2019-12-17 1595
FHIRDATA.CLAIMRESPONSE_RESOURCES 68 2019-12-17 2020-02-26 186
FHIRDATA.ACCOUNT_STR_VALUES 68 2019-12-17 2020-02-26 169
FHIRDATA.ACCOUNT_RESOURCES 68 2019-12-17 2020-02-26 144
FHIRDATA.APPOINTMENTRESPONSE_TOKEN_VALUES 68 2019-12-17 2020-02-26 121
FHIRDATA.APPOINTMENT_LOGICAL_RESOURCES 68 2019-12-17 2020-02-26 108

Run ADMIN_CMD to RUNSTATS

SELECT 'CALL SYSPROC.ADMIN_CMD (''' || 'RUNSTATS ON TABLE ' || TABSCHEMA || '.' || TABNAME || ' ON KEY COLUMNS and INDEXES ALL''' || ');'
FROM SYSCAT.TABLES
WHERE type='T'
AND TABSCHEMA LIKE 'FHIRDATA2'
AND (TIMESTAMPDIFF(16, CHAR(LASTUSED - stats_time))) > 0
WITH UR;

Useful links for RunStats are:

  • https://github.com/IBM/FHIR/wiki/IBM-FHIR-Server-on-DB2—SQL-Tips
  • https://stackoverflow.com/questions/10666035/difference-between-and-lastexitcode-in-powershell
  • https://www.ibm.com/support/knowledgecenter/SSCJDQ/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0001063.html
  • https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_bif_timestampdiff.html
  • https://datageek.blog/en/2016/03/03/db2-administrative-sql-cookbook-listing-all-objects-in-a-schema/

DB2 Performance

A few strong references for Db2 performance, I recommend are:

Check Bufferpool Status

The specific error is com.ibm.db2.jcc.am.SqlException: There are no pages currently available in bufferpool "4099".. SQLCODE=-1218, SQLSTATE=57011, DRIVER=4.19.49

BP_NAME LOGICAL_READS PHYSICAL_READS HIT_RATIO MEMBER
-------------------- -------------------- -------------------- --------- ------
IBMDEFAULTBP 193898 951 99.50 0
MYPOOLP32K 458 140 69.43 0
IBMSYSTEMBP4K 0 0 - 0
IBMSYSTEMBP8K 0 0 - 0
IBMSYSTEMBP16K 0 0 - 0
IBMSYSTEMBP32K 0 0 - 0

6 record(s) selected.

Solution required reactivating the DB.

Handy DB2 Tool to test Connectivity

java -Djavax.net.ssl.trustStore=keystore.jks \
-Djavax.net.ssl.trustStorePassword=PASSWORD \
-Djavax.net.ssl.keyStore=keystore.jks \
-Djavax.net.ssl.keyStorePassword==****** -cp db2jcc4-4.19.49.jar \ com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://:50443 \ /DATABASE:sslConnection=true;" -user  -password  -tracing

[root@server]# java -Djavax.net.ssl.trustStore=keystore.jks -Djavax.net.ssl.trustStorePassword=******** -Djavax.net.ssl.keyStore=keystore.jks -Djavax.net.ssl.keyStorePassword=******** -cp WEB-INF/lib/db2jcc4-4.19.49.jar com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://:50443/database:sslConnection=true;" -user db2user -password ******** -tracing

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://:50443/database:sslConnection=true; -user db2user -password ******** -tracing

[jcc][time:2017-07-14-01:48:18.880][Thread:main][tracepoint:10] DataSource created. Table size: 1
 [jcc] BEGIN TRACE_XML_CONFIGURATION_FILE
 [jcc] dsdriverConfigFile=null
 [jcc] END TRACE_XML_CONFIGURATION_FILE
 [jcc] BEGIN TRACE_DRIVER_CONFIGURATION
 [jcc] Driver: IBM Data Server Driver for JDBC and SQLJ 4.19.49
 [jcc] Compatible JRE versions: { 1.6, 1.7 }
 [jcc] Target server licensing restrictions: { z/OS: disabled; SQLDS: disabled; iSeries: disabled; DB2 for Unix/Windows: disabled; Cloudscape: enabled; Informix: enabled }
 [jcc] License editions: { O: not found; ZS: not found; IS: not found; AS: not found; EE: not found; PE: not found }
 [jcc] Range checking enabled: true
 [jcc] Bug check level: 0xff
 [jcc] Default fetch size: 64
 [jcc] Default isolation: 2
 [jcc] Collect performance statistics: false
 [jcc] No security manager detected.
 [jcc] Detected local client host: client/ip
 [jcc] Access to package sun.io is permitted by security manager.
 [jcc] JDBC 1 system property jdbc.drivers = null
 [jcc] Java Runtime Environment version 1.8.0
 [jcc] Java Runtime Environment vendor = IBM Corporation
 [jcc] Java vendor URL = http://www.ibm.com/
 [jcc] Java installation directory = /opt/ibm/ibm-java-sdk-8.0-4.5/jre
 [jcc] Java Virtual Machine specification version = 1.8
 [jcc] Java Virtual Machine specification vendor = Oracle Corporation
 [jcc] Java Virtual Machine specification name = Java Virtual Machine Specification
 [jcc] Java Virtual Machine implementation version = 2.8
 [jcc] Java Virtual Machine implementation vendor = IBM Corporation
 [jcc] Java Virtual Machine implementation name = IBM J9 VM
 [jcc] Java Runtime Environment specification version = 1.8
 [jcc] Java Runtime Environment specification vendor = Oracle Corporation
 [jcc] Java Runtime Environment specification name = Java Platform API Specification
 [jcc] Java class format version number = 52.0
 [jcc] Java class path = WEB-INF/lib/db2jcc4-4.19.49.jar
 [jcc] Java native library path = /opt/ibm/ibm-java-sdk-8.0-4.5/jre/lib/amd64/compressedrefs:/opt/ibm/ibm-java-sdk-8.0-4.5/jre/lib/amd64:/usr/lib64:/usr/lib
 [jcc] Path of extension directory or directories = /opt/ibm/ibm-java-sdk-8.0-4.5/jre/lib/ext
 [jcc] Operating system name = Linux
 [jcc] Operating system architecture = amd64
 [jcc] Operating system version = 3.10.0-327.10.1.el7.x86_64
 [jcc] File separator ("/" on UNIX) = /
 [jcc] Path separator (":" on UNIX) = :
 [jcc] User's account name = root
 [jcc] User's home directory = /root
 [jcc] User's current working directory = /tmp
 [jcc] JCC outputDirectory = /tmp
 [jcc] Using global configuration settings:
 [jcc] maxTransportObjects = 1000
 [jcc] Dumping all system properties: { java.vendor=IBM Corporation, sun.java.launcher=SUN_STANDARD, javax.net.ssl.trustStorePassword==******, os.name=Linux, ..., com.ibm.oti.vm.library.version=28, sun.jnu.encoding=UTF-8, file.encoding.pkg=sun.io, file.separator=/, java.specification.name=Java Platform API Specification, com.ibm.packed.version=2, java.class.version=52.0, user.country=US, java.home=/opt/ibm/ibm-java-sdk-8.0-4.5/jre, java.vm.info=JRE 1.8.0 Linux amd64-64 Compressed References 20170419_344392 (JIT enabled, AOT enabled)
 J9VM - R28_20170419_1004_B344392
 JIT - tr.r14.java_20170419_344392
 GC - R28_20170419_1004_B344392_CMPRSS
 J9CL - 20170419_344392, os.version=3.10.0-327.10.1.el7.x86_64, java.awt.fonts=, }
 [jcc] Dumping all file properties: { }
 [jcc] END TRACE_DRIVER_CONFIGURATION
 [jcc] BEGIN TRACE_CONNECTS
 [jcc] Attempting connection to :50443/database
 [jcc] Using properties: { maxStatements=0, currentPackagePath=null, currentLockTimeout=-2147483647, timerLevelForQueryTimeOut=0, optimizationProfileToFlush=null, timeFormat=1, monitorPort=0, sendCharInputsUTF8=0, LOCKSSFU=null, alternateGroupDatabaseName=null, extendedTableInfo=0, sendDataAsIs=false, stripTrailingZerosForDecimalNumbers=0, diagLevelExceptionCode=0, returnAlias=1, supportsAsynchronousXARollback=2, sessionTimeZone=null, pkList=null, atomicMultiRowInsert=0, traceFileCount=2, DEBUG=null, IFX_UPDDESC=1, traceDirectory=null, maxRowsetSize=32767, driverType=4, extendedDiagnosticLevel=240, accountingInterval=null, monitoredDataSourceName=null, concurrentAccessResolution=0, LKNOTIFY=yes, clientProgramName=null, enableAlternateGroupSeamlessACR=false, connectNode=-1, traceFileSize=1048576, progressiveStreaming=0, profileName=null, DBMAXPROC=null, // }
 [jcc] END TRACE_CONNECTS
 [jcc][am] [time:2017-07-14-01:48:18.972][Thread:main][tracepoint:100]Connection com.ibm.db2.jcc.t4.b@980bc6a1 start time: 1499996898972
 [jcc][am] [time:2017-07-14-01:48:18.974][Thread:main][tracepoint:101]securityMechanism applied on connection object=3
 [jcc][t4] [time:2017-07-14-01:48:19.016][Thread:main][tracepoint:111]Connection isClosed: true. getApplicableTimeout (false) returning: 0
 [jcc][t4] [time:2017-07-14-01:48:19.016][Thread:main][tracepoint:111]Connection isClosed: true. getApplicableTimeout (true) returning: 0
 [jcc][t4] [time:2017-07-14-01:48:19.016][Thread:main][tracepoint:316]creating a socket to 192.168.1.24 at 50443
 [jcc][t4] [time:2017-07-14-01:48:20.529][Thread:main][tracepoint:100]OpenSSLAction creating socket with tcipTimeout: 0 and so_timeout: 0
 [jcc][t4] [time:2017-07-14-01:48:20.538][Thread:main][tracepoint:320]acrossAlternateGroup_=false
 [jcc][t4][time:2017-07-14-01:48:20.541][Thread:main][tracepoint:1][Request.flush]
 [jcc][t4] SEND BUFFER: EXCSAT (ASCII) (EBCDIC)
 [jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D 
<REMOVED>
 [jcc][t4]
 [jcc][t4] [time:2017-07-14-01:48:20.596][Thread:main][tracepoint:101]Request flushed.
 [jcc][t4] [time:2017-07-14-01:48:20.596][Thread:main][tracepoint:111]Connection isClosed: true. getApplicableTimeout (true) returning: 0
 [jcc][t4] [time:2017-07-14-01:48:20.596][Thread:main][tracepoint:102]Reply to be filled.

...
 [jcc][t4]
 [jcc][ResultSetMetaData@7f399260] BEGIN TRACE_RESULT_SET_META_DATA
 [jcc][ResultSetMetaData@7f399260] Result set meta data for statement Statement@89b322dd
 [jcc][ResultSetMetaData@7f399260] Number of result set columns: 1
 isDescribed=true[jcc][ResultSetMetaData@7f399260] Column 1: { label=1, name=1, type name=INTEGER, type=4, nullable=0, precision=10, scale=0, schema name=, table name=, writable=false, sqlPrecision=0, sqlScale=0, sqlLength=4, sqlType=496, sqlCcsid=0, sqlArrExtent=0, sqlName=1, sqlLabel=null, sqlUnnamed=1, sqlComment=null, sqludtxType=, sqludtRdb=, sqludtSchema=, sqludtName=, sqlxKeymem=0, sqlxGenerated=0, sqlxParmmode=0, sqlxOptlck=0, sqlxCorname=null, sqlxName=null, sqlxBasename=null, sqlxUpdatable=0, sqlxSchema=null, sqlxRdbnam=DATABASE, internal type=4, is locator parameter=false }
 [jcc][ResultSetMetaData@7f399260]{ sqldHold=1, sqldReturn=0, sqldScroll=0, sqldSensitive=0, sqldFcode=85, sqldKeytype=0, sqldRdbnam=, sqldSchema=null }
 [jcc][ResultSetMetaData@7f399260] END TRACE_RESULT_SET_META_DATA
 [jcc][Time:2017-07-14-01:48:20.685][Thread:main][PreparedStatement@89b322dd]executeQuery () returned com.ibm.db2.jcc.t4.h@5e36998
 [jcc][Thread:main][SystemMonitor:stop] core: 24.900053999999997ms | network: 5.893339999999999ms | server: 1.162ms [STMT@-1984748835]
 [jcc][SystemMonitor:start]
 [jcc][Time:2017-07-14-01:48:20.685][Thread:main][ResultSet@5e36998]close () called
 [jcc][Time:2017-07-14-01:48:20.686][Thread:main][ResultSet@5e36998]closeX (null, com.ibm.db2.jcc.t4.b@980bc6a1) called
 [jcc][t4][time:2017-07-14-01:48:20.686][Thread:main][tracepoint:1][Request.flush]
 [jcc][t4] SEND BUFFER: RDBCMM (ASCII) (EBCDIC)
 [jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF
 [jcc][t4]
 [jcc][Connection@980bc6a1] DB2 LUWID: 192.168.0.64.53338.170714014901.0004
 [jcc][Time:2017-07-14-01:48:20.935][Thread:main][Connection@980bc6a1]commit () returned null
 [jcc][Thread:main][SystemMonitor:stop] core: 2.103266ms | network: 1.58358ms | server: 0.013000000000000001ms
 [jcc][Time:2017-07-14-01:48:20.935][Thread:main][Connection@980bc6a1]close () called
 [jcc][Connection@980bc6a1] DB2 LUWID: 192.168.0.64.53338.170714014901.0005
 [jcc][t4] [time:2017-07-14-01:48:20.935][Thread:main][tracepoint:202] closing non-pooled Transport

Links

Solution: Remove Duplicates in Db2 Columnar Format

I had dupe data in my OLAP table, where the columnar data can be duplicated based on event id. (I loaded data 2x). I had to differentiate the data and remove the duplicates, so I assigned row_numbers over a partition ordered by.

I hope this helps you.

db2 "update (select OME.*, row_number() 
    over(partition by IDN_EVENT_ID order by IDN_EVENT_ID) as rnk 
    from X.OLAP OME) set APP_NM = rnk"

Then I removed using this. db2 "DELETE X.OLAP OME WHERE APP_NM = 2"

I recommend the two-phase, as you can in theory run this en batch, or async, and double check, versus hope it works.

Monitor the SQL Statements

The sql statements are running from custom code you always wonder what gets executed…. wait no more…

db2 connect to mydb
db2 " CREATE EVENT MONITOR DEBUG FOR STATEMENTS WRITE TO TABLE"

Turn on the event monitor db2 SET EVENT MONITOR DEBUG STATE 1

Run Traffic

Verify the output db2 "SELECT * FROM DB2INST1.STMT_DEBUG" | grep -i SELECT

Debug…

428 1 172.17.0.1.34184.201115215327 1 SYSLVL01 NULLID SQL_CURSN200C4 0 
0 0 0 0 SYSSN200 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 00002 0 163 136 SQLCA 
0 0 0 0 0 0 0 SQL11054 00000 2020-11-15-21.53.40.407043 0 4 SELECT 
R.RESOURCE_ID, R.LOGICAL_RESOURCE_ID, R.VERSION_ID, R.LAST_UPDATED, 
R.IS_DELETED, R.DATA, LR.LOGICAL_ID FROM Patient_RESOURCES R, 
Patient_LOGICAL_RESOURCES LR WHERE LR.LOGICAL_ID = ? AND R.RESOURCE_ID 
= LR.CURRENT_RESOURCE_ID``