SAS Viya 3.5 ODBC DB2 , UNPREPARE class usage ended by SQL0104N

[N224]

Hi ! I've finally got to work SAS ODBC connector with DB2, at Centos 7 using unixODBC (after looot of problems).

Finally deployment was based on https://blogs.sas.com/content/sgf/2017/11/16/connecting-sas-db2-database-via-odbc-without-tears/  (IMO with tears 😐 ) .

Everything works until I use CAS (libname procedures work).

After 

cas mysession2;
caslib casdb3 datasource=(srctype="ODBC" username="..." password="..." 
schema="..."
odbc_dsn="db2odbc" dm_unicode="utf-16"
);

proc casutil; 
list files incaslib="casdb3";
run;

I've got successful prepared caslib but, when proc casutil plays it comes with error :

ERROR: An error occurred during transcoding on column REMARKS.
ERROR: Function failed.

So.. i've listed all fields from all tables in column called "REMARKS" (including mostly SYSIBM tables), and all of them are NULL.

Then, I came up to trace whole db2 communication by setting this at DB2 :

db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TracePathName /tmp/db2
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceComm 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlush 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceTimeStamp 3

And finally I got trace and targeted the problem without ANY idea how to resolve it without your help

[02/14/2020 13:59:55.928972] SQLExecDirectW( hStmt=1:2, pszSqlStr="UNPREPARE", cbSqlStr=9 )
[02/14/2020 13:59:55.929214]     ---> Time elapsed - +4.000000E-005 seconds
[02/14/2020 13:59:55.929347] ( StmtOut="UNPREPARE" )
[02/14/2020 13:59:55.929411] 
[02/14/2020 13:59:55.929461] ( Package="SYSSH200          ", Section=65 )
[02/14/2020 13:59:55.929543] 
    sqlccsend( Handle - 0139913848053056 )
    sqlccsend( ulBytes - 134 )
    sqlccsend( ) rc - 0, time elasped - +4.400000E-005
    sqlccrecv( timeout - +0.000000E+000 )
    sqlccrecv( ulBytes - 233 ) - rc - 0, time elapsed - +1.944000E-003
    Elapsed Server Processing Time - +1.027000E-003
    Elapsed Server Processing Time - +2.900000E-005
[02/14/2020 13:59:55.931855] ( COMMIT REPLY RECEIVED=1 )
[02/14/2020 13:59:55.931940] 

[02/14/2020 13:59:55.932034] SQLExecDirectW( )
[02/14/2020 13:59:55.932090]     <--- SQL_ERROR   Time elapsed - +3.118000E-003 seconds

[02/14/2020 13:59:55.932152] SQLGetDiagRecW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2, iRecNumber=1, pszSqlState=&00007f404d625ae0, pfNativeError=&00007f404d625ad8, pszErrorMsg=&00007f404d625af0, cbErrorMsgMax=512, pcbErrorMsg=&00007f404d625adc )
[02/14/2020 13:59:55.932409]     ---> Time elapsed - +6.200000E-005 seconds
[02/14/2020 13:59:55.932729] ( iRowNumber=-1, iColumnNumber=-2 )
[02/14/2020 13:59:55.932828] 

[02/14/2020 13:59:55.932862] SQLGetDiagRecW( pszSqlState="42601", pfNativeError=-104, pszErrorMsg="[IBM][CLI Driver][DB2/NT64] SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "UNPREPARE".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
", pcbErrorMsg=180 )
[02/14/2020 13:59:55.933040]     <--- SQL_SUCCESS   Time elapsed - +8.880000E-004 seconds

[02/14/2020 13:59:55.933084] SQLGetDiagRecW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2, iRecNumber=2, pszSqlState=&00007f404d625ae0, pfNativeError=&00007f404d625ad8, pszErrorMsg=&00007f404d625af0, cbErrorMsgMax=512, pcbErrorMsg=&00007f404d625adc )
[02/14/2020 13:59:55.933350]     ---> Time elapsed - +4.400000E-005 seconds

