How do I expand a field length in an existing physical file and update all supporting programs reference that field/file?

Hi All,

I'm assisting a friend in changing the field length in an existing physical file on an AS/400,iSeries.  We are trying to understand two things:

1. What is the best way of going about this.

2. What is the best way of updating all supporting programs which use the PF or LF.  

Attached is a document we received from a guy who responded to our post on an IBM website.  This has been helpful; however, there are hundreds of programs that reference that PF that we are changing.  

If anyone can shed some light on a more effective way of making these changes, it would be greatly appreciated.

Thank you.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
No attachment.

But what you are talking about (IBM i DB2 database field expansion) can be a very big, complex project.  

Changing the field size is trivial - you make a copy of the original file, modify the DDS source code (or convert to SQL DDL if that is an option in your shop) and change the size of the field in DDS.  Then you recompile the file and copy the data from the old file to the new file.  Finally,  you recreate any dependent logicals (after reviewing them for impact and making any required changes).

If SQL is an option, you can use the ALTER TABLE statement, which is much simpler.

Unfortunately, then there is the matter of all of the dependent programs and other dependent objects in the system.  At a minimum, they will probably need to be recompiled and thoroughly tested.  Also, is there positional logic for this field in these programs?  For example, we once did a field expansion of a customer number field where various positions has specific meanings - first 4 bytes meant something, last 4 bytes meant something.  As a result, when we expanded, we added bytes to each sub-field, which required special data conversion, and updates to every program that contained logic to split the customer number into two components.

Also, you'll need to identify any other local objects (other physical files, logical files, display files, printer files, ICF files, Query/400 queries,  QMQRY queries, CL programs, RPG programs, SQL scripts, etc.), that reference this physical file, and perform analysis to determine what, if any, modifications are needed.  You may also need to identify remote applications and tools that reference this file in any way (ODBC, DRDA, DDM, JDNC, OLEdb, ADO.NET, file transfer tools, data replication tools, SQL Server Linked server/linked tables, etc.) and determine the impact of the column length change.

Note that there are third party tools to help with both impact analysis and modifications:

  • Hawkeye Pathfinder (cross-reference tool, helps identify local references the the file and field).  This is a green-screen tool.  It is inexpensive, and I've used it (and continue to use it) in a lot of ships.
  • Fresche Legacy x-Analysis (an even better, in my opinion, though more expensive than Pathfinder, cross-reference tool).  I'd buy this if you can afford it.
  • Fresche also has a resizing tool called x-Resize designed specifically for this task.  Not an endorsement - I've never used it, but would definitely evaluate it if I had a large resizing project to do.

Both Hawkeye and x-Analysis can scan your existing objects and source code and help you identify "where used" and "how used".  There are other tools (including the native DSPDBR and DSPPGMREF commands) that can help with this analysis, but these are the only two commercial tools I have used recently.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.