ajeh's questions

O logon que executa todos esses comandos é um membro da sysadminfunção de servidor em uma instância do SQL 2012 Developer. É o proprietário do banco de dados em que isso está sendo implantado. EXTERNAL ACCESS ASSEMBLYpermissão foi concedida ao login neste banco de dados. Também tentei tudo abaixo como o salogin sem sucesso.

alter database test set trustworthy on
go
create assembly [icsharpcode.sharpziplib]
from 'C:\Workspace\111\icsharpcode-SharpZipLib-4f2d664\bin\Release\ICSharpCode.SharpZipLib.dll'
with permission_set = UNSAFE --< This works!
go
create assembly OutOfRowCompression
from 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'
with permission_set = UNSAFE --< This fails!
go

O último comando falha com:

Msg 10327, nível 14, estado 1, linha 1 CREATE ASSEMBLY para assembly 'OutOfRowCompression' falhou porque assembly 'OutOfRowCompression' não está autorizado para PERMISSION_SET = UNSAFE. O assembly é autorizado quando uma das seguintes condições for verdadeira: o proprietário do banco de dados (DBO) tem a permissão UNSAFE ASSEMBLY e o banco de dados tem a propriedade de banco de dados TRUSTWORTHY ativada; ou o assembly é assinado com um certificado ou uma chave assimétrica que possui um logon correspondente com permissão UNSAFE ASSEMBLY.

Depois de obter o erro, assinei o assembly com uma nova chave assimétrica key1.pfxe a adicionei ao banco de dados mestre:

CREATE ASYMMETRIC KEY key1
FROM executable
FILE = 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'

Verificado se a chave existe:

Criou um login a partir desta chave e concedeu o modo Deus:

create login clr_key1 from asymmetric key key1
go
GRANT EXTERNAL ACCESS ASSEMBLY TO clr_key1
go

Ainda o mesmo erro do comando de criação de montagem.

Tentei construir o assembly OutOfRowCompressioncom acesso SAFE e UNSAFE no Visual Studio 2012 - nenhuma diferença.

Sinto que satisfiz todos os requisitos apresentados pela mensagem de erro. Deve funcionar. o que estou perdendo?

Há uma tabela existente no SQL 2012 Enterprise DB, que tem aproximadamente 120 MB de tamanho com aproximadamente 2.000 registros. Seu maior campo nvarchar(max)contém XML esponjoso (variando de 20 a 300 kB) que por si só compacta aproximadamente 50-75% em 7Zip ou RAR.

Eu tentei os seguintes comandos que retornam instantaneamente, mesmo que simplesmente consultar * da tabela leve alguns segundos:

alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=ROW)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=NONE)

Mas o tamanho não muda após nenhum deles, mesmo que as propriedades da tabela mostrem corretamente o tipo de compactação.

O servidor está realmente aplicando e removendo a compactação das linhas/páginas? Aparentemente, não tem tempo para fazer isso na fração de segundo que cada comando leva para ser executado.

Uma vez em algumas semanas ou meses, mas sempre no mesmo dia da semana, uma consulta de um procedimento armazenado usado em um trabalho em lote diário pode ficar visivelmente travada e ser executada por cerca de 200 minutos até que o aplicativo elimine sua conexão. Esta é uma consulta de livro didático muito trivial que une 5 tabelas e calcula um sum()em um campo para atribuir a uma variável, portanto, não há sentido em fornecê-la aqui. O número total de registros a serem somados é normalmente de 1 a 2 dezenas. Esta consulta não tem parâmetros, portanto, a detecção de parâmetros está (felizmente) fora de questão.

Eu olhei para várias coisas:

• Não há usuários online no banco de dados. Ele é usado apenas como um despejo de dados.
• Não há trabalho que seja executado simultaneamente. A última atividade no banco de dados é um backup tranlog que é concluído cerca de 10 minutos antes. A próxima atividade começa após cerca de 2 horas, é um backup completo que é concluído bem após alguns minutos (enquanto esta consulta ainda está enlouquecendo).

• Existem os seguintes bloqueios durante a execução desta consulta, todos no status GRANT:

  • OBJECTbloquear em uma das tabelas solicitadas no Sch-Smodo
  • HOBT.BULK_OPERATIONbloquear em outra mesa no Smodo
  • OBJECTlock em mais uma mesa solicitada no IXmodo

