Todo SGBD que se preze possui um recurso para gerar um contador usado em cada tabela. Os nomes até mudam, mas a funcionalidade é muito parecida: SEQUENCE, AUTO_INCREMENT ou IDENTITY.

Estes objetos/colunas, que eu chamarei apenas de identificadores daqui para frente, são usados para várias coisas: para identificar a ordem natural de entrada de dados na tabela ou para criar uma chave primária substituta, por exemplo. O que importa é que este recurso permite inserir em cada registro um valor númerico único, que não se repete nesta tabela.

No caso do DB2, temos três opções de identificadores:

1. SEQUENCE: é um objeto independente que oferece o recurso de contagem. Uma mesma sequência, identificada por um nome, pode ser usada em várias tabelas inserindo o mesmo número em todas elas;
2. IDENTITY GENERATED ALWAYS: é um identificador que faz parte de uma tabela específica e que sempre irá inserir um número automaticamente, não importa o tipo de operação que seja realizada (INSERT, LOAD, etc). Neste caso, você nunca poderá especificar um ID para um registro. O DB2 sempre vai inserir um número automaticamente;
3. IDENTITY GENERATED BY DEFAULT: também faz parte de uma tabela específica, mas com a vantagem de se poder especificar um ID para o registro, o que é muito útil em operações de transferência de dados entre tabelas (LOAD/IMPORT/DB2MOVE).

Veja que estes três identificadores oferecem recursos para resolver uma grande variedade de problemas, mas eles exigem um pouco de atenção do DBA.

Vejamos alguns exemplos: se precisamos usar um identificador numérico que vai ser repetido em várias tabelas, como é o caso do número de uma nota fiscal, SEQUENCE oferece uma boa solução. O objeto é controlado pelo SGBD e simplesmente usamos o objeto SEQUENCE como um “serviço”, sem nos preocuparmos com maiores detalhes.

Se vamos usar um identificador que seja a chave substituta para uma tabela, podemos usar o IDENTITY. Se esta tabela não recebe cargas de dados de outras tabelas e/ou sistemas, a opção óbvia é usar o IDENTITY GENERATED ALWAYS. Neste caso, passamos a bola pro SGBD e não precisamos mais nos preocupar com este identificador: novamente é o SGBD que toma conta de tudo.

Mas são muito comuns casos em que a tabela precisa importar dados de outros sistemas. E estes dados vêm de outras tabelas, que por sua vez usam seus próprios identificadores. Nesta situação, não é desejável que você importe os registros usando IDs diferentes daqueles que vieram da tabela fonte. Em outras palavras, não podemos deixar que o SGBD insira um valor automático para o campo ID em cada registro que é importado.

Para resolver estes problemas, o DB2 usa o IDENTITY GENERATED BY DEFAULT. Caso sua instrução (INSERT, LOAD, etc) não especifique um valor pro ID, o DB2 vai gerar um valor automático. Mas se a instrução recomendar um ID específico, o DB2 vai usá-lo sem nenhum problema.

Parece perfeito, não é? Mas para se usar este recurso é necessário observar alguns detalhes. Imagine uma situação em que você cria uma tabela com uma coluna IDENTITY GENERATED BY DEFAULT e, em seguida, faz uma carga de dados para esta tabela. Dependendo do modo como você executa esta carga de dados, serão importados valores para todos os campos da tabela, inclusive para o campo ID.

Depois de realizar esta importação com sucesso, você começa a operar o sistema normalmente, fazendo novas inserções de registros nesta tabela. Num belo dia, você recebe uma mensagem de erro ao fazer uma nova inserção nesta tabela:

One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by “1” constrains table “XXXX” from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505

Este é um erro clássico no uso de colunas do tipo IDENTITY GENERATED BY DEFAULT. Mas se você não conhecer este tipo de problema, vai ter um trabalho considerável até identificar que o erro está no campo ID.

O que acontece é que geralmente criamos um campo IDENTITY definindo que a contagem começa com um valor baixo, como por exemplo 1. Veja o exemplo a seguir:

create table "XXXX"  ( 
		  "ID" bigint not null generated by default as identity (   
		    start with +1   
		    increment by +1   
		    minvalue +1   
		    maxvalue +9223372036854775807   
		    no cycle   
		    cache 20   
		    no order ) , 
		  "DESCRIPTION"  varchar(50)
		);

Como fizemos uma importação de dados logo depois de criarmos a tabela, teoricamente ainda não começamos a usar o contador IDENTITY. Portanto o contador vai receber o valor 1 (veja na declaração “start with +1”) no primeiro INSERT que não especificar valor para o campo ID. No próximo INSERT, o ID recebe valor 2 e assim sucessivamente.

Estas operações de INSERT vão acontecer normalmente até que o valor a ser inserido no campo ID seja igual a um valor que já foi usado na operação de importação. E é aí que veremos a mensagem de erro mencionada acima.

Por esta razão, recomenda-se que se tenha dois cuidados principais com o uso do IDENTITY GENERATED BY DEFAULT. São eles:

1. Antes de fazer uma importação, verifique se o intervalo de valores do campo ID da fonte de dados não se sobrepõe ao intervalo de valores do campo ID da tabela de destino. É fácil importar dados para uma tabela vazia, mas quando importamos dados para uma tabela populada, temos obrigatoriamente que garantir que os dados importados usam uma faixa de valores de ID que não coincidem com os valores já existentes na tabela.
2. Depois de executar qualquer importação de dados, é essencial que se verifique qual é o valor máximo do campo ID para então redefinir o contador IDENTITY com o próximo valor disponível. Veja no script a seguir:

select max(ID)+ 1 as NEWID from XXXX;
			--resultado da consulta é 52347

		alter table "XXXX" alter column ID restart with 52347;

Com estes pequenos cuidados, você vai minimizar seus problemas com uso de contadores no DB2.

Para mais informações, clique aqui para saber mais sobre colunas IDENTITY no DB2, ou aqui para aprender mais sobre as SEQUENCES.