topic Re: Creating QVDs fails but no error message in QlikView

Creating QVDs fails but no error message

Mon, 25 Aug 2014 13:21:39 GMT

I have a script that creates QVDs for another qvw to use. It currently takes about 22 hours to run. When it finishes running, the log file looks as though everything completed as expected (shows number of rows written, no errors are shown). But in QEMC it shows that the task failed, and when I try to load the resulting QVDs into another qvw, I get 0 rows.

Anyone seen this before or have ideas of what i need to look at?

Re: Creating QVDs fails but no error message

giakoum — Mon, 25 Aug 2014 13:26:26 GMT

can you see in the log that the table stored into the qvd had records? Could you please attach the log file? 22 hours?? monster ...

Re: Creating QVDs fails but no error message

jonathandienst — Mon, 25 Aug 2014 13:30:10 GMT

If the script is trying to write to a non-existent folder, or if the target file is locked for some reason, you will get what you observed.

HTH

Jonathan

Re: Creating QVDs fails but no error message

Anonymous — Mon, 25 Aug 2014 13:37:16 GMT

Have you tried to store one or two qvd:s just to check that qvds are being created with your script?

Or try running it in debug mode with say 1000 rows just to check. I agree with Ioannis 22 hours is a monster. What are the specs of the machine you are trying to run it on?

Re: Re: Creating QVDs fails but no error message

Mon, 25 Aug 2014 13:37:20 GMT

Yes, it is a monster unfortunately... I want to try and reduce the amount of data being pulled or somehow optimize the query but right now I need to just get the doc delivered because I'm a week past my deadline due to wrestling this.

I've attached the log file.

Re: Creating QVDs fails but no error message

giakoum — Mon, 25 Aug 2014 13:56:21 GMT

"it takes 20 minutes to store 1 billion records

but 3 hours to store 422 million records."

sorry my mistake! 429 million records in 10 minutes.

try to store locally and see what happens. With the debug mode and 1000 lines only you should be able to determine if this works or not. Do you know where to find debug? It is in the script windows, next to the reload button.

There might be timeout issues here that prevent the store from completing.

Check disk space, access rights, server restarting during the night etc.

Re: Creating QVDs fails but no error message

rustyfishbones — Mon, 25 Aug 2014 14:01:07 GMT

How are you calling this script?

Are you using the Include Statement for the QVD Script?

Re: Creating QVDs fails but no error message

giakoum — Mon, 25 Aug 2014 14:05:43 GMT

is this the store command syntax you are using :

store Orders into \\na01\qvshare_dev\Make Orders_qvd_test.qvd

store Orders into \\na01\qvshare_dev\Make Orders_qvd_test.qvd (qvd)

Re: Creating QVDs fails but no error message

alematex — Mon, 25 Aug 2014 14:07:59 GMT

do you have a pasword in your file?

Re: Re: Creating QVDs fails but no error message

Mon, 25 Aug 2014 14:26:12 GMT

I don't think that is the issue... I can see QVD files written in the target location and they are quite large. I'm just not pulling any data in when I run the script in the other QVW.

Re: Re: Creating QVDs fails but no error message

Mon, 25 Aug 2014 14:27:03 GMT

I'm not sure how to answer your question, and i'm not familiar with the Include statement, so no i'm not using that.

Re: Re: Creating QVDs fails but no error message

Mon, 25 Aug 2014 14:27:36 GMT

No, no passwords, except on the database connection.

Re: Creating QVDs fails but no error message

giakoum — Mon, 25 Aug 2014 14:28:59 GMT

then this is the wrong log you attached

load * from \\na01\qvshare_dev\Make Orders_qvd_test.qvd is the syntax I guess.

if you create 100 rows qvd with debug, can you load that?

Re: Creating QVDs fails but no error message

jonathandienst — Mon, 25 Aug 2014 14:32:21 GMT

This what I suggest:

Create a text file on your desktop. Call it more.bat

Edit the file so that it consists of the line:

more %1

Now drag the qvd file and drop it on the more.bat icon.

You should now see the qvd's XML header in the cmd window.

Scan this for the CreatorDoc, the date and (at the end of the field metadata, the row count and the data lineage of QVD

You can use this information to see when the QVD is being updated and what has been updated. This may assist in diagnosing the problem.

HTH

Jonathan

Re: Creating QVDs fails but no error message

christian77 — Mon, 25 Aug 2014 14:50:17 GMT

Hi.

I think it is a very large file that complicates it all. 22 hours is to much. You run it on monday when you get to work. You see the result on tuesday.

You may want to parallelize your script, which means, divide tasks and run them at the same
time, or one every 15 minutes. If it is only one table, try to divide by some field/s or key field/s.

There is also the incremental reload, and even partial reload.

Good luck.

Re: Creating QVDs fails but no error message

Wed, 27 Aug 2014 14:36:34 GMT

OK, i removed a 'Where Exists' clause in the load of the 2nd table/QVD which I think was causing the error, because it now loads, although it takes about 30 hours from beginning to distribute. But now it's so huge it won't render in Access Point or load in developer. One strange thing i found is that the log file says it's writing 1.1 Billion records to the QVD but when I run a 'SELECT COUNT(*)' against the database, there should only be ~350M records so i don't know why it's loading 3x more rows. It's just a straight table load with no joins that could be causing duplication.

I'm trying an incremental load now to see how long that will take, and hoping somehow i get a more reasonable number of records.

Re: Creating QVDs fails but no error message

fabio_vallone — Wed, 27 Aug 2014 14:47:00 GMT

Maybe you can change, in server (Documents > Reload) the "Timeout seconds" to 86.400 (=24 hours)

Re: Creating QVDs fails but no error message

christian77 — Wed, 27 Aug 2014 14:52:14 GMT

Hi.

It is impossible to see California and Texas in detail and at the same time.

Those 350 million records have to be seen at the same time? Impossible.

Bring detailed information of the present and try to group the past.

DataBases like AVAYA, call center does that. There is no way I can save the detail for those million calls that I receive every day.

Use Publisher to divide information by section access. Once is published single files are smaller.

There is also DIRECT SELECT from QV 11.2. Data comes on demand of the user. A constant connection is needed. I don't know about performance but I don't think it is very fast.

Simplify your life.

About the 3x more rows, can you send an example of your script? I'm sure somebody in this community will find the error.

Re: Creating QVDs fails but no error message

Wed, 27 Aug 2014 15:03:04 GMT

The script that is being run is included in the log file I attached above.

DIRECT SELECT isn't really something we need - we're not looking for live data feeds, in fact we only reload this document weekly.

I would love to be able to summarize the older data on import but i don't know how to do that and still meet the requirement for the report. We need to measure the effect of coupon use on ordering history. So the user needs to see for any coupon code(s) selected, what was the average number of orders placed in the 3/6/12 month intervals prior to and after their first use of the coupon. I don't know how to achieve that flexibility without pulling in all the ordering data for the past 2 years. I'm open to suggestions on that.