Show TOC

Administration of Partitions in Sybase IQLocate this document in the navigation structure

Use

Moving the data creates a partitioned table for every InfoProvider in the Sybase IQ database. The system creates a new partition for every new archiving request. It does this either by splitting the MAX partition, which should always be empty, or by splitting the partition that belongs to the previous archiving request.

Sybase IQ, Version 15.4 can manage up to 1024 partitions per table. If you move data to Sybase IQ very frequently, you might reach the upper limit for partitions. In this case, you can manage the partitions.

You can monitor and reduce the number of partitions using report RSDA_SYB_PARTITION_MONITOR. The report attempts to reduce the partitions by deleting or merging them. To start with, all partitions are deleted that are flagged for deletion or as invalid. The system deletes the partitions in order of age, starting with the oldest. If this deletion step does not bring the number of partitions back below the limit, the system merges the partitions.

Prerequisites

To run the report, you need authorization STOR for field S_ADMI_FCD in authorization object S_ADMI_FCD and authorization 03 (Display) for field ACTVT in authorization object S_RZL_ADM. You can then only see which activities are being executed. You can also run the report in simulation mode. With authorization 01 (Add or Create) for field ACTVT in authorization object S_RZL_ADM, you can merge or delete partitions manually.

Procedure

Run Sybase IQ Partition Monitor Manually

  1. Run report RSDA_SYB_PARTITION_MONITOR. You can run it in the dialog or schedule it to run periodically in the background.

  2. Enter the name of your near-line connection to Sybase IQ.

  3. You also have the option of entering the name of the database table in Sybase IQ. If you do not enter anything here, the system will check all partitions.

  4. Specify a lower and upper threshold value. The default setting is 20% for the lower threshold and 80% for the upper threshold. The system then checks for tables with over 80% of the maximum number of partitions. If this threshold value is exceeded, the system reduces the partitions for this table until the lower threshold is reached.

  5. In execution mode, you can decide whether to just run a check or to actually have partitions deleted and/or merged.

  6. If you run the report in the dialog, the system first displays a list of all selected tables. All tables where the number of partitions exceeds the upper threshold value are marked in red in the Partitions column. By double-clicking on a row, you can call the detail view for the partitions in a table. Here you see one partition per row. In the Action column, you can see what options you have for reducing the number of partitions. If you have selected Drop and Merge Partitions as the execution mode, you have the following options:

    • No icon: This partition cannot be deleted or merged with another partition.

    • : This partition can be deleted.

    • : This partition can be merged with the following partition.

    In the Seq.No column, you can see the sequence in which the merge is performed.

    In execution mode Drop Partitions (no Merge), you only see partitions that can be deleted. In execution mode Merge Partitions (no Drop), you only see partitions that can be merged.

    If you have selected one or more rows and choose Drop & Merge, the corresponding action is executed for the selected rows. To start with, all rows are selected that need to be processed in order to reach the lower threshold value.

  7. If you run the report in the background, the partitions are processed automatically in accordance with the system proposal.

    The report starts by attempting to delete all partitions that only contain requests with request status Invalid (8). It processes the requests in order of age, starting with the oldest. If the lower threshold value can be reached in this way, the report stops deleting. If this lower threshold value cannot be reached in this way, the report attempts to merge neighboring partitions. Neighboring partitions are always merged if they only contain requests that have at least request status Active (7). The system starts by merging neighboring partitions that are smallest when put together.

    Partitions that contain requests with a lower status are not merged. The partitions are merged in order of size, starting with the smallest. Once the lower threshold is reached, the report stops merging.

Schedule Sybase IQ Partition Monitor by Process Chain

  1. Call the process chain maintenance transaction. Choose Process Chain Maintenance under Administration in the Data Warehousing Workbench. The Process Chain Maintenance Planning View screen appears.

  2. In the left-hand screen area of the required display component, navigate to the process chain where you want to integrate the Sybase IQ Partition Monitor and double-click to select it. The system displays the process chain plan view in the right-hand screen area.

    If you cannot find a suitable process chain, create a new one. More information: Creating Process Chains.

  3. To insert the process, choose Process Types in the left-hand screen area. The system displays all process categories that you can choose from.

  4. In process category Others, choose application process type Sybase IQ Partition Monitor.

  5. Use drag and drop to insert this application process type into the process chain. The dialog box for inserting a process variant appears.

  6. Connect the processes.

  7. Save and check your process chain as described in Creating Process Chains.

    The process step is completed successfully once the upper threshold value is no longer exceeded for any of the selected tables. If the upper threshold value is exceeded for one or more of the selected tables, the process step ends with an error. You now have the option of using the alerting function in the process chains, for example to send an e-mail to the system administrator.

    Here you can configure the same options for the variant as you can for manual execution. If you configure the process step to perform drop and/or merge operations (in addition to running checks), then the condition for triggering the error/alert applies to the status after executing the drop/merge operations.

    More information: Sending Alerts to Process Chains