What time was that subsystem started?

The question was simple:  Can you tell when a subsystem started?

I am going to give two examples. The first will show what I would do if you asked me what time the QPGMR subsystem was started. I only need to give an answer one time, and I do not need to write a program or code to do this.

In the second example I do want some code to show when the QPGMR subsystem was started. This would be used in a scenario where I would need to know frequently what time it started. I would add it to the Job Scheduler, run the job periodically, and it would send me an email with the date and time it started included within.

 

SQL's whenever gets modernized

Many years ago, and yes it is so long ago I cannot remember exactly how long ago, when I was first embedding SQL into my RPG/400 programs I was introduced to the WHENEVER SQL statement. This statement can be used to cope with SQL errors and warnings returned from the previously executed statement. Personally I do not like what it does, and I do not use it in my programs.

In the announcement for the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, was mention of enhancements made to this SQL statement, which I think has won me over.

Before I explain what these enhancements I need to show the way the WHENEVER statement works without these enhancements. Even after the TRs you can still use the same old way.

The WHENEVER allows you to handle three types of exceptions:

View columns for SQL tables, etc. located in QTEMP

One of the problems of using the SQL Views to list the columns of SQL Tables, Views, etc. is that they do not include objects in the library QTEMP. The latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, has brought us a View that allows me to get to the information of those SQL objects in QTEMP.

Most people use the SQL View SYSCOLUMNS to learn about the columns in their SQL objects. For several IBM i releases has had a similar View SYSCOLUMNS2 that contains all the same columns, even using the same column names, and with ten additional columns not found in SYSCOLUMNS. If you compare the list of columns in both Views the last column common to both is HAS_FLDPROC.

IBM recommends us to use SYSCOLUMNS2 rather than the other view as:

... a query that uses SYSCOLUMNS2 will typically perform better than querying SYSCOLUMNS.

Check if submitted job is still on job queue

The question was: How can I check if job is still on the job queue and if it is held?

Fortunately the solution is simple using SQL. I can check a specific job queue or all for the job. The gotcha is that the job name does need to be unique. If the job was submitted to batch with the default job name, which is the user profile of the job that submitted it, it will be hard to determine which job is the one I want. But as I always submit jobs with a distinct name it is easy. In this example I want to check for a job called TRIAL_BAL that is submitted to the QPGMR job queue, which is in the QGPL library.

I have given examples of using the JOB_INFO SQL Table function in a previous post, but I did not write about how I can use it to get details of jobs waiting in job queues.

JOB_INFO Table function has a parameter called JOB_STATUS-FILTER that can be used to limit the type of jobs returned in the results. When *JOBQ is used it will return all of the jobs waiting on job queues.

If I use this SQL statement I get every job that is waiting on a job queue returned in the results:

Renaming existing table with a short system name

Someone created a SQL DDL Table with a long name and with long column names only, and did not give short or system names. I have discussed in an earlier post how to add the short/system names to the Table and columns when you are creating the table. But in this example the programmer who created just created the Table with the long names only:

01 DROP TABLE IF EXISTS MYLIB.THIS_IS_A_LONG_NAME ; 02 CREATE TABLE MYLIB.THIS_IS_A_LONG_NAME( 03 FIRST_LONG_FIELD_NAME CHAR(7), 04 SECOND_LONG_FIELD_NAME TIMESTAMP 05 ) ;

Line 1: This deletes the Table if it already exists. A nice addition in the latest round of Technology Refreshes is the addition of the IF EXISTS, it prevents the DROP TABLE from generating an error if the Table does not exist.