5.5.5. Managing tablespaces

<< Prev   Next >>

5.5.5.1. Support for changing the location of predefined tablespaces

Available for DBMS:

• IBM Db2
• Oracle Database
• PostgreSQL

Predefined tablespaces:

• IBM Db2:
  • For indexes: V81C_INDEXSPACE.
  • For data: V81C_LARGESPACE.
  • for LOB: V81C_LOBSPACE.
  • Temporary user tablespace: V81C_USERTEMP.
  • Temporary system tablespace: V81C_SYSTEMPBP.
• Oracle Database:
  • For indexes: V81C_INDEX.
  • For data: v81c_data.
  • For LOB: V81C_LOB.
  • Temporary tablespace: V81C_TEMP.
• PostgreSQL:
  • For indexes: V81C_INDEX.
  • For data: v81c_data.
  • Temporary tablespace: custom name that is specified in the postgresql.conf configuration file.

5.5.5.2. Managing user tablespaces

5.5.5.2.1. General information

In addition to managing the location of predefined tablespaces, you can create user tablespaces and place database tables and indexes in them. You can use tablespaces to organize the storage space of various database objects, for example, tables and indexes. To manage tablespaces, you can use the following tools:

1. Tablespace usage mode configuration property.
2. Database tablespace management standard function.
3. Infobase testing and correction feature.

The feature is available for all supported database management systems:

• IBM Db2
• Microsoft SQL Server
• Oracle Database
• PostgreSQL

User tablespaces belong to a specific database. Tablespace settings are not transferred between databases when you export an infobase to a dt-file and import it back to another DBMS.

To create user tablespaces, grant the following rights to the user to be connected to the DBMS:

• IBM Db2: SYSCTRL or SYSADM rights.
• Microsoft SQL Server: ALTER right to the database.
• Oracle Database: CREATE TABLESPACE and DROP TABLESPACE privileges.
• PostgreSQL: assign the SUPERUSER role.

User tablespace management is not supported for Data Accelerator and file infobases.

See also:

• Configuration properties
• Standard functions
• Infobase testing and correction feature

5.5.5.2.2. Adding tablespaces

To add a new tablespace:

1. Enable the use of tablespaces (for Version 8.3.22 or earlier compatibility mode). You can do it in Designer.
2. Run the client application (thin client or web client).
3. Open the standard Database tablespace management function.
4. Create a new tablespace. Note that directories that you specify when creating a tablespace must exist on the computer running the DBMS server at the moment you click Save in the standard function form.
5. Place required tables and indexes in the created tablespaces.
6. Click Save. If the operation is successfully completed, perform the following action.
7. Update the placement of infobase tables via Designer in interactive or batch mode. For that:
   • In interactive mode, use the Verify and repair infobase Designer function and select the Update infobase table placement mode.
   • In batch mode, use the /IBCheckAndRepair command with the -RefreshTableLocation parameter.

     If the operation is successfully completed, the tablespaces are created and ready for use.

If you need to connect to already created tablespaces, do the following when you add a new tablespace:

• Find out the exact name of an existing tablespace and the directory where the files of this tablespace are located.
• Open the standard tablespace management function.
• Add a new user tablespace and specify:
  • As a name, the name of the existing tablespace.
  • As a path, the directory of the tablespace files.
• Perform the remaining operations to add a tablespace.

5.5.5.2.3. Deleting tablespaces

To delete a user tablespace:

1. Open the standard Database tablespace management function.
2. Exclude all tables and indexes from the tablespace you want to delete.
3. Update the placement of infobase tables via Designer in interactive or batch mode. For that:
   • In interactive mode, use the Verify and repair infobase Designer function and select the Update infobase table placement mode.
   • In batch mode, use the /IBCheckAndRepair command with the -RefreshTableLocation parameter.

     If the operation is successfully completed, the tablespaces are no longer used in the DBMS.

4. Open the standard Database tablespace management function again.
5. Delete unused tablespaces.

5.5.5.2.4. Using 1C:Enterprise language

In 1C:Enterprise, you can programmatically manage user tablespaces. To access this API, use the DatabaseTablespaces global context property. It will grant you access to the database tablespace manager. So, calling API methods will look as follows: DatabaseTablespaces.CalledMethodName(). For simplicity, the name of the global context property will be omitted in the following text.

At the moment 1C:Enterprise is started, the current configuration of infobase tablespaces is read. To get this configuration, iterate the collection of existing tablespaces using the For each iterator or the [] operator:

For Each Tablespace In DatabaseTablespaces Do
Message("Name: " + Tablespace.Name + ", path: " + Tablespace.TablespacePath);
EndDo;

The DatabaseTablespaces collection includes objects of the DatabaseTablespaceManager type. Let us take a closer look at the object. To identify a tablespace, use the Name property. You can find the path to the tablespace files in the TablespacePath property. If you know the tablespace name, you can get the respective tablespace manager using the Find() method of the tablespace manager.

Besides the name and the path, the object that describes a tablespace has two property sets:

• Current tablespace content. A list of tables and indexes that are currently included in the tablespace. This data is stored in the UsedDataContent and UsedIndexesContent properties respectively.
• Future tablespace content. A list of tables and indexes that will be transferred to the tablespace after updating the table placement. This data is stored in the ChangedDataContent and ChangedIndexesContent properties respectively.

Each of the specified properties has a value of the DatabaseTablespaceContent type. This type is a collection of objects of the DatabaseTablespaceContentItem type. The DatabaseTablespaceContentItem object contains only one property: Metadata. In this property, you can specify an object of metadata whose data tables or indexes will be placed in a certain tablespace. The DatabaseTablespaceContent object is a typical collection with the standard set of management methods.

It is also important to note two more pairs of tablespace manager methods:

• FindByUsedDataContent() and FindByUsedIndexesContent(). These methods allow you to determine what tablespace has data tables or indexes of a certain configuration object.
• FindByChangedDataContent() and FindByChangedIndexesContent(). With these methods, you can also find out what tablespace will contain data tables or indexes after updating the table placement.

Let us take a look at how you can add two tablespaces (for data and indexes) for the Goods catalog. The example does not demonstrate any verifications.

Item = New DatabaseTablespaceContentItem;
Item.Metadata = Metadata.Catalogs.Goods;

DataTablespace = DatabaseTablespaces.Add("ExtraData", "d:\data\data-files");
DataTablespace.ChangedDataContent.Add(Item);
DataTablespace.Write();
TablespaceIndex = DatabaseTablespaces.Add("ExtraIndex", "d:\data\index-files");
TablespaceIndex.ChangedIndexesContent.Add(Item);
TablespaceIndex.Write();

After you execute this code in 1C:Enterprise language, update the database table placement manually or in Designer batch mode.

<< Prev   Next >>