Unconfigured Ad Widget

**GlenKerner** · 08-03-2004, 11:52 AM

Exporting data from iSeries to PC database

The simplest way is to create the file the way you want it (including the | and /) into a flat file. Then use CPYTOIMPF to copy it to wherever you want (as long as the server is in QNTC). I have a file I create that is multiformated to send to one of our vendors. I would ask if you could send a CVS file (Oracle should be able to handle it).

**michaelsoucy@netzero.net** · 08-03-2004, 12:28 PM

Exporting data from iSeries to PC database

I had thought of using the CPYTOIMPF command, but the command won't allow me to specify a "/" as the end of record parameter (RCDDLM) for a stream file. I'm pretty sure they won't accept a CSV file, and that it has to be in the format they have defined. I had thought about writing an RPG program and use the Unix type APIs to write a file to the IFS. I wasn't sure how to output any of the numeric fields in the stream file, and I wasn't sure what would happen when I outputted one of the Null fields. I don't know Java so, that option is out. Any other ideas?

**GlenKerner** · 08-03-2004, 12:36 PM

Exporting data from iSeries to PC database

It really surprises me that the State agency is requiring one specific format. Normally they allow comma delimeted files. I would press them a little further. The layout you are describing sounds to me like a custom program of theirs not a requirement of Oracle.

**buck.calabro@commsoft.net** · 08-04-2004, 06:00 AM

Exporting data from iSeries to PC database

> I wasn't sure how to output any > of the numeric fields in the stream > file, You must check with the spec given you by your trading partner, but probably edited is the right answer. That is (in RPG) something like %editc(amount: 'J') > and I wasn't sure what would > happen when I outputted one > of the Null fields. Again, the spec is the answer, but my experience is that for NULL fields, you output nothing. In your example, that would be two adjacent pipes; something like |12,345.67|JOHN||SMITH| (middle name NULL) --buck

**R.Cozzi** · 08-04-2004, 06:06 AM

Exporting data from iSeries to PC database

If you give me a short example of the output data you expect, I can test the RPG xTools to see if they have this capability. There is the CPYTOCSV() interface that allows customization. I could see if I can fake it out so that it writes the tokens/symbols you need.

**Guest.Visitor** · 08-04-2004, 08:05 AM

Exporting data from iSeries to PC database

