topic Re: Problem loading and storing QVD files at the same time in QlikView

Problem loading and storing QVD files at the same time

Anonymous — Wed, 29 Apr 2015 14:29:16 GMT

Hi all,

I have an QlikView application, let's call it "QVD Generator", that loads a lot of data and finally in the script stores my only fact table into a QVD file. I load it through the QlikView Publisher with a tight schedule that reloads every half hour.

Then i have another application, let's call it "Consumer Application", that loads data from the QVD file in the load script. I load this application through the QlikView Publisher once a day, every morning at 7 am.

To my problem: I have noticed that the load of the "QVD Generator" application fails once a day, at 7 am of course. Other times, no problem. I have generated several log files, and all of them shows that my script fails when trying to STORE the QVD file (that is being loaded from another application).

There must be more that have experienced the same kind of problem, are there any clever solution to this issue? I have searched though blog posts, discussions and topics, without finding this problem. If you know any reading to this topic, please refer to it here...

Thanks for your help.

Best regards,

Filip

Re: Problem loading and storing QVD files at the same time

Anonymous — Wed, 29 Apr 2015 14:33:31 GMT

Hi,

I had the same problem some time ago: my qvds "were using" by another application, while the QVD Generator was triyng to store new data in them , so it failed!

Re: Problem loading and storing QVD files at the same time

Gabriel — Wed, 29 Apr 2015 14:37:54 GMT

Hi,

What I would look to do in this case is have the second task which is "Consumer Application" to start upon successful completion of the "QVD Gnerator."

So go to publisher and under Triggers tab edit the task.

Start the task = On event from another task

Start on = Successful

Completion of = "QVD Gnerator."

This should resolve the issue

Re: Problem loading and storing QVD files at the same time

stevedark — Wed, 29 Apr 2015 14:38:22 GMT

I had a similar problem where a database load was happening frequently, but it failed each morning when some admin task was carried out on the database. I added code to skip over the load in the QVD Generator between 7am and 8am. It reads like this:

let vTimeNow = (now() - today());

if vForceLoad = 0 and (vTimeNow >= (1 / 24 * 7) and vTimeNow <= (1 / 24 * 8)) then

// skip reload due to database maintenance

let vLoaded = 0;

else

// do the load

let vLoaded = 1;

The vLoaded variable is used to show a message on the first sheet of the QVD Generator if the load has not taken place. There is a toggle button on the front page to set vForceReload, so if someone really wants to run the task at 7:15am (from the desktop) they can.

Regarding your problem, I would be pretty certain that the issue is that the QVD is trying to be written at the same time it is being read by the other process. Can you not chain these two loads in the scheduler so that they run one after the other, rather than at the same time?

Hope that helps,

Steve

Re: Problem loading and storing QVD files at the same time

Anonymous — Thu, 30 Apr 2015 10:46:52 GMT

Thank you for a helpful response.

I have a lot of data. My "QVD Generator" has a running time of about 15 minutes... My "Consumer Application", that actually is a several, has a loading time of about 30 minutes. In that sense it wouldn't help me to organize the reload jobs to have a successive schedule.

Is it possible to sync a QVD file to another, in some way?

Are there any other solutions to this? Feels like this should be a quite common problem...

Best,

Filip

Re: Problem loading and storing QVD files at the same time

stevedark — Thu, 30 Apr 2015 11:03:36 GMT

What you could do is write your QVDs to a staging filename, and then issue an execute statement in the load script to rename them at the last minute of the generate routine. This way the amount of time the file is locked due to writing will be minimized.

Having a 30 minute load time for a front end app sounds like you have a problem in the load script. If all QVD loads are optimised you can bring in a serious amount of data in a much shorter time.

To have an optimised load you need to ensure that all data manipulation is done in the QVD generate and your load into the front end app follows a number of simple rules.

This article gives more detail on this:

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/‌

Hope that helps.

Steve

Re: Problem loading and storing QVD files at the same time

Peter_Cammaert — Thu, 30 Apr 2015 11:05:37 GMT

Yes there are many solutions to this problem.

One could be to let your QVD Generator perform a double save of the required Facts QVD (simply STORE twice). The second save goes to a separate folder, to be written only during the 7 o'clock run (adjust your QVD Generator script accordingly), and to be used by the "Consumer Application" only (change the "Consumer Application" schedule to run only after 7:15).

This way you create some sort of data isolation depending on who will consume the data and how often and at what time. The original Facts QVD will still be available in updated form every other half hour for anyone who can read the data within 10 minutes or so (whatever is remaining of the 30 minute time slot). The copy of the QVD is available for 24hrs to the "Consumer Application".

