| Searching for Fields in the Database | KEVIN RESCHENBERG
05-02-2005 |
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.
|