A consulta tem (NOLOCK)dicas sobre todas as tabelas unidas. Está em RUNNABLEestado e de acordo com sp_whoisactive, que roda a cada 15 min no servidor, a contagem de CPU da consulta em questão está aumentando constantemente. A CPU aumenta para cerca de 1.000 vezes a execução da consulta autônoma. O IO da consulta de sp_whoisactiveé quase o mesmo como se eu a executasse de forma autônoma, mas a contagem de leituras é enorme, 10.000 vezes mais do que a execução autônoma. Parece que a consulta está em um loop infinito. Mas quando chego ao escritório pela manhã, o trabalho é executado novamente em alguns segundos.

• O plano relatado por sp_whoisactiveé o mesmo que autônomo. Existe uma dica do otimizador para criação de um índice quando eu o executo, mas como o tempo autônomo da consulta é em torno de 200ms, não estou preocupado com essa dica. Implementá-lo parece reduzir mais 10ms desse tempo.

• Não há erros relatados por DBCC CHECKTABLEem nenhuma das tabelas envolvidas ou DBCC CHECKDB.

O único ponto de suspeita que posso ter sobre o plano é que ele tem dois ícones de paralelismo. Mas a adição option (maxdop 1)apenas aumenta sua execução de 200 para 400ms.

Existem 5 tabelas envolvidas, todas de tamanhos diferentes, a maior tem cerca de 1 * 10 ^ 6 registros, não muito grande. Atualizamos estatísticas e índices diariamente às 4h e o trabalho é executado à meia-noite antes que grandes volumes de dados sejam importados. O trabalho em si adiciona apenas 1 a 10 registros.

Forçar o plano de execução armazenado para a consulta travada leva ao mesmo resultado: consulta travada, CPU e E/S enormes. Deixar o otimizador fazer sua mágica ou forçar o plano que eu obtenho autônomo (são os mesmos) é concluído em uma fração de segundo.

...

Para encurtar a história: tive permissão para executar um rastreamento noturno agendado na produção durante o tempo de execução "normal" deste trabalho para tentar solucionar isso. Não sabemos quando será a próxima ocorrência, apenas esperamos que siga o padrão que observamos - a cada poucas semanas.

Minha pergunta de um milhão de dólares é quais eventos devo rastrear? Sou apenas um desenvolvedor SQL e não tenho as habilidades típicas de DBA, então não saberia quais entranhas do servidor SQL deveriam ser expostas por meio de um rastreamento para pegar o culpado em flagrante.

Quando crio um caso de teste em uma sessão #1:

create table ##t (a int)
begin tran
insert into ##t select 1

e em outra sessão #2 correr

select * from ##t

e ainda em outra sessão executar

sp_who2 active

então nenhum contador de CPU e IO está aumentando na sessão bloqueada #2. Isso faz sentido, mas é sempre o caso de uma instrução bloqueada?

Gostaria de saber se seria possível usar over (partition by...)cláusula para evitar o uso de uma sub-seleção no exemplo abaixo:

declare @t table (
     id         int
    ,code       char(2)
    ,descriptor int
)

insert into @t
      select 1, 'a1', 10
union select 1, 'a1', 20
union select 1, 'a1', 30
union select 2, 'b1', 10
union select 2, 'b1', 20
union select 2, 'b1', 30
union select 2, 'b2', 10
union select 2, 'b2', 20
union select 2, 'b2', 30
union select 3, 'c4', 10
union select 3, 'c4', 20
union select 3, 'c4', 30
union select 3, 'c7', 10
union select 3, 'c7', 20
union select 3, 'c7', 30


select *
from @t as t
where code = (select min(code) from @t where id = t.id)

Quero ver apenas os registros com codeigual a a1, b1e c4. Posso over (partition by...)atribuir 1 a todos aqueles e 2 a códigos b2e c7etc, para que no final eu possa dizer where row_number = 1em vez de usar uma subconsulta?

De acordo com este artigo do MSDN http://msdn.microsoft.com/en-us/library/ms190328.aspx

Quando um nome de tabela temporária é especificado, o nome do banco de dados deve vir antes do nome da tabela temporária, a menos que o banco de dados atual seja tempdb. Por exemplo: SELECT OBJECT_ID('tempdb..#mytemptable').

Mas quando estou realmente criando uma tabela temporária em um banco de dados diferente tempdbda função não está funcionando conforme especificado:

use beta1
create table #t(id int)
select object_name(object_id('tempdb..#t')) as from_beta1
use tempdb
select object_name(object_id('tempdb..#t')) as from_tempdb
drop table #t

Isto é o que eu vejo:

from_beta1
----------
NULL

from_tempdb
---------------
#t_________________...______0000000000B9