Best,

Peter

Re: Problem loading and storing QVD files at the same time

Anonymous — Thu, 30 Apr 2015 11:19:03 GMT

Actually I went further with my last thought:
- Is it possible to sync a QVD file to another, in some way?

I created a .bat file witch copies the file to same location but another file name. I run this external task upon a successful reload of my "QVD Generator". Then my "Consumer application" will load data from the new file.

I will try this first..

Re: Problem loading and storing QVD files at the same time

Peter_Cammaert — Thu, 30 Apr 2015 11:33:29 GMT

Nice solution. However, your Customer Application may now try to run every half hour by itself, which creates the same problem in a different spot.

Best,

Peter

Re: Problem loading and storing QVD files at the same time

Anonymous — Thu, 30 Apr 2015 11:39:27 GMT

Actually not

I have two different load chains...

Chain 1

Job 1: Load of "QVD Generator" - every half hour
Job 2: Load of "BAT job" (external task) - every half hour if step 1 is successful

Chain 2
Job 3: Load of "Consumer application" - other schedule, once a day

That will make me copy the QVD file every half hour, but not start the load of "Consumer application".

Re: Problem loading and storing QVD files at the same time

Peter_Cammaert — Thu, 30 Apr 2015 11:45:27 GMT

Perfect. My suggestion does about the same, although without Job 2.

Good luck,

Peter

Re: Problem loading and storing QVD files at the same time

Anonymous — Wed, 06 May 2015 06:53:23 GMT

Thanks for your response Steve!

"What you could do is write your QVDs to a staging filename, and then issue an execute statement in the load script to rename them at the last minute of the generate routine. This way the amount of time the file is locked due to writing will be minimized."

How do I write this piece of code in the load script?

This sounds like the best solution, for both storing a file without failing the "QVD Generator" load, and to make sure that the data my "Consumer Applications" reads is updated.

Re: Problem loading and storing QVD files at the same time

Anonymous — Wed, 06 May 2015 06:56:31 GMT

I just noticed that my idea with the copy of the QVD file with an "external program" task in the scheduler gives me problem.

My "Consumer Applications" only loads a part from the QVD file that data is being copied to. I've noticed that the running time of the external program takes very long time... about one and a half minute, but when I run the BAT file from Windows, it copies within just a second. Can any of you guys explain that, or should I make a new thread on this topic?

This is from the task log in QV Publisher:

(2015-05-05 13:33:09) Information: Starting task 'Kopia av Resultatrader.qvd'. Id:a39c9418-b447-4a2c-b317-aff0166c695a. Triggered by 'TaskCompletedTrigger'. Id:79db2c50-871d-4c01-9574-5e3a72b2d4ec (2015-05-05 13:33:09) Information: Entering Task Execution. (2015-05-05 13:33:09) Information: ClusterID=1 (2015-05-05 13:33:09) Information: QDSID=485e0c73-f39d-970c-7470-34d4abd27f1e (2015-05-05 13:33:09) Information: TaskID=a39c9418-b447-4a2c-b317-aff0166c695a (2015-05-05 13:33:09) Information: MaxRunTime=1.00:00:00 (2015-05-05 13:33:09) Information: MachineName=SATSQLKDKHOS003 (2015-05-05 13:33:09) Information: Max attempts:1 (2015-05-05 13:33:09) Information: Current Attempt=0 (2015-05-05 13:33:09) Information: Task Dependencies are OK (2015-05-05 13:33:09) Information: Executing "D:\QlikView Storage\Private Data\Cmd Files\Kopia av Resultatrader.qvd.bat (2015-05-05 13:33:09) Information: Executing commandline: "D:\QlikView Storage\Private Data\Cmd Files\Kopia av Resultatrader.qvd.bat" in folder "D:\QlikView Storage\Private Data\Cmd Files". (2015-05-05 13:34:36) Information: The process exited with ExitCode zero (OK) at 2015-05-05 13:34:36 (2015-05-05 13:34:36) Information: Task Execute Duration=00:01:26.5025545 (2015-05-05 13:34:36) Information: The task "Kopia av Resultatrader.qvd" finished successfully (2015-05-05 13:34:36) Information: TaskResult.status=Finished (2015-05-05 13:34:36) Information: Notifying all triggers of new state:Finished (2015-05-05 13:34:36) Information: Notifying all triggers of new state:Finished - completed (2015-05-05 13:34:36) Information: Saving Task Result