DB2 - Exemplos simples - CREATE INDEX



Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

DB2 - Exemplos simples - CREATE INDEX

Creates an index on a table (allows duplicate values)
Cria um índice em uma tabela (permite valores duplicados)

Indexes are used to retrieve data from the database more quickly than otherwise.
The users cannot see the indexes, they are just used to speed up searches/queries.
Os índices são usados para recuperar dados do banco de dados mais rapidamente do que antes.
Os usuários não podem ver os índices, eles são usados apenas para agilizar as pesquisas/consultas.

Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.
Atualizar uma tabela com índices leva mais tempo do que atualizar uma tabela sem (porque os índices também precisam de uma atualização).
Portanto, crie apenas índices em colunas que serão pesquisadas com frequência.


    CREATE [UNIQUE] INDEX index-name
            ON table-name (column-name [ASC | DESC])
            [CLUSTER | NOT CLUSTER]
            [PARTITIONED]
            [PADDED | NOT PADDED]
            [using-specification]>
            [free-specification]>
            [DEFINE YES | NO]
            [COMPRESS YES | NO]
            [PARTITION BY RANGE partition-element
                          using-specification
                          free-specification]
            [BUFFERPOOL bp-name]
            [CLOSE YES | NO]
            [DEFER YES | NO]
            [PIECESIZE integer]
            [COPY YES | NO]

    using-specification
            USING
                  VCAT catalog-name |
                  STOGROUP stogroup-name
                      PRIQTY integer
                      SECQTY integer
                      ERASE YES | NO

    free-specification
            FREEPAGE integer
            PCTFREE integer

    partition-element
            PARTITION integer
            ENDING AT (constant/MAXVALUE/MINVALUE)

UNIQUE:

This prevents the table from containing two or more rows with the same value of the index key. Isso evita que a tabela contenha duas ou mais linhas com o mesmo valor da chave do índice.

ASC:

  • Specifies that the index entries are to be kept in ascending order of the column values.
    Especifica que as entradas do índice devem ser mantidas em ordem crescente dos valores da coluna.

  • This is the default setting.
    Esta é a configuração padrão.

DESC:

Specifies that index entries are to be kept in descending order of the column values.
Especifica que as entradas de índice devem ser mantidas em ordem decrescente dos valores da coluna.

CLUSTER or NOT CLUSTER:

Specifies whether the index is the clustering index for the table or not.
Especifica se o índice é o índice de clustering da tabela ou não.

PARTITIONED:

Specifies that the index is data partitioned Especifica que o índice é particionado por dados

PADDED or NOT PADDED:

Specifies how varying-length string columns are to be stored in the index.
Especifica como colunas de string de comprimento variável devem ser armazenadas no índice.

  • PADDED: Specifies that varying-length string columns within the index are always padded with the default pad character to their maximum length.
    PADDED: Especifica que colunas de string de comprimento variável dentro do índice são sempre preenchidas com o caractere de preenchimento padrão até seu comprimento máximo.

  • NOT PADDED: Specifies that varying-length string columns are not to be padded to their maximum length in the index.
    NOT PADDED: Especifica que colunas de string de comprimento variável não devem ser preenchidas até seu comprimento máximo no índice.

Using clause:

  • For non-partitioned indexes, the USING clause indicates whether the data sets for the index are to be managed by the user or managed by DB2.
    Para índices não particionados, a cláusula USING indica se os conjuntos de dados para o índice devem ser gerenciados pelo usuário ou gerenciados pelo DB2.

  • VCAT: Specifies that the first data set for the index is managed by the user, and that following data sets, if needed, are also managed by the user.
    VCAT: especifica que o primeiro conjunto de dados para o índice é gerenciado pelo usuário e que os conjuntos de dados seguintes, se necessário, também são gerenciados pelo usuário.

  • STOGROUP: Specifies that DB2 will define and manage the index data sets.
    PRIQTY: Specifies the minimum primary space allocation for the DB2 managed data set.
    STOGROUP: Especifica que o DB2 definirá e gerenciará os conjuntos de dados de índice.
    PRIQTY: Especifica a alocação mínima de espaço primário para o conjunto de dados gerenciado do DB2.

  • SECQTY: Specifies the minimum secondary space allocation for the DB2 managed data set.
    SECQTY: Especifica a alocação de espaço secundário mínimo para o conjunto de dados gerenciado do DB2.

  • ERASE: Indicates whether the DB2-managed data sets are to be erased when the index is deleted.
    ERASE: Indica se os conjuntos de dados gerenciados pelo DB2 devem ser apagados quando o índice for excluído.

FREEPAGE:

Specifies how often to leave a page of free space when index entries are created.
Especifica com que freqüência deixar uma página de espaço livre quando as entradas de índice são criadas.

PCTFREE:

Determines the percentage of free space to be left in each non-leaf page and leaf page when entries are added to the index.
Determina a porcentagem de espaço livre a ser deixado em cada página não folha e página folha quando as entradas são adicionadas ao índice.

