Topic
  • 5 replies
  • Latest Post - ‏2012-12-11T08:02:56Z by SystemAdmin
kjdsfoa
kjdsfoa
2 Posts

Pinned topic History reporting with TBSM 4.2.1

‏2009-10-05T10:40:00Z |
I installed TBSM 4.2.1 with Historical reporting option.
I also use ITM 6.2.2 and DB2 v9.5.
Everything work well, but when I try to look at TBSM report I see the following error:

ReportDesign (id = 1):
  • Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: "db2inst1.KR9_TBSM_SERVICE_STATUS" is an undefined name.
SQL error #2: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "db2inst1.KR9_TBSM_SERVICE_STATUS".
SQL error #3: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "db2inst1.KR9_TBSM_SERVICE_STATUS".

Cannot get the result set metadata.SQL statement does not return a ResultSet object.SQL error #1: "db2inst1.KR9_TBSM_SERVICE_STATUS" is an undefined name.SQL error #2: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "db2inst1.KR9_TBSM_SERVICE_STATUS".SQL error #3: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "db2inst1.KR9_TBSM_SERVICE_STATUS". ( 1 time(s) )
detail : org.eclipse.birt.report.data.adapter.api.AdapterException: Cannot get the result set metadata.SQL statement does not return a ResultSet object.SQL error #1: "db2inst1.KR9_TBSM_SERVICE_STATUS" is an undefined name.SQL error #2: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "db2inst1.KR9_TBSM_SERVICE_STATUS".SQL error #3: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "db2inst1.KR9_TBSM_SERVICE_STATUS". at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:450) at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:151) at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:117) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:182) at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1683) at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:76) at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:61) at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:42) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:45) at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:33) at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:63) at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:27) at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:133) at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:95) at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutNodes(HTMLInlineStackingLM.java:135) at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:27) at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:133) at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:68) at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:27) at org.eclipse.birt.report.engine.layout.html.HTMLTableLM.layoutChildren(HTMLTableLM.java:76) at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:133) at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:68) at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:85) at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:106) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:140) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:68) at com.ibm.tivoli.reporting.reportEngine.impl.BIRTReportEngineRunRenderHandler.runAndRenderReport(BIRTReportEngineRunRenderHandler.java:1219) at com.ibm.tivoli.reporting.reportEngine.impl.BIRTReportEngine.runAndRenderReport(BIRTReportEngine.java:1001) at com.ibm.tivoli.reporting.common.runAndRender.RunAndRenderServices.runAndrenderReport(RunAndRenderServices.java:920) at com.ibm.tivoli.reporting.ejb.TivoliReportingEJBBean.runAndrenderReport(TivoliReportingEJBBean.java:5046) at com.ibm.tivoli.reporting.ejb.EJSLocalStatelessTivoliReportingEJB_f305bd50.runAndrenderReport(Unknown Source) at com.ibm.tivoli.reporting.reportViewer.ReportViewer.processRequest(ReportViewer.java:590) at com.ibm.tivoli.reporting.reportViewer.ReportViewer.doGet(ReportViewer.java:227) at javax.servlet.http.HttpServlet.service(HttpServlet.java:743) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1102) at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:570) at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478) at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:90) at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:748) at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1466) at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:119) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:458) at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:387) at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:102) at com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1818) at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:556) at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:606) at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:979) at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1064) at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1473)

I'm shure that db2inst1.KR9_TBSM_SERVICE_STATUS exists and it's not empty.

my tbsm.rptlibrary has the following source
<data-sources>
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="ITM Warehouse" id="4">
<property name="comments">comments</property>
<text-property name="displayName">ITM Warehouse</text-property>
<property name="odaDriverClass">com.ibm.db2.jcc.DB2Driver</property>
<property name="odaURL">jdbc:db2://nebula.com:50000/WAREHOUS:currentSchema=DB2INST1;</property>
<property name="odaUser">DB2INST1</property>
<encrypted-property name="odaPassword" encryptionID="base64">bmV0Y29vbA==</encrypted-property>
<property name="odaJndiName">jdbc/ibm/tivoli/tbsm/v4</property>
</oda-data-source>
<script-data-source name="ReportPeriodChoicesDS" id="1620"/>
</data-sources>

I also coppied db2jcc.jar and db2jcc_license_cu.jar drivers to /opt/IBM/tivoli/tip/products/tcr/lib/birt-runtime-2_2_1/ReportEngine/plugins/org.eclipse.birt.report.data.oda.jdbc_2.2.1.r22x_v20070919/drivers