O que estou perdendo ou fazendo de errado?

Eu queria descobrir se era possível evitar o uso do cursor para iterar as linhas da tabela em PL/SQL e tentei o seguinte script:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0; -- number(10,0)
    v2 tbl_plant.name%type := ''; -- varchar2(50)
begin
    while true
    loop
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        exit when sql%rownum = 0;

        dbms_output.put_line(v1 || v2);
    end loop;
end;

Então, percebi que o compilador otimiza isso em um cursor implícito quando o whileloop é usado e tentei contornar:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0;
    v2 tbl_plant.name%type := '';
begin
<<divein>>
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        if sql%rownum = 0 then
          goto bailout;
        end if;

        dbms_output.put_line(v1 || v2);
        goto divein;
<<bailout>>
    null;
end;

Mas em ambos os casos, recebi exatamente o mesmo erro:

PLS-00207: identifier 'ROWNUM', applied to implicit cursor SQL, is not a legal cursor attribute

Mas se eu comentasse whileou gotoe os rótulos, eliminando qualquer tipo de loop, o mesmo select funcionava bem e retornava o primeiro id/nome tbl_plantpor ID da planta.

É possível iterar sem usar um cursor?

Há um tempo atrás (por volta de 2004) trabalhei como desenvolvedor back-end em uma equipe onde todos usavam a cláusula where joins. Como proponente do uso de junções internas/externas, mostrei a eles um exemplo de junção de cláusula where que ilustrou como poderia produzir resultados ambíguos dependendo dos dados e como as junções externas estavam livres desse problema.

Eu tenho que fazer a mesma apresentação, mas não consigo mais encontrar esse trecho. Está em algum lugar no meu arquivo de arquivos ao qual infelizmente não tenho acesso. Alguém tem um exemplo útil?

Pergunta 1:

Sempre que um Oracle SP deseja retornar código/mensagem de erro, ele falha ao retornar a exceção abaixo:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB.GET_COST", line 244
ORA-01403: no data found
ORA-06512: at line 1".

Estou chamando do MS SQL por meio de um servidor vinculado da seguinte maneira:

declare
     @id1   int
    ,@id2   varchar(8)
    ,@code  varchar(10)
    ,@curr  varchar(4)
    ,@ed    datetime
    ,@return_status int
    ,@error_code    varchar(10)
    ,@error_msg     varchar
    ,@landed_cost   numeric(14,5)

begin try
    exec ('BEGIN db.get_cost(?,?,?,?,?,?,?,?,?); END;'
         ,@code
         ,@id1
         ,@id2
         ,@curr
         ,@ed
         ,@return_status    output
         ,@error_code       output
         ,@error_msg        output
         ,@cost             output
    ) AT ORASRV
end try
begin catch
    select
          @return_status    as return_status
         ,@error_code       as error_code
         ,@error_msg        as error_msg
end catch

Parece que os parâmetros de entrada funcionam bem, pois seus valores são tais que quando o SP está retornando um resultado correto, tudo está funcionando. Somente os valores do parâmetro IN podem fazer com que o SP retorne um código de erro, quando o erro acima for lançado.

A declaração do lado do Oracle é a seguinte:

PROCEDURE get_cost (
     code   IN  VARCHAR2(10 BYTE),
     id1    IN  NUMBER(7,0),
     id2    IN  VARCHAR2(8 BYTE),
     curr   IN  VARCHAR2(4 BYTE),
     ed     IN  DATE,
     return_status  OUT NUMBER
     error_code     OUT VARCHAR2(10 BYTE)
     error_msg      OUT VARCHAR2,
     cost   OUT NUMBER(14,5)
)

Pergunta 2:

Para adicionar insulto à lesão, depois de lançar a exceção acima 3 vezes e funcionar bem uma vez, o script morre com a seguinte mensagem de erro do MS SQL:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Ele morre mesmo se eu excluir as 3 primeiras chamadas que causam o erro original e apenas chamar o SP com sucesso uma vez.

Ao chamar uma função Oracle com o seguinte em tipos de parâmetro

NUMBER(7,0)
VARCHAR2(8 BYTE)
VARCHAR2(4 BYTE)
DATE

e tipo de retorno NUMBER(14,5)do SQL Server 2008R2 por meio de um servidor Oracle vinculado, declarei as variáveis ​​de parâmetro no lado do SQL Server da seguinte maneira:

numeric(7,0) -- also tried INT, BIGINT
varchar(8)
varchar(4)
datetime -- also tried DATE

