Tuesday, June 30, 2015

Today is Leap second day

leap second day time adjustment

Today is a special day for two reasons:

  1. My wife's birthday
  2. Leap second day

A Leap second is one that is added to a day to keep UTC (clock) time synchronized with solar time. As the earth's rotation is slowing these seconds need to be added to keep the two together. Tonight that extra second will be represented by the time 23:59:60.

Wednesday, June 24, 2015

Select the number of rows from SQL's result set

sql select with fetch rows only dspobjd

There are times when I do not want all of the rows/records from a table/file just, for example, the 100 with the largest value in a variable. I could use a "Copy File" command, CPYF, to copy on the first 100, but I cannot sort with a CPYF to select the largest. Fortunately SQL's Select does offer me a way to do this.

Today did not start well as the development server reached critical storage, 93%+ of DASD was being used. After doing some basic deleting and purging of a few files I was not able to lower the percentage of DASD more than 1%, which was not enough. What I need to do was to identify the largest object on this IBM i and determine if they could be deleted or their contents purged. But how to find those big objects?

Wednesday, June 17, 2015

Overriding control block size using SQL

override_table is like ovrdbf but is used in sql

Last week I discussed how to use control block size in the "Override with database file" command,OVRDBF, see here. In this post I am going to introduce the SQL procedure OVERRIDE_TABLE. Unlike the OVRDBF command this procedure can only do one thing, set the control block size for a table/file.

The OVERRIDE_TABLE appears to have introduced in IBM i 7.1 TR7. It is not mentioned in the IBM i 7.1 KnowledgeCenter website, but is in the 7.2 site.

This procedure has only three parameters:

Tuesday, June 16, 2015

OCEAN Technical conference 2015

I have just signed up to attend this year’s OCEAN IBM i Technical Conference on July 17, 2015. I went to last year's and it was great to meet other IBM i professionals from Southern California, the experts who came to give the presentations, and I learned new stuff.

If you are in So Cal I recommend that this is the event to attend. You can learn more about the conference at OCEAN’s website here.

I look forward to seeing you there.

Wednesday, June 10, 2015

Using control blocking to improve database performance

ovrdbf control blocks to speed up copy files

IBM i 7.1 TR7 saw an addition to the "Override with data base file" command, OVRDBF, the ability to set the control block size when performing file I/O. By increasing the control block size I can increase the memory allocated to the file's I/O buffer, which will increase the speed of I/O to the file. The enhancement is to the second element of the "Limit to sequential only" parameter, SEQONLY.

Limit to sequential only:
  Sequential only  . . . . . . > ____        *NO, *YES
  Number of records  . . . . . > _________   Number, *BUF32KB, *BUF64KB...

Wednesday, June 3, 2015

Using SQL to get information from Job Logs

getting job log data using sql using joblog_info instead of rcvmsg

I know I have written a lot recently about SQL, but IBM has added a lot of great views that allow you to access information easily. A good example is today's post: how to get information from a job log using SQL. Prior to this if you need to get information from the job log you had to use the RCVMSG command or the "List Job Log Messages" API, QMHLJOBL. Now I can get the information I want just using a SQL Select statement.

The JOBLOG_INFO table function was added to IBM i 7.2 and 7.1 TR9. It returns one row for each job log message, and what I really like about it is I can choose what information to return by using a WHERE clause in the Select statement.

Monday, June 1, 2015

Analyzing recursive data with SQL

This presentation was made by Birgitta Hauser, IBM i SQL expert, to Common Europe on May 28.