Question Re: Proxy a iSeries table with 64bit dbsrv engine in Technology Q&A

Proxy a iSeries table with 64bit dbsrv engine

thomas_duemesnil — Tue, 20 Sep 2011 11:29:53 GMT

Hello

I currently have a problem with a database instance that crashes when I access a proxy table from a iSeries System (DB2).

The Environment is Window 2008 R2 x64, SA 10.0.1.4239, iAccess for Windows V7R1(IBM ODBC Driver v13.00.01.00 Date 1/15/2010 CWBODBC.DLL)

The Software installer has installed a 64bit and a 32bit ODBC Driver. When I start the Database with the 32bit Database engine the System can access the remote table and returns a result set.

When started in 64bit mode a plain select against the proxy table

select * from eurinvx64

Crashes the database with an internal Server error after a couple of seconds. ISQL has already prepared the result set columns but I think this is done based on the locally stored proxy definition.

My knowledge of the iSeries environment is very limited. Does anybody know if a better 64bit ODBC Driver is available ?

I have tested first with the iSeries Software V6R1 but the behaviour was the same.

Any tips are welcome

Re: Proxy a iSeries table with 64bit dbsrv engine

jeff_albion — Tue, 20 Sep 2011 12:00:10 GMT

It seems that there should be a specific 64-bit Microsoft Windows ODBC driver available for the iSeries database:

The ODBC architecture involves an application, driver manager, ODBC driver, and a data source. IBM i Access provides both a 32-bit and 64-bit ODBC driver. The 64-bit ODBC driver is automatically installed along with the 32-bit ODBC driver when running under a 64-bit version of Windows . ODBC applications running in 64-bit versions of Windows will automatically use the appropriate ODBC driver, depending on what bit version the application was compiled for. For example, the 64-bit driver can only be used by a 64-bit application.

Some follow-up questions:

How have you defined the connection string to the remote server, exactly? Are you using a DSN or are you providing a connection string directly?
If you're using a DSN to connect, how have you defined the DSN in Windows, exactly? Did you use the 32-bit or the 64-bit ODBC Administrator to create the DSN? Is it a User or System DSN?
Are you starting the database server on the command line or via a service definition?

Re: Proxy a iSeries table with 64bit dbsrv engine

thomas_duemesnil — Wed, 21 Sep 2011 03:22:36 GMT

I have installed the complete "i Access for Windows" which installs a 32bit and 64bit ODBC Driver.

With %windir%system32odbcad32.exe I have created a system DSN for the proxy connection called BUSA for use with the 64bit database engine.

With %windir%SysWOW64odbcad32.exe I have created a system DSN for the proxy connection called BUSA32 for use with the 32bit database engine.

For testing I build a empty database and defined
CREATE SERVER "S108FC6Px64" CLASS 'DB2ODBC' USING 'BUSA';
CREATE SERVER "S108FC6P" CLASS 'DB2ODBC' USING 'BUSA32';
CREATE EXTERNLOGIN "DBA" TO "S108FC6Px64" REMOTE LOGIN 'europe'
IDENTIFIED BY ENCRYPTED 'XXXXX';
CREATE EXTERNLOGIN "DBA" TO "S108FC6P" REMOTE LOGIN 'europe'
IDENTIFIED BY ENCRYPTED 'XXXXX';
CREATE EXISTING TABLE "DBA"."EURINVx64" ( "LOT#" char(10) NOT NULL, ... more fields ... ) AT 'S108FC6Px64;S108FC6P;EUROPE;EURINV';
CREATE EXISTING TABLE "DBA"."EURINV" ( "LOT#" char(10) NOT NULL, ... more fields ... ) AT 'S108FC6P;S108FC6P;EUROPE;EURINV';

With this database prepared I can start the database with %SQLANY10%x64dbsrv10.exe -x SharedMemory "D:Test DBTest.db" %SQLANY10%win32dbsrv10.exe -x SharedMemory "D:Test DBTest.db"

With the 32bit a select * from eurinv works and returns a result set. With the 64 bit a select * from eurinvx64 brings a Internal error.

I'm running my tests with manually started engine. The reason is that my production database, that runs as a service, was crashing when the event that reads the proxy table was crashing.

Re: Proxy a iSeries table with 64bit dbsrv engine

thomas_duemesnil — Wed, 21 Sep 2011 03:44:22 GMT

The Minidump shows this VERSION=10.0.1.4239
FILENAME=C:ProgramDataSQL Anywhere 10diagnostics
SA10_20110921_093647_1324.crash_log
OS=Windows Vista Build 7601 Service Pack 1
PROCESSOR=X86_64
EXEC_ARCH=X86_64
EXEC_PATH=C:Program FilesSQL Anywhere 10x64dbsrv10.exe
MODULE_PATH=C:Program FilesSQL Anywhere 10x64dbserv10.dll
EXCEPTION_PTR=000000003778F1A0
EXCEPTION_CODE=3221225477
EXCEPTION_FLAGS=0
EXCEPTION_RECORD=0000000000000000
EXCEPTION_ADDRESS=000007FEFA52536E
EXCEPTION_NumParameters=2
EXCEPTION_Param0=0000000000000000
EXCEPTION_Param1=00000000000000B0
TRYING_TO_SAVE_MINI_DUMP C:ProgramDataSQL Anywhere 10diagnosticsSA10_20110921_093647_1324.mini_core
DUMPLEVEL 0
SAVING_MINI_DUMP_COMPLETED
CRASH_LOG_COMPLETE

Re: Proxy a iSeries table with 64bit dbsrv engine

jeff_albion — Wed, 21 Sep 2011 13:26:55 GMT

CREATE SERVER "S108FC6Px64" CLASS 'DB2ODBC' USING 'BUSA'

CLASS 'DB2ODBC' is really intended for "DB2 LUW" setups, not "iSeries for AS/400". Can you perhaps try using the generic 'ODBC' server class, rather than the specific 'DB2ODBC' class?

If changing this does not help, can you open a Command Prompt and type 'dbsupport -sa' and list the submission IDs that result from this command? This will upload the crash dump you have listed above to our servers and we will be able to investigate further.

If this is an urgent issue, we would invite you to open a technical support case directly with technical support.

Re: Proxy a iSeries table with 64bit dbsrv engine

thomas_duemesnil — Thu, 22 Sep 2011 06:21:11 GMT

The change of the class did not change the behaviour.

CREATE SERVER "S108FC6Px64" CLASS 'ODBC' USING 'BUSA';

I have uploaded the dump files with id's 609071-609075. Perhaps you can check if this helps in this case.