The best way to find what is using all the disk space

Almost by accident I have started discussions on how to determine the largest objects on your IBM i server. I have given examples of how:

• Identify the 250 biggest objects using the DSPOBJD command, followed by a SQL statement, see here.
• Retrieve the number of deleted records/rows in files/tables, see here.

Discovering the number of deleted records in a file

In last week's post, here, I gave an example of how I determine the 250 biggest objects on my IBM i server. I created the information about the objects by using the Display Object Description command, DSPOBJD, and mentioned if anyone knew of a better way to get the same information to contact me.

Select the number of rows from SQL's result set

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?

Overriding control block size using 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:

Using control blocking to improve database performance

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...

Using SQL to get information from Job Logs

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.

Analyzing recursive data with SQL

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

New TRs available today

The latest Technology Refreshes for IBM i, TR2 for 7.2 and TR10 for 7.1, are available today to download. You can learn more about them from IBM's developerWorks web site here.

You can learn more about what is in these Technology Refreshes from these posts:

• More on IBM i 7.2 TR2 and 7.1 TR10
• IBM i 7.1 TR10 and 7.2 TR2 announced at Common
• IBM i 7.2 TR2 and 7.1 TR10 DB2 for i enhancements

Getting information about Views and Indexes

We have been given a number of SQL views that allow us to harvest information from our IBM i's, and many are unaware of their existance.

With the move from DDS files to SQL tables, indexes, and views I needed to update my toolset to be able to list information about the last two in a quick to run and easy to read way. Fortunately I can use some of these views to do so:

Creating derived columns in SQL View

A SQL View is a virtual table. Its columns can come from one or more SQL tables or DDS files. The data in the View is not stored in its own database object. When the View is used the Select statement is executed to produce the data. You can also create, what I call, "derived" columns. The "derived" columns do not exist in the original tables/files, they are calculated when the Select statement is run.

In the examples I am going to show how I can code "derived" fields in a View to:

Converting DDS files to SQL DDL using SQL

In my last post I wrote about how to convert DDS files to SQL DDL using the QSQGNDDL API, you can read it here, and I mentioned that in IBM i 7.2 and 7.1 TR9 there was an alternative, the GENERATE_SQL SQL procedure.

Having used both I prefer GENERATE_SQL as it is simpler to use, just one statement in STRSQL, and it has the ability to use wild cards for mass conversions. Many of its parameters are in English, which makes it easier for others to understand my code. The has six mandatory parameters:

API to convert files to SQL

As part of the IBM i 7.1 release IBM notified us all of their intentions not to perform any future enhance to DDS for data (physical and logical) files, and are encouraging us to migrate those files to their equivalents in SQL (tables, views, and indexes). For many of us this is an overwhelming proposition as the applications we support contain many thousands of files. To convert these by hand would take a very long time. Fortunately IBM has an API, 'Generate Data Definition Language' (QSQLGNDDL), to assist.