Operation Console issue - ODBQueryApp failed to run.

[BharathiR]

Hi, looking for a expertise help in Operation Console setup IIS8.7 in AIX platform. We have done the setup as per the procedure provided by Concierge program team, Created ops database using the scripts regenerated after applying ops console rollup patch.

Starting the services is returning as below.

./DSAppWatcher.sh -start
AppWatcher:STARTED
EngMonApp:STARTING
ODBQueryApp:STARTING

./DSAppWatcher.sh -status
AppWatcher:RUNNING
EngMonApp:RUNNING
ODBQueryApp:STOPPED
ResMonApp:RUNNING


The logs with respect to the above issue

2012-07-27 00:31:57,930 DEBUG com.ibm.datastage.runtime.odbqapp.server.ODBQueryAppSocketServer.main(ODBQueryAppSocketServer.java:182) - main method is invok
ed.
2012-07-27 00:31:58,258 INFO com.ibm.datastage.runtime.odbqapp.server.ODBQueryAppSocketServer.main(ODBQueryAppSocketServer.java:218) - ODBQueryApp server i
s listening on port :13451
2012-07-27 00:31:58,739 ERROR com.ibm.datastage.runtime.odbqapp.server.ODBQueryAppSocketServer.main(ODBQueryAppSocketServer.java:261) - Error checking avail
ability of database operation functions.
com.ibm.db2.jcc.am.xo: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=RUNMAJORSTATUS;FUNCTION, DRIVER=3.58.135
at com.ibm.db2.jcc.am.ed.a(ed.java:676)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.am.zm.c(zm.java:2524)
at com.ibm.db2.jcc.am.zm.d(zm.java:2512)
at com.ibm.db2.jcc.am.zm.a(zm.java:1992)
at com.ibm.db2.jcc.t4.fb.g(fb.java:140)
at com.ibm.db2.jcc.t4.fb.a(fb.java:40)
at com.ibm.db2.jcc.t4.t.a(t.java:32)
at com.ibm.db2.jcc.t4.ub.i(ub.java:135)
at com.ibm.db2.jcc.am.zm.fb(zm.java:1963)
at com.ibm.db2.jcc.am.zm.a(zm.java:2983)
at com.ibm.db2.jcc.am.zm.a(zm.java:652)
at com.ibm.db2.jcc.am.zm.executeQuery(zm.java:636)
at com.ibm.datastage.runtime.odbqapp.server.ODBQueryAppSocketServer.main(ODBQueryAppSocketServer.java:247)
2012-07-27 00:31:58,741 ERROR com.ibm.datastage.runtime.odbqapp.server.ODBQueryAppSocketServer.main(ODBQueryAppSocketServer.java:262) - Unable to verify that the operations database functions have been installed into the database.
Please check that you have run all the required manual post installation scripts following any update such as a patch or feature pack installation.


Any help would be highly appreciated. Thanks in advance

[ray.wurlod]

Did you follow the steps for setting up the ODB in exacting detail? It's absolutely vital that this be done correctly.

During beta testing I needed three attempts to get it right.

Once it is set up properly it's a great tool. I think they need to simplify its setup but, if you think about what it's doing, there is necessary complexity in there.

[BharathiR]

Solved the problem by creating the Operation Database in DB2 Version 9.7

Earlier we used DB2 9.5 FP7 and there seems some bug.

UDF queries from Application is defined without casting the data-types which verifies the operations database functions. It seems we need to Cast the data-type in UDF Queries.

Example:


In DB2 9.5

Without Casting the datatype
----------------------------------

$db2 "SELECT DSODB.RunMajorStatus('FIN') FROM SYSIBM.SYSDUMMY1"
SQL0440N No authorized routine named "RUNMAJORSTATUS" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884

By giving the datatype
---------------------------

$ db2 "SELECT DSODB.RunMajorStatus(CHAR('FIN')) FROM SYSIBM.SYSDUMMY1"

1
-----------
2

1 record(s) selected.

Whereas the above two UDF queries works in DB2 9.7

Ref the below link on UDFs and Datatypes

http://catterallconsulting.blogspot.in/ ... fined.html

[ray.wurlod]

I guess that's why they specify version 9.7 of DB2 for the DSODB.