VIM – JOIN Conditions with Unicode and ASCII

JOIN Conditions with Unicode and ASCII

I cannot stress the dangers of copying data from Excel or HTML and assuming that it’s ASCII. For example U+0040 is the unicode version of @. We ingested the unicode version and couldn’t see why a JOIN condition on the data table wasn’t working.

I looked at the source JSON ( a FHIR DSTU2 Group ) and loaded in VIM and used the following trick:

set encoding=latin1

We ended up showing that our data table’s contents were different using:

SELECT HEX(RESOURCE_VALUE) FROM FHIR.DIM_GROUP
0A40 vs 40

References

https://unix.stackexchange.com/questions/108020/can-vim-display-ascii-characters-only-and-treat-other-bytes-as-binary-data

BufferPool Error, Debug and Relevant Solution

My Java code kept hitting a buffer pool issue.

com.ibm.db2.jcc.am.SqlException: There are no pages currently available in bufferpool "4099".. SQLCODE=-1218, SQLSTATE=57011, DRIVER=4.19.49
at com.ibm.db2.jcc.am.kd.a(Unknown Source)
at com.ibm.db2.jcc.am.kd.a(Unknown Source)
at com.ibm.db2.jcc.am.kd.a(Unknown Source)
at com.ibm.db2.jcc.am.ResultSet.completeSqlca(Unknown Source)
at com.ibm.db2.jcc.am.ResultSet.earlyCloseComplete(Unknown Source)
at com.ibm.db2.jcc.t4.bb.a(Unknown Source)
at com.ibm.db2.jcc.t4.bb.n(Unknown Source)
at com.ibm.db2.jcc.t4.bb.j(Unknown Source)
at com.ibm.db2.jcc.t4.bb.d(Unknown Source)
at com.ibm.db2.jcc.t4.p.c(Unknown Source)
at com.ibm.db2.jcc.t4.vb.j(Unknown Source)
at com.ibm.db2.jcc.am.fp.mb(Unknown Source)
at com.ibm.db2.jcc.am.gp.b(Unknown Source)
at com.ibm.db2.jcc.am.gp.kc(Unknown Source)
at com.ibm.db2.jcc.t4.j.kc(Unknown Source)
at com.ibm.db2.jcc.am.gp.executeQuery(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:528)

I checked the db2diag file. The query was using the hidden bufferpools – Buffer Pool Documentation.  4099 is one of the hidden / default bufferpools.

2018-03-21-20.22.16.750579+000 E55613036E857 LEVEL: Warning
 PID : 23072 TID : 139860291610368 PROC : db2sysc 0
 INSTANCE: db2inst1 NODE : 000 DB : MYDB
 APPHDL : 0-9747 APPID: *LOCAL.db2inst1.180321202200
 AUTHID : DB2INST1 HOSTNAME: test.local
 EDUID : 158 EDUNAME: db2agent (MYDB) 0
 FUNCTION: DB2 UDB, buffer pool services, sqlbAssignBufferPool, probe:2
 MESSAGE : ADM6073W The table space "TBS_LDE" (ID "7") is configured to use
  buffer pool ID "2", but this buffer pool is not active at this time.
  In the interim the table space will use buffer pool ID "4099". The
  inactive buffer pool should become available at next database startup
  provided that the required memory is available.
2018-03-21-20.22.26.859427+000 E55613894E861 LEVEL: Warning
 PID : 23072 TID : 139860291610368 PROC : db2sysc 0
 INSTANCE: db2inst1 NODE : 000 DB : MYDB
 APPHDL : 0-9747 APPID: *LOCAL.db2inst1.180321202200
 AUTHID : DB2INST1 HOSTNAME: test.local
 EDUID : 158 EDUNAME: db2agent (MYDB) 0
 FUNCTION: DB2 UDB, buffer pool services, sqlbAssignBufferPool, probe:2
 MESSAGE : ADM6073W The table space "TBS_MY_IDX" (ID "8") is configured to use
  buffer pool ID "2", but this buffer pool is not active at this time.
  In the interim the table space will use buffer pool ID "4099". The
  inactive buffer pool should become available at next database startup
  provided that the required memory is available.

I ran a script to determine the status of the buffer pools, minimal hits in the bufferpools. IBMSYSTEMBP32K

 
A silly solution presented itself… restart the database instance, and the buffer pool was activated
 
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0052485.html
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022013.html
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005395.html
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0053942.html
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0052485.html