Wednesday, January 17, 2018

Using RPG data structures with SQL insert and update

rpg data structures with sql inserts and updates

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  ) ;

Wednesday, January 10, 2018

Displaying more than one subfile at a time

more than 1 subfile on a screen

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.

Wednesday, January 3, 2018

Determining length of a string using SQL

calculating length of data within a column

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:

Monday, January 1, 2018

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:

What were your favorites of the 2017 introductions?

If you need a reminder check these links:

Wednesday, December 27, 2017

Signed numeric in CL data structures

handling a cl data structure with signed fields in it

The germ for this post comes from a question I was asked. The questioner was passing a file record as a data structure from a RPG program to a CL program, and when they looked at the data structure the CL program would "really mess up" some of the numeric data structure subfields. After further questioning I discovered the "messed up" fields we all signed numeric fields. Once I established that I knew what the problem was, and came up with a simple solution.

Let me show what the problem is, and then the solution I came up with. Let me start with the file, you will not be surprised to discover that I called it TESTFILE.

Wednesday, December 20, 2017

Using alias for data structure subfields

externally described data structures using alias subfield names

Have you ever used a file or table to externally define a data structure, and then wondered how to use the alias field/column names rather than the short ones?

Fortunately it is simple to do. But, before I start with showing the file and table I will be using in these examples. Let me start with the file, TESTFILE:

A          R TESTFILER
A            F001           7A         ALIAS(FIRST_FIELD)
A            F002           7P 2       ALIAS(SECOND_FIELD)
A            F003            L         ALIAS(DATE_FIELD)
A            F004            T         ALIAS(TIME_FIELD)

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.