Wednesday, December 13, 2017

Using SQL to get information about ASP capacity

sql view asp_info get data about asp

Someone asked me if there was an easy way to monitor the available storage space in their Auxiliary Storage Pools (ASP)? I have to admit this is something I have not been asked before, and not something I worried about. I knew we were getting close to critical storage when I would receive the message from the system operator asking what could be deleted from the system before that was reached.

Fortunately in the latest round of Technology Refreshes, IBM i 7.3 TR3 and 7.2 TR7, a new Db2 for i View was added to return information about the ASP.

The new View, ASP_INFO, is one of a pair of additions to Db2 for for ASP monitoring. Like all the recent additions to Db2 for i this View is found in the library QSYS2. I am not going to list all the columns here, as I will only using a few in this example. If you want a list of them all you can go to IBM's KnowledgeCenter here.

Wednesday, December 6, 2017

Resolving "array X not defined or not usable"

overcome the array not defined or not usable compile error

I have shared my example of using a multiple row fetch as an efficient way to get multiple records/rows at once, and place the fetched data into a data structure array. To save myself time, and effort, I don't enter all the fields from the file/table as subfields, I use the file/table to externally define the data structure. Many of my colleagues where I work, and many of you, use this method as it makes things like programming subfiles so easy.

Every once in a while someone comes to me with, what I can only describe as a cryptic, message in the SQL precompile listing:

Wednesday, November 29, 2017

Getting data about jobs using SQL

JOB_INFO table function in place of WRKSBMJOB, WRKUSRJOB, and WRKSBSJOB

This UDTF, User Defined Table Function, slipped under my radar when it was introduced last year as part of Technology Refreshes for IBM i 7.3, TR1, and 7.2, TR5. It is as if the commands WRKUSRJOB (work with user jobs), WRKSBMJOB (work with submitted jobs), and WRKSBSJOB (work with subsystem jobs) were all rolled into one to create the UDTF JOB_INFO.

Wednesday, November 22, 2017

SQL View for information about Job Queues

using sql db2 for i view to get job queue information

Another of the new Views added to the Db2 for i as part of the new Technology Refreshes, 7.3 TR3 and 7.2 TR7, is one that lists all of the job queues on your IBM i.

The JOB_QUEUE_INFO View will return a row for each job queue. It is essentially the equivalent of the Work with Job Queue command, WRKJOBQ, and the Retrieve Job Queue Information API, QSPRJOBQ. There are over 60 columns of information I can get in my results, don't worry I am not going to list them all here. IBM does a great job of describing them all here, I will only describe the columns I use in my examples.

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.