Changing a File Layout - Help!

ave you ever needed to change a file layout and broke out into a cold sweat. Maybe it was just a small change like making one field a little longer. Besides looking after copying your data back and forth you had all sorts of issues you had to address such as security, journaling, triggers, referential integrity, logical files and many more. If, like me, you have always wished for something simpler - well let me wish you an early Merry Christmas. Some of the nicest gifts from the Rochester lab recently have been the enhancements to DB2/400. One of my favourite enhancements is the new SRCFILE parameter on the CHGPF (Change Physical File) command. Sounds simple doesn’t it. Let me explain this great new feature available to anyone on V3R2, V3R6 or V3R7.

Changing a file’s layout is now faster and easier than ever before. This is excellent timing. Many programmers are going to have to face changing a lot of 2 byte fields to 4 byte fields as the turn of the century approaches. To change a file layout you need a DDS describing the new layout. You can simply modify a copy of the DDS you originally used to create the file. You can change data types, decimal positions, field lengths, sequence of fields and more. When you enter the command CHGPF xxxxxx SRCFILE(new DDS name) the system will look after: temporarily ending journaling, renaming your current file, creating the new file, removing triggers and referential integrity constraints from the original file and moving them to the new file, adding the members to the new file, granting all appropriate authorities to the new file, copying all the data back, restarting journaling and moving or rebuilding all the logical files. WOW!

When the system creates a new file for you it preserves many of the original file’s attributes including: file creation date, the last save and restore dates, and file ownership. For logical files the system is smart enough to detect whether or not your changes will affect the access path. If the key field attributes haven’t changed then your indexes are preserved and moved over to the new file. This can be a huge timesaver. If the index does need to be rebuilt the system looks after that as well.

One item to consider when using CHGPF is level checking. If you have made modifications that change the file’s buffer layout then your programs will level check when you attempt to open the file.

You need to know what programs are affected by your changes and recompile those programs. Another consideration is that DB2/400 requires exclusive use of the physical and logical files during the CHGPF process. Be aware that you cannot use CHGPF to change field names. DB2/400 uses the field names to tie together your before and after definitions. As is the case today, you still require enough free space to hold an extra copy of the file on your system during this process.

If you are an SQL user, instead of the CHGPF command you will use the ALTER TABLE statement. The ALTER TABLE statement lets you do almost everything CHGPF does. One difference is that you cannot resequence fields. ALTER TABLE allows you to change attributes such as a field’s data type, precision and scale. These are significant enhancements over and above the ANSI standard. T < G

Jackie Jansen is an AS/400 Specialist and Consulting SE, providing national technical and marketing support for the AS/400 in Canada. Jackie frequently speaks at AS/400 Technical Conferences and User Group meetings.