e retorna o tipo de variávelnumeric(14,5)

exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price, @id1, @id2, @curr, @ed ) AT ORASRV

A chamada falha com o seguinte erro:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1".

O que estou fazendo errado?

Para testar essa função usei o seguinte script PL/SQL e funcionou bem (impresso 0que é um resultado correto para os parâmetros):

declare
  c varchar2(4);
  ed date;
  res number(14,5);
begin
  c := 'USD';
  ed := to_date('22-MAY-14', 'DD-Mon-YYYY');
  res := pkg.getPrice(214360, '129746', c, ed);
  DBMS_OUTPUT.PUT_LINE(res);
end;

Também criei uma função apenas para teste:

create or replace FUNCTION getTest ( 
    a IN VARCHAR2 -- also tried INTEGER
)
RETURN  VARCHAR2 -- also tried INTEGER
IS
  res VARCHAR2(1); -- also tried INTEGER
BEGIN
  res := a;
  RETURN res;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;    
END;

Quando eu chamo do SQL Server da seguinte maneira

declare @a varchar(1),@b varchar(1) -- also tried INT
select @b = 'a'
exec ( 'BEGIN ? := db.getTest(?); END;', @a, @b) AT ORASRV

Estou tendo o erro a seguir:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1".
Msg 7215, Level 17, State 1, Line 172
Could not execute statement on remote server 'ORASRV'.

No caso de INTEGER no lado do Oracle e INT no lado do SQL Server, a mensagem é apenas"ORA-06502: PL/SQL: numeric or value error

No SQLDeveloper 4, alguns pacotes PL/SQL de 10g têm uma caixa regular, enquanto outros têm um ponto verde.

Aqueles com o ponto verde não expandem seus corpos (pode ser apenas uma coincidência). Qual é a diferença entre as embalagens com e sem ponto verde e por que algumas embalagens não expandem seus corpos?

Qual seria a sintaxe para testar se já existe uma tabela temporária global no DB2 para IBM i 7.1?

Eu tentei isso depois DECLARE GLOBAL TEMPORARY TABLE:

SELECT * FROM QSYS2.SYSTABLES WHERE SYSTEM_TABLE_SCHEMA = 'QTEMP';

e retornou um conjunto de resultados vazio. As tabelas permanentes parecem estar incluídas nessa visão, mas não as tabelas temporárias.

Por fim, preciso descartar condicionalmente uma tabela temporária ou truncar/excluir dados, se existirem.

Depois de ler a seção ' Definindo um procedimento SQL ' do DB2 UDB para iSeries SQL Programming Concepts V5R2 e a seção ' Retornando conjuntos de resultados de procedimentos armazenados ' do Centro de Informações do i5/OS, Versão 5 Release 4, criei o seguinte procedimento armazenado:

CREATE PROCEDURE TESTDAT.REPORT (IN DATESTART DATE, IN DATEEND DATE)
LANGUAGE SQL READS SQL DATA
BEGIN
    DECLARE CSR1 CURSOR WITH RETURN FOR
    SELECT *
    FROM  SYSIBM.SYSDUMMY1;

    OPEN CSR1;
    RETURN;
END

Em seguida, li a seção 'Exemplo 1: Chamando um procedimento armazenado que retorna um único conjunto de resultados' do mesmo i5/OS Information Center, Versão 5 Release 4 e tentei chamar esse procedimento do cliente SqlDbx (via ODBC) ou do relatório SSRS (via provedor IBMDASQL) usando a instrução abaixo:

CALL TESTDAT.REPORT (DATE('11/01/2013'), DATE('11/03/2013'));

Ele foi executado com sucesso, mas não retornou o conjunto de resultados. Executar isso através do navegador iSeries retorna com sucesso 2 instruções - selecione e declare o cursor. Chamando

SELECT * FROM  SYSIBM.SYSDUMMY1;

retorna a coluna IBMREQD com 1 linha de 'Y'. O que estou perdendo nos documentos acima?

Atualizar:

O SQL gerado para este procedimento inclui outras opções além das já discutidas:

SPECIFIC TESTDAT.REPORT 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 
SET OPTION  ALWBLK = *ALLREAD , 
ALWCPYDTA = *OPTIMIZE , 
COMMIT = *NONE , 
DECRESULT = (31, 31, 00) , 
DFTRDBCOL = *NONE , 
DYNDFTCOL = *NO , 
DYNUSRPRF = *USER , 
SRTSEQ = *HEX   

Algum deles pode afetar o retorno do conjunto de resultados para o SSRS?