Highlighted
Absent Member.
Absent Member.
337 views

JDBC Event Log - unwanted loopback


I'm trying to create an event log entry for the connected database to
process. For example, the staging table is updated with changes from the
Identity Vault and an event log entry is created as a pointer to the
changed row.
The same event log is being used to publish data from a staging table
into the Identity Vault.
The document says two things about this.
If you use the "driver's database username" in the perpetrator column,
1) the driver will not process the event log entry via the Publisher
channel unless 2) the "Allow Loopback" parameter is set to true.

Unfortunately, even though I've used what I think is the username, and
"Allow Loopback" is false, the driver Publisher sets the status to 'S'
and deletes the entry.

Any suggestions?
By "driver database username" I presume it means the username in the
"authentication" section of the driver config?

Database is Oracle 11g. IDM Driver version is 3.5.9 with latest Oracle
JDBC. IDM is 4.0.2


--
SFDavies
------------------------------------------------------------------------
SFDavies's Profile: https://forums.netiq.com/member.php?userid=866
View this thread: https://forums.netiq.com/showthread.php?t=55787

Labels (1)
0 Likes
6 Replies
Highlighted
Absent Member.
Absent Member.

Re: JDBC Event Log - unwanted loopback

On 4/28/2016 11:44 AM, SFDavies wrote:
>
> I'm trying to create an event log entry for the connected database to
> process. For example, the staging table is updated with changes from the
> Identity Vault and an event log entry is created as a pointer to the
> changed row.
> The same event log is being used to publish data from a staging table
> into the Identity Vault.
> The document says two things about this.
> If you use the "driver's database username" in the perpetrator column,
> 1) the driver will not process the event log entry via the Publisher
> channel unless 2) the "Allow Loopback" parameter is set to true.
>
> Unfortunately, even though I've used what I think is the username, and
> "Allow Loopback" is false, the driver Publisher sets the status to 'S'
> and deletes the entry.
>
> Any suggestions?
> By "driver database username" I presume it means the username in the
> "authentication" section of the driver config?
>
> Database is Oracle 11g. IDM Driver version is 3.5.9 with latest Oracle
> JDBC. IDM is 4.0.2
>
>


Your understanding is correct. I'm not aware of any bugs related to that feature (ever).
It must be a mismatch in the configs. Set the trace level to 5 on the driver, stop the driver, delete the trace, and then
start it up so you have a nice clean start.

You should see the driver initialization parameters that include the configured database admin name. That string needs to
match exactly to what is in your perpetrator column. Looking at the trace as opposed to the config should clear up any
confusion. Keep in mind that depending on how your Oracle was installed the name MAY be case sensitive. I believe when the
driver does the event log query it is actually using a where clause to exclude that perp name so it needs to be exactly the same.

If that still doesn't do it, post the driver startup and then post a level 5 trace of the polling event.
We can pursue it further from there.

--
-----------------------------------------------------------------------
Will Schneider
Knowledge Partner http://forums.netiq.com

If you find this post helpful, please click on the star below.
0 Likes
Absent Member.
Absent Member.

Re: JDBC Event Log - unwanted loopback


I've obtained some new information. Setting Trace to 5 I got this in the
startup:

