Discover size of library using SQL

Someone asked me if there was a way, using SQL, to determine the size of a library?

This is a trick question as few people realize that the size of a library and the total size of all the objects within are different.

The questioner clarified that they wanted to know the total size of all of the objects in the library. But I gave him examples showing the difference between the two.

He was using the Display Library command, DSPLIB and was trying to find a way to get the same total from this command in a way it could be retrieved programmatically.

In the days before all the cool Db2 for i views, table functions, etc. I would have used the Display Object Description command, DSPOBJD. To get the size of the library I would have used the following:

Presenting at Ocean UG

Tomorrow, Tuesday April 16, I will be one of four presenters at the Ocean Users' Group meeting. This is the first time I will be presenting to an IBM i user group, and I am looking forward to it.

If you can attend the event is at the National University in Costa Mesa. Dinner is at 5:30 PM, with the presentations starting at 6:30 PM.

Validating SQL statements

This post is about an API I stumbled across when looking for another. The Syntax Check SQL Statement API, QSQCHKS, will validate any SQL statement passed to it. I can see this being useful when I build a SQL statement in a variable before executing it. If I was to validate the statement it would allow me identify any errors I may have made.

When I show my code, below, you might think it looks complicated, with several strangely names data structures, which is why I have placed it in its own procedure with just one parameter passed to it and one returned. K.I.S.S. .

I have created two procedures to achieve my goal: to return a character variable containing the SQL error message's text. To this end I have created two procedures, the first in RPG, the second in CL. I could have combined them into a single service program, but as this is an example I left them as modules, RPGMOD001 and CLMOD001, to be bound to the calling program at program creation (compile) time.

Happy St Isidore of Seville day

Today is the day of the Catholic church's patron saint of computers, computer programmers, and the internet. St Isidore of Seville was a sixth century scholar and bishop who was given this special role in 1997 by Pope John Paul II.

Therefore, if you are having one of those days where every is just going to pieces it might be time to ask for divine help with a quick prayer to St Isidore.

You can learn more about this saint clicking this link here.

Using FTP with IFS files

In previous posts I have written about using FTP to copy files from one IBM i partition to another. Almost all of the examples I gave were for transferring of a file, or files, in what I call the "IBM i environment", good old libraries and files.

Earlier this week one of my colleagues came to me and asked if there was a way to use FTP to copy a file in the IFS of one IBM i to the IFS of another partition. I looked back at the posts I had written and realized that I did not have a good example of how to do this. I thought I would share what we played with so he could understand what to do.

Before we get started I need to explain that FTP with IBM i has two naming conventions for the file's path:

Delaying a job for a fraction of a second

Someone asked me if there was a way to pause a job for a fraction of a second. Their colleague had advised them to create a Do-loop that would be performed a certain number of times and that would be "good enough". I was very pleased to learn that the person who asked this question was not satisfied with that answer, and correctly thought that there must be a better way.

I am sure we are all know of the Delay Job command, DLYJOB, but it will only delay a job for whole seconds and not fractions. Fortunately IBM i has two external C functions that can be used to delay a job:

API to check if debug is active

There are some APIs I stumble across and I am left wondering why they were created? The API I will be writing about today, QteRetrieveDebugAttribute, is one of those. This API is used to determine what the debug attribute values are for a job when it is being debugged. I am not going to list which debug attributes they are as it is not relevant to what I am trying to determine. If you are interested in learning what they are click on the link to IBM's documentation at the bottom of this post.

In this example I am just interest to know if the job is being debugged.

I am going to have a procedure within a module call the API, this way I can either bind it into the calling program or included it in a service program, which is my preference. Let me start with the module:

IBM i 7.4 section now in KnowledgeCenter

In January I found signs that a new release of IBM i would happen this year. This week I found another sign when I discovered that the KnowledgeCenter now has a section for IBM i 7.4.

Alas, it is just a "place holder", with no information is within it, but it is another sign of the pending new release.

If you are interested in visiting the new section click here.

Using SQL to check if a batch job is active

A colleague asked me if there was an easy way, using SQL, to tell if a job was active in a subsystem. This subsystem, I am going to call it TESTSBS, should contain two jobs that remain active all day, copying and sending data between various IBM i partitions. During the day-end process the jobs are ended, and then resubmitted after the backups.

Recently there have been problems with these jobs. If one of these jobs errored the system operators would answer the message with "C", which would end the job and the data would not be transferred until someone else noticed. Or the jobs would not end during the day-end process, therefore, after the backups completed two new versions of the job would be submitted resulting in four jobs, two of each.

My colleague had looked into ways of determining which jobs were active in the subsystem. During the day if one of the jobs was missing it could be resubmitted. After the backup finished only if the job was not active would it be submitted. All of the methods she had come up with to determine if the jobs were active were, in her opinion, too complicated. Which is why she came to me.

Does end of Google+ mean the end of this website?

Over the past week I have received several communications asking if the end of Google+ means that this site will also be closing.

This site is hosted on another Google product, Blogger. Google's commitment to the Blogger platform continues unabated. There have been some minor changes to Blogger because of Google+'s demise, but none of these will effect your viewing pleasure of this website.

If you have included me in your Google+ circle and want to continue that relationship feel free to send me a Facebook friend request.