Solved

Extract AS400 physical File Data

Posted on 2013-12-03
6
1,102 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 (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 tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now