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":