DB2 = 5 and other interesting facts

[former_member2987]

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.

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?