DEFINE:

Specifies when the underlying data sets for the index are physically created.
Especifica quando os conjuntos de dados subjacentes para o índice são fisicamente criados.

  • YES: he data sets are created when the index is created YES is the default.
    SIM: os conjuntos de dados são criados quando o índice é criado. SIM é o padrão.

  • NO: The data sets are not created until data is inserted into the index.
    NÃO: Os conjuntos de dados não são criados até que os dados sejam inseridos no índice.

COMPRESS:

Specifies whether compression for index data will be used. Especifica se a compactação para dados de índice será usada.

  • NO: Specifies that no index compression will be used.
    This is the default.
    NÃO: Especifica que nenhuma compactação de índice será usada.
    Este é o padrão.

  • YES: Specifies that index compression will be used.
    SIM: Especifica que a compactação de índice será usada.

PARTITION BY RANGE:

Specifies the index is the partitioning index.
Especifica que o índice é o índice de particionamento.

  • PARTITION integer: A PARTITION clause specifies the highest value of the index key in one partition of a partitioning index.
    PARTITION integer: uma cláusula PARTITION especifica o valor mais alto da chave de índice em uma partição de um índice de particionamento.

  • ENDING AT: Specifies that this is the partitioning index and indicates how the data will be partitioned.
    ENDING AT: Especifica que este é o índice de particionamento e indica como os dados serão particionados.

    CONSTANT: Specifies a constant value with a data type that must conform to the rules for assigning that value to the column.
    CONSTANT: especifica um valor constante com um tipo de dados que deve estar em conformidade com as regras de atribuição desse valor à coluna.

    MAXVALUE: Specifies a value greater than the maximum value for the limit key of a partition boundary.
    MAXVALUE: especifica um valor maior que o valor máximo para a chave de limite de um limite de partição.

    MINVALUE: Specifies a value that is smaller than the minimum value for the limit key of a partition boundary.
    MINVALUE: especifica um valor menor do que o valor mínimo para a chave de limite de um limite de partição.

BUFFERPOOL:

Identifies the buffer pool that is to be used for the index.
Identifica o buffer pool que deve ser usado para o índice.

CLOSE:

Specifies whether or not the data set is eligible to be closed when the index is not being used and the limit on the number of open data sets is reached.
Especifica se o conjunto de dados está ou não qualificado para ser fechado quando o índice não está sendo usado e o limite no número de conjuntos de dados abertos é atingido.

  • YES: Eligible for closing. This is the default
    SIM: Elegível para fechar. Este é o padrão

  • NO: Not eligible for closing.
    NÃO: Não elegível para fechamento.

DEFER:

Indicates whether the index is built during the execution of the CREATE INDEX statement. Indica se o índice é construído durante a execução da instrução CREATE INDEX.

  • NO: The index is built.
    This is the default.
    NÃO: O índice é construído.
    Este é o padrão.

  • YES: The index is not built.
    SIM: o índice não foi construído.

PIECESIZE:

Specifies the maximum addressability of each data set for an index.
Especifica a endereçabilidade máxima de cada conjunto de dados para um índice.

COPY:

Indicates whether the COPY utility is allowed for the index.
Indica se o utilitário COPY é permitido para o índice.

  • NO: Does not allows full image or concurrent copies NO is the default.
    NÃO: Não permite imagens completas ou cópias simultâneas NÃO é o padrão.

  • YES: Allows full image or concurrent copies.
    SIM: Permite imagem inteira ou cópias simultâneas.

Example 1 - Exemplo 1:

Create an index on the column TAB1_COL2 of a table TB_TAB1.
Crie um índice na coluna TAB1_COL2 de uma tabela TB_TAB1.

    CREATE INDEX IX_TAB1_COL2
           ON TB_TAB1(TAB1_COL2);

Create an index named IX_PROJECT_NAME on the TB_PROJECT table.
The purpose of the index is to ensure that there are not two entries in the table with the same value for project name (PROJECT_NAME).
The index entries are to be in ascending order.

Crie um índice denominado IX_PROJECT_NAME na tabela TB_PROJECT.
O objetivo do índice é garantir que não haja duas entradas na tabela com o mesmo valor para o nome do projeto (PROJECT_NAME).
As entradas do índice devem estar em ordem crescente.

    CREATE UNIQUE INDEX IX_PROJECT_NAME
           ON TB_PROJECT(PROJECT_NAME);

Create an index named IX_EMPLOYE_JOB_DEPT on the TB_EMPLOYE table.
Arrange theindex entries in ascending order by job title (EMPLOYE_JOB) within each department (EMPLOYE_DEPT).
Crie um índice denominado IX_EMPLOYE_JOB_DEPT na tabela TB_EMPLOYE.
Organize as entradas de índice em ordem crescente por cargo (EMPLOYE_JOB) dentro de cada departamento (EMPLOYE_DEPT).

    CREATE INDEX IX_EMPLOYE_JOB_DEPT
           ON TB_EMPLOYE (EMPLOYE_DEPT, EMPLOYE_JOB);

