ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Need help extracting data from as400

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    I just realized why you can't use that command - your system is too danged old!

    Maybe someone else can recall but there was I think a predecessor command but I'm danged if I can recall the name. Only one I can think of is CPYTOSTMF but that is no use for database files - only for program described.

    Comment


    • #17
      Cpytopcd
      All my answers were extracted from the "Big Dummy's Guide to the As400"
      and I take no responsibility for any of them.

      www.code400.com

      Comment


      • #18
        Yes, I saw Cpytopcd. I tried using it in fact, but I be doing something wrong. I am not able to see the folders of my as400, it says that user is not enrolled in system distribution directory. But I seem to have every other permissions. SO I tried CPYTOPCD FROMFILE (@JC/JHIS) TOFLR ('/HOME') hoping this would lead somethwere but "Folder name not correct."

        Edit: I managed to find my profile permissions. Are these enough..?

        *SECOFR *SYSVAL1060816161148 *NO *NO 1291006070652 *SYSVAL*ALLOBJ *AUDIT *IOSYSCFG *JOBCTL *SAVSYS *SECADM *SERVICE *SPLCTL

        Comment


        • #19
          That's the one - but sadly what I had forgotten was that it didn't produce CSV files.

          Comment


          • #20
            But still, can I copy this document to my pc to a readble format, with all the information in the table? I used ftp to download the JHIS.FILE, it seems to have data in it, but only 2 or 3 columns seems to have readble data. Also, the size of the .file when I look in the library is 260 067 328 and the size of the downloaded file is 54,816.. Is it normal..?

            Comment


            • #21
              Not normal to have that big a difference no.

              Please try issuing this command:

              DSPFFD FILE(yourfilename/yourlibraryname)

              Then from the resulting screen page down past the initial "stuff" and you will see a list of fields that looks something like this:
              Code:
              Field      Type       Length  Length  Position        Usage    Heading            
              PRODCD     CHAR            7       7         1        Both     Product Code       
                Field text  . . . . . . . . . . . . . . . :  Product Code                       
                Coded Character Set Identifier  . . . . . :     37                              
              PRODDS     CHAR           30      30         8        Both     Product Description
                Field text  . . . . . . . . . . . . . . . :  Product Description                
                Coded Character Set Identifier  . . . . . :     37                              
              CATCOD     CHAR            2       2        38        Both     Category Code      
                Field text  . . . . . . . . . . . . . . . :  Category Code                      
                Coded Character Set Identifier  . . . . . :     37                              
              STOH       PACKED       7  0       4        40        Both     Stock on Hand      
                Field text  . . . . . . . . . . . . . . . :  Stock on Hand
              Then post a similar extract of that output here. That way we can tell you whether a simple copy is going to work or not and what your options are.

              Comment


              • #22
                Code:
                                             Display Spooled File                              
                File  . . . . . :   QPDSPFFD                         Page/Line   1/1           
                Control . . . . .                                    Columns     1 - 78        
                Find  . . . . . .                                                              
                *...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+... 
                                             Display File Field Description                    
                 Input parameters                                                              
                   File  . . . . . . . . . . . . . . . . . . . :  JHIS                         
                     Library . . . . . . . . . . . . . . . . . :  @JC                          
                 File Information                                                              
                   File  . . . . . . . . . . . . . . . . . . . :  JHIS                         
                     Library . . . . . . . . . . . . . . . . . :  @JC                          
                   File location . . . . . . . . . . . . . . . :  *LCL                         
                   Externally described  . . . . . . . . . . . :  Yes                          
                   Number of record formats  . . . . . . . . . :      1                        
                   Type of file  . . . . . . . . . . . . . . . :  Physical                     
                   File creation date  . . . . . . . . . . . . :  09/30/10                     
                 Record Format Information                                                     
                   Record format . . . . . . . . . . . . . . . :  RHIS                         
                   Format level identifier . . . . . . . . . . :  43C643480530C                
                   Number of fields  . . . . . . . . . . . . . :     28                        
                                                                                       More...
                                              Display Spooled File                              
                 File  . . . . . :   QPDSPFFD                         Page/Line   1/22          
                 Control . . . . .                                    Columns     1 - 78        
                 Find  . . . . . .                                                              
                 *...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+... 
                    Record length . . . . . . . . . . . . . . . :    256                        
                  Field Level Information                                                       
                               Data        Field  Buffer    Buffer        Field    Column       
                    Field      Type       Length  Length  Position        Usage    Heading      
                    NOJOB      ZONED        9  0       9         1        Both     NOJOB        
                    NODIV      CHAR            4       4        10        Both     NODIV        
                      Coded Character Set Identifier  . . . . . :     37                        
                    NOASS      ZONED        2  0       2        14        Both     NOASS        
                    NOSAS      ZONED        2  0       2        16        Both     NOSAS        
                    #PIECE     ZONED        3  0       3        18        Both     #PIECE       
                    DATARV     ZONED        8  0       8        21        Both     DATARV       
                    ABREV      CHAR            2       2        29        Both     ABREV        
                      Coded Character Set Identifier  . . . . . :     37                        
                    SCODE      ZONED        2  0       2        31        Both     SCODE        
                    ANSPER     ZONED        6  0       6        33        Both     ANSPER       
                    SEQTRN     ZONED        7  0       7        39        Both     SEQTRN       
                                                                                        More... 
                                                          Display Spooled File                              
                 File  . . . . . :   QPDSPFFD                         Page/Line   1/41          
                 Control . . . . .                                    Columns     1 - 78        
                 Find  . . . . . .                                                              
                 *...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+... 
                    NOREF      CHAR            8       8        46        Both     NOREF        
                      Coded Character Set Identifier  . . . . . :     37                        
                    DATTRN     ZONED        8  0       8        54        Both     DATTRN       
                    DESTRN     CHAR           40      40        62        Both     DESTRN       
                      Coded Character Set Identifier  . . . . . :     37                        
                    MNTTRN     PACKED      11  2       6       102        Both     MNTTRN       
                    HRSSIM     PACKED       9  2       5       108        Both     HRSSIM       
                    HRSDMI     PACKED       9  2       5       113        Both     HRSDMI       
                    HRSDBL     PACKED       9  2       5       118        Both     HRSDBL       
                    MNTADM     PACKED       9  2       5       123        Both     MNTADM       
                    MNTFIX     PACKED       9  2       5       128        Both     MNTFIX       
                    NOCMD      ZONED        6  0       6       133        Both     NOCMD        
                    #ITEM      CHAR           15      15       139        Both     #ITEM        
                      Coded Character Set Identifier  . . . . . :     37                        
                    QTEITM     PACKED       7  2       4       154        Both     QTEITM       
                    MNTDEV     PACKED      11  2       6       158        Both     MNTDEV       
                                                                                        More... 
                
                                                                    Display Spooled File                              
                 File  . . . . . :   QPDSPFFD                         Page/Line   1/57          
                 Control . . . . .                                    Columns     1 - 78        
                 Find  . . . . . .                                                              
                 *...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+... 
                    TAUCHG     PACKED       9  6       5       164        Both     TAUCHG       
                    NODEVI     CHAR            3       3       169        Both     NODEVI       
                      Coded Character Set Identifier  . . . . . :     37                        
                    NOLOT      ZONED        5  0       5       172        Both     NOLOT        
                    NOMEMP     CHAR           20      20       177        Both     NOMEMP       
                      Coded Character Set Identifier  . . . . . :     37                        
                    BC0060     CHAR           60      60       197        Both     BC0060       
                      Coded Character Set Identifier  . . . . . :     37                        
                
                
                
                
                
                
                
                
                                                                                         Bottom
                This is what I get..

                Comment


                • #23
                  OK. These are your "problem children"

                  Code:
                  MNTTRN PACKED 11 2 6 102 Both MNTTRN      
                  HRSSIM PACKED 9 2 5 108 Both HRSSIM
                  And of course ALL the other packed fields.

                  Packed (known as BCD (Binary Coded Decimal) in some variants places 2 digits in each byte. And places a sign indication in the rightmost nyble.

                  This is a problem for you as when the data is translated from EBCDIC TO ASCI it will be translated in such a way as to not be readily decipherable.

                  So - I suspect given the age of the box that the easiest way to proceed it to create a new copy of the file(s) you need - replacing all packed fields by zoned - and then use the CPYF command to copy the data (with format changes) from the old file to the new. You can then use Cpytopcd to download a readable version of the file. The only remaining issue you should have is that the right-most digit of a zoned field is a character because an extra bit is added to indicate the sign. But this is pretty easy to deal with.

                  At this point though I would suggest that maybe your best bet is to:

                  a) Create a savefile containing the file(s) you want.
                  b) Use FTP (in binary mode) to transfer that to your PC
                  c) Create an account on a (relatively) current IBM i system (there are free usage ones out there and others that let you use it for a day and then clean up the account each day - but you won't need long!)
                  d) Upload your save file to the new system (binary again)
                  e) Restore your savefile
                  f) Run the original CPYTOSTMF command we mentioned earlier.

                  You could also ask nicely and someone might do steps c to f for you <grin> I'm afraid I've spent too much time already on this and have deadlines to hit - but someone else might offer.

                  P.S. You won't need to create a copy of the file under the recommended scenario.

                  Comment


                  • #24
                    After analyzing my jhis.file data, I saw that the only missings columns are Non readable: MNTTRN HRSSIM HRDSDMI HRDSBL MNTADM MNTFIX NOCMD #ITEM (still, that's 8 columns) but most of these are also empty.. I'll try to figure out if this data is important or not, if not I will just spend time formating my document, and if it is, I'll look into copying the savefile to another system.. (If I find one, AFAIK, nobody work / own an as400 in a 150 km range around me, besides stores like Staples and others...)

                    Thank you very much for your help, I appreciate.

                    Comment


                    • #25
                      Where are you? There are IBM i pretty much everywhere. Not that it matters - you'd access it via the internet just as you do this forum. My system is somewhere in the USA and I'm in Canada!

                      If you look at those field names they are the very ones I said would give you issues i.e. they are packed values. I doubt very much that they are empty. For example - the value 12345 would be stored as X'12345F' the only byte that translates to a regular character is the final byte which translates to "¬". The first two bytes are control characters. So a value like 101.12 which would be X'10112F' would probably not result in any readable data.

                      Comment


                      • #26
                        I'm located in Quebec, Canada. I'll try looking into that, thanks. One last question, can I know the exact number of rows of my file? Just so I know if these 370,143 rows is my real number. And when I say these columns are empty, when I did ODBC (where I managed to be able to read to approx. row 66,000, these columns were mostly empty (some data somewhere, but if I can live without, I will) or maybe the MSquery translated these values to 0..

                        Comment


                        • #27
                          So you have ODBC ... OK - that would have saved us a bunch of time.

                          ODBC will retrieve the data correctly (or should do) as the Db2 interface understands the difference between packed and zoned numbers.

                          You should be able to get an accurate count from select count(*) from library.file;

                          I don't know where you got your 370K row count from but the DSPFD command shows the accurate number on about the 3rd page of the display under the heading " Current number of records ."

                          The fact that you only got 66K records suggests that your ODBC connection has a limit (time or volume). If you can't change that just use whatever capability MSquery offers to return part of the total set. It looks as if you have a variety of options http://www-db.deis.unibo.it/courses/...l_top.asp.html

                          Comment


                          • #28
                            Yeah, sorry. When I said that we tried to extract it to excel with MSquery, I tought it would be clear. But you're right, I should have of course stated that I have ODBC, as it could have been something else.. My mistake

                            The 370k count comes from the jhis.file that I downloaded from FTP. It stops at 370k. But in fact, after searching through the menu, I found DSPPFM, typed file/library/*FIRST/*END and got the total count, which is 1011258, which is far from 370k.. Lol.

                            I am now trying to break theses records in smaller pieces, 100k each files with CPYF. Right now, the first 100k did work, i'll see how it's going..

                            Comment


                            • john.sev99
                              john.sev99 commented
                              Editing a comment
                              I think the issue you have struck here, is that excel only allows 65536 rows of data, hence it is stopping before retrieving everything. Not sure if newer versions of excel have increased this limit.

                            • JonBoy
                              JonBoy commented
                              Editing a comment
                              Good catch John.sev99 - that does sound like a possibility. In which case splitting the file would do the job. I notice though that the OP later on said there was an issue at #327423 so he had obvious got beyond 64K.

                            • Pudubat
                              Pudubat commented
                              Editing a comment
                              65536 rows is for Excel 2003. Excel 2010(which I used) have a maximum of 1048576. The issue was really corrupted entries. But thanks for the info, I had to check to make sure :P

                          • #29
                            Just use the ODBC to retrieve in bits as I suggested. No real point in breaking the file up into pieces.

                            And apologies - I just re-read the first post and saw the MSQuery bit - that was long ago and I had forgotten.

                            Comment


                            • #30
                              Well, I just found that the record #327423 has an error. So after breaking the file into 100k pieces (Idk why but whenever I try to enter SQL parametres in the querry, I get errors, and got pissed, I might, again, be doing something wrong.) But still, I think that ODBC would crash on that record. I just tried to cpyf from 300000 to 400000 with ERRLVL(*NOMAX) then i'll try to see if something happens. Right now, my MSquery seems to be crashing..

                              Comment

                              Working...
                              X