- 4 November 2016, added stockindexonline exports (Infradox XS 27.5)
In version 27.3 or later, you can schedule server jobs. Currently you can export data as csv (comma separated values) from several tables. If you need a specific query then create a support ticket. Other job types will be added in future versions.
Furthermore you can create CSV import jobs to update metadata of files already online, and you can create jobs to delete files by uploading a CSV file with file numbers.
Adding a new data export job
Go to Site Configuration in backoffice, and click on the Job server icon. An overview of current jobs will appear (if any). To add a new job, click on the New button in the toolbar. A job server properties dialog will open.
- Check the Enable processing checkbox. To (temporarily) prevent a job from being executed, untick this box.
- Select Job type SQL Export.
- Select a value for Ouput.
Currently you can only select CSV. CSV files contain comma separated values and a header row. You can open these files in Excel or with a plain text editor.
- Compress output
Check this box if you want to have the resulting file compressed in a ZIP file. The file will be smaller and quicker to download.
- Enter a name.
Use a name that shows what the export is about. This name is also used to generate names for output files created by Job server.
In the SQL box you can enter a predefined query which must start with XSJOB. Other SQL queries are possbile but can only be entered by the system administrator. You can click the SQL button for an overview of predefined queries. Click on a name to copy the SQL into the box. Then change the parameters. E.g. XSJOB_USERS [days],[includeorganisations]. The parameters are between brackets. You must replace the parameters (including the brackets). For example XSJOB_USERS 7,1 to export new users that were added in the last 7 days and include organisation info.
Stockindexonline: In version 27.5 a SQL stored procedure has been added to export data for Stockindexonline. Click on the SQL button and select the SQL. Replace the parameters with the number of days (e.g. 365) and between apostrophes the name of your website. E.g. www.mywebsite.com.
- Run on content DB
This box should be left unchecked unless your content database is not the same as your website database.
This box will be checked if a sysadmin created a query for you. You can not change the query yourself if this is true.
- Columns to export
After you have entered the query to use, click on the link "Fetch the column names". This will put all the column names in the "Columns to export" box. You can then remove the column names (fields) that you don't need. This is also a good starting point for CSV Metadata update jobs, which is described further down.
Select Run once if you want the job to be executed only one time
Select Run on selected days if you want the job to be executed more than once. E.g. daily, or once a week on Friday etc.
Select the day or days on which you want the job to execute. If you select Run once, then you can select only one day.
- Run after
Select an hour after which you want the job to execute. E.g. you may want jobs to executed when your website is not busy because certain jobs may have an impact on database performance (e.g. large metadata exports).
Although Job server is a multi threaded application, jobs are executed one after another to minimize performance impact on the database. If several jobs are scheduled, you can use the priority parameter to changed the order in which jobs are executed.
If you want a notification by e-mail when the job is done, then enter an e-mail address. You will also receive a notfication if a job fails. The e-mail will include a link to the generated file (in case of an export). If you don't want notifications, then leave this field empty.
When you are done, click Save. The dialog will close and the job overview will reload.
Checking the status of a job and downloading files
The job overview will show the status of a job. I.c. when did it start, when did it finish, how long did it take and what was the status (running, finished or finished with errors). You can click the Refresh button in the toolbar to reload the job list, it will not refresh automatically.
If a job has finished with success then the overview will display the name of the file that was last generated. Click it to download the file. If a job has executed more than once, then you can click on the Folder link to show a list of all the files. In this list you can click on a name to download that file.
Note that you also configure jobs to send you an e-mail with a link to the downloadble file.
Restarting a job
If you want to run a job again or if a job has failed, then click edit to open the job properties. If necessary, check the error message and correct the problem.
Check the box Reset last execution and save the job. In the overview you will see that the last start date now shows Waiting for next run.
Deleting jobs and files, and deactivating jobs
You can keep jobs in the overview as long as you want but note that exported files are automatically removed after 7 days - so make sure to download your files in time (you can of course always run a job again).
If a job is scheduled to run only once then you can consider deleting it when it's done, or you can disable it by editing its properties.
CSV Metadata update jobs
You can update the metadata of files already online by uploading a CSV file with only columns for fields that you want to change. For example only the keywords. It is recommended to use a CSV file previously exported with the Data export job function, because such a file will have all the correct/valid column names. The CSV file that you upload must have a header row with valid column names and at least the column ID (the column header must have ID). The ID column must have the Infradox file numbers which are used to find the rows to update in the database.
Further instructions will show in the job server properties dialog after you have changed the Job type dropdown box to CSV Metadata update.
After an update or delete job has been successfully completed, the input file will be removed from the server.
CSV Delete files jobs
To delete many files from the database, you can upload a CSV file with Infradox numbers. The CSV file must have a header row with the column ID. Further instructions can be found in the job server properties dialog after you have changed the Job type dropdown box to CSV Delete files.