topic ODBC SQL crash in QlikView

ODBC SQL crash

Mon, 27 Apr 2009 12:43:12 GMT

I am in my very early days of using Qlikview with server based Lotus Notes databases using Notes SQL ODBC drivers. I have had success until now. I am trying to load around 80,000 records form a view. The SQL script runs and the load ceases at record 16536. There does not seem to be anything strange about record 16537.

Restriciting the load via where statements does not work I just get "Column not Found" error.

This is the error data from Qlikview

SQL Error

SQL Scriptline

SQL State 00000

SQL Select * "from Maintenance all by REquest No all" (the view name)

In the nsd log file (which would have bee ngenerated by the Notes SQL driver or such like these are the lines that seem to realte to the crash:

Generated Messages:

INFO (0): Walk mem for process NLNOTES (13d4)
INFO (0): Walk mem for process ntaskldr (10c8)
INFO (0): Walk mem for process nsqle32 (1330)
INFO (0): terminated process [ NLNOTES: 5076]
INFO (0): terminated process [ntaskldr: 4296]
INFO (0): terminated process [ nsqle32: 4912]
INFO (0): Deleting pid.nbf
WARNING (0): Debugger still attached to 3 processes

Please send the following files:

NSD Log : C:\Program Files\lotus\notes\data\IBM_TECHNICAL_SUPPORT\nsd_W32I_T18036662TP5000_2009_04_27@14_18_50.log
Core Dump : C:\Program Files\lotus\notes\data\IBM_TECHNICAL_SUPPORT\core.nsqle32.W32I_T18036662TP5000_2009_04_27@14_18_50

Am I just running out of RAM. I have had no trouble with views with a few thousand records but it is my undersanding that Qlkview can handle very large amounts of data.

Any feedback will help

ODBC SQL crash

Mon, 27 Apr 2009 13:41:02 GMT

Is it possible to use all column names in your SQL statement instead of using *?

And did you try it withou the quotes ("): SQL Select * from Maintenance all by REquest No all

ODBC SQL crash

Tue, 28 Apr 2009 06:10:51 GMT

I removed the quotes but kept the *. It still fails at the same point- giving me the Lotus Notes error (which indicateds the ODBC driver fails) It fails ath the same point loading all records up to 16536.

ODBC SQL crash

Tue, 28 Apr 2009 06:28:52 GMT

Does it work if you run the same select statement in a tool other than QlikView? It doesn't seem like QlikView is the source of the issue.

ODBC SQL crash

Oleg_Troyansky — Tue, 28 Apr 2009 06:29:28 GMT


bming wrote: I have had no trouble with views with a few thousand records but it is my undersanding that Qlkview can handle very large amounts of data.

Your trouble is not in QlikView yet - it's somewhere between Notes and the ODBC driver. You need to find out the limitations of the ODBC transfer - is there any threshold that could be configured.

Try going through various settings within your ODBC Data Source definitions. For example, AS/400 ODBC driver allows configuring the "block size" for the individial "packets" being sent between the database and the client. Depending on the size of the database and the performance of the machine, this parameter needs to be tweaked to achieve optimum performance. When it's set too high, the database issues sporadic messages and failures. There might be something similar for Notes (never had a chance to load Notes data into QlikView...)

good luck,

Oleg

ODBC SQL crash

rwunderlich — Tue, 28 Apr 2009 06:32:29 GMT

Qlikview can handle large amounts of data, but it sounds like the NotesSQL driver is choking.

What version of NotesSQL are you using?

Can you do large selects from other views? That is, does the problem only appear with this nsf/view? How many columns are in your view?

Do you have large text fields in the select? If so, can you try decreasing the MaxVarcharLen and MaxLongVarcharLen driver parms?

Can you post the NSD .log file (as an attachment)?

-Rob

ODBC SQL crash

rwunderlich — Tue, 28 Apr 2009 06:38:18 GMT

One more thought. It sounds like you've identified the documents represented by rows 16536 & 16537. Can you delete these documents (from a copy of the DB) to see if the error goes away or still occurs on that row#?

-Rob

ODBC SQL crash

Tue, 28 Apr 2009 06:49:24 GMT

The ODBC driver version is 3.02.2006.706 from IBM. I am using Domino Vers 6.5.2 and version 8.5 of Qlkview. I have attached the latest log file from the last failure. [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4061.nsd.log:550:0]

ODBC SQL crash

rwunderlich — Tue, 28 Apr 2009 07:09:34 GMT

If this view has very complex documents, see if this is a good fit for the problem:

http://www-01.ibm.com/support/docview.wss?uid=swg21164400

I believe the NotesSQL trace facility can give you a node count for the statement.

Not sure which release 3.02.2006 corresponds to -- I'm used to seeing an alpha at the end of the release like "3.02g". I'm actually using the Notes 8 driver now.

The latest 3.02 release is 3.2i. If your's is older than that, you might want to try an upgrade to 3.02i.

..and then contact IBM.

-Rob

ODBC SQL crash

Tue, 28 Apr 2009 11:00:59 GMT

I limited the Load to just one column of the view instead of the ten or so and actually managed to load slightly FEWER records. That is wierd. I used the ODBC trace - the resultant file was empty. No other log file was created

ODBC SQL crash

Tue, 28 Apr 2009 11:40:37 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0763.latest-nsd.log:550:0]I tried the version 8 SQL driver with same failure. Reverted back because of other problems with the driver. Tried again with a different copy of the database - no improvement. Tried with selecting one column and got a worse result. Latest log file attached.

ODBC SQL crash

Tue, 28 Apr 2009 16:22:36 GMT

I think that the SQL is not match SQL 92 standard.

Please try it like

SQL Select * from "from Maintenance all by REquest No all"

or Select * from [from Maintenance all by REquest No all]

Good luck!

ODBC SQL crash

Thu, 30 Apr 2009 12:42:23 GMT

The error in the link in your reply would seem to indicate that it would occur when the first document was hit. In my case the Notes client falls over at document 8729. The error only occurs with this database. It does not directly relate to the number of documents (around 65000) as I have used the same procedure with even larger document numbers in different Lotus Notes files. I cannot see anything odd with the documents. They all look the same. It's some unsolvable anomaly.

Latest error log attached

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3301.ndserror.log:550:0]

ODBC SQL crash

rwunderlich — Thu, 30 Apr 2009 13:23:08 GMT

1. Can you copy the database (using Notes)? Sometimes this surfaces structural problems with the nsf.

2. Have you tried deleting document 8729 to see if the error moves?

3. Have you tried running fixup on this DB?

From the nsd file, your error is "LookupHandle: null handle" trying to unlock something. You'll proabably have to take this to IBM support if deleting a document doesn't fix it.

If you google "LookupHandle: null handle" you'll find a number of reported problems (not with NotesSQL, but other apps) where a string field has a null value. Do any of the view fields have null values? If so, can you add column formula to make them empty '' strings?

-Rob

Re: ODBC SQL crash

edmondo_tassi — Tue, 16 Apr 2013 10:20:13 GMT

Hello, the star * in sql select is an issue.

Try this in spite of:

SQL SELECT "TABLE NAME".*

FROM "TABLE NAME".

It works 2 me.

Bye

p.s. Keep in mind to modify in 4096 the transfer rate on sql notes configuration, is quite faster.