22 Replies Latest reply: Sep 29, 2016 7:26 AM by Friedrich Hofmann RSS

    qvd file blocked - can I somehow prevent that?

    Friedrich Hofmann

      Hi,

       

      the new report I have built is supposed to be run locally  by the users whereby of course they use the qvd file.

      It seems to me that the qvd file is being blocked as long as an app supposed to read it is open - even if it's not being read, can that be?

      Anyway, our primary load_script runs once an hour and at that precise time, if the qvd file is blocked and cannot be written to, of course there is an error and the whole thing fails.

      Is there any way I can prevent that?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: qvd file blocked - can I somehow prevent that?
          Marcus Sommer

          Hi DataNibbler,

           

          take a look on this posting: General Script Error.

           

          - Marcus

            • Re: qvd file blocked - can I somehow prevent that?
              Friedrich Hofmann

              Hi Marcus,

               

              sorry, but I couldn't really see a solution in this thread because the thing that I suspect was there ruled out quite early on.

              - In my instance, the "General script error" occurs after the script is actually finished, the last statement being a STORE, so I assume that the file is blocked for writing, and the only reason I can think of for this is that it is being read at that moment or an app supposed to read it is open - which I cannot really prevent since that is the real purpose of this report: To be opened and run locally by the users, which means reading that qvd file.

              => Could I somehow force that writing_process to take place, killing any read_process at that moment - the writing (which takes place every hour) is the more important one because there are some more things coming in the wake of that. There's no great damage done if there is a failure for one or both of the users of this report.

                • Re: qvd file blocked - can I somehow prevent that?
                  Friedrich Hofmann

                  Yep - it worked fine right now ;-)  - which, as far as I can see, proves that the issue is really that the file is blocked at that precise instant. (which is usually at about 12min past the hour, but that could change, and telling the users to watch the clock while working with the report is not a good plan ...)

                    • Re: qvd file blocked - can I somehow prevent that?
                      Friedrich Hofmann

                      I have an idea now - I could use the >> !!!inuse << file that is generated whenever a log file is currently being written to - if that is found, that means that the data_loading script is running and the other report should not be used for a few minutes - I can insert some code in the report itself to check that and trigger a message to the user ...

                        • Re: qvd file blocked - can I somehow prevent that?
                          Friedrich Hofmann

                          Okay, that is not quite as easy as it looked - I would have to construct a relatively complex filepath to reach it and then, that file is quite strange - it does not have a size and I cannot seem to load it in QlikView - can I use any of the file_attributes like filepath() or filebasename() to determine this?

                          Still, this solution would only be second-best - the best thing would be to simply force any reading_process on that file to stop as soon as the load_script comes around and wants to write to it.

                          Do you know any solution to this?

                            • Re: qvd file blocked - can I somehow prevent that?
                              Friedrich Hofmann

                              Another idea: I forget that file and I go for the regular log instead - that has a filesize all right, that is - as far as I can see - reset to 0 when the corresp. app starts and only after it has finished, the filesize goes up to whatever ... Let's see.

                              • Re: qvd file blocked - can I somehow prevent that?
                                Marcus Sommer

                                I don't think that there is any practically way to prevent an access to the qvd - then the potential causer will be the OS or any scanner which will run with very high access-rights and priority (I mean thinking about changing the access rights or something similar).

                                 

                                More helpful could be to initiate a new read-process on this qvd (within a sub-routine like in link above mentioned) like:

                                 

                                temp:

                                noconcatenate

                                first 1 load * from qvd.qvd (qvd);

                                drop temp;

                                sleep 1500;

                                 

                                which might kick-off a new file-handling and resolve the blocking file-flag.

                                 

                                Another (and more expensive) workaround could be to store the qvd within a copy and doing some time later a renaming and/or moving/deleting from the origin-file and the new copy (it's rather the worse-case).

                                 

                                - Marcus

                                  • Re: qvd file blocked - can I somehow prevent that?
                                    Friedrich Hofmann

                                    Hi Marcus,

                                     

                                    sorry, I don't quite get you.

                                    However, I now have another idea - LOADing the filesize of the log_file works, but no real figure is loaded, so I can use the ISNULL() function to query whether the log is currently in use and not yet finished.

                                    If I find that that is the case, I can trigger a MsgBox to the user and exit. That is still not an optimal solution because that way the user will have to wait about 20min though the time_window where that block can occur is only about 2min, but it is doable.

                                     

                                    Thanks a lot for helping!

                                    Best regards,

                                     

                                    DataNibbler

                                    • Re: qvd file blocked - can I somehow prevent that?
                                      Piet Hein van der Stigchel

                                      Maybe an other idea is to store the qvd with a timestamp and alter the loadscript to use the qvd to load the most current. The amount of files/space stored would increase, but maybe that can be solved by some archiving process. Additional advantage is that there is a backup of the old situation one can revert to and that the user is always able to load the qvd.

                                       

                                      Load script for finding the most recent file would be along the lines of:

                                      DIRECTORY;

                                      DatesOfFiles:

                                      LOAD MaxString(Mid(FileBaseName(),10, 8)) AS PossibleDates

                                      FROM

                                      .\Data\Bezoeken*.txt

                                      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

                                       

                                      LET vLastDate = Peek('PossibleDates', -1);

                                      LET file='.\Data\Bezoeken.'&vLastDate&'.txt';

                                      TRACE $(file);

                                        • Re: qvd file blocked - can I somehow prevent that?
                                          Friedrich Hofmann

                                          Thanks Piet!

                                           

                                          I've had other situations before where, out of a rather long list of files, I had to find the newest one, using some part of the filename, so I know roughly how to do that.

                                          For now, I'll try my idea. But if that doesn't do, I will try yours - I think I could just alter that load_script to write a file with  no suffix and, after the load_script has finished (as the very last command), I  could use an ECECUTE cmd command to add the suffix _old to it (and delete the existing one, or just overwrite it) and that report could just always read from the qvd file with the suffix _old. That way the report would never end up reading from the same file that my loading_script needs to write to.

                                          Best regards,

                                           

                                          DataNibbler

                                            • Re: qvd file blocked - can I somehow prevent that?
                                              Friedrich Hofmann

                                              Oops - indeed it doesn't seem to work - very strange, though: I am loading the filesize of the log_file, which is 0 while the script is running, and the first time I tried this a NULL value was loaded - but now (the script is currently running and the filesize such as I see it is 0), the value is loaded as the size it was last time when the script was finished ...

                                              I cannot quite make head or tail of this.

                                              But I could now try - no, Piet's approach won't work either - the loading_script on the server would have to run a command_line to rename the file (with the suffix_old) and thus overwrite the existing _old file - but programs running on the server are not allowed to use the command_line ;-)

                                              I would have to implement a code into the report (which is run locally) to do essentially that - but then there could be conflicts again if the load_script tries to write to the new file and at the same time, the report tries to rename it ... only, in that instance, the order_of_priority would be reversed and the renaming_process would fail because the file is blocked by the writing_process.

                                              Oh my, this is complicated ...

                                                • Re: qvd file blocked - can I somehow prevent that?
                                                  Friedrich Hofmann

                                                  Ah - I've got another idea ;-)

                                                  I can query the time the qvd_file was last generated - and since I know that the load_script runs once an hour, it is pretty sure that between the creation of one qvd and the next, there will be one hour (plusminus), right?

                                                  So I can query whether the current timestamp is 55min or so bigger than the creation_timestamp of the existing qvd and if it is, I can say that the relevant part of the loading_script is probably currently running or just about to run and the user should wait a few minutes or maybe even close QlikView for a few minutes ...

                                                    • Re: qvd file blocked - can I somehow prevent that?
                                                      Friedrich Hofmann

                                                      Hi,

                                                       

                                                      finally it works.

                                                      What could still happen is that the report is open and the user is currently not at his desk when this warning appears, so they cannot close it - is there any possibility to close QlikView altogether from the script?

                                                       

                                                      Best regards,

                                                       

                                                      DataNibbler

                                                        • Re: qvd file blocked - can I somehow prevent that?
                                                          Friedrich Hofmann

                                                          Oops - I was too early in being happy ;-) It doesn't work yet - I can't quite understand why, the data_types are matching.

                                                          The code I use for finding out whether the relevant part of the data_loading script is currently running is this:

                                                          FIRST 1 LOAD
                                                            TIME(filetime('$(v_Pfad_TransDtl)')) as Letzte_Generierung, // (that is a filepath_variable pointing to the qvd file)
                                                            MINUTE(TIME(NOW()) - TIME(filetime('$(v_Pfad_TransDtl)'))) as Zeit_seither_min,
                                                            HOUR(TIME(NOW()) - TIME(filetime('$(v_Pfad_TransDtl)'))) as Zeit_seither_hr
                                                          RESIDENT Werkscheck
                                                          ;

                                                          // The data_loading script runs every hour, so the time between one updating of the qvd and the next is always equal.
                                                          IF num(Zeit_seither_min) >= 55 THEN
                                                             LOAD
                                                                 MsgBox('Die Archivierungslogik in der LOAD_AOS muss in Kürze auf die Datendatei zugreifen. Bitte QlikView ganz schließen, in ca. 10min kann es weitergehen.', 'Fehlschlag', 0, 64, 0) as Meldung
                                                               autogenerate 1;
                                                             EXIT SCRIPT;
                                                          ENDIF

                                                          EXIT Script;

                                                           

                                                          (I first had a bigger construct to differentiate between the two plants, but for now I only want to get that query going for the one plant where that load_script runs every 60min.

                                                          The datatype of that field seems to be numeric all right, but still - the code runs and a similar query on the GUI does work, but this doesn't, the MsgBox does not appear even when that field is 57 or 58.

                                                           

                                                          Can anyone spot the error in that code?

                                                          Thanks a lot!

                                                          Best regards,

                                                           

                                                          DataNibbler

                                                           

                                                          P.S.: OK, whatever, I now made that into a variable using PEEK() and that works.

                                                            • Re: qvd file blocked - can I somehow prevent that?
                                                              Marcus Sommer

                                                              Hi DataNibbler,

                                                               

                                                              you couldn't access the field "Zeit_seither_min" directly - you will need a peek for this:

                                                               

                                                              peek('Zeit_seither_min', 0, 'Tablename')

                                                               

                                                              I assume it's the story about "... der Wald und die Bäume ..."

                                                               

                                                              - Marcus

                                                                • Re: qvd file blocked - can I somehow prevent that?
                                                                  Friedrich Hofmann

                                                                  Hi Marcus,

                                                                   

                                                                  yep, you're right - as I just wrote, it works with PEEK() - just found it out, you were faster ;-) I was looking at a lot of trees - trying that >>_Inuse!!<< file first and the filesize of the log then - that was quite a long jog ...

                                                                  Now the only thing I wonder is - would it be possible from the script to close QlikView altogether - ah, no, to do that, the script would need to run. If a user is not at his desk and the report is open, the script cannot run, so that wouldn't do - but I guess that should not affect the qvd and the updating by the LOAD_AOS should succeed.

                                                                  This whole thing is done then, the thread can be closed.

                                                                   

                                                                  Best regards,

                                                                   

                                                                  DataNibbler

                                                                    • Re: qvd file blocked - can I somehow prevent that?
                                                                      Marcus Sommer

                                                                      Normally there should be no blocking of qvd's and if this happens (and it's ensured that there is no parallel running qv-task) it will be caused from some conflicting of the OS (filesystem) and Qlik and further processes like windows shadow copies (take a look on the windows-logs) or any scanners - maybe you could convince your IT to exclude qvd's from any scanning (it would reduce the number of possible causes and rather improve the performance).

                                                                       

                                                                      - Marcus

                                                                        • Re: qvd file blocked - can I somehow prevent that?
                                                                          Friedrich Hofmann

                                                                          Hi Marcus,

                                                                           

                                                                          unfortunately this issue just resurfaced - there can be conflicts on other qvd files that are used by the report, too, and it just happened, again causing one rather critical data_loading script to fail.

                                                                           

                                                                          Convincing IT of anything will be rather difficult since QlikView is not even officially supported by IT - and anyway, IT does have its own ways in many ways here ...

                                                                           

                                                                          But I have another idea to exclude any possibility of read-vs-write conflicts:

                                                                          I cannot run a batch file on the server - but locally that should be possible, so the report should be able to use the EXECUTE command to create a copy of the qvd file and load that ... I just have to see how long it takes to create a copy of the qvd file - if it takes too long, the report has to first check if a copy exists ...

                                                                           

                                                                          P.S.: Hmm ... but still the report should always use the most current data file available, so I have to check the filetime() of that qvd_copy against the filetime() of the "original" qvd, and if the latter one is newer, it has to be copied again - or I can just always have it copied, that way I don't have to check anything - but then there might again be conflicts in case the app wants to copy the file just in the same instant that the loading_script wants to write to it ...

                                                                          I guess that in the last consequence that risk cannot really be excluded when QlikView apps are used both locally and on the server. We'll just have to live with it.

                                                                            • Re: qvd file blocked - can I somehow prevent that?
                                                                              Marcus Sommer

                                                                              Hi DataNibbler,

                                                                               

                                                                              without a real access to the server you will be always cure the effects and not the cause ... (I know that you know it) ... and therefore it will be difficult to find a stable solution. I think at least you will need access to the log-files in any way.

                                                                               

                                                                              - Marcus

                                                                                • Re: qvd file blocked - can I somehow prevent that?
                                                                                  Friedrich Hofmann

                                                                                  Hi Marcus,

                                                                                   

                                                                                  well, I do have access to the directory on tthe server where the log_files are stored. That was one concession by the IT_guys.

                                                                                  Do you think that it would be possible to build a solution from there? I tried once with that INUSE_file - but I cannot load the properties of that file (filetime or filesize), that's why I went for the filetime() of the qvd last time - but I cannot really go that way because that data_loading_script runs for quite a while and I cannot well ask the users to keep QlikView closed all the time, else the report will become a lot less practical for them ...

                                                                                   

                                                                                  Best regards,

                                                                                   

                                                                                  DataNibbler

                                                                                    • Re: qvd file blocked - can I somehow prevent that?
                                                                                      Marcus Sommer

                                                                                      Hi DataNibbler,

                                                                                       

                                                                                      such file-blocking should be really an rather seldom exception. If it happens quite regulary then there are any conflicting processes and if they aren't obvious (like parallel running tasks) then you will need the logs from the qv-server, the OS and other processes like an anti-virus/firewall to find the conflicting process directly or at least a suspicious pattern.

                                                                                       

                                                                                      - Marcus

                                                                                        • Re: qvd file blocked - can I somehow prevent that?
                                                                                          Friedrich Hofmann

                                                                                          Okay,

                                                                                           

                                                                                          I don't have access to the logs from the server, so I could only check parallel-running QlikView apps - and we have taken good care to make sure they do not conflict (all the apps running by a schedule on the server) - so I guess that is the point where I decide that it's not worthwhile to analyze this further - since, as you suggest, it is rather seldom - well, it happens about once or twice a day - but the loading_script runs every hour and the odds are that the next run will be fine again. The damage done is not great.

                                                                                          Thanks for your help! I guess this it is with this issue. There are things closer to being worthwhile ...

                                                                                           

                                                                                          Best regards,

                                                                                           

                                                                                          DataNibbler