[02/14/2020 13:59:55.933388] SQLGetDiagRecW( )
[02/14/2020 13:59:55.933422]     <--- SQL_NO_DATA_FOUND   Time elapsed - +3.380000E-004 seconds

[02/14/2020 13:59:55.933513] SQLFreeHandle( fHandleType=SQL_HANDLE_STMT, hHandle=1:2 )
[02/14/2020 13:59:55.933607]     ---> Time elapsed - +9.100000E-005 seconds
[02/14/2020 13:59:55.933647] ( Caching Statement Structure=1 )
[02/14/2020 13:59:55.933708] 

[02/14/2020 13:59:55.933742] SQLFreeHandle( )
[02/14/2020 13:59:55.933776]     <--- SQL_SUCCESS   Time elapsed - +2.630000E-004 seconds

[02/14/2020 13:59:55.934144] SQLFreeHandle( fHandleType=SQL_HANDLE_STMT, hHandle=1:1 )
[02/14/2020 13:59:55.934260]     ---> Time elapsed - +3.680000E-004 seconds

[02/14/2020 13:59:55.934406] SQLFreeHandle( )
[02/14/2020 13:59:55.934447]     <--- SQL_SUCCESS   Time elapsed - +3.030000E-004 seconds

[02/14/2020 13:59:55.934531] SQLDisconnect( hDbc=0:1 )
[02/14/2020 13:59:55.934603]     ---> Time elapsed - +8.400000E-005 seconds
    sqlccsend( Handle - 0139913848053056 )
    sqlccsend( ulBytes - 28 )
    sqlccsend( ) rc - 0, time elasped - +2.800000E-005
    sqlccrecv( timeout - +0.000000E+000 )
    sqlccrecv( ulBytes - 76 ) - rc - 0, time elapsed - +1.565000E-003
    sqlccrecv( timeout - +0.000000E+000 )
    sqlccrecv( ulBytes - 0 ) - rc - 54, time elapsed - +7.940000E-004

[02/14/2020 13:59:55.941164] SQLDisconnect( )
[02/14/2020 13:59:55.943363]     <--- SQL_SUCCESS   Time elapsed - +8.832000E-003 seconds

[02/14/2020 13:59:55.944400] SQLFreeHandle( fHandleType=SQL_HANDLE_DBC, hHandle=0:1 )
[02/14/2020 13:59:55.944903]     ---> Time elapsed - +1.037000E-003 seconds
[02/14/2020 13:59:55.945343] ( Number of allocations left before we freed the Pool=1 )
[02/14/2020 13:59:55.945464] 

[02/14/2020 13:59:55.945524] SQLFreeHandle( )
[02/14/2020 13:59:55.945571]     <--- SQL_SUCCESS   Time elapsed - +1.171000E-003 seconds

[02/14/2020 13:59:55.945634] SQLFreeHandle( fHandleType=SQL_HANDLE_ENV, hHandle=0:1 )
[02/14/2020 13:59:55.945761]     ---> Time elapsed - +6.300000E-005 seconds

[02/14/2020 13:59:55.945864] SQLFreeHandle( )
[02/14/2020 13:59:55.945914]     <--- SQL_SUCCESS   Time elapsed - +2.800000E-004 seconds

In my opinion it goes like :

SAS:  "Hey DB2, let's use UNPREPARE class, same as Microsoft got, I hope you like it"

DB2:  "Nope."

plz help 🙂

[alexal]

@N224 ,

I would like to see your odbc.ini file. What did you specify for db2odbc/Driver? Also, I'm wondering if you have "QEWSD=" in your odbc.ini file?

[N224 · Accepted solution]

There is no need to upload new files, i've got it to work and here's my solution :

https://communities.sas.com/t5/Administration-and-Deployment/SAS-Viya-3-5-ODBC-DB2-connect-Tutorial/...