Anonymous_User Absent Member.
Absent Member.
237 views

Changing JDBC Publisher Query


I am doing IDM for a year. I am using the Triggered JDBC driver and it
is really slow now processing events from the event log. A DBA said the
JDBC query is not very good, even though it is indexed. We have about
10million records in the event log they wish to keep for auditing as
well.

The JDBC query is this:
PT:SELECT * FROM ( SELECT * FROM xxxx.ID_EVENTLOG WHERE STATUS = 'N'
ORDER BY PK_RECORD_ID ASC) WHERE ROWNUM <= 10000 ORDER BY PK_RECORD_ID
ASC

the DBA said it would be much faster is changed to this:
select * from xxx.ID_EVENTLOG where status=’N’ and rownum<=10000 order
by 1 asc;

There does not appear to be anyway to change it? Is the solution just to
keep the event log cleaned out? Right now I have about 2 million "N"
waiting to process, and about 18 million "S" sitting around. We just did
a huge dump of info about 4 days ago. The event trace on JDBC I see the
query waiting for about 8 minutes, then it processes the 10,000 changes
very quickly, but than has to query again.


--
ritap2015
------------------------------------------------------------------------
ritap2015's Profile: https://forums.netiq.com/member.php?userid=10296
View this thread: https://forums.netiq.com/showthread.php?t=54111

Labels (1)
0 Likes
3 Replies
Knowledge Partner
Knowledge Partner

Re: Changing JDBC Publisher Query

On 8/21/2015 8:54 AM, ritap2015 wrote:
>
> I am doing IDM for a year. I am using the Triggered JDBC driver and it
> is really slow now processing events from the event log. A DBA said the
> JDBC query is not very good, even though it is indexed. We have about
> 10million records in the event log they wish to keep for auditing as
> well.
>
> The JDBC query is this:
> PT:SELECT * FROM ( SELECT * FROM xxxx.ID_EVENTLOG WHERE STATUS = 'N'
> ORDER BY PK_RECORD_ID ASC) WHERE ROWNUM <= 10000 ORDER BY PK_RECORD_ID
> ASC
>
> the DBA said it would be much faster is changed to this:
> select * from xxx.ID_EVENTLOG where status=�N� and rownum<=10000 order
> by 1 asc;
>
> There does not appear to be anyway to change it? Is the solution just to
> keep the event log cleaned out? Right now I have about 2 million "N"
> waiting to process, and about 18 million "S" sitting around. We just did
> a huge dump of info about 4 days ago. The event trace on JDBC I see the
> query waiting for about 8 minutes, then it processes the 10,000 changes
> very quickly, but than has to query again.


Any chance they can can give you a view that has only N records? Then
your view will always be smaller?

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Changing JDBC Publisher Query


I have suggested that. I just was unsure if the JDBC query can be
changed though I am afraid to change it. Good to know if it can be.

geoffc;260062 Wrote:
> On 8/21/2015 8:54 AM, ritap2015 wrote:
> >
> > I am doing IDM for a year. I am using the Triggered JDBC driver and

> it
> > is really slow now processing events from the event log. A DBA said

> the
> > JDBC query is not very good, even though it is indexed. We have about
> > 10million records in the event log they wish to keep for auditing as
> > well.
> >
> > The JDBC query is this:
> > PT:SELECT * FROM ( SELECT * FROM xxxx.ID_EVENTLOG WHERE STATUS = 'N'
> > ORDER BY PK_RECORD_ID ASC) WHERE ROWNUM <= 10000 ORDER BY

> PK_RECORD_ID
> > ASC
> >
> > the DBA said it would be much faster is changed to this:
> > select * from xxx.ID_EVENTLOG where status=�N� and rownum<=10000

> order
> > by 1 asc;
> >
> > There does not appear to be anyway to change it? Is the solution just

> to
> > keep the event log cleaned out? Right now I have about 2 million "N"
> > waiting to process, and about 18 million "S" sitting around. We just

> did
> > a huge dump of info about 4 days ago. The event trace on JDBC I see

> the
> > query waiting for about 8 minutes, then it processes the 10,000

> changes
> > very quickly, but than has to query again.

>
> Any chance they can can give you a view that has only N records? Then
> your view will always be smaller?



--
ritap2015
------------------------------------------------------------------------
ritap2015's Profile: https://forums.netiq.com/member.php?userid=10296
View this thread: https://forums.netiq.com/showthread.php?t=54111

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: Changing JDBC Publisher Query


The driver JDBC must have changed behavior from 3.6x to 4.5. I looked at
some traces I did a year ago before we upgraded, and my query looks
different and we never had this issue.

SELECT * FROM XXX.ID_EVENTLOG WHERE STATUS = 'N' ORDER BY PK_RECORD_ID
ASC

So what changed in the JDBC driver?

geoffc;260062 Wrote:
> On 8/21/2015 8:54 AM, ritap2015 wrote:
> >
> > I am doing IDM for a year. I am using the Triggered JDBC driver and

> it
> > is really slow now processing events from the event log. A DBA said

> the
> > JDBC query is not very good, even though it is indexed. We have about
> > 10million records in the event log they wish to keep for auditing as
> > well.
> >
> > The JDBC query is this:
> > PT:SELECT * FROM ( SELECT * FROM xxxx.ID_EVENTLOG WHERE STATUS = 'N'
> > ORDER BY PK_RECORD_ID ASC) WHERE ROWNUM <= 10000 ORDER BY

> PK_RECORD_ID
> > ASC
> >
> > the DBA said it would be much faster is changed to this:
> > select * from xxx.ID_EVENTLOG where status=�N� and rownum<=10000

> order
> > by 1 asc;
> >
> > There does not appear to be anyway to change it? Is the solution just

> to
> > keep the event log cleaned out? Right now I have about 2 million "N"
> > waiting to process, and about 18 million "S" sitting around. We just

> did
> > a huge dump of info about 4 days ago. The event trace on JDBC I see

> the
> > query waiting for about 8 minutes, then it processes the 10,000

> changes
> > very quickly, but than has to query again.

>
> Any chance they can can give you a view that has only N records? Then
> your view will always be smaller?



--
ritap2015
------------------------------------------------------------------------
ritap2015's Profile: https://forums.netiq.com/member.php?userid=10296
View this thread: https://forums.netiq.com/showthread.php?t=54111

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.