iSeries SQL：获取与更新目录条目

Hey there! I’ve worked through similar user directory address modification tasks on IBM i before, so let me share what I’ve learned to help you out:

1. Has this requirement been implemented before? #

Absolutely! This is a common need when organizations standardize address formats, update location info after a move, or align directory data with new organizational rules. Most teams use either database-aided command execution or custom CL programs to handle this efficiently.

2. Database tables/views for user directory entries #

IBM i provides built-in database objects to query directory entry details safely:

• QSYS2.DIRECTORY_ENTRY: A user-friendly SQL view that lets you pull all directory entry data, including the address field (ADDRESS column). It’s ideal for auditing or gathering a list of entries needing updates.
• QSYS.DIRENT: The underlying system table storing directory entries, but direct modification here is strongly discouraged. IBM i expects you to use official system commands (like RNMDIRE) to alter directory data, which ensures system integrity and avoids unexpected issues.

You can run this quick SQL query to check existing user addresses:

SELECT USRID, ADDRESS FROM QSYS2.DIRECTORY_ENTRY WHERE USRCLS = 'USER';

3. Feasibility of using a CL program with RNMDIRE #

This is a totally valid and widely adopted approach! Here’s a high-level breakdown of how to make it work:

• Step 1: Collect user entries: Use DSPDIRE OUTPUT(*OUTFILE) to export directory entries to a physical file, or run an SQL query to populate a work file with user IDs and their current addresses.
• Step 2: Loop through entries in CL: Use a sequential file read or DOFOR loop to process each user. For every entry, execute the RNMDIRE command to update the address:

  RNMDIRE USRID(&USRID) OLDADR(&OLDADR) NEWADR(&NEWADR)
  

• Step 3: Handle edge cases: Account for users with no existing address, special characters in address fields, and ensure your profile has the necessary permissions (*SECADM or *ALLOBJ authority is required to modify directory entries).

Pro Tips: #

• Always back up directory entries first with SAVDIRE before bulk changes—this lets you roll back if something goes wrong.
• If you need to apply a consistent address tweak (e.g., replacing a street name), use SQL to generate bulk RNMDIRE commands, then execute them via RUNSQLSTM or a CL program.
• Test the process with a small subset of users first to validate changes work as expected.

内容的提问来源于stack exchange，提问作者geojf3