ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Need help extracting data from as400

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

  • Need help extracting data from as400

    Hi,

    We have an old AS400 (don't have type, but it's something like 30 years old) that we need to extract data from (disk started to have a hard time to spin, we needed to shake them in order to make it spin again...)

    We were able to extract data from some tables, but we have one with 1,5 million entries. We did the other with MSquery to excel, which worked well with no issues. But this one, of course, Excel won't take 1.5 million lines. Then we tried to send it to Access, but an error occur, and we are not able to skip this error (on line 235,716)

    I was trying to split the query, but I get an error while sending to MSquery. "SQL0227 - FETCH not valid, cursor CRSR0003 is unknown position."

    I tried to exclude the line, skip the line, but I get this error. If I try to open it in msquery and copy/paste data, I can do it with the first 65,000 lines, but after this msquery crash. If I try to send it to excel / access, it just crash.

    Does anyone have any idea on how to pull data from this nightmare? I tried asking old IT technician who worked for years with AS400 and they don't know how to get through. We were ready to pay for someone to extract the data, but there is no company with AS400 expertise around me (looked 200 km around me) and the only person that may have an idea on how to achieve this is not able to give me a valid reference (the data is sensitive...)

    This machine is older than me, and I have no experience with as400. I saw some interesting features in the programming menu in client access, such as STRQM, but I don't know where to start, and I really don't want to mess things up. Someone have a link to a good tutorial (I tried looking, but couldn't find something useful/I could understand well...)

    Thank you..

  • #2
    You obviously have a comms link to the box but the first question I would ask is what are you planning to do with the data? Bringing it into Excel (unless that is where you want it) wasn't really the best step.

    Probably the most universal way to unload the data is via CSVs. There is a command Copy To Import File (CPYTOIMPF) that will extract any table to a CSR format in the IFS from where it can easily be FTP'd to any box accessible on the network.

    In terms of advising you of future options it would help to know the release level of the operating system. Issue the command GO LICPGM and then take option 10 to show the licensed programs on the box. If the installed release does not show up (second column) then press F11 until you see something like V5R4M0.

    Comment


    • #3
      Re your private message.

      NO NO NO - DO NOT PRINT THE BLOODY STUFF. It is a computer for goodness sake (and even though on a very old release) a @#%@#%@#%@#% good one. The fact that at 30 years old it still works despite obviously having been ignored should tell you something.

      Assume that the table you want to export is called MYTABLE and it is in library MYLIB. Then you simply issue the command:

      CPYTOIMPF FROMFILE(MYLIB/MYTABLE) TOSTMF('/home/mytable.csv') STMFCCSID(*PCASCII)

      That will take the entire content of the table formatted as a CSV file in the /home directory (or whatever other name you specify) suitable for importing into any other inferior database.

      If the format is not exactly what you want then use the F4 key to bring up the command prompt and the F1 key for information about any of the individual fields. This is a very easy system to learn and unlike Windoze and Unix actually uses sensible and consistent commands. Each and every command on the system has this kind of help support. take the time to learn a little more about the box and you will save yourself weeks if not months.

      Once you have the file then simply use FTP (which pretty much works the same on IBM i as any other box) and FTP the file to a PC or wherever else you want it.

      Comment


      • RDKells
        RDKells commented
        Editing a comment
        "... into any other inferior database..."

        Brilliant.

    • #4
      If the system is that old, I think it pre-dates TCP/IP...

      Cheers,

      Emmanuel

      Comment


      • jtaylor___
        jtaylor___ commented
        Editing a comment
        It sounds like they did ODBC from Excel, so it should have TCP.

    • #5
      Depends on what you're wanting to do with the data. If the file has numeric data in packed decimal or zone decimal. If everything in it is in Zone Decimal FTP will work. Otherwise JonBoys recommendation is sound advice.

      Comment


      • #6
        Originally posted by EmmanuelW1 View Post
        If the system is that old, I think it pre-dates TCP/IP...
        My memory says that it was available by V2R3 - some others I have talked to think it came in around V2R1. Either way it is available on a V3 box.

        Update - Mark Waterbury came up with this link:



        In that document, it mentions 5728-TC1, so it was definitely available fairly early on. It mentions release 3 which would indicate that it was available in V1R3 since in those days we only talked releases not versions.
        Last edited by JonBoy; August 15, 2019, 10:23 AM.

        Comment


        • #7
          Yes, TCP was available way back. I had it on my 9402. If they have the ability to download to Excel, they should be able to use the same PC to download a .csv format file. I would suggest also saving the file definition so it can be used to structure a table to import the data into.

          Comment


          • #8
            Great, I can finally post a reply.

            First, whenever I try to use CPYTOIMPF, I get an error "Command CPYTOIMPF in library *LIBL not found." and I did select the library I want..

            Second, I tried downloading the file with FTP. but I get an error:

            ftp> get J080.his.file
            200 PORT subcommand request successful.
            426-Member J080.HIS in file J080.HIS in library QGPL not found.
            426 Data transfer ended.
            ftp> get test.file
            200 PORT subcommand request successful.
            426-Member TEST in file TEST in library QGPL not found.
            426 Data transfer ended.

            (I assume that QGPL is the library I want, since it's the only one with non-system files..)

            Any insights...?

            Comment


            • #9
              QGPL is an IBM library, I would doubt that someone has placed enterprise data here.
              Can you log onto the greenscreen using the credentials from FTP?
              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


              • #10
                Ah, I see.. So I'm wrong. Yes I'm able to log in using tn5250, I have an admin account, but I don't know which level of admin, and I don't have the qsecfor password. I tried loggin in using Telnet, but Putty just close without further notice..

                Comment


                • #11
                  When you are logged in with tn5250 issue the command DSPLIBL and tell us what you see.

                  Comment


                  • #12
                    On the command line type:
                    PHP Code:
                    wrklib *all 
                    If you post the resulting data we can help you identify production libraries.
                    Last edited by jamief; August 20, 2019, 09:40 AM.
                    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


                    • #13
                      AH! I see, I was using DSPLIBL where I could only see system library, but with wrklib *all I could see that my library is @JC. I keep getting an error with CPYTOIMPF but I am now trying to download the file named JHIS in my @JC library. I have no idea what the data will look like, but that's a start. I'll give you an update soon..! Thanks

                      Comment


                      • #14
                        Originally posted by Pudubat View Post
                        Great, I can finally post a reply.

                        First, whenever I try to use CPYTOIMPF, I get an error "Command CPYTOIMPF in library *LIBL not found." and I did select the library I want..
                        CPYTOIMPF is shipped in QSYS - hard to believe your system doesn't have it. Issue the command WRKOBJ OBJ(*ALL/CPYTOIMPF) and you should see it. If it is there it would imply that QSYS is not in your library (*LIBL) list. Highly unlikely but ...

                        How are you trying to execute the command? From a tn5250 session?

                        Comment


                        • #15
                          Yes, from TN5250, and right now, I have no idea on how to communicate with the 400 beside 5250 and ftp.. :/ after doing the command, it says (cannot find object to match specified name.)

                          Comment

                          Working...
                          X