013 This week on show we have the most awesome Scott Forstie from IBM.  Scott stops by the show to discuss his favorite subjects SQL, IBM Services and why you need to expand your horizons and think out of the box with SQL.  Scott is a video blogger, author, speaker, and all-around great guy.  And in his spare time, he enjoys running, hiking, listening to music and spend time with his family.Scott’s contact information and social:Email: [email protected]: @Forstie_IBMiDb2 for i – Technology Updates (ibm.com)IBM i Services (SQL)IBM.biz/IBMi­_ACSRFE : https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=138470IBM i executive briefings: email [email protected] incredible Th(i)ng!  Products, gadgets, recipes, music, or things we are loving right now!Tony’s picks for this week:Cooler fall weather!Peg’s pick for the week: Pumpkin Spice Lattes from Starbucks!Scott’s pick for the week:Scott celebrates National Record Player Day on August 30, 2021!  He recently purchased Aretha Franklin’s Greatest Hits and Black Pumas latest album. Upcoming Events:POWERUp21 A virtual event is scheduled for October 4-7, 2021. COMMON provides the best in Power Systems and IBM I education – come learn from the most celebrated industry experts in the field.  Head over to COMMON.org for more information.RPG & DB@ Summit October 20-26, 2021 A virtual event focused exclusively on IBM i developers. Join RPG, database and open source experts for 5+ days of live, interactive, online in-depth sessions for developers. Check out the sessions and the expert speakers and see for yourself.https://www.ibm.com/training/events/vtechu2021This episode is sponsored by…COMMONhttps://www.common.org/homeMidrange Dynamics Change Management Software – Built for IBM i modernization! https://www.midrangedynamics.com/ Interested in sharing your IBM i story?  Want to sponsor the podcast?  We want to hear from you!  Reach out to Peg at [email protected] 

013 This week on show we have the most awesome Scott Forstie from IBM.  Scott stops by the show to discuss his favorite subjects SQL, IBM Services and why you need to expand your horizons and think out of the box with SQL.  Scott is a video blogger, author, speaker, and all-around great guy.  And in his spare time, he enjoys running, hiking, listening to music and spend time with his family.

Scott’s contact information and social:
Email: [email protected]
Twitter: @Forstie_IBMi
Db2 for i – Technology Updates (ibm.com)
IBM i Services (SQL)
IBM.biz/IBMi­_ACS
RFE : https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=138470
IBM i executive briefings: email [email protected]

One incredible Th(i)ng!  Products, gadgets, recipes, music, or things we are loving right now!

Tony’s picks for this week:
Cooler fall weather!

Peg’s pick for the week:
Pumpkin Spice Lattes from Starbucks!

Scott’s pick for the week:
Scott celebrates National Record Player Day on August 30, 2021!  He recently purchased Aretha Franklin’s Greatest Hits and Black Pumas latest album.

Upcoming Events:
POWERUp21 A virtual event is scheduled for October 4-7, 2021. COMMON provides the best in Power Systems and IBM I education – come learn from the most celebrated industry experts in the field.  Head over to COMMON.org for more information.

RPG & DB@ Summit October 20-26, 2021 A virtual event focused exclusively on IBM i developers. Join RPG, database and open source experts for 5+ days of live, interactive, online in-depth sessions for developers. Check out the sessions and the expert speakers and see for yourself.

https://www.ibm.com/training/events/vtechu2021

This episode is sponsored by…

COMMON
https://www.common.org/home

Midrange Dynamics Change Management Software – Built for IBM i modernization!
https://www.midrangedynamics.com/

Interested in sharing your IBM i story?  Want to sponsor the podcast?  We want to hear from you!  Reach out to Peg at [email protected] 

