In my prior tip, I covered a new feature provided in the i7.1 technology refresh 4 update that allows a INSERT/SELECT statement to insert data into a local DB2 for i table from a query against a remote DB2 database. In this tip, I will cover a method that can be used to query multiple DB2 relational databases in a single statement.

Author’s Note: Incidentally, the remote DB2 database can be another member of the DB2 family besides DB2 for i. Check out my article on DB2 for Windows for more information.

As I mentioned in my last tip

My boss asked me if I know of a cheap or free tool that can automatically export a DB2 (for i) table to an XML file. I told him that I could write a Java program pretty easily that could accomplish this for a specific table, but I’m not aware of any way to “generically” do it for any table.

For example:

select *
  from deleteme

TheKey   SomeText
     1   Row Number 1
     2   Row Number 2

That code should produce output something like this:

<?xml version="1.0" encoding="UTF-8" ?>
<deleteme>
 <row>
   <TheKey>1</TheKey>
   <SomeText>Row Number 1</SomeText>
 </row>
 <row>
   <TheKey>2</TheKey>
   <SomeText>Row Number 2</SomeText>
 

Last issue, I introduced a two-part article discussing techniques for deleting user profiles. Part one focused on prepping a user profile for deletion. In part two this week, I discuss how to determine whether the user is a critical user and what to do about it, and I demonstrate three techniques for deleting users. After reading both articles, you will have a good template for creating an IBM i user deletion procedure in your organization.

The Five Steps Of User Deletion

Last issue, I introduced a template that determined the best way to deal with terminated IBM i users