In this post, I'll discuss how SAP IDM developers can find occurrences of any given character string, such as a table or attribute name, in the source code of their scripts, jobs, tasks or attribute definitions. Using a bit of SQL and XML, you can turn your graphical database client into a minimalist where-used list for SAP IDM.
Prerequisites
This post is intended to serve as a tutorial-style documentation for an SQL query which I originally developed in one of my past projects and later decided to publish as open source on GitHub. I'll refer to this SQL query as where-used query throughout the remainder of this document. You can download the latest version for Microsoft SQL Server here. Separate versions for Oracle and IBM DB2 exist as well, but I'll focus on SQL Server in this post exclusively.
You will need the following things to try out the steps of this tutorial on your own:
- Source code of the where-used query (see download link above)
- SAP IDM 7.2 on Microsoft SQL Server
- SAP Identity Center Designtime based on Microsoft Management Console (MMC)
- Microsoft SQL Server Management Studio (SSMS)
- Database connection as admin user from SSMS to the SAP IDM database
Please note that although the where-used query requires admin access, all database operations it performs are read-only (i.e. SELECT). Its execution will not alter any data in the database. It's still good practice to use it in development systems only. Please don't try it in production.
Apart from the tools listed above, I assume some familiarity with Microsoft SQL Server Management Studio on the reader's behalf. Readers should now how to open and edit SQL files and how to connect to the database with a specific user.
Overview
The basic workflow for finding locations containing the search term you're interested in is:
- In SSMS: edit the SQL code to fill in your search term
- In SSMS: execute the where-used query as admin user
- In SSMS: display ID, name and content of matching locations
- In MMC: navigate to matching locations
A Practical Example
Let's use the where-used query to find occurrences of the database table name "mxi_attrvaluehelp" in SAP IDM. Since this is the standard value help table, it's likely to be used in a variety of locations within any SAP IDM system, provided the SAP Provisioning Framework and maybe a couple of standard jobs have been imported. Locations where this table is typically used include:
- Legal values of Identity Store attributes
- Source code of global or (job) local scripts
- Anywhere within job definitions, e.g. in the source or destination of a certain pass
Preparing Execution
To start, open the where-used query in Microsoft SQL Server Management Studio. Scroll down in the SQL query editor window to the very bottom, and find the following XQuery statements near the end:
for $t in (//attribute::*, /descendant-or-self::text())
where contains(upper-case($t),upper-case("YOUR_SEARCH_TERM_HERE"))
return $t
Now comes the most important part: in the above code, replace YOUR_SEARCH_TERM_HERE with mxi_attrvaluehelp, so it becomes:
for $t in (//attribute::*, /descendant-or-self::text())
where contains(upper-case($t),upper-case("mxi_attrvaluehelp"))
return $t
The next step is to verify that the query will be executed as admin user.
Have a look at the status line in the lower right corner of Microsoft SQL Server Management Studio to verify the name of the database user used by the current query editor window. In a default installation, the user name displayed should be MXMC_ADMIN. Note that the name mxmc728_admin displayed in the following screenshot is specific to my lab environment. Yours will be different.
If you're currently connected as any user other than admin, switch the DB connection before proceeding.
Finally, verify that the button "Results to Grid" in the Microsoft SQL Server Management Studio toolbar is checked, so the result set returned by the where-used query will be displayed in a graphical table control, and not as plain-text. This will become important later on for navigating to the full content of matching text locations.
Finally, make sure you don't have accidentally selected any of the SQL code in the query editor window with your mouse or cursor. This would result in errors during execution, because only the highlighted parts of the code would be executed. If unsure, perform a single left mouse click into the query editor window to discard any current text selection.
Now execute the query, e.g. by pressing F5 on your keyboard. You should expect a couple of seconds execution time only. In my lab environment, query execution returns thirty something rows. Again, your number of results will be different.
The column headers and first few result set rows look like this:
Now what can we do with that information?
Result Set Structure
Each row of the where-used query's result set is one match location, i.e. a piece of text from an SAP IDM designtime object. This piece of text contains your search term at least once. I use the term "designtime object" as a generalization here, as I don't like to spell out "attributes or tasks or jobs or scripts" all the time.
The result set has the following row structure:
| Column Position | Column Name | Column Description |
|---|---|---|
| 1 | NODE_TYPE | Type of designtime object |
| 2 | NODE_ID | ID of designtime object |
| 3 | NODE_NAME | Name of designtime object |
| 4 | MATCH_LOCATION_XML | XML view on MATCH_LOCATION_TEXT (see below) |
| 5 | MATCH_LOCATION_TEXT | Piece of text containing search term; part of designtime object's content |
| 6 | MATCH_DOCUMENT | XML view on designtime object as a whole, or at least significant parts of its textual content |
The first three columns, NODE_TYPE, NODE_ID and/or NODE_NAME, let us identify the designtime object a match has been found in. The remaining columns, MATCH_LOCATION_XML, MATCH_LOCATION_TEXT and MATCH_DOCUMENT, serve to display the matching content and its surrounding context.
NODE_TYPE indicates the type of designtime object a match is in. It can have one of the following values:
- A for Identity Center attributes
- T for tasks
- J for jobs
- S for global scripts
NODE_ID is the numeric ID which has internally been assigned by SAP IDM to this designtime object. For attributes, NODE_ID is the attribute ID, for tasks it is the task ID, and so on.
NODE_NAME is the user-provided name of the designtime object. For attributes, NODE_NAME is the attribute name, for tasks it is the task name, and so on.
MATCH_LOCATION_XML and MATCH_LOCATION_TEXT are very similar. Let's focus on MATCH_LOCATION_TEXT for now. That column contains a piece of text from the designtime object's definition. In general, this is only a smaller part, rather than the designtime object's whole content. Scripts are a notable exception to this rule, in that MATCH_LOCATION_TEXT contains their whole source code, not just a part of it.
MATCH_DOCUMENT, on the other hand, is a more complete, XML-based view on the designtime object's content. You can think of this XML document as the surrounding context of MATCH_LOCATION_*. In general, it contains user-provided text related to the designtime object's definition. In the case of attributes, for example, this XML document contains the attribute's description, allowed attribute values, SQL query used for value help and regular expression to validate attribute values. In the case of jobs, MATCH_DOCUMENT even contains the complete job definition in an internal and undocumented XML format defined by SAP.
Finally, it's important to note that the where-used query works case-insensitive and uses substring matching. Hence, searching for "mxi_attrvaluehelp" would find "mxi_AttrValueHelp", "$FUNCTION.my_function(MXI_ATTRVALUEHELP)$$" and others.
With that knowledge under our belt, let's inspect the result set from our example query in some more detail.
Matches in Attributes
The first result set row in the previous screenshot corresponds to an Identity Center attribute (NODE_TYPE=A) whose ID is 3 (NODE_ID=3) and whose name is MX_LANGUAGE (NODE_NAME=MX_LANGUAGE). As you might guess, the most likely place where any attribute definition would refer to the table mxi_AttrValueHelp is in its "Attribute values" section, so let's switch over to MMC and inspect the attribute definition of MX_LANGUAGE.
If you have multiple Identity Stores and thus multiple attributes with the same name "MX_LANGUAGE", you could have a look at MATCH_DOCUMENT (use the hyperlink) to see exactly which Identity Store this match relates to. I'll skip this detail here for brevity and simply trust that we're talking about MX_LANGUAGE from the "Enterprise People" Identity Store.
As you can see, the exact content from the MATCH_LOCATION_TEXT column, "mxi_AttrValueHelp", can be found in the input field "Table name" on the "Attribute values" tab in MMC.
Matches in Jobs
Scroll further down your result set, and check if it contains any rows with NODE_TYPE=J, which indicates a match within a job. In my environment, I have multiple result set rows which all relate to the SAP standard job "ABAP Read Help Values". You may not have this job in your system at all, in which case these or similar matches will be missing from your result set. Here's an excerpt from my matches:
All match locations shown in the previous screenshot are contained in one job, as we can see from the identical values for NODE_TYPE, NODE_ID and NODE_NAME in these rows. The first two rows each have different MATCH_LOCATION_TEXT values, while the values in the last two rows are identical. The latter indicates that the job contains this MATCH_LOCATION_TEXT in two different places, e.g. in two separate passes.
Again, you could open the complete job definition as XML by following the MATCH_DOCUMENT hyperlink, and inspect the XML document to figure more precisely where each matching text is hidden. To keep it short, I'll not do that here and instead switch over to MMC directly.
How do we locate the relevant job in MMC, first of all? Fortunately, MMC lets us navigate to any job quickly, provided we know its ID. In MMC, select "Action ==> Find" from the menu bar. When prompted, enter the job ID displayed in column NODE_ID into the "Find" input field.
Make sure that you check "Find tasks or jobs only" to speed up the search, then press "Find next":
MMC will take you directly to the job definition. Let's inspect some of the job's passes, e.g. the very first one "Convert Locale of ValueHelp Table to UpperCase". On the destination tab of this toDatabase pass, we can see our search term "mxi_attrvaluehelp" as part of an SQL update statement:
Referring back to our result set, this SQL statement is exactly what we see as MATCH_LOCATION_TEXT in the first line.
Let's browse further down to the pass "Address Salutation - to HelpValues". Switch to the destination tab of this pass. It's not exactly easy to spot, but the field "Database table name" used in this pass contains the MATCH_LOCATION_TEXT from row three (or four - there's no way to tell) from the query result set.
As mentioned earlier, the existence of multiple lines containing this value indicates that the job has a corresponding number of additional locations, maybe separate passes, which also contain this text. I'll not verify this now, but end my exploration of the job "ABAP Read Help Values" job at this point.
I'll also deliberately skip discussion of result set line number two, whose MATCH_LOCATION_TEXT starts with "//Main function: ...". This is a match from a local script defined inside the job. Pretty much all of the information provided in the next section, matches in global scripts, is true for local scripts as well.
Matches in Global Scripts
Switch back to your query result set in Microsoft SQL Server Management Studio and scroll down until you find any line with NODE_TYPE=S, which indicates a match in a global script (JavaScript or VBScript). In my environment, there are matches in three global scripts, the first of which is sap_abap_getHelpValKey.
When you have a look at the content of MATCH_LOCATION_TEXT of this result set row, you'll notice that it contains the complete JavaScript source code of this script. Unfortunately, though, all of the script's source code is displayed on one line, which makes MATCH_LOCATION_TEXT more or less unusable in this case. The content is simply too wide to be displayed on any monitor.
Here is where MATCH_LOCATION_XML is very handy. Click on the hyperlink of that column's value, and Microsoft SQL Server Management Studio will open a new, separate window with the complete script source code.
There's a small but unavoidable oddity here, in that the content of MATCH_LOCATION_XML always has artificial markup characters "<?X " at the beginning of the first and "?> at the end of the last line. These markup characters are not part of the real content from the database, but generated dynamically by the where-used query. For people who can live with this glitch, MATCH_LOCATION_XML's hyperlink navigation provides a convenient way to display script source code, or any other longer text matching your search term, directly in Microsoft SQL Server Management Studio.
Please note that matches in one single script will always result in one single match location of the where-used query. This is true even when there are multiple occurrences of your search term in this script's source code. For example, sap_abap_getHelpValKey contains our search term "mxi_attrvaluehelp" two times, as illustrated in the following screenshot. However, there's only a single line for this script in the query result set. If in doubt, I recommend you use the "Quick Find" (Ctrl+F) function in the editor window displaying the MATCH_LOCATION_XML value. This will help you step through all occurrences easily.
Conclusion
With this, I would like to conclude our tour through the where-used query. This post has grown more lengthy than I intended to, but I hope you still found it useful. Topics I have not covered at all, or touched upon only briefly, include:
- How can you use it on Oracle or IBM DB2?
- How can you refine your search?
- What's in the MATCH_DOCUMENT column and how can you use it?
I might follow up on these in future posts.
Admittedly, the approach presented here is a stop-gap solution lacking any integration into the official SAP IDM tools. In particular, the need for switching back and forth between Microsoft SQL Server Management Studio and MMC is tedious, of course.
On the other hand, my using it almost every day for refactoring or reverse engineering makes me think that other IDM developers might find it useful as well. Personally, I would love to see SAP build this or comparable capabilities into their Eclipse-based IDM designtime in a future support package of SAP IDM 8.0.
Let's see if it ever happens.
Cheers!
- SAP Managed Tags:
11 Comments
