cpyfrmimpf command issue

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)
robin_smith123Asked:
Who is Participating?
 
tliottaConnect With a Mentor Commented:
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
 
robin_smith123Author Commented:
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
 
momi_sabagCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
robin_smith123Author Commented:
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
 
robin_smith123Author Commented:
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
 
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
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
 
robin_smith123Author Commented:
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
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
robin_smith123Author Commented:
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
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
robin_smith123Author Commented:
If the ERRRCDOPT parameter is specified is there any chance that the command will look for printer to display the error messages
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.