As always, we start with a quote:

Captain Spock: All things being equal, Mr. Scott, I would agree with you. However, all things are not equal.

My latest project has me working with DB2 as the IDM backend. We’ve faced several challenges along the way, many in the area of performance and some in just general development. I’ll be addressing some of the performance issues in a future post (need more information from DBAs and other research)

One of the first things I learned about DB2, is how IDM recognizes it. I’m not talking about the Oracle emulation layer, but rather what code IDM uses to determine the database type. Consider the following code from the SAP IDM RDS Solution, which I have since modified:


// Main function: sapc_prepareSQLStatement

// 12MAR2014 – MGP – Added DB2 Support, some cleanup

function sapc_prepareSQLStatement(Par){

var dbType = “%$ddm.databasetype%”;

var script = “sapc_prepareSQLStatement”;

var returnValue=””;

//uWarning (“dbType: “+dbType);

// Processing

if ( dbType == 1 ) { // MS-SQL

// uWarning(“Database Type is MS-SQL.”);

// uWarning(“Par: ” + Par);

return Par;

}

else if ( dbType == 2 ) { // ORACLE

returnValue = uReplaceString(Par, “AS”, “”);

// uWarning(“Database Type is Oracle.”);

// uWarning(“returnValue: ” + returnValue);

return returnValue;

}

else if ( dbType == 5 ) { // DB2

returnValue = uReplaceString(Par, “AS”, “”);

// uWarning(“Database Type is DB2.”);

// uWarning(“returnValue: ” + returnValue);

return returnValue;

}

else {

uErrMsg(2, script + ” SQL Task: invalid database type: ” + dbType );

// return error message and empty result

return “”;

}

}

Note that while SQL Server has a value of 1, Oracle has a value of 2, while DB2 has a value of 5. Makes you wonder what happened to 3 and 4… (Sybase and MySQL, maybe?)

The other thing that I discovered is that when writing values back to a table in DB2 certain values are not welcome. I needed to write a multi-value entry back to the database and I kept receiving error messages. I was finally able to get a useful error message by changing the properties of the To Database pass I was using so that it would do SQL Updating.

To Database.png

When I did that, I received the following message (data has been changed to protect the innocent):

SQL Update failed. SQL:INSERT into recon_roleassign_EPD values (AAA__00000,AAA__00000,BBB__00000) com.ibm.db2.jcc.am.SqlSyntaxErrorException: An unexpected token “!” was found following “PD values (NL__HR005”. Expected tokens may include: “!!”.. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.66.46

I was somewhat confused since this is the “standard” delimiter, at least as far as IDM is concerned, however after checking with a knowledgeable DB2 DBA, he confirmed that not only is ‘!!’ illegal to write, but so is ‘||’. He also mentioned that there is a list out there on the web somewhere, but I was not able to find it. If anyone can find the list of illegal characters, please comment on this entry and I will update the entry. I wound up using ‘;;’ as a delimiter.

As an aside, I have been asked over the years, why use ‘!!’, ‘||’, or even ‘;;’ as a delimiter? The answer as I understand it, is this:

There’s a chance when you use any character as a delimiter that it could be part of the string. Somewhat obvious when you think about common delimiters such as comma, colon, or dash. Even possible for characters such as pipe, slash, or pound. However when you use two like characters as a delimiter the chance that it’s supposed to be part of the actual data string is greatly reduced.

So there you have it, DB2 = 5, and you can’t write ‘!!’ or ‘||’ to a table. What other DB2 tips do you have to share?

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Rene Feister

    Hi Matt,

    first of all thanks for creating this blog.

    I also have some additional findings related to using DB2 as IDM database.

    • IDM database prefix: default is not mxmc_db but IC_DB
    • Custom database prefix cannot be longer than 6 characters (if needed)
    • Database installation is a bit more complex and documentation was not 100% clear on how to fill in the database connectivity fields in MMC when setting up the Identity Center configuration

    I will add more items if we find additional ones.

    Regards,

    René

    (0) 
    1. Matt Pollicove Post author

      Thanks, Rene!

      Good points to add, although I kind of assumed that people knew those things.  I think it’s good though to put it “all on the table”

      I’ve got some more stuff to post.  Just need to find the time… maybe next week. 🙂 , But keep it all coming!

      Matt

      (0) 
  2. Jack Xiong

    Hi Matt,

    Thanks for the blog. I am expecting the next topic — DB2 performance issue because I am using DB2 in my current project and also have performance issue. The system is stable and running pretty slow. 🙂

    Cheers

    Jack Xiong

    (0) 
  3. Matt Pollicove Post author

    I was going to write a separate blog, but I might just rewrite the blog and thread as a document so that the community as a whole can update it since DB2 does indeed seem to be the “unexplored jungle” of the IDM world.  Who knows maybe we can get some of the Development team to contribute as well!

    Working with some of the DBAs the following additonal operations have been performed at our site:

    • Changed the Database parameter DFT_QUERYOPT value from 5 to 9
    • Changed the Database parameter DFT_DEGREE value from 1 to 5
    • Enabled Index Compress on tables MXI_LINK,MXI_ENTRY,MXP_AUDIT and MXI_VALUES
    • Performed Offline Rerog and Runstats on all IC_OPER tables

    Anyone else have anything interesting to add?

    (0) 
  4. Matthew Pecorelli

    Thanks for this info, Matt.  We are also working with DB2 in my current implementation and will compile our recent findings into the thread/document.

    (0) 
  5. Per Krabsetsve

    Note that while SQL Server has a value of 1, Oracle has a value of 2, while DB2 has a value of 5. Makes you wonder what happened to 3 and 4… (Sybase and MySQL, maybe?)


    No need to wonder too long:


    MS-SQL Server

    Microsoft SQL-Server

    1

    Oracle

    Oracle

    2

    Solid

    3

    Obsolete

    MS-Access

    4

    Not in use

    db2

    IBM DB2

    5

    mySQL

    6

    Not in use

    Domino

    7

    Not in use

    HANA

    SAP HANA

    42

    There’s an 8 and 9 that are reserved too, but those are and will be someone elses problem 🙂

    Br,

    Chris

    (0) 
    1. Matt Pollicove Post author

      Thanks, Chris, so HANA is the answer to Life, the Universe, and Everything?

      Glad to see someone has a sense of humor!

      Matt

      PS – Watch for those locking issues in Access! 🙂

      (0) 

Leave a Reply