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:
- Schema/library
- Table/file
- 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)
inz('CPYF FROMFILE(MYLIB/BIGFILE) TOFILE(QTEMP/BIGCOPY) +
MBROPT(*ADD)') ;
06 exec sql CALL QSYS2.OVERRIDE_TABLE('MYLIB','BIGFILE','*BUF256KB') ;
07 exec sql CALL QSYS2.OVERRIDE_TABLE('QTEMP','BIGCOPY','*BUF256KB') ;
08 QCMDEXC(%trimr(Cpyf):%len(%trimr(Cpyf))) ;
09 exec sql CALL QSYS2.OVERRIDE_TABLE('MYLIB','BIGFILE',0) ;
10 exec sql CALL QSYS2.OVERRIDE_TABLE('QTEMP','BIGCOPY',0) ;
|
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:
| Control buffer |
OVRDBF Seconds |
OVERRIDE_TABLE Seconds |
| 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:
exec sql INSERT INTO QTEMP/BIGCOPY
SELECT FLD1,FLD2,FLD3,FLD4,FLD5,FLD6,FLD7,FLD8,FLD9
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.
When I have a complex extraction, I make this
ReplyDeletewFlNm = %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