8 Replies Latest reply on Jul 20, 2017 6:36 PM by aknudsen

    PSOS on PSOS

    aknudsen

      Here's a question I've not ben able to find an sewer to .... yet. I have several SQL wan connected servers I need to pull data from. I have a parent/child where the parent have the connection settings and the child the actual imported data. I need to loop through the parent table and spawn a subscript import data to the child table. I need to import several SQL servers at the same time. This server side schedule will execute once a day and I need to trap if there are already imports ongoing from the last time it ran.

       

      So the questions are:

      Will a subscript spawn a separate session or keep the same as the parent script?

      Can I use PSOS to call a subscript from the server?

      Is a recursive script my only option?

       

      Any help is greatly appreciated.

        • 1. Re: PSOS on PSOS
          wimdecorte

          aknudsen wrote:

           

           

          So the questions are:

          Will a subscript spawn a separate session or keep the same as the parent script?

           

           

          Same session.

           

          aknudsen wrote:

           

          Can I use PSOS to call a subscript from the server?

           

           

          If you are inside a PSoS session you can not use the "perform script on server" script step.  But you don't need it, you are already executing things on the server.

           

          aknudsen wrote:

           

           

          Is a recursive script my only option?

           

           

           

          Not sure where recursiveness comes into play?

          1 of 1 people found this helpful
          • 2. Re: PSOS on PSOS
            aknudsen

            I need to loop through a file and spawn a subscript for SQL import. I have several SQL servers I need to import data from. These imports needs to run in parallel since there are not enough time to serialize them in a 24 hour period.

            • 3. Re: PSOS on PSOS
              wimdecorte

              You may want to use a server-side schedule or a set of them instead of PSoS, if this needs to run once a day.  You can have multiple server-side schedules running at the same time, they could each be talking to one SQL Server.

               

              Recursiveness would be if you call the the script from inside that same script; there is no need for that here it seems.  Recursive scripts are run in sequence, not in parallel.

              • 4. Re: PSOS on PSOS
                aknudsen

                Multiple schedules, as far I've come to understand, is my only option. That is all good for a static table, but as I add additional SQL servers I need to keep adding schedules.  One would think that you could spawn a subscript as a new process since the server is multithreaded.

                 

                And yes you're right about recursive option.

                • 5. Re: PSOS on PSOS
                  wimdecorte

                  You wouldn't need to add more schedules, but I don't know enough about your setup and how long the things take.

                   

                  I would set up say 10 or 20 schedules, each schedule would read from a queue of to-dos and pick the next task, mark the one that they working on so that the next schedule would know to pick the next one.  You can set up enough schedules that you wouldn't need to add more unless you really start running out of time.

                  • 6. Re: PSOS on PSOS
                    aknudsen

                    We're pretty much on the same page. I have a lot of checks and balances for the imports. Checks and balances for when (not if) the import fails and a whole set of tuning parameters for the connections.

                     

                    On a good day I can push about 1 mill an hour. On a normal day about 200k an hour. This is a highly segregated and secure network. Wish it was server to server on a 1GB link.

                     

                    Currently storing about 40mill records and growing to about 3 times by the end of the year. Analysis, processing and reporting on the data is a whole different issue

                     

                    Guess it's time to start designing a scheduler.

                     

                    Hey Filemaker how about a new function: SpawnScriptOnServer

                    • 7. Re: PSOS on PSOS
                      greglane

                      You could use Perform Script on Server with the "wait for completion" option set to "off". This will effectively trigger the execution of the script on the server and the client script will immediately continue. You'd need to use another mechanism to log errors or monitor the status of the server-side script.

                      • 8. Re: PSOS on PSOS
                        aknudsen

                        It will be a set of server side schedules as wimdecorte suggested and some additional checks I have to do t see if the current SQL server is being imported or is idle. In addition I also have several other components I need to automate so I will come up with a scheduler I can manage from the client with a server side schedule to handle the queue.