Bending SAP IDM Stored Procedures to your Will

[former_member2987]

Everything you can imagine is real.” ― Pablo Picasso

One of the things I’ve always been hesitant to do in my IDM career is play with the actual database objects such as the Stored Procedures. There really should not be a  problem with using them as long as you are careful and use common sense. For example, making changes to the actual Stored Procedure code is a bad idea. At the very least your changes will be overwritten in the next update, and at worst, they could potentially break the update process (or IDM itself!) But I think as long as the basic precautions are taken, the stored procedures can be used with some effectiveness to enhance how we use IDM. The main thing is to go slowly, test what you are working with thoroughly and use examples from existing working jobs and the Stored Procedure definitions themselves so you know what is expected.

If you want to see how this is done in general, take a look at an initial load job, when IDM goes to create the account attribute in the very first pass (at least in the AS Java (Database) – Initial Load job.)

From this screen shot, we see a To Database pass and that it is possible to execute several operations in the same pass. Also the SQL updating option has been selected. This option allows the IDM engine to act as a direct gateway to the back end database where you can run almost any valid SQL command. I don’t know that there are any real exceptions to this save that whatever command is to be executed needs to have permissions for the [mxmc]_rt account.

Looking a little deeper, we see there’s a script being executed to prepare the statement called sap_care_callStoredProcedure and that it takes two arguments separated by the standard IDM delimiter of ‘!!’ Let’s take a quick look to see what it does:

// Main function: sap_core_callStoredProcedure

//

// Call/execute a stored procedure.

// Parameters (separated by "!!"):

// - Name of the stored procedure

// - List of procedure arguments (separated by "," and string arguments enclosed in '' - actually this is the syntax accepted by MSSQL and Oracle at least)

// Note: The <prefix>_rt user/role must be allowed to execute the respective procedure!

function sap_core_callStoredProcedure(Par){

var ParComponents = Par.split("!!");

var procedure = ParComponents[0];

var arguments = ParComponents[1];

var dbType = "%$ddm.databasetype%";

var result = "";

if (dbType == 1) { // MSSQL

result = "execute " + procedure + " " + arguments;

} else { // Oracle

result = "call " + procedure + " (" + arguments + ")";

}

return result;

}

So basically, all this script does is break apart the pieces and then add the appropriate database command based on database type (be careful if you’re using DB2, I have not tested it, but if issues arise, please refer to this  article. I’m pretty sure that the proper database command is ‘call’ since DB2 is being used in Oracle emulation mode.) When troubleshooting you might want to add in a uInfo (result) or uWarning (result) in just before the return statement, it really helps sometimes.

The nice thing about this using this script is that it makes it much easier to call the Stored Procedures and work with the various databases supported by IDM. You might also notice that when the Oracle Database is used that the stored procedure arguments are also encased in parentheses ()

For a practical example of how to use this functionality, I’m going to work with the stored procedure mxi_xcreate_objectclass. This is the stored procedure that is used by IDM to create a new EntryType. (The original architecture of IDM was heavily influenced by LDAP directories) Personally, I’ve wanted to be able to do this particular operation on some past projects where there’s been a need to create EntryTypes in multiple environments and systems, so putting this into a job helps to automate it. Also as a consultant, it helps me to “productize” some of the enhancements I develop, which makes them easier to distribute.

So calling the Stored Procedure seems pretty easy as it’s called as follows:

$FUNCTION.sap_core_callStoredProcedure(mxi_xcreate_objectclass!!1,'ZMY_ENTRYTYPE,'An EntryType Example','My Entry Type',0,1,NULL)$$

However I was a little confused when I first tried to call it as I could not get the last parameter, Pocid parameter to populate correctly, so I needed to look at the code to figure things out (including the names of the parameters) This is probably also a good time to mention that there is no documentation for working with the stored procedures, so you probably should take some time to review any code before you use it.  It's a good way to learn about how IDM's innermost workings are built and also gives you an idea of what is expected when using any of these Stored Procedures.

Pocid correlates to the MSKEY and is generated during the execution of the stored procedure. When I looked through the code and saw it was checking if that parameter was NULL, I knew how to populate it and things worked just fine.

So there’s a couple of things to consider when working with these stored procedures.

• There’s not too much documentation here, so be careful.
• Don’t’ make changes to the Stored Procedure code, comments might be OK, but remember that they will potentially be removed with the next update, so be careful.
• Working directly with the Stored Procedures has direct impact on the Identity Store with fewer built in safeguards, so make sure things are backed up if they are important, and be careful.

Are you sensing the overriding theme here? :smile:

If you’d like to take this example a step further, go ahead and use the Stored Procedures mxi_schema_create_attr_ns and mx_schema_add_attr_to_oc_ns to create a new attribute and then add it to the EntryType. Examples can be found in the SAP NetWeaver Templates (start with the Initial Load job for a repository)  If you have questions, post them in comments or start a discussion thread if they are more general.

So go ahead and try and use some of the Stored Procedures to advance your IDM needs, just be careful. I’ll be working on a somewhat more complicated example next and as soon as my testing and verification is complete, I’ll be sharing again with the community.

I have not been able to test this with Version 8 yet.  If anyone does, please let me know how it works and what you needed to do.