PolicyServer deletes the server commands based on the "Flush Journal Entries Older than" setting in the SmConsole.
Every time when Policy Server was trying to delete the server commands it was reporting the error:
[18509/40][Fri Nov 18 2011 17:02:24][CSmDbODBC.cpp:192][ERROR] Data source 'SiteMinder Policy Data XXXX ', State = HYT00 Internal Code = 0 - [DataDirect][ODBC Oracle Wire Protocol driver]Timeout expired.,SQLResult=-1 from 'DoExecuteDirect'API at '../../../CSmDbODBC.cpp:2235'
Error is coming because smservercommand4 table has unusually large number of records (in this case 688511 records) which Policy Server was unable to delete.
By default QueryTimeout value is 15 seconds. In order to resolve this issue value of QueryTimeout has to be increased. It depends on environment to environment.
In this particular case where the number of records were 688511 setting the value to 30 seconds didn't resolve the issue. When the value was set to 60 seconds POlicy Server was able to delete all the records in smservercommand4 table.
Add the QueryTimout registry parameter in the following Key:
ConnectionTimeout= 0x1; REG_DWORD
QueryTimeout= 0x3c; REG_SZ
OdbcBrandingLib= sminstallapi; REG_SZ
UpdateSyncDelay= 0; REG_DWORD
Version= 5.0; REG_SZ
In this case 0x3c (hexadecimal) means 60 secs.
Once all the records were deleted you can change the value of QueryTimeout to default.