Admin Alert: More Info on Default Password, Excel ODBC, Failed Saves

by Joe Hertvik

The nice thing about writing a column is that you sometimes learn more about a topic after you write about it. A reader may provide information or questions that expand your knowledge. You may also run into an unusual problem that forces you to expand on your solution. Lately I've received more information about recent topics that I've written about, so I thought I'd share them with you in this week's Admin Alert.

Dealing with Default OS/400 Passwords

In "Dealing with Default OS/400 Passwords," I discussed how to use the Analyze Default Passwords (ANZDFTPWD) command to spot and deal with user profiles when the user's password has the same value as the user profile name. Recently, I received the following e-mail from Tom Liotta, systems programmer for PowerTech Group, which explained another side of this command:

I just wanted to add a minor piece to your discussion of ANZDFTPWD, a tidbit that seems always to be overlooked. The help text for ANZDFTPWD mentions that the command creates a file in QUSRSYS named QASECPWD, which contains a listing of the default user profiles and their user names, as well as each profile's status and password expiration values before and after ANZDFTPWD was run. This file can be very useful, especially when you want to run ANZDFTPWD with ACTION(*NONE).

I ran ANZDFTPWD on an OS/400 V5R1 system and, sure enough, all the information displayed in the QPSECPWD spool file created by the command was also listed in the QUSRSYS/QASECPWD file. And Tom wasn't kidding about how handy this is. You can feed this file into a Microsoft Excel spreadsheet or Access database using ODBC. You can run reports using the information, or you can read the file into a CL program and perform specialized processing, like sending a warning message to all users before you automatically change user passwords. This file, which isn't even listed as a parameter in the ANZDFTPWD command, opens up some different options for dealing with these passwords, and it's worth taking a look at it. Thanks, Tom.

An Easy Way to Import OS/400 Data into Excel

After my column last week, "An Easy Way to Import OS/400 Data into Excel," I was asked, in an e-mail from reader Steve Doty, for a way to move OS/400 data into a Microsoft Excel 2000 spreadsheet by using ODBC. After getting Steve's e-mail, I discovered a technique for doing this by using Excel's Database Query function, which was incredibly easy to do. The gist of the technique is to use ODBC and the Microsoft Query function to quickly select and import tables into Excel from any library in the user portion of the signed-on user's library list. To test this, I imported the QUSRSYS/QASECPWD file that is updated through the ANZDFTPWD command (see the section above, "Dealing with Default OS/400 Passwords") into an Excel 2000 spreadsheet. Here are the step-by-step commands for duplicating this import on an OS/400 V5R1 machine:

1. Run the ANZDFTPWD command to update the file (as described above in "Dealing with Default OS/400 Passwords").
2. Make sure that the QUSRSYS library is included either as the current library or as part of the user library list for the user who will be running the query.
3. Inside Excel 2000, click the spreadsheet cell where you want the data to start being posted. Start an Excel data file import by clicking Data, Get External Data, then New Database Query, from the Excel 2000 menu bar. This brings up a prompt to start the Microsoft Query software.
4. Microsoft Query will prompt you to choose a data source. For this example, I selected an ODBC data source that pointed to the OS/400 box holding my target file. I could have selected a previously defined query or an OLAP data cube to bring data back into my spreadsheet. There is also a check box at the bottom of the Choose Data Source screen that asks if you want to use the Query Wizard to create and edit your queries. I put a check mark in that box.
5. The program launches the Query Wizard, which prompts you to choose a target OS/400 table and the specific columns you want to include in that query. All of the available tables from the current library and the libraries in the user part of your library list will be displayed. Select all the columns in the QASECPWD table.
6. Selecting Next on the Query Wizard screen brings you to a Filter Data screen, where you have the option to include or exclude only the records that meet certain criteria. For my spreadsheet, I opted to select all the QASECPWD records where the user profile status (field DFSTAB) was equal to *ENABLED.
7. The Query Wizard will next prompt you for which fields the returned records should be sorted by. I opted to sort by the text description of the user profile (field DFPTXT).
8. The wizard will finally ask you whether you want to return the data to Microsoft Excel, view or edit data in Microsoft Query, or create an OLAP data cube using this query. I opted to simply return it to Excel, but you could save the query for later processing.

Like the data transfer add-in discussed in last week's column, using ODBC to bring data into an Excel spreadsheet is relatively painless and very easy. So it's worth giving it a try.

CL Programming Techniques for Solving Backup Problems

Three weeks ago, in "CL Programming Techniques for Solving Backup Problems," I provided a technique for saving most of your OS/400 libraries without going into restricted mode. This can be done by performing two Save Library (SAVLIB) commands in a save-while-active configuration, where one SAVLIB command only backups the user libraries (*ALLUSR) and the other command only backs up all the IBM (*IBM) libraries. This technique worked fine for months, but this past week I ran into a situation where the SAVLIB statement ended unexpectedly. The job bombed out one of my SAVLIB commands without saving all of the listed libraries. The SAVLIB statement crashed to the extent that it didn't even write an end-of-sequence marker on the tape, disabling the job from saving any other information on that particular tape and wiping out the rest of the backup in the process.

This problem occurs when there's another job in the system that has an open commitment or rollback operation on the library for which you're running a save-while-active SAVLIB statement. It may be that this job is hung, but the key is that it's holding an open commitment or rollback operation, which causes your save attempt to timeout. If you see this occurring, check your operator's message queue (QSYSOPR) for the following messages:

• CPI8365 -- Save-while-active request requires commit or rollback for job job_number/user ID/job_name.
• CPI8367 -- Save-while-active request for job job_number/user ID/job_name ended.
• CPF377F -- Save-while-active request prevented by pending record changes.

These messages alert you that the save failed because another job had an open incomplete commitment or rollback processing request in the library that you're trying to save while active. But OS/400 does help you out here. You can find the names of any jobs that are stopping your backup by checking the CPI8365 messages in QSYSOPR, where the complete job number, user ID, and job name of any problem jobs are listed. Once you resolve the open request, you can rerun your save-while-active statements and the saves should complete cleanly this time.

Sponsored By QUADRANT SOFTWARE

LOOKS COUNT! Pre-printed forms, checks, and bar code labels are plain, costly, and a security risk. Managing everything is a nightmare. Look great with our Electronic Document Distribution solutions. Eliminate fax machines, pre-printed forms, and blank check stock. Look professional. Save time and money, too! Better Documents. Better Delivery. Better Together. Call 800-258-3399, visit www.quadrantsoftware.com, or email sales@quadrantsoftware.com