Use SQL to Run PDM Options?

Do you know of any way to filter the object list shown by PDM based on object ownership or other attributes like last used date? I have looked through the documentation, but I can’t find an API or exit program to select PDM records. Instead, I write one-time programs that read output from the Display Object Description (DSPOBJD) or Display File Description (DSPFD) commands and then execute a command. Is there a scripting tool I can use to run commands on objects that allows for more flexible object selection?

–Susan

I can’t tell you how to filter the object list provided by PDM, but I can show you an alternative that uses SQL scripts. You will need iSeries Navigator to run the SQL scripts. The trick is to generate your commands using SQL and then run them using iSeries Navigator.

Suppose you want to change every object in library MYLIB that is owned by JDOE so that it is owned by QPGMR. Start Operations Navigator and select Run SQL scripts by expanding the Database node and right clicking on the database icon for your system. The first time you run SQL scripts; you should open the Connection drop-down menu and select JDBC Setup. Next, click on the Format tab and change the Naming Convention to System *SYS. After changing the naming convention, close and reopen the run SQL scripts window.

You are now ready to run system commands and SQL statements that build an output file and generate commands to change the owner of selected objects. Separate each SQL command with a semi-colon (;) and start CL commands with CL: (that’s CL followed by a colon and no spaces). Enter the following statements replacing Display Object Description (DSPOBJD) command parameters to something that is valid on your system.

CL:dspobjd obj(mylib/*all) objtype(*all) output(*outfile) outfile(qtemp/objects);

Now you have an output file containing all of the objects in MYLIB. You can verify this by running the following statement.

select * from qtemp/objects;

Now try generating commands to change the ownership of objects by running the following statement. Note that these statements will not be run in this step. Remember to replace the JDOE in the where clause with something that is valid on your system.

select ‘CL:chgobjown obj(‘ || trim(ODLBNM) || ‘/’ || trim(ODOBNM) || ‘) objtype(‘ || trim(ODOBTP) || ‘) newown(qpgmr);’ from qtemp/objects where ODOBOW = ‘JDOE’;

I have joined constant data like ‘CL:chgobjown obj(‘ with data returned from the input file like the object library and object name. The two perpendicular bars represent the concatenate operator and join two strings together. The output from this statement will look something like the following.

CL:chgobjown obj(MYLIB/TEST) objtype(*PGM) newown(qpgmr);
CL:chgobjown obj(MYLIB/TEST) objtype(*MODULE) newown(qpgmr);

CL:chgobjown obj(MYLIB/TEST) objtype(*FILE) newown(qpgmr);
CL:chgobjown obj(MYLIB/TEST) objtype(*QRYDFN) newown(qpgmr);
CL:chgobjown obj(MYLIB/TEST) objtype(*QMQRY) newown(qpgmr);

You can now select the records shown by clicking on the first record, scrolling to the end, press and hold the shift key and click on the last record. Copy these records by selecting Copy from the Edit drop down menu. Now paste the selected records to the script pane using Paste from the Edit drop down menu.

Run the Change Object Owner (CHGOBJOWN) commands by placing the cursor on the first command and clicking on the From Selected option on the Run drop down menu.

I have given you enough information to get started. Now, you need to do some exploring to see just how powerful and quick this technique can be. In my example, you can select based on any object attribute. You can also use SQL’s built-in functions and powerful record selection capabilities to build your command strings. You are not limited to output generated by the DSPOBJD command; you can read input generated by any command that supports an output file.

I hope this gets you off to a good start.

–David