Thomas,
When we use iSphere feature to compare (side by side) 2 journal entries, we can see the differences on fields' values for those specific entries. It's very nice having that option that parses the journal entry data(BLOB in DB2) into different fields so we can actually see the values. But this feature is limited to a direct pre-selection of 2 entries.
What if we want to search journal entries where a specific value was present in the saved row's image?
We can do it in DB2 by using function QSYS2.DISPLAY_JOURNAL(...) and then filtering results where CAST(SUBSTR(ENTRY_DATA, 1, 100) AS VARCHAR(100) FOR BIT DATA) like '%string%'. And I mentioned operator "Like", but we can use any other SQL operator, even REGEXP functions against the specific position where we know the field is held within the journal entry data field (ENTRY_DATA).
It would be nice being able to do the same with the returned list of entries that Journal Explorer view has. This view already includes an option to filter entries through SQL, but it fails when trying operator "CAST". It looks like it's not supported. That's why I wanted to know what kind of SQL is supported in that view. I went to
http://isphere.sourceforge.net/help/, and there is a special topic there for "SQL Reference" on "Reference/Journal Explorer". That link points to "/help//biz.isphere.base.help/html/sql/sql_reference.html" but page is showing error 404. I found the requested info in a different location "
http://isphere.sourceforge.net/help/html/sql/sql_reference.html";. I know, eventually, this link will be fixed (not a big deal). The info showed there says "The SQL language that is used for filtering journal entries is very similar to the IBM SQL. In most cases it should be possible to use the same SQL statements that would be used on the host system." ; and it lists some operators that can be used. I don't see operator CAST, so I'm wondering how can we filter them based on the journal entry data field?
Also, when using button "Add field" it shows all fields but JOESD (that makes you think this field is not supported for filtering the rows) , whoever it is showed on the grid as a string field. It looks like it was parsed to string when loading the grid.
Thanks,
Juan Concepcion
Applications Programmer
CU*Answers
-----Original Message-----
From: WDSCI-L <wdsci-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Thomas Raddatz
Sent: Friday, April 30, 2021 5:11 AM
To: Rational Developer for IBM i <wdsci-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WDSCI-L] iSphere 4.0.0.r released
WARNING: EXTERNAL EMAIL. Do NOT CLICK on links or open attachments unless you are expecting it or have verified with the sender.
Juan,
I am not sure whether it is a good idea to touch BLOB data, because LOB fields (character of binary) can be very large. So searching LOB fields with a LIKE expression is not a good idea, isn't it?
Thomas.
-----Ursprüngliche Nachricht-----
Von: WDSCI-L <wdsci-l-bounces@xxxxxxxxxxxxxxxxxx> Im Auftrag von Juan Concepcion
Gesendet: Donnerstag, 29. April 2021 16:31
An: 'Rational Developer for IBM i' <wdsci-l@xxxxxxxxxxxxxxxxxx>
Betreff: Re: [WDSCI-L] iSphere 4.0.0.r released
Hi Thomas,
I'm trying to filter journals entries listed on the Journal Explorer view using SQL operators over field JOESD and I haven't had luck.
I've tried:
- JOESD like '%string%', (I know the journal entry is a BLOB field in DB2, but I had to try it, because online help mentioned the result set is loaded locally, so SQL type might be different or even the loaded field value could have been converted already).
- CAST(SUBSTR(JOESD, 1, 100) AS VARCHAR(100) FOR BIT DATA) like '%string%', (this works on DB2 when using it with QSYS2.DISPLAY_JOURNAL(...) but doesn't work here, maybe because 'the result set is loaded locally').
There is an entry on
http://isphere.sourceforge.net/help/ for SQL Reference in Journal Explorer view, but the link is broken.
Could you let us know what kind of SQL can be used here?
Thanks,
Juan Concepcion
Applications Programmer
CU*Answers
-----Original Message-----
From: WDSCI-L <wdsci-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Thomas Raddatz
Sent: Friday, April 23, 2021 10:41 AM
To: WDSCI-L@xxxxxxxxxxxxxxxxxx
Subject: [WDSCI-L] iSphere 4.0.0.r released
WARNING: EXTERNAL EMAIL. Do NOT CLICK on links or open attachments unless you are expecting it or have verified with the sender.
Hi Folks,
It is time to update your iSphere plug-in to version 4.0.0.r.
There is no need for updating your library unless you want to update the STRPREPRC utility to version 1.19.1.
=================================================================
The iSphere library is compiled for 7.1.
=================================================================
Work With Spooled Files:
* Added key modifier "Ctrl" to menu option "Work With Spooled Files".
Pressing the "Ctrl" key while clicking the menu option opens the
spooled files view setting the "pinned" flag.
* Changed persistence attributes of the iSphere "Spooled File" view
due to internal changes. Sort orders of pinned views are lost, when
updating to iSphere 4.0.0.r.
* Fixed problem that the sort order was not restored when loading a
pinned iSphere "Work With Spooled Files" view.
Source File Search:
* Changed layout of iSphere Source File Search result view.
* Changed iSphere Source File Search to resolve remote system filters
and objects in batch. Added option for switching between the old
and new mode to the preferences page.
* Added toolbar button for closing all search result tabs of the
iSphere Source File Search result view.
Message Search:
* Changed layout of iSphere Message File Search result view.
* Added toolbar button for closing all search result tabs of the
iSphere Message File Search result view.
Journal Explorer:
* Greatly improved performance, when loading journal entries from
a journal.
* Changed compare side-by-side compare dialog of iSphere Journal
Explorer to resize the columns of both sides together.
* Added "Copy" and "Copy value" actions to context menu of journal
entry details viewer.
* Enhanced the iSphere Journal Explorer and added option to search
for entry specific data.
* Enabled the iSphere Journal Explorer for objects of type *DTAARA
and *DTAQ.
* Added option to display only changed fields, when comparing records
in the iSphere Journal Explorer.
Remote Systems View:
* Added preferences option to merge the results of a spooled file
filter with more than one filter expression. The final result is
sorted by creation time.
* Added option "Work With Spooled Files" to job entries of a RSE
job filter.
* Added option "Copy Qualified Name" to job entries of a RSE job
filter.
* Added special value *TODAY to the "End date" property of the
iSphere spooled file filter.
Source Member Compare:
* Added option "Ignore white spaces" to source member compare
preferences page.
Other Components:
* Changed SQL query engine to ignore trailing spaces.
* Enabled command key F5 for refreshing the iSphere "Work With Spooled
Files" view.
* Enabled command key F5 for refreshing the iSphere "Job Log Explorer"
view.
* Enabled command key F5 for refreshing the iSphere "Job Trace
Explorer" view.
* Enabled command key F5 for refreshing the iSphere "Journal Explorer"
view.
* Updated STRPREPRC command to v1.19.1 with improved error reporting.
iSphere APIs:
* Added iSphere Core plug-in API.
* Added iSphere Job Log Explorer plug-in API.
* Added iSphere Journal Explorer Core plug-in API.
* Added iSphere Job Trace Explorer plug-in API.
A lot of features have been realized due to your feedback.
Feedback is important for us. If you think something is bad, please let us know. If you think something is good, we ask you to rate iSphere on the SourceForge project page. If you have ideas for new features, don't hesitate to send us a feature request.
Here are the links to iSphere on the Internet:
http://sourceforge.net/projects/isphere/
http://isphere.sourceforge.net/
http://isphere.sourceforge.net/help/
http://isphere.sourceforge.net/eclipse/rdi8.0/
Hope you like it.
Regards,
Thomas.
--
This is the Rational Developer for IBM i (WDSCI-L) mailing list
To post a message email: WDSCI-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/wdsci-l.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com
--
This is the Rational Developer for IBM i (WDSCI-L) mailing list
To post a message email: WDSCI-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/wdsci-l.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com
--
This is the Rational Developer for IBM i (WDSCI-L) mailing list
To post a message email: WDSCI-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/wdsci-l.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com
midrange.com
