Getting the library list from a Job Description

The germ for this post came from this question:

How can get the Library List from a Job Description?

I can use the DSPJOBD command to see the Job Description's library list, alas I can only display or print it. This does not make it easy for me to use in a program.

DSPJOBD JOBD(MYLIB/MYJOBD) Display Job Description Job description: MYJOBD Library: MYLIB Initial library list: Sequence number Library 10 QTEMP 20 MYLIB 30 ANOTHERLIB 40 QGPL

SQL Views to list SQL programs and the statements within them

I found these two Views when going through the list of Db2 for i Views on Scott Forstie's Db2 for i poster (if you have not printed this out and stuck it to your wall do so now!). Both contain information about programs containing SQL statements:

1. SYSPROGRAMSTAT:  One row for each program that contains a SQL statement
2. SYSPROGRAMSTMTSTAT:  One for each SQL statement in a program

There are a couple for gotchas I found working with these Views, which I was disappointed by, I will mention these shortcomings when I discuss the View in detail below.

Using RPG data structures with SQL insert and update

As I can fetch rows using SQL from a table, or file, into a data structure. I was asked:

Can I insert or update rows using a RPG data structure?

The answer is a definite "Yes".

In these examples I am going to be inserting and updating the following table:

01 CREATE TABLE QTEMP.TABLE1 ( 02 COLUMN1 CHAR(10) NOT NULL, 03 COLUMN2 DECIMAL(11,2), 04 COLUMN3 DATE, 05 COLUMN4 TIMESTAMP 06 ) ;

Displaying more than one subfile at a time

I decided to write the post after being asked if I had an example of having two subfiles on the same screen. I did a quick search using Google and was unable to find what I considered a good example I felt comfortable sharing.

In this example I am going to show two subfiles horizontally (i.e. one on top of the other). I could have shown them vertically (next to one another), or even had more than two. The principals I show here can easily be adapted to fit either of those other scenarios. The most important thing to remember is that each set of subfile and subfile control record format cannot overlap another.

To reduce the size of the display file source code, shown here, I have removed all unnecessary color and display attributes codes that I use in the screens I build. I am going to show my display source in multiple parts to make it easier to understand what each record format is for. Let me start with the file level keywords.

Determining length of a string using SQL

This is just a quickie in response to a question I was asked: How can I determine the length of string within a column using SQL?

This is very simple as there is Db2 for i scalar function to do this LENGTH. But before I do that let me explain why using this scalar function is simpler than doing the same using RPG.

I have a simple DDL table of just one column. There is no "NOT NULL", therefore, this column can be null.

CREATE OR REPLACE TABLE QTEMP.TESTFILE ( FIELD1 VARCHAR(80) ) ;

And it contains the following data:

Welcome to 2018

At the start of a new year I am always excited about what it will bring, and grateful what the old year brought.

The past few years have been a really exciting time to be involved with the IBM i world. In my opinion the twice yearly Technical Refreshes has allowed IBM to bring us new things without us having to go the through upgrading to a new release. My favorites of last year's, 2017, introductions were additions to Db2 for i:

• JOB_QUEUE_INFO
• LIST_AGG
• MESSAGE_QUEUE_INFO

What were your favorites of the 2017 introductions?

If you need a reminder check these links: