Debugging top ten WMB problems with databases on

Download Debugging top ten WMB problems with databases on .PDF

8593733 Downloads

4.88 MB .pdf

ini file The final step is to deploy a message flow for XA. org Access key product show-me demos and tutorials by visiting IBM® Education Assistant: http://www. so /var/mqm/exits64/JDBCSwitch WebSphere® Support Technical Exchange 58 IBM Software Group JDBC and XA Configure the MessageFlow for XA coordination Open the toolkit In the BAR editor, add the message flow In the Configure tab, select Coordinated Transaction Points to remember: If the database has to be brought down, then ensure that broker is restarted as well On Windows if configured with DB2, then ensure that MQ and WMB Service user IDs are members of the group DB2USERS WebSphere® Support Technical Exchange 59 IBM Software Group Problem 10: Toolkit JDBC for maps Problem: When trying to map database objects on a mapping node in the Toolkit, my table directory is shown as empty. Correct either the database or the broker configuration. 0 WebSphere® Support Technical Exchange 44 IBM Software Group Oracle RAC – Typical configuration Broker Preferred Listener A Available Listener B Oracle RAC Nodes - Active/Passive Instance A Instance B Node A Node B - Same Service offered Shared Storage WebSphere® Support Technical Exchange 45 IBM Software Group Oracle RAC - Failover Oracle RAC provides two methods of failover: Connect Time Failover • The ability to connect to the database through alternative listeners • Does not preserve states for transactions or queries • Supported by Message Broker. The error has the following diagnostic information: SQL State '‘40001'' SQL Native Error Code ‘-911' SQL Error Text ''[IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout Reason code=2 '‘ Identify the database being used In this case DB2® Identify the Inserts SQLState:40001 Native Error Code: -911 Diagnostic Message: ''[IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout Reason code=2 WebSphere® Support Technical Exchange 14 IBM Software Group What do the inserts tell us? The description for ‘40001’ is: Code Description 40001 Serialization Failure WebSphere® Support Technical Exchange Applicable API Calls SQLBulkOperations SQLColumnPrivileges SQLColumns SQLEndTran SQLExecDirect SQLExecute SQLFetch SQLFetchScroll SQLForeignKeys SQLGetTypeInfo SQLMoreResults SQLParamData SQLPrimaryKeys SQLProcedureColumns SQLProcedures SQLSetPos SQLSpecialColumns SQLStatistics SQLTablePrivileges SQLTables 15 IBM Software Group What do the inserts tell us? The description for ‘-911’ is: The current unit of work was the victim in a deadlock, or experienced a timeout, and had to be rolled back. 0 IE02 SupportPac On Windows, all versions use the Microsoft driver manager From V7 onwards, the BIP2393 message logs which driver manager is being used: BIP2393E: Database error: ODBC return code '-1' from data source ''odbc::TEST1'' using ODBC driver manager ''libbipodbc. The broker needs to explicitly re-establish a new connection Transparent Application Failover (TAF) • If a communication link failure occurs after a connection has been established, the connection fails over to another active node. so WebSphere® Support Technical Exchange 41 IBM Software Group ODBC XA - Configuring the Broker DataSourceName stanza should be defined in the $ODBCINI file in the same way as for non-XA connections The DataSourceName should match the one given in the XAOpenString in the qm. DB2Driver' WebSphere® Support Technical Exchange 35 IBM Software Group Considerations Only available from V7. ini Example, for an Oracle datasource: XAResourceManager: Name=OracleXA SwitchFile=UKoradtc24. Use the following messages to determine the cause of the error. mqsichangeproperties V8BK -c JDBCProviders –o DB2_1 -n securityIdentity –v DB2DataSource1 WebSphere® Support Technical Exchange 54 IBM Software Group JDBC connectivity The connectionUrlFormat property should not be edited, but rest of the properties may need to be changed WebSphere® Support Technical Exchange 55 IBM Software Group JDBC connectivity Solution: Ensure the ConnectionURLFormat property itself is not touched All the parameters listed in ConnectionURLFormat are correctly configured in the configurable service securityIdentity is correctly configured Configure the node in message flow: Name of your JDBCProvider service must match the datasourceName parameter in the getJDBCType4Connection() call For example: mqsicreateconfigurableservice V8BK -c JDBCProviders -o DB2_1 Code the following in JCN: Connection DB2_Connection = this. You can grant the required access by using the following Oracle SQLPLUS command: • grant select on DBA_PENDING_TRANSACTIONS to userid; WebSphere® Support Technical Exchange 39 IBM Software Group ODBC XA - Configuring MQ Add an XAResourceManager stanza to qm. log WebSphere® Support Technical Exchange 19 IBM Software Group Resolution The maxlocks db config parameter determines when lock escalation is triggered Set too low this can cause escalation from row locks to table locks Increase this value on db WebSphere® Support Technical Exchange 20 IBM Software Group Problem 3: Character Conversion problem Unexpected character conversion causes either parse failures or data corruption Multiple codepage conversions can occur depending on scenario WebSphere® Support Technical Exchange 21 IBM Software Group UCS-2 “Statement Converter” Platform / db based default (Usually UTF-8 or UTF-16) Overridden with MQSI_ODBC_ASSOCIATE_CONVERTERS Message Tree – UCS-2 ODBC Layer UTF Driver Manager UTF ANSI (IANAAppCodePage) ANSI (IANAAppCodePage) Driver ANSI (NLS_LANG) Client (Oracle @ WMB 6. ini file references the stanza for the Data Source Name Check that the Data Source Name (spelling, capitalization) is consistent in the file Remove any extraneous whitespace (at end of lines) from the odbc. Add the message flow to a broker archive Select the Manage tab, and select the message flow. o) 13 IBM Software Group Problem 2: SQLState 40001 BIP2322E: Database error: SQL State '‘40001''; Native Error Code ‘-911'; Error Text ''[IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout Reason code=2''. html WebSphere® Support Technical Exchange 68 IBM Software Group Connect with us! 1. ini file (UNIXes) or QMgr properties in MQExplorer (Windows) and add the following stanza for each database: XAResourceManager: Name=Database_Name SwitchFile=JDBCSwitch XAOpenString=JDBC_DataSource ThreadOfControl=THREAD WebSphere® Support Technical Exchange (DSN defined on the configurable service) (fixed for UNIXes & Win64-bit, but fully qualified name for Win32-bit - \JDBCSwitch