Friday, November 17, 2017

Last of 7.3 TR3 and 7.2 TR7 PTFs now available

last of ptfs for ibm i 7.3 tr3 and 7.2 tr 2 out now

I have been remiss in letting you know when the PTFs for parts of the latest Technology Refreshes, that were not released in October, became available.

The PTFs for the new RPG features were released November 6:

  • IBM i 7.2 TR7: SI66149
  • IBM i 7.3 TR3: Target release current SI66150
  • IBM i 7.3 TR3: Target release previous SI66153

More information about these PTFs can be found here.

A trial version of the new version of Rational Developer for i, RDi version 9.6, became available yesterday, Thursday November 15.

The full version will be available on November 21.

This page provides more information about the new version, including where to download it from.

That is it. All of the features and functions promised in the new TRs are now available to us.

Wednesday, November 15, 2017

Limit-ing the number of rows affected by SQL statement

using limit to give the number of results to return using sql

Included in the latest Technology Refresh PTFs, IBM i 7.3 TR3 and 7.2 TR7, is the Limit clause for SQL statements. At the time of my writing this post I cannot find a description of this clause within IBM's KnowledgeCenter, therefore, I have come up with my own:

The Limit clause will affect the number of results given by it. If I use Limit in a Select statement, I will only return the number of rows in the Limit clause. Limit does not replace the FETCH NEXT x ROWS, it does the same.

Below I will give examples of using Limit in Delete and Update statements, and also show how it can be used when defining a cursor in a RPG program.

Wednesday, November 8, 2017

Inserting more than one row at a time

sql insert to add more than 1 record at a time

Most of us who have been programming in RPG for many years are familiar with doing things at a record level. We read, write, update, and delete one record at a time. Moving to SQL allows us to do things a set of data at a time, more than one row/record. We can fetch multiple rows from a table/file, delete more than one row with a single statement, and we can add multiple rows/records to a table/file at once. You can still do row/record level access using SQL, but until you start thinking in sets you do not get to experience the speed of file I/O that you can with using multiple row/record statements.

In this post I am going to give two examples of performing multiple row inserts.

  1. Inserting more than one row/record at a time with just one statement with hard coded values
  2. Inserting just a few rows/records from one table to another, as you would do to create testing data

Wednesday, November 1, 2017

Program stack information via SQL

stack_info to get call stock information using sql

The guys at RZKH applied the latest Technology Refresh PTFs over the weekend, which means I can start playing with the new things. I am starting with the new table function STACK_INFO.

STACK_INFO allows me to use a Select statement to retrieve a row for each entry in the call stack for a specific thread in a job. It supports all types of call stacks: OPM, ILE, Java, PASE, and even the LIC stack. It gives me that same information I could get using the Display Job command, DSPJOB, or the Retrieve Call Stack API, QWVRCSTK. To be able to use this table function my profile must have *JOBCTL special authority, and *SERVICE to get the LIC stack entries.

This table function has two "parameters":

Friday, October 27, 2017

Technology Refresh PTFs out today

ibm i 7.3 tr3 and 7.2 tr7 out now

Today is the day the PTFs for IBM i 7.3 TR3 and 7.2 TR7 are released.

IBM i 7.3 TR3 IBM i 7.2 TR7
Base TR SF99727 level 3 SF99717 level 7
Db2 for i SF99703 level 7 SF99702 level 19

Yes, the Db2 for i is not included in the Base TR PTF.

IBM were a bit cheeky with the some of the other products they announced.

IBM i 7.3 TR3 IBM i 7.2 TR7
RDi November 21,2017
RPG "Available with PTFs delivered later in 2017"

When I finished publishing this post I am going to be asking the IBM i hosting service I use, RZKH, to download these PTFs.

Wednesday, October 25, 2017

Fetching more rows when using multiple row Fetch

getting more rows using a multiple row fetch

In an earlier post I gave an example of how to fill a "load all" subfile using a SQL multiple row Fetch and a data structure. A "load all" subfile can have a maximum of 9,999 records, therefore, what happens if I have more than 9,999 records/rows in my file/table?

I would need to load the next 9,999 records from the point where the first multiple row Fetch finished. Fortunately this is simple using OFFSET in the declaration of the cursor.

Just as a quick reminder: Why would I use a multiple row Fetch to get the data for a "load all" subfile. By using the multiple rows Fetch I would fetch 9,999 records in one file I/O. If I used the RPG read operation code I would need to perform 9,999 I/O operations.

Wednesday, October 18, 2017

Replacing parts of strings using regexp

regexp replace to replace parts of strings and fields

The fifth installment of my examples of the using regular expressions added to Db2 for i as part of IBM i 7.3 TR2 and 7.2 TR6 is going to be about REGEXP_REPLACE. I can already replace parts of strings using SQL's REPLACE and RPG's %SCANRPL, but REGEXP_REPLACE allows me to use regular expressions to find the pattern I wish to replace.

The syntax for the REGEXP_REPLACE is:

  REGEXP_REPLACE(source-string, search-pattern, replacement-string,
                 start-position, occurrence, flags)

Wednesday, October 11, 2017

Using Regexp to substring

regular expression, regexe, to substring

In this third installment of the regular expression functions added to Db2 for i is going to be about REGEXP_SUBSTR. The SQL SUBSTRING and RPG %SUBST built in function need a fixed start and number of positions. The REGEXP_SUBST allows me to use regular expressions patterns to find and substring the pattern I desire.

All of these Regular Expressions functions were added to Db2 for i as part of the 7.3 TR2 and 7.2 TR6 updates. Rather than repeat what I have in the earlier posts, I will refer you to them for more details.

The format for this function is:

  REGEXP_SUBSTR(source-string, search-pattern, starting-position,
                occurrence, flags, group)

Wednesday, October 4, 2017

More information about the new TRs

more links about ibmi 7.3 tr3 7.2 tr7

I saw this on Twitter this morning. Quote from Tim Rowe, business architect for application development on IBM i:

"We will keep updating RPG as long as we keep updating IBM i and we have a long road map"

A day after the announcement of the latest Technology Refreshes for IBM i 7.3 and 7.2 a few more links are coming out from various IBM sources which I consider worthy of sharing:

Tuesday, October 3, 2017

IBM i 7.3 TR3 and 7.2 TR7 announced

ibmi 7.3 tr3 and ibmi 7.2 tr 7 announced

In a blog post published this morning Steve Will, chief architect of IBM i, announced the new Technology Refreshes for IBM i 7.3, TR3, and 7.2, TR7. I am not going to give a long list of links to all the new and enhanced features and functions, the Enhancements pages, listed below, do that. What I will mention are the things that caught my eye.

As with earlier TRs there are many additions and changes to Db2 for i (SQL). These will keep me busy over the next few months writing about them for this blog.