Troubleshooting Microsoft SQL Server nickname errors

When creating a MSSQL nickname, you might encounter an error if the statistics associated with the remote table not defined or are not complete.

The following example shows an error you might see In the db2diag.log:

2009-04-14-06.31.21.644000-300 I18637947H1357 LEVEL: Error
PID : 4412 TID : 4512 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
EDUID : 4512
FUNCTION: DB2 UDB, mssql wrapper, Mssql_Server::get_error_info, probe:7
MESSAGE : ODBC error txt: 
DATA #1 : Hexdump, 222 bytes
0x05E93B80 : 5B4D 6963 726F 736F 6674 5D5B 4F44 4243 [Microsoft][ODBC
0x05E93B90 : 2053 514C 2053 6572 7665 7220 4472 6976 SQL Server Driv
0x05E93BA0 : 6572 5D5B 5351 4C20 5365 7276 6572 5D43 er][SQL Server]C
0x05E93BB0 : 6F75 6C64 206E 6F74 206C 6F63 6174 6520 ould not locate 
0x05E93BC0 : 7374 6174 6973 7469 6373 2027 434D 5F59 statistics 'CM_Y
0x05E93BD0 : 4F5F 444D 5927 2069 6E20 7468 6520 7379 O_DMY' in the sy
0x05E93BE0 : 7374 656D 2063 6174 616C 6F67 732E 0000 stem catalogs...
0x05E93BF0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x05E93C00 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x05E93C10 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x05E93C20 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x05E93C30 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x05E93C40 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x05E93C50 : 0000 0000 0000 0000 0000 0000 0000 ..............

This error usually occurs when the Microsoft SQL Server wrapper collects the statistics information for nickname columns. If no index or statistics collection is found for the specified target, the remote Microsoft SQL Server reports an error: "Could not locate statistics ..." and a message "DBCC execution completed..." when calling "DBCC SHOW_STATISTICS (table , target)". For more details, see: http://msdn.microsoft.com/en-us/library/aa258821(SQL.80).aspx.

To resolve the problem, run "EXEC sp_createstats" at the remote Microsoft SQL Server to create statistics for all eligible columns for all user tables in the current database.