Solved

Extract AS400 physical File Data

Posted on 2013-12-03
6
1,186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
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 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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