As in the Highlitghs of Serie A football, this post aims to highlight the most interesting things of the IBM autumn announcements (for IBM i and Power, with links to posts, videos, webcasts and podcasts from the last few days.

We have been waiting for them, the announcements of the Technology Refresh IBM i of Autumn: as usual in recent years, IBM arrives on time at Privamere and Autumn (and this time even a moment earlier!) with announcements of a Technology Refresh for currently supported versions.

Instead, we can think that the next Spring announcements will bring us a new version (7.5?) IBM i, but, in the meantime, let’s enjoy the news of now: SQL, RPG, Open Source, ACS, Rdi and even Hardware, with the presentation of the new and powerful Power10 processor.

But let’s go in order:

IBM i Technology Refresh 7.4 TR5 and 7.3 TR11

Let’s start with the links to the official IBM announcements and then move on to the more interesting things, at least from my point of view as an IBM i developer:

IBM i 7.4 Technology Refresh 5 delivers significant enhancements

IBM i 7.3 Technology Refresh 11 delivers significant enhancements

The new HTTP Functions SQL

I believe that by now all of us IBM i developers have had to integrate calls to Web Services into our applications using the different techniques available (Scott Klement’s HttpApi, SQL Http Functions and Axisc Transoport APIs). Personally I find it really convenient to consume Web Services using the HTTPGETCLOB, HTTPPOSTCLOB functions for simplicity and the possibility of testing them even outside the RPG source using single SQL statements from ACS SQL Script.

New HTTP functions arrive in the technology refreshes TR5 and TR11 ( New HTTP functions based on QSYS2 ) (in QYS2 and no longer in the SYSTOOLS library) that no longer use the Java Virtual Machine but the IWS Transport API, with considerable advantages from a performance point of view (absolutely important when we start using Web Services it becomes important as in the applications studied in “microservices”).

QSYS2.HTTP_GET, QSYS2. HTTP_POST, QSYS2. HTTP_PUT (and their VERBOSE versions (table no longer scalar!)) they somehow replace SYSTOOLS.HTTPGETCLOB, SYSTOOLS.HTTPPOSTCLOB and SYSTOOLS.HTTPPUTCLOB (and their VERBOSE versions).

The QSYS2.HTTP_DELETE and QSYS2_HTTP_DELETE_VERBOSE functions are added.

I just made a post dedicated to the comparison between QSYS2.HTTP vs SYSTOOLS.HTTP functions: “ New QSYS2.HTTP Functions SQL (7.4 TR5 and 7.3 TR11) “… in addition to the comparison between the old and new functions in the post you will also find an interesting integration with Google Translate API through HTTPPOSTCLOB and HTTP_POST SQL.

RPG Updates

Interesting updates also side RPG with the new BIF% MAXARR and% MINARR which return the index of the maximum and minimum value of an array or the SORT operation to sort an array of DS data structures which can now also work on multiple fields of the DS (things like SORT orders % FIELDS (order: line) … very useful when you keep an array of data in memory and want to manage it as if it were a table in QTEMP, with naturally better performance).

There is an excellent post on RPGPGM.COM by Simont Hutchinson that presents some concrete cases of use of these new BIF RPGs: “ RPGPGM.COM New RPG BiF to retrieve greatest and lowest value in an array “

Rational Developer for i 9.6.0.11

At the same time as the announcements of the TR5, the update of Rdi to version 9.6.0.11 was also made available which, in addition to supporting the new% BIF and operations of the RPG Updates described above, presents some interesting news and improvements:

The ability to print and save string search results in sources Improvements in code refactoring functionsImprovements to the already great Outline viewSeveral improvements requested in RFE Request for ehnancements (IBM hears us !, we use the RFE to propose improvements that can be useful to everyone!)

More details in this excellent post by Blogger Marco Riva: “ MARKONETOOLS: Released update for RDi 9.6.0.11 “

ACS Access Client Solutions 1.1.8.8

Certainly could not miss a new version of ACS and its excellent additional functions such as SQL Sript etc:

SQL Script: warning in case of pending transactions … when we are under commit and have not yet done a commit rollback with pending transactions … Great!New examples available in the “Insert from example” function … very useful … which, personally, I enrich with my personal examples but which is excellent as a starting point if you have little memory … like the conscriptImprovements in application memory management and Upload results functions etc.

Open Source

Also on the Open Source and IBM i side, interesting news … from Node.js version 16 and Python version 3.9 to the new GCC Compilers version 10, but also Ghostscript officially brought to the platform for PDF creation or PDF-PCL transformation very useful in printing PDFs on Laser printers that do not support PDF Direct Printing or IPDS

IBM i Services

Among the new IBM i Services available in this Technology Refresh, we certainly find some things that will make IBM i systems engineers happy, such as services QSYS2.COLLECTION_SERVICES_INFO and the various services on Audit Journals Entries , but also excellent new services that are also useful to developers such as QSYS2.SYSFILES to get the same info as DSPFD and QDBRTVFD API not so easy to use. Or QSYS2.ACTIVE_QUERY_INFO to keep under control the active SQL queries or those remaining in “pseudo open” state.

IBM i Navigator for i

Very nice the new graphics of the Navigator for I, all Web, with several new functions, which will please IBM i systems engineers

You can listen to this Podcast by Tim Rowe to Ichime by Charles Guarino: “iCHIME meeting with Tim Rowe discussing the new System Navigator for i – Part 1”

Power10 processor

Here it is finally the new IBM Power10 processor that also changes the way of writing it “Power10” compared to the “POWER9” all caps as before (I would not have even noticed it, I think it is more interesting to worry about the performance than the way to write the name … but I’ll be old fashioned!).

On the Power10 they only run 7.3 and 7.4 and, for the moment, only the Power System E1080 is available

Without going too far into the technicalities, which I personally do not know, I would say that it is worth listening to the Webcast of the announcement of the Power10 and the TRs , held by Steve Will, IBM i Chief Architect, Dan Sundt, IBM i Product Manager and Tim Rowe IBM i Business Architect System Management, or directly the presentation of the IBM Power10 System E1080 on Yuotube.

Conclusions

As you have seen, there are many news, in all areas … long live IBM i!

—
Roberto De Pedrini
Faq400.com

A few days ago the new Technology Refresh TR4 for 7.4 and TR11 for 7.3 of the IBM i operating system were presented

Among the various innovations are the new HTTP functions in QSYS2, based on the IWS Transport API and no longer on Java … with notable improvements in performance and management of possibilities in the consumption of HTTPS SSL services.

If you have already used HTTPGETCLOB, HTTPPOSTCLOB or their VERBOSE versions, switching to the new QSYS2-HTTP_ functions will be very easy for you (if you pay due attention to the position of the parameters, those pranksters from IBM Rochester’s lab have reversed them (see below !).

If, on the other hand, you haven’t fought with SQL’s HTTP functions yet, the new versions will save you some bad words, because these new functions have better error handling.

Consumption of a REST web service with GET method (without errors):

Let’s see a simple example of consumption of a REST Web Service in GET: SYSTOOLS.HTTPGETCLOB and QSYS2.HTTP_GET, they are not very different … just call the correct function:

— Get an existing WEB page with SYSTOOLS.HTTPGETCLOB … it’s so simple!

select SYSTOOLS.HTTPGETCLOB(‘http://www.google.com/’,”) from sysibm.sysdummy1;

— Get the same WEB page with QSYS2.HTTP_GET … about the same

select QSYS2.HTTP_GET(‘http://www.google.com/’,”) from sysibm.sysdummy1;

Consumption of a REST in GET with errors (example 404-Not found)

If there are no mistakes the thing is very simple … if, instead, we have to deal with HTTP errors (4xx or 5xx), things get a little more complicated with old SYSTOOLS.HTTPGETCLOB compared to new QSYS2.HTTP_GET function.

If we try to call from the ACS SQL Script management, a Web Service that returns, for example, a 404-Not found error (such as a web page that does not exist), HTTPGETCLOB goes into error and a Popup window is presented with the error itself … a general Java error that could mean anything and everything.

If we want to avoid this error message we can use the SYSTOOLS.HTTPGETCLOBVERBOSE function, which, at least, responds with an HTTPHeader from the Web Service and, with a little attention on the request Header, we can also get response message ( from which, generally, we can understand the real reason for the error).

Let’s see it step-by-step:

— Trying a REST Get web service in 404-Not found Error … I
— I got a Popup Error with SQL State 38000 Vendor code -302 !!!!!!!!!!

select SYSTOOLS.HTTPGETCLOB(‘http://www.google.com/notExist’,”) from sysibm.sysdummy1;

— Ok, let me try with the HTTPGETCLOBVERBOSE (pay attention, is Table Function, not a
— scalar function!)

select * from table( systools.httpGetClobVerbose(‘http://www.google.com/notExist’,”)) x;

— Now I don’t get the popup-error,
— I can read the HTTP Response-Header but I don’t get the response-message (body) from Google

— Let me add this HttpHeader attribute (includeErrorMsg=”true”)
— (pay attention, is an HTTPHeader Attribute not an header value!!!)

select * from table( systools.httpGetClobVerbose(‘http://www.google.com/notExist’,'<httpHeader includeErrorMsg=”true”/>’)) x;

— Now let me try with the new QSYS2.HTTP_GET function

select QSYS2.HTTP_GET(‘http://www.google.com/notExist’,”) from sysibm.sysdummy1;

— Good! I can get the response-message from Google without annoying popup-messages

Web Service REST with POST method: SYSTOOLS.HTTPPOSTCLOB vs QSYS2.HTTP_POST (Google Translate API Example)

The sources of this part can be found in this Github Gist ;

When instead we have to consume REST Web Services in POST (or in PUT), things become a moment more complicated because we have to pass the body (payload) of the message, in JSON and, often, also some particular settings of the HTTP Header.

To take an example of calling up a Web Service Rest in POST, I “stole” to my colleague Marco Balgera, his own script for translation with the Google Translator API … of course, I first opened my own account on Google Cloud Platform to get the appropriate API-KEY to be used in calls to Web Services (since I was stealing the idea … I could also steal his API-KEY … actually! … but I’m an honest thief!).

The Web Service we’re going to call is one of the many made available by Google in the Cloud Console … if we can use this service for translation, we shouldn’t have any problems with other Google web services too!

API-KEY, URL, HEADER & BODY … Global Variable

To simplify the syntax of the examples that I report below, I first build the Global Variable SQL (we can think of them as a kind of Data-Area) with the JSON or XML texts that we will need for the web services …

I also store my API-KEY in a Global Variable … if I had to change it … I only change the Default value of my variable and all the SQL statements continue to work.

— Google Translator (API Key from my Google Cloud Console)
create variable faq400.GV_GoogleAPIKey varchar(100) default(‘xxxxxxxxxxxxxxxxxxxxxxxxxx’);

// insert here your GoogleAPI key!!!

— Other Global variables
create variable faq400.GV_URL varchar(256);
create variable faq400.GV_HEADER varchar(1000);
create variable faq400.GV_HEADERJSON varchar(1000);
create variable faq400.GV_BODY varchar(32000);

I create a small table in QTEMP with the names of some fruits … goal of the project is to translate those names into different languages and update the table in QTEMP accordingly

— Create a sample simple table
CREATE TABLE qtemp.FruitNames ( “ID” INTEGER GENERATED ALWAYS AS IDENTITY, NameITA varCHAR(30) CCSID 280 NOT NULL DEFAULT ”, NameENG varCHAR(30) CCSID 37 NOT NULL DEFAULT ”,
NameESP varCHAR(30) CCSID 284 NOT NULL DEFAULT ”,
NameGER varCHAR(30) CCSID 273 NOT NULL DEFAULT ”,
NameFRA varCHAR(30) CCSID 297 NOT NULL DEFAULT ”
);

— Insert som fruits in Italian (Mela=Apple and so on)

insert into qtemp.FruitNames (NameITA)
values(‘Mela’),
(‘Pera’),
(‘Pesca’),
(‘Albicocca’),
(‘Banana’),
(‘Ciliegia’),
(‘Fragola’),
(‘Caco’),
(‘Castagna’);

To the Google Translation API, as explained in the documentation of the function itself, we need to pass a JSON with an array of words or phrases and references to the source and target languages.

So I try to create a JSON as desired by the Google API:

— Create a JSON with an array of words o sentences … as Google API like
select json_object(‘q’ value JSON_ARRAYAGG(nameITA),
‘source’ value ‘it’,
‘target’ value ‘en’,
‘format’ value ‘text’) as “BodyMsg”
from qtemp.FruitNames ;

— Here my JSON
{
“q”: [
“Mela”,
“Pera”,
“Pesca”,
“Albicocca”,
“Banana”,
“Ciliegia”,
“Fragola”,
“Caco”,
“Castagna”
],
“source”: “it”,
“target”: “en”,
“format”: “text”
}

I just have to put it all together and really try the translation service:

— URL … with my APi-KEY

set faq400.GV_URL=’https://translation.googleapis.com/language/translate/v2?key=’ concat faq400.GV_GoogleAPIKey;

— Header (XML)

set faq400.GV_HEADER='<httpHeader><header name=”content-type” value=”application/json”/></httpHeader>’;

— Body … with my words

set faq400.GV_BODY= (
select json_object(‘q’ value JSON_ARRAYAGG( nameITA),
‘source’ value ‘it’,
‘target’ value ‘en’,
‘format’ value ‘text’) as “BodyMsg”
from qtemp.FruitNames ) ;

— And finally, let’s try Google Translator API

select systools.httppostclob(faq400.GV_URL,
cast(faq400.GV_HEADER as clob(1k)),
cast(faq400.GV_BODY as clob(10k)) )
from sysibm.sysdummy1;

— Here the JSON I get
{
“data”: {
“translations”: [
{
“translatedText”: “Apple”
},
{
“translatedText”: “Pear”
},
{
“translatedText”: “Fishing”
},
{
“translatedText”: “Apricot”
},
{
“translatedText”: “Banana”
},
{
“translatedText”: “Cherry”
},
{
“translatedText”: “Strawberry”
},
{
“translatedText”: “Caco”
},
{
“translatedText”: “Chestnut”
}
]
}
}

OK, SYSTOOLS.HTTPPOSTCLOB works … after all, my colleague Marco had already tried it … I had no doubt!

Let’s see if it also works with QSYS2.HTTP_POST … but, be careful … it is not enough to change the name of the SQL Function, we must pay attention to the order of the parameters and to the Header, which is no longer an XML but a JSON: those Rochester pranksters made this joke on us!

— Let’s create a new Header, JSON with a special option “sslTolerate”=”true” if we don’t need to check SSL Certificates

set faq400.GV_HEADERJSON= json_object(‘header’ value ‘content-type,application/json’,
‘header’ value ‘content-length, ‘ concat length(trim(faq400.GV_BODY)),
‘sslTolerate’ value ‘true’
);

— Now we can try QSYS2.HTTP_POST (pay attention to parameter’s sequence, not the same!)
select QSYS2.HTTP_POST(faq400.GV_URL,
cast(faq400.GV_BODY as clob(10k)), cast(faq400.faq400.GV_HEADERJSON as clob(1k)) )
from sysibm.sysdummy1;

Good! Everything works in both the “old” and the new way! Great!

The aim of the project, however, was to translate into various languages and to store the translations in our “simple-sample” table in QTEMP: let’s do it … both with SYSTOOLS.HTTPPOSTCLOB and with QSYS2.HTTP_POST

— Translate in English …. using HTTPOSTCLOB
update qtemp.FruitNames a set nameEng= (select translatedText from json_table(systools.httppostclob(
faq400.GV_URL, cast(faq400.GV_HEADER as clob(1k)), cast(json_object(‘q’ value JSON_ARRAY(a.nameITA),
‘source’ value ‘it’,
‘target’ value ‘en’,
‘format’ value ‘text’) as clob(10k))), ‘$.data.translations’ COLUMNS (translatedText varchar(30) path ‘$.translatedText’)) fetch first 1 rows only);

— Translate in Spanish
update qtemp.FruitNames a set nameEsp= (select translatedText from json_table(systools.httppostclob(
faq400.GV_URL, cast(faq400.GV_HEADER as clob(1k)), cast(json_object(‘q’ value JSON_ARRAY(a.nameITA),
‘source’ value ‘it’,
‘target’ value ‘es’,
‘format’ value ‘text’) as clob(10k))), ‘$.data.translations’ COLUMNS (translatedText varchar(30) path ‘$.translatedText’)) fetch first 1 rows only);

— Translate in German
update qtemp.FruitNames a set nameGer= (select translatedText from json_table(systools.httppostclob(
faq400.GV_URL, cast(faq400.GV_HEADER as clob(1k)), cast(json_object(‘q’ value JSON_ARRAY(a.nameITA),
‘source’ value ‘it’,
‘target’ value ‘de’,
‘format’ value ‘text’) as clob(10k))), ‘$.data.translations’ COLUMNS (translatedText varchar(30) path ‘$.translatedText’)) fetch first 1 rows only);

— And Now in French … with QSYS2.HTTP_POST
update qtemp.FruitNames a set nameFRA= (select translatedText from json_table(QSYS2.HTTP_POST(
faq400.GV_URL, cast(json_object(‘q’ value JSON_ARRAY(a.nameITA),
‘source’ value ‘it’,
‘target’ value ‘fr’,
‘format’ value ‘text’) as clob(10k)),
cast(faq400.GV_HEADERJSON as clob(1k))
), ‘$.data.translations’ COLUMNS (translatedText varchar(30) path ‘$.translatedText’)
));

OK, we have our amazing fruit table in all languages … fantastic … did you know that the Strawberry is called Erdbeere in German and the Pear, Poire in French?

No? That’s what these web services are for!

Dislcaimer

No fruit has been mistreated for this post.

The aim was not to learn languages but to see how the new HTTP functions of the TR5 and TR11 work … thanks IBM (and next time, less jokes about parameter order).

Source of this post on Github Gist

The sources of all this post are available in this Gihub GiST:

https://gist.github.com/Faq400Git/19d3c1e48a220c25c142fa81bcd89a5c

—
Roberto De Pedrini
Faq400.com

Thanks so much Patrick @P_Behr for the outstanding workshop yesterday! Everyone was motivated and inspired to try something new! #IBMi #IBMiOSS #SQL #IWS #ReactJS twitter.com/OCEANUserGroup…

– OCEAN User Group (@OCEANUserGroup)20:20 – Sep 19, 2021Quoted Tweet:

A full day of in-person training with #IBMi Expert Patrick Behr @P_Behr is coming up on Saturday, Sept. 18th! #ServicePrograms #SQL #IWS #React Get the #IBMiOSS training you need! FREE for OCEAN members. oceanusergroup.org pic.twitter.com/TB1f5FzyFl

– OCEAN User Group (@OCEANUserGroup)09:53 – Aug 30, 2021