Example 2 - Exemplo 2:

Create a unique index, named DSN8910.IX_DEPT, on table DSN8910.TB_DEPT.
Index entries are to be in ascending order by the single column DEPT_NO.
DB2 is to define the data sets for the index, using storage group DSN8G910.

Crie um índice exclusivo, denominado DSN8910.IX_DEPT, na tabela DSN8910.TB_DEPT.
As entradas do índice devem estar em ordem crescente pela única coluna DEPT_NO.
O DB2 deve definir os conjuntos de dados para o índice, usando o grupo de armazenamento DSN8G910.

Each data set should hold 1 megabyte of data at most.
Use 512 kilobytes as the primary space allocation for each data set and 64 kilobytes as the secondary space allocation.
Make the index padded.

Cada conjunto de dados deve conter no máximo 1 megabyte de dados.
Use 512 kilobytes como a alocação de espaço primária para cada conjunto de dados e 64 kilobytes como a alocação de espaço secundária.
Faça o índice acolchoado.

The data sets can be closed when no one is using the index and do not need to be erased if the index is dropped.
Os conjuntos de dados podem ser fechados quando ninguém está usando o índice e não precisam ser apagados se o índice for eliminado.

    CREATE UNIQUE INDEX DSN8910.IX_DEPT
           ON DSN8910.TB_DEPT
                 (DEPT_NO ASC)
           PADDED
           USING STOGROUP DSN8G910
                          PRIQTY 512
                          SECQTY 64
                          ERASE NO
           BUFFERPOOL BP1
           CLOSE YES
           PIECESIZE 1M;

Explanation - Explicação:

The underlying data sets for the index will be created immediately, which is the default (DEFINE YES).
Assuming that table DSN8910.TB_DEPT is empty, if we wanted to defer the creation of the data sets until data is first inserted into the index, we would specify DEFINE NO instead of accepting the default behavior.

Os conjuntos de dados subjacentes para o índice serão criados imediatamente, que é o padrão (DEFINE YES).
Supondo que a tabela DSN8910.TB_DEPT esteja vazia, se quiséssemos adiar a criação dos conjuntos de dados até que os dados fossem inseridos primeiro no índice, especificaríamos DEFINE NO em vez de aceitar o comportamento padrão.

Specifying PADDED ensures that the varying-length character string columns in the index are padded with blanks.
Especificar PADDED garante que as colunas de sequência de caracteres de comprimento variável no índice sejam preenchidas com espaços em branco.

Example 3 - Exemplo 3:

Create a cluster index, named IX_EMP, on table TB_EMP in database DSN8910.
Put the entries in ascending order by column EMP_NO.
Let DB2 define the data sets for each partition using storage group DSN8G910.
Make the primary space allocation be 36 kilobytes, and allow DB2 to use the default value for SECQTY.
If the index is dropped, the data sets need not be erased.

Crie um índice de cluster, denominado IX_EMP, na tabela TB_EMP do banco de dados DSN8910.
Coloque as entradas em ordem crescente pela coluna EMP_NO.
Deixe o DB2 definir os conjuntos de dados para cada partição usando o grupo de armazenamento DSN8G910.
Faça com que a alocação de espaço primária seja de 36 kilobytes e permita que o DB2 use o valor padrão para SECQTY.
Se o índice for eliminado, os conjuntos de dados não precisam ser apagados.

There are to be 4 partitions, with index entries divided among them as follows:
Deve haver 4 partições, com entradas de índice divididas entre elas da seguinte forma:

  • Partition 1: entries up to H99 - Partição 1: entradas até H99
  • Partition 2: entries above H99 up to P99 - Partição 2: entradas acima de H99 até P99
  • Partition 3: entries above P99 up to Z99 - Partição 3: entradas acima de P99 até Z99
  • Partition 4: entries above Z99 - Partição 4: entradas acima de Z99

Associate the index with buffer pool BP1 and allow the data sets to be closed when no one is using the index.
Enable the use of the COPY utility for full image or concurrent copies.

Associe o índice ao buffer pool BP1 e permita que os conjuntos de dados sejam fechados quando ninguém estiver usando o índice.
Habilite o uso do utilitário COPY para imagem inteira ou cópias simultâneas.

    CREATE INDEX DSN8910.IX_EMP
            ON DSN8910.TB_EMP
              (EMP_NO ASC)
            USING
                STOGROUP DSN8G910
                        PRIQTY 36
                         ERASE NO
            CLUSTER
            PARTITION BY RANGE
                (PARTITION 1 ENDING AT('H99'),
                 PARTITION 2 ENDING AT('P99'),
                 PARTITION 3 ENDING AT('Z99'),
                 PARTITION 4 ENDING AT('999'))
            BUFFERPOOL BP1
            CLOSE YES
            COPY YES;