This is the document I produced to copy to the IFS etc hope it might help in some way Files and directories created within the IFS are treated and objects and there are a new set of commands to manipulate these objects All objects within ROOT should use these commands files within QDLS should be accessed using the current methods. For example to copy an object within DATATRAN the CPY command should be used CPY OBJ('DATATRAN/TEST.DOC') TOOBJ(TEST.TXT) OWNER(*KEEP) note blank out the TODIR field To copy to a different directory CPY OBJ('DATATRAN/time.old') TODIR(restore) OWNER(*KEEP) To rename an object RNM command should be used RNM OBJ('DATATRAN/time.old') NEWOBJ(time.txt) To move an object MOV command There is a full set of commands which can be used by using the command GO FILESYS . When objects are created from Lotus NOTES their owner is QNOTES - you will have to grant authority for these objects to be used. The command for this is CHGAUT CHGAUT OBJ('RESTORE/TIME.TXT') USER(IPTS) DTAAUT(*RWX) OBJAUT(*ALL) CPYTOIMPF FROMFILE(EAOPS/EANSF4) TOSTMF('datatranmag.txt') MBROPT(*REPLACE) STMFCODPAG(*STDASCII) RCDDLM(*CRLF) DTAFMT(*FIXED) To copy to W2000 server/pc (after you have got permission from PCN) on the iSeries MKDIR DIR('/QNTC/YourServernameHere')" you need to share out the folder - on your server/pc - you require to transfer data to The path name is formated thus /QNTC/Servername/Sharename/MyDirectory/MyFile.pdf change the default directory of the user to root chgusrprf aaaaa homedir('/') to transfer data from iseries use the following command CPYTOIMPF FROMFILE(EAOPS/EANSKU) TOSTMF('/qntc/itd10166269rdg/shared folder/TEST1.txt') MBROPT(*REPLACE) STMFCODPAG(1252) RCDDLM(*CRLF) DTAFMT(*FIXED) this will copy an iSeries file to a shared drive on a users PC Copy From Import File (CPYFRMIMPF) Type choices, press Enter. From stream file . . . . . . . . FROMSTMF > '/qntc/itd10166269rdg/FTP/eaku2.TXT' To data base file: TOFILE File . . . . . . . . . . . . . > eaKU2 Library . . . . . . . . . . > EAOPS Member . . . . . . . . . . . . *FIRST Replace or add records . . . . . MBROPT > *rEPLACE Stream file record length . . . STMFLEN *TOFILE From CCSID . . . . . . . . . . . FROMCCSID *FILE Record delimiter . . . . . . . . RCDDLM > *CRLF Record format of import file . . DTAFMT > *FIXED String delimiter . . . . . . . . STRDLM > *NONE Remove leading blanks . . . . RMVBLANK > *NONE Field delimiter . . . . . . . . FLDDLM ',' Field definition file: FLDDFNFILE File . . . . . . . . . . . . . > QDDSSRC Library . . . . . . . . . . > EAOPS Member . . . . . . . . . . . . > EAKU2FD Decimal point . . . . . . . . . DECPNT *PERIOD Date format . . . . . . . . . . DATFMT[*]ISO Date separator . . . . . . . . . DATSEP '/' Time format . . . . . . . . . . TIMFMT *ISO Time separator . . . . . . . . . TIMSEP ':' Copy from record number: FROMRCD Copy from record number . . . *FIRST Number of records to copy . . *END Errors allowed . . . . . . . . . ERRLVL *NOMAX Error record file: ERRRCDFILE File . . . . . . . . . . . . . *NONE Library . . . . . . . . . . Member . . . . . . . . . . . . Replace or add records . . . . . ERRRCDOPT *ADD Replace null values . . . . . . RPLNULLVAL > *FLDDFT Identity column . . . . . . . . IDCOL *GEN The field definition file is set up thus EAOPS/QDDSSRC EAKU2FD *************** Beginning of data ************************************* 0001.00 UPUPRF 1 10 0 (start end null) 0002.00 UPPSOD 11 16 0 0003.00 *END ****************** End of data **************************************** Field name same as QDDSSRC To copy objects from one server to another tye CPY command can be used CPY OBJ('/qntc/itd10166269rdg/ftp/eaku2.txt') TOOBJ('/qntc/itd102965269rdg/ftpqntc/ku3.txt') the directory coping to must have the correct permissions to copy object from QDLS CPY OBJ('/qdls/ipsaimp/sls00013.dta') TOOBJ('/qntc/ITD10166269RDG/ftp/sls00013.txt') TOCCSID(*CALC) Here are some notes on saving and restoring IFS (mainly for Notes running on iSeries) SAVE AND RESTORE FROM IFS INCLUDING NOTES MAIL FILES ALL DOCUMENTS AND FILES ARE TO RESIDE IN THE FOLDER /EADIR SUBDIRECTORIES WILL BE CREATED WHEN REQUIRED ie ITDB To save this folder the SAV command is used and to restore data RST command is used The following command saves all the contents of the folder to a savefile EAOPS/TESTSAVF SAV DEV('QSYS.LIB/EAOPS.LIB/TESTSAVF.FILE') OBJ((EADIR)) OUTPUT(*PRINT) The following command will restore back the object STAFFD.OLD to the EADIR folder RST DEV('QSYS.LIB/EAOPS.LIB/TESTSAVF.FILE') OBJ(('EADIR/STAFFD.OLD')) OUPUT(*PRINT) the following will restore back from the disk save and IT database file. RST DEV('/QSYS.LIB/SAVFDOC.LIB/EADIR.FILE') OBJ(('ITDB/Store Footage.mdb') The following will restore back the invoice data base from disk RST DEV('qsys.lib/savfnotes6.lib/sfnotedata.file') OBJ(('/domino/eadis01/data/applications/invoicing.nsf')) OUTPUT(*print) RESTORE A NOTES MAIL FILE There is a command set up to do this CALL EAMODLIB/RSTDOMUSEM and enter the user(8 chars) nsf name ie MLANE or DLAITHWA or use the RST command Device . . . . . . . . . . . . . > 'QSYS.LIB/SAVFNOTES6.LIB/SFNOTEDATA.FILE' Objects: Name . . . . . . . . . . . . . > Domino/EADIS01/Data/MAIL/user.nsf Include or omit . . . . . . . *INCLUDE New object name . . . . . . . DOMINO/EADIS01/DATA/RESTORE/user.nsf Directory subtree . . . . . . . *ALL Output . . . . . . . . . . . . . *print The file will be restored to to the folder DOMINO/EADIS01/DATA/RESTORE/ on the root directory of S4410133 To save directly to TAPE the following command can be run SAV DEV('QSYS.LIB/TAP03.DEVD') OBJ(('/*') ('/QSYS.LIB' *OMIT) ('/QDLS' *OMIT)) SAVACT(*YES) OUTPUT(*PRINT) UPDHST(*YES) RETSORE NOTES FILES FROM TAPE Because we now save the savefile using savsavfdata we can now restore object back much easier RST DEV ('QSYS.LIB/TAP03.DEVD') OBJ (('/domino/EADIS01/Data/Applications/SCalltrak.nsf' *INCLUDE '/domino/eadis01/data/restore/Scalltrak.nsf')) OUTPUT (*print) SEQNBR (11)

