Infrastructure (Server, Network, Database)

cancel
Showing results for 
Search instead for 
Did you mean: 

db Agent throws SqlSyntaxErrorException

New Poster

db Agent throws SqlSyntaxErrorException

Hello,

 

I'm trying to run a db agent on my machine. It is not on the machine hosting the db (db2). It also gets the connection to the db, because the authentification is working. I tried some wrong username/password combinations and it threw a no permission exception. So it should work.

 

The whole error message looks like that:

 

ERROR [<dbserverVM1>-Scheduler-2] ADBCollector:160 - Error collecting data for database 'dbserverVM1'
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-444, SQLSTATE=42724, SQLERRMC=*NNECTION;MON_GET_CONNECTION;...brouttrusted;monGetConnectionv101fp4;6, DRIVER=4.16.53

 

Can it be that the auto generated sql statement is wrong?

 

-kind regards

Tags (1)
db Agent throws SqlSyntaxErrorException
8 REPLIES 8
Highlighted
AppDynamics Team

Re: db Agent throws SqlSyntaxErrorException

siegfried, good investigative work plugging in an incorrect password to validate the connection. Going by that sounds evidence, it seems that some of the Stored Procs that AppD DataBase Monitoring depends on (for collecting usage data) maybe failing or has references that this user doesn't have access to. I'd check to make sure that the database user you are using has all the requisite privileges / roles assigned for Database Monitoring to function. see https://docs.appdynamics.com/display/PRO42/IBM+DB2+LUW+Database+Permissions for some dtls.. Would be curious to know if you already had taken care of these pre-reqs.. and if you are still seeing this error when configuring the collector

New Poster

Re: db Agent throws SqlSyntaxErrorException

Thank you for your reply.

I gave the permissions to the user "root" with the given commands, because it's the user starting the agent. vm100 is my db host and vm200 is my agent host (is root still the DBMon_Agent_User?).

But I got an error on the last command:

 

$ db2 grant execute on function SYSPROC.MON_GET_DATABASE to DBAgent_User


DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "SYSPROC.MON_GET_DATABASE" is an undefined name.  SQLSTATE=42704

 

Every other command was successfully.

 

Btw. the db2 version is DB2/LINUXX8664 10.1.3

 

Still the same error.

 

Kind regards

AppDynamics Team

Re: db Agent throws SqlSyntaxErrorException

siegfried, sorry for the late response. is it possible that you(the DBAgent_User) doen't have EXECUTE permissions on MON_GET_DATABASE? That DB2 provided (built-in) routine should work in 10.1.x as well, as far as I can tell. 

See http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0060769.ht...

 

To confirm this is only a Permissions / Privilege issue and that the routine itself does exist, you can run the following (as DB2 admin)

select funcname from syscat.functions where funcschema='SYSPROC'

 

If that lists MON_GET_DATABASE, then we can conclude that the missing permissions for your user is the likely cause of the failure.. Hope that helps

New Poster

Re: db Agent throws SqlSyntaxErrorException

Hello Hari,

 

thank you for your response. Sadly the command you send me didn't list MON_GET_DATABASE. This function is not existing. Is it possible to add this afterwards?

 

Best regards

 

//EDIT: It's working now...I feel so stupid. I used the standart command to start the agent: java -jar <direction>/db-agent.jar

 

With the command java -Ddbagent.name='Scarborough Network Database Agent' -XX:+HeapDumpOnOutOfMemoryError -XX:OnOutOfMemoryError="kill -9 %p" -jar <db_agent_home>/db-agent.jar from the guide solved the problem.

 

//EDIT2: Ok, it's still not working, same issue. I just forgot to change in my db-collector settings the agent name I set with the parameter. Quick update though, I activated the machine monitoring and I get CPU an memory usage.

 

//EDIT3: Quick update. After some testing I found out, that the routine MON_GET_CONNECTION is somehow not found in my db but it exists. I have to clarify what went wrong there. So I created a new database and tested this routine and it worked. But after starting the agent on this new db I get another exception:

 

ERROR [<dbVM>-Scheduler-1] ADBCollector:160 - Error collecting data for database 'dbVM'

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MON_GET_DATABASE;FUNCTION, DRIVER=4.16.53

 

As you can see, the agent tries to use the not existing routine MON_GET_DATABASE. This would be available at db2 version 10.5, but I'm using 10.1.

It seems the agent asumes my db2 has the version 10.5.

Re: db Agent throws SqlSyntaxErrorException

Hi,

 

We are having same problem and I haven't been able to solve it. Have you solved your problem already?

 

I've already tried to update to latest DB agent to no avail.

 

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-444, SQLSTATE=42724, SQLERRMC=*NNECTION;MON_GET_CONNECTION;...brouttrusted;monGetConnectionv101fp5;6, DRIVER=4.16.53

 

What's odd to me is that the DB2 is version 10.1.0.2 (i.e. 10.1fp2) but in the error string there is reference to 'fp5'. Could it be that the agent misinterprets the database version and therefore somehow fails? I saw that in your original error there was also different version of Fix Pack in the error string to what you say the database version is.

New Poster

Re: db Agent throws SqlSyntaxErrorException

Hey,

 

we contacted the support directly and they gave us a fixed but unofficial version of the agent. We didn't have time to test it yet, though, because we decided not to use the DB agent for now. They also stated that they could not test it by themselfes, because they have no DB2 10.1.

 

The support ticket is Req Nr. 76088, if you would like to contact the support regarding this issue (or if some official is reading this). I don't know if I'm allowed to distribute this version or not, so I decide to not.

 

Sorry ithat I can't help you more than this.

 

Kind regards

Re: db Agent throws SqlSyntaxErrorException

Thanks, Siegfried.

 

I have now opened a ticket with AppDynamics for our issue.

 

I'm thinking it might be because the database has two MON_GET_CONNECTION functions - one under SYSPROC and one used for some other monitoring tool under a different schema. In the error message the reference is onlyt to 'MON_GET_CONNECTION' and not to 'SYSPROC.MON_GET_CONNECTION'. Of course, I can't be sure because I don't know if the schema is defined somewhere earlier in the query.

 

-Antti

Re: db Agent throws SqlSyntaxErrorException

Hi,

 

In our case the problem was that the DB2 instance was on level v10.1 FP2 but the database was restored from a full backup of v10.1 FP5 so the SYSPROCs were from higher version.

 

-Antti