Anonymous_User Absent Member.
Absent Member.
295 views

Database collector trouble


Hi,

I've made a custom db collector. When I run it I get a ORA-00923: FROM
keyword not found where expected error in the collection manager log.
The sql I use ing sqlquery.base is

SELECT
invoke_time, Date_started, date_ended, orderno, User_id, Report_id
FROM PRHR01_AGR01.ACRREPORD
WHERE invoke_time > TO_TIMESTAMP ('%s', 'RRRR-MM-DD HH24:MI:SS')
ORDER BY invoke_time

This sql works just fine in SQL Developer.

I also get alot of repeating row_left=-1. 30+ in a minute. Is this
normal.

--
Frode


--
fsjovatsen
------------------------------------------------------------------------
fsjovatsen's Profile: http://forums.novell.com/member.php?userid=9767
View this thread: http://forums.novell.com/showthread.php?t=451094

0 Likes
6 Replies
Anonymous_User Absent Member.
Absent Member.

Re: Database collector trouble

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Regarding the Oracle error, that does not seem right. Have you compared
your collector with a Novell-supplied Oracle DB collector to see how
they compare? I have not, but it may shed some light on things.

Regarding the 'row_left=-1' my guess is that this is printed whenever
the query to the DB comes back with nothing to do, or maybe as a side
effect of the error above with the missing/relocated FROM keyword, but
that's just a guess. I'd worry about that after being sure you get
events if it really causes some kind of trouble.

Good luck.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.15 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQIcBAEBAgAGBQJPGPNqAAoJEF+XTK08PnB5wPkQAMkGjiavvH4up1JthsUmJEMF
Z54vqSVUbfgx2ql/EuUrVI0zCA37WGZq4QzctdmmM7RJwo7dyc5tZxpoxNGt2xm+
vjnwGkt6Uqgt+Y0GKZT98hd1EMUQ7iM9a5nWfyLGbB2iIN6BNteYvGAQd94Gb6iF
cJU5or0pCSdWDw7Aimenwc4VLrVhiVnpPmJXMLenTuPG5JjHfsENDqUinzdtkgMb
3+FOrvK++XvE2lJ78g2bjOyKD63YWCPuMvybJQ0mwUPz1ro/2WIN+Dfa3SPnf4M3
KJpRnV0T3LyvX8f0yGxfOmWabgmOZJT1zPhXuGeWzr4CioSHBKkbCp6C+DW/15IB
AN8rAM7Gyk3qJEqVBGbXbV2IRF8NhHBdYG27BJjyPZQujeLBi6zrjeRDq3klmgAS
IAAQP038sEB4+XRW9JDER9NOJTS5vPN1q0ySfSTqBkyvT2aTzJp3kE5BEIYPCBlJ
xa7lJsYnOoZgcA3oA0DYB0nGLSYDS3sEWJ0UO5192Z53JuIogMNeotV/9oWE8VAK
uAf3A8JMdHYPf+5geV2xSRu9WoOPWCKXladBSc3aEo2tX41UIsCpf2chJQ2VGcM6
lvJsdhORvwG0eUjvYDYwcY/KCnZeH9SlenEtVtCN0S/fLFHhB++Q08y8by5oZz6E
EqL8096JAeEyla7+SghK
=MKL+
-----END PGP SIGNATURE-----
0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Database collector trouble


Hi Frode,

Yes, the Row_Left=-1 indicates that the query failed.

I don't see anything blatantly wrong with your query, but there are
lots of things that could cause this issue from a misplaced quote to a
funny non-printing character to... well, lots of things.

One thing you can do is to increase the logging level of the Database
Connector so that it reports the exact query that it's about to issue to
the Oracle database. Then you can verify that it's appearing exactly as
you expect. I forget exactly which file you edit and what property you
set to do that, but 'ab' should know and can hopefully respond.

Which leads me to my next thought, which has to do with pre-seeding the
offset. See, when you start the Collector for the very first time, the
'%s' will be replaced with whatever you have defined for your
'InitialStartOfData' attribute on the Connection Mode (in
connectionMethods.xml). If that's not set, it will probably come through
as '0' or garbage; that will probably cause the TO_TIMESTAMP call to
return garbage, which could be what's breaking your query. One way to
check this is to manually set the offset for your Event Source in ESM to
something like '1970-01-01 00:00:00' and then start your Collector.

For reference, our standard Oracle Collector sets InitialStartOfData
like this:

<ConnectionMode>
<InternalName>map</InternalName>
<DisplayName>Database:SQL Query</DisplayName>
<IsDefault>1</IsDefault>
<Properties>
<Property>
<Name>ProtocolVersion</Name>
<Value>600</Value>
</Property>
<Property>
<Name>DataFormat</Name>
<Value>map</Value>
</Property>
<Property>
<Name>InitialStartOfData</Name>
<Value>1970-01-01 00:00:00</Value>
</Property>
</Properties>
</ConnectionMode>

Then the query is defined as follows:

SELECT OS_USERNAME, USERNAME, USERHOST, TERMINAL,
TO_CHAR(EXTENDED_TIMESTAMP ,'YYYY/MM/DD HH24:MI:SS') AS
EXTENDEDTIME,
TO_CHAR(LOGOFF_TIME ,'YYYY/MM/DD HH24:MI:SS') AS LFTIME,
TO_CHAR(TIMESTAMP ,'YYYY/MM/DD HH24:MI:SS') TIMESTAMP,
ACTION, ACTION_NAME, SESSIONID, ENTRYID, STATEMENTID, RETURNCODE,
OWNER, OBJ_NAME , GLOBAL_UID, SCN, GRANTEE, SQL_BIND, SQL_TEXT,
(select MACHINE from v$session where schemaname='SYS' and rownum=1)
as DHN,
(select * from v$version where BANNER LIKE 'Oracle%') AS DB_VERSION
FROM SYS.DBA_AUDIT_TRAIL
WHERE TIMESTAMP >= TO_TIMESTAMP ('%s', 'YYYY/MM/DD HH24:MI:SS.FF6') and
rownum <= %d
ORDER BY TIMESTAMP

I also don't know what impact using RRRR instead of YYYY is, maybe try
that instead.


--
DCorlette
------------------------------------------------------------------------
DCorlette's Profile: http://forums.novell.com/member.php?userid=4437
View this thread: http://forums.novell.com/showthread.php?t=451094

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Database collector trouble


Hi,

I've figured it out. I used a custom jdbc connection. SLM then sends a
select 1 when initiating the connection (I think). On Oracle it should
be select 1 from dual. The reason for using custom jdbc connection were
because it seems like SLM don't support service name for the db, only
sid. I've change to a Oracle connection and SID.

Now the collector works just fine awhile. But after importing a
handful of rows it starts to do something with two of the date columns.
What happens is that the raw data is not the same as the data in the db
table:


"RawData":"02.02.2012 13:52:06,01.01.1900 00:00:00,01.01.1900
00:00:00,4046,SYSNO,Ergo.IntegrationSuite.Agresso.Reports.Poller.Poll"
02.02.2012 13:52:06,02.02.2012 13:52:07,02.02.2012
13:52:11,4046,SYSNO,Ergo.IntegrationSuite.Agresso.Reports.Poller.Poll

The first line is from the rawdata file. The second line is from a
query in SQL Developer.

The sql is:

SELECT
TO_CHAR(invoke_time, 'DD.MM.YYYY HH24:MI:SS') INVOKETIME,
TO_CHAR(date_started, 'DD.MM.YYYY HH24:MI:SS') DATESTARTED,
TO_CHAR(date_ended, 'DD.MM.YYYY HH24:MI:SS') DATEENDED,
orderno,
user_id,
report_id
FROM PRHR01_AGR01.ACRREPORD
WHERE invoke_time > TO_TIMESTAMP ('%s', 'DD.MM.YYYY HH24:MI:SS')
AND rownum <= %d
ORDER BY invoke_time

The parse code is:

Record.prototype.parse = function(e){

//var startTime = Date.parseExact(rec.RXMap.col_DATESTARTED,
"dd.MM.yyyy HH:mm:ss");
//var endTime = Date.parseExact(rec.RXMap.col_DATEENDED,
"dd.MM.yyyy HH:mm:ss");
var eventTime = Date.parseExact(rec.RXMap.col_INVOKETIME, "dd.MM.yyyy
HH:mm:ss");

this.dt = eventTime;

this.baseMessage = "ReportID " + rec.RXMap.col_REPORT_ID + " initiated
by " + rec.RXMap.col_USER_ID + ". OrderNo: "
+ rec.RXMap.col_ORDERNO + ", date started: " +
rec.RXMap.col_DATESTARTED
+ ", date ended: " + rec.RXMap.col_DATEENDED;

this.eventName = "ACRREPORD Event";

if (this.dt) {
instance.SEND_EVENT = true;
}
else {
rec.sendUnsupported();
return false;
}

return true;
}

Any suggestions what causes this?

--
Frode


--
fsjovatsen
------------------------------------------------------------------------
fsjovatsen's Profile: http://forums.novell.com/member.php?userid=9767
View this thread: http://forums.novell.com/showthread.php?t=451094

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Database collector trouble


Hmm...

Beyond the raw data, are you also seeing the zeroed-out dates appearing
at your Collector input (e.g. rec.RXMap.col_DATESTARTED)?

Also, some notes:

1) it should be:
var eventTime = Date.parseExact(*this*.RXMap.col_INVOKETIME,
"dd.MM.yyyy HH:mm:ss");
because you're in a member function of the 'rec' variable already.

2) Not sure why you have
this.dt = eventTime;
You probably want something more like:
e.setDeviceEventTime(eventTime);

If you are seeing incorrect dates appearing in the Collector, then that
sounds like a Connector issue, and I'd definitely file a Service Request
via NTS:
'Support: Submit a Service Request'
(http://support.novell.com/contact/)


--
DCorlette
------------------------------------------------------------------------
DCorlette's Profile: http://forums.novell.com/member.php?userid=4437
View this thread: http://forums.novell.com/showthread.php?t=451094

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Database collector trouble


Thank's for the reply!

I'm seeing the zeroed-out dates in the raw data tap of the event source
and collector. I also see if I run the collector in the debugger.

Guess this is a SR then.

--
Frode


--
fsjovatsen
------------------------------------------------------------------------
fsjovatsen's Profile: http://forums.novell.com/member.php?userid=9767
View this thread: http://forums.novell.com/showthread.php?t=451094

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Database collector trouble


Yes, sorry. I know with the standard Oracle connection, it does send
SELECT 1 from dual, but I guess not the custom. So this is likely an
enhancement to the Connector to either support service name, or to
auto-detect when it needs to send SELECT 1 from dual.


--
DCorlette
------------------------------------------------------------------------
DCorlette's Profile: http://forums.novell.com/member.php?userid=4437
View this thread: http://forums.novell.com/showthread.php?t=451094

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.