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:

  1. Schema/library
  2. Table/file
  3. Control block size
  CALL QSYS2.OVERRIDE_TABLE(library,table,buffer size)

All of the buffer sizes that the OVRDBF command uses are supported, and one more to reset the control block size:

  • *BUF32KB buffer size = 32KB
  • *BUF64KB buffer size = 64KB
  • *BUF128KB buffer size = 128KB
  • *BUF256KB buffer size = 256 KB
  • 0 (zero) reset to default buffer size

I performed the same test I did with the OVRDBF, same files: BIGFILE and BIGCOPY, and the same number of records: 1 million. This time I chose to do this test embedded in RPG. Why? Just to show that it does not matter which programming language you use.

I have stripped the relevant parts out of my test program to give you an idea of what I did. As this SQL procedure is only available in the same Technical Refresh that brought us all free RPG I am only going to give the code in all free:

01  dcl-pr QCMDEXC extpgm ;
02    *n char(1000) options(*varsize) const ;
03    *n packed(15:5) const ;
04  end-pr ;

05  dcl-s Cpyf char(200)
         MBROPT(*ADD)') ;


08  QCMDEXC(%trimr(Cpyf):%len(%trimr(Cpyf))) ;


Lines 1 – 4 define a procedure for an external program, in this case QCMDEXC. It has two parameters, the first can vary in size and the second does not. As I have used the program's name as the procedure name I do not have to give the program's name in the extpgm parameter on line 1.

On line 5 I have defined the "Copy file" command, CPYF, in a variable.

On lines 6 and 7 I call the OVERRIDE_TABLE procedure. Line 6 for the From file BIGFILE in MYLIB, and line7 for the To file BIGCOPY in QTEMP. In this example I have made the control buffer 256KB for both tables/files.

The CPYF is performed on line 8. The command is QCMDEXC's first parameter. I have used the %TRIMR to remove any trailing blanks, one that follow the command's string. The second parameter I am calculating the length of the string without the trailing blanks.

After the CPYF I change the override on the table/file back to the default, using "0" (zero). Notice as this is a numeric value there are no apostrophes, ', around the zero as there are with the buffer length values.

Having determined the time each CPYF took for each of the control buffer lengths I found it very similar to those where I had used the OVRDBF:

None 25.65688 25.3684
*BUF32KB 24.22434 23.5752
*BUF64KB 23.79016 23.1765
*BUF128KB 23.46741 23.0595
*BUF256KB 23.32999 22.1641

I decided to perform the same test using the SQL INSERT statement, rather than the CPYF:

               FROM MYLIB/BIGFILE ;

What I found was the speed of the execution of the INSERT does not change if the control block size is changed. The INSERT, finishing in an average of 18.944 seconds, was faster than all of the CPYF commands, no matter which control block size was used. This leaves me to conclulde that if you need to copy data from one file/table to another perhaps you should use the SQL INSERT rather than a CPYF.


As I mentioned at the start of this post I have found this enhancement on an IBM i 7.1 server with TR7, but there is no mention of this in IBM's online documentation for 7.1. Therefore, the link is for the OVERRIDE_TABLE command is from their IBM i 7.2 site here.


This article was written for IBM i 7.1 TR7, and later refreshes and releases.

1 comment:

  1. When I have a complex extraction, I make this
    wFlNm = %str( TmpNam( *NULL));
    SqlCmd= 'CREATE TABLE ' + wFlNm + ' LIKE myTable';
    exec SQL EXECUTE :SqlCmd;
    SqlCmd= 'INSET INTO ' + wFlNm + ' SELECT MyTable WHERE ...';
    exec SQL EXECUTE :SqlCmd;
    Open WrkFile; // WrkFile with key word EXTFILE(wFlNm) and USROPN
    This is faster that uses a SQL cursor


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.