Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Highlighted
Trusted Contributor.. JuliaVII Trusted Contributor..
Trusted Contributor..
153 views

Time-based SmartConnectors handling last query date wrong

Hello there. We have a Time-based SmartConnector collecting specific registers from Oracle Database (one table and a lot of joins). The flow of registers is heavy, so we need the query to run each 30 seconds more or less so it does not consumes a lot of resource from the database and/or network with long running db sessions. 

Since we created the connector we found a critical problem: the connector does not uses the last query time. Instead it always run the query with the starting date of the connector. We did workaround this problem rebooting the conector every day for some time. 

However looks like updating the conector to the last version (not sure when it was did, i just found the evidences of update) the conector is sending a wrong date to the query, like two days prior. It kills the process as the query takes too long that database kills it. Even if it work, it will bring like thousands of events per minute which is useless. Heres an example from the log: 

INFO | jvm 1 | 2019/02/06 11:05:30 | [Wed Feb 06 11:05:30 BRST 2019] [INFO ] Query will start at date [Sat Feb 04 00:00:00 BRT 19] for [jdbc:oracle:thin:@db-bigdatal...]

I tried everything i could find in documentation, forum, self-support articles, stakeoverflow and etc. Neither support can find the problem. 

This is the things i tried: 

1- Query has "where DATA_FIELD > ?" 

2- timestamp.field setted to DATA_FIELD

3- using "lastdate.query=select max(DATA_FIELD) from db.bigdata" and also not using. 

4‌ -agents[0].preservestate=true (tried false too, just because why not) 

5- tried using to_date() in the querys

and more, that i do not remember now. 

We have diferent time-based connectors that work in another oracle database. 

Anyone had this problem? Anyone has any idea of what can i do? 

0 Likes
2 Replies
nikhiljoshi15
New Member.

Re: Time-based SmartConnectors handling last query date wrong

Hi Julia,

As i understand, you want to fetch the logs from database table. However if you want to extract only the appended data (or say changes) you need to have a timestamp field in the table. Try using this at the end of your query "WHERE update_time >= ?". What this will do is that it will fetch the data where update time is >= run time of the query. Also, you can set the frequency of the query while setting up the flex connector as per your requirement.

Thanks,

Nikhil

 

 

0 Likes
Trusted Contributor.. yakupisler Trusted Contributor..
Trusted Contributor..

Re: Time-based SmartConnectors handling last query date wrong

Hello Julia,

Can you add those lines to your agent.properties file?

agents[x].startatend=true

agents[x].preservestate=true

agents[x].startatdate=01/01/111 00:00:00

Once the connector works as it should, you can remove startatdate line and restart connector, by doing so, connector will remember the last date and previous events are not  going to be sent to logger, however; it is not mandatory.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.