Extract AS400 physical File Data

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 ?
cobpAsked:
Who is Participating?
 
tliottaConnect With a Mentor Commented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
cobpAuthor Commented:
Would there be a way to know what physical files have stored data ?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
cobpAuthor Commented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
So, how many physical files are in the library?
0
 
cobpAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.