Guys, help to troubleshut it.
Thanks in anvance.
Updated on 2012-12-11T08:02:56Z at 2012-12-11T08:02:56Z by SystemAdmin
  • kjdsfoa
    kjdsfoa
    2 Posts

    Re: History reporting with TBSM 4.2.1

    ‏2009-10-06T19:11:24Z  
    JNDI was incorrect
  • Kovax
    Kovax
    2 Posts

    Re: History reporting with TBSM 4.2.1

    ‏2009-10-21T09:58:12Z  
    • kjdsfoa
    • ‏2009-10-06T19:11:24Z
    JNDI was incorrect
    Could you please describe your solution? We have the same issue.
    Thanks in advance!
  • SystemAdmin
    SystemAdmin
    373 Posts

    Re: History reporting with TBSM 4.2.1

    ‏2009-10-21T13:43:16Z  
    • Kovax
    • ‏2009-10-21T09:58:12Z
    Could you please describe your solution? We have the same issue.
    Thanks in advance!
    TBSM reports work with JNDI. With JNDI, the the report only contains a JNDI name. That JNDI name is then defined in TBSM/TCR's embedded WebSphere with the associated user name, pwd, schema (this is case sensitive), jdbc driver location, db server, db port, etc.

    The prior user had an error in one of these settings and corrected it to get it to work.

    When you make a change to the JNDI setting, you have to restart the server to get the change picked up by the report.
    The TBSM script used to delete the connection, recreate the connection, or change the credentials:

    You may use a script on the TBSM install media to set/reset the database connection parameters (logs will probably have specific message from DB2 such as invalid password etc). The script is $IMAGE/TBSM/COI/DeploymentSteps/TBSMHistRpt_Install/FILES/setupTBSMDatasource.jy
    can be used to create, update the userid/password, or delete the JDBC connection. Run
    $TIP_HOME/profiles/TIPProfile/bin/wsadmin.sh -lang jython -username $TIPUSER -password $TIPPASS \
    -f $IMAGE/TBSM/COI/DeploymentSteps/TBSMHistRpt_Install/FILES/setupTBSMDatasource.jy
    to see the options. The TBSM dashboard server must be running for this script to work.

    Change the userid or password: With the dashboard server running, run the following as a single command:
    $TIP_HOME/profiles/TIPProfile/bin/wsadmin.sh -lang jython -username $TIPUSER -password $TIPPASS \
    -f $IMAGE/TBSM/COI/DeploymentSteps/TBSMHistRpt_Install/FILES/setupTBSMDatasource.jy \
    -updateAlias <userid> <password>
    Where:
    • The value of the variable $TIP_HOME is <install_dir>/tip.
    • The variables $TIPUSER and $TIPPASS refer to the userid and password given during
    installation on the "TIP Information" panel.
    • $IMAGE is the root of the TBSM install media
    • <userid> and <password> are the userid and password to use to access TDW database

    Configure the JDBC connection to TDW DB2 database. NOTE: you must run the script with the -delete option first if you are trying to change setttings on an existing connection. All of this is a single command:
    $TIP_HOME/profiles/TIPProfile/bin/wsadmin.sh -lang jython -username $TIPUSER -password $TIPPASS \
    -f $IMAGE/TBSM/COI/DeploymentSteps/TBSMHistRpt_Install/FILES/setupTBSMDatasource.jy \
    -createDB2 itmuser itmpass db2server.example.com WAREHOUS $TIP_HOME/universalDriver/lib 50000 ITMUSER

    In above,
    • itmuser is userid to access DB2
    • itmpass is password to access DB2
    • db2server.example.com is host name of DB2 server
    • WAREHOUS is TDW database name
    • $TIP_HOME/universalDriver/lib is path to db2jcc.jar and db2jcc_license_cu.jar
    • 50000 is port used by TDW DB2 instance on server
    • ITMUSER is schema name for TDW tables

    • The value of the variable $TIP_HOME is <install_dir>/tip.
    • The variables $TIPUSER and $TIPPASS refer to the userid and password given during
    installation on the "TIP Information" panel.
    • $IMAGE is the root of the TBSM install media
  • Kovax
    Kovax
    2 Posts

    Re: History reporting with TBSM 4.2.1

    ‏2009-10-21T15:26:42Z  
    • Kovax
    • ‏2009-10-21T09:58:12Z
    Could you please describe your solution? We have the same issue.
    Thanks in advance!
    We have found the proper settings:

    Display Name: ITM Warehouse
    JDBC Driver: com.ibm.db2.jcc.DB2Driver
    User ID: <db2 user ID>
    JDBC URL: jdbc:db2://<hostname>:<portnumber>/<databasename> (without schema name and semicolon!)
    Password: <db2 password>
    JNDI Name: java:comp/env/jdbc/ibm/tivoli/tbsm/v4
  • SystemAdmin
    SystemAdmin
    373 Posts

    Re: History reporting with TBSM 4.2.1

    ‏2012-12-11T08:02:56Z  
    • Kovax
    • ‏2009-10-21T15:26:42Z
    We have found the proper settings:

    Display Name: ITM Warehouse
    JDBC Driver: com.ibm.db2.jcc.DB2Driver
    User ID: <db2 user ID>
    JDBC URL: jdbc:db2://<hostname>:<portnumber>/<databasename> (without schema name and semicolon!)
    Password: <db2 password>
    JNDI Name: java:comp/env/jdbc/ibm/tivoli/tbsm/v4
    Hi,

    Could you please provide help in troubleshooting as I am facing same issue on TBSM 6.1

    And I am unable to find setupTBSMDatasource.jy in TBSM install media.

    Regards,
    Avinash