Exporting data from iSeries to PC database

[K.Forsythe]

Michael, I don't know any existing tools that will do all the formatting that you need. My first thought would be write a little java program to read the data from DB2 using JDBC and then write it to a local file on the IFS in an ASCII format with all the special characters you need. I've only messed with that a little bit, maybe someone else will have a better suggestion. Kevin

[GlenKerner]

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]

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]

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]

> 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]

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]

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]

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]

A back slash is "".

[michaelsoucy@netzero.net]

I always get those confused. Thanks.