Solved

Extract AS400 physical File Data

Posted on 2013-12-03
6
1,139 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:daveslash
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 18

Expert Comment

by:daveslash
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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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