**michaelsoucy@netzero.net** · 08-04-2004, 11:46 AM

Exporting data from iSeries to PC database

I think I might try and see if I can get the CPYTOIMPF command to work. I want to see if I can define the last field as 1 character field and just force out a back slash "/". That was my only stumbling block with that option. They said the last character must be a back slash. I think it's so they'll know when their at the end of the record. I wonder if their import program will just ignore the end of record indicator or carriage return if it's after the back slash. This file is for the State of Maine. We're waiting for a reply from them on some questions regarding what we are supposed to populate for data in some of the fields, and when they think they will be able to accept test files. I can't really start too much programming until I get my answers from them. Thanks for all the help.

**Guest.Visitor** · 08-04-2004, 01:43 PM

Exporting data from iSeries to PC database

A back slash is "".

**michaelsoucy@netzero.net** · 08-05-2004, 05:01 AM

Exporting data from iSeries to PC database

I always get those confused. Thanks.

**TBarstow** · 08-09-2004, 09:50 AM

Exporting data from iSeries to PC database

I did the same type of thing a couple years ago. I no longer have the code, I remember doing a RPG program to write the file to a program-described file with all the formatting in place, then FTP to the network. I'm not sure this really fits your needs, but it may give you a starting place.

**michaelsoucy@netzero.net** · 11-06-2004, 02:32 PM

Exporting data from iSeries to PC database

I need to be able to create an export file from an iSeries DB2 file to a file in a PC format. The file will be used for reporting data to our state government. The file will be imported into an Oracle database. The file must be an ASCII text file with a predetermined list of fields. Each field must be delimitated with a pipe symbol "|", and each record must end with a back slash "/", with no carriage return or line feed characters. The fields required are expressed in data types like: "VARCHAR2", "DATE", "INTEGER", and "NUMERIC". Three of the fields must contain NULL values. What would be the best way to create this export file? Any help would be greatly appreciated.

**T.Malik** · 11-06-2004, 02:32 PM

Exporting data from iSeries to PC database

I use .Net XML webservices to extract data from the AS/400 and then format it into xml and then insert the data into Oracle tables in our shop.Once you hav the data in .Net you can foramt it pretty much any which way you want. Then You simply open a connection to oracle and insert into the Oracle Database. I think you can do the same with Java. You are going to need either Java or .Net to accomplish what you are trying to do. I cant think of a pure RPG solution this problem. if you need help drop me a line t_malik@bellsouth.net

Unconfigured Ad Widget

Announcement

Exporting data from iSeries to PC database

Exporting data from iSeries to PC database

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment