?
Solved

Extract AS400 physical File Data

Posted on 2013-12-03
6
Medium Priority
?
1,267 Views
Last Modified: 2013-12-04
We are retiring are old AS400 but I have a task of pulling all the physical file data from the production library in a usable format if needed. Does anyone have a best solution to doing this ? I was trying to attempt it to do this in sql but was timing out. Any good suggestions without exporting every single physical file separte, but being able to pull the whole library itself ?
0
Comment
Question by:cobp
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 39692810
How many physical files are in the library?

if you don't know, it's easy enough to count them with the DspFD command:

From OS/400:

DSPFD FILE(MyLib/*ALL)
      TYPE(*BASATR)
      OUTPUT(*OUTFILE)
      FILEATR(*PF)
      OUTFILE(MyLib/PFList)

Open in new window


From SQL:

select count(*)   
  from MyLib/PFList

Open in new window

0
 

Author Comment

by:cobp
ID: 39692936
Would there be a way to know what physical files have stored data ?
0
 

Author Comment

by:cobp
ID: 39693023
Actually what needs to happen is the ability to pull all the physical files and their data in put them in a sql server database if possible. Example if I have a payroll pf with data to extract the pf as payroll in sql. Would there be a feasible way to pull the whole library and do this ?
Finally if the pf has no data create a blank table.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 18

Expert Comment

by:Dave Ford
ID: 39693166
So, how many physical files are in the library?
0
 

Author Comment

by:cobp
ID: 39693189
a whooping 6145 pf's it is coming from an erp system, do you know if there is a way now just to query if there is data in the pfm without going into each one. I think the table structure is to big for dumping into a sql db. your thoughts ??
0
 
LVL 27

Accepted Solution

by:
tliotta earned 1500 total points
ID: 39694224
You can't assume that empty files can be ignored. File definitions may be created as 'data dictionaries' or as templates that are referenced elsewhere. A missing definition can mess up other parts.

Your only reasonable choice is likely to be through tools. Simplest one to start with is probably iSeries Navigator. Setting up an iNav connection will let you drill into the schema (i.e., the library or libraries) and generate the SQL for 'All Objects'. Having all SQL will give you a chance to see how the database structure might fit within a different DBMS.

After the SQL is generated, you can export data. However, because every table will export into a separate container, each will have to be handled individually. The alternative is to create programming that can request export while providing some kind of naming for the various outputs or other structure that can keep it organized.

Tom
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question