May 2, 2005

Searching for Fields in the Database

Last week's post discussed modifying your setup data while the system is in production. You might do this to clean up or correct a faulty setup. I introduced the idea of what I call an "in-place conversion," where you map your setup data (departments or business units, for example) to new values and simply change all existing data to the new values, without inserting new rows. The question was, how can we reliably find all occurrences of a particular field--department ID, for example--in the entire database?

Our first idea might be to ask both the functional and technical team members to list all of the tables they know of that contain the field. This might be an interesting exercise but it's nowhere near reliable enough for our purposes. Of course, if you have someone who insists he can just give you the list, don't fight it. Thank him for the offer...and then continue with developing your own list.

The next idea could be to open the field definition for DEPTID in Application Designer and do a "find object references." That works--but for DEPTID only. What about a field name such as OLD_DEPT? That field probably contains department IDs as well. What if a developer created a field called X_COST_CENTER and filled it with department IDs? Or, to take another example, suppose that you are converting all of your employee IDs. Did you remember SUPERVISOR_ID, which contains employee IDs but doesn't sound anything like EMPLID? The list you can get using "find object references" is good as a sanity check. However, it will not only miss relevant fields, but it will also list tables that are empty.

Your PeopleSoft system contains thousands of tables. But if you look at a random sample, you will see that most are empty. We shouldn't need to be concerned about these, because there is no data to convert.

Here is a method I've used in the past to find all of the fields in all of the tables containing a particular type of data. Let's continue with DEPTID as an example.

First, identify the main "prompt" table for this field. This would be DEPT_TBL or DEPARTMENT_TBL, depending on the product you have. For employee IDs, the prompt table would be PERSONAL_DATA or PERSON, depending on the release.

Next, create a program (SQR is great for this) that scans every field on every table having the characteristics of a department ID field, using dynamic SQL. DEPTID is a 10-character field. PSDBFIELD will give you all of the 10-character fields, and then PSRECFIELD will give you all of the "records" containing each of these fields. Join it with PSRECDEFN and select only records of type "0" to get only the actual physical tables. We are not interested in views, derived records and other types.

We are, however, interested in subrecords. There are two ways to go about handling these. You can expand them yourself or use the database's own internal tables to find the fields on the tables, bypassing the Tools tables. I've found it convenient to create my own temporary RECFIELD table by expanding all subrecords first. Another way is to write a separate procedure in your SQR to expand any "field" that is actually a subrecord. This can be done recursively, or just expect a certain number of levels. Two levels of nesting should be sufficient.

Now for the main operation. With each table/column ("record/field") combination, read the data from the table. Then look up the value in the prompt table. If you get a lot of matches you probably have a "hit" and should list this table/column combination. For example, if all of the non-blank values in PS_MY_TABLE's X_COST_CTR field can be found in PS_DEPT_TBL, then X_COST_CTR is almost certainly a department ID field. You might want to set a slightly lower percentage to allow for any bad data--e.g., if 90% of the non-blank values match, report a hit.

Earlier I said that we would search for all 10-character fields. But what if a developer created a custom field X_COST_CTR with only 5 characters, because all of your department IDs are 5 characters long? Or what if the developer decided to use DESCR to hold department IDs, just to avoid creating a new field? (I've seen stranger things!) For a much more thorough search, you might consider looking at fields that are longer or shorter than 10 characters. Of course, if your department IDs are 5 characters long, you don't need to look at any fields defined as shorter than 5 characters.

The SQR puts together a SELECT for each table/column combination, using dynamic SQL, and executes it. It then lists all of the possible hits. The last step is to print the list and go over it. There will be entries on this list that can be eliminated for one reason or another. Then, with the final list, you can create SQL to do the actual conversion.

You are probably objecting that the search method described above would run forever. In practice, though, I've found that it takes much less time than might be expected--maybe a couple of hours of run time. It's also a generalized method that can be used for just about any field you need to convert.

Until next time...