[04/29/16 08:38:30.944]:BODSDEV PT:BEGIN Get database properties.
[04/29/16 08:38:30.944]:BODSDEV PT: Database name: Oracle
[04/29/16 08:38:30.944]:BODSDEV PT: Database version: Oracle Database
11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
[04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver name: Oracle JDBC
driver
[04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver version: 11.2.0.4.0
[04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver major version: 11
[04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver minor version: 2
[04/29/16 08:38:30.944]:BODSDEV PT: Case sensitive: false
[04/29/16 08:38:30.944]:BODSDEV PT: Stored case: upper
[04/29/16 08:38:30.944]:BODSDEV PT: Association case: upper
[04/29/16 08:38:30.944]:BODSDEV PT: Username: 'LBU_STAGE_ADMIN'

Then I found this in the trace:

[04/29/16 09:09:04.398]:BODSDEV PT:SELECT * FROM IDVAULT_EVENTLOG WHERE
STATUS = 'N' AND ROWNUM <= 1 ORDER BY RECORD_ID ASC
[04/29/16 09:09:04.413]:BODSDEV PT: Allocated result set 56e58587 from
statement 3797d6f9.
[04/29/16 09:09:04.413]:BODSDEV PT:END Query for events.
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 1, field 'RECORD_ID',
value = 43279
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 2, field 'TABLE_KEY',
length: 9, value = 'ID=519925'
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 3, field 'STATUS',
length: 1, value = 'N'
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 4, field 'EVENT_TYPE',
value = 5
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 6, field 'PERPETRATOR',
length: 15, value = 'LBU_STAGE_ADMIN'
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 7, field 'TABLE_NAME',
length: 12, value = 'IDVAULT_DATA'
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 8, field 'COLUMN_NAME',
value = null
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 9, field 'OLD_VALUE',
value = null
[04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 10, field 'NEW_VALUE',
value = null
[04/29/16 09:09:04.413]:BODSDEV PT:BEGIN Row.
[04/29/16 09:09:04.413]:BODSDEV PT: RECORD_ID: 43279
[04/29/16 09:09:04.413]:BODSDEV PT: TABLE_KEY: 'ID=519925'
[04/29/16 09:09:04.413]:BODSDEV PT: STATUS: 'N'
[04/29/16 09:09:04.413]:BODSDEV PT: EVENT_TYPE: 5; insert row
(query-back)
[04/29/16 09:09:04.413]:BODSDEV PT: PERPETRATOR: 'LBU_STAGE_ADMIN'
[04/29/16 09:09:04.413]:BODSDEV PT: TABLE_NAME: 'IDVAULT_DATA'
[04/29/16 09:09:04.413]:BODSDEV PT: COLUMN_NAME: NULL
[04/29/16 09:09:04.413]:BODSDEV PT: OLD_VALUE: NULL
[04/29/16 09:09:04.413]:BODSDEV PT: NEW_VALUE: NULL
[04/29/16 09:09:04.413]:BODSDEV PT:END Row.
[04/29/16 09:09:04.413]:BODSDEV PT:Loopback row 43279.
[04/29/16 09:09:04.413]:BODSDEV PT: Closed result set 56e58587.
[04/29/16 09:09:04.413]:BODSDEV PT:Have no events to submit.
[04/29/16 09:09:04.429]:BODSDEV PT:BEGIN Transaction
[04/29/16 09:09:04.429]:BODSDEV PT: Not locking.
[04/29/16 09:09:04.429]:BODSDEV PT: BEGIN Batch
[04/29/16 09:09:04.429]:BODSDEV PT: UPDATE IDVAULT_EVENTLOG SET
STATUS = 'S' WHERE RECORD_ID = 43279
[04/29/16 09:09:04.429]:BODSDEV PT: END Batch
[04/29/16 09:09:04.429]:BODSDEV PT: COMMIT
[04/29/16 09:09:04.429]:BODSDEV PT:END Transaction

So the username matches and indeed the driver says "nothing to do here"
but STILL sets the status anyway!!


Will Schneider;267349 Wrote:
> On 4/28/2016 11:44 AM, SFDavies wrote:
> >
> > I'm trying to create an event log entry for the connected database to
> > process. For example, the staging table is updated with changes from

> the
> > Identity Vault and an event log entry is created as a pointer to the
> > changed row.
> > The same event log is being used to publish data from a staging table
> > into the Identity Vault.
> > The document says two things about this.
> > If you use the "driver's database username" in the perpetrator

> column,
> > 1) the driver will not process the event log entry via the Publisher
> > channel unless 2) the "Allow Loopback" parameter is set to true.
> >
> > Unfortunately, even though I've used what I think is the username,

> and
> > "Allow Loopback" is false, the driver Publisher sets the status to

> 'S'
> > and deletes the entry.
> >
> > Any suggestions?
> > By "driver database username" I presume it means the username in the
> > "authentication" section of the driver config?
> >
> > Database is Oracle 11g. IDM Driver version is 3.5.9 with latest

> Oracle
> > JDBC. IDM is 4.0.2
> >
> >

>
> Your understanding is correct. I'm not aware of any bugs related to
> that feature (ever).
> It must be a mismatch in the configs. Set the trace level to 5 on the
> driver, stop the driver, delete the trace, and then
> start it up so you have a nice clean start.
>
> You should see the driver initialization parameters that include the
> configured database admin name. That string needs to
> match exactly to what is in your perpetrator column. Looking at the
> trace as opposed to the config should clear up any
> confusion. Keep in mind that depending on how your Oracle was installed
> the name MAY be case sensitive. I believe when the
> driver does the event log query it is actually using a where clause to
> exclude that perp name so it needs to be exactly the same.
>
> If that still doesn't do it, post the driver startup and then post a
> level 5 trace of the polling event.
> We can pursue it further from there.
>
> --
> -----------------------------------------------------------------------
> Will Schneider
> Knowledge Partner http://forums.netiq.com
>
> If you find this post helpful, please click on the star below.



--
SFDavies
------------------------------------------------------------------------
SFDavies's Profile: https://forums.netiq.com/member.php?userid=866
View this thread: https://forums.netiq.com/showthread.php?t=55787

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: JDBC Event Log - unwanted loopback

On 4/29/2016 3:24 AM, SFDavies wrote:
> So the username matches and indeed the driver says "nothing to do here"
> but STILL sets the status anyway!!


Ohhhh, yes. That is the expected behavior.
Since you are wanting to ignore the loopback event this is the desired outcome.
What are you hoping the event would do?
Since you are using queryback events there really isn't anything useful in the event log for this user.
If you want to capture events so you can look at history there is a setting for that too where the value won't be deleted
from the log and instead will be moved to the _PROCESSED table. There is a stored procedure to do that and a driver config
setting.

Does that address your need? Or what problem are you trying to solve then?

--
-----------------------------------------------------------------------
Will Schneider
Knowledge Partner http://forums.netiq.com

If you find this post helpful, please click on the star below.
0 Likes
Highlighted
Valued Contributor.
Valued Contributor.

Re: JDBC Event Log - unwanted loopback

IDM 4.6.4 | Driver: JDBC | Database: Oracle 12c | Publication mode: Triggered 

Issue: Unable to get events from Event Log Table thru publisher channel. Though there are new events, the remote loader log says "Have no events to submit." as below.

DirXML: [11/29/19 18:39:05.09]: TRACE:  BEGIN Row.
DirXML: [11/29/19 18:39:05.09]: TRACE:                RECORD_ID:    1197
DirXML: [11/29/19 18:39:05.09]: TRACE:                TABLE_KEY:    'LOCATIONKEY=617'
DirXML: [11/29/19 18:39:05.09]: TRACE:                STATUS:       'N'
DirXML: [11/29/19 18:39:05.09]: TRACE:                EVENT_TYPE:   6; update row (query-back)
DirXML: [11/29/19 18:39:05.09]: TRACE:                TABLE_NAME:   'LOCATIONS'
DirXML: [11/29/19 18:39:05.09]: TRACE:                COLUMN_NAME:  'Street'
DirXML: [11/29/19 18:39:05.09]: TRACE:                OLD_VALUE:    'King'
DirXML: [11/29/19 18:39:05.09]: TRACE:                NEW_VALUE:    'Queen'
DirXML: [11/29/19 18:39:05.09]: TRACE:                PERPETRATOR:  NULL
DirXML: [11/29/19 18:39:05.09]: TRACE:  END   Row.
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING ResultSet.next()
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
DirXML: [11/29/19 18:39:05.09]: TRACE:  RETURNED VALUE:  false
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING ResultSet.close()
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
DirXML: [11/29/19 18:39:05.09]: TRACE:                Closed    result set 49bc1d2c.
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING PreparedStatement.clearParameters()
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING Statement.ClearWarnings()
DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
DirXML: [11/29/19 18:39:05.09]: TRACE:  Have no events to submit.
DirXML: [11/29/19 18:39:05.09]: TRACE:  Remote Loader: Received document from publication shim.
DirXML: [11/29/19 18:39:05.10]: TRACE:  <nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
     

 

Can anyone help please?  Highly appreciate your time and cooperation

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

Re: JDBC Event Log - unwanted loopback

Stupid queston:  Is the table/column in the Filter?

0 Likes
Highlighted
Valued Contributor.
Valued Contributor.

Re: JDBC Event Log - unwanted loopback

Yes, they are in filter

Thanks for checking

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.