[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

cpyfrmimpf command issue

Posted on 2008-11-04
15
Medium Priority
?
1,001 Views
Last Modified: 2013-12-06
I m using following command for importing data from a flat file to a table ...but the date is not getting inserted in the same order as in the flat file.I want the data to b in same order as in the falt file.Order means:order of rows.Pls help.



CPYFRMIMPF  FROMSTMF('folder/filename') STMFLEN(*TOFILE) TOFILE(schema/tablename *FIRST) MBROPT(*ADD) RPLNULLVAL(*NO) RMVBLANK(*LEADING) RCDDLM(*ALL) DTAFMT(*DLM) STRDLM('"') FLDDLM('|') DECPNT(*PERIOD) DATFMT(*ISO) TIMFMT(*ISO) ERRLVL(*NOMAX) ERRRCDOPT(*REPLACE)
0
Comment
Question by:robin_smith123
13 Comments
 

Author Comment

by:robin_smith123
ID: 22883576
I m using following command for importing data from a flat file to a table ...but the data is not getting inserted in the same order as in the flat file.I want the data to b in same order as in the falt file.Order means:order of rows.Pls help.



CPYFRMIMPF  FROMSTMF('folder/filename') STMFLEN(*TOFILE) TOFILE(schema/tablename *FIRST) MBROPT(*ADD) RPLNULLVAL(*NO) RMVBLANK(*LEADING) RCDDLM(*ALL) DTAFMT(*DLM) STRDLM('"') FLDDLM('|') DECPNT(*PERIOD) DATFMT(*ISO) TIMFMT(*ISO) ERRLVL(*NOMAX) ERRRCDOPT(*REPLACE)
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22883617
what do you mean not getting the data in the same order?
how do you know?
why do you care?
do you have a clustering index?
0
 

Author Comment

by:robin_smith123
ID: 22883634
actually i used load command on windows to do the same operation.
in windows the command loads the data in the table .

Actually data in same order means ,the first row in flat file is coming as 5 row or some some other row number.I want exactly the same rows in the table also as in flat file
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:robin_smith123
ID: 22883871
Pls tell me how can i ensure that the ordering of rows in table is same as in flat file when i use cpyfrmimpf command
0
 
LVL 27

Accepted Solution

by:
tliotta earned 1000 total points
ID: 22888773
robin_smith123:

Please provide the file description for [schema/tablename]. Without knowing how that table is described, we cannot tell why the row order should change. There is no reason that the physical sequence would change unless the file has a description that causes it to change. Various referential constraints -- unique keys, for example -- might be involved.

How have you determined that "the first row in flat file is coming as 5 row"?

Tom
0
 
LVL 36

Assisted Solution

by:Gary Patterson
Gary Patterson earned 1000 total points
ID: 22889133
Robin,

If you are loading rows to an existing table, AND that table contains deleted rows, AND the table is configured to reuse deleted records, that would account for the difference in sequence.

Otherwise it is probably just related to how you are viewing or accessing the table after it is loaded.

Use the DSPFD command to determine if the tabel ise configured to "reuse deleted records".  If it is, you can use the CHGPF command to change this parameter to NO.  this will force new records to be added to the end of the table.  The drawback is that you will tend to accumulate deleted space in the table in teh future, and you may occasionally need to reorganize the file to compress out deleted records.

If the file is not set up to reuse deleted records, then you are probably viewing the file through an index.  As several others have asked above, how, exactly (what command or program) are you viewing the file that makes you believe that the file is out of order?

- Gary Patterson
0
 

Author Comment

by:robin_smith123
ID: 23070571
CPYFRMIMPF  FROMSTMF('folder/filename') STMFLEN(*TOFILE) TOFILE(schema/tablename *FIRST) MBROPT(*ADD) RPLNULLVAL(*NO) RMVBLANK(*LEADING) RCDDLM(*ALL) DTAFMT(*DLM) STRDLM('"') FLDDLM('|') DECPNT(*PERIOD) DATFMT(*ISO) TIMFMT(*ISO) ERRLVL(*NOMAX) ERRRCDOPT(*REPLACE)

wht doz "ERRLVL(*NOMAX)" and  "ERRRCDOPT(*REPLACE)" attributes do in the above command,I havent created any errorrecord file.Do i need these attributes in the command.can some tell me the use of these 2 attributes clearly.
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 23071213
ERRLVL allows you to set the number of recoverable errors that can be encountered without terminating the import process.  NOMAX says to continue the import no matter how many errors are detected.

ERRRCDOPT is only used with a error record file, so you can ignore this parameter if you arent using an error file.  *ADD causes error records to be appended to the end of the existing error record file.  *REPLACE causes the error record file to be cleared before new records are written.

All of this information is available in the CPYTOIMPF command's help screens, just like it is for almost all AS/400 commands.  On a command line, type "cpytoimpf". and press function key F4 (Prompt) to prompt the command.  Put the cursor in the field that you need help with and press the F1 (Help).

You can also find the command documentation online in the IBM Information Center for your version:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/cl/cpyfrmimpf.htm

- Gary Patterson

0
 

Author Comment

by:robin_smith123
ID: 23071836
i m not making any error record file but i m using ERRRCDOPT attribute,will it stop the command.What will be the effect of using this attribute without error record file
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 23071961
ERRRCDOPT is only used with an error record file, otherwise it is meaningless.  Don't specify it unless you also specify an error record file.  I don't know if it will cause an error or not.  If you are curious, test it and see what happens.  

Most likely, if you specify it, it will be ignored, but it is a bad practice to specify dependent parameters like this when you know they aren't going to be used.

- Gary
0
 

Author Comment

by:robin_smith123
ID: 23076282
If the ERRRCDOPT parameter is specified is there any chance that the command will look for printer to display the error messages
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 23078218
Not if you are using commandCall from Java.  

Error messages are handled in several different ways depending on how you execute a command:

1) Interactively from a command line - error messages are typically displayed in the message area at the bottom of the screen, and written to the current job's job log (DSPJOBLOG).

2) Command submitted to batch using the SBMJOB command - Error messages are written to the batch job's job log.  The job log is spooled to the job log output queue.  This is the closest thing to "looking for a printer" that may happen.

3) Command executed from Java using CommandCall - Error messages can be retrieved using the commandCall.getMessageList method:  

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/rzahh/javadoc/com/ibm/as400/access/CommandCall.html

There are other ways to execute commands, of course, including CL programs, QCMDEXC API, DDM, RUNRMTCMD, etc, and each have their own method of displaying or handling error messages.

- Gary Patterson
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 24093794
My answer, #22889133, provides the top two major reasons why the records might not appear in the same order.  I recommend selecting it as a correct answer, but it does not really matter to me.

- Gary Patterson
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
The article covers five tools all IT professionals should know about, as they up productivity by a great deal!
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month18 days, 23 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question