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

Anthony6890
Anthony6890 used Ask the Experts™
on
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.

-Anthony
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
VP 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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial