Paul Bastide

Tag: db2

  • Upper Limit for PreparedStatement Parameters

    Thanks to Lup Peng PostgreSQL JDBC Driver – Upper Limit on Parameters in PreparedStatement I was able to diagnose an upper limit:

    Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 54838
	at org.postgresql.core.PGStream.sendInteger2(PGStream.java:349)
	at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1546)
	at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1871)
	at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1432)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:314)
	... 96 more

    I had 54K parameters on my query. It turns out due to public void sendInteger2(int val) throws IOException PGStream.java has a maximum number of Short.MAX_VALUE – 32767

    Net for others hitting the same issue in different RDBMS systems:

    1. Postgres – 32767 Parameters
    2. IBM Db2 Limit – Maximum number of host variable references in a dynamic SQL statement -> 32,767 32,767 Parameters and 2,097,152 length of the text in the generated sql. Limits
    3. Derby – Storage capacity is the limit https://db.apache.org/derby/docs/10.14/ref/refderby.pdf

  • IBM FHIR Server – Debug Drop Tablespace

    I’ve been debugging a drop tablespace issue on Db2 – IBM/FHIR: 2354. The core issue was a timing problem with lots of partitions dettaching. This adds a delay to the dropTablespace so the async operation can complete, and cleanly exit with a specific error code so downstream consumers can work around the issue.

    When debugging why a partition hasn’t dettached, I found that it’s worth checking the details when a drop tablespace fails:

    [db2inst1@53fe3a4d3ad2 ~]$ db2 list utilities show detail

ID                               = 18435
Type                             = ASYNCHRONOUS PARTITION DETACH
Database Name                    = FHIRDB
Member Number                    = 0
Description                      = Finalize the detach for partition '3' of table 'FHIRDATA.PARAMETER_NAMES'
Start Time                       = 06/08/2021 16:31:05.526513
State                            = Executing
Invocation Type                  = Automatic
Progress Monitoring:
      Description                = Performing detach operation and
 making the target table available; new compilations blocked
      Start Time                 = 06/08/2021 16:31:10.836936

    I’ve attached a useful partition.sql to demo partitions and check the system catalog.

    Links

    Example Code

  • Setting up Db2 JDBC Access

    I have been working IBM Integration Broker 10.0.0.4. I found it hard to find concise documentation on setting up/creating a new provider

    mqsicreateconfigurableservice TESTNODE_cheetah -c JDBCProviders \
-o DB2Two -n connectionUrlFormat -v \
"jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];"

mqsisetdbparms TESTNODE_cheetah -n jdbc::employeeIdentity -u db2admin -p passw0rd1940!

mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n securityIdentity -v employeeIdentity
 mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n portNumber -v 50000
 mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n serverName -v 9.32.18.166
 mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n databaseType -v DB2
 mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n databaseName -v SAMPLE
 mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n jarsURL -v c:\db2jars

mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n type4DatasourceClassName -v com.ibm.db2.jcc.DB2XADataSource
 mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n type4DriverClassName -v com.ibm.db2.jcc.DB2Driver

mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n databaseVersion -v 10.1
 mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n environmentParms -v ""

mqsichangeproperties TESTNODE_cheetah -c JDBCProviders -o DB2Two -n jdbcProviderXASupport -v true

    Restart the Integration Server

    Links