Sql-server – Query DB2 without using Openquery

Thanks to Jon Seigel, it seems like the issue in part due to the CPU affinity setting in SQL.

We deployed this VM with SQL from an image which causes a known issue as listed here (scenario #3):

• IBM - Slow performance in Controller due to inability to use available CPU cores in SQL server

Removing the automatic affinity option and setting the affinity mask option to use all the CPUs available resolved the issue.

• Affinity Mask Option

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 15;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

We have updated our image to include this option. Thanks everyone for the help in this issue.

Please note, this option is deprecated in SQL Server 2012 and is not recommended for use. Additionally, it is related to processor thread handling in Windows 2000 and Windows 2003 operating systems.

• Affinity Mask Option - SQL Server 2012

There are three (well, actually four...) ways to execute export in DB2.

There is the db2export API (.sh in Unix/Linux, .exe in Windows). This can be executed from anything that understands command shell.

There is the export command to the db2 command. This can be run from a DB2 interactive session, or from the command shell.

There is also the export command through the ADMIN_CMD stored procedure. This allows you to invoke the export from a stored procedure which is accessable through anything that can execute SQL against DB2. This is probably the option you are looking for if you want to do export via SQL through another tool.

There is also the db2move utility which can perform an export, but that is done via command shell as well.