Alternate command for the CPYF
Last Post 28 Aug 2009 05:55 AM by Satid Singkorapoom. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
trksagar
New Member
New Member
Posts:7

--
27 Aug 2009 08:11 AM
I have created cl program, in that cl program i have used two CPYF statements because of these two statements it is taking too much of time to complete. Is there any alternative way to get eecuted the program in less time with out using CPYF statements. statements used in CL program: CPYF FROMFILE(&SOURCE_LIB/TXNCRMWRK5) + TOFILE(&TARGET_LIB/TXNPHY01CR) + MBROPT(*REPLACE) INCREL((*IF MAB0DT *NG + &BUSDATE)) CPYF FROMFILE(ISS04PDLIB/TXNPHY01) + TOFILE(&TARGET_LIB/TXNPHY01CR) + MBROPT(*ADD) INCREL((*IF MANSST *EQ 'SL') + (*AND MAB0DT *EQ &BUSDATE))
pramendrapandeya
New Member
New Member
Posts:7

--
27 Aug 2009 09:51 AM Accepted Answer
why do not you use SQl fro copying the data???

INSERT INTO FirstTableName (columnname1, columnname2, columnname3)
SELECT *
FROM SecondTable
WHERE yourcondition 
Pramendra Pandeya
Satid Singkorapoom
Senior Member
Senior Member
Posts:2990
Avatar

--
27 Aug 2009 10:13 AM Accepted Answer
You can create an SQL Stored Procedure and use it for your purpose. The SQL statements in its body that you can use to accomplish this may look like this: [FONT="Courier New"]BEGIN SET SCHEMA = TARGETLIBVAR; DELETE * FROM TXNPHY01CR; INSERT INTO TXNPHY01CR (SELECT FROM TXNCRMWRK5 WHERE MAB0DT < BUSDATEVAR UNION ALL SELECT FROM TXNPHY01 WHERE MAB0DT = BUSDATEVAR AND MANSST = 'SL') ; END[/FONT] Using SQL and proper indexes on the source files can help speed up the read operations from the source files for the copy process. In your case, you need to create an index on the columns MANSST + MAB0DT of the file TXNPHY01. Another index for the column MAB0DT of the file TXNCRMWRK5. Use SQL statement CREATE INDEX to do this. I'm not sure if CPYF with INCREL can use the indexes or not. I suspect it cannot. If the target file TXNPHY01CR receives a lot of added records from this copy operation (say, more than 100-200MB each time), you can also try preallocating the disk space of the target file before doing the copy with CHGPF command. This should help preventing OS from doing multiple on-line disk allocations during the write operation to the target file and should help speed up the write. This article explains how to do it: Allocating for High-Velocity Inserts on DB2 UDB for iSeries by Kent Milligan.
Harry Edmondson
Veteran Member
Veteran Member
Posts:780
Avatar

--
27 Aug 2009 11:30 AM Accepted Answer
If the source file is indexed, and you don't care about the record order in the target file, you can try FROMRCD(1). This will have CPYF use arrival sequence when reading the source.
Brian Rusch
Advanced Member
Advanced Member
Posts:549

--
27 Aug 2009 04:21 PM Accepted Answer
A couple of other suggestions you can play around with: 1. Override the files before the copy commands to bump up the number of records read and written per disk access.
OPNQRYF FILE((&SOURCE_LIB/TXNCRMWRK5)) QRYSLT('MAB0DT <=' *BCAT &BUSDATE)
CPYFRMQRYF FROMOPNID(TXNCRMWRK5) TOFILE(&TARGET_LIB/TXNPHY01CR) MBROPT(*REPLACE)
CLOF OPNID(TXNCRMWRK5)

OPNQRYF FILE((ISS04PDLIB/TXNPHY01)) QRYSLT('MANSST = "SL" *AND MAB0DT =' *BCAT &BUSDATE)
CPYFRMQRYF FROMOPNID(TXNPHY01) TOFILE(&TARGET_LIB/TXNPHY01CR) MBROPT(*ADD)
CLOF OPNID(TXNPHY01)
Or you could use a combination of the two. In my tests, some runs were faster, some were about the same, depending on the selection of data.
trksagar
New Member
New Member
Posts:7

--
28 Aug 2009 05:29 AM Accepted Answer
why do not you use SQl fro copying the data???

INSERT INTO FirstTableName (columnname1, columnname2, columnname3)
SELECT *
FROM SecondTable
WHERE yourcondition 
Pramendra Pandeya
Hi Premendra... In the first table data is presented already,no need to insert the data into first file. The case is i have to copy the data from first table to second table in the Cl program. because of this im getting performence problem suggest me how to avoid this....
Satid Singkorapoom
Senior Member
Senior Member
Posts:2990
Avatar

--
28 Aug 2009 05:55 AM Accepted Answer
What are the sizes of the two source files? And do you have a rough idea how much percentage of records are selected from the source files TXNCRMWRK5 and TXNPHY01 (compared to the total records in each of the two files) to be written to the target file? If less than 60%, you can speed up the read by using SQL or Query (as per Mr. Rusch's) and creating the indexes that I suggested above. If you must use CL and not SQL, then Mr. Rusch's query sample is the way to go. If the amount of data to be written to the target file is also large, you can speed up the write by the file space preallocation that I mentioned.
pramendrapandeya
New Member
New Member
Posts:7

--
28 Aug 2009 06:49 AM Accepted Answer
Hi Premendra... In the first table data is presented already,no need to insert the data into first file. The case is i have to copy the data from first table to second table in the Cl program. because of this im getting performence problem suggest me how to avoid this....
Yeah!! I got you but if u just replace FirstTableName to SecondTable then it will insert the data into second table. :) As for the perforamance view 2nd suggestion is u want to use only Cl program Rusch answer will suit your problem. Hcedmondson suggestion may suit you ...if it is indexed try CPYF instead.. Pramendra Pandeya
Satid Singkorapoom
Senior Member
Senior Member
Posts:2990
Avatar

--
28 Aug 2009 07:26 AM Accepted Answer
I found more information on CPYF performance: - Overlooked features of the Copy File Command by Tim Granatir
CPYF FROMFILE(FILE1) TOFILE(FILE2) FROMRCD(1): If you have a keyed physical file, this will cause the copy file command to ignore the key values and copy by relative record number instead of using the index, which will result in substantially shorter times to perform the copy. I do not put keys on physical files, but if you do and you use this technique be aware that the records in your to file will be in relative record number order and not ordered by key. Also, copying by relative record number is sometimes a good way to isolate blocks of records for debug or test data purposes.
- Faster CPYF file command by Richard McDermott
You are not authorized to post a reply.

Acceptable Use Policy