× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. WDSCI-L
  3. May 2021

Re: iSphere 4.0.0.r released

Juan,

The link to the SQL reference works, when you open it from the SQL editor (question mark next to "WHERE:") or from the RDi main menu "Help - Help Contents - iSphere - ...". I did not yet check why it does not work on the Internet help. But of course it will be fixed.

I first thought that you were talking about true BLOB fields when you asked for the correct SQL syntax. But now I think that you mean JOESD when talk about the CLOB field. So in that case you do not need to cast anything, because you can instantly use the original field names.

The steps you have to take are:

a) Load the journal entries.

b) Open the SQL editor.

c) Use the drop down box labeled as "Table name" and select the name of the table that contains the fields you want to use in the WHERE clause.

d) Enter the WHERE clause using the original field names.

Use Ctrl+SPACE or "Add Field" to open the field list of the table that you selected in the "Table name" drop down box.

I will think about to add JOESD to the available fields but I am not sure whether or not that is a good idea. It is just a feeling.

Regards,

Thomas.

Am 03.05.2021 um 18:30 schrieb Juan Concepcion:
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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx> On Behalf Of Thomas Raddatz
Sent: Friday, April 30, 2021 5:11 AM
To: Rational Developer for IBM i <wdsci-l-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx>
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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx> Im Auftrag von Juan Concepcion
Gesendet: Donnerstag, 29. April 2021 16:31
An: 'Rational Developer for IBM i' <wdsci-l-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx>
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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx> 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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx
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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx
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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@xxxxxxxxxxxxxxxx
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




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.