Brian:
Here's the info.. Should have all you were asking for... thx, jay >>> I actually have the linked server created and pulling information. But the connection is flakey. As in periodically I get a ‘catastrophic error’. This is if I pull significant data through the linked server using SQL Server Management Studio (2005). ---This is the Error--- This is the first error: OLE DB provider "MSDASQL" for linked server "PAUVQA2" returned message "[IBM][UVODBC][2300439]Error ID: 1 Severity: FATAL Facility: MEMERR - Out of memory.". Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "PAUVQA2". --- If I run another query against that linked server ; Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "PAUVQA2" reported an error. The provider reported an unexpected catastrophic failure. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "PAUVQA2". -----------end error--- At that point I am out of luck and no additional queries to the linked server will work. I ‘believe’ I am using ODBC drivers. I have the UNIDK installed (from 10.2 disc) and use the Universe UCI Config Editor As well as the Universe ODBC Management tool – Additionally – The Linked Server SQL Code is - /****** Object: LinkedServer [PAUVQA2] Script Date: 06/11/2010 10:59:49 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'PAUVQA2', @srvproduct=N'IBM Universe for ODBC', @provider=N'MSDASQL', @datasrc=N'PAUVQA2', @location=N'ODBCPROD' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PAUVQA2',@useself=N'False',@locallogin=NULL,@rmtuser=N'BISVCS',@rmtpassword='########' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'rpc out', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'PAUVQA2', @optname=N'use remote collation', @optvalue=N'true' +++++++++ We have used the –G option in the SQL Server Service startup to increase to 2048 (2 gig) . the protected memory pool. The server is a 16 processor Windows 2003 server with 32 gig of memory - 22 gig is allocated to SQL server. Increasing the –g option from the default 348 to 2048 allows us to run a day’s worth of transactions (most usually) and we restart the sql server service nightly as a scheduled job to flush out this memtoleave area. +++++++++ We use the following code to interrogate the MemtoLeave region of memory - SELECT Size = VAS_Dump.Size, Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size], region_allocation_base_address [Base] FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION SELECT CONVERT(VARBINARY, region_size_in_bytes) [Size], region_allocation_base_address [Base] FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VAS_Dump GROUP BY Size ) INSERT INTO tbl_chkMemToLeaveSpace SELECT GETDATE(), SUM(CONVERT(BIGINT,Size)*Free)/1024, CAST(MAX(Size) AS BIGINT)/1024 FROM VAS_Summary WHERE Free <> 0 ++++++ This has given us insight into the 2 gig protected memory pool… it seems to us that there is a memory leak. Only restarting the SQL Server Service remediates this collapse. The collapse doesn’t tear down sql server - but no calls using the linked servers to UV will work without a restart. --- I reset the server service at about 10:57 – then executed a large Query select * from PAUVQA2…Polmast this is the data from the Memtoleave procedure – we snapshot that memory region every 5 minutes and record it: Datetime TotalAvailMemKB MaxFreeSizeKB 6/11/2010 11:17 44024 2728 6/11/2010 11:16 44024 2728 6/11/2010 11:15 44024 2728 6/11/2010 11:14 45916 2728 6/11/2010 11:13 48200 2728 6/11/2010 11:12 48200 2728 6/11/2010 11:11 48324 2728 6/11/2010 11:10 47880 2728 6/11/2010 11:09 47880 2728 6/11/2010 11:08 138432 9216 6/11/2010 11:07 233336 18652 6/11/2010 11:06 329260 26940 6/11/2010 11:05 439516 53096 6/11/2010 11:04 515536 63932 6/11/2010 11:03 597136 63932 6/11/2010 11:02 707848 93412 6/11/2010 11:01 839004 122216 6/11/2010 11:00 905124 122216 6/11/2010 10:59 917204 157412 6/11/2010 10:58 917204 157412 6/11/2010 10:57 916688 157412 Brian Leach wrote: > > Hi > > 1. The error messages themselves were stripped from the mail - please can > you repost them (and not as images, the list server strips all attachments > and non-textual entries). > > 2. You haven't said what version or edition of SQL Server you are running. > > 3. Whilst your query to analyze memory is interesting, it is of little use > without knowing the queries you are executing against the linked server > <grin>... can you provide some details of those? > > > Brian > > > > > -----Original Message----- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of ptnaman > Sent: 10 June 2010 3:34 PM > To: u2-users@listserver.u2ug.org > Subject: [U2] SQL server to Universe via ODBC problems > > > This document describes a continuing problem we have been experiencing > using > a SQL Server linked server connection to an IBM UniVerse data source via > ODBC. > > This issue manifests itself when querying the linked server, and appears > to > be related to an overall gradual loss of memory. We have tested using both > our production server as well as a virtual test environment and see the > same > results. > >>> Universe environment > Universe version 10.2.10 > Microsoft Windows Server 2003 R2 > Service Pack 2 > > In our production environment we are using the following versions: > Microsoft SQL Server Management Studio > 9.00.4035.00 > Microsoft Analysis Services Client Tools > 2005.090.4035.00 > Microsoft Data Access Components (MDAC) > 2000.086.3959.00 (srv03_sp2_rtm.070216-1710) > Microsoft MSXML > > 2.6 3.0 6.0 > Microsoft Internet Explorer > > 7.0.5730.13 > Microsoft .NET Framework > > 2.0.50727.3607 > Operating System > > 5.2.3790 (Windows Server 2003 R2 with 32GB RAM) > > We use the UniOLEDB Provider for the linked server and we have checked for > the latest UniOLEDB drivers. We have tried changing the MemToLeave area by > setting the �g option to 3072 - which delays the problem but does not stop > it from occurring, it just happens less frequently. > > In our virtual test environment, we are using the following versions: > Microsoft SQL Server Management Studio > 9.00.4035.00 > Microsoft Analysis Services Client Tools > 2005.090.4035.00 > Microsoft Data Access Components (MDAC) > 2000.086.3959.00 (srv03_sp2_rtm.070216-1710) > Microsoft MSXML > > 2.6 3.0 6.0 > Microsoft Internet Explorer > > 8.0.6001.18702 > Microsoft .NET Framework > > 2.0.50727.3607 > Operating System > > 5.2.3790 (Windows Server 2003 R2 with 2GB RAM) > > In test, we have utilized the following query to monitor memory usage > while > performing large queries against the linked server: > > ;WITH VAS_Summary AS > ( > SELECT > Size = VAS_Dump.Size, > Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1 > END), > Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) > FROM > ( > SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size], > region_allocation_base_address [Base] > FROM sys.dm_os_virtual_address_dump > WHERE region_allocation_base_address <> 0x0 > GROUP BY region_allocation_base_address > UNION > SELECT CONVERT(VARBINARY, region_size_in_bytes) [Size], > region_allocation_base_address [Base] > FROM sys.dm_os_virtual_address_dump > WHERE region_allocation_base_address = 0x0 > ) > AS VAS_Dump > GROUP BY Size > ) > > INSERT INTO tbl_chkMemToLeaveSpace > SELECT GETDATE(), SUM(CONVERT(BIGINT,Size)*Free)/1024, > CAST(MAX(Size) AS BIGINT)/1024 > FROM VAS_Summary > WHERE Free <> 0 > > The results of this monitoring have always shown that we start with > approximately 836132KB TotalAvailMem and 122216KB MaxFreeSize. Then, as > the > queries run, the amount of available and free memory have continually > dropped until there is only 2000-3000KB free, and then we see any one of > the > following error message(s) during any attempted query execution: (In this > example � when we received this message, our monitoring showed 43248KB > TotalAvailMem and 2728KB MaxFreeSize) > > OLE DB provider "MSDASQL" for linked server "pauvqa1" returned message > "[IBM][UVODBC][0000000]Unable to allocate sufficient memory!". > Msg 7330, Level 16, State 2, Line 1 > Cannot fetch a row from OLE DB provider "MSDASQL" for linked server > "pauvqa1". > > After trying to query a second time: > > Msg 7399, Level 16, State 1, Line 1 > The OLE DB provider "MSDASQL" for linked server "pauvqa1" reported an > error. > The provider reported an unexpected catastrophic failure. > Msg 7330, Level 16, State 2, Line 1 > Cannot fetch a row from OLE DB provider "MSDASQL" for linked server > "pauvqa1". > > Additionally, we have seen the following series of error messages when > performing the same test of using SQL queries, combined with utilizing a > custom Access ADP (access data project) that calls stored procedures > relying > on the Uni OLEDB driver as part of the programming. In this example, we > saw > the following error message in SQL Server: > > > > Then we tried the Access ADP functionality, which calls a stored procedure > that relies on the UniVerse linked server (Uni OLEDB) and got the > following > series of error messages: > > > > > > > > > We have tried the DBCC options for clearing the various caches but it did > not help. The only way we have been able to resolve the issue and restore > the memory is to restart the MSSQLSERVER service. > > > -- > View this message in context: > http://old.nabble.com/SQL-server-to-Universe-via-ODBC-problems-tp28843932p28843932.html > Sent from the U2 - Users mailing list archive at Nabble.com. > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > > Internal Virus Database is out of date. > Checked by AVG - www.avg.com > Version: 9.0.819 / Virus Database: 271.1.1/2911 - Release Date: 06/01/10 > 19:25:00 > > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > > -- View this message in context: http://old.nabble.com/SQL-server-to-Universe-via-ODBC-problems-tp28844105p28878425.html Sent from the U2 - Users mailing list archive at Nabble.com. _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
