B - Production Support » Maybank Support » Maybank BII M2U-RIB Support

Problem:
Below hibernate HQL query could not be executed in production:

getSession().beginTransaction();
String hql="from IbBancnetList where status=:status and deleted=:deleted and bankCode in (:bankCode)";
bancNets = getSession ().createQuery (hql)
.setLong("status", 0)
.setLong("deleted", 1)
.setParameterList("bankCode", bankCodes).list(); //CODE DIES HERE

[11/30/15 9:24:02:437 WIT] 000000bf SystemErr R org.hibernate.exception.DataException: could not execute query
Caused by: com.ibm.db2.jcc.am.SqlDataException: The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-
302, SQLSTATE=22001, DRIVER=3.63.123

Solution:
- Unable to replicate the issue in local. Tried to insert the bankCodes with character longer than column length (16), but it works in local.
- Tried by setting up jndi in UAT server, however still unable to replicate the issue (same as above).
- It seems there are issue in hibernate query vs. db2. However, I got this recommendation from hibernate forum: https://forum.hibernate.org/viewtopic.php?f=1&t=993890 that suggests below recommendation (no.3)

Workaround solution:
- For this particular user, the bank code can be truncated to 16. The favourite data: "swift code:citidjx" is anyway not a valid data,
- increase column length:
ALTER TABLE DBO.IB_BANCNET_LIST ALTER COLUMN BANK_CODE SET DATA TYPE VARCHAR;
CALL SYSPROC.ADMIN_CMD( 'REORG TABLE DBO.IB_BANCNET_LIST' );
CALL SYSPROC.ADMIN_CMD( 'RUNSTATS ON TABLE DBO.IB_BANCNET_LIST' );
- Do a code change: change ".setParameterList("bankCode", bankCodes).list();" to "crit.add(Restrictions.eq"