SQL Plan

Background information

OceanBase Developer Center (ODC) allows you to periodically execute user-created SQL scripts to meet the needs of periodic database development and maintenance tasks, such as periodic execution of stored procedures, backups, and synchronization of database and table structures.

This topic describes how to apply for and manage SQL plan jobs in OceanBase Developer Center (ODC).

Create an SQL plan job

Example: Query the employee table every 2nd and 29th of the month.

1. Click Jobs > SQL Plan > Create New in the SQL window.

2. Specify the following information.

   Parameter	Description
   Database	Select a database.
   SQL Content	You can specify SQL content in one of the following ways: SQL Entry (default): You can directly enter an SQL script in the editing area. By default, you can enter up to 10 MB of characters. If the input exceeds the limit, you can upload an attachment. Click IN Value Conversion to convert batch-copied data into the IN ('A','B') format. The column value separator is a line break. The row value separator is a space or tab. File Upload: After you select this option, you can click the file pool to enter the file resource manager and select the file to import, or you can directly drag the file to the file pool to upload the file. You can upload up to 500 files, and the total file size cannot exceed 256 MB. By default, the uploaded files are sorted in the order in which they are uploaded. You can drag the files to change the order. The tasks are executed in the order of the files. Currently, only .sql files can be uploaded.
   Delimiter	You can specify a delimiter.
   Query Result Limit	You can specify the query result limit (1-1,000,000).
   Execution Timeout	Enter a number in the Execution Timeout text box to specify the statement execution timeout, in hours (the default timeout is 48 hours, and the maximum is 480 hours).
   Execution Mode	Select Periodic Execution, Immediate Execution, or Scheduled Execution. For periodic execution, you can select the following options: You can directly select the hourly, daily, or weekly execution mode. You can set the periodic execution mode in one of the following ways: Default mode: You can select the daily, weekly, or monthly execution mode and set the required execution period. Custom mode: You can select the second, minute, hour, day, month, or week execution mode and set the required execution period.
   Job Settings	Select Task Error Handling Method: Abort Task: This is the default mode. If an error occurs during script execution, the task is stopped. Ignore Error and Continue: If you select this option, the task will skip the error statement and continue execution after an error occurs during script execution. Select Task Execution Timeout Handling Method: Initiate Another Task When Current Task Is Completed Ignore Current Task Status and Periodically Initiate New Tasks

3. Click Create to apply for the job.

4. After the job is generated, you can view the job information in the Jobs > SQL Plan list.

View an SQL plan job

1. Go to Jobs > SQL Plan, click the name of the target job in the list to view its details. You can also click ··· in the Actions column to perform more operations on the job.

   • Terminate: If the job execution mode is set to periodic execution or scheduled execution, you can terminate the job. You can also select the target jobs and click Batch Terminate at the top to terminate multiple jobs.
   • Disable/Enable: If the job execution mode is set to periodic execution or scheduled execution, you can disable the job to temporarily stop its execution. The job will resume execution after it is enabled.
   • Edit: If the job status is disabled, you can edit the job to update its content.
   • Delete: If the job status is completed or terminated, you can delete the job.
   • View: View the job details.
   • Clone: Clone the job to quickly generate a new job with the same configuration.
   • Share: Copy the job access address.

2. In the job details panel that appears, the Basic Information tab is displayed by default. You can view information such as the job type, database information, variable configuration, partitioning strategy, and execution mode.

3. In the job details panel, click the Execution Records tab to view the execution details of the job.

4. In the job details panel, click the Operation Records tab to view the approval status and change records of the job.

Batch import and export

Batch export

1. Go to Jobs > SQL Plan.

2. Select the target tasks and click Batch Actions > Batch Export.

3. Confirm the export content and click Export to complete the batch export.

4. After the export is completed, the export result is displayed on the right side of the page. You can view the key and view the exported file locally.

   Notice

   Keep the key properly. It is required when you import the exported file.

Batch import

1. Go to Jobs > SQL Plan.

2. Click the ∨ icon next to Create New and select Import SQL Plan.

3. Select the file to import, enter the file key, select the target project, and click Next: Import.

4. Select the tasks to import and the target database, and click Import after you select I acknowledge the relevant business risks..

5. After the tasks are imported, you can view them in the list.