This post is inspired by a situation that I encountered a couple of weeks ago that left me flabbergasted. It started with a phone call from a department head complaining that another department was eating into his department's time to run a report. At first I was confused, and after talking to three department heads I discovered that each one has a time they can run this report. Department A can run the report with any selection criteria they desire from 8:00 – 8:59 AM, Department B can do the same from 9:00 – 9:59 AM, and Department C from 10:00 – 10:59 AM. Anyone can run the report in the afternoon, but only after calling the other departments to make sure they are not running the report at the same time.
Why was this an issue? In this job the programmer who had created it used a work file that was in a production library. Therefore, only one person could run the report at a time to ensure that only one department's data was included in the report. If two people ran the report at the same time the work file could contain mixed data from both jobs. I also found that when the job completed the work file was not cleared, still containing all of the data from the last run, wasting disk space.
The programmer who created these programs has retired so I was not able to ask the question I wanted to: Why?
Looking at library list for the jobs from this application I found that QTEMP was in the library below all of the production libraries. Could this be the reason why?
Never mind the why, the solution was simple. In the job just before the work file was needed I would copy it into QTEMP, and use it there. I could have used a OVRDBF to ensure that the job would know which file to use, but I thought another approach would be easier for other programmers to quickly understand which file was being used: adding the EXTFILE keyword to the file definition in the File Definition specifications. I have discussed this approach in an earlier post, Useful keywords for your F-specs, but I will be repeating parts of it here and giving examples using the free DCL-F too.
I create two work files using the CRTDUPOBJ command, from the same original file:
CRTDUPOBJ OBJ(TESTFILE) FROMLIB(*LIBL) OBJTYPE(*FILE) +
TOLIB(QTEMP) NEWOBJ(TESTFILE1) CST(*NO) +
TRG(*NO)
CRTDUPOBJ OBJ(TESTFILE) FROMLIB(*LIBL) OBJTYPE(*FILE) +
TOLIB(QTEMP) NEWOBJ(TESTFILE2) CST(*NO) +
TRG(*NO)
|
In the example below I have defined in which library to find the files using the EXTFILE. The EXTDESC is only used at compile time. It gives where to find the description for the files, as all the files are based on the same file they all contain the same name. I use this so I do not have to create any files in QTEMP before compiling this program/procedure. The RENAME is needed as the record format name is the same in both the input and output files.
FTESTFILE1 IF E K DISK extfile('QTEMP/TESTFILE1')
F extdesc('TESTFILE')
F rename(TESTFILER:INPUT)
FTESTFILE2 O E DISK extfile('QTEMP/TESTFILE2')
F extdesc('TESTFILE')
F rename(TESTFILER:OUTPUT)
dcl-f TESTFILE1 keyed extfile('QTEMP/TESTFILE')
extdesc('TESTFILE') rename(TESTFILER:INPUT) ;
dcl-f TESTFILE2 usage(*output) extfile('QTEMP/TESTFILE')
extdesc('TESTFILE') rename(TESTFILER:OUTPUT) ;
|
Personally I think it is easier for another programmer to see where each file is used from. And if the same program is called by multiple jobs it prevents the problem of a program missing an OVRDBF and using the wrong file.
The EXTFILE can be used with any file type. I have a suite of programs where I have used the EXTFILE with all data, display, and printer files, therefore, the programs can be called without having to be in the users' library list.
FDISPLAYF CF E WORKSTN extfile('MYLIB/DISPLAYF')
FPRINTERF O E PRINTER extfile('MYLIB/PRINTERF')
dcl-f DISPLAYF workstn extfile('MYLIB/DISPLAYF') ;
dcl-f PRINTERF printer extfile('MYLIB/PRINTERF') ;
|
I went through all the programs in that library and all of the report programs that did not copy use work files in QTEMP have now been changed to use this method, and now any department can run the report when they want.
More information about these keywords can be found on the IBM website:
This article was written for IBM i 7.1, and it should work with earlier releases too.
QTEMP should always be used for work files unless you can't possibly get around creating one. Also if you are using SQL stored procedures you can use QTEMP with the temporary table statement.
ReplyDeleteDECLARE GLOBAL TEMPORARY TABLE Session.yourfile
(field1 CHAR(9) NOT NULL,
field2 CHAR(12) NOT NULL,
field3 CHAR ( 12000 ),
field4 CHAR ( 12000 )
)
With Replace;
Using the temporary table allows you to create work files in stored procedures called from other systems i.e. mssql, etc.
For more details on what Kevin suggest above see here
DeleteConsider adding a record format name so we don't have to all add a RENAME on the F-SPEC for SQL tables. Thanks.
DeleteYes, always add the record format to the SQL table. See the post linked to comment I gave above for details.
Deletejust a hint: We use qtemp files as the intermediate in our client/server applications. Works really great. files are always nice cleaned if the client stops and processing qtemp/files is very fast. As we use a fifth generation development tool. We can link the contents on the screen with the data in the qtemp file. And the client application does not even need to know the database structure beyond.
ReplyDeleteYou can run into authority issues with CRTDUPOBJ. See help text on CRTDUPOBJ command. So either need to ensure these "report work files" are created so any report user can access them or can use
ReplyDeleteCPYF FROM(workfile) TO(qtemp/workfile) FROMRCD(1) TORCD(1), then CLRPFM qtemp/workfile
To create a copy of a files en QTEMP, you should always use CRTDUPOBJ, The library QTEMP, should be "first" in the list, and if it has LF files, you have to use CRTDUPOBJ, too. If you do this, you will never have any problem. The QTEMP, is also very usefull for OnLine programs.
ReplyDeleteI agree with your statement about QTEMP. In my opinion it should always be first in the user part of the library list.
DeleteI am completly agree with Juan Dragone, I use QTEMP in that cases.
ReplyDeleteI have used both techniques. I prefer the CRTDUPOBJ, tho' while unlikely the report file has them, make sure not to duplicate triggers (TRG parm defaults to *YES). But I have worked at clients where authority issues have caused the CRTDUPOBJ has failed and had to resort to the CPYF method.
ReplyDeleteWhile doing some analysis a few months ago, I restored from a journal to files in QTEMP. Chris, a senior walks-on-water AS400 programmer, insisted that files in QTEMP ate up RAM. I suggested to Chris that the machine has single level store so data in RAM could be paged out to disk but Chris, even tho he knew about single level storage, could not see how that might reduce RAM utilization.
ReplyDeleteAnother reason to use CRTDUPOBJ is that CPYF does not work well with SQL defined tables.
If you don't like QTEMP, I just create a library during a job, put the files in it, and then delete the library when the job competes.
ReplyDeleteI do not agree with Juan that QTEMP should be first in library list. The use of QTEMP objects should always be in a controlled and deliberate way. Having QTEMP on top of the LIBL, will give objects in QTEMP top priority.
ReplyDeleteI am aware though, that this is a disputatious subject ;-)
I my world, the deliberate use of QTEMP always warrants a specific override
ReplyDeleteAnd this brings us back to subject of this post...
ReplyDeleteI still think that using EXTFILE is better than OVRDBF for all the reasons mentioned above.
I agree with Paul. If you want to be certain the program uses the copy in QTEMP, use an override. And I question your conclusion that using the EXTFILE keyword would help other programmers understand the program. Yes, it tells them where the file is, but says nothing about its content. I would suggest putting an OVRDBF with the CRTDUPOBJ command in a CL program that calls the RPG would tell the whole story. And since we want the file to be empty, why not a CRTPF instead of CRTDUPOBJ?
DeleteI once got back 15% of DASD years ago when disk space was expensive. A program added a member to work file then filled that member. Three years prior that process blew up. Then they tried it again. Also failed. Then for a third time. Failed again. Not realizing that the work files still had those members and taking up 15% of dasd. These were sales analysis files/members and contained hundreds of thousands of records. When I got there, they were running at 92% dasd utilization. Bearly able to run. The sales dept was taking orders in 30 mins to do what use to take 10 mins in a normal environment at the busiest time of the year. It was a public company and sales were down 6% from a year ago. The company dividend was cut from .03 to zero, Stock which was at $11 went to $3 and eventually most, if not all, of senior management left or was forced out. Company almost went bankrupt. Six months after that I found that work file I was there only 9 months at the time. Existing staff never realized the problem and did not try to identify if there was anything could be done to mitigate the problem. Since then I when appropriate override the duplicated files to QTEMP and do an override. If the job blows up and is cancelled, the job will clean itself up.
ReplyDeleteNever have QTEMP in the top of your library list, and even if your in an environment where you can't change this (as is common in the life of a consultant) never expect it to be first in the list. (One day someone might come to their senses and put QTEMP down to the end of the library list.) I also prefer using the ExtFile keyword on the file specification rather than OVRDBF since not everyone understands all the ramifications of activation groups and it often saves having to create a CL program solely for the purpose of pointing your program to the right objects.
ReplyDelete1. Since there are different opinions to put QTEMP on the top or bottom of the Library List, name the filees in QTEMP with a different name fro the namr in the permanent library.
ReplyDelete2. If using QTEMP is part of the program logic, use EXTFILE. OVRDBF is right to use if it ouside of the program logic, to have a sparate case like testing without changing the program before testing.
3. Since the introduction of activation groups, more caution needs to be done with overrides, in some cases the override may not work the right way. Therefore, whenever it is possible, EXTFILE is a better way to go.