SAS Connection to DB2 Via ODBC Error

Question

I have been working in SAS connecting to DB2 via ODBC for a while now and this PROC SQL step generally works:

proc sql;
connect to odbc(dsn=DSQ user="UserID" password="Password"); 
execute (set current degree = 'ANY' ) by ODBC;
create table tempTable as select * from connection to odbc (
    select * 
    from schemaName.tableName
);
quit;

However, occassionally I get the following error, and when I get this error I won't be able to run another ODBC query for quite some time because everytime I try to run the query I get the same error:

ERROR: CLI error trying to establish connection: [IBM][CLI Driver] SQL1042C  An unexpected system
   error occurred.  SQLSTATE=58004

After some time the error either resolves itself or I do something that I am not aware of that fixes the issue. This is a very frustrating issue and since I never know how long this issue is going to persist I would like a more robust solution to this issue. I have checked the db2diag.log file and here is the part that describes this error:

2015-06-03-08.17.34.345000-300 I60888H446           LEVEL: Error
PID     : 4452                 TID : 7804           PROC : sas.exe
INSTANCE: DB2                  NODE : 000
HOSTNAME: 
EDUID   : 7804
FUNCTION: DB2 Common, Cryptography, cryptDynamicLoadGSKitCrypto, probe:998
MESSAGE : ECF=0x90000007=-1879048185=ECF_UNKNOWN
          Unknown
DATA #1 : unsigned integer, 4 bytes
60
DATA #2 : String, 11 bytes
gsk8sys.dll

I was trying to find an example to put in this post so I ran this snippet of code in SAS to see if the error would come back, however it seemed to have resolved the error because after running this piece of code, I ran the initial code and it worked. Here is the code that seemed to have resolved the issue.

proc sql;
    connect to odbc(dsn=DSQ user="UserID" password="Password"); 
    execute (set current degree = 'ANY' ) by ODBC;
    create table column_names as select * from connection to odbc (
        select * from sysibm.syscolumns
    );
quit;

I have tried googling this issue, but there isn't much help on this particular error. Is there any reason that the second SAS code would have fixed the issue I was having? Is there any way to fix this problem so it won't come back in the future?

Please note, when the error occurs, I am still able to run queries via ODBC in Microsoft Access without any problems. It appears this is an issue with just a particular instance.

System Setup:

Windows 7 64-bit

SAS 9.3 (32)

DB2 v10.5.300.125

Thanks in advance for your help!

Update: On a few occasions, I was able to go into task manager and delete some processes that were still running and then this issue would resolve itself, however today when this problem occurred, those processes weren't there. Any thoughts on this would be greatly appreciated.

Answer 1

The symptom may be depended on the installation order of Db2 connect and a program (sas.exe). If DB2 connect is installed first, it should not be occurred. So it is suggested to remove and re-install both products once then install Db2 connect first